Seštejte vrednosti po letih v Excelu
Če predpostavimo, da imate vrsto podatkov, ki vsebuje seznam datumov in zneskov prodaje, zdaj želite dobiti skupni znesek na podlagi danih let, kot je prikazano na spodnjem posnetku zaslona. V Excelu lahko običajni funkciji SUMIFS in SUMPRODUCT pomagata hitro in enostavno rešiti to nalogo. Ta vadnica bo podrobno pokazala, kako uporabljati ti dve funkciji.
- Seštejte vrednosti po letih v Excelu s funkcijama SUMIFS in DATE
- Seštejte vrednosti po letih v Excelu s funkcijo SUMPRODUCT
Seštejte vrednosti po letih v Excelu s funkcijama SUMIFS in DATE
Če želite sešteti skupno vrednost na podlagi danega leta, morate določiti prvi in zadnji datum v letu s funkcijo DATE, nato pa s funkcijo SUMIFS sešteti vse vrednosti znotraj določenih datumov, generična sintaksa je:
- sum_range: obseg vrednosti, ki jih je treba sešteti;
- date_range: obseg datumov, ki jih je treba testirati za določeno leto;
- year: določeno leto, na podlagi katerega želite sešteti vrednosti. (leto, 1,1 označuje prvi dan v letu in leto, 12,31, XNUMX označuje zadnji dan v letu.)
Če želite to nalogo rešiti v Excelu, kopirajte ali vnesite spodnjo formulo v prazno celico in nato formulo povlecite navzdol v druge celice, da dobite rezultat:
Razlaga te formule:
=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))
- 2 C $: 12 C $:je »obseg_vsote«, ki vsebuje vrednosti, ki jih želimo sešteti.
- $B$2:$B$12,">="&DATE(E2,1,1): preveri datume v obsegu B2:B12, ki so večji ali enaki 01/01/2020 – prvi dan v letu v celici E2.
- $B$2:$B$12,"<="&DATE(E2,12,31): preveri datume v območju B2:B12, ki so manjši ali enaki 12. 31. 2020 – zadnji dan v letu v celici E2.
- SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)):Po izpolnjevanju kriterijev se poiščejo in seštejejo vrednosti v območju C2:C12.
Seštejte vrednosti po letih v Excelu s funkcijo SUMPRODUCT
Tukaj vam lahko pomaga še ena preprosta formula, ki jo ustvari funkcija SUMPRODUCT, generična sintaksa je:
- date_range: obseg datumov, ki jih je treba testirati za določeno leto;
- year: določeno leto, na podlagi katerega želite sešteti vrednosti;
- sum_range: obseg vrednosti, ki jih je treba sešteti.
Kopirajte ali vnesite naslednjo formulo v prazno celico in nato formulo povlecite navzdol v druge celice, da dobite rezultat:
Razlaga te formule:
=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)
- LETO($B$2:$B$12)=E2: Funkcija YEAR vrne številko leta iz vsake datumske celice v obsegu B2:B12. Ta izraz preveri, ali je leto v E2 enako letu v območju B2:B12. In dobili boste matriko, kot je ta: {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 tem izrazu bodo vrednosti TRUE in FALSE vsiljene na 1s in 0s, ta niz pa se pomnoži z vsako količino v obsegu C2:C12 in dobili boste novo matriko, kot je ta: {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}): Funkcija SUMPRODUCT končno sešteje vsa števila v tem nizu in vrne rezultat kot 2386.
Uporabljena relativna funkcija:
- SUMIFS:
- Funkcija SUMIFS v Excelu lahko pomaga pri seštevanju vrednosti celic na podlagi več meril.
- SUMPRODUCT:
- S funkcijo SUMPRODUCT lahko pomnožite dva ali več stolpcev ali nizov in nato dobite vsoto izdelkov.
- DATA:
- Funkcija DATE nam lahko pomaga združiti številke leta, meseca in dneva iz ločenih celic na veljaven datum.
- LETO:
- Funkcija YEAR vrne leto na podlagi danega datuma v štirimestni obliki serijske številke.
Več člankov:
- Vsote vrednosti po mesecih (z letom ali brez)
- Ta vadnica podrobno prikazuje dve formuli, ki pomagata pri seštevanju vrednosti po določenem mesecu in letu ali seštevanju vrednosti po določenem mesecu prezreti leto v Excelu.
- Vsote vrednosti po tednu določenega datuma
- Če želite sešteti vrednosti po tednu, v katerem pade določen datum, lahko uporabite formulo, ki temelji na funkciji SUMIFS.
- Vsote vrednosti glede na teden ali dan v tednu
- Ta vadnica korak za korakom prikazuje formule, ki vam pomagajo sešteti vrednosti v določenem obsegu na podlagi določene številke tedna ali dneva v tednu v Excelu.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel - vam pomaga izstopati iz množice
Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...
Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)
- Eno sekundo za preklop med desetinami odprtih dokumentov!
- Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
- Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
- Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.
