Note: The other languages of the website are Google-translated. Back to English

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

Č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:

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

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


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:

=SUMPRODUCT((YEAR(date_range)=year)*sum_range)
  • 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.

Najboljša orodja za pisarniško produktivnost

Kutools za Excel - vam pomaga izstopati iz množice

Bi radi svoje vsakodnevno delo opravili hitro in popolno? Kutools za Excel prinaša 300 zmogljivih naprednih funkcij (združevanje delovnih zvezkov, seštevanje po barvi, razdelitev vsebine celice, pretvorba datuma itd.) in vam prihrani 80 % časa.

  • Zasnovan za 1500 delovnih scenarijev vam pomaga rešiti 80 % Excelovih težav.
  • Vsak dan zmanjšajte na tisoče klikov na tipkovnici in miški, razbremenite utrujene oči in roke.
  • V 3 minutah postanite strokovnjak za Excel. Ni vam več treba zapomniti nobenih bolečih formul in kod VBA.
  • 30-dnevno neomejeno brezplačno preskusno obdobje. 60-dnevno jamstvo za vračilo denarja. Brezplačna nadgradnja in podpora za 2 leti.
Trak Excela (z nameščenim Kutools za Excel)

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, Firefox in New Internet Explorer.
Posnetek zaslona programa Excel (z nameščenim zavihkom Office)
Komentarji (0)
Ocene še ni. Bodite prvi in ​​ocenite!
Tu še ni objavljenih komentarjev
Pustite vaše komentarje
Objava kot gost
×
Ocenite to objavo:
0   Znaki
Predlagane lokacije

Sledi nam

Copyright © 2009 - www.extendoffice.com. | Vse pravice pridržane. Poganja ga ExtendOffice. | Kazalo
Microsoft in logotip Office sta blagovni znamki ali registrirani blagovni znamki družbe Microsoft Corporation v ZDA in / ali drugih državah.
Zaščiteno s Sectigo SSL