Santrauka
Stulpeliuose apibendrindami kiekvieną mėnesį galite naudoti funkciją SUMIFS kartu su funkcija EOMONTH. Parodytame pavyzdyje formulė G5 yra:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Šioje formulėje naudojami trys įvardyti diapazonai: „suma“ (D5: D15), „klientas“ (B5: B15) ir „data“ (C5: C15).
Paaiškinimas
Funkcija SUMIFS skirta sumoms, esančioms diapazone, susieti pagal vieną ar daugiau kriterijų. Šiuo atveju mums reikia trijų kriterijų:
- Kliento vardas = klientas F stulpelyje
- Data> = mėnesio pirmasis (nuo 4 eilutės datos)
- Data <= mėnesio pabaiga (nuo 4 eilutės datos)
Pagrindinis momentas: mėnesių pavadinimai G4: I4 yra galiojančios datos, suformatuotos pagal pasirinktinį skaičių formatą „mmm“. Tai leidžia mums tiesiogiai naudoti datos reikšmes G4: I4 pagal kriterijus Nr. 2 ir Nr. 3.
Atgal į SUMIFS, pirmoji diapazono / kriterijų pora naudojama kliento vardui patikrinti:
client,$F5
Pastaba F5 yra mišri nuoroda, kolona užrakinta. Tai leidžia eilutę pakeisti, kai formulė nukopijuojama per lentelę, tačiau kliento vardas visada gaunamas iš F stulpelio.
Antroji diapazono / kriterijų pora naudojama datoms tikrinti pagal mėnesio pirmąją:
date,">="&G$4
Kaip minėta pirmiau, G4: I4 reikšmės iš tikrųjų yra datos: 2019 m. Sausio 1 d., 2019 m. Vasario 1 d. Ir 2019 m. Kovo 1 d. Taigi, pagal šiuos kriterijus paprasčiausiai tikrinamos datos, kurios yra didesnės nei 4 mėnesio pirmosios datos. Atkreipkite dėmesį, kad tai dar viena mišri nuoroda, šį kartą užrakinta eilutė. Tai leidžia keisti stulpelį, kai formulė nukopijuojama lentelėje, tačiau eilutės numeris lieka fiksuotas. Sujungimas su ampersandu (&) yra būtinas kuriant kriterijus, kuriuose naudojamas loginis operatorius ir kitos ląstelės vertė.
Trečioji diapazono / kriterijų pora naudojama datų tikrinimui pagal paskutinę mėnesio dieną:
date,"<="&EOMONTH(G$4,0)
Norėdami gauti paskutinę kiekvieno mėnesio dieną, mes naudojame funkciją EOMONTH datą iš stulpelio antraštės 4 eilutėje. Argumentui mėnesiai nurodžius nulį, EOMONTH grąžina paskutinę to paties mėnesio dieną. Vėlgi, nuoroda į G4 yra sumaišyta, kad eilutė nesikeistų.
„Pivot Table“ tirpalas
„Pivot“ lentelė būtų puikus šios problemos sprendimas, nes ji gali automatiškai sugrupuoti kiekvieną mėnesį be jokių formulių. Norėdami palyginti formules ir suvestines lenteles, žr. Šį vaizdo įrašą: Kodėl reikia sukti lenteles.
