فرض کنید یک کاربرگ بزرگ دارید که شامل تمام قرائت های بارندگی برای یک منطقه معین در صد سال گذشته یا بیشتر است. در سلول های A2:A42733، تاریخ ها، 1 ژانویه 1903 تا 31 دسامبر 2019 را دارید. در سلول های B2:B42733، اندازه گیری های هر تاریخ را دارید. علاوه بر این، برخی از اندازه گیری ها می توانند صفر (اگر بارندگی در آن روز وجود نداشته باشد) یا خالی (اگر در آن روز خاص اندازه گیری نشده باشد) باشد. با تمام این اطلاعات، شما می خواهید میانگین بارندگی تاریخی را برای هر روز معین از سال محاسبه کنید.
یک راه حل شامل استفاده از فرمول های آرایه است که در اینجا توضیح داده شده است:
=SUM((MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2))*Rainfall)
=SUM((MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2))*(Rainfall<>""))
=IF(F2<>0,E2/F2,"")
- تمام سلول های ستون A که حاوی تاریخ هستند را انتخاب کنید و به این محدوده نام تاریخ ها را اختصاص دهید.
- تمام سلول های مربوطه را در ستون B انتخاب کنید. این ها حاوی داده های بارندگی برای تاریخ های ستون A هستند. نام Rainfall را به این محدوده اختصاص دهید.
- در ستون D، با شروع سلول D2، تمام روزهای سال را قرار دهید. شما باید با D2 تا D366 پر شده با تاریخ مواجه شوید.
- در سلول E2، فرمول آرایه زیر را وارد کنید (فرمول را با فشار دادن Shift+Ctrl+Enter خاتمه دهید ). نتیجه فرمول مجموع تمام سلول های محدوده Rainfall برای تاریخ مشخص شده در سلول D2 است.
- در سلول F2، فرمول آرایه زیر را وارد کنید (فرمول را با فشار دادن Shift+Ctrl+Enter خاتمه دهید ). نتیجه فرمول تعداد سلول هایی در محدوده Rainfall برای تاریخ سلول D2 است که دارای یک مقدار هستند.
- در سلول G2 فرمول معمولی زیر را وارد کنید. این میانگین تاریخ شما در سلول D2 است.
- محدوده E2:G2 را انتخاب کنید و تمام تاریخ های نشان داده شده در ستون D را کپی کنید.
این روش کار می کند، اما زمان زیادی برای محاسبه طول می کشد. این به این دلیل است که شما به طور موثر 822 فرمول آرایه را وارد کرده اید که هر کدام بیش از 42000 سلول را بررسی می کند. این کار زیادی است، و در نتیجه ممکن است به نظر برسد که دستگاه شما پس از تکمیل مرحله 7 "آویزان" شده است. فقط کمی طول می کشد تا محاسبات کامل شود.
برای کاهش تعداد محاسباتی که باید انجام شوند، می توانید از تغییراتی در مراحل بالا استفاده کنید. مراحل 1 تا 3 را همانطور که ذکر شد دنبال کنید و سپس فرمول آرایه زیر را در سلول E2 قرار دهید:
=AVERAGE(IF((Dates<>0)*(Rainfall<>0)*(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))
سپس می توانید فرمول را برای تمام تاریخ های نشان داده شده در ستون D کپی کنید. نتیجه این فرمول میانگین واقعی بارندگی است، همان چیزی که در ستون G در روش قبلی نشان داده شده بود.
با خلاص شدن از شر تمام جدولی که میانگین ها را برای هر روز از سال محاسبه می کند، می توانید سربار محاسبه را حتی بیشتر کاهش دهید. با توجه به تاریخ و بارندگی در ستون A و B، این مراحل را دنبال کنید:
- تمام سلول های ستون A که حاوی تاریخ هستند را انتخاب کنید و به این محدوده نام تاریخ ها را اختصاص دهید.
- تمام سلول های مربوطه را در ستون B انتخاب کنید. این ها حاوی داده های بارندگی برای تاریخ های ستون A هستند. نام Rainfall را به این محدوده اختصاص دهید.
- در سلول D2، تاریخی را که می خواهید میانگین بارندگی را بررسی کنید، قرار دهید. (سال مهم نیست، فقط ماه و روز در محاسبه استفاده می شود.)
- فرمول زیر را در سلول E2 وارد کنید:
=AVERAGE(IF((Dates<>0)*(Rainfall<>0)*(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))
خودشه. اکنون می توانید تاریخ سلول D2 را به دلخواه تغییر دهید و سلول E2 همیشه میانگین بارندگی آن تاریخ را نشان می دهد. فرمول سلول E2 همان فرمول مورد استفاده در آخرین رویکرد است. تفاوت این است که شما آن را برای تمام روزهای یک سال محاسبه نمی کنید و بنابراین محاسبه بسیار سریعتر انجام می شود.
روش دیگر شامل استفاده از قابلیت های فیلتر اکسل است. قبل از اینکه بتوانید از آنها به درستی استفاده کنید، باید ستونی ایجاد کنید که فقط ماه و روز را برای هر تاریخ در داده های شما نشان دهد. از این فرمول در سلول C2 استفاده کنید:
=MONTH(A2) & "-" & DAY(A2)
اکنون، AutoFiltering را روشن کنید (نمایش تب Data بر روی نوار | Sort & Filter group | Filter) و از لیست کشویی در بالای ستون جدید برای انتخاب تاریخی که میانگین می خواهید استفاده کنید. سپس از فرمول زیر که در هر سلول دلخواه قرار می گیرد برای نشان دادن میانگین بارندگی برای تاریخ انتخابی استفاده می کنید:
=SUBTOTAL(1,B:B)
