تابع Filter از 1 مقدار 1 محدوده برمیگرداند!
تابع Filter از 1 مقدار 1 محدوده برمیگرداند!
وقتی از یکی از توابع جستجوگر مثل VLOOKUP و XLOOKUP استفاده می کنید حتما به این موضوع فکر کرده اید که ای کاش می شد به جای این که از یک مقدار فقط یک داده را بتوان فراخوانی کرد ، از یک مقدار بتوان یک محدوده را فراخوانی کرد. تابع Filter در اکسل این کار را برای شما به راحتی انجام میدهد.
با ابزار Filter در آموزش های قبلی آشنا شدید و کار کردن با این ابزار در جهت تهیه گزارشات حرفه ای را یاد گرفتید . در این آموزش با تابع Filter می خواهیم گزارشات حرفه ای تری را ایجاد کنیم. فرض کنید یک لیست بزرگی از داده های مربوط به بازراریاب های خود دارید و میخواهید بر اساس نام گزارش ماهیانه آنها را سریع ایجاد کنید و از آن کپی بگیرید . تابع Filter این کار را برای شما به راحتی انجام می دهد .
تابع Filter به تازگی در اکسل 2021 به جمع توابع نرم افزار اکسل اضافه شده است . در اکسل 2021 شما می توانید در یک سلول فرمول بدهید و یک محدوده را برگردانید . از تابع Filter فقط در اکسل 2021 می توانید استفاده کنید. برای مشاهده مسیر یادگیری اکسل مقدماتی و پیشرفته می توانید به مقاله آموزش اکسل مراجعه کنید و با مابقی فرمول های اکسل 2021 آشنا شوید.
گزارش سازی با تابع Filter
تابع Filter در تهیه گزارشات بسیار پرکاربرد است. فرض کنید جدولی از اطلاعات فروش محصولات در مدت مشخصی در دست است.
بر اساس هر کدام از سر تیتر ها می توان اقدام به نوشتن تابع Filter کرد . در دو حالت مختلف این فرمول را به شما آموزش می دهم . ابتدا بر اساس یک ستون ( گروه کالا ) تابع Filter را می نویسیم و سپس بر اساس مبلغ فروش ، بین دو مبلغ را با استفاده از فرمول Filter فراخوانی می کنیم.
مثال 1 : گروه کالا را برای من فیلتر کن!
اول یک قسمتی از اکسل را برای نوشتن فرمول Filter آماده می کنیم. فقط کافی است سر تیتیر ها را به دنبال هم بنویسیم و یک قسمتی هم برای نوشتن گروه کالا آماده کنیم.
بهتر است در سلول گروه کالا (سبز رنگ) ، گروه کالا ها را به صورت یک لیست کشویی ایجاد کنیم
در سلول سمت چپ این قسمت که سلول گروه کالا است اقدام به فرمول نویسی می کنیم. تابع Filter را در سلول خاکستری رنگ می نویسیم و آرگومان های این تابع را به صورت زیر پر می کنیم .
آرگومان Array : در این آرگومان باید آدرس کل محدوده ای که می خواهید فیلتر شود را بنویسید . در صورتی که داده های شما هر روز در حال تکمیل و به روزرسانی هستند بهتر است محدوده ی داده ها را ابتدا تبدیل به Table کنید و سپس اقدام به انتخاب محدوده داده ها در آرگومان Array کنید.
در مثال ما محدوده انتخابی در آرگومان Array از سلول A2 تا F20 است که با توجه به Table بودن محدوده مورد نظر ، در این آرگومان محدوده Table1 را انتخاب می کنیم.
آرگومان Include : این آرگومان همانند آرگومان Logical Test در فرمول IF عمل می کند. در این آرگومان یک شرط می نویسیم یا در واقع یک مقداری را برای فیلتر کردن مشخص میکنیم . مهمترین آرگومان فرمول Filter همین آرگومان است .
در مثال ما ، در این آرگومان باید ستون گروه کالا را انتخاب کنیم و آن را مساوی با سلول گروه کالا در لیست جدید قرار دهیم.
در نهایت پرانتز را میبندیم و اینتر را میزنیم . فرمول ما آماده است . زمانی که لیست باکس در گروه کالا را تغییر می دهیم ، محدوده فراخوانی شده نیز تغییر میکند .
ممکنه در حین کار با فرمول Filter با دو خطا مواجه شوید.
خطای !CLAC# : در زمانی که تابع Filter نتواند مقدار متناظر با شرط شما را پیدا کند ، این خطا را به شما نشان می دهد . در مثال ما اگر به جای گروه کالا یک نام دیگری که در گروه کالا وجود ندارد بنویسیم و یا سلول خالی باشد و نام هیچ گروه کالایی در آن نباشد این خطا ظاهر می شود.
برای رفع این خطا به آرگومان سوم تابع Filter نیاز داریم
آرگومان If Empty : در زمانی که فرمول Filter مقدار خواسته شده را نتواند در محدوده پیدا کند این آرگومان اجرا میشود . مثلا در این آرگومان میتوان متن “محدوده مورد نظر پیدا نشد” را نوشت . زمانی که فرمول Filter محدوده مورد نظر را پیدا نکند این پیام به شما نمایش داده می شود.
خطای !SPILL# : این خطا در زمانی اتفاق می افتد که تابع Filter جای کمی برای برگردادن محدوده مورد نظر دارد. در جایی که فرمول Filter محدوده برمی گرداند باید تمام سلول ها خالی باشند . اگر یک سلول حاوی داده ای باشد فرمول این خطا را نمایش می دهد . برای رفع این خطا ، محدوده مورد نظر فرمول Filter را پاک سازی کنید و مطمئن شوید در این محدوده داده ای وجود ندارد.
مثال 2 : بین دو مبلغ فروش را نمایش بده!
این بار می خواهیم بر اساس دو شرط اقدام به نوشتن فرمول Filter کنیم. همانند مثال قبل ابتدا مکانی برای نوشتن فرمول Filter آماده می کنیم.
در اینجا آرگومان اول همانند مثال 1 باید محدوده جدول باشد . اما مهمترین آرگومان فرمول Filter ، آرگومان include ، در این آرگومان باید دو شرط را بنویسیم . اولین شرط مربوط به مبالغ بیشتر از و دومین شرط مربوط به مبالغ کمتر از.
برای این کار باید از فرمول کمکی And استفاده کنیم. برای استفاده از فرمول And در تابع filter باید از یک ترفند استفاده کرد . هر کدام از شرط ها را در پرانتز می نویسیم و پرانتز ها را در هم ضرب می کنیم.
=FILTER(Table1,(Table1[مبلغ فروش]>Q2)*(Table1[مبلغ فروش]<S2))
برای استفاده از تابع And در آرگومان Include باید از علامت * اصتفاده کرد و برای استفاده از تابع Or باید از علامت + استفاده کرد.
با این روش به راحتی می توانید بین چند مقدار مختلف و چند داده مختلف اقدام به فیلتر سازی داده ها کنید و گزارشات بسیار حرفه ای را در اکسل بسازید.
جمع بندی
تابع Filter یک تابع جدید در اکسل 2021 است که متاسفانه افراد کمی از آن مطلع هستند . این تابع در گزارشگیری ها بسیار کارامد عمل می کند . در توابع جستجوگر اکسل مثل فرمول VLOOKUP و XLOOKUP ما به دنبال یک مقدار خاصی جستجو می کردیم اما با تابع فیلتر به دنبال مقادیر زیادی می گردیم .
برای یادگیری بهتر فرمول Filter در اکسل ، ویدئوی ۀموزشی را مشاهده کنید . همچنین می توانید برای کسب اطلاعات بیشتر به مقاله مربوطه در سایت رسمی مایکروسافت آفیس نیز مراجعه کنید
