Kopsavilkums
Kolonnās apkopojot katru mēnesi, varat izmantot funkciju SUMIFS kopā ar funkciju EOMONTH. Parādītajā piemērā formula G5 ir:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Šajā formulā tiek izmantoti trīs nosauktie diapazoni: "summa" (D5: D15), "klients" (B5: B15) un "datums" (C5: C15).
Paskaidrojums
Funkcija SUMIFS ir paredzēta, lai summētu vērtības diapazonā, pamatojoties uz vienu vai vairākiem kritērijiem. Šajā gadījumā mums ir nepieciešami trīs kritēriji:
- Klienta nosaukums = klients F kolonnā
- Datums> = mēneša pirmais (no datuma 4. rindā)
- Datums <= mēneša beigas (no datuma 4. rindā)
Galvenais punkts: mēnešu nosaukumi G4: I4 ir faktiski derīgi datumi, kas formatēti ar pielāgoto skaitļu formātu "mmm". Tas ļauj mums tieši izmantot datuma vērtības G4: I4 kritērijiem Nr. 2 un Nr. 3 iepriekš.
Atpakaļ SUMIFS, lai pārbaudītu klienta vārdu, tiek izmantots pirmais diapazona / kritēriju pāris:
client,$F5
Paziņojums F5 ir jaukta atsauce, kolonna ir bloķēta. Tas ļauj mainīt rindu, kad formula tiek kopēta caur tabulu, bet klienta nosaukums vienmēr nāk no F kolonnas.
Otro diapazonu / kritēriju pāri izmanto, lai pārbaudītu datumus pret mēneša pirmo:
date,">="&G$4
Kā minēts iepriekš, vērtības G4: I4 faktiski ir datumi: 2019. gada 1. janvāris, 2019. gada 1. februāris un 2019. gada 1. marts. Tādējādi šie kritēriji vienkārši pārbauda datumus, kas ir lielāki par 4. rindas mēneša pirmā datumiem. šī ir vēl viena jaukta atsauce, šoreiz ar bloķētu rindu. Tas ļauj kolonnu mainīt, kad formula tiek kopēta visā tabulā, bet rindas numurs tiek fiksēts. Apvienošana ar zīmi (&) ir nepieciešama, veidojot kritērijus, kuros izmantots loģisks operators un vērtība no citas šūnas.
Trešo diapazonu / kritēriju pāri izmanto, lai pārbaudītu datumus pret mēneša pēdējo dienu:
date,"<="&EOMONTH(G$4,0)
Lai iegūtu katra mēneša pēdējo dienu, mēs izmantojam funkciju EOMONTH datumā no kolonnas galvenes 4. rindā. Ja nulle ir norādīta mēneša argumentam, EOMONTH atgriež tā paša mēneša pēdējo dienu. Atkal atsauce uz G4 ir jaukta, lai rinda nemainītos.
Pagrieziena galda risinājums
Pagrieziena tabula būtu lielisks risinājums šai problēmai, jo tā var automātiski sagrupēt pa mēnešiem bez vispār formulām. Lai salīdzinātu formulas un rakurstabulas, skatiet šo videoklipu: Kāpēc rakurstabulas.
