Het berekenen van gemiddelden op datum

Stel dat je een enorm werkblad dat alle regenmetingen voor een bepaalde locatie voor de afgelopen honderd jaar of zo bevat hebben. In de cellen A2: A37987 heb je de data, 1 januari 1903 tot en met 31 december 2006. In de cellen B2: B37987 u de metingen voor elke datum. Verder kunnen sommige van de metingen kan nul zijn (als er geen regen gedurende de dag) of leeg (indien geen lezing genomen die dag). Met al deze informatie, u wilt de gemiddelde historische neerslag te berekenen voor een bepaalde dag van het jaar.

Een oplossing omvat het gebruik van matrixformules, zoals hier beschreven:

  1. Selecteer alle cellen in kolom A die data bevatten, en wijs deze reeks de naam Data.
  2. Selecteer alle cellen in kolom B die neerslag gegevens bevatten, en wijs deze reeks de naam Rainfall.
  3. In kolom D, te beginnen in cel D2, plaatst alle dagen van het jaar. Je moet eindigen met D2 via D366 gevuld met data.
  4. In cel E2, voert u de volgende matrixformule (beëindigen van de formule door te drukken op Shift + Ctrl + Enter). Het resultaat van de formule is de som van alle cellen in de Neerslag traject van de in cel D2 datum.

= SOM ((MAAND (Data) = MAAND (D2)) * (DAY (Data) = DAY (D2)) * Neerslag)

  • In cel F2, voert u de volgende matrixformule (beëindigen van de formule door te drukken op Shift + Ctrl + Enter). Het resultaat van de formule is het aantal cellen in de Neerslag traject van de datum in cel D2, die een waarde in hen.

= SOM ((MAAND (Data) = MAAND (D2)) * (DAY (Data) = DAY (D2)) * (Neerslag <> ""))

  • In cel G2, voert u de volgende reguliere formule. Dit is uw gemiddelde voor de datum in cel D2.

= IF (F2 <> 0, E2 / F2, "")

  • Selecteer het bereik E2: G2 en kopieer naar beneden voor alle volgens de in kolom D. data

Deze aanpak werkt, maar het duurt een tijdje om te berekenen. Dit komt omdat je effectief ingevoerd 730 matrixformules, elke controle meer dan 37.000 cellen. Dit is een hoop werk, en dus kan het lijken alsof uw machine is "opgehangen" nadat u stap 7. Het is niet opgehangen; het zal gewoon neem het een tijdje om de berekeningen te voltooien.

Om het aantal berekeningen die uitgevoerd moeten worden afnemen, kan een variant gebruiken op de bovenstaande stappen. Volg de stappen 1 tot en met 3, zoals opgemerkt, en plaats dan de volgende matrixformule in cel E2:

= GEMIDDELDE (ALS (ISNUMBER (Data) * ISNUMBER (Neerslag) *
(MAAND (Data) = MAAND (D2)) * (DAY (Data) = DAY (D2)), Neerslag))

U kunt vervolgens kopiëren van de formule naar beneden voor alle volgens de in kolom D. data Het resultaat van deze formule is de werkelijke gemiddelde neerslag, hetzelfde als was aangetoond in kolom G in de vorige aanpak.

Deze formule werkt als gevolg van de manier waarop Booleaanse rekenkundige werkt in Excel. De ISGETAL functie retourneert Waar of niet waar, en de vergelijkingen (maand en dag) terug Waar of Onwaar. Deze resultaten worden allemaal vermenigvuldigd tegen elkaar, waardoor True indien alle afzonderlijke tests True. Alleen als ze zijn allemaal True zal het gemiddelde van de Regen voor die bepaalde datum worden berekend.

U kunt de berekening overhead nog verder te verminderen door simpelweg het wegwerken van al de tabel die de gemiddelden berekent voor elke dag van het jaar. Met uw data en regenval in de kolommen A en B, als volgt te werk:

  1. Selecteer alle cellen in kolom A die data bevatten en wijs deze reeks de naam Data.
  2. Selecteer alle cellen in kolom B die neerslag data bevatten en wijs deze reeks de naam Rainfall.
  3. In cel D2, plaatst u de datum waarvoor u de gemiddelde neerslag controleren. (Het jaar is niet belangrijk, alleen de maand en dag worden gebruikt in de berekening.)
  4. Voer de volgende formule in cel E2:

= GEMIDDELDE (ALS (ISNUMBER (Data) * ISNUMBER (Neerslag) *
(MAAND (Data) = MAAND (D2)) * (DAY (Data) = DAY (D2)), Neerslag))

Dat is het. Nu, kunt u de datum te wijzigen in cel D2 zoals gewenst, en cel E2 zal altijd geven de gemiddelde neerslag voor die datum. De formule in cel E2 is dezelfde als de formule in de laatste benadering; het verschil is dat je het niet te berekenen voor alle dagen in een jaar, en dus de berekening is veel sneller gedaan.

Een andere benadering omvat het gebruik van Excel filtermogelijkheden. Voordat je ze goed kunt gebruiken, moet u echter een kolom die alleen de maand en dag toont voor elke datum in uw gegevens te maken. Gebruik deze formule in cel C2:

= MAAND (A2) & "-" & DAY (A2)

Nu, zet AutoFiltering (Data | Filter | AutoFilter) en gebruik maken van de drop-down lijst aan de bovenkant van de nieuwe kolom om de datum waarvoor u een gemiddelde selecteren. Vervolgens gebruikt u de volgende formule, geplaatst in een cel gewenst, om de gemiddelde neerslag voor de geselecteerde datum weer te geven:

= SUBTOTAAL (1, B: B)

ExcelTips is uw bron voor kosteneffectieve Microsoft Excel training. Deze tip (2350) is van toepassing op Microsoft Excel 97, 2000, 2002 en 2003. U kunt een versie van deze tip voor de ribbon-interface van Excel (Excel 2007 en later) hier vinden: het berekenen van gemiddelden op datum.