Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan kell használni a SUMIFS függvényt és a helyettesítő karaktereket az Excelben és a Google Táblázatokban meghatározott szöveget tartalmazó celláknak megfelelő adatok összegzéséhez.
Összeg, ha a szöveg tartalmaz
Először bemutatjuk, hogyan lehet a SUMIFS függvény segítségével összegezni az adott szöveget tartalmazó cellákhoz kapcsolódó adatokat.
A SUMIFS függvény összesíti azokat az adatsorokat, amelyek megfelelnek bizonyos feltételeknek. Szintaxisa a következő:
Ez a példa mindent összefoglal Pontszámok val,-vel Állam neve amely a „Dakota” -t tartalmazza a SUMIFS függvény és a * helyettesítő karakter használatával.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
A * karakter lehetővé teszi, hogy tetszőleges számú (beleértve a nullát) más karakter lépjen a helyére.
Ebben a példában minden olyan cella megtalálására szolgál, amely tartalmazza a „Dakota” szöveget. Ez a keresés nem különbözteti meg a kis- és nagybetűket, ezért a „dakota” azonos a „Dakota” vagy „DAKOTA” kifejezésekkel. Észak-Dakota és South Dakota „Dakota” -t tartalmaznak, ezért szerepelnek az összegben.
Összeg, ha a szöveg ezzel kezdődik
A * karakter használható a megadott szöveggel kezdődő cellák keresésére is:
| 1 | = SUMIFS (C3: C9, B3: B9, "Új*") |
New York, New Jersey, és Új-Mexikó kezdje az „Új” szóval, és ezért szerepel az összegben. Ne feledje, hogy az „Új*” keresési kifejezéssel a szövegcellának „Új” betűvel kell kezdődnie; nem elegendő csupán ezeket a karaktereket tartalmazni.
Összeg, ha a szöveg véget ér
Hasonlóképpen összegezhetünk mindent Pontszámok számára Államok „o” betűvel végződik a következő használatával:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Új-Mexikó és Ohio „o” betűvel végződnek, ezért szerepelnek az összegben.
Használni a ? Helyettesítő karakter
Az ? karakter használható bármely karakter megjelenítésére egy szöveges karakterláncban.
Ez a példa mindent megtalál Állam nevek kezdve „Új”, majd pontosan 7 karakter (szóközökkel együtt).
| 1 | = SUMIFS (C3: C9, B3: B9, "Új ???????") |
New Jersey és Új-Mexikó megfelel ezeknek a kritériumoknak, de New York nem, mivel csak 5 karakter követi az „Új” betűt New York.
Vegye figyelembe, hogy * és? a helyettesítő karakterek kombinálhatók, ha szükséges, nagyon specifikus keresési parancsok készítéséhez. Ez a következő példa talál Állam nevek amelyek „N” betűvel kezdődnek, és a karakterlánc utolsó karaktere előtt „o” karaktert tartalmaznak. Ez kizárja Új-Mexikó; „N” betűvel kezdődik, de az utolsó karakter előtt nincs „o”.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Ez a funkció különösen akkor hasznos, ha termékkódok, irányítószámok vagy sorozatszámok között keres, ahol az egyes karakterek pozíciójának meghatározott jelentése van.
A ~ (Tilde) karakter használata
A ~ különleges karakter (a tilde) lehetővé teszi számunkra a * vagy? karaktereket, mintha egyszerű szöveges értékek lennének, és nem viselkednek helyettesítő karakterekként.
Az alábbi példában össze kell foglalnunk a Állományszint amikor az termék név kifejezetten megfelel a „Termék?” szövegnek:
| 1 | = SUMIFS (C3: C8, B3: B8, "Termék ~?") |
A ~ közvetlenül a * vagy a? karakter szöveges értékké változtatja, így a „Termék ~?” keresési kifejezés a „Termék?” szöveggel való pontos egyezés megtalálására szolgál.
SUMIFS helyettesítő karakterek és cellahivatkozások kombinálása
Általában nem helyes gyakorlat, ha az értékeket képletekbe kódolják. Ehelyett rugalmasabb, ha külön cellákat használunk a keresési kifejezések értékeinek megadásához.
Annak megkereséséhez, hogy a cellák tartalmazzák -e az E3 cellában található szöveget, használhatjuk a SUMIFS függvényt cellahivatkozással és * helyettesítő karakterekkel:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Ne feledje, hogy a „Dakota” szöveg helyére az & E3 & cellahivatkozás lép, és a * karakterek idézőjelek közé kerülnek („”).
Több cellahivatkozás és helyettesítő karakter is kombinálható. Megtalálni Állam nevek amelyek az E3 cella szövegével kezdődnek, és az F3 cellában található szöveget követik, amelyet legalább 1 további karakter követ, a következő képlet használható:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Cellareferenciák zárolása
A képleteink könnyebb olvashatósága érdekében megmutattuk a képleteket zárolt cellahivatkozások nélkül:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
De ezek a képletek nem fognak megfelelően működni, ha másolja és beilleszti a fájl máshová. Ehelyett a következőképpen kell zárolt cellahivatkozásokat használni:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
További információért olvassa el a Cellareferenciák zárolása című cikkünket.
Összegzés, ha a cella adott szöveget tartalmaz, helyettesítő karaktereket használva a Google Táblázatokban
Ezek a képletek pontosan ugyanúgy működnek a Google Táblázatokban, mint az Excelben.
