میلیونها نفر هر روز از مایکروسافت اکسل برای ذخیره و تجزیه و تحلیل حجم قابل توجهی از دادهها استفاده میکنند. با وجود این حجم از اطلاعات ذخیره شده در اکسل ، سوال این است که چگونه دادههای مورد نیاز را پیدا میکنید؟ اینجاست که تابع FILTER اکسل وارد عمل میشود. FILTER یک تابع آرایه پویای قدرتمند است و برای کسانی که اشتراک مایکروسافت آفیس 365 دارند قابل دسترسی است . این تابع به فیلتر کردن مجموعه دادهها بر اساس معیارهای خاص کمک میکند. خروجی به محلی که تابع وارد شده است بازگردانده میشود، اما مهمتر از همه، به طور خودکار به هر تعداد سلول مجاور که برای نمایش نتایج لازم است، میریزد. برای آموزش نحوه استفاده از تابع FILTER در اکسل تا پایبن این مقاله با سلام دنیا همراه باشید.
تابع FILTER چیست؟
به عبارت ساده، یک فیلتر، دادهها را بر اساس معیارهای تعیینشده شما استخراج میکند. این فیلتر دادهها را از یک آرایه موجود میگیرد و خروجیای را تولید میکند که فیلتر اعمالشده را برآورده میکند. این امر مدیریت و پالایش مجموعه دادههای بزرگ را آسانتر میکند.
خب، تفاوت بین FILTER و یک فیلتر خودکار ساده در اکسل چیست؟ سادهترین پاسخ این است که تابع FILTER پویا است، در حالی که فیلترهای خودکار پویا نیستند. این بدان معناست که نتایج با تغییر دادههای شما به طور خودکار بهروزرسانی میشوند و آرایه خروجی برای تطبیق با مقادیر بازگشتی جدید تغییر اندازه میدهد. تابع FILTER همچنین در تطبیق با چندین معیار فیلتر، انعطافپذیرتر است .
حتما بخوانید: رفع مشکل خطای اجرا و باز شدن اکسل در ویندوز
نحو و آرگومانها
سینتکس تابع FILTER به صورت زیر است:
=FILTER(array, include, [if_empty])
سه آرگومان آن، array، include و if_empty اختیاری ، در زیر تعریف شدهاند.
آرایه (array)
اولین آرگومان تابع FILTER، آرایه است . این آرگومان مشخص میکند که میخواهید دادهها را از کدام سلولها استخراج کنید. با استفاده از تابع آرایه، میتوانید سلولهایی را که دادههای مورد نیاز شما را دارند و ترتیب نمایش آنها را انتخاب کنید.
این به اکسل میگوید که شما میخواهید فقط بخش خاصی از دادهها یا به شیوهای متفاوت از آنچه در ابتدا در مجموعه دادههای موجود فهرست شده است را نمایش دهید. هدف تابع FILTER همین است. این تابع به شما کمک میکند تا دادهها را بر اساس معیارهای تعریفشده خود مرتب کنید، به جای اینکه همان دادهها را به همان ترتیب و در مکانی متفاوت استخراج کنید.
Include
آرگومان include به اکسل میگوید که چه فیلتر یا مقادیری را میخواهید در خروجی خود بگنجانید. این میتواند عددی یا متنی باشد. اساساً، include از پارامترهای خاصی برای یافتن دادهها در اکسل استفاده میکند.
If_empty
این یک آرگومان اختیاری است که زمانی استفاده میشود که احتمال دارد فرمول هیچ مقداری را که با معیارها مطابقت دارد، پیدا نکند. آرگومان «if_empty» به شما امکان میدهد در صورتی که عملیات فیلتر منجر به یک آرایه خالی شود، یک خروجی (مانند یک پیام سفارشی یا یک محاسبه جایگزین) مشخص کنید. اگر این آرگومان حذف شود، یک آرایه خالی خطای #CALC! را برمیگرداند.
حتما بخوانید: سریع ترین راه برگرداندن فایل های حذف شده اکسل
موارد استفاده از تابع FILTER
رایجترین کاربرد تابع FILTER این است که اگر مجموعه دادههای عظیمی دارید و در استخراج اطلاعات مورد نظر خود مشکل دارید، میتوانید از FILTER استفاده کنید. این تابع با برگرداندن مقادیر عددی یا متنی که با پارامترهای فیلتر شما مطابقت دارند، دادههای قابل مشاهده در صفحه اکسل را محدود میکند.
برای مثال، میتوانید تابع FILTER را برای یافتن سلولهایی با مقادیر عددی بین 300 تا 800 اجرا کنید و خروجی، فهرستی از سلولهای دارای این مقادیر را تولید خواهد کرد.
مرتبسازی و استخراج اطلاعات با استفاده از FILTER به جای جستجوی دستی دادههای مورد نیاز، آسان است. به همین ترتیب، وارد کردن متنی که میخواهید قابل مشاهده باشد به شما کمک میکند تا مشخص کنید که آیا قبلاً اطلاعات خاصی را وارد کردهاید یا خیر و دقیقاً در کجای اکسل قرار دارد.
نحوه استفاده از تابع FILTER در اکسل
مثال پایهای تابع FILTER
بیایید مثال تابع FILTER را بررسی کنیم تا تصویر روشنی از نحوه عملکرد آن به دست آوریم.
- فرض کنید این برگه کاری است و دادهها در سه ستون (A تا C) سازماندهی شدهاند. نام نماینده فروش، منطقهای که در آن کار میکنند و کل فروشی که انجام دادهاند، در این ستونها وجود دارد.
- فرض کنید میخواهید اطلاعات مربوط به هدف فروش به دست آمده توسط افراد را در همان برگه کاری استخراج کنید. بنابراین، شما سه ستون مشابه (E تا G) ایجاد کردهاید.
مرحله 1: انتخاب آرایه
محل قرارگیری آرایه خود را تعیین کنید ، یعنی ستونها و ردیفهایی را که میخواهید دادهها را از آنها استخراج کنید، انتخاب کنید. در این حالت، دادهها در محدوده A2:C11 قرار خواهند گرفت.
=FILTER(A2:C11,
مرحله 2: معیارهای فیلتر خود را تعیین کنید
مشخص کنید که چه نوع دادهای را میخواهید استخراج کنید یا در خروجی خود بگنجانید. برای مثال، در تصویر زیر، ستون منطقه را انتخاب کرده و «ایالات متحده» را وارد کردهایم تا دادههای حاصل، نامها و فروشها را در منطقه ایالات متحده نشان دهند.
حتما بخوانید: قرار دادن تصویر داخل سلول اکسل
در این مثال، آرگومان if_empty را حذف خواهیم کرد .
=FILTER(A2:C11,B2:B11=”US”)
وقتی Enter را بزنید، تمام سلولها با نام، منطقه و فروش فقط برای نمایندگان منطقه ایالات متحده پر میشوند. در اینجا نحوه نمایش آن را مشاهده میکنید.
مرحله 3 (اختیاری): اگر هیچ مقداری معیارها را برآورده نکند، چه باید کرد؟
شما میتوانید از آرگومان if_empty استفاده کنید ، مخصوصاً اگر فکر میکنید این احتمال وجود دارد که مجموعه دادهها مقادیر مورد نظر شما را نداشته باشند.
برای مثال، فرمول زیر خطای #CALC! را برمیگرداند زیرا هیچ مقداری در مجموعه دادهها، معیارهای شمول را برآورده نمیکند .
=FILTER(A2:C11,C2:C11>20000)
برای برگرداندن نتیجهای که برای خواننده قابل فهمتر باشد، میتوانیم فرمول خود را به صورت زیر بهروزرسانی کنیم:
=FILTER(A2:C11,C2:C11>20000, “No results”)
کاربرد پیشرفته: فیلتر با معیارهای چندگانه
فرض کنید میخواهید دو شرط را لحاظ کنید. چند روش برای انجام این کار در اکسل وجود دارد، اما میتوان گفت تابع FILTER بهترین روش است.
حتما بخوانید: راهنمای تبدیل تصویر اسکرین شات به ستون های اکسل
1. معیارهای چندگانه – مثال 1
فرض کنید میخواهید دادههای مربوط به نمایندگان فروشی که در ایالات متحده هستند و فروش بالای 10000 واحد داشتهاند را استخراج کنید. فقط رکوردهایی که هر دو شرط را برآورده میکنند باید برگردانده شوند. در این صورت، باید هر دو شرط را جداگانه با استفاده از عملگر ضرب (*) ذکر کنید. در مثال ذکر شده در بالا، این شکلی خواهد بود.
=FILTER(A2:C11, (B2:B11=”US”) * (C2:C11>10000))
توجه داشته باشید که وقتی چندین شرط وجود دارد، هر شرط داخل پرانتز قرار میگیرد. وقتی Enter را بزنید، نتایجی که این دستور تولید میکند به شرح زیر است.
توجه داشته باشید که رکوردهایی که فقط یکی از شرایط لازم را داشته باشند نمایش داده نمیشوند.
معیارهای چندگانه – مثال 2
همچنین یک شرط «یا» وجود دارد که در آن میخواهید مقادیری را برگردانید که هر یک از شرایط ذکر شده را برآورده کنند. فرض کنید میخواهید لیست نمایندگان فروش و فروش آنها را برای ایالات متحده و کانادا نمایش دهید. در اینجا فرمولی که برای دریافت دادههای ترکیبی هر دو کشور استفاده خواهید کرد، آمده است.
در این حالت، از علامت جمع (+) برای جدا کردن شرطها استفاده خواهیم کرد.
=FILTER(A2:A11, (B2:B11=“US”) + (B2:B11=“Canada”))
این با موفقیت سوابق نمایندگانی را که در ایالات متحده یا کانادا هستند استخراج میکند.
عیبیابی: خطای #SPILL! هنگام استفاده از تابع FILTER
از آنجایی که خروجی تابع FILTER یک آرایه پویا است، به گونهای طراحی شده است که روی سلولهای مختلف سرریز شود تا کل اطلاعات مربوط به معیار داده شده را پوشش دهد. اگر ناحیه خروجی سلولهای خالی کافی برای جای دادن نتیجه نداشته باشد، اکسل خطای #SPILL! را برمیگرداند.
سخن آخر
تابع FILTER تنها یکی از چندین ویژگی است که میتواند صفحه گسترده اکسل معمولی شما را به یک ابزار قدرتمند تجزیه و تحلیل دادهها تبدیل کند. مهارتهای اکسل بیشتری کسب کنید تا به شما در بهبود بهرهوری و کارایی کلیتان کمک کند.
مقالات مرتبط:
روش مخفی کردن شیت ها در اکسل بطور همزمان
دیدگاه ها