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

Ö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

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:

=SUMIF(range,"*text*",sum_range)
  • 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:

=SUMIF(range,"*"&cell&"*",sum_range)
  • 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,"*T-shirt*",$B$2:$B$12)                     (Type the criteria manually)
=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ő:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • 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("T-Shirt",$A$2:$A$12))))          (Type the criteria manually)
=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:

  • Ö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

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