É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
- Értékek összegzése évenként Excelben SUMPRODUCT funkcióval
É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:
- 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:
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:
- 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:
- Összesített értékek havi bontásban (évvel vagy év nélkül)
- Ez az oktatóanyag két képletet mutat be részletesen, amelyek segítenek az értékek összegzésében bizonyos hónapok és évszámok szerint, vagy az értékek bizonyos hónapok szerinti összegzése, figyelmen kívül hagyva az évet az Excelben.
- Értékek összege egy adott dátum hetében
- Az értékek összegzéséhez azon hét szerint, amelyre egy adott dátum esik, alkalmazhat egy képletet a SUMIFS függvényen.
- Összesített értékek hétszám vagy hétköznap szerint
- Ez az oktatóanyag lépésről lépésre bemutatja a képleteket, amelyek segítenek összegezni az értékeket egy bizonyos tartományban az Excelben megadott hétszám vagy hétnap alapján.
A legjobb irodai termelékenységi eszközök
Kutools for Excel - segít kitűnni a tömegből
A Kutools for Excel több mint 300 funkcióval büszkélkedhet, Győződjön meg arról, hogy amire szüksége van, csak egy kattintásnyira van...
Office lap – A lapos olvasás és szerkesztés engedélyezése a Microsoft Office programban (beleértve az Excelt 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 Excelt is), akárcsak a Chrome, az Edge és a Firefox.
