هنک یک برگه دارد که در آن اقلام مختلف تولید شده توسط شرکتش را ردیابی می کند. یکی از ستون ها شامل هزینه تخمینی برای هر آیتم است و ستون دیگری شامل هزینه واقعی آن آیتم است. حدود 1500 ردیف داده در کاربرگ وجود دارد، و برای هنک بسیار مفید خواهد بود که بتواند داده ها را فیلتر کند تا فقط ردیف هایی را نشان دهد که هزینه واقعی با هزینه برآورد شده متفاوت است.
چندین راه وجود دارد که می توان این کار را انجام داد. روش سنتی اضافه کردن یک ستون کمکی ساده برای جزئیات تفاوت بین دو ستون است. با فرض اینکه هزینه تخمینی شما در ستون C و هزینه واقعی شما در ستون D باشد، می توانید فرمول زیر را در هر سلول از ستون کمکی قرار دهید:
=C1-D1
چیزی که در نهایت به آن می رسید ستونی است که واریانس بین هزینه های واقعی و برآورد شده را نشان می دهد. سپس می توانید بر اساس ستون کمکی فیلتر کنید، که به شما امکان می دهد فقط ردیف هایی را که می خواهید نمایش دهید.
البته، این یک راه اب زیرکانه برای دور زدن میل به فیلتر کردن بر اساس مقایسه دو سلول است - ستون کمکی شما، در نهایت، یک سلول واحد است که روی آن فیلتر می کنید. با این حال، می توانید با استفاده از تغییری که به ستون کمکی نیازی ندارد، این رویکرد اب زیرکانه را ادامه دهید. یک قالب مشروط برای سلول های ستون D (هزینه واقعی) اعمال کنید که اگر هزینه واقعی کمتر از مقدار تخمینی است، سلول را به یک رنگ برجسته می کند و اگر بالاتر از تخمین زده شده باشد، رنگ متفاوتی دارد. با نمایش رنگ ها، می توانید به راحتی بر اساس رنگ ها فیلتر کنید و دوباره فقط ردیف هایی را که می خواهید ببینید به شما ارائه می شود.
رویکرد سوم استفاده از یک فیلتر پیشرفته برای محدود کردن ردیف های خود است. مجدداً، فرض می کنیم که هزینه تخمینی شما در ستون C (سلول های C2:C1501) و هزینه واقعی در ستون D (سلول های D2:D1501) است. هر سلول در ردیف 1 باید سرصفحه ستونی مانند "Estimated" و "Actual" داشته باشد. اکنون باید یک جدول معیارهای کوچک در جایی در یک منطقه استفاده نشده از کاربرگ خود ایجاد کنید. در این مورد، من کلمه "تفاوت" را در سلول K1 و فرمول زیر را در سلول K2 وارد کردم:
=C2<>D2
کلمه ای که در سلول K1 وارد کردم واقعاً مهم نبود. این امر ضروری بود زیرا ابزار Advanced Filter انتظار دارد یک هدر در جدول معیارها قرار گیرد. کلید فرمول وارد شده به سلول K2 است. با وجود جدول معیارها، این مراحل را دنبال کردم:
شکل 1. کادر محاوره ای فیلتر پیشرفته. محدوده داده نشان داده شده شما متفاوت خواهد بود.
- هر سلولی را در جدول داده های اصلی (نه در جدول معیارها) انتخاب کنید.
- تب Data روبان را نمایش دهید.
- در گروه Sort & Filter روی Advanced کلیک کنید. اکسل کادر گفتگوی Advanced Filter را با آدرس جدول داده های اصلی شما در کادر List Range نشان می دهد. (شکل 1 را ببینید.)
- قسمت Criteria Range را انتخاب کنید. نقطه درج باید در فیلد ظاهر شود.
- از ماوس برای انتخاب محدوده معیار، سلول های K1:K2 استفاده کنید.
- روی OK کلیک کنید.
خودشه. اکسل بلافاصله داده های شما را فیلتر می کند تا فقط ردیف هایی را نشان دهد که هزینه واقعی با هزینه تخمینی متفاوت است.
در صورت رعایت دو شرط می توان از رویکرد چهارم استفاده کرد. اول اینکه محدوده داده های خود را به صورت جدول فرمت کرده اید و دوم اینکه از اکسل 2021 یا نسخه اکسل ارائه شده با Office 365 استفاده می کنید. سپس تنها کاری که باید انجام دهید این است که یک ناحیه استفاده نشده از کاربرگ خود را پیدا کرده و وارد کنید. فرمول زیر:
=FILTER(Table1,Table1[Actual]<>Table1[Estimated],"")
The formula spills into as many cells as necessary in order to return the rows where the actual and estimated costs differ. This approach is very simple, and I only noted two potential drawbacks. First, the FILTER function does not return column headings; it just returns data rows. The second potential drawback is that it always returns something, even if there was nothing in some of the source cells. For instance, if you have a column in your data called Comments, and there is not a comment in every cell in the column, then FILTER returns a 0 value for those empty cells. These two potential drawbacks can make your returned data look a little funky, but that can always be touched up with the judicious use of formatting.
