7.1 lecke · 1/4 · kb. 4 perc
Összeadás: SZUM, RÉSZÖSSZEG, ÖSSZESÍT, SZORZATÖSSZEG
Az összeadás az Excel leggyakoribb művelete, és nem csak egy függvény való rá. Ebben a leckében megnézzük, mikor elég a SZUM, és mikor van szükség a RÉSZÖSSZEG, az ÖSSZESÍT vagy a SZORZATÖSSZEG függvényre.
Hat összeadó függvény
A mindennapi munkához az Excel hat összeadó függvényt kínál:
| Függvény | Mire való? |
|---|---|
SZUM (SUM) | egyszerű összeadás |
RÉSZÖSSZEG (SUBTOTAL) | összeadás csak a látható sorokból, részösszeges táblázatokban |
ÖSSZESÍT (AGGREGATE) | összeadás a rejtett sorok és a hibaértékek kihagyásával |
SZUMHA (SUMIF) | összeadás egy feltétellel |
SZUMHATÖBB (SUMIFS) | összeadás több feltétellel |
SZORZATÖSSZEG (SUMPRODUCT) | párok szorzása, majd a szorzatok összeadása |
Ebben a leckében az első három és az utolsó kerül sorra. A feltételes összeadásnak (SZUMHA, SZUMHATÖBB) külön lecke jut.
A SZUM részletei
A SZUM argumentumai lehetnek számok, cella- és tartományhivatkozások vagy képletek, legfeljebb 255 darab. Egy finomság: a hivatkozott cellákban álló logikai értékeket és szövegként tárolt számokat kihagyja, a közvetlenül argumentumként megadottakat viszont beszámítja.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | szám | számok | logikai | szöveg |
| 2 | 100 | 200 | IGAZ | 11 |
| 3 | 300 | |||
| 4 | 400 |
A D2 cellában a 11 szövegként van tárolva (balra igazodik).
| Képlet | Eredmény | Miért? |
|---|---|---|
=SZUM(A2;B2:B4;C2:D2) | 1000 | a C2 logikai értékét és a D2 szövegét kihagyja |
=SZUM(A2;B2:B4;IGAZ;"1") | 1002 | a közvetlenül megadott IGAZ 1-et ér, a "1" számmá alakul |
Figyelem
A SZUM a szűréssel elrejtett és a kézzel elrejtett sorok számait is összeadja. Ha egy szűrt táblázat alján SZUM áll, az eredmény nem a látható sorok összege lesz.
Csak a látható sorok: RÉSZÖSSZEG és ÖSSZESÍT
RÉSZÖSSZEG(függv_szám; hivatkozás1; [hivatkozás2]; …)ÖSSZESÍT(függv_szám; beállítások; hivatkozás1; …)Mindkét függvény több műveletre is képes (átlag, darab, max stb.). Az első argumentum, a függvényszám mondja meg, melyik műveletet végezze. Összeadáshoz:
RÉSZÖSSZEG(9;…): kihagyja a szűréssel elrejtett sorokat, de a kézzel elrejtett sorokat beszámítja.RÉSZÖSSZEG(109;…): a szűrt és a kézzel elrejtett sorokat is kihagyja.ÖSSZESÍT(9;5;…): a 9 itt is az összeadás, az 5-ös beállítás pedig azt jelenti, hogy minden rejtett sort kihagy, akár szűrés, akár kézi elrejtés miatt nem látszik.
Példa: egy hét napi bevétele áll a B2:B8 tartományban (hétfőtől vasárnapig 120, 95, 130, 110, 150, 80, 60 ezer Ft, összesen 745). A szombatot és a vasárnapot elrejted. Mit adnak a függvények?
| Képlet | Szűréssel elrejtve | Kézzel elrejtve |
|---|---|---|
=SZUM(B2:B8) | 745 | 745 |
=RÉSZÖSSZEG(9;B2:B8) | 605 | 745 |
=RÉSZÖSSZEG(109;B2:B8) | 605 | 605 |
=ÖSSZESÍT(9;5;B2:B8) | 605 | 605 |
Hibaértékek átugrása
Ha a tartományban akár egyetlen hibaérték (például #HIÁNYZIK) is van, a SZUM eredménye is hiba lesz. Az ÖSSZESÍT a második argumentumával erre is utasítható:
- 5: a rejtett sorokat hagyja ki;
- 6: a hibaértékeket hagyja ki;
- 7: a rejtett sorokat és a hibaértékeket is kihagyja.
Részösszeges táblázat
Gyakori, hogy egy oszlopban részösszegek is állnak, alul pedig a végösszeg. Ha a részösszegeket SZUM-mal számolod, a végösszeg SZUM-ja őket is hozzáadja, és dupla eredményt kapsz.
A RÉSZÖSSZEG viszont kihagyja a tartományában álló többi RÉSZÖSSZEG-et. Ezért a megoldás: minden összegző cellában a RÉSZÖSSZEG függvényt használd.
| A | B | C | |
|---|---|---|---|
| 1 | eladás (db) | képlet | |
| 2 | január | 120 | |
| 3 | február | 135 | |
| 4 | március | 150 | |
| 5 | I. negyedév | 405 | =RÉSZÖSSZEG(9;B2:B4) |
| 6 | április | 160 | |
| 7 | május | 170 | |
| 8 | június | 140 | |
| 9 | II. negyedév | 470 | =RÉSZÖSSZEG(9;B6:B8) |
| 10 | I. félév | 875 | =RÉSZÖSSZEG(9;B2:B9) |
A B10 cella a B2:B9 tartományt összegzi, de a B5 és a B9 részösszegét kihagyja.
Ha a B5 és a B9 cellában SZUM állna, a =SZUM(B2:B9) végösszeg 1750 lenne, vagyis a félév kétszerese. Figyelj: a SZUM-mal számolt részösszegeket sem a RÉSZÖSSZEG, sem az ÖSSZESÍT nem ismeri fel, ezért azokat cseréld le.
SZORZATÖSSZEG: szorzás és összeadás egyben
SZORZATÖSSZEG(tömb1; [tömb2]; …)A SZORZATÖSSZEG az azonos pozícióban álló számokat összeszorozza, majd a szorzatokat összeadja. Klasszikus felhasználása: darabszám × egységár, végösszeg egy lépésben, segédoszlop nélkül.
| A | B | C | |
|---|---|---|---|
| 1 | tétel | darab | egységár (Ft) |
| 2 | toll | 12 | 250 |
| 3 | füzet | 8 | 690 |
| 4 | mappa | 5 | 1 290 |
| 5 | ragasztó | 3 | 480 |
| 6 | végösszeg | 16 410 |
A C6 cella képlete: =SZORZATÖSSZEG(B2:B5;C2:C5)
A számítás: 12 × 250 + 8 × 690 + 5 × 1 290 + 3 × 480 = 3 000 + 5 520 + 6 450 + 1 440 = 16 410 Ft. A tömböknek azonos méretűeknek kell lenniük, különben #ÉRTÉK! hibát kapsz.
Összefoglalás
- A SZUM a rejtett és a szűrt sorokat is összeadja; hivatkozásban a logikai értéket és a szövegként tárolt számot kihagyja.
RÉSZÖSSZEG(9;…)a szűrt sorokat hagyja ki,RÉSZÖSSZEG(109;…)a kézzel elrejtetteket is.ÖSSZESÍT(9;5;…): rejtett sorok nélkül,6: hibaértékek nélkül,7: mindkettő nélkül.- Részösszeges táblázatban minden összegző cellába RÉSZÖSSZEG kerüljön, mert az kihagyja a többi RÉSZÖSSZEG-et.
- A SZORZATÖSSZEG párban szoroz és összead:
=SZORZATÖSSZEG(B2:B5;C2:C5).
Gyakorlás
Töltsd le a gyakorló munkafüzetet, és oldd meg a megadott munkalapok feladatait. A sárga cellákba kerülnek a képletek. Ha elakadsz, nézd meg a megoldást.
Összesítés és megszámlálás: gyakorló feladatok
- Raktár Készletérték SZORZATÖSSZEG-gel, szűrt összeg RÉSZÖSSZEG-gel, hibát átugró ÖSSZESÍT.