داده‌های خارج از محدوده یا Outlier به مقادیری گفته می‌شود که فاصله‌ی زیادی با دیگر داده‌های یک مجموعه عدد دارند. به عبارت دیگر بسیار کمتر یا بسیار بیشتر از سایر موارد هستند. در نرم‌افزار Excel مجموعه‌ی آفیس مایکروسافت توابعی برای شناسایی این نوع مقادیر وجود دارد. به علاوه اگر قصد محاسبه کردن میانگین داده‌ها را داشته باشید، تابعی موجود است که اعداد خارج از محدوده را در محاسبه لحاظ نمی‌کند.
در ادامه با بررسی یک مثال ساده، استفاده از توابع اکسل برای یافتن داده‌های خارج از محدوده را توضیح می‌دهیم. با یکی دیگر از سری مطالب آموزش توابع اکسل همراه شوید.
در تصویر زیر نام افراد در ستون A و مقادیر در ستون B ذکر شده است. همان‌طور که مشاهده می‌کنید عدد ۱۷۳ و ۲ دو مقداری هستند که به مراتب بیشتر و کمتر از سایر اعداد به نظر می‌رسند. البته این لیست کوتاه و ساده است و در یک نگاه داده‌های خارج از محدوده یا Outlier قابل تشخیص است. برای لیست‌های طولانی و عظیم، این روش پاسخ‌گوی نیاز کاربران اکسل نیست.
حذف کردن مقادیر خارج از محدوده در برخی محاسبات و مطالعه‌های آماری بسیار مهم و ضروری است چرا که ممکن است این داده‌ها، موارد دارای خطا باشند و به نوعی استثناء محسوب شوند.
مراحل شناسایی داده‌های خارج از محدوده در Excel
برای این کار می‌بایست چند گام زیر را طی کنید:
- یافتن مقدار چارک اول و سوم
- مشخص کردن بازه‌ی اعداد میانی
- مشخص کردن مرز بالا و مرز پایین مجموعه داده‌ها
- استفاده از مرزها برای یافتن داده‌های خارج از محدوده
و اما توضیحات چهار گام فوق با بررسی مثال ذکر شده: ابتدا یک جدول کوچک برای ذکر چارک اول و سوم و محدوده‌ی میانی و همین‌طور مرز بالا و پایین ایجاد کنید:
محاسبه‌ی چارک‌ها در مجموعه داده‌ها به کمک اکسل
اگر داده‌های یک سری داده را به چهار دسته تقسیم کنید، به هر یک از این دسته‌ها یک چارک یا Quartile گفته می‌شود. ۲۵ درصد اعداد کوچک‌تر در چارک اول قرار می‌گیرد و ۲۵ درصد اعدادی که بزرگ‌تر هستند در چارک چهارم قرار می‌گیرد. بازه‌ی میانی به محدوده‌ای گفته می‌شود که شامل نیمی از اعداد میانی است و به عبارت دیگر شامل اعداد چارک دوم و سوم است.
یکی از پرکاربردترین تعریف‌ها برای داده‌ی خارج از محدوده، این گونه است:
عدد خارج از محدوده، عددی است که ۱.۵ برابر بیشتر از بازه‌ی میانی از عدد چارک اول کوچک‌تر است و یا عددی است که ۱.۵ برابر بازه‌ی میانی از چارک سوم بزرگ‌تر است.
لذا علاوه بر مرز چارک‌های اول و آخر، به کمیت دیگری به اسم محدوده‌ی بازه‌ی میانی که به اختصار IQR یا InterQuartile Range گفته می‌شود نیاز داریم.
برای محاسبه کردن چارک‌ها در اکسل می‌توانید از تابعی به اسم QUARTILE استفاده کنید. در این تابع دو داده به عنوان ورودی دریافت می‌شود که اولین مورد آرایه‌ی اعداد است و دومین مورد نیز شماره‌ی چارک است.
بنابراین باید از فرمول زیر در سلول موردنظر استفاده کنید:
=QUARTILE(array, quart)
برای محاسبه‌ی چارک اول، آرگومان دوم در این تابع می‌بایست ۱ باشد و برای محاسبه کردن چارک سوم نیز باید از عدد ۳ استفاده کنید.
توجه کنید که مایکروسافت در Excel 2010 دو تابع دیگر به نام‌های QUARTILE.INC و QUARTILE.EXC را نیز ارایه کرده که گزینه‌های بهینه‌تری هستند اما تابع قدیمی QUARTILE سازگاری کاملی با نسخه‌های قدیمی‌تر اکسل دارد و لذا استفاده از آن توصیه می‌شود.
به مثال ابتدای مقاله برگردیم:
رنج داده‌ها در سلول B2 الی B14 قرار گرفته است. بنابراین در سلول F2 که چارک اول موردنیاز است، فرمولی به صورت زیر را تایپ می‌کنیم:
=QUARTILE(B2:B14,1)
همان‌طور که مشاهده می‌کنید اکسل حین تایپ کردن تابع، پیشنهاد می‌کند که آرگومان دوم را ۰ الی ۴ وارد کنیم. عدد ۰ به معنی محاسبه کردن مقدار کمینه است و عدد ۴ نیز مقدار بیشینه را محاسبه می‌کند. سه عدد دیگر برای مشخص کردن مرز چارک‌ها کاربرد دارد.
در سلول F3 نیز از رابطه‌ی زیر استفاده می‌شود که مرز چارک سوم و چهارم را مشخص می‌کند:
=QUARTILE(B2:B14,3)
محاسبه کردن بازه‌ی میانی یا Interquartile Range در Excel
بازه‌ی میانی به محدوده‌ای شامل ۵۰ درصد میانی اعداد در سری داده گفته می‌شود. بنابراین مرز بالا و مرز پایین بازه‌ی میانی همان مقدار مرز چارک اول و چارک سوم است و اندازه‌ی محدوده میانی از فرمول زیر محاسبه می‌شود:
=F3-F2
محاسبه‌ی مرز بالا و مرز پایین
همان‌طور که اشاره کردیم، اعدادی خارج از محدوده محسوب می‌شوند که ۱.۵ برابر محدوده‌ی میانی از مرز چارک اول یا سوم کوچک‌تر یا بزرگ‌تر باشند. بنابراین مرز پایینی از فرمول زیر محاسبه می‌شود:
=F2-(1.5*F4)
و در مورد مرز بالایی نیز ۱.۵ برابر IQR را به مرز چارک سوم و چهارم اضافه می‌کنیم:
=F3+(1.5*F4)
مشخص کردن داده‌های خارج از محدوده به کمک اکسل
اکنون مرز بالا و پایین مشخص شده و با استفاده از رابطه‌ی منطقی OR که یکی از توابع اکسل است، می‌توانید دو شرط بزرگ‌تر از مرز بالا یا کوچک‌تر از مرز پایین را محاسبه کنید. مقدار خروجی این تابع در صورت برقراری یکی از شرط‌ها، TRUE است.
بنابراین در اولین سلول از ردیف C از رابطه‌ی زیر استفاده می‌شود:
=OR(B2<$F$5,B2>$F$6)
علت استفاده از علامت $ این است که برای کپی کردن فرمول در سلول‌های بعدی ستون C، می‌بایست ریفرنس به سلول‌های جدول شامل مرز بالا و پایین تغییر نکند.
توجه کنید که استفاده از $ قبل از نام ردیف‌ها و سلول‌ها و همین‌طور قبل از شماره‌ی سلول، به این معنی است که با کپی کردن سلول در خانه‌های دیگر، این بخش‌ها تغییر نمی‌کند و سایر بخش‌ها متناسب با موقعیت سلول جدید، عوض می‌شود.
حال با روش درگ کردن مربع کوچک و پایین سلول، می‌توانید فرمول را در سلول‌های بعدی کپی کنید.
همان‌طور که در تصویر زیر مشاهده می‌کنید، دو سلول خارج از محدوده به راحتی شناسایی می‌شود.
تابع محاسبه میانگین بدون داده‌های خارج از محدوده
با روشی که توضیح داده شد، به راحتی داده‌ای که Outlier محسوب می‌شود، قابل شناسایی است اما برای محاسبه‌ی میانگین داده‌ها در اکسل، می‌توانید روش کوتاه‌تری را به کار گیرید که نیازی به شناسایی داده‌های خارج از محدوده ندارد. برای این مهم از تابع TRIMMEAN استفاده می‌شود. سینتکس کلی استفاده از این تابع به صورت زیر است:
=TRIMMEAN(array, percent)
آرگومان اول سلول‌هایی است که به میانگین‌شان نیاز دارید و آرگومان دوم که درصد نام دارد، درصدی از داده‌ها است که خارج از مرز بالا و مرز پایین مجموعه داده قرار دارند. این آرگومان را می‌توانید به صورت درصدی با ذکر علامت % یا به صورت عدد اعشاری تایپ کنید.
بنابراین برای محاسبه‌ی میانگین داده‌ها بدون در نظر گرفتن ۲۰ درصد داده‌های خارج از محدوده، فرمول زیر را در سلول موردنظر تایپ کنید:
=TRIMMEAN(B2:B14, 20%)
howtogeekسیاره‌ی آی‌تی