Härledning Månadsmedianvärde

October 13

John har en enorm arbetsblad som innehåller dagliga uppgifter som omfattar cirka sextio år. Han vill komma med en formel som beräknar medianvärdena för varje månad under den tidsperiod som omfattas av data.

Innan man föreslår en lösning, är det bäst att lägga ut vissa antaganden. Vid tillämpningen av detta tips, låt oss anta att det dagliga data i kolumnerna A och B. I kolumn A är de datum som är förknippade med data och i kolumn B är de faktiska datavärdena för varje av dessa datum. Vidare cellerna A1 och B1 innehåller rubriker för varje kolumn. Detta innebär att de faktiska uppgifter är ungefär i intervallet A2: B22000.

För att göra dina formler lättare att använda, bör du definiera namn för data i båda kolumnerna A och B. Välj datumintervall (t.ex. A2: A22000) och tilldela den ett namn som AllDates. Välj antal motsvarande data (till exempel B2: B22000) och använder samma process för att skapa ett namn som AllData.

Du kan använda matrisformler för att beräkna den faktiska medianvärdena. Detta handlar om att sätta upp en annan datatabell som innehåller median. Placera rubriker som "Month" i cell E1 och "Median" i cell F1. I cell E2 placera den första dagen i den första månaden av din datauppsättning, t.ex. 1940/01/01. I cell satte E3 ett datum som är en månad senare, såsom 1940/02/01. Välj dessa två celler (E2: E3) och dra fyllningshandtaget nedåt för antalet månader som du vill ha i datatabellen.

Om det inte finns några tomma i dina sextio år av data, ange sedan följande formel i cell F2:

= MEDIAN (IF (DATUM (ÅR (AllDates), MÅNAD (AllDates), 1) = E2, AllData))

Slutföra formeln genom att trycka Ctrl + Skift + Enter, som berättar Excel att detta är en matrisformel. Du kan sedan kopiera formeln i F2 i varje cell i kolumn F som har en motsvarande månad i kolumn E. Formeln analyserar datum i kolumn B, och om år och månad är lika med vad datum som du sätter i cell E2 , då median beräknas från alla motsvarande datapunkter.

Om det finns tomma i dina sextio år av data (ett datum i kolumn A utan motsvarande värde i kolumn B), då formeln behandlar ämnet som om det är ett nollvärde. Om du har ämnen, kan detta resultera i skeva medianer. För att komma runt detta, kan du använda en annan matrisformel som kontrollerar om och ignorerar eventuella tomma värden:

= MEDIAN (IF ((DATUM (ÅR (AllDates), MÅNAD (AllDates), 1) = E2) * ISNUMBER (AllData), AllData))

Det finns en varning till att använda matrisformler på detta sätt. Om du har sextio års data, med cirka 22.000 enskilda värden, så är det fortfarande en hel del månader: ca 720 av dem. Det innebär att du skapar 720 matrisformler, varje analys 22.000 datavärden för att komma fram till ett svar. Det är en hel del att beräkna händer, så kommer du att märka en avmattning i lyhördhet Excel när den beräknar kalkylbladet.

Om tröghet blir ett problem, då kan du titta på omorganisera dina ursprungliga data så att varje rad i kalkylbladet representerar en enskild månad. Kolumn A kunde innehålla månaden för raden (1940/01/01, 1940/02/01, 1940/03/01, etc.) och kolumnerna B: AF skulle vara dagar 1 till 31 för varje månad. De korsande celler i tabellen kan då innehålla datapunkt för varje dag i månaden, och du kan använda den MEDIAN funktionen i kolumn AG att beräkna medianen för varje månad. Detta resulterar fortfarande i 720 formler, men dessa är regelbundna formler att varje behöver bara behandla en månads uppgifter i stället för de matrisformler som behöver varje process sextio år av data. Resultatet är en mycket snabbare beräkningar.

Naturligtvis för de flesta människor tanken på att omorganisera en sådan enorm mängd data är tillräckligt för att hålla dig vaken på natten. Istället kan du ta en helt annan inställning till att analysera data. Detta tillvägagångssätt är möjligt eftersom en median är en mycket enkel statistisk funktion för att manuellt beräkna. Du sorterar helt enkelt dina data in och, om antalet poster i datamängden är udda, markerar mittpost. Om antalet objekt är ännu, då du tar medelvärdet av de två mittersta poster.

För att göra sig redo för att analysera data, det finns ett par saker att göra. Först kommer det att vara praktiskt att ha något sätt att unikt identifiera månaden i varje datapunkt. I cell C2 placera följande formel:

= 100 * År (A2) + Månad (A2)

Detta resulterar i en värden som 194.001, 194.002, 194.003, etc. lagras i kolumn C. Detta är den unika månaden värdet. Nu ska du sortera data efter kolumn C och därefter genom kolonn B. Båda typer ska vara i stigande ordning, så att du hamnar med dina data först sorterade efter år / månad och sedan av värde inom år / månad.

Därefter måste du lägga till delsummor till dina data. Välj delsummor från menyn Data, som visar Delsumma dialogrutan. Du vill lägga till en delsumma vid varje förändring i kolumn C. Funktionen använda är greve, och du vill lägga till delsumman till kolumn B. När du klickar på OK, du sluta med 720 delsummor, en för varje månad i data intervall, var och en visar en räkningen på hur många uppgifter objekt fanns i den månaden.

För att få median, lägga till en formel till cell D2:

IF (HÖGER (B2,5) = "Greven", IF (MOD (C2,2) = 1, INDIREKT ("B" & (RAD () - 1) C2 / 2 + 1/2), (INDIREKT ( "B" & (RAD () - 1) -C2 / 2) + INDIREKT ("B" & (RAD () - 1) -C2 / 2 + 1)) / 2), "")

Formeln undersöker vad som finns i cell B2, och om den innehåller ordet "Räkna", då den vet att detta är en delsumma rad. I så fall kontrollerar den för att se om räknevärdet i cell C2 är udda eller jämnt. Om det är märkligt, då INDIREKT funktion används för att greppa vad medianvärdet är för månaden. Om det är till och med, då de två mittersta värdena för månaden läggs ihop och delas i hälften.

Resultatet är att du nu har medianvärden för varje månad, i samma rad som dina delsummor. Således kan du komprimera konturerna för data (klicka på plustecken i konturerna området till vänster i dina uppgifter), så att du bara visa dessa delsummor rader.

ExcelTips är din källa för kostnadseffektiv Microsoft Excel utbildning. Detta tips (3086) gäller för Microsoft Excel 97, 2000, 2002, och 2003. Du kan hitta en version av detta tips för menyfliksområdet i Excel (Excel 2007 och senare) här: härledning Månadsmedianvärden.