7.2 lecke · 2/4 · kb. 6 perc
Feltételes összeadás: SZUMHA, SZUMHATÖBB
Gyakran nem az összes számot kell összeadnod, csak azokat, amelyek valamilyen feltételnek megfelelnek: a váci eladásokat, a 100 000 Ft feletti tételeket, a márciusi számlákat. Erre való a SZUMHA és a SZUMHATÖBB. A legfontosabb tudnivaló a feltétel helyes írásmódja.
A SZUMHA felépítése
SZUMHA(tartomány; kritérium; [összeg_tartomány])- tartomány: azok a cellák, amelyekben a feltételt vizsgálni kell;
- kritérium: a feltétel, vagyis egy reláció és egy viszonyítási alap (például
">100000"); - összeg_tartomány: az összeadandó számok cellái. Ha a feltétel magukra az összeadandó számokra vonatkozik, ezt elhagyhatod.
Ebben a leckében végig ezt a táblázatot használjuk:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | üzletkötő | város | eladás (Ft) | akció |
| 2 | Hegyi Félix | Vác | 125 000 | nyári |
| 3 | Szőke Petra | Érd | 84 500 | |
| 4 | Kovács Dénes | Vác | 210 000 | |
| 5 | Takács Rita | Pécs | 56 000 | nyári |
| 6 | Bodnár Imre | Érd | 132 000 | |
| 7 | Fazekas Lili | Vác | 47 500 | |
| 8 | Molnár Ádám | Pécs | 98 000 | őszi |
Az eladások összege 753 000 Ft.
Feltétel a számokra
Mennyi a 100 000 Ft feletti eladások összege? A feltétel magukra az összeadandó számokra vonatkozik, ezért elég két argumentum:
Eredmény: 125 000 + 210 000 + 132 000 = 467 000.
Feltétel egy másik oszlopra
Mennyi a váci eladások összege? Most a feltétel a városra vonatkozik, az összeadandó számok viszont a C oszlopban vannak:
Eredmény: 125 000 + 210 000 + 47 500 = 382 500. A függvény a B2:B8 minden cellájánál megnézi, „Vác” áll-e benne, és ha igen, hozzáadja a C oszlop ugyanabban a sorban álló számát.
A kritérium írásmódja
A feltétel két részből áll: reláció és viszonyítási alap. Az egyenlőséget nem kell jelölni: a reláció nélküli feltétel azonosságot jelent. A relációs jelek: =, <> (nem egyenlő), <, >, <=, >=. A kétkarakteres jelek sorrendje kötött: =< vagy >< nem működik.
| Feltétel | Írásmód | Eredmény a példán |
|---|---|---|
| egyenlő egy szöveggel | "Vác" | 382 500 |
| egyenlő egy számmal | 98000 vagy "98000" | 98 000 (C2:C8-ra) |
| nem egyenlő | "<>Vác" | 370 500 |
| nagyobb, kisebb stb. | ">100000", "<=50000" | 467 000; 47 500 |
| cellában lévő értékhez | ">"&H1 | H1 tartalmától függ |
| képlet eredményéhez | "<"&ÁTLAG(C2:C8) | 286 000 |
| dátumhoz | ">=2025.01.01" vagy ">="&DÁTUM(2025;1;1) | (dátumoszlopra) |
| bármilyen szöveg | "*" | szöveget tartalmazó cellák |
| üres cella | "" | 474 000 (D2:D8-ra) |
| nem üres cella | "<>" | 279 000 (D2:D8-ra) |
Mikor kell idézőjel?
A feltétel idézőjelek között áll. Kivétel, ha a reláció egyenlőség, és a viszonyítási alap szám, logikai érték, cellahivatkozás vagy képlet: ilyenkor az idézőjel elhagyható (98000, IGAZ, H1).
Reláció cellával vagy képlettel: az & jel
Ha a viszonyítási alap cellában áll vagy képlet adja, a relációs jel idézőjelbe kerül, és az & összefűző jel kapcsolja hozzá a hivatkozáshoz. Így a határt nem kell a képletbe beégetned, egyetlen cella átírásával módosíthatod.
Gyakori hiba
A ">H1" nem a H1 cella tartalmához hasonlít, hanem a „H1” szöveghez. A hivatkozás mindig az idézőjelen kívül, & jellel csatlakozik: ">"&H1.
A példában az átlag 753 000 / 7 ≈ 107 571 Ft, ennél kisebb eladás négy van: 84 500 + 56 000 + 47 500 + 98 000 = 286 000.
Számok és dátumok a feltételben
- A szám tartalmazhat tizedesvesszőt és százalékjelet (
">0,35"vagy">35%"), de pénznemet (Ft, HUF) ne írj bele. - Egyenlőségnél a pontos érték számít: ha a feltétel 1,2, akkor sem az 1,20001, sem az 1,199 nem felel meg neki.
- A dátumban az egységeket ponttal, kötőjellel vagy perjellel választhatod el, az időben kettősponttal. Az óra:perc alakú idő a feltételben 0 másodperccel egészül ki, ezt egyenlőségnél vedd figyelembe.
Helyettesítő karakterek
Szöveges feltételben két helyettesítő karaktert használhatsz. A kis- és nagybetűt a függvény nem különbözteti meg.
| Karakter | Jelentése | Példa | Kire illik? |
|---|---|---|---|
* | tetszőleges számú karakter (nulla is) | "P*" | Pécs, Pomáz, P |
? | pontosan egy karakter | "?r?" | Érd (de a Vác nem) |
~*, ~? | maga a csillag, illetve kérdőjel | "*~?" | kérdőjelre végződő szöveg |
Ez az „Sz” betűkkel kezdődő nevű üzletkötők eladásait adja össze: 84 500 (Szőke Petra). A "*" feltétel önmagában minden szöveget tartalmazó cellára illik; tagadása, a "<>*" minden nem szöveges cellára, az üreseket is beleértve.
Üres és nem üres cellák
Az üres cellát nulla hosszúságú szöveggel (""), a tartalommal rendelkezőt pedig a viszonyítási alap nélküli nem egyenlő relációval ("<>") adod meg.
Az akció nélküli eladások összege: 84 500 + 210 000 + 132 000 + 47 500 = 474 000. A =SZUMHA(D2:D8;"<>";C2:C8) az akciós eladásokat adja: 125 000 + 56 000 + 98 000 = 279 000. A kettő együtt kiadja a 753 000-et.
Több feltétel: SZUMHATÖBB
SZUMHATÖBB(összeg_tartomány; kritériumtartomány1; kritérium1; [kritériumtartomány2; kritérium2]; …)A SZUMHATÖBB legfeljebb 127 feltételpárt fogad el. Egy szám akkor kerül az összegbe, ha a sorában minden feltétel teljesül. A kritériumok írásmódja ugyanaz, mint a SZUMHA-nál.
Figyelem: más az argumentumok sorrendje
A SZUMHATÖBB-nél az összeg_tartomány az első argumentum, a SZUMHA-nál az utolsó. Ha SZUMHA-t alakítasz át SZUMHATÖBB-re, ezt mindig rendezd át.
Mennyi a váci, 100 000 Ft feletti eladások összege?
Vácon három eladás van, ebből kettő haladja meg a 100 000 Ft-ot: 125 000 + 210 000 = 335 000. Ahogy látod, az összeg_tartomány egyben kritériumtartomány is lehet.
A tartományoknak azonos méretűeknek kell lenniük. A SZUMHA ebben elnéző (az összeg_tartománynak csak a bal felső celláját veszi figyelembe, és a méretet az első argumentumhoz igazítja), a SZUMHATÖBB viszont eltérő méretű tartományokra #ÉRTÉK! hibát ad. Ez jó: a hibát rögtön észreveszed.
Összefoglalás
SZUMHA(tartomány; kritérium; [összeg_tartomány]): egy feltétellel ad össze.SZUMHATÖBB(összeg_tartomány; tartomány1; kritérium1; …): több feltétellel, és itt az összeg_tartomány áll elöl.- A feltétel idézőjelek között áll; cellához vagy képlethez
&kapcsolja a relációt:">"&H1. - Helyettesítő karakterek:
*(bármennyi karakter),?(egy karakter),~(maga a jel). - Üres cella:
""; nem üres cella:"<>".
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
- Megtakarítások Fiatalok megtakarításai városonként és nemenként: SZUMHA és SZUMHATÖBB.