Összegzés, ha a cellák meghatározott szöveget tartalmaznak egy másik oszlopban
Ez az oktatóanyag megmutatja, hogyan kell összegezni az értékeket, ha a cellák egy másik oszlopban meghatározott vagy részleges szöveget tartalmaznak. Vegyük példának az alábbi adattartományt, hogy a „Póló” szöveget tartalmazó termékek összmennyiségét megkaphassuk, a SUMIF függvény és a SZUMPRODUCT függvény is meg tudja oldani ezt a feladatot az Excelben.
- Értékek összegzése, ha a cella meghatározott vagy részleges szöveget tartalmaz SUMIF funkcióval
- Összegértékek, ha a cella meghatározott vagy részleges szöveget tartalmaz SUMPRODUCT funkcióval
Értékek összegzése, ha a cella meghatározott vagy részleges szöveget tartalmaz SUMIF funkcióval
Az értékek összegzéséhez, ha egy cella adott szöveget tartalmaz egy másik oszlopban, használhatja a SUMIF függvényt helyettesítő karakterrel (*), az általános szintaxisok a következők:
Általános képlet, kódolt szöveggel:
- range: A kritériumok alapján értékelni kívánt adattartomány;
- *text*: Az a kritérium, amely alapján az értékeket összegezni szeretné. Itt a * helyettesítő karakterrel tetszőleges számú karaktert találhat, hogy megfeleljen az összes olyan elemnek, amely egy adott szöveget tartalmaz, helyezze a szöveget a két * karakter közé. (Vegye figyelembe, hogy a szöveget és a helyettesítő karaktert dupla idézőjelbe kell tenni.)
- sum_range: Az egyező numerikus értékekkel rendelkező cellák tartománya, amelyet összegezni szeretne.
Általános képlet cellahivatkozással:
- range: A kritériumok alapján értékelni kívánt adattartomány;
- "*"&cell&"*": A kritériumok, amelyek alapján az értékeket összegezni kívánja;
- *: A helyettesítő karakter, amely tetszőleges számú karaktert megtalál.
- sejt: A cella tartalmazza a keresendő konkrét szöveget.
- &: Ez az összefűzési operátor (&) a cellahivatkozások csillagokkal való összekapcsolására szolgál.
- sum_range: Az egyező numerikus értékekkel rendelkező cellák tartománya, amelyet összegezni szeretne.
Miután megismerte a függvény alapvető működését, használja az alábbi képletek bármelyikét, majd nyomja meg a gombot belép kulcs az eredmény eléréséhez:
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12) (Use a cell reference)
Megjegyzések: Ez a SUMIF funkció nem érzékeny a kis- és nagybetűkre.
Összegértékek, ha a cella meghatározott vagy részleges szöveget tartalmaz SUMPRODUCT funkcióval
A SUMPRODUCT funkció abban is segíthet, hogy a feladatot helyettesítő karakterek használata nélkül fejezze be. Itt az ISNUMBER és SEARCH függvényeket egy SUMPRODUCT függvénybe kell csomagolni, az általános szintaxis a következő:
- sum_range: Az egyező numerikus értékekkel rendelkező cellák tartománya, amelyet összegezni szeretne;
- criteria: Az a kritérium, amely alapján az értékeket összegezni szeretné. Lehet cellahivatkozás vagy meghatározott szöveg, amelyet definiált;
- range: A kritériumok alapján értékelni kívánt adattartomány;
Kérjük, használja az alábbi képletek bármelyikét egy üres cellába, és nyomja meg a gombot belép kulcs az eredmény visszaadásához:
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12)))) (Use a cell reference)
Ennek a képletnek a magyarázata:
=SZUMTERMÉK($B$2:$B$12*(ISNUMBER(KERESÉS("Póló",$A$2:$A$12))))
- KERESÉS ("Póló", 2 $: 12 $): A SEARCH függvény visszaadja az A2:A12 adattartományból a „T-Shirt” konkrét szöveg helyét, így egy ilyen tömböt kapunk: {5;#ÉRTÉK!;#ÉRTÉK!;7;#ÉRTÉK! ;7;#ÉRTÉK!;#ÉRTÉK!;#ÉRTÉK!;#ÉRTÉK!;7}.
- ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Ez az ISNUMBER függvény a numerikus értékek tesztelésére szolgál, és egy új tömböt ad vissza: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
- $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: A két tömb megszorzásához a matematikai művelet az IGAZ és a HAMIS értékeket 1-re és 0-ra kényszeríti. Tehát a két tömb szorzása a következőképpen alakul: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1; 0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
- SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):Végül a SUMPRODUCT függvény összegzi a tömb összes értékét, így kapja meg a 1605 eredményt.
Használt relatív függvény:
- SUMIF:
- A SUMIF függvény segíthet a cellák egy kritérium 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.
- SZÁM:
- Az Excel ISNUMBER függvény IGAZ értéket ad vissza, ha egy cella számot tartalmaz, és hamis, ha nem.
- KERESÉS:
- A KERESÉS funkció segítségével megkeresheti egy adott karakter vagy részsor pozícióját az adott szöveges karakterláncból
További cikkek:
- Összegzés, ha meghatározott szöveggel vagy karakterrel kezdődik, vagy azzal végződik
- Az értékek összegzéséhez, ha a megfelelő cellák egy adott értékkel kezdődnek vagy azzal végződnek, a SUMIF függvényt helyettesítő karakterrel (*) alkalmazhatja a kihúzáshoz. Ez a cikk részletesen bemutatja a képlet használatát.
- Összeg A legkisebb vagy alsó N érték
- Az Excelben a SUM függvény segítségével könnyen összefoglalhatunk egy sejttartományt. Néha előfordulhat, hogy össze kell adnia a legkisebb vagy alsó 3, 5 vagy n számot egy adattartományban, ahogy az alábbi képernyőképen látható. Ebben az esetben a SUMPRODUCT a SMALL funkcióval együtt segíthet megoldani ezt a problémát az Excelben.
- Összeg A legkisebb vagy alsó N értékek kritériumok alapján
- Az előző oktatóanyagban megbeszéltük, hogyan összegezhetjük egy adattartomány legkisebb n értékét. Ebben a cikkben egy további speciális műveletet hajtunk végre – a legalacsonyabb n értékek összegzésére egy vagy több feltétel alapján az Excelben.
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.
