Általános képlet
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Összegzés
Az adatok havi összesítéséhez, év figyelmen kívül hagyásával, használhatja a SUMPRODUCT és a MONTH függvényeken alapuló képletet. A bemutatott példában a H6 képlete a következő:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Ennek eredménye az összes márciusi értékesítés, az évet figyelmen kívül hagyva.
Magyarázat
Ez az adatkészlet több mint 2900 rekordot tartalmaz, és a fenti képlet két megnevezett tartományt használ:
dates = B5:B2932 amounts = E5:E2932
A SUMPRODUCT függvény belsejében a MONTH függvény használható az adatkészlet minden dátumának hónapszámának kinyerésére és összehasonlítására a 3-as számmal:
(MONTH(dates)=3)
Ha egy kis adatkészletet feltételezünk, amelyek 3 dátumot tartalmaznak januárban, februárban és márciusban (ebben a sorrendben), akkor az eredmény egy tömb, amely kilenc ilyen számot tartalmaz:
(1;1;1;2;2;2;3;3;3)
ahol minden szám a dátum "hónapszáma". Ha az értékeket összehasonlítjuk a 3-mal, az eredmény egy ilyen tömb:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Ezt a tömböt ezután megszorozzuk az egyes márciusi dátumokkal társított összegértékekkel. Ha feltételezzük, hogy mind a kilenc összeg 100-nak felel meg, a művelet így néz ki:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Vegye figyelembe, hogy a matematikai művelet az IGAZ HAMIS értékeket egysé és nullává változtatja. Szorzás után egyetlen tömbünk van a SUMPRODUCT-ban:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Vegye figyelembe, hogy az egyetlen fennmaradó összeg márciushoz kapcsolódik, a többi nulla.
Végül a SUMPRODUCT visszaadja az összes elem összegét - 300 a fenti rövidített példában és 25 521 a képernyőképen tényleges adatokkal.
Számoljon havonta, figyelmen kívül hagyva az évet
Ha évenként figyelmen kívül akarja venni a havi számlálást, használhatja a következő SUMPRODUCT-ot:
=SUMPRODUCT(--(MONTH(dates)=3))
Átlag havonta, figyelmen kívül hagyva az évet
Az év figyelmen kívül hagyásával kiszámíthatja és havonta átlagolhatja a fenti két SUMPRODUCT képletet:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
