تابع outlier و تشخیص داده‌های پرت در اکسل
هنگام کار با داده‌ها در اکسل، اغلب مشکلات مربوط به مدیریت اطلاعات پرت در مجموعه داده‌های خود را خواهید داشت. داشتن مقادیر پرت در انواع داده‌ها کاملاً رایج بوده و مهم است که این موارد پرت را شناسایی و از بین ببرید. به این ترتیب می‌توانید مطمئن شوید که تحلیل شما درست و معنادار است.
در این آموزش از مجله علمی آموزشی مکتب خونه، می‌خواهیم نحوه تشخیص داد های پرت در اکسل را همراه با برخی از تکنیک‌هایی که برای رسیدگی به این موارد پرت کاربرد دارند را به شما نشان دهیم. پس تا انتها همراه ما باشید.
تابع Outliers در اکسل چیست و چرا یافتن نقاط پرت مهم است؟
نقطه پرت یک نقطه داده است که بسیار فراتر از سایر نقاط داده در مجموعه است. هنگامی که شما یک داده پرت دارید، آن می‌تواند داده‌های شما را منحرف کند و موجب استنتاج‌های نادرست شود. بگذارید یک مثال ساده برای شما بزنیم.
فرض کنید 30 نفر با اتوبوس از مقصد A به مقصد B سفر می‌کنند. همه افراد در یک گروه وزنی و گروه درآمدی مشابه هستند. برای هدف این آموزش، متوسط وزن را 220 پوند و متوسط درآمد سالانه را 70000 دلار در نظر می‌گیریم.
حالا اگر جایی در وسط مسیر ما، اتوبوس بایستد و بیل گیتس وارد شود، فکر می‌کنید میانگین وزن و متوسط درآمد افراد داخل اتوبوس چه می‌شود؟
درست است، میانگین وزن احتمالاً تغییر زیادی نخواهد کرد اما متوسط درآمد افراد در اتوبوس به شدت افزایش خواهد یافت. این به این دلیل است که درآمد بیل گیتس در گروه ما پرت است و این به ما تفسیر اشتباهی از داده‌ها می‌دهد. درآمد متوسط برای هر نفر در اتوبوس چند میلیارد دلار خواهد بود که بسیار فراتر از ارزش واقعی است.
هنگام کار با مجموعه داده‌های واقعی در اکسل، می‌توانید مقادیر پرت را در هر جهتی داشته باشید (به عنوان مثال، نقطه پرت مثبت یا منفی). برای اطمینان از اینکه تحلیل شما درست است، باید به نحوی این موارد پرت را شناسایی کرده و سپس تصمیم بگیرید که چگونه به بهترین شکل با آن‌ها رفتار کنید. حالا بیایید چند راه برای پیدا کردن نقاط پرت در اکسل را با هم یاد بگیریم.
با مرتب سازی داده‌ها، موارد پرت را پیدا کنید!
با مجموعه داده‌های کوچک، یک راه سریع برای شناسایی موارد پرت این است که به سادگی داده‌ها را مرتب کنید و به صورت دستی برخی از مقادیر نامرتبط در این داده‌های مرتب شده را مرور کنید. از آنجایی که ممکن است در هر دو جهت مثبت و منفی، نقاط پرت وجود داشته باشد، مطمئن شوید که ابتدا داده‌ها را به ترتیب صعودی و سپس به ترتیب نزولی مرتب کرده‌اید و سپس عملیات یافتن نقاط پرت را انجام دهید.
بگذارید این کار را با یک مثال به شما نشان دهیم. در زیر مجموعه داده‌ای داریم که در آن مدت زمان تماس (بر حسب ثانیه) برای 15 تماس خدمات مشتری وجود دارد.
در زیر مراحل مرتب‌سازی این داده‌ها وجود دارد تا بتوانیم مقادیر پرت را در مجموعه داده شناسایی کنیم:
- از سربرگ ستونی را که می‌خواهید مرتب کنید انتخاب کنید.
- روی تب Home کلیک کنید.
- در گروه Editing بر روی آیکون Sort & Filter کلیک کنید.
- سپس بر روی Custom Sort کلیک کنید.
- در کادر محاوره‌ای مرتب‌سازی، «مدت» را در فهرست کشویی مرتب‌سازی بر اساس «بزرگترین به کوچکترین» را در فهرست کشویی ترتیب انتخاب کنید.
- در آخر بر روی دکمه Ok را کلیک کنید
مراحل بالا ستون مدت تماس را با بالاترین مقادیر در بالا مرتب می‌کند. اکنون می‌توانید داده‌ها را به صورت دستی اسکن کنید و ببینید که آیا موارد پرت وجود دارند یا خیر.
در این مثال ما، می‌توانم ببینم که دو مقدار اول بسیار بالاتر از بقیه مقادیر هستند (و دو مقدار پایین نیز بسیار پایین‌تر هستند).
توجه: این روش با مجموعه داده‌های کوچکی کار می‌کند که می‌توانید داده‌ها را به صورت دستی اسکن کنید. بنابراین این یک روش خوب برای مجموعه‌های بزرگ با داده‌های کلان نیست.
یافتن نقاط پرت با استفاده از توابع ربع
اکنون بیایید در مورد راه حل علمی‌تری صحبت کنیم که می‌تواند به شما کمک کند تا تشخیص دهید که آیا موارد پرت وجود دارند یا خیر. در آمار، یک چارک یک چهارم مجموعه داده است. به عنوان مثال، اگر 12 نقطه داده دارید، چارک اول سه نقطه داده پایینی، چارک دوم سه نقطه داده بعدی و غیره خواهد بود. در زیر، مجموعه داده‌ای است که می‌خواهیم مقادیر پرت آن را پیدا کنیم. برای انجام این کار، ما باید چارک 1 و 3 را محاسبه کنیم و سپس با استفاده از آن حد بالا و پایین را محاسبه نماییم.
در زیر فرمول محاسبه ربع اول در سلول E2 آمده است:
=QUARTILE.INC($B$2:$B$15,1)
و در اینجا فرمولی برای محاسبه ربع سوم در سلول E3 است:
=QUARTILE.INC($B$2:$B$15,3)
اکنون، می‌توانیم از دو محاسبه بالا برای بدست آوردن محدوده بین ربعی (که 50 درصد داده‌های ما در چارک اول و سوم است) استفاده کنیم.
=F3-F2
اکنون از محدوده بین ربع برای یافتن حد پایین و بالایی که بیشتر داده‌های ما را شامل می‌شود، استفاده می‌کنیم. آنگاه هر چیزی که خارج از این حد پایین و بالایی باشد، پرت در نظر گرفته می‌شود. در زیر فرمول محاسبه حد پایین آمده است:
=Quartile1 – 1.5*(Inter Quartile Range)
که در مثال ما می‌شود:
=F2-1.5*F4
و فرمول محاسبه حد بالا به صورت زیر است:
=Quartile3 + 1.5*(Inter Quartile Range)
که در مثال ما می‌شود:
=F3+1.5*F4
اکنون که حد بالا و پایین را در مجموعه داده‌های خود داریم، می‌توانیم به داده‌های اصلی برگردیم و به سرعت مقادیری را که در این محدوده قرار ندارند شناسایی کنیم.
یک راه سریع برای انجام این کار این است که هر مقدار را بررسی کنید و یک TRUE یا FALSE را در یک ستون جدید برگردانید.
ما از فرمول OR زیر برای بدست آوردن TRUE در مقادیری که پرت هستند استفاده کرده‌ایم.
=OR(B2<$F$5,B2>$F$6)
اکنون می‌توانید ستون Outlier را فیلتر کنید و فقط رکوردهایی را نشان دهید که مقدار آن TRUE است. همچنین، می‌توانید از قالب بندی شرطی برای برجسته کردن تمام سلول‌هایی که مقدار آن TRUE است استفاده کنید.
توجه: در حالی که این روش پذیرفته شده تری برای یافتن مقادیر پرت در آمار است، اما به نظر ما این روش را در سناریوهای واقعی کمی غیر قابل استفاده می دانیم. در مثال بالا، حد پایین محاسبه شده با فرمول 103- است، در حالی که مجموعه داده‌ای که داریم فقط می‌تواند مثبت باشد. بنابراین این روش می‌تواند به ما کمک کند که نقاط پرت را در یک جهت (مقادیر بالا) پیدا کنیم و در شناسایی نقاط پرت در جهت دیگر بی فایده است.
یافتن نقاط پرت با استفاده از توابع LARGE/SMALL
اگر با داده‌های زیادی کار می‌کنید (مقادیر در چندین ستون)، می‌توانید بزرگترین و کوچکترین 5 یا 7 مقدار را استخراج کنید و ببینید که آیا مقادیر پرت در آن وجود دارد یا خیر. اگر موارد پرت وجود داشته باشد، می‌توانید بدون نیاز به مرور همه داده‌ها در هر دو جهت، آن‌ها را شناسایی کنید.
فرض کنید مجموعه داده زیر را داریم و می‌خواهیم بدانیم که آیا موارد پرت در آن وجود دارد یا خیر.
در زیر فرمولی است که بیشترین مقدار را در مجموعه داده به شما می‌دهد:
=بزرگ ($B$2:$B$16,1)
به طور مشابه، دومین مقدار بزرگ توسط داده می‌شود.
=بزرگ ($B$2:$B$16,1)
اگر از مایکروسافت 365 که دارای آرایه‌های پویا است استفاده نمی‌کنید، می‌توانید از فرمول زیر استفاده کنید و پنج مقدار بزرگ از مجموعه داده را با یک فرمول واحد به شما می‌دهد:
=بزرگ ($B$2:$B$16,ROW($1:5))
به طور مشابه، اگر کوچکترین 5 مقدار را می‌خواهید، از فرمول زیر استفاده کنید:
=کوچک ($B$2:$B$16,ROW($1:5))
یا موارد زیر در صورتی که آرایه‌های پویا ندارید:
=کوچک ($B$2:$B$16,1)
هنگامی که این مقادیر را داشته باشید، پیدا کردن موارد پرت در مجموعه داده بسیار آسان است. در حالی که ما انتخاب کرده‌ایم که بزرگترین و کوچکترین 5 مقدار را استخراج کنم، شما می‌توانید بر اساس حجم مجموعه داده خود 7 یا 10 را انتخاب کنید.
چگونه به روش صحیح با موارد دور از دسترس برخورد کنیم ؟
تاکنون روش‌هایی را دیده‌ایم که به ما کمک می‌کنند مقادیر پرت را در مجموعه داده‌هایمان پیدا کنیم، اما وقتی متوجه شدید که موارد پرت وجود دارد، چه باید کرد. در اینجا چند روش وجود دارد که می‌توانید از آن‌ها برای رسیدگی به موارد پرت استفاده کنید تا تجزیه و تحلیل داده‌های شما صحیح باشد.
Outliers را حذف کنید.
ساده‌ترین راه، حذف اطلاعات پرت از مجموعه داده‌های شماست. به این ترتیب آن‌ها نمی‌توانند تحلیل شما را منحرف کنند. هنگامی که مجموعه داده‌های بزرگی دارید و حذف چند عدد پرت بر تجزیه و تحلیل کلی تأثیر نمی‌گذارد، حذف کردن راه حل مناسب‌تری است. البته، قبل از حذف داده‌ها، مطمئن شوید که یک کپی ایجاد کرده‌اید و در مورد آنچه که باعث ایجاد این موارد پرت می‌شود، تحقیق کنید.
عادی سازی نقاط پرت
اگر بخواهید، می‌توانید برای تمام مقادیر پرت، آن‌ها را به مقداری تغییر دهید که کمی بالاتر از حداکثر مقدار در مجموعه داده باشد. این باعث می‌شود که داده‌ها را حذف نکنیم، اما در عین حال اجازه هم نمی‌دهیم که داده‌هایمان را منحرف کنند.
برای ارائه یک مثال واقعی، اگر حاشیه سود خالص شرکت‌ها را تجزیه و تحلیل می‌کنید، جایی که اکثر شرکت‌ها بین 10- تا 30 درصد قرار دارند و چند مقدار وجود دارد که بالای 100 درصد هستند. به سادگی این مقادیر پرت را به 30٪ یا 35٪ تغییر می‌دهد. بنابراین اینها برخی از روش‌هایی هستند که می‌توانید در اکسل برای پیدا کردن موارد پرت استفاده کنید .
هنگامی که نقاط پرت را شناسایی کردید، می‌توانید به داده‌ها بپردازید و به دنبال علت این موارد بگردید، در عین حال یکی از تکنیک‌ها را برای رسیدگی به این موارد پرت انتخاب کنید (که می‌تواند حذف آن‌ها یا عادی سازی‌شان با تنظیم مقدار باشد.)
امیدواریم این آموزش اکسل برای شما مفید بوده باشد. اگر هم خواهان شرکت در دوره آموزش جامع اکسل مجله علمی آموزشی مکتب خونه هستید، همین امروز، ثبت نام کنید و همه نکات و موارد کاربردی اکسل را از صفر تا صد، زیر نظر برترین اساتید کشور، یاد بگیرید.
