راب هر روز آمار هر کاری را که انجام می دهد نگه می دارد. به عنوان مثال، او تعداد دقیق ساعت ها و دقیقه های صرف شده برای هر کار را با وارد کردن زمان شروع (ستون B) و زمان پایان (ستون C) برای هر کار در قالب: 05/11/20 11:25 صبح دنبال می کند. در یک ستون جداگانه (ستون G) تاریخ هر روز از ماه را دارد. در سمت راست هر تاریخ (در ستون H) او می خواهد نشان دهد که در هر یک از تاریخ های تقویم چند کار انجام داده است. راب تعجب می کند که از چه فرمولی برای محاسبه این تعداد استفاده می کند.
این کار آنقدرها هم که در ابتدا به نظر می رسد آسان نیست. دلیل آن به دلیل نحوه ذخیره زمان شروع و پایان است. ستون B شامل تاریخ و زمان شروع در همان ستون، با قالب "05/11/20 11:25 am" و ستون C شامل تاریخ و زمان پایان در همان قالب است. ممکن است فکر کنید می توانید از تابع COUNTIF در ستون G به این صورت استفاده کنید:
=COUNTIF(C$2:C$1000,G2)
با این حال، این کار نخواهد کرد. دلیل آن ساده است - ستون G (سلول G2، در این مثال) حاوی تاریخ است، بدون زمان. به عنوان مثال، ممکن است حاوی تاریخ 05/11/20 باشد. وقتی این با سلول C2 مقایسه می شود، که ممکن است شامل 05/11/20 11:25 صبح باشد، آنها یکسان نیستند. به یاد داشته باشید که اکسل مقایسه را بر اساس تاریخ و شماره سریال زمانی ذخیره شده در هر سلول انجام می دهد. با توجه به مقادیر مثالی که ذکر شد، سلول G2 دارای شماره سریال 39217 و سلول C2 دارای شماره سریال 39217.47569 خواهد بود. از آنجایی که این دو مقدار یکسان نیستند، COUNTIF آنها را برابر حساب نمی کند.
بدیهی است که یک راه حل اضافه کردن ستون دیگری است که فقط حاوی تاریخ پایان هر کار باشد، بدون زمان. سپس می‌توانید از تابع COUNTIF در فرمول خود استفاده کنید زیرا به اصطلاح «سیب را با سیب» مقایسه می‌کنید. اما راه حلی وجود دارد که نیازی به استفاده از یک نتیجه میانی در یک ستون جدید ندارد. این رویکرد از تابع COUNTIFS استفاده می کند که دو معیار را برای تمام سلول های یک محدوده اعمال می کند:
=COUNTIFS(C$2:C$1000,">=" & G2,C$2:C$1000,"
این فرمول از دو معیار استفاده می کند که به طور موثر می بیند که آیا آنچه در سلول های ستون C وجود دارد بزرگتر یا مساوی با مقدار G2 و در عین حال کمتر از G2+1 است یا خیر. به عبارت دیگر، همه چیزهایی را که در طول روز مشخص شده در G2 رخ می دهد، شمارش می کند.
روش دیگر این است که مطمئن شوید مقدار صحیح هر آنچه در ستون C است با تاریخ‌های ذخیره شده در ستون G مقایسه می‌شود. این کار را می‌توان با استفاده از تابع SUMPRODUCT به این صورت انجام داد:
=SUMPRODUCT((INT(C$2:C$1000)=G2)*1)
عدد صحیح هر یک از مقادیر در محدوده C2:C1000 با تاریخ در G2 مقایسه می شود و آرایه ای از مقادیر True و False را ارائه می دهد. با ضرب هر یک از اینها در 1 مقادیر True و False به ترتیب به مقادیر 1 و 0 تبدیل می شوند. سپس فرمول این محصولات را جمع می کند و تعداد مورد نظر را به دست می دهد.
اگر ترجیح می دهید، می توانید از فرمول زیر نیز استفاده کنید، که اساساً همان کار را با استفاده از SUMPRODUCT انجام می دهد:
=SUMPRODUCT(--(INT(C$2:C$1000)=G2))
یکی از مزایای استفاده از تابع COUNTIFS یا تابع SUMPRODUCT برای تعیین نتیجه این است که شما نیازی به استفاده از فرمول آرایه ندارید. با این حال، برخی افراد ترجیح می دهند از فرمول های آرایه استفاده کنند. اگر دوست دارید از آنها استفاده کنید، می توانید از یکی از موارد زیر استفاده کنید:
=COUNT(IF(INT(C$2:C$1000)=INT(G2),1,FALSE))
=SUM(IF(ROUNDDOWN(C$2:C$1000,0)=G2,1,0))
به یاد داشته باشید که فرمول های آرایه باید با استفاده از Ctrl+Shift+Enter در یک سلول وارد شوند .
