Anand دارای یک کاربرگ است که حاوی اطلاعات فروش متشکل از نام مشتریان (ستون A) و تاریخ خرید (ستون B) است. او باید لیست را فیلتر کند تا بتواند مشتریانی را ببیند که دقیقاً X بار در یک ماه تقویمی خاص خرید کرده‌اند، جایی که X مقداری است مانند 1، 2 و غیره. در فیلترینگ یا روش دیگری بهتر است.
در واقع چندین راه مختلف وجود دارد که می توانید با این مشکل برخورد کنید. اکسل ابزارهای مختلفی را ارائه می دهد که می توانند برای به دست آوردن نتیجه مورد استفاده قرار گیرند، اما من فقط روی چند مورد از آنها تمرکز خواهم کرد. هر دو روش به ستون های کمکی برای استفاده در فیلتر کردن نیاز دارند.
روش اول فقط از یک ستون کمکی استفاده می کند. با فرض اینکه داده های شما از ردیف 2 شروع می شود و بیش از 99 ردیف را اشغال نمی کند، موارد زیر را در سلول C2 وارد کنید:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,">="&
DATE(YEAR(B2),MONTH(B2),1),$B$2:$B$100,"
این یک فرمول واحد است. تعداد خریدهایی را که فرد در ستون A در ماه مشخص شده با تاریخ در ستون B انجام داده است، برمی گرداند. فرمول را به تعداد ردیف هایی که برای داده های خود لازم است کپی کنید. با انتخاب سلولی در داده‌های شما، تب Data روبان را نمایش دهید و روی ابزار Filter در گروه Sort & Filter کلیک کنید. اکسل کنترل های فیلتر را در بالای هر ستون در داده های شما نمایش می دهد.
روی کنترل در بالای ستون تاریخ کلیک کنید تا پالت فیلتر نمایش داده شود، سپس ماه مورد نظر را انتخاب کنید. (در قسمت سال ها در پایین پالت، کادر انتخاب همه را پاک کنید، سپس یک سال و در نهایت یک ماه را انتخاب کنید.) روی کنترل در بالای ستون کمکی کلیک کنید تا تعداد فروش مورد نظر خود را در قسمت فیلتر شده انتخاب کنید. فهرست (شکل 1 را ببینید.)
شکل 1. داده های فیلتر شده شما.
توجه داشته باشید که نتیجه، تکرارهای متعدد هر نام را برای ماه مورد نظر نشان می دهد. بنابراین، اگر بخواهید مواردی را که 3 بار در یک ماه خرید کرده اند نمایش دهید، 3 ردیف برای هر نامی در ماه مورد نظر خواهید دید.
این برای یک نرخ X نسبتاً پایین برای یک ماه خوب است. با این حال، اگر بخواهید کسانی را ببینید که 50 بار در یک ماه خرید کرده‌اند، به سرعت بی‌حرکت می‌شود. در عوض، شما به راهی برای محدود کردن به یک نمونه از یک نام خاص در لیست فیلتر شده نیاز دارید.
این ما را به راه‌حلی می‌رساند که به دو ستون کمکی نیاز دارد که آنها را Count و Tally می‌نامیم. اگر اولین سلول شما در ستون شمارش C4 است، این را در آن سلول وارد کنید:
=IF(EOMONTH(B4,0)<>EOMONTH($B$1,0),0,IF(A4=A3,C3+1,1))
این فرمول را تا جایی که برای داده های خود لازم است کپی کنید. مقدار 0 را برمی گرداند مگر اینکه ماه و سال با ماه و سال سلول B1 مطابقت داشته باشد. (تاریخ سلول B1 باید در ماه مورد نظر شما باشد. می تواند هر تاریخی در آن ماه باشد.) اگر ماه و سال مطابقت دارند، تعداد فروش برای هر مشتری برای همان مشتری در آن ماه افزایش می یابد.
اگر اولین سلول ستون Tally سلول D4 است، این فرمول را در آن سلول قرار دهید:
=IF(C4>=C5,C4,0)
این فرمول را در پایین ستون نیز کپی کنید. فرمول تنها بالاترین مقدار را از ستون شمارش برای هر مشتری حفظ می کند و همه مقادیر دیگر را روی 0 تنظیم می کند.
اکنون، با انتخاب هر سلولی از داده های خود، تب Data را در نوار نمایش داده و بر روی ابزار Filter کلیک کنید. اکسل کنترل های فیلتر را در بالای هر ستون در داده های شما نمایش می دهد. (شکل 2 را ببینید.)
شکل 2. مجموعه نمونه ای از داده ها.
اکنون آماده دریافت اطلاعات مورد نظر هستید. فقط داده ها را ابتدا بر اساس ستون A (ستون نام) و سپس بر اساس ستون B (ستون تاریخ) مرتب کنید. سپس می توانید از کنترل فیلتر کشویی در بالای ستون D (ستون Tally) برای انتخاب تعداد فروش مورد نظر برای ماه مورد نظر (که به یاد داشته باشید در سلول B1 مشخص شده است) استفاده کنید. اگر مشخص کنید که فقط مقدار 1 نمایش داده شود، نام تمام افرادی که فقط یک بار در ماه خرید کرده اند را دارید. اگر 2 را انتخاب کنید، نام های نمایش داده شده کسانی هستند که دو بار خرید کرده اند و غیره.
