Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako uporabiti funkcijo SUMIFS in nadomestne znake za seštevanje podatkov, ki ustrezajo celicam, ki vsebujejo določeno besedilo v Excelu in Google Preglednicah.
Vsota, če besedilo vsebuje
Najprej bomo pokazali, kako z uporabo funkcije SUMIFS sešteti podatke, povezane s celicami, ki vsebujejo določeno besedilo.
Funkcija SUMIFS povzame podatkovne vrstice, ki izpolnjujejo določena merila. Njegova sintaksa je:
Ta primer bo povzemal vse Rezultati z Ime države ki vsebuje "Dakota" z uporabo funkcije SUMIFS in * nadomestnega znaka *.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
Znak * omogoča, da njegovo mesto prevzame poljubno število (vključno z ničlo) drugih znakov.
V tem primeru se uporablja za iskanje vseh celic, ki vsebujejo besedilo "Dakota". To iskanje ni občutljivo na velike in male črke, zato se »dakota« šteje za isto kot »Dakota« ali »DAKOTA«. Severna Dakota in Južna Dakota vsebujejo "Dakota" in so zato vključeni v vsoto.
Vsota, če se besedilo začne z
Znak * lahko uporabite tudi za iskanje celic, ki se začnejo z določenim besedilom:
| 1 | = SUMIFS (C3: C9, B3: B9, "Novo*") |
New York, New Jersey, in Nova Mehika začnite z "Novo" in so zato vključeni v vsoto. Upoštevajte, da se mora pri iskalnem izrazu »Novo*« besedilna celica začeti z »Novo«; preprosto shranjevanje teh znakov ni dovolj.
Vsota, če se besedilo konča z
Podobno lahko povzamemo vse Rezultati za Držav ki se konča na »o« z uporabo:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Nova Mehika in Ohio končajo z "o" in so zato vključene v vsoto.
Uporabljati ? Nadomestni znak
The? znak lahko uporabite za predstavitev katerega koli posameznega znaka v besedilnem nizu.
Ta primer najde vse Imena držav začenši z »Novo«, sledi natančno 7 znakov (vključno s presledki).
| 1 | = SUMIFS (C3: C9, B3: B9, "Novo ???????") |
New Jersey in Nova Mehika izpolnjujejo ta merila, vendar New York ne, saj je v vrstici »Novo« le 5 znakov New York.
Upoštevajte, da * in? nadomestne znake lahko po potrebi kombinirate za zelo specifične ukaze za iskanje. Ta naslednji primer najde Imena držav ki se začnejo z "N" in vsebujejo "o" pred zadnjim znakom niza. To izključuje Nova Mehika; Začne se z “N”, vendar pred zadnjim znakom nima “o”.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Ta funkcija je še posebej uporabna pri iskanju po kodah izdelkov, poštnih številkah ali serijskih številkah, kjer ima položaj vsakega znaka poseben pomen.
Uporaba znaka ~ (Tilda)
Posebni znak ~ (znan kot a tilda) nam omogoča zdravljenje * ali? znakov, kot da so preproste besedilne vrednosti in se ne obnašajo kot nadomestni znaki.
V spodnjem primeru moramo povzeti Raven zalog ko ime izdelka se posebej ujema z besedilom »Izdelek?«:
| 1 | = SUMIFS (C3: C8, B3: B8, "Izdelek ~?") |
The ~ tik pred a * ali a? znak ga spremeni v besedilno vrednost, zato iskalni izraz »izdelek ~?« se uporablja za iskanje natančnega ujemanja besedila z »Izdelek?«.
Združevanje nadomestnih znakov SUMIFS z referencami celic
Običajno ni primerno trdo kodirati vrednosti v formule. Namesto tega je prilagodljivejša uporaba ločenih celic za določanje vrednosti za naše iskalne izraze.
Za iskanje, ali celice vsebujejo besedilo v celici E3, lahko uporabimo funkcijo SUMIFS s sklicem na celico in * nadomestnimi znaki:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Upoštevajte, da je bilo besedilo »Dakota« nadomeščeno s sklicem na celico & E3 &, znaki * pa v narekovajih (»«).
Lahko se združijo tudi več sklicev na celice in nadomestnih znakov. Najti Imena držav ki se začnejo z besedilom v celici E3 in vsebujejo besedilo v celici F3, čemur sledi še vsaj 1 znak, se lahko uporabi naslednja formula:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Zaklepanje referenc celic
Za lažje branje formul smo prikazali formule brez zaklenjenih sklicev na celice:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Toda te formule ne bodo delovale pravilno, če jih kopirate in prilepite drugam v datoteko. Namesto tega uporabite zaklenjene sklice na celice, kot je ta:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Če želite izvedeti več, preberite naš članek o zaklepanju referenc celic.
Vsota, če celica vsebuje določeno besedilo z uporabo nadomestnih znakov v Google Preglednicah
Te formule delujejo popolnoma enako v Google Preglednicah kot v Excelu.
