Oletame, et peate koosoleku kuupäevi haldama Exceli failis. Nüüd soovite teada, mitu kuupäeva (koosolekut) on jooksval kuul, järgmisel kuul või eelmisel kuul. Tavaliselt kasutate selleks filtreid. Aga see pole lahendus.
Ühe valemi abil saame Exceli vahemikust praeguste kuupäevade arvu lugeda. Seda värskendatakse automaatselt ja te ei pea enam kuupäevade loendamiseks filtreid kasutama.
Kasutada saab kolme valemit. Need põhinevad:
1. COUNTIFSi kasutamine koos EOMONTH ja TODAY funktsiooniga
2. COUNT ja IF kasutamine koos funktsiooniga KUU ja TÄNA
3. SUMPRODUCT kasutamine koos KUU ja TÄNA funktsiooniga
Vaatame need ükshaaval üle.
Meetod 1. Loendage kuupäevad jooksval kuul, kasutades Exceli COUNTIFS funktsiooni EOMONTH ja TODAY
Üldine valem on
| = COUNTIFS (kuupäevad, "> =" & EOMONTH (TODAY (),-1)+1, kuupäevad, "<=" & EOMONTH (TODAY (), 0)) |
Kuupäevad:See on vahemik, mis sisaldab kuupäevi. See võib olla vahemik või nimega vahemik.
EOMONTH (TÄNA (),-1) +1:Sellega saate jooksva kuu esimese kuupäeva. Saate seda jaotist kohandada järgmise kuu või eelmise kuu esimese kuupäeva saamiseks, muutes vastavalt -1 väärtust 0 ja 1. Suurendage kuu arvu ja suurendage seda.
EOMONTH (TODAY (), 0):Selle eesmärk on saada jooksva kuu viimane kuupäev. Kui suurendate selle segmendi arvu, saate järgmisel kuul viimased kuupäevad, kui vähendate, siis eelmiste kuude viimase kuupäeva.
Kuu juhtimiseks kasutage ülaltoodud kahte segmenti.
Näide: hankige praegusel kuul planeeritud koosolekud
Siin on meil tabel, millel on koosoleku kuupäevad. Peame saama kuupäevade arvu vahemikust, mis on kavandatud jooksval kuul.
Kirjutage see valem:
| = COUNTIFS (B2: B12, "> =" & EOMONTH (TODAY (),-1)+1, B2: B12, "<=" & EOMONTH (TODAY (), 0)) |
See tagastab 5, kuna veebruaris 2020 on 5 kuupäeva.
Kuidas see töötab?
Põhimõtteliselt tahame lugeda kuupäevi, mis on suuremad või võrdsed jooksva kuu esimesest kuupäevast ja väiksemad või võrdsed jooksva kuu viimase päevaga. Seega kasutame funktsiooni COUNTIF.
Nüüd peame hindama ainult jooksva kuu esimest kuupäeva ja jooksva kuu viimast kuupäeva. Teeme seda Exceli funktsioonide abil EOMONTH ja TODAY.
Funktsioon EOMONTH tagastab sisestatud kuupäeva kuu viimase kuupäeva. Saame kontrollida järgmise argumendi kasutamisele eelnevat ja järgnevat kuud. Tänase kuupäeva esitame funktsiooni TODAY abil. Nüüd tagastab EOMONTH (TODAY (),-1) +1 avaldus jooksva kuu esimese kuupäeva (loe funktsiooni EOMONTH kohta täpsemalt siit). EOMONTH (TODAY (), 0) tagastab jooksva kuu viimase kuupäeva.
Lõpuks saab valemist lihtne COUNTIFS valem.
| = COUNTIFS (B2: B12, "> =" & 43862, B2: B12, "<=" & 43890) |
See valem tagastab jooksva kuu loendamiskuupäevad vahemikus B2: B12.
Meetod 2. Loendage kuupäevad jooksval kuul, kasutades funktsiooni COUNT ja IF koos funktsiooniga KUU, AASTA ja TÄNA
Kui te ei soovi funktsiooni EOMONTH kasutada, saate selle valemi abil lugeda praegusel kuul toimuvaid kuupäevi.
Üldine valem:
| = COUNT (KUI (KUU (kuupäevad) & AASTA (kuupäevad) = KUU (TÄNA ()) & AASTA (TÄNA ()), 1, "")) |
Kuupäevad:See on vahemik, mis sisaldab kuupäevi. See võib olla vahemik või nimega vahemik.
Kasutame seda üldist valemit ülaltoodud näites. Valem saab olema järgmine:
| = COUNT (KUI (KUU (B2: B12) & AASTA (B2: B12) = KUU (TÄNA ()) & AASTA (TÄNA ()), 1, "")) |
Kuidas see töötab?
TheKUU (B2: B12) ja AASTA (B2: B12) osa tagastab massiivi, mis sisaldab iga kuupäeva kuud ja aastat. Massiiv saab olema
{"32020";"22020";"32020";"32020";"12020";"22020";"22020";"32020";"22020";"22020";"32020"}.
TheMONTH (TODAY ()) & YEAR (TODAY ())osa loob stringi, mis sisaldab jooksva kuu kuu numbrit ja aastat. Selles näites on see "22020". Seda väärtust võrreldakse ülaltoodud massiivi iga väärtusega, kasutades võrdusmärki (=). See tagastab massiivi TRUE ja FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Nüüd tagastab iga tõelise väärtuse korral IF funktsioon 1 ja vale ei tagasta midagi ("").
{"";1;"";"";"";1;1;"";1;1;""}
Lõpuks loendab funktsioon COUNT massiivi arvväärtused ja tagastab valitud vahemikust jooksva kuu kuupäevad.
Meetod 3. Loendage kuupäevad jooksval kuul, kasutades funktsiooni SUMPRODUCT koos funktsiooniga MONTH, YEAR ja TODAY
Funktsioon SUMPRODUCT on hämmastav, kui tegemist on hullude kriteeriumidega väärtuste loendamise ja summeerimisega.
Selle meetodi puhul kasutame praeguse kuu kuupäevade vahemiku loendamiseks loogilist loogikat.
| = SUMPRODUCT (-(MONTH (kuupäevad) & AASTA (kuupäevad) = MONTH (TODAY ()) & AASTA (TODAY ()))) |
Kuupäevad:See on vahemik, mis sisaldab kuupäevi. See võib olla vahemik või nimega vahemik.
Kui kasutame seda funktsiooni oma näites, saame täpselt sama vastuse.
| = SUMPRODUCT (-(KUU (B2: B12) & AASTA (B2: B12) = KUU (TÄNA ()) & AASTA (TÄNA ()))) |
Kuidas see töötab?
(KUU (B2: B12) & AASTA (B2: B12) = KUU (TÄNA ()) & AASTA (TÄNA ())):See segment teeb sama, mida COUNT ja IF meetodil ning tagastab massiivi TRUE ja FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Väljaspool seda väidet on meil kahekordne negatiivne operaator (-), mis teisendab need tõelised ja valed väärtused 1-ks ja 0-ks. Lõpuks on meil valem lihtsustatud järgmiselt:
SUMPRODUCT ({0; 1; 0; 0; 0; 1; 1; 0; 1; 1; 0})
Funktsioon SUMPRODUCT võtab selle massiivi kokku ja tagastab vahemiku jooksva kuu numbrikuupäevade arvu.
Nii et jah, poisid, need on viisid, kuidas kaks loevad jooksva kuu kuupäevi. Valige see, mis teile kõige paremini sobib. Loodan, et see oli selgitav ja kasulik. Kui teil on selle teema kohta kahtlusi, küsige allpool kommentaaride jaotises. Mul on hea meel sinust kuulda.
Kuidas Excelis järelejäänud päevi loendada | Ülejäänud päevade loendamiseks kasutame Excelis lihtsat lahutamisvalemit. Kui soovite praegusest kuupäevast järelejäänud päevi lugeda, kasutame funktsiooni TODAY.
Kuidas arvutada päevi, kuid ja aastaid| Päevade, kuu ja aasta arvutamiseks on Excelil kolm funktsiooni nimega PÄEV, KUU ja AASTA. Need funktsioonid saavad päevast, kuust ja aastast antud kuupäevast.
Kuidas Excelis järelejäänud kuupäevi arvutada |Kuu ülejäänud päevade arvutamiseks kasutame funktsiooni EOMONTH. Kuu lõpu kuupäevast lahutame praeguse kuupäeva.
Loendage sünnikuupäevad vahemikust kuude kaupa Excel| Funktsiooni SUMPRODUCT ja MONTH kasutamine. See funktsioon annab meile teada, mitu sünnikuupäeva on konkreetsel kuul.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada funktsiooni Excel VLOOKUP| See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Kuidas Excelit kasutada Funktsioon COUNTIF| Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea konkreetse väärtuse loendamiseks oma andmeid filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.