V tomto článku sa naučíme, ako počítať dátumy konkrétneho roku v rozsahu dátumov.
Jednoducho povedané, pri práci s dátumami niekedy potrebujeme spočítať bunky, kde sú dátumy z daného roku.
Ako problém vyriešiť?
V tomto článku budeme musieť používať funkciu SUMPRODUCT. Ak dostávame dátumy v rozsahu údajov a konkrétnu hodnotu roku, musíme spočítať hodnoty dátumu, kde dátum patrí konkrétnemu danému roku.
Všeobecný vzorec:
= SUMPRODUCT ( - (YEAR (range)) = rok))
rozsah: hodnoty dátumu uvedené v rozsahu
rok: hodnota roku uvedená ako referencia bunky.
Príklad:
Otestujme teda tento vzorec spustením na príklade uvedenom nižšie.
Tu máme dátové záznamy a musíme nájsť hodnoty dátumu ležiace v konkrétnom roku alebo vrátiť riadky počítania, kde sa hodnota dátumu nachádza v rámci daného roka.
Po prvé, musíme definovať pomenovaný rozsah pre rozsah dátumov ako rozsah. Stačí vybrať hodnoty dátumu a do ľavého horného rohu napísať názov (rozsah) rozsahu.
Teraz použijeme nasledujúci vzorec na získanie počtu dátumov, ktoré boli v roku 2016.
Použite vzorec:
= SUMPRODUCT ( - (YEAR (range) = F4))
rozsah: pomenovaný rozsah použitý pre hodnoty dátumu D3: D11.
F4: hodnota roku uvedená ako referencia bunky.
Vysvetlenie:
- Funkcia YEAR (range) vyberie hodnotu roku z dátumu a priradí ju k danému roku a operátorovi.
= SUMPRODUCT ( - ({2019; 2018; 2019; 2016; 2020; 2019; 2017; 2016; 2020} = 2016))
- = operátor ho porovná s hodnotou roku 2019 a vráti pole PRAVDIVÝCH a NEPRAVDIVÝCH hodnôt v závislosti od operácie.
= SUMPRODUCT ( - ({FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}))
- -- operátor použitý na konverziu hodnoty TRUE na 1 a nepravdivej hodnoty na 0.
= SUMPRODUCT ({0; 0; 0; 1; 0; 0; 0; 1; 0})
- Funkcia SUMPRODUCT získa vo vrátenom poli súčet 1 s, čo bude počet požadovaných dátumov alebo riadkov.
Tu je hodnota roku uvedená ako referencia bunky a rozsah je uvedený ako odkaz na bunku. Počítadlo získate stlačením klávesu Enter.
Ako vidíte, celkové dátumové zhody za rok 2016 sú 2. To znamená, že existujú 2 hodnoty dátumu alebo 2 riadky, v ktorých sa hodnota roku rovná roku 2016.
Teraz skopírujte a prilepte vzorec do iných buniek pomocou klávesovej skratky CTRL + D alebo potiahnite nadol v programe Excel.
Ako vidíte na vyššie uvedenej snímke, pomocou vzorca v Exceli získavame všetky hodnoty údajov, ktoré ležia v konkrétnom roku. Kontroly toho istého môžete získať aj pomocou možnosti filtra Excel. Použite filter na hlavičku Dátumy, zrušte výber možnosti Vybrať všetko a vyberte možnosť roka, ktorú chcete filtrovať.
Voľba filtra vo výsledku vráti 3 hodnoty. Vykonajte to isté s inými hodnotami dátumu, aby ste získali počet dátumov alebo riadkov s kritériami pre daný konkrétny rok.
Tu je niekoľko pozorovacích poznámok uvedených nižšie.
Poznámky:
- Vzorec funguje iba s číslami.
- Vzorec funguje iba vtedy, ak vo vyhľadávacej tabuľke nie sú žiadne duplikáty.
- Funkcia SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkcia SUMPRODUCT považuje logickú hodnotu TRUE za 1 a False za 0.
- Pole argumentov musí mať rovnakú dĺžku ako funkcia.
Dúfam, že tento článok o tom, ako vrátiť počet, ak je v programe SUMPRODUCT v programe Excel vysvetľujúci. Tu nájdete ďalšie články o funkciách SUMPRODUCT. Podeľte sa o svoj dotaz nižšie v poli pre komentáre. Pomôžeme vám.
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.
COUNTIFS s rozsahom dynamických kritérií : Počet buniek závislých od iných hodnôt buniek v programe Excel.
COUNTIFS zápas dvoch kritérií : Počet buniek zodpovedajúcich dvom rôznym kritériám v zozname v programe Excel.
COUNTIFS s ALEBO pre viac kritérií : Počet buniek s viacerými kritériami sa zhoduje pomocou funkcie OR.
Funkcia COUNTIFS v programe Excel : Počet buniek závislých od iných hodnôt buniek.
Ako používať Countif vo VBA v programe Microsoft Excel : Počítanie buniek pomocou kódu Visual Basic for Applications.
Ako používať zástupné znaky v programe Excel : Spočítajte bunky zodpovedajúce frázam pomocou zástupných znakov v programe Excel
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu
Upravte rozbaľovací zoznam
Absolútna referencia v Exceli
Ak s podmieneným formátovaním
V roku 2019 vyberte rok 2019 a kliknite na tlačidlo OK.