عملکرد FILTER در مایکروسافت اکسل یکی از حیاتی ترین توابع برای تسلط است. بدون آن، به سختی می توانید داده های مورد نیاز خود را پیدا کنید. در اینجا یک دوره آموزشی برای استفاده از FILTER در اکسل وجود دارد.
توجه به این نکته نیز مفید است که این تابع تنها راه فیلتر کردن داده ها در MS Excel نیست. شما ابزارهایی مانند فیلتر خودکار و فیلتر پیشرفته برای رسیدن به همین هدف دارید، با برخی از هشدارهای مهم که در این راهنما به آنها اشاره خواهیم کرد.
عملکرد FILTER چیست؟
توابع یا فرمول های اکسل نان و کره اکسل است که به شما امکان می دهد کارهایی مانند یافتن میانگین یک مجموعه داده بزرگ یا ایجاد نمودار منحنی بل را انجام دهید. هر تابع نحو خود را دارد که معمولاً می توانید فقط با وارد کردن نام تابع در اکسل آن را بررسی کنید.
تابع Excel FILTER، همانطور که از نام آن مشخص است، برای "فیلتر کردن" مقادیر یک محدوده مشخص با توجه به شرایط خاص استفاده می شود. هم محدوده و هم شرایطی که باید استفاده شود با این تابع وارد می‌شوند و آن را بسیار قابل تنظیم می‌کند.
با پارامترهای مناسب، می‌توانید اطلاعات دقیق مورد نیاز خود را از یک صفحه‌گسترده استخراج کنید، بدون اینکه لازم باشد کل مورد را برای یافتن ورودی‌های منطبق به صورت دستی مرور کنید. و از آنجایی که خروجی در یک سلول قرار دارد، می‌توانید توابع بیشتری را برای انجام محاسبات یا تجسم نتایج در یک نمودار زنجیره‌ای کنید.
چرا تابع FILTER نسبت به فیلتر پیشرفته؟
بیشتر مبتدیان اکسل به جای تلاش برای یادگیری نحو یک تابع، به ابزارهای داخلی فیلتر داده در اکسل پایبند هستند. فیلتر خودکار ساده ترین است که به شما امکان می دهد ستون ها را حذف کنید و معیارهای فیلتر را از یک رابط مبتنی بر منو تنظیم کنید. سپس فیلتر پیشرفته با قابلیت اعمال چندین معیار برای اجرای طرح های فیلترینگ پیچیده وجود دارد.
پس چرا حتی استفاده از تابع FILTER را به خود زحمت دهید؟
مزیت اصلی استفاده از توابع اکسل نسبت به انجام هر عملیات به صورت دستی (با ابزار اکسل دیگر یا حتی هر برنامه دیگری) پویا بودن توابع است. فیلتر خودکار یا فیلتر پیشرفته به شما نتایج یکباره ای می دهد که با تغییر داده های منبع تغییر نمی کنند. از سوی دیگر، تابع FILTER، نتایج خود را بر اساس آن هنگام تغییر داده ها به روز می کند.
Syntax تابع FILTER
نحو فرمول FILTER به اندازه کافی ساده است:
=FILTER(آرایه، شامل، [if_empty])
یک آرایه یک زیرمجموعه مستطیلی از صفحه گسترده است که با تعیین محدوده ای بین سلول بالا سمت چپ و سلول پایین سمت راست نشان داده می شود. به عنوان مثال، A3:E10 آرایه ای است که از ستون های A تا E و ردیف های 3 تا 10 تشکیل شده است..
پارامتر بعدی صرفاً معیاری است که باید استفاده شود، یا از نظر فنی، یک آرایه بولی است. این به شکل عبارتی وارد می‌شود که مقدار محدوده‌ای از سلول‌ها (معمولاً یک ستون) را ارزیابی می‌کند که TRUE یا FALSE را برمی‌گرداند. به عنوان مثال، زمانی که مقدار سلول با رشته داده شده مطابقت داشته باشد، A3:A10=”Pass” TRUE را برمی گرداند.
در نهایت، می توانید مقداری را وارد کنید تا زمانی که هیچ ردیفی با شرایط مطابقت نداشته باشد، توسط تابع FILTER برگردانده شود. این می تواند یک رشته ساده مانند "هیچ رکوردی پیدا نشد" باشد.
مرتبط: آیا باید ببینید که داده‌های نمونه چقدر با یک مجموعه داده بزرگ‌تر مطابقت دارند؟ راهنمای ما در مورد محاسبه خطای استاندارد در اکسل را بررسی کنید.
استفاده از عملکرد FILTER
اکنون که نحو تابع FILTER را می دانیم، بیایید نحوه استفاده از FILTER را در صفحه گسترده بررسی کنیم.
داده‌های نمونه‌ای که برای این نمایش استفاده می‌کنیم آرایه‌ای از A2 تا F11 دارد که نمرات زیست‌شناسی ده دانش‌آموز را همراه با توزیع نرمال جدول‌بندی می‌کند.
بیایید تابعی بنویسیم تا ورودی‌ها را بر اساس نمرات امتحانشان فیلتر کنیم (در ستون D ذکر شده است) و فقط مواردی را که نمره کمتر از 30 گرفته‌اند برگردانیم. این باید نحوی باشد:
=FILTER(A2:F11,D2:D11<30,"هیچ منطبقی یافت نشد")
از آنجایی که نتایج فیلتر شده زیرمجموعه ای از آرایه هستند، از تابع در سلولی با فضای کافی بعد از آن استفاده کنید. ما این کار را در زیر جدول اصلی انجام خواهیم داد:
و ما نتایج مورد انتظار را دریافت می کنیم. همه ورودی‌های با امتیاز کمتر از 30 انتخاب شده و در قالب یک جدول نمایش داده می‌شوند.
شما نیز محدود به یک شرط نیستید. از عملگر AND (*) استفاده کنید تا چندین عبارت را به عنوان یک پارامتر به زنجیر بکشید و فیلتر پیچیده تری ایجاد کنید.
بیایید تابعی بسازیم که ورودی های بین 30 تا 70 علامت را برمی گرداند. در اینجا نحو و نتایج آمده است:
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),"هیچ منطبقی یافت نشد")
برای معیارهای غیر انحصاری، می‌توانید از عملگر OR (+) نیز استفاده کنید. این با فیلتر مطابقت دارد حتی اگر فقط یکی از شرایط موجود به درستی ارزیابی شود.
در فرمول زیر، با فیلتر کردن نتایج کمتر از 15 یا بیشتر از 70، از آن برای یافتن نقاط پرت استفاده می‌کنیم.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70)،"هیچ رکوردی یافت نشد")
در نهایت، به جای استفاده از یک مقدار یا رشته برای برگرداندن زمانی که تابع FILTER چیزی پیدا نمی کند، می توانید مقادیری را برای هر ستون مشخص کنید تا اطمینان حاصل کنید که خروجی همیشه در قالب یکنواخت باقی می ماند..
ابتدا، اجازه دهید شرطی را که می دانیم نادرست است امتحان کنیم تا ببینیم به طور پیش فرض چگونه به نظر می رسد:
=FILTER(A2:F11,D2:D11>90،"هیچ منطبقی یافت نشد")
همانطور که می بینید، نتیجه فقط یک رشته دارد که با قالب مورد انتظار مغایرت دارد. این معمولاً مشکلی نیست مگر اینکه بخواهید نتایج (یا برخی مقادیر از آن) را به فرمول دیگری وارد کنید.
بنابراین بیایید سعی کنیم مقادیر پیش فرض را با همان فرمت ورودی آرایه ارائه کنیم. ما می توانیم این کار را با تعیین مقادیر جدا شده با کاما در پرانتزهای مجعد انجام دهیم. مانند این:
=FILTER(A2:F11,D2:D11>90،{"بدون سابقه"، "بدون رکورد"، "بدون سابقه"، 0})
این به ما نتایج دلپذیرتری می‌دهد، مطابق با بقیه قالب صفحه‌گسترده.
آیا عملکرد FILTER ارزشش را دارد؟
حتی اگر فقط از MS Excel برای نگهداری سوابق استفاده می‌کنید و نمی‌خواهید محاسبات جالبی انجام دهید، عملکرد FILTER یکی از معدود مواردی است که هنوز باید به آن توجه کنید.
زیرا وقتی کتاب کار شما به اندازه معینی رسید، مکان یابی دستی داده ها می تواند دردناک باشد. و در حالی که فیلتر خودکار و ابزارهای فیلتر پیشرفته مفید هستند، استفاده از یک تابع در درازمدت راحت‌تر است زیرا نتایج خود به‌روزرسانی می‌شوند و می‌توانند با سایر عملکردها جفت شوند.
.