جان یک کاربرگ عظیم دارد که شامل داده های روزانه حدود شصت سال است. او می خواهد فرمولی ارائه دهد که مقادیر میانه را برای هر ماه در دوره زمانی تحت پوشش داده ها محاسبه کند.
قبل از ارائه راه حل، بهتر است برخی از فرضیات را مطرح کنید. برای اهداف این نکته، اجازه دهید فرض کنیم که داده های روزانه در ستون های A و B هستند. در ستون A تاریخ های مرتبط با داده ها و در ستون B مقادیر واقعی داده ها برای هر یک از آن تاریخ ها هستند. علاوه بر این، سلول های A1 و B1 حاوی سرفصل هایی برای هر ستون هستند. این بدان معنی است که داده های واقعی تقریباً در محدوده A2:B22000 هستند.
برای سهولت استفاده از فرمول های خود، باید نام هایی را برای داده ها در هر دو ستون A و B تعریف کنید. محدوده تاریخ ها را انتخاب کنید (مثلا A2:A22000) و نامی مانند AllDates به آن اختصاص دهید. محدوده داده های مربوطه را انتخاب کنید (به عنوان مثال B2:B22000) و از همان فرآیند برای ایجاد نامی مانند AllData استفاده کنید.
می توانید از فرمول های آرایه برای محاسبه مقادیر میانه واقعی استفاده کنید. این شامل تنظیم جدول داده دیگری برای حاوی میانه ها است. عناوینی مانند "ماه" را در سلول E1 و "میانگین" را در سلول F1 قرار دهید. در سلول E2 اولین روز از اولین ماه مجموعه داده های خود را قرار دهید، مانند 1/1/1940. در سلول E3 تاریخ یک ماه بعد را قرار دهید، مانند 1/2/1940. این دو سلول (E2:E3) را انتخاب کنید و دسته پر را برای تعداد ماه هایی که در جدول داده می خواهید به سمت پایین بکشید.
اگر در شصت سال اطلاعات شما هیچ جای خالی وجود ندارد، فرمول زیر را در سلول F2 وارد کنید:
=MEDIAN(IF(DATE(YEAR(AllDates),MONTH(AllDates),1)=E2,AllData))
فرمول را با فشار دادن Ctrl+Shift+Enter نهایی کنید ، که به اکسل می گوید که این یک فرمول آرایه است. سپس می توانید فرمول F2 را در هر یک از سلول های ستون F که یک ماه متناظر در ستون E دارد کپی کنید. ، سپس میانه از تمام نقاط داده مربوطه محاسبه می شود.
اگر در شصت سال داده های شما خالی وجود داشته باشد (تاریخی در ستون A بدون مقدار متناظر در ستون B)، پس فرمول با آن خالی به گونه ای برخورد می کند که گویی مقدار صفر است. اگر جای خالی دارید، این ممکن است منجر به میانه های کج شود. برای دور زدن این موضوع، می توانید از فرمول آرایه دیگری استفاده کنید که مقادیر خالی را بررسی می کند و نادیده می گیرد:
=MEDIAN(IF((DATE(YEAR(AllDates),MONTH(AllDates),1)=E2)*ISNUMBER(AllData),AllData))
یک اخطار برای استفاده از فرمول های آرایه در این روش وجود دارد. اگر شصت سال داده دارید، با تقریباً 22000 مقدار فردی، آنگاه هنوز ماه های زیادی است: حدود 720 مورد. این بدان معناست که شما در حال ایجاد 720 فرمول آرایه هستید که هر کدام 22000 مقدار داده را تجزیه و تحلیل می کنند تا به یک پاسخ برسید. این محاسبات زیادی در حال انجام است، بنابراین هر زمان که اکسل کاربرگ را دوباره محاسبه می کند، متوجه کاهش سرعت پاسخ دهی خواهید شد.
اگر کندی به یک مشکل تبدیل می شود، می توانید به سازماندهی مجدد داده های اصلی خود نگاه کنید تا هر ردیف از کاربرگ یک ماه جداگانه را نشان دهد. ستون A می تواند شامل ماه برای ردیف باشد (1/1/1940، 2/1/1940، 3/1/1940، و غیره) و ستون های B:AF روزهای 1 تا 31 برای هر ماه خواهد بود. سلول های متقاطع در جدول می توانند حاوی نقطه داده برای هر روز در ماه باشند و می توانید از تابع MEDIAN در ستون AG برای محاسبه میانگین هر ماه استفاده کنید. این هنوز به 720 فرمول منجر می شود، اما این فرمول ها فرمول های معمولی هستند که هرکدام به جای فرمول های آرایه ای که برای پردازش شصت سال داده نیاز دارند، فقط به پردازش یک ماه داده نیاز دارند. نتیجه محاسبات بسیار سریعتر است.
البته، برای اکثر مردم ایده سازماندهی مجدد چنین حجم عظیمی از داده ها برای بیدار نگه داشتن شما در شب کافی است. در عوض، می توانید رویکردی کاملاً متفاوت برای تجزیه و تحلیل داده ها داشته باشید. این رویکرد ممکن است زیرا میانه یک تابع آماری بسیار آسان برای محاسبه دستی است. شما به سادگی مجموعه داده های خود را مرتب می کنید و اگر تعداد موارد موجود در مجموعه داده فرد باشد، مورد میانی را انتخاب کنید. اگر تعداد آیتم ها زوج باشد، میانگین دو مورد میانی را می گیرید.
برای آماده شدن برای تجزیه و تحلیل داده ها، چند کار وجود دارد. اول، داشتن راهی برای شناسایی منحصر به فرد ماه هر نقطه داده مفید خواهد بود. در سلول C2 فرمول زیر را قرار دهید:
=100*Year(A2)+Month(A2)
این باعث می شود مقداری مانند 194001، 194002، 194003 و غیره در ستون C ذخیره شود. این مقدار ماه منحصر به فرد است. اکنون باید داده ها را بر اساس ستون C و سپس بر اساس ستون B مرتب کنید. هر دو مرتبه باید به ترتیب صعودی باشند، به طوری که در نهایت داده های خود را ابتدا بر اساس سال/ماه و سپس بر اساس مقدار در سال/ماه مرتب کنید.
در مرحله بعد باید جمع های فرعی را به داده های خود اضافه کنید. Subtotals را از منوی Data انتخاب کنید که کادر محاوره ای Subtotal را نشان می دهد. شما می خواهید در هر تغییر در ستون C یک جمع فرعی اضافه کنید. تابع مورد استفاده Count است و می خواهید جمع فرعی را به ستون B اضافه کنید. وقتی روی OK کلیک می کنید، در نهایت 720 جمع فرعی خواهید داشت، یک عدد برای هر ماه در داده ها. محدوده، هر یک تعداد موارد داده در آن ماه را نشان می دهد.
برای بدست آوردن میانه ها، یک فرمول به سلول D2 اضافه کنید:
IF(RIGHT(B2,5)="Count", IF(MOD(C2,2)=1, INDIRECT("B"&(ROW()-1)-C2/2+1/2), (INDIRECT("B"&(ROW()-1)-C2/2)+INDIRECT("B"&(ROW()-1)-C2/2+1))/2), "")
فرمول آنچه در سلول B2 وجود دارد را بررسی می کند، و اگر حاوی کلمه "Count" باشد، می داند که این یک ردیف فرعی است. در آن صورت، بررسی می کند که آیا تعداد در سلول C2 فرد است یا زوج. اگر فرد باشد، از تابع INDIRECT برای گرفتن هر مقدار میانه ماه استفاده می شود. اگر زوج باشد، دو مقدار وسط ماه با هم جمع شده و به نصف تقسیم می شوند.
نتیجه این است که شما اکنون مقادیر میانه را برای هر ماه، در ردیف مجموع فرعی خود دارید. بنابراین، می توانید طرح کلی داده ها را جمع کنید (روی علامت های مثبت در ناحیه طرح کلی در سمت چپ داده های خود کلیک کنید) تا فقط آن ردیف های فرعی را نشان دهید.
