Povedzme, že musíte spravovať dátumy schôdzí v súbore programu Excel. Teraz chcete zistiť, koľko dátumov (schôdzí) je v aktuálnom mesiaci, budúcom mesiaci alebo predchádzajúcom mesiaci. Obvykle na to použijete filtre. Ale to nie je riešenie.
Pomocou jedného vzorca môžeme spočítať počet aktuálnych dátumov z rozsahu Excelu. Aktualizuje sa automaticky a na počítanie dátumov už nebudete musieť používať filtre.
Môžete použiť tri vzorce. Sú založené na:
1. Používanie COUNTIFS s funkciami EOMONTH a TODAY
2. Používanie funkcie COUNT a IF s funkciou MONTH a TODAY
3. Používanie SUMPRODUCT s funkciou MONTH a TODAY
Pozrime sa na ne jeden po druhom.
Metóda 1. Počítanie dátumov v aktuálnom mesiaci pomocou programu Excel COUNTIFS s funkciou EOMONTH a DNES
Obecný vzorec je
| = COUNTIFS (dátumy, "> =" & EOMONTH (DNES (),-1)+1, dátumy, "<=" & EOMONTH (DNES (), 0)) |
Termíny:Je to rozsah, ktorý obsahuje dátumy. Môže to byť rozsah alebo pomenovaný rozsah.
EOMONTH (DNES (),-1) +1:Získate tak prvý dátum aktuálneho mesiaca. Túto sekciu môžete upraviť tak, aby ste získali prvý dátum budúceho alebo predchádzajúceho mesiaca, a to zmenou -1 až 0 a 1. Zvýšte počet a zvýšte ofset mesiaca.
EOMONTH (TODAY (), 0):Toto má získať posledný dátum aktuálneho mesiaca. Ak zvýšite počet v tomto segmente, získate posledné dátumy budúci mesiac, ak číslo znížite, získate posledný dátum predchádzajúcich mesiacov.
Na ovládanie mesiaca použite vyššie uvedené dva segmenty.
Príklad: Nechajte si naplánovať schôdze na aktuálny mesiac
Tu máme tabuľku, ktorá obsahuje dátumy schôdze. Potrebujeme získať počet dátumov z rozsahu naplánovaných na aktuálny mesiac.
Napíšte tento vzorec:
| = COUNTIFS (B2: B12, "> =" & EOMONTH (DNES (),-1)+1, B2: B12, "<=" & EOMONTH (DNES (), 0)) |
Vráti sa 5, pretože existuje 5 dátumov februára 2020.
Ako to funguje?
V zásade chceme počítať dátumy väčšie alebo rovnajúce sa prvému dátumu aktuálneho mesiaca a menšie alebo rovné poslednému dňu aktuálneho mesiaca. Používame teda funkciu COUNTIF.
Teraz musíme vyhodnotiť iba prvý dátum aktuálneho mesiaca a posledný dátum aktuálneho mesiaca. Robíme to pomocou funkcií Excelu EOMONTH a DNES.
Funkcia EOMONTH vráti posledný dátum v mesiaci zadaného dátumu. Nasledujúci argument môžeme ovládať mesiac pred a po ňom. Dnešný dátum poskytujeme pomocou funkcie DNES. Teraz príkaz EOMONTH (TODAY (),-1) +1 vráti prvý dátum aktuálneho mesiaca (podrobne si prečítajte o funkcii EOMONTH tu). EOMONTH (TODAY (), 0) vráti posledný dátum aktuálneho mesiaca.
Nakoniec sa zo vzorca stane jednoduchý vzorec COUNTIFS.
| = COUNTIFS (B2: B12, "> =" & 43862, B2: B12, "<=" & 43890) |
Tento vzorec vráti dátumy počítania aktuálneho mesiaca v rozsahu B2: B12.
Metóda 2. Počítanie dátumov v aktuálnom mesiaci pomocou funkcie COUNT a IF s funkciami MONTH, YEAR a TODAY
Ak nechcete používať funkciu EOMONTH, môžete podľa tohto vzorca spočítať dátumy, ktoré sa vyskytujú v aktuálnom mesiaci.
Generický vzorec:
| = COUNT (IF (MONTH (date) & YEAR (dates) = MONTH (TODAY ()) & YEAR (TODAY ()), 1, ""))) |
Termíny:Je to rozsah, ktorý obsahuje dátumy. Môže to byť rozsah alebo pomenovaný rozsah.
Použime tento generický vzorec vo vyššie uvedenom príklade. Vzorec bude:
| = COUNT (IF (MONTH (B2: B12) & YEAR (B2: B12) = MONTH (TODAY ()) & YEAR (TODAY ()), 1, ""))) |
Ako to funguje?
TheMESIAC (B2: B12) & ROK (B2: B12) časť vráti pole, ktoré obsahuje mesiac a rok každého dátumu. Pole bude
{"32020";"22020";"32020";"32020";"12020";"22020";"22020";"32020";"22020";"22020";"32020"}.
TheMESIAC (DNES ()) a ROK (DNES ())časť vytvorí reťazec, ktorý obsahuje číslo mesiaca a rok aktuálneho mesiaca. V tomto prípade je to „22020“. Táto hodnota sa porovná s každou hodnotou vo vyššie uvedenom poli pomocou znamienka rovná sa (=). To vráti pole TRUE a FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Teraz pre každú hodnotu TRUE funkcia IF vráti hodnotu 1 a pre hodnotu FALSE nevráti nič ("").
{"";1;"";"";"";1;1;"";1;1;""}
Nakoniec funkcia COUNT spočíta číselné hodnoty z poľa a vráti dátumy aktuálneho mesiaca z vybraného rozsahu.
Metóda 3. Spočítanie dátumov v aktuálnom mesiaci pomocou funkcie SUMPRODUCT s funkciou MONTH, YEAR a TODAY
Funkcia SUMPRODUCT je úžasná, pokiaľ ide o počítanie a sčítanie hodnôt s bláznivými kritériami.
V tejto metóde používame boolovskú logiku na počítanie dátumov aktuálneho mesiaca v rozsahu.
| = SUMPRODUCT (-(MESIAC (dátumy) & ROK (dátumy) = MESIAC (DNES ()) a ROK (DNES ()))) |
Termíny:Je to rozsah, ktorý obsahuje dátumy. Môže to byť rozsah alebo pomenovaný rozsah.
Keď v tomto prípade použijeme túto funkciu, dostaneme presne rovnakú odpoveď.
| = SUMPRODUCT (-(MONTH (B2: B12) & YEAR (B2: B12) = MONTH (TODAY ()) & YEAR (TODAY ()))) |
Ako to funguje?
(MONTH (B2: B12) & YEAR (B2: B12) = MONTH (TODAY ()) & YEAR (TODAY ())):Tento segment robí to isté, čo robí v metóde COUNT a IF a vracia pole TRUE a FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Mimo tohto príkazu máme dvojnásobný záporný operátor (-) na konverziu týchto PRAVDIVÝCH a NEPRAVDIVÝCH hodnôt na 1 s a 0 s. Nakoniec máme vzorec zjednodušený na:
SUMPRODUCT ({0; 1; 0; 0; 0; 1; 1; 0; 1; 1; 0})
Funkcia SUMPRODUCT toto pole sumarizuje a vráti počet dátumových čísel aktuálneho mesiaca v rozsahu.
Takže áno, chlapci, toto sú dva spôsoby počítania dátumov v aktuálnom mesiaci. Vyberte si ten, ktorý vám najviac vyhovuje. Dúfam, že to bolo vysvetľujúce a užitočné. Ak máte akékoľvek pochybnosti o tejto téme, opýtajte sa ich v sekcii komentárov nižšie. Budem rád, keď sa mi ozvete.
Ako odpočítavať zostávajúce dni v programe Excel | Na počítanie zostávajúcich dní používame v Exceli jednoduchý vzorec na odčítanie. Ak chcete počítať zostávajúce dni od aktuálneho dátumu, potom používame funkciu DNES.
Ako vypočítať dni, mesiace a roky| Na výpočet dní, mesiacov a rokov má Excel tri funkcie s názvom DEŇ, MESIAC a ROK. Tieto funkcie získavajú deň, mesiac a rok od daného dátumu.
Ako vypočítať zostávajúce dni v mesiaci v programe Excel |Na výpočet zostávajúcich dní v mesiaci používame funkciu EOMONTH. Aktuálny dátum odpočítame od dátumu konca mesiaca.
Počítajte dátumy narodenia z rozsahu podľa mesiacov Excel| Použitie funkcie SUMPRODUCT a MONTH. Táto funkcia nám dá vedieť, koľko je narodení v konkrétnom mesiaci.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať funkciu Excel VLOOKUP| Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať Excel Funkcia COUNTIF| Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnej hodnoty nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.