Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak pomocí funkce SUMIFS a zástupných znaků sečíst data odpovídající buňkám, které obsahují konkrétní text v Excelu a Google Sheets.
Součet Pokud text obsahuje
Nejprve si předvedeme, jak pomocí funkce SUMIFS sečíst data související s buňkami obsahujícími konkrétní text.
Funkce SUMIFS shrnuje datové řádky, které splňují určitá kritéria. Jeho syntaxe je:
Tento příklad sečte všechny Skóre s Název státu který obsahuje „Dakota“ pomocí funkce SUMIFS a zástupného znaku *.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
Znak * umožňuje, aby místo zaujal libovolný počet (včetně nuly) jiných znaků.
V tomto příkladu se používá k vyhledání všech buněk obsahujících text „Dakota“. Toto vyhledávání nerozlišuje velká a malá písmena, takže „dakota“ je považováno za stejné jako „Dakota“ nebo „DAKOTA“. Severní Dakota a Jižní Dakota obsahují „Dakota“, a jsou proto zahrnuty v součtu.
Součet, pokud text začíná na
Znak * lze také použít k vyhledání buněk, které začínají zadaným textem:
| 1 | = SUMIFS (C3: C9, B3: B9, "New*") |
New York, New Jersey, a Nové Mexiko začínají na „Nové“, a jsou proto zahrnuty v součtu. U hledaného výrazu „New*“ musí textová buňka začínat „New“; pouhé obsazení těchto znaků nestačí.
Součet, pokud text končí na
Podobně můžeme shrnout všechny Skóre pro Státy končící na „o“ pomocí:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Nové Mexiko a Ohio končí na „o“, a jsou proto zahrnuty v součtu.
Za použití ? Zástupný znak
? znak lze použít k reprezentaci libovolného znaku v textovém řetězci.
Tento příklad najde vše Státní jména počínaje „novým“, za nímž následuje přesně 7 znaků (včetně mezer).
| 1 | = SUMIFS (C3: C9, B3: B9, "Nové ???????") |
New Jersey a Nové Mexiko splňovat tato kritéria, ale New York nemá, protože za „New“ v je pouze 5 znaků New York.
Všimněte si, že * a? zástupné znaky lze v případě potřeby kombinovat a vytvářet tak velmi specifické vyhledávací příkazy. Tento další příklad najde Státní jména které začínají na „N“ a obsahují „o“ před posledním znakem řetězce. To vylučuje Nové Mexiko; začíná na „N“, ale před posledním znakem nemá „o“.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Tato funkce je obzvláště užitečná při vyhledávání v produktových kódech, PSČ nebo sériových číslech, kde pozice každého znaku má specifický význam.
Pomocí znaku ~ (vlnovka)
Zvláštní znak ~ (známý jako a vlnovka) nám umožňuje léčit * nebo? znaky, jako by to byly jednoduché textové hodnoty, a nechovají se jako zástupné znaky.
V níže uvedeném příkladu musíme sečíst Úroveň zásob když jméno výrobku konkrétně odpovídá textu „Produkt?“:
| 1 | = SUMIFS (C3: C8, B3: B8, "Product ~?") |
~ Bezprostředně před * nebo a? znak z něj udělá textovou hodnotu, takže hledaný výraz „Produkt ~?“ slouží k nalezení přesné shody textu s „Produktem?“.
Kombinace zástupných znaků SUMIFS s odkazy na buňky
Obvykle není dobrým zvykem zakódovat hodnoty do vzorců. Místo toho je flexibilnější použít samostatné buňky k určení hodnot pro naše vyhledávací dotazy.
Chcete -li zjistit, zda buňky obsahují text obsažený v buňce E3, můžeme použít funkci SUMIFS s odkazem na buňku a zástupnými znaky *:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Všimněte si toho, že text „Dakota“ byl nahrazen odkazem na buňku & E3 & a znaky * byly vloženy do uvozovek („“).
Lze také kombinovat více odkazů na buňky a zástupné znaky. Najít Státní jména které začínají textem v buňce E3 a obsahují text v buňce F3 následovaný alespoň 1 dalším znakem, lze použít následující vzorec:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Zamykání odkazů na buňky
Aby byly naše vzorce čitelnější, ukázali jsme vzorce bez zamčených odkazů na buňky:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Tyto vzorce ale nebudou správně fungovat, když je zkopírujete a vložíte jinam do souboru. Místo toho byste měli použít uzamčené odkazy na buňky takto:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, „*“ & E3 & „*“) |
Další informace najdete v našem článku o zamykání odkazů na buňky.
Součet, pokud buňka obsahuje konkrétní text pomocí zástupných znaků v Tabulkách Google
Tyto vzorce fungují v Tabulkách Google úplně stejně jako v Excelu.
