Recimo, da morate datume sestankov upravljati v Excelovi datoteki. Zdaj želite vedeti, koliko datumov (sestankov) je v tekočem mesecu, naslednjem mesecu ali prejšnjem mesecu. Za to običajno uporabljate filtre. Ampak to ni rešitev.
Število trenutnih datumov lahko iz Excelovega območja preštejemo z eno formulo. Posodobil se bo samodejno in za štetje datumov vam ne bo treba več uporabljati filtrov.
Uporabite lahko tri formule. Temeljijo na:
1. Uporaba COUNTIFS s funkcijami EOMONTH in TODAY
2. Uporaba COUNT in IF s funkcijami MONTH in TODAY
3. Uporaba SUMPRODUCT s funkcijami MESEC in DANES
Preverimo jih enega za drugim.
Metoda 1. Štetje datumov v trenutnem mesecu z uporabo programa Excel COUNTIFS s funkcijami EOMONTH in TODAY
Splošna formula je
| = COUNTIFS (dates, "> =" & EOMONTH (TODAY (),-1)+1, dates, "<=" & EOMONTH (TODAY (), 0)) |
Datumi:To je obseg, ki vsebuje datume. Lahko je obseg ali imenovani obseg.
EOMONTH (DANES (),-1) +1:To dobite za prvi datum tekočega meseca. Ta razdelek lahko prilagodite tako, da dobite prvi datum naslednjega ali zadnjega meseca s spremembo -1 na 0 oziroma 1. Povečajte število in povečajte odmik meseca.
EOMONTH (DANES (), 0):To je zadnji datum tekočega meseca. Če povečate število v tem segmentu, dobite zadnje datume naslednji mesec, če zmanjšate število, dobite zadnji datum prejšnjih mesecev.
Za nadzor meseca uporabite zgornja dva segmenta.
Primer: Pridobite razpored sestankov v trenutnem mesecu
Tukaj imamo tabelo z datumi srečanja. Iz razpona moramo dobiti število datumov, načrtovanih v tekočem mesecu.
Zapišite to formulo:
| = COUNTIFS (B2: B12, "> =" & EOMONTH (DANES (),-1)+1, B2: B12, "<=" & EOMONTH (DANES (), 0)) |
To vrne 5, saj je 5 februar 2020.
Kako deluje?
V bistvu želimo šteti datume, ki so večji ali enaki prvemu datumu tekočega meseca in manjši ali enaki zadnjemu dnevu v tekočem mesecu. Zato uporabljamo funkcijo COUNTIF.
Zdaj moramo oceniti le prvi datum tekočega meseca in zadnji datum tekočega meseca. To počnemo z uporabo Excelovih funkcij EOMONTH in DANES.
Funkcija EOMONTH vrne zadnji datum v mesecu navedenega datuma. Mesec pred in po uporabi naslednjega argumenta lahko nadziramo. Današnji datum podamo s funkcijo TODAY. Zdaj stavek EOMONTH (TODAY (),-1) +1 vrne prvi datum trenutnega meseca (podrobno preberite o funkciji EOMONTH tukaj). EOMONTH (TODAY (), 0) vrne zadnji datum tekočega meseca.
Sčasoma formula postane preprosta formula COUNTIFS.
| = COUNTIFS (B2: B12, "> =" & 43862, B2: B12, "<=" & 43890) |
Ta formula vrne datume štetja trenutnega meseca v območju B2: B12.
Metoda 2. Štetje datumov v trenutnem mesecu z uporabo COUNT in IF s funkcijami MONTH, YEAR in TODAY
Če ne želite uporabljati funkcije EOMONTH, lahko s to formulo štejete datume, ki se pojavijo v tekočem mesecu.
Splošna formula:
| = COUNT (ČE (MESEC (datumi) & LETO (datumi) = MESEC (TODAY ()) & LETO (TODAY ()), 1, "")) |
Datumi:To je obseg, ki vsebuje datume. Lahko je obseg ali imenovani obseg.
Uporabimo to generično formulo v zgornjem primeru. Formula bo naslednja:
| = COUNT (ČE (MESEC (B2: B12) & LETO (B2: B12) = MESEC (DANES ()) & LETO (DANES ()), 1, "")) |
Kako deluje?
TheMESEC (B2: B12) & LETO (B2: B12) del vrne polje, ki vsebuje mesec in leto vsakega datuma. Niz bo
{"32020";"22020";"32020";"32020";"12020";"22020";"22020";"32020";"22020";"22020";"32020"}.
TheMESEC (DANES ()) & LETO (DANES ())del ustvari niz, ki vsebuje številko meseca in leto trenutnega meseca. V tem primeru je "22020". Ta vrednost se primerja z vsako vrednostjo v zgornjem nizu z uporabo znaka equals (=). To vrne niz TRUE in FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Zdaj za vsako vrednost TRUE funkcija IF vrne 1, za vrednost FALSE pa nič ("").
{"";1;"";"";"";1;1;"";1;1;""}
Končno funkcija COUNT šteje številske vrednosti iz matrike in iz izbranega obsega vrne datume trenutnega meseca.
Metoda 3. Štetje datumov v trenutnem mesecu z uporabo SUMPRODUCT s funkcijami MONTH, YEAR in TODAY
Funkcija SUMPRODUCT je neverjetna, ko gre za štetje in seštevanje vrednosti z norimi merili.
Pri tej metodi uporabljamo logično logiko za štetje datumov trenutnega meseca v razponu.
| = SUMPRODUCT (-(MESEC (datumi) & LETO (datumi) = MESEC (DANES ()) & LETO (DANES ()))) |
Datumi:To je obseg, ki vsebuje datume. Lahko je obseg ali imenovani obseg.
Ko v našem primeru uporabimo to funkcijo, dobimo popolnoma enak odgovor.
| = SUMPRODUCT (-(MESEC (B2: B12) & LETO (B2: B12) = MESEC (DANES ()) & LETO (DANES ()))) |
Kako deluje?
(MESEC (B2: B12) & LETO (B2: B12) = MESEC (DANES ()) & LETO (DANES ())):Ta segment naredi enako kot v metodah COUNT in IF ter vrne niz TRUE in FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Zunaj te izjave imamo dvojni negativni operator (-), ki te vrednosti TRUE in FALSE pretvori v 1s in 0s. Končno imamo formulo poenostavljeno:
PODROČJE ({0; 1; 0; 0; 0; 1; 1; 0; 1; 1; 0})
Funkcija SUMPRODUCT povzame to matriko in vrne štetje datumov števila trenutnega meseca v obsegu.
Torej, fantje, na ta način dva štejeta datume v tekočem mesecu. Izberite tistega, ki vam najbolj ustreza. Upam, da je bilo razlagalno in koristno. Če dvomite o tej temi, vprašajte v spodnjem razdelku komentarjev. Z veseljem vas bom slišal.
Kako odštevati preostale dni v Excelu | Za štetje preostalih dni uporabljamo preprosto formulo odštevanja v Excelu. Če želite odšteti preostale dni od trenutnega datuma, potem uporabimo funkcijo TODAY.
Kako izračunati dneve, mesece in leta| Excel za izračun dni, meseca in leta ima tri funkcije, imenovane DAN, MESEC in LETO. Te funkcije dobijo dan, mesec in leto od navedenega datuma.
Kako izračunati preostale dneve v mesecu v Excelu |Za izračun preostalih dni v mesecu uporabimo funkcijo EOMONTH. Trenutni datum odštejemo od datuma konca meseca.
Štejte datume rojstva iz obsega v mesecu Excel| Uporaba funkcije SUMPRODUCT in MONTH. Ta funkcija nam bo sporočila, koliko rojstnih dni je v določenem mesecu.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati funkcijo Excel VLOOKUP| To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati Excel COUNTIF funkcija| Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določene vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.