Řekněme, že musíte spravovat data schůzek v souboru aplikace Excel. Nyní chcete zjistit, kolik termínů (schůzek) je v aktuálním měsíci, příštím měsíci nebo předchozím měsíci. Obvykle k tomu použijete filtry. Ale to není řešení.
Pomocí jednoho vzorce můžeme spočítat počet aktuálních dat z rozsahu Excelu. Automaticky se aktualizuje a pro počítání dat již nebudete muset používat filtry.
Můžete použít tři vzorce. Jsou založeny na:
1. Používání COUNTIFS s funkcí EOMONTH a TODAY
2. Použití funkce COUNT a IF s funkcí MONTH a TODAY
3. Používání SUMPRODUCT s funkcí MONTH a TODAY
Pojďme se na ně podívat jeden po druhém.
Metoda 1. Počítání dat v aktuálním měsíci pomocí Excel COUNTIFS s funkcí EOMONTH a TODAY
Obecný vzorec je
| = COUNTIFS (data, "> =" & EOMONTH (TODAY (),-1)+1, dates, "<=" & EOMONTH (TODAY (), 0)) |
Termíny:Je to rozsah, který obsahuje data. Může to být rozsah nebo pojmenovaný rozsah.
EOMONTH (DNES (),-1) +1:Získáte tak první datum aktuálního měsíce. Tuto sekci můžete upravit, abyste získali první datum příštího měsíce nebo minulého měsíce změnou -1 na 0, respektive 1. Zvyšte počet a zvyšte ofset měsíce.
EOMONTH (TODAY (), 0):To má získat poslední datum aktuálního měsíce. Pokud zvýšíte počet v tomto segmentu, získáte poslední data příští měsíc, pokud počet snížíte, získáte poslední datum předchozích měsíců.
Pomocí výše uvedených dvou segmentů můžete ovládat měsíc.
Příklad: Získejte schůzky naplánované na aktuální měsíc
Zde máme tabulku, která obsahuje data schůzky. Potřebujeme získat počet termínů z rozsahu naplánovaných na aktuální měsíc.
Napište tento vzorec:
| = COUNTIFS (B2: B12, "> =" & EOMONTH (DNES (),-1)+1, B2: B12, "<=" & EOMONTH (DNES (), 0)) |
Vrací se 5, protože v únoru 2020 je 5 dat.
Jak to funguje?
V zásadě chceme počítat data větší nebo rovna prvnímu datu aktuálního měsíce a menší nebo rovna poslednímu dni aktuálního měsíce. Používáme tedy funkci COUNTIF.
Nyní musíme vyhodnotit pouze první datum aktuálního měsíce a poslední datum aktuálního měsíce. Děláme to pomocí funkcí Excelu EOMONTH a DNES.
Funkce EOMONTH vrací poslední datum měsíce zadaného data. Můžeme ovládat měsíc před a po použití dalšího argumentu. Dnešní datum poskytujeme pomocí funkce DNES. Nyní příkaz EOMONTH (TODAY (),-1) +1 vrací první datum aktuálního měsíce (podrobně si o funkci EOMONTH přečtěte zde). EOMONTH (TODAY (), 0) vrací poslední datum aktuálního měsíce.
Nakonec se ze vzorce stane jednoduchý vzorec COUNTIFS.
| = COUNTIFS (B2: B12, "> =" & 43862, B2: B12, "<=" & 43890) |
Tento vzorec vrací data počítání aktuálního měsíce v rozsahu B2: B12.
Metoda 2. Počítání dat v aktuálním měsíci pomocí funkce COUNT a IF s funkcí MONTH, YEAR a TODAY
Pokud nechcete používat funkci EOMONTH, můžete pomocí tohoto vzorce počítat data, která se vyskytují v aktuálním měsíci.
Obecný vzorec:
| = COUNT (IF (MONTH (data) & YEAR (dates) = MONTH (TODAY ()) & YEAR (TODAY ()), 1, ""))) |
Termíny:Je to rozsah, který obsahuje data. Může to být rozsah nebo pojmenovaný rozsah.
Použijme tento obecný vzorec ve výše uvedeném příkladu. Vzorec bude:
| = COUNT (IF (MONTH (B2: B12) & YEAR (B2: B12) = MONTH (TODAY ()) & YEAR (TODAY ()), 1, "")) |
Jak to funguje?
TheMĚSÍC (B2: B12) & ROK (B2: B12) část vrací pole, které obsahuje měsíc a rok každého data. Pole bude
{"32020";"22020";"32020";"32020";"12020";"22020";"22020";"32020";"22020";"22020";"32020"}.
TheMĚSÍC (DNES ()) A ROK (DNES ())část vytvoří řetězec, který obsahuje číslo měsíce a rok aktuálního měsíce. V tomto případě je to „22020“. Tato hodnota je porovnána s každou hodnotou ve výše uvedeném poli pomocí znaku rovná se (=). Tím se vrátí pole PRAVDA a NEPRAVDA.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Nyní pro každou hodnotu TRUE vrátí funkce IF hodnotu 1 a pro hodnotu FALSE nevrací nic ("").
{"";1;"";"";"";1;1;"";1;1;""}
Nakonec funkce COUNT spočítá číselné hodnoty z pole a vrátí data aktuálního měsíce z vybraného rozsahu.
Metoda 3. Počítání dat v aktuálním měsíci pomocí funkce SUMPRODUCT s funkcí MONTH, YEAR a TODAY
Funkce SUMPRODUCT je úžasná, pokud jde o počítání a sčítání hodnot s bláznivými kritérii.
V této metodě používáme logickou logiku k počítání dat aktuálního měsíce v rozsahu.
| = SUMPRODUKT (-(MĚSÍC (data) & ROK (data) = MĚSÍC (DNES ()) a ROK (DNES ()))) |
Termíny:Je to rozsah, který obsahuje data. Může to být rozsah nebo pojmenovaný rozsah.
Když tuto funkci použijeme v našem příkladu, dostaneme přesně stejnou odpověď.
| = SUMPRODUCT (-(MONTH (B2: B12) & YEAR (B2: B12) = MONTH (TODAY ()) & YEAR (TODAY ()))) |
Jak to funguje?
(MĚSÍC (B2: B12) & ROK (B2: B12) = MĚSÍC (DNES ()) a ROK (DNES ())):Tento segment dělá totéž jako v metodě COUNT a IF a vrací pole PRAVDA a NEPRAVDA.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Mimo toto tvrzení máme dvojitý záporný operátor (-), který převede tyto PRAVDIVÉ a NEPRAVDIVÉ hodnoty na 1 s a 0 s. Nakonec máme vzorec zjednodušený na:
SUMPRODUCT ({0; 1; 0; 0; 0; 1; 1; 0; 1; 1; 0})
Funkce SUMPRODUCT shrnuje toto pole a vrací počet číselných dat aktuálního měsíce v rozsahu.
Takže ano, lidi, toto jsou způsoby, jak počítat dvě data aktuálního měsíce. Vyberte si ten, který vám nejlépe vyhovuje. Doufám, že to bylo vysvětlující a užitečné. Pokud máte ohledně tohoto tématu jakékoli pochybnosti, zeptejte se v sekci komentáře níže. Budu rád, když se ozvete.
Jak odpočítávat zbývající dny v aplikaci Excel | K počítání zbývajících dnů používáme v Excelu jednoduchý odečítací vzorec. Pokud chcete počítat zbývající dny od aktuálního data, pak používáme funkci DNES.
Jak vypočítat dny, měsíce a roky| Pro výpočet dnů, měsíců a roku má Excel tři funkce zvané DEN, MĚSÍC a ROK. Tyto funkce získají den, měsíc a rok od daného data.
Jak vypočítat zbývající dny v měsíci v aplikaci Excel |Pro výpočet zbývajících dnů v měsíci používáme funkci EOMONTH. Aktuální datum odečteme od data konce měsíce.
Počítejte data narození z rozmezí po měsících Excel| Pomocí funkce SUMPRODUCT a MONTH. Tato funkce nám sdělí, kolik dat narození je v konkrétním měsíci.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat funkci Excel VLOOKUP| Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
Jak používat Excel Funkce COUNTIF| Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétní hodnoty není nutné filtrovat data. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.