Sečíst hodnoty podle roku v Excelu
Předpokládejme, že máte řadu dat, která obsahuje seznam dat a částek prodeje, nyní chcete získat celkovou částku na základě daných let, jak je uvedeno níže. V Excelu mohou běžné funkce SUMIFS a SUMPRODUCT pomoci tento úkol rychle a snadno vyřešit. Tento tutoriál podrobně ukáže, jak tyto dvě funkce používat.
- Sečte hodnoty podle roku v Excelu s funkcemi SUMIFS a DATE
- Součet hodnot podle roku v Excelu s funkcí SUMPRODUCT
Sečte hodnoty podle roku v Excelu s funkcemi SUMIFS a DATE
Chcete-li sečíst celkovou hodnotu na základě daného roku, měli byste zadat první a poslední datum roku pomocí funkce DATE a poté pomocí funkce SUMIFS sečíst všechny hodnoty v rámci zadaných dat, obecná syntaxe je:
- sum_range: Rozsah hodnot, které mají být sečteny;
- date_range: Rozsah dat, která mají být testována pro konkrétní rok;
- year: Konkrétní rok, podle kterého chcete sečíst hodnoty. (rok, 1,1 označuje první den v roce a rok,12,31 označuje poslední den v roce.)
Chcete-li tento úkol vyřešit v aplikaci Excel, zkopírujte nebo zadejte níže uvedený vzorec do prázdné buňky a poté přetáhněte vzorec dolů do jiných buněk, abyste získali výsledek:
Vysvětlení tohoto vzorce:
=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))
- $ C $ 2: $ C $ 12:je „rozsah_součtu“, který obsahuje hodnoty, které chceme sečíst.
- $B$2:$B$12,">="&DATUM(E2,1,1;XNUMX;XNUMX): zkontroluje data v rozsahu B2:B12, která jsou větší nebo rovna 01 – první den roku v buňce E01.
- $B$2:$B$12,"<="&DATUM(E2,12,31;XNUMX;XNUMX): zkontroluje data v rozsahu B2:B12, která jsou menší nebo rovna 12. 31. 2020 – poslední den v roce v buňce E2.
- SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)):Po splnění kritérií budou nalezeny a sečteny hodnoty v rozsahu C2:C12.
Součet hodnot podle roku v Excelu s funkcí SUMPRODUCT
Zde vám může pomoci další jednoduchý vzorec, který je vytvořen funkcí SUMPRODUCT, obecná syntaxe je:
- date_range: Rozsah dat, která mají být testována pro konkrétní rok;
- year: Konkrétní rok, podle kterého chcete sečíst hodnoty;
- sum_range: Rozsah hodnot, které mají být sečteny.
Zkopírujte nebo zadejte následující vzorec do prázdné buňky a poté přetáhněte vzorec dolů do jiných buněk, abyste získali výsledek:
Vysvětlení tohoto vzorce:
=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)
- ROK($B$2:$B$12)=E2:Funkce YEAR vrací číslo roku z každé buňky data v rozsahu B2:B12. Tento výraz kontroluje, zda se rok v E2 rovná roku v rozsahu B2:B12. A dostanete pole jako toto: {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}.
- (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12: V tomto výrazu budou hodnoty TRUEs a FALSE vynuceny na 1s a 0s a toto pole se vynásobí každou hodnotou v rozsahu C2:C12 a získáte nové pole takto: {347;0;398;430; 0;379;412;0;0;420;0}.
- SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): Funkce SUMPRODUCT nakonec sečte všechna čísla v tomto poli a vrátí výsledek jako 2386.
Použitá relativní funkce:
- SUMIFS:
- Funkce SUMIFS v Excelu může pomoci sečíst hodnoty buněk na základě více kritérií.
- SUMPRODUCT:
- Funkci SUMPRODUCT lze použít k vynásobení dvou nebo více sloupců nebo polí dohromady a následnému získání součtu produktů.
- DATA:
- Funkce DATE nám může pomoci spojit čísla roku, měsíce a dne ze samostatných buněk do platného data.
- ROK:
- Funkce YEAR vrátí rok na základě daného data ve čtyřmístném formátu sériového čísla.
Další články:
- Součet hodnot podle měsíce (s rokem nebo bez něj)
- Tento kurz podrobně demonstruje dva vzorce, které vám pomohou sečíst hodnoty podle určitého měsíce a roku nebo sečíst hodnoty podle určitého měsíce ignorovat rok v Excelu.
- Součet hodnot podle týdne daného data
- Chcete-li sečíst hodnoty podle týdne, do kterého dané datum spadá, můžete použít vzorec založený na funkci SUMIFS.
- Součet hodnot podle Weeknum nebo Weekday
- Tento kurz demonstruje vzorce krok za krokem, které vám pomohou sečíst hodnoty v určitém rozsahu na základě zadaného čísla týdne nebo dne v týdnu v Excelu.
Nejlepší kancelářské nástroje produktivity
Kutools pro Excel - pomůže vám vyniknout před davem
Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...
Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)
- Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
- Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
- Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
- Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.