Sažetak
Za sumiranje po mjesecima u stupcima možete koristiti funkciju SUMIFS zajedno s funkcijom EOMONTH. U prikazanom primjeru formula u G5 je:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Ova formula koristi tri imenovana raspona: "iznos" (D5: D15), "klijent" (B5: B15) i "datum" (C5: C15).
Obrazloženje
Funkcija SUMIFS dizajnirana je za zbrajanje vrijednosti u rasponu na temelju jednog ili više kriterija. U ovom su nam slučaju potrebna tri kriterija:
- Ime klijenta = klijent u stupcu F
- Datum> = prvi u mjesecu (od datuma u retku 4)
- Datum <= kraj mjeseca (od datuma u retku 4)
Ključna točka: nazivi mjeseci u G4: I4 zapravo su važeći datumi, formatirani u formatu prilagođenog broja "mmm". To nam omogućuje da izravno koristimo vrijednosti datuma u G4: I4 za gore navedene kriterije # 2 i # 3.
Natrag u SUMIFS-u, prvi par raspona / kriterija koristi se za provjeru imena klijenta:
client,$F5
Obavijest F5 mješovita je referenca, sa zaključanim stupcem. To omogućuje promjenu retka dok se formula kopira kroz tablicu, ali ime klijenta uvijek dolazi iz stupca F.
Drugi par raspona / kriterija koristi se za provjeru datuma u odnosu na prvi u mjesecu:
date,">="&G$4
Kao što je gore spomenuto, vrijednosti u G4: I4 zapravo su datumi: 1. siječnja 2019., 1. veljače 2019. i 1. ožujka 2019. Dakle, ovaj kriterij jednostavno provjerava datume veće od datuma prvog u mjesecu u redu 4. Primijetite da ovo je još jedna mješovita referenca, ovaj put s zaključanim redom. To omogućuje promjenu stupca dok se formula kopira preko tablice, ali zadržava broj retka fiksnim. Spajanje znakom & (&) potrebno je pri izgradnji kriterija koji koriste logički operator i vrijednost iz druge ćelije.
Treći par raspona / kriterija koristi se za provjeru datuma u odnosu na zadnji dan u mjesecu:
date,"<="&EOMONTH(G$4,0)
Da bismo dobili zadnji dan svakog mjeseca, koristimo funkciju EOMONTH na datum iz zaglavlja stupca u retku 4. S nulom navedenom za argument mjeseci, EOMONTH vraća zadnji dan istog mjeseca. Opet, referenca na G4 je pomiješana da se red ne mijenja.
Rješenje zaokretne tablice
Zaokretna tablica bila bi izvrsno rješenje za ovaj problem jer se automatski može grupirati po mjesecima bez ikakvih formula. Za usporednu usporedbu formula i zaokretnih tablica pogledajte ovaj videozapis: Zašto zaokretne tablice.
