اگر محدوده سلولی دارید که با تاریخ های تصادفی و مرتب نشده پر شده است، ممکن است بخواهید بفهمید که چه تعداد از این تاریخ ها بین تاریخ شروع و پایان قرار می گیرند. به عنوان مثال، اگر B1:B101 حاوی تاریخ های تصادفی، سلول E1 حاوی تاریخ شروع، و سلول E2 حاوی تاریخ پایان باشد، ممکن است بخواهید بدانید که از چه نوع فرمولی می توانید در سلول E4 برای برگرداندن تعداد تاریخ ها در B1 استفاده کنید. :B101 که بین E1 و E2 قرار می گیرند.
در واقع چندین راه مختلف وجود دارد که می توانید به یک راه حل برسید. البته اولین مورد استفاده از تابع COUNTIFS به این صورت است:
=COUNTIFS(B1:B101,">="&E1,B1:B101,"
این فرمول تعداد تاریخ های موجود در محدوده را برمی گرداند، همانطور که شما می خواهید. با این حال، مشکل COUNTIFS این است که یک افزودنی نسبتاً جدید به اکسل است که برای اولین بار در اکسل 2013 در دسترس قرار گرفت. سپس شما به یک راه حل متفاوت نیاز دارید. تعادل رویکردهای موجود در این نکته با تمام نسخه های اکسل کار می کند، اما مخصوصاً برای نسخه های قدیمی (قبل از ۲۰۱۳) مناسب است.
اولین رویکرد این است که به سادگی یک فرمول در هر سلول در سمت راست تاریخ های ستون B اضافه کنید و اگر تاریخ بین E1 و E2 باشد، فرمول 1 یا اگر نه 0 است. سپس می توانید ستون را جمع کنید تا تعداد مورد نظر را بدست آورید. از فرمول زیر در هر سلول سمت راست تاریخ ها استفاده می کنید:
=IF(AND(B1>=E$1,B1
این فرمول خاص به سلول C1 می رود و سپس می تواند به سلول های C2 تا C101 کپی شود. سپس، در سلول E4، تنها کاری که باید انجام دهید این است که از فرمول زیر استفاده کنید:
=SUM(C1:C101)
البته اشکالی که در این مورد وجود دارد این است که مقادیر اضافی در C1:C101 می تواند ظاهر یک کاربرگ با دقت ساخته شده را خراب کند. خوشبختانه راه هایی برای یافتن نتیجه مناسب بدون نیاز به استفاده از مقدار میانی وجود دارد.
یکی از راه ها استفاده از فرمول آرایه است. فرمول زیر که در سلول E4 قرار داده شده است، این کار را انجام می دهد. تنها کاری که باید انجام دهید این است که به یاد داشته باشید که فرمول را با استفاده از Shift+Ctrl+Enter وارد کنید . (این به اکسل نشان می دهد که شما یک فرمول آرایه را وارد می کنید.)
=COUNT(IF((B1:B101>$E$1)*(B1:B101
اگر ترجیح می دهید از فرمول آرایه استفاده نکنید، می توانید از فرمول استاندارد زیر COUNTIF در E4 استفاده کنید:
=-COUNT(B1:B101)+COUNTIF(B1:B101,">"&E1)+COUNTIF(B1:B101,"
وقتی این فرمول را وارد می کنید، اکسل فرض می کند که مقدار تاریخ را برمی گردانید و بنابراین سلول را به عنوان یک تاریخ قالب بندی می کند. تنها کاری که برای اصلاح این مورد باید انجام دهید استفاده از Format | است سلول برای قالب بندی سلول با استفاده از چیزی غیر از قالب تاریخ، مانند عمومی.
این فرمول با شمارش تمام تاریخ هایی که بعد از تاریخ اولیه هستند به اضافه تمام تاریخ هایی که قبل از تاریخ دیرهنگام هستند کار می کند. این اساساً همه تاریخ ها را یک بار می شمارد و انتخاب مورد نظر را دوبار شمارش می کند. با کم کردن تعداد تاریخ های موجود در محدوده (در ابتدای فرمول)، فرمول به طور موثر همه را حذف می کند به جز نتیجه دلخواه.
در صورت تمایل، می توانید از تابع DCOUNT نیز برای برگرداندن تعداد مورد نظر استفاده کنید. با این حال، این فقط کمی بیشتر درگیر است. برخلاف راه حل های قبلی، DCOUNT به استفاده از محدوده های نام گذاری شده متکی است. این مراحل را دنبال کنید:
- یک ردیف خالی در بالای داده های خود قرار دهید، که همه چیز را یک سلول به پایین فشار می دهد.
- با قرار دادن تاریخ هایتان در سلول های B2:B102، سلول B1 را انتخاب کنید و یک برچسب مانند MyDates را در سلول قرار دهید.
- سلول B1 را به دلخواه قالب بندی کنید.
- کل محدوده، از جمله برچسب (سلول های B1:B102) را انتخاب کنید.
- با استفاده از Name Box (درست بالای سرصفحه های ردیف و سمت چپ سرصفحه های ستون)، یک نام برای محدوده تعریف کنید. نام باید با نامی که در مرحله 2 استفاده کردید (MyDates) باشد.
- در سلول های D1 و E1، نام برچسب خود (MyDates) را قرار دهید.
- در سلول D2 یک علامت بزرگتر از و تاریخ پایین را برای محدوده تاریخ خود وارد کنید، مانند ">4/1/2002" (بدون علامت نقل قول).
- در سلول E2 یک علامت کمتر از و تاریخ بالایی را برای محدوده تاریخ خود وارد کنید، مانند "<6/1/2002" (بدون علامت نقل قول).</li>
- در سلول E4 فرمول زیر را وارد کنید:
=DCOUNT(MyDates,1,D1:E2)
تابع DCOUNT از معیارهایی که در D1:E2 مشخص کرده اید استفاده می کند تا محدوده داده های تعریف شده به عنوان MyDates را بررسی کند و تعداد سلول هایی را که این معیارها را برآورده می کنند برگرداند.
