Pieņemsim, ka sanāksmju datumi jāpārvalda Excel failā. Tagad vēlaties uzzināt, cik datumu (sanāksmju) ir pašreizējā mēnesī, nākamajā mēnesī vai iepriekšējā mēnesī. Lai to izdarītu, parasti izmantojat filtrus. Bet tas nav risinājums.
Mēs varam saskaitīt pašreizējo datumu skaitu no Excel diapazona, izmantojot vienu formulu. Tas tiks automātiski atjaunināts, un datumu skaitīšanai vairs nebūs jāizmanto filtri.
Varat izmantot trīs formulas. To pamatā ir:
1. COUNTIFS izmantošana ar EOMONTH un TODAY funkciju
2. COUNT un IF izmantošana ar MONTH un TODAY funkciju
3. SUMPRODUCT izmantošana ar MONTH un TODAY funkciju
Pārbaudīsim tos pa vienam.
1. metode. Saskaitiet datumus pašreizējā mēnesī, izmantojot Excel COUNTIFS ar EOMONTH un TODAY funkciju
Vispārējā formula ir
| = COUNTIFS (datumi, "> =" & EOMONTH (TODAY (),-1)+1, datumi, "<=" & EOMONTH (TODAY (), 0)) |
Datumi:Tas ir diapazons, kurā ir datumi. Tas var būt diapazons vai nosaukts diapazons.
MĒNESIS (ŠODIEN (),-1) +1:Lai iegūtu pašreizējā mēneša pirmo datumu. Jūs varat pielāgot šo sadaļu, lai iegūtu nākamā mēneša vai pagājušā mēneša pirmo datumu, attiecīgi mainot -1 uz 0 un 1. Palieliniet mēneša skaitu un palieliniet nobīdi.
MĒNESIS (ŠODIEN (), 0):Tas ir, lai iegūtu pašreizējā mēneša pēdējo datumu. Palielinot skaitli šajā segmentā, jūs saņemsiet pēdējos datumus nākamajā mēnesī, ja samazināsiet skaitli, tad iepriekšējo mēnešu pēdējo datumu.
Izmantojiet iepriekš minētos divus segmentus, lai kontrolētu mēnesi.
Piemērs: iegūstiet plānotās sapulces pašreizējā mēnesī
Šeit mums ir tabula, kurā ir sapulces datumi. Mums ir jāiegūst datumu skaits no diapazona, kas plānots pašreizējā mēnesī.
Uzrakstiet šo formulu:
| = COUNTIFS (B2: B12, "> =" & EOMONTH (TODAY (),-1)+1, B2: B12, "<=" & EOMONTH (TODAY (), 0)) |
Tas atgriež 5, jo 2020. gada februārī ir 5 datumi.
Kā tas darbojas?
Būtībā mēs vēlamies saskaitīt datumus, kas ir lielāki vai vienādi ar kārtējā mēneša pirmo datumu un mazāki vai vienādi ar kārtējā mēneša pēdējo dienu. Tātad mēs izmantojam funkciju COUNTIF.
Tagad mums ir jānovērtē tikai kārtējā mēneša pirmais datums un pēdējā mēneša datums. Mēs to darām, izmantojot Excel funkcijas EOMONTH un TODAY.
Funkcija EOMONTH atgriež norādītā datuma mēneša pēdējo datumu. Mēs varam kontrolēt mēnesi pirms un pēc nākamā argumenta izmantošanas. Mēs sniedzam šodienas datumu, izmantojot funkciju TODAY. Tagad paziņojums EOMONTH (TODAY (),-1) +1 atgriež kārtējā mēneša pirmo datumu (sīkāk par EOMONTH funkciju lasi šeit). EOMONTH (TODAY (), 0) atgriež kārtējā mēneša pēdējo datumu.
Galu galā formula kļūst par vienkāršu COUNTIFS formulu.
| = COUNTIFS (B2: B12, "> =" & 43862, B2: B12, "<=" & 43890) |
Šī formula atgriež pašreizējā mēneša skaitīšanas datumus diapazonā B2: B12.
2. metode. Saskaitiet datumus pašreizējā mēnesī, izmantojot COUNT un IF ar funkciju MONTH, YEAR un TODAY
Ja nevēlaties izmantot funkciju EOMONTH, varat izmantot šo formulu, lai saskaitītu pašreizējā mēneša datumus.
Vispārējā formula:
| = COUNT (JA (MĒNESIS (datumi) un GADS (datumi) = MĒNESIS (ŠODIEN ()) & GADS (ŠODIEN ()), 1, "")) |
Datumi:Tas ir diapazons, kurā ir datumi. Tas var būt diapazons vai nosaukts diapazons.
Izmantosim šo vispārīgo formulu iepriekš minētajā piemērā. Formula būs šāda:
| = SKAITS (JA (MĒNESIS (B2: B12) & GADS (B2: B12) = MĒNESIS (ŠODIEN ()) & GADS (ŠODIEN ()), 1, "")) |
Kā tas darbojas?
TheMĒNESIS (B2: B12) un GADS (B2: B12) daļa atgriež masīvu, kurā ir katra datuma mēnesis un gads. Masīvs būs
{"32020";"22020";"32020";"32020";"12020";"22020";"22020";"32020";"22020";"22020";"32020"}.
TheMĒNESIS (ŠODIEN ()) UN GADS (ŠODIEN ())daļa izveido virkni, kurā ir pašreizējā mēneša mēneša numurs un gads. Šajā piemērā tas ir "22020". Šo vērtību salīdzina ar katru vērtību iepriekš minētajā masīvā, izmantojot vienādības (=) zīmi. Tas atgriež masīvu TRUE un FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Tagad katrai TRUE vērtībai funkcija IF atgriež 1 un FALSE neatgriež neko ("").
{"";1;"";"";"";1;1;"";1;1;""}
Visbeidzot, funkcija COUNT saskaita skaitliskās vērtības no masīva un atgriež pašreizējā mēneša datumus no atlasītā diapazona.
3. metode. Saskaitiet datumus pašreizējā mēnesī, izmantojot SUMPRODUCT ar funkciju MONTH, YEAR un TODAY
Funkcija SUMPRODUCT ir pārsteidzoša, ja runa ir par vērtību skaitīšanu un summēšanu ar trakiem kritērijiem.
Šajā metodē mēs izmantojam Būla loģiku, lai skaitītu diapazona pašreizējā mēneša datumus.
| = SUMPRODUCT (-(MĒNESIS (datumi) un GADS (datumi) = MĒNESIS (ŠODIEN ()) & GADS (ŠODIEN ()))) |
Datumi:Tas ir diapazons, kurā ir datumi. Tas var būt diapazons vai nosaukts diapazons.
Izmantojot šo funkciju mūsu piemērā, mēs saņemam tieši tādu pašu atbildi.
| = SUMPRODUCT (-(MĒNESIS (B2: B12) & GADS (B2: B12) = MĒNESIS (ŠODIEN ()) & GADS (ŠODIEN ()))) |
Kā tas darbojas?
(MĒNESIS (B2: B12) & GADS (B2: B12) = MĒNESIS (ŠODIEN ()) & GADS (ŠODIEN ())):Šis segments dara to pašu, ko COUNT un IF metodē, un atgriež masīvu TRUE un FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Ārpus šī apgalvojuma mums ir dubults negatīvs operators (-), lai šīs patiesās un nepatiesās vērtības pārvērstu par 1s un 0s. Visbeidzot, mums ir vienkāršota formula:
SUMPRODUCT ({0; 1; 0; 0; 0; 1; 1; 0; 1; 1; 0})
Funkcija SUMPRODUCT apkopo šo masīvu un atgriež diapazonā esošā mēneša skaitļu datumu skaitu.
Tātad, jā, puiši, šādi tiek skaitīti kārtējā mēneša datumi. Izvēlieties sev piemērotāko. Es ceru, ka tas bija izskaidrojošs un noderīgs. Ja jums ir šaubas par šo tēmu, jautājiet komentāru sadaļā zemāk. Es priecāšos dzirdēt no jums.
Kā saskaitīt atlikušās dienas programmā Excel | Lai saskaitītu atlikušās dienas, programmā Excel mēs izmantojam vienkāršu atņemšanas formulu. Ja vēlaties skaitīt atlikušās dienas no pašreizējā datuma, tad mēs izmantojam funkciju TODAY.
Kā aprēķināt dienas, mēnešus un gadus| Lai aprēķinātu dienas, mēnesi un gadu, programmā Excel ir trīs funkcijas DAY, MONTH un YEAR. Šīs funkcijas iegūst dienu, mēnesi un gadu no noteiktā datuma.
Kā aprēķināt atlikušās mēneša dienas programmā Excel |Lai aprēķinātu atlikušās mēneša dienas, mēs izmantojam funkciju EOMONTH. Mēs atņemam pašreizējo datumu no mēneša beigu datuma.
Saskaitiet dzimšanas datumus no diapazona pa mēnešiem Excel| Izmantojot funkciju SUMPRODUCT un MONTH. Šī funkcija ļaus mums uzzināt, cik dzimšanas datumu ir noteiktā mēnesī.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā izmantot Excel VLOOKUP funkciju| Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot Excel Funkcija COUNTIF| Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai uzskaitītu noteiktu vērtību. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot funkciju SUMIF programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.