جورج یک کاربرگ دارد که شامل تاریخ ها (در ستون A) و مقادیر مرتبط با آن تاریخ ها (در ستون B) است. کاربرگ شامل مقادیر چندین سال گذشته است. او مایل است میانگین تمام مقادیر را برای یک ماه معین در یک سال مشخص محاسبه کند. برای مثال، جورج می‌خواهد میانگین تمام مقادیر را برای فوریه ۲۰۲۰ محاسبه کند.
چندین راه مختلف برای برخورد با این مشکل وجود دارد. یکی از راه ها ایجاد یک PivotTable بر اساس داده های خود است. (PivotTables برای جمع آوری و تجزیه و تحلیل حجم عظیمی از داده ها عالی هستند.) به راحتی می توانید فیلد مقدار را روی میانگین (به جای مجموع پیش فرض) تنظیم کنید و ستون Dates را بر اساس هر چیزی که می خواهید گروه بندی کنید.
اگر ترجیح می دهید از یک PivotTable استفاده نکنید، هر تعداد فرمول وجود دارد که می توانید به کاربرگ خود اضافه کنید. به عنوان مثال، فرمول زیر از تابع SUMPRODUCT برای محاسبه میانگین استفاده می کند:
=SUMPRODUCT((MONTH(A2:A1000)=5)*(YEAR(A2:A1000)=2020)*(B2:B1000)) /
(SUMPRODUCT((MONTH(A2:A1000)=5)*(YEAR(A2:A1000)=2020)*1))
این فرمول فرض می‌کند که تاریخ‌ها و مقادیر شما از ردیف 2 شروع می‌شوند (برای اینکه عناوین وجود داشته باشند) و از ردیف 1000 عبور نمی‌کنند. اگر در داده‌ها تاریخی وجود نداشته باشد که در ماه فوریه 2020 است، فرمول یک #DIV/0 را برمی‌گرداند. ! خطا
روش دیگر استفاده از فرمول آرایه است، مانند موارد زیر:
=AVERAGE(IF((MONTH(A2:A1000)=5)*(YEAR(A2:A1000)=2020),B2:B1000))
این رویکرد کوتاه‌تر از فرمول SUMPRODUCT است، اما باید به خاطر داشته باشید که هنگام وارد کردن فرمول، Ctrl+Shift+Enter را نگه دارید. همچنین اگر داده ای برای ماه و سال مورد نظر وجود نداشته باشد، خطای تقسیم بر صفر را دریافت خواهید کرد.
روش دیگر استفاده از یکی از توابع پایگاه داده اکسل، DAVERAGE است. تنها کاری که باید انجام دهید این است که جدول معیارهایی را تنظیم کنید که آنچه را که به دنبال آن هستید مشخص کند. برای مثال، فرض کنید که سرفصل‌های ستون‌ها چیزی اصلی هستند، مانند تاریخ (سلول A1) و مقدار (سلول B1). می توانید یک جدول معیارها را در مکان دیگری تنظیم کنید، مانند D1:E2. جدول می تواند به شکل زیر باشد:
Date Date
>1/31/20
جدول معیارها می گوید که شما می خواهید DAVERAGE از هر چیزی استفاده کند که در آن ستون Date دارای تاریخ بزرگتر از 1/31/20 و تاریخ کمتر از 3/1/20 باشد. این فرمول است:
=DAVERAGE(A1:B1000,"Value",D1:E2)
پارامتر اول پایگاه داده شما را تعریف می کند، پارامتر دوم نشان می دهد که می خواهید میانگین اطلاعات در ستون Value (ستون B) را به دست آورید، و پارامتر سوم به DAVERAGE می گوید که جدول معیارهای شما در کجا قرار دارد.
یکی از راه‌های بسیار آسان، اعمال فیلتر کردن تاریخ‌ها و استفاده از تابع SUBTOTAL است. فرمول زیر را در یک سلول وارد کنید:
=SUBTOTAL(101,B2:B1000)
یک سلول در محدوده داده خود را انتخاب کنید و داده های خود را فیلتر کنید (برگه Data روبان را نمایش دهید و روی ابزار Filter کلیک کنید). روی فلش فیلتر در بالای ستون A کلیک کنید و سپس Date Filters | را انتخاب کنید فیلتر سفارشی از لیست کشویی. اکسل کادر محاوره ای Custom AutoFilter را نمایش می دهد. (شکل 1 را ببینید.)
شکل 1. کادر محاوره ای Custom AutoFilter.
از کنترل های موجود در کادر محاوره ای استفاده کنید تا مشخص کنید که رکوردهای بزرگتر از 1/31/20 و کمتر از 3/1/20 را می خواهید. وقتی روی OK کلیک می‌کنید، فقط آن رکوردها در فوریه 2020 نمایش داده می‌شوند و فرمول فرعی میانگین آن رکوردهای قابل مشاهده را نشان می‌دهد.
با این حال، شاید ساده ترین روش استفاده از تابع AVERAGEIFS باشد. این به شما امکان می‌دهد میانگین برخی از مقادیر را بسته به اینکه آیا یک مقدار مرتبط (تاریخ، در این مورد) با معیارهایی که شما مشخص می‌کنید مطابقت دارد یا خیر محاسبه کنید. این فرمول چگونه به نظر می رسد:
=AVERAGEIFS(B2:B1000,A2:A1000,">31 Jan 2020",A2:A1000,"
توجه داشته باشید که این یک فرمول آرایه نیست و شما نیازی به تعریف جدول معیار برای معیارهای خود ندارید - آنها مستقیماً در فرمول ساخته می شوند.
