اگر هنوز هم به صورت دستی از طریق صفحات گسترده یا تکیه بر ابزارهای مرتب سازی در مدرسه قدیمی و قدیمی ، به صورت دستی می چرخید ، این کار را به سختی انجام می دهید. اکسل دارای عملکرد فیلتر قدرتمند است ، یک راه حل داخلی که احتمالاً از آن غافل شده اید.
اگر هنوز هم به صورت دستی از طریق صفحات گسترده یا تکیه بر ابزارهای مرتب سازی در مدرسه قدیمی و قدیمی ، به صورت دستی می چرخید ، این کار را به سختی انجام می دهید. اکسل دارای عملکرد فیلتر قدرتمند است ، یک راه حل داخلی که احتمالاً از آن غافل شده اید.
عملکرد فیلتر در اکسل چیست
عملکرد فیلتر یک فرمول آرایه پویا است که به طور خودکار ردیف ها را از داده های شما بر اساس معیارهای خاص استخراج می کند. این عملکرد به جای استفاده از روش هایی برای مرتب سازی و فیلتر کردن داده ها بر اساس رنگ ، لیستی را به روز می کند که هر زمان که داده منبع شما تغییر می کند تغییر می کند.
در اینجا نحو اساسی وجود دارد:
= فیلتر (آرایه ، شامل ، [if_empty])
آرایه دامنه داده شما است ، شامل معیارهای شما تعریف می شود ، و IF_EMPTY در صورت مطابقت با نتیجه ، پیام سفارشی را نشان می دهد.
رفتار پویا مزیت اصلی آن است. هنگامی که یک مقدار را در داده های منبع خود تغییر می دهید ، نتایج فیلتر شده فوراً به روز می شود ، بنابراین نیازی به تازه کردن پس از آن نیست. از این رو ، روشهای فیلتر استاتیک را که هر بار تغییر داده های شما نیاز به به روزرسانی های دستی دارد ، ضرب می کند.
عملکرد فیلتر با متن ، اعداد و تاریخ کار می کند. شما می توانید داده های فروش را بر اساس منطقه فیلتر کنید ، کارمندان استخدام شده پس از یک تاریخ خاص را پیدا کنید یا محصولات را بالاتر از یک قیمت خاص استخراج کنید. شما حتی می توانید آن را با سایر توابع ترکیب کنید تا ریاضیات اساسی را در اکسل بر روی نتایج فیلتر شده خود انجام دهید.
بیایید بگوییم که شما یک مدیر منابع انسانی هستید که باید همه کارمندان فعال را به سرعت شناسایی کنید. به جای پیمایش از طریق 3000 سوابق کارمند ، می توانید استفاده کنید:
= فیلتر (D2: D3004 ، (K2: K3004 = “فعال”)
این فرمول از عملکرد فیلتر برای نمایش فقط کارمندان فعال استفاده می کند و نیاز به انجام هر نوع مرتب سازی دستی را از بین می برد.
ترفندهای فیلتر پیشرفته برای کاربران برق
پس از تسلط بر فیلتر اساسی ، می توانید چندین معیار را با هم ترکیب کنید ، که امکانات بیشتری را برای تجزیه و تحلیل داده ها باز می کند. بر خلاف فیلتر ساده داده ها در جداول ، تکنیک های پیشرفته فیلتر به شما امکان می دهد منطق پیچیده ای را ایجاد کنید که آینه تصمیمات تجاری را نشان می دهد.
فیلتر با و منطق
و منطق به هر شرط نیاز دارد تا به طور همزمان برآورده شود. در اینجا ، ما از اپراتور ستاره (*) استفاده می کنیم تا تست های منطقی را با هم ضرب کنیم. هر شرط به عنوان دروازه ای عمل می کند که داده ها باید از آن عبور کنند.
به عنوان نمونه ای از داده های کارمندان ، فرمول زیر فقط آن دسته از کارمندانی را که فعال هستند ، در فروش کار می کنند و تمام وقت هستند.
= فیلتر (A2: AC3004 ، (K2: K3004 = “فعال”) * (Q2: Q3004 = “فروش”) * (L2: L3004 = “تمام وقت”))
هر سه شرط باید برای حضور یک کارمند در نتایج شما صادق باشد. اگر یک شرط از دست رفته باشد ، کارمند کاملاً فیلتر می شود. این رویکرد کاملاً کار می کند که شما نیاز به کاهش نامزدها برای نقش های خاص یا الزامات مربوط به انطباق داشته باشید.
شما می توانید به همان اندازه و شرایط لازم ، مانند اضافه کردن معیارهای عملکرد برای یافتن کارمندان فروش فعال با رتبه های بالا و کسانی که به طور کامل شرایط را برآورده می کنند ، جمع کنید.
= فیلتر (D2: D3004 ، (K2: K3004 = “فعال”) * (Q2: Q3004 = “فروش”) * (AB2: AB3004> = 4) * (AA2: AA3004 = “کاملاً ملاقات می کند”))
فیلتر با یا منطق
یا منطق در صورت برآورده شدن هر یک از شرایط نتیجه می گیرد. در این حالت ، ما از اپراتور Plus (+) بین تست های منطقی استفاده می کنیم.
= فیلتر (D2: D3004 ، (Q2: Q3004 = “فروش”) + (Q2: Q3004 = “تولید”) + (Q2: Q3004 = “فناوری”))
فرمول فوق کارمندان بخش های فروش ، تولید یا فناوری را به کار می برد. بر خلاف و فیلتر ، ملاقات فقط یک شرط برای واجد شرایط بودن یک رکورد برای ورود کافی است. بنابراین ، یا منطق به جای باریک شدن آنها ، نتایج شما را گسترش می دهد.
این نوع فیلتر هنگام ریختن یک شبکه گسترده تر مفید است. به عنوان مثال ، اگر به کارمندان چندین بخش برای یک پروژه متقابل نیاز دارید ، می توانید بدون نیاز به فرمول های جداگانه برای هر بخش ، از همه افراد مرتبط استفاده یا منطق استفاده کنید.
ترکیب و یا منطق
برای نمایش داده های پیچیده می توانید با هم مخلوط و یا منطق را مخلوط کنید. اگر می خواهید کارمندان با کارایی بالا از بخش های کلیدی داشته باشید ، می توانید ترکیب زیر را امتحان کنید.
= فیلتر (A2: AC3004 ، ((Q2: Q3004 = “فروش”) + (Q2: Q3004 = “تولید”)) * (AB2: AB3004> = 4))
این فرمول کارمندان فروش یا تولید را پیدا می کند که دارای رتبه بندی عملکرد چهار یا بالاتر هستند. پرانتز درست مانند معادلات ریاضی ، ترتیب عملیات را کنترل می کند.
شما حتی می توانید این موارد را در سناریوهای پیچیده تر مانند فیلتر کردن کارمندان خاتمه یافته به دلایل خاص اعمال کنید. چنین فرمول ترکیبی می تواند کارمندانی را که داوطلبانه مانده اند یا به دلیل علت خاتمه یافته اند ، اسیر کند ، اما فقط کسانی که توصیف خاتمه دارند پر شده اند.
درک چگونگی کار این اپراتورهای منطقی می تواند برای قابلیت های پیشرفته تجزیه و تحلیل داده ها مفید باشد که باعث کاهش هرج و مرج صفحه گسترده به بینش های عملی می شود.
مشکلات عملکرد فیلتر مشترک برای جلوگیری از
حتی کاربران با تجربه Excel هنگام اولین بار از فیلتر لک می زنند. این اشتباهات رایج می تواند فرمول های شما را از بین ببرد ، اما هنگامی که می دانید چه چیزی را تماشا کنید ، آسان است.
#Spill! خطا
این خطا هنگامی ظاهر می شود که نتایج فیلتر شده شما نمایش داده نمی شود زیرا سایر داده ها مسیر آنها را مسدود می کنند. فیلتر آرایه های پویا ایجاد می کند که برای گسترش به فضای خالی نیاز دارند. برای رفع این مشکل ، هرگونه داده زیر و سمت راست سلول فرمول خود را پاک کنید.
اگر 50 کارمند را فیلتر می کنید اما فقط 10 ردیف خالی در دسترس دارید ، Excel پرتاب می کند #spill! خطا به جای نشان دادن نتایج جزئی. همیشه از فضای کافی برای حداکثر خروجی ممکن خود اطمینان حاصل کنید.
انواع داده های ناسازگار
متنی که شبیه اعداد است ، گاهی اوقات می تواند منطق فیلتر را بشکند. شناسه های کارمند ذخیره شده به عنوان متن با معیارهای عددی مطابقت ندارند. من از فرمول زیر برای تبدیل شناسه های کارمند مبتنی بر متن به اعداد قبل از فیلتر استفاده می کنم.
= فیلتر (A2: A3004 ، مقدار (A2: A3004))
به طور مشابه ، تاریخ های قالب بندی شده به عنوان متن نیاز به تبدیل ستونهای تاریخ WRAP با DateValue () برای اطمینان از مقایسه مناسب دارند.
= فیلتر (A2: AC3004 ، DateValue (E2: E3004)> = تاریخ (2020،1،1))
مسائل مربوط به حساسیت مورد
فیلتر “فروش” و “فروش” را به عنوان مقادیر مختلف درمان می کند. با استفاده از تابعی مانند فوقانی () یا پایین () برای استاندارد سازی متن قبل از مقایسه ، متن متن را در اکسل تبدیل کنید. این باعث تغییر در ورود داده ها می شود که در غیر این صورت از طریق فیلترهای شما می چرخد.
= فیلتر (A2: AC3004 ، فوقانی (Q2: Q3004) = “فروش”)
عدم تطابق اندازه دامنه
محدوده معیارهای شما باید دقیقاً با محدوده داده شما مطابقت داشته باشد. اگر داده های شما شامل A2: AC3004 اما معیارهای شما به K2: K3000 مراجعه می کند ، چهار ردیف داده را از دست می دهید – سوابق مهم مهم.
همیشه بررسی کنید که دامنه های شما تراز شده است. از Ctrl + Shift + End استفاده کنید تا مرزهای داده واقعی خود را پیدا کنید تا حدس بزنید که داده های شما به پایان می رسد.
رسیدگی به نتایج خالی
هنگامی که هیچ داده ای با معیارهای شما مطابقت ندارد ، فیلتر بازگشت #Calc! به طور پیش فرض بنابراین ، شما باید از پارامتر سوم برای نمایش پیام های سفارشی استفاده کنید. این گزارش های پاک تر را ایجاد می کند و هنگام به اشتراک گذاری داده های فیلتر شده با همکاران که ممکن است پیام های خطای اکسل را درک نکنند ، از سردرگمی جلوگیری می کند.
= فیلتر (A2: AC3004 ، K2: K3004 = “آینده شروع” ، “هیچ کارمند آینده پیدا نشده است”)
عملکرد با مجموعه داده های بزرگ
فیلتر را هر بار که داده های منبع شما تغییر می کند ، محاسبه می کند. با هزاران ردیف و معیارهای متعدد ، این می تواند باعث کند شدن اکسل به خزیدن شود. با استفاده از میانبر Ctrl + Alt + F9 هنگام کار با مجموعه داده های بزرگ ، از حالت محاسبه دستی استفاده کنید.
برای گزارش های مکرر ، به جای نگه داشتن فرمول های زنده ، نتایج فیلتر شده را به عنوان مقادیر کپی و چسباند. انجام این کار باعث کاهش اندازه پرونده می شود و ضمن حفظ داده های تحلیل شده شما ، عملکرد را بهبود می بخشد.
اکسل روشهای زیادی برای سازماندهی داده ها ارائه می دهد ، اما فیلتر بدون دردسر نتایج فوری و پویا را ارائه می دهد. هنگامی که فیلتر در زمان واقعی را تجربه کردید که به طور خودکار به روز می شود ، به عقب نگاه نمی کنید.