Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du bruker SUMIFS -funksjonen og jokertegn for å summere data som tilsvarer celler som inneholder spesifikk tekst i Excel og Google Sheets.
Sum Hvis teksten inneholder
Først vil vi demonstrere hvordan vi summerer data relatert til celler som inneholder spesifikk tekst ved hjelp av SUMIFS -funksjonen.
SUMIFS -funksjonen summerer datarader som oppfyller visse kriterier. Syntaksen er:
Dette eksemplet vil summere alt Poeng med en Statens navn som inneholder “Dakota” ved hjelp av SUMIFS -funksjonen og * jokertegnet.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
Tegnet * gjør at et hvilket som helst antall (inkludert null) andre tegn kan ta sin plass.
I dette eksemplet brukes det til å finne alle celler som inneholder teksten "Dakota". Dette søket er ikke skille mellom store og små bokstaver, så "dakota" regnes som det samme som "Dakota" eller "DAKOTA". Norddakota og Sør Dakota inneholder “Dakota” og er derfor inkludert i summen.
Sum hvis tekst starter med
Tegnet * kan også brukes til å lete etter celler som starter med den angitte teksten:
| 1 | = SUMIFS (C3: C9, B3: B9, "Ny*") |
New York, New Jersey, og New Mexico start med "Nytt" og er derfor inkludert i summen. Vær oppmerksom på at med søkeordet "Ny*", må tekstcellen starte med "Ny"; bare å inneholde disse tegnene er ikke nok.
Sum hvis tekst ender med
På samme måte kan vi summere alle Poeng til Stater slutter på “o” ved å bruke:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
New Mexico og Ohio avslutt med “o” og er derfor inkludert i summen.
Bruker ? Jokertegn
Den? tegn kan brukes til å representere et enkelt tegn i en tekststreng.
Dette eksemplet finner alt Statlige navn som begynner med "Ny", etterfulgt av nøyaktig 7 tegn (inkludert mellomrom).
| 1 | = SUMIFS (C3: C9, B3: B9, "Ny ???????") |
New Jersey og New Mexico oppfyller disse kriteriene, men New York gjør det ikke, siden det er bare 5 tegn etter “Ny” i New York.
Legg merke til at * og? jokertegn kan kombineres om nødvendig for å lage helt spesifikke søkekommandoer. Dette neste eksemplet finner Statlige navn som starter med “N” og inneholder et “o” før siste tegn i strengen. Dette utelukker New Mexico; den starter med "N", men har ikke et "o" før det siste tegnet.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Denne funksjonaliteten er spesielt nyttig når du søker gjennom produktkoder, postnummer eller serienumre der posisjonen til hvert tegn har en bestemt betydning.
Bruk ~ (Tilde) -tegnet
Spesialtegnet ~ (kjent som a tilde) lar oss behandle * eller? tegn som om de er enkle tekstverdier og ikke oppfører seg som jokertegn.
I eksemplet nedenfor må vi oppsummere Lagernivå når Produktnavn samsvarer spesielt med teksten “Produkt?”:
| 1 | = SUMIFS (C3: C8, B3: B8, "Produkt ~?") |
~ Rett før a * eller a? tegn gjør det tilbake til en tekstverdi, så søkeordet "Produkt ~?" brukes for å finne en eksakt tekst som samsvarer med “Produkt?”.
Kombinere SUMIFS -jokertegn med cellereferanser
Vanligvis er det ikke god praksis å kode verdier inn i formler. I stedet er det mer fleksibelt å bruke separate celler for å spesifisere verdier for søkeordene våre.
For å søke om cellene inneholder teksten i celle E3, kan vi bruke SUMIFS -funksjonen med en cellereferanse og * jokertegn:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Vær oppmerksom på at teksten “Dakota” er erstattet av cellereferansen & E3 & og * -tegnene er satt i anførselstegn (”“).
Flere cellereferanser og jokertegn kan også kombineres sammen. Å finne Statlige navn som starter med teksten i celle E3 og inneholder teksten i celle F3 etterfulgt av minst 1 tegn til, kan følgende formel brukes:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Låsing av cellereferanser
For å gjøre formlene våre lettere å lese, har vi vist formlene uten låste cellereferanser:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Men disse formlene fungerer ikke som de skal når de kopieres og limes inn andre steder i filen. I stedet bør du bruke låste cellereferanser slik:
| 1 | = SUMMER ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Les artikkelen vår om låsing av cellereferanser for å lære mer.
Sum hvis cellen inneholder spesifikk tekst ved bruk av jokertegn i Google Regneark
Disse formlene fungerer nøyaktig det samme i Google Regneark som i Excel.
