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

Értékek összegzése évenként Excelben

Tételezzük fel, hogy van egy adattartománya, amely tartalmazza a dátumok és eladási összegek listáját, és most szeretné megkapni a teljes összeget adott évek alapján, az alábbi képernyőképen látható módon. Az Excelben a normál SUMIFS és SUMPRODUCT függvények segíthetnek a feladat gyors és egyszerű megoldásában. Ez az oktatóanyag részletesen bemutatja e két funkció használatát.


Értékek összegzése évenként Excelben SUMIFS és DATE függvényekkel

Egy adott év alapján a teljes érték összegzéséhez meg kell adnia az év első és utolsó dátumát a DÁTUM függvénnyel, majd a SUMIFS függvénnyel összegzi az összes értéket a megadott dátumokon belül, az általános szintaxis:

=SUMIFS(sum_range,date_range,">="&DATE(year,1,1),date_range,"<="&DATE(year,12,31)
  • sum_range: Az összegezendő értéktartomány;
  • date_range: A tesztelendő dátumok tartománya az adott évben;
  • year: Az a konkrét év, amely alapján az értékeket összegezni szeretné. (év, 1,1 az év első napját jelzi, és év,12,31 az év utolsó napját jelzi.)

A feladat Excelben való megoldásához másolja vagy írja be az alábbi képletet egy üres cellába, majd húzza le a képletet más cellákba az eredmény eléréséhez:

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


Ennek a képletnek a magyarázata:

=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:az „összeg_tartomány”, amely tartalmazza az összegezni kívánt értékeket.
  • $B$2:$B$12,">="&DÁTUM(E2,1,1;XNUMX;XNUMX): azokat a dátumokat ellenőrzi a B2:B12 tartományban, amelyek nagyobbak vagy egyenlők, mint 01. – az év első napja az E01 cellában.
  • $B$2:$B$12,"<="&DATE(E2,12,31): ellenőrzi a B2:B12 tartományban lévő dátumokat, amelyek kisebbek vagy egyenlők, mint 12. – az év utolsó napja az E31 cellában.
  • SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)):A feltételek teljesítése után a C2:C12 tartományban lévő értékek megkeresésre és összegzésre kerülnek.

Értékek összegzése évenként Excelben SUMPRODUCT funkcióval

Itt egy másik egyszerű képlet, amelyet a SUMPRODUCT függvény hoz létre, szintén segíthet, az általános szintaxis:

=SUMPRODUCT((YEAR(date_range)=year)*sum_range)
  • date_range: A tesztelendő dátumok tartománya az adott évben;
  • year: Az a konkrét év, amely alapján az értékeket összegezni szeretné;
  • sum_range: Az összegezendő értéktartomány.

Kérjük, másolja vagy írja be a következő képletet egy üres cellába, majd húzza le a képletet más cellákba az eredmény eléréséhez:


Ennek a képletnek a magyarázata:

=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)

  • ÉV($B$2:$B$12)=E2:Az YEAR függvény az évszámot adja vissza a B2:B12 tartomány minden dátumcellájából. Ez a kifejezés azt ellenőrzi, hogy az E2-ben lévő év megegyezik-e a B2:B12 tartomány évével. És egy ilyen tömböt kapsz: {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: Ebben a kifejezésben az IGAZ és HAMIS értékek 1-re és 0-ra lesznek kényszerítve, és ezt a tömböt megszorozzuk a C2:C12 tartomány minden egyes értékével, és így kapunk egy új tömböt, így: {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}): A SUMPRODUCT függvény végül összeadja az összes számot ebben a tömbben, és az eredményt 2386-ként adja vissza.

Használt relatív függvény:

  • SUMIFS:
  • Az Excel SUMIFS függvénye segíthet a cellaértékek több feltétel alapján történő összegzésében.
  • SUMPRODUCT:
  • A SUMPRODUCT funkcióval két vagy több oszlop vagy tömb összeszorozható, majd megkapjuk a szorzat összegét.
  • DÁTUM:
  • A DÁTUM függvény segíthet abban, hogy a külön cellákból származó évet, hónapot és napot egy érvényes dátummal kombináljuk.
  • ÉV:
  • Az ÉV függvény az adott dátum alapján négy számjegyű sorozatszám formátumban adja vissza az évet.

További cikkek:


A legjobb irodai termelékenységi eszközök

Kutools for Excel - segít kitűnni a tömegből

Szeretnéd gyorsan és tökéletesen elvégezni mindennapi munkádat? A Kutools for Excel 300 hatékony speciális funkciót kínál (munkafüzetek összevonása, színenkénti összegzés, cellatartalom felosztása, dátum konvertálása stb.), és 80%-os időt takaríthat meg Önnek.

  • 1500 munkaforgatókönyvhöz tervezve, segít megoldani az Excel problémák 80%-át.
  • Csökkentse a napi billentyűzet és egér kattintások ezreit, enyhítse fáradt szemét és kezét.
  • Legyen Excel szakértő 3 perc alatt. Nem kell többé emlékezni semmilyen fájdalmas képletre és VBA kódra.
  • 30 napos korlátlan ingyenes próbaidőszak. 60 napos pénzvisszafizetési garancia. Ingyenes frissítés és támogatás 2 évre.
Az Excel szalagja (telepítve a Kutools for Excel programmal)

Office fül - Füles olvasás és szerkesztés engedélyezése a Microsoft Office alkalmazásban (beleértve az Excel programot is)

  • Egy másodperc a tucatnyi nyitott dokumentum közötti váltáshoz!
  • Csökkentse az egérkattintások százait az Ön számára minden nap, búcsút mondjon az egér kezének.
  • 50% -kal növeli a termelékenységet több dokumentum megtekintésekor és szerkesztésekor.
  • Hatékony lapokat hoz az Office-ba (beleértve az Excel-t is), akárcsak a Chrome-ot, a Firefox-ot és az új Internet Explorert.
Az Excel képernyőképe (az Office Tab telepítve)
A megjegyzések rendezése szerint
Hozzászólások (0)
Még nincs értékelés. Legyen Ön az első, aki értékel!
Még senki sem írt megjegyzést
Hagyja meg észrevételeit
Feladás vendégként
×
Értékeld ezt a bejegyzést:
0   Karakterek
Javasolt helyek

Kövess minket

Copyright © 2009 - www.extendoffice.com. | Minden jog fenntartva. Powered by ExtendOffice. | Oldaltérkép
A Microsoft és az Office logó a Microsoft Corporation védjegyei vagy bejegyzett védjegyei az Egyesült Államokban és / vagy más országokban.
Sectigo SSL védi