V tomto článku se naučíme, jak počítat data konkrétního roku v rozsahu dat.
Jednoduše řečeno, při práci s daty někdy potřebujeme spočítat buňky, kde jsou data z daného roku.
Jak problém vyřešit?
Pro tento článek budeme muset použít funkci SUMPRODUCT. Pokud máme data v rozsahu dat a konkrétní hodnotu roku, musíme počítat hodnoty data, kde datum patří konkrétnímu danému roku.
Obecný vzorec:
= SUMPRODUCT ( - (YEAR (range)) = rok))
rozsah: hodnoty data uvedené v rozsahu
rok: hodnota roku udaná jako referenční buňka.
Příklad:
Pojďme tedy tento vzorec otestovat spuštěním na níže uvedeném příkladu.
Zde máme datové záznamy a potřebujeme najít hodnoty data ležící v konkrétním roce nebo vrátit řádky počítání, kde hodnota data leží v daném roce.
Nejprve musíme definovat pojmenovaný rozsah pro rozsah dat jako rozsah. Stačí vybrat hodnoty data a do levého horního rohu zadat název (rozsah) rozsahu.
Nyní použijeme následující vzorec k získání počtu dat, která ležela v roce 2016.
Použijte vzorec:
= SUMPRODUCT ( - (YEAR (range) = F4))
rozsah: pojmenovaný rozsah použitý pro hodnoty data D3: D11.
F4: rok udávaný jako referenční hodnota buňky.
Vysvětlení:
- Funkce YEAR (range) extrahuje hodnotu roku z data a odpovídá jí s daným rokem a operátorem.
= SUMPRODUCT ( - ({2019; 2018; 2019; 2016; 2020; 2019; 2017; 2016; 2020} = 2016))
- = operátor jej porovná s hodnotou roku 2019 a vrátí pole PRAVDIVÝCH a NEPRAVDIVÝCH hodnot v závislosti na operaci.
= SUMPRODUCT ( - ({FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}))
- -- operátor použitý k převodu TRUE hodnoty na 1 & False hodnoty na 0.
= SUMPRODUCT ({0; 0; 0; 1; 0; 0; 0; 1; 0})
- Funkce SUMPRODUCT získá součet 1 s ve vráceném poli, což bude počet požadovaných dat nebo řádků.
Zde je jako referenční hodnota buňky uvedena hodnota roku a jako referenční hodnota buňky je uveden rozsah. Stisknutím klávesy Enter získáte počet.
Jak vidíte, celkové datum odpovídá roku 2016 vychází na 2. To znamená, že existují 2 hodnoty data nebo 2 řádky, kde se hodnota roku rovná roku 2016.
Nyní zkopírujte a vložte vzorec do jiných buněk pomocí kombinace kláves CTRL + D nebo přetažení v aplikaci Excel.
Jak vidíte na výše uvedeném snímku, získáme všechny datové hodnoty, které leží v konkrétním roce, pomocí vzorce Excel. Kontroly pro totéž můžete získat také pomocí možnosti filtru Excel. Použijte filtr na záhlaví Data a zrušte výběr možnosti Vybrat vše a vyberte možnost roku, kterou chcete filtrovat.
Možnost filtru v důsledku vrátí 3 hodnoty. Proveďte totéž pro jiné hodnoty data, abyste získali počet dat nebo řádků s kritérii pro daný konkrétní rok.
Zde je několik pozorovacích poznámek uvedených níže.
Poznámky:
- Vzorec funguje pouze s čísly.
- Vzorec funguje pouze v případě, že ve vyhledávací tabulce nejsou žádné duplikáty.
- Funkce SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkce SUMPRODUCT považuje logickou hodnotu TRUE za 1 a False za 0.
- Pole argumentů musí mít stejnou délku jako funkce.
Doufám, že tento článek o tom, jak vrátit počet, pokud s SUMPRODUCT v aplikaci Excel je vysvětlující. Další články o funkcích SUMPRODUCT najdete zde. Sdílejte prosím svůj dotaz níže v poli pro komentáře. Pomůžeme vám.
Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu.
COUNTIFS s rozsahem dynamických kritérií : Počet buněk závislých na jiných hodnotách buněk v Excelu.
COUNTIFS odpovídá dvěma kritériím : Počet buněk odpovídajících dvěma různým kritériím v seznamu v aplikaci Excel.
COUNTIFS S NEBO Pro více kritérií : Pomocí funkce NEBO spočítejte buňky s více kritérii.
Funkce COUNTIFS v aplikaci Excel : Počet buněk závislých na jiných hodnotách buněk.
Jak používat Countif ve VBA v Microsoft Excelu : Počítejte buňky pomocí kódu Visual Basic for Applications.
Jak používat zástupné znaky v aplikaci Excel : Počítejte buňky odpovídající frázím pomocí zástupných znaků v aplikaci Excel
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity
Upravte rozevírací seznam
Absolutní reference v Excelu
Pokud s podmíněným formátováním
e rok 2019, vyberte rok 2019 a klikněte na OK.