Utlede Månedlig medianverdier

John har et stort regneark som inkluderer daglige data som dekker omtrent seksti år. Han ønsker å komme opp med en formel som vil beregne medianverdier for hver måned i perioden som dekkes av dataene.

Før den foreslår en løsning, er det best å legge ut noen forutsetninger. For formålene med denne spiss, la oss anta at den daglige data er i kolonnene A og B. I kolonne A er datoene i forbindelse med dataene og i kolonne B er de faktiske dataverdier for hver av disse datoer. Videre celler A1 og B1 inneholder posisjonene for hver kolonne. Dette betyr at selve dataene er tilnærmet i området A2: B22000.

Å lage formler enklere å bruke, bør du definere navn for dataene i begge kolonnene A og B. Velg utvalget av datoer (for eksempel A2: A22000) og tildel den et navn som AllDates. Velg rekke tilsvarende data (for eksempel B2: B22000) og bruke den samme prosessen for å skape et navn som AllData.

Du kan bruke matriseformler for å beregne de faktiske medianverdier. Dette innebærer å sette opp en annen datatabellen for å inneholde medianer. Plasser overskrifter som "Month" i celle E1 og "Median" i celle F1. I celle E2 plassere den første dagen i den første måneden av datasettet, for eksempel 1/1/1940. I celle E3 sette en dato som er en måned senere, for eksempel 2/1/1940. Velge disse to celler (E2: E3) og drar fyllhåndtaket nedover for det antall måneder du ønsker i datatabellen.

Hvis det ikke er noen blanke i dine seksti år med data, deretter inn følgende formel i celle F2:

= MEDIAN (IF (DATO (ÅR (AllDates), MÅNED (AllDates), 1) = E2, AllData))

Ferdigstille formel ved å trykke Ctrl + Shift + Enter, som forteller Excel at dette er en matriseformel. Du kan deretter kopiere formelen i F2 i hver av cellene i kolonne F som har en tilsvarende måned i kolonne E. Formelen analyserer datoene i kolonne B, og hvis det år og måned er lik uansett dato du putter i celle E2 Da median beregnes ut fra alle tilsvarende datapunkter.

Hvis det er emnene i de seksti år data (en dato i kolonne A med ingen tilsvarende verdi i kolonne B), så formelen behandler emnet ut som om det er en nullverdi. Hvis du har blanks, kan dette resultere i skjeve medianverdier. For å komme rundt dette, kan du bruke en annen matriseformel som sjekker for og ignorerer eventuelle tomme verdier:

= MEDIAN (IF ((DATO (ÅR (AllDates), MÅNED (AllDates), 1) = E2) * ISNUMBER (AllData), AllData))

Det er en påminnelse til å bruke matriseformler på denne måten. Hvis du har seksti år med data, med ca 22.000 individuelle verdier, så det er fortsatt mange måneder: ca 720 av dem. Det betyr at du oppretter 720 matriseformler, hver analysere 22.000 dataverdier å komme frem til et svar. Det er mye å beregne skjer, så du vil merke en nedbremsing i respons fra Excel når det beregner regnearket.

Hvis treghet blir et problem, så du kan se på å omorganisere de opprinnelige dataene, slik at hver rad i regnearket representerer en individuell måned. Kolonne A kan inneholde måneden for raden (1/1/1940, 2/1/1940, 3/1/1940, etc.) og kolonner B: AF ville være dag 1 til 31 for hver måned. Kryssende celler i tabellen kan da inneholde datapunkt for hver dag i måneden, og du kan bruke den MEDIAN-funksjonen i kolonne AG for å beregne median for hver måned. Dette fortsatt resulterer i 720 formler, men disse er vanlige formler som hver eneste trenger å behandle en måned er verdt av data i stedet for de matriseformler som må hver prosess seksti år med data. Resultatet er mye raskere beregninger.

Selvfølgelig, for folk flest ideen om å omorganisere slik en enorm mengde data er nok til å holde deg våken om natten. I stedet kan du ta en helt annen tilnærming til å analysere dataene. Denne tilnærmingen er mulig fordi en median er en meget enkel statistisk funksjon å beregne manuelt. Du bare sortere dataene satt, og hvis antall elementer i datasettet er merkelig, velger du midt element. Dersom antall elementer er enda, så ta gjennomsnittet av de to midterste elementene.

Å bli klar til å analysere dataene, er det et par ting å gjøre. Først vil det være nyttig å ha noen måte å identifisere måned hvert datapunkt. I celle C2 plassere følgende formel:

= 100 * Year (A2) + Måned (A2)

Dette resulterer i en verdi som 194001, 194002, 194003, etc. som blir lagret i kolonne C. Dette er den unike måned verdi. Nå, bør du sortere dataene etter kolonne C og deretter ved kolonne B. Begge typer bør være i stigende rekkefølge, slik at du ender opp med dataene først sortert etter år / måned og deretter av verdi innenfor år / måned.

Neste må du legge til delsummer til dine data. Velg Delsummer fra Data-menyen, som viser Delsum dialogboksen. Du ønsker å legge til en delsum ved hver endring i kolonne C. Funksjonen å bruke er Count, og du vil legge til delsum til kolonne B. Når du klikker OK, ender du opp med 720 delsummer, en for hver måned i data rekkevidde, hver og en som viser en opptelling av hvor mange dataelementer som det var i den måneden.

Å få medianer, legge til en formel til celle D2:

IF (HØYRE (B2,5) = "Greven", IF (MOD (C2,2) = 1, INDIREKTE ("B" & (ROW () - 1) C2 / 2 + 1/2), (INDIREKTE ( "B" & (RAD () - 1) C2 / 2) + INDIREKTE ("B" & (RAD () - 1) C2 / 2 + 1)) / 2), "")

Formelen undersøker hva som er i celle B2, og hvis det inneholder ordet "Count", så vet den at dette er en delsum rad. I så fall, sjekker den for å se om greven i celle C2 er oddetall eller partall. Hvis det er merkelig, da den INDIREKTE-funksjonen brukes til å hente hva medianverdien er for måneden. Hvis det er enda, vil de to midterste verdiene for måneden blir lagt sammen og delt i to.

Resultatet er at du nå har medianverdier for hver måned, i samme rad som dine delsummer. Dermed kan du kollapse omrisset for dataene (klikk plusstegnene i disposisjonsområdet til venstre for dine data), slik at du bare vise disse delsumlinjer.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (3086) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: utledning Månedlig medianverdier.