Přejít k hlavnímu obsahu

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

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:

=SUMIFS(sum_range,date_range,">="&DATE(year,1,1),date_range,"<="&DATE(year,12,31)
  • 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:

=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))


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:

=SUMPRODUCT((YEAR(date_range)=year)*sum_range)
  • 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 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 AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  Vyvolejte funkce Kutools...
Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

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í...

Popis


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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations