Kokkuvõte
Veergude kaupa kokkuvõtete kaupa saate kasutada funktsiooni SUMIFS koos funktsiooniga EOMONTH. Näidatud näites on valem G5-s järgmine:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Selles valemis kasutatakse kolme nimega vahemikku: "summa" (D5: D15), "klient" (B5: B15) ja "kuupäev" (C5: C15).
Selgitus
Funktsioon SUMIFS on loodud selleks, et summeerida väärtused vahemikus ühe või mitme kriteeriumi alusel. Sel juhul vajame kolme kriteeriumi:
- Kliendi nimi = klient veerus F
- Kuupäev> = kuu esimene (alates 4. rea kuupäevast)
- Kuupäev <= kuu lõpp (alates 4. rea kuupäevast)
Põhipunkt: kuu nimed kirjas G4: I4 on tegelikult kehtivad kuupäevad, mis on vormindatud kohandatud numbrivorminguga "mmm". See võimaldab meil kasutada ülaltoodud kriteeriumide nr 2 ja 3 puhul otse kuupäeva väärtusi jaotises G4: I4.
Tagasi SUMIFS-i kasutatakse kliendi nime kontrollimiseks esimest vahemikku / kriteeriumide paari:
client,$F5
Märkus F5 on segatud viide, veerg lukustatud. See võimaldab rida muuta, kui valem kopeeritakse läbi tabeli, kuid kliendi nimi tuleb alati veerust F.
Teist vahemikku / kriteeriumipaari kasutatakse kuupäevade kontrollimiseks kuu esimesega:
date,">="&G$4
Nagu eespool mainitud, on G4: I4 väärtused tegelikult kuupäevad: 1. jaanuar 2019, 1. veebruar 2019 ja 1. märts 2019. Seega kontrollib see kriteerium lihtsalt kuupäevi, mis on suuremad kui kuu esimese kuupäeva real 4. Pange tähele, et see on veel üks segane viide, seekord rida lukus. See võimaldab veergu muuta, kui valem kopeeritakse kogu tabelisse, kuid hoiab rea numbri fikseerituna. Liitmine tärniga (&) on vajalik kriteeriumide koostamisel, mis kasutavad loogilist operaatorit ja väärtust teisest lahtrist.
Kolmandat vahemikku / kriteeriumide paari kasutatakse kuupäevade võrdlemiseks kuu viimase päevaga:
date,"<="&EOMONTH(G$4,0)
Iga kuu viimase päeva saamiseks kasutame kuupäeval funktsiooni EOMONTH veeru päisest 4. real. Kui argumendiks kuu on ette nähtud null, tagastab EOMONTH sama kuu viimase päeva. Jällegi segatakse viide G4-le, et rida ei muutuks.
Pöördlaua lahendus
Pöördtabel oleks selle probleemi jaoks suurepärane lahendus, sest see saab automaatselt rühmitada kuude kaupa, ilma et oleks üldse valemeid. Valemite ja pöördtabelite kõrvuti võrdlemiseks vaadake seda videot: Miks pöördtabeleid.
