Stiahnite si ukážkový zošit
Tento tutoriál predvedie, ako použiť funkciu SUMIFS a zástupné znaky na súčet údajov zodpovedajúcich bunkám, ktoré obsahujú konkrétny text v Exceli a Tabuľkách Google.
Suma, ak text obsahuje
Najprv si ukážeme, ako pomocou funkcie SUMIFS zhrnúť údaje súvisiace s bunkami obsahujúcimi konkrétny text.
Funkcia SUMIFS sumarizuje riadky údajov, ktoré spĺňajú určité kritériá. Jeho syntax je:
Tento príklad zhrnie všetko Výsledky s Štátny názov ktorý obsahuje „Dakota“ pomocou funkcie SUMIFS a zástupného znaku *.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
Znak * umožňuje nahradiť akékoľvek číslo (vrátane nuly) iných znakov.
V tomto prípade sa používa na vyhľadanie všetkých buniek obsahujúcich text „Dakota“. Pri tomto vyhľadávaní sa nerozlišujú malé a veľké písmená, takže „dakota“ sa považuje za to isté ako „Dakota“ alebo „DAKOTA“. Severná Dakota a Južná Dakota obsahujú „Dakota“, a preto sú zahrnuté v sume.
Suma, ak sa text začína na
Znak * je možné použiť aj na vyhľadanie buniek, ktoré začínajú zadaným textom:
| 1 | = SUMIFS (C3: C9, B3: B9, "Nový*") |
New York, New Jerseya Nové Mexiko začínajú na „Nové“, a preto sú zahrnuté v sume. Upozorňujeme, že v prípade hľadaného výrazu „Nový*“ musí textová bunka začínať na „Nové“; nestačí iba obsahovať tieto znaky.
Suma, ak sa text končí na
Podobne môžeme zhrnúť všetky Výsledky pre Štátov končiace na „o“ pomocou:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Nové Mexiko a Ohio končia na „o“, a preto sú zahrnuté v sume.
Pomocou? Zástupný znak
The? znak môže byť použitý na reprezentáciu ľubovoľného jedného znaku v textovom reťazci.
Tento príklad nájde všetko Názvy štátov počnúc „novým“, za ktorým nasleduje presne 7 znakov (vrátane medzier).
| 1 | = SUMIFS (C3: C9, B3: B9, „Nové ???????“) |
New Jersey a Nové Mexiko spĺňať tieto kritériá, ale New York nemá, pretože za „Novinkou“ v je iba 5 znakov New York.
Všimnite si toho * a? V prípade potreby je možné kombinovať zástupné znaky a vytvárať tak veľmi špecifické vyhľadávacie príkazy. Tento nasledujúci príklad nájde Názvy štátov ktoré začínajú na „N“ a obsahujú „o“ pred posledným znakom reťazca. To vylučuje Nové Mexiko; začína sa na „N“, ale nemá písmeno „o“ pred posledným znakom.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Táto funkcia je obzvlášť užitočná pri vyhľadávaní kódov produktov, PSČ alebo sériových čísel, kde pozícia každého znaku má špecifický význam.
Použitie znaku ~ (vlnovka)
Špeciálny znak ~ (známy ako a vlnovka) nám umožňuje liečiť * alebo? znaky, ako keby išlo o jednoduché textové hodnoty, a nesprávajú sa ako zástupné znaky.
V nižšie uvedenom príklade musíme zhrnúť Úroveň zásob keď Meno Produktu sa konkrétne zhoduje s textom „Produkt?“:
| 1 | = SUMIFS (C3: C8, B3: B8, "Product ~?") |
~ Bezprostredne pred znakom * alebo a? znak ho zmení späť na textovú hodnotu, takže hľadaný výraz „Produkt ~?“ sa používa na nájdenie presnej zhody textu s názvom „Produkt?“.
Kombinácia zástupných znakov SUMIFS s referenciami na bunky
Obvykle nie je vhodné zafixovať hodnoty do vzorcov. Namiesto toho je flexibilnejšie použiť samostatné bunky na zadanie hodnôt pre naše hľadané výrazy.
Ak chcete zistiť, či bunky obsahujú text uložený v bunke E3, môžeme použiť funkciu SUMIFS s odkazom na bunku a zástupnými znakmi *:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Upozorňujeme, že text „Dakota“ bol nahradený odkazom na bunku & E3 & a znaky * boli vložené do úvodzoviek („“).
Tiež je možné kombinovať viacero odkazov na bunky a zástupné znaky. Nájsť Názvy štátov ktoré začínajú textom v bunke E3 a obsahujú text v bunke F3, po ktorom nasleduje najmenej 1 ďalší znak, je možné použiť nasledujúci vzorec:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Zamknutie odkazov na bunky
Aby boli naše vzorce ľahšie čitateľné, ukázali sme vzorce bez uzamknutých odkazov na bunky:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Tieto vzorce však nebudú fungovať správne, keď ich skopírujete a vložíte na iné miesto v súbore. Namiesto toho by ste mali použiť uzamknuté odkazy na bunky takto:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, „*“ & E3 & „*“) |
Ak sa chcete dozvedieť viac, prečítajte si náš článok o uzamknutí odkazov na bunky.
Súčet, ak bunka obsahuje konkrétny text pomocou zástupných znakov v Tabuľkách Google
Tieto vzorce fungujú v Tabuľkách Google úplne rovnako ako v programe Excel.
