Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger SUMIFS -funktionen og jokertegn til at opsummere data, der svarer til celler, der indeholder specifik tekst i Excel og Google Sheets.
Sum Hvis tekst indeholder
Først vil vi demonstrere, hvordan man summerer data relateret til celler, der indeholder specifik tekst ved hjælp af SUMIFS -funktionen.
SUMIFS -funktionen summerer datarækker, der opfylder visse kriterier. Dens syntaks er:
Dette eksempel vil summe alt Resultater med en Statens navn der indeholder “Dakota” ved hjælp af SUMIFS -funktionen og * wildcard -tegnet.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
* Tegnet giver mulighed for, at ethvert antal (inklusive nul) andre tegn indtager sin plads.
I dette eksempel bruges det til at finde alle celler, der indeholder teksten "Dakota". Denne søgning er ikke store og små bogstaver, så "dakota" betragtes som det samme som "Dakota" eller "DAKOTA". North Dakota og South Dakota indeholder “Dakota” og er derfor inkluderet i summen.
Sum, hvis tekst starter med
* -Tegnet kan også bruges til at lede efter celler, der starter med den angivne tekst:
| 1 | = SUMIFS (C3: C9, B3: B9, "Ny*") |
New York, New Jersey, og Ny mexico start med “Nyt” og er derfor inkluderet i summen. Bemærk, at med søgeordet "Ny*" skal tekstcellen starte med "Ny"; bare at indeholde disse tegn er ikke nok.
Sum hvis tekst ender med
På samme måde kan vi summe alle Resultater til Stater der slutter på “o” ved hjælp af:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Ny mexico og Ohio slutter med “o” og er derfor inkluderet i summen.
Bruger ? Jokertegn
Det ? tegn kan bruges til at repræsentere et enkelt tegn i en tekststreng.
Dette eksempel finder alt Statsnavne startende med "Ny", efterfulgt af præcis 7 tegn (inklusive mellemrum).
| 1 | = SUMIFS (C3: C9, B3: B9, "Ny ???????") |
New Jersey og Ny mexico opfylder disse kriterier, men New York gør det ikke, da der kun er 5 tegn efter "Ny" i New York.
Bemærk at * og? jokertegn kan kombineres, hvis det kræves for at lave meget specifikke søgekommandoer. Dette næste eksempel finder Statsnavne der starter med “N” og indeholder et “o” før strengens sidste tegn. Dette udelukker Ny mexico; det starter med "N", men har ikke et "o" før det sidste tegn.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Denne funktionalitet er især nyttig, når du søger gennem produktkoder, postnumre eller serienumre, hvor placeringen af hvert tegn har en bestemt betydning.
Brug ~ (Tilde) -tegnet
Specialtegnet ~ (kendt som a tilde) giver os mulighed for at behandle * eller? tegn som om de er simple tekstværdier og ikke opfører sig som jokertegn.
I eksemplet herunder skal vi opsummere Lagerniveau når produktnavn matcher specifikt teksten “Produkt?”:
| 1 | = SUMIFS (C3: C8, B3: B8, "Produkt ~?") |
~ Umiddelbart før a * eller a? tegn gør det tilbage til en tekstværdi, så søgeudtrykket "Produkt ~?" bruges til at finde en nøjagtig tekst, der matcher "Produkt?".
Kombination af SUMIFS -jokertegn med cellereferencer
Normalt er det ikke god praksis at hard-kode værdier i formler. I stedet er det mere fleksibelt at bruge separate celler til at angive værdier for vores søgeudtryk.
For at søge efter, om cellerne indeholder teksten i celle E3, kan vi bruge SUMIFS -funktionen med en cellereference og * jokertegn:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Bemærk, at teksten “Dakota” er blevet erstattet af cellereferencen & E3 & og * tegnene er blevet sat i anførselstegn (”“).
Flere cellereferencer og jokertegn kan også kombineres sammen. At finde Statsnavne der starter med teksten i celle E3 og indeholder teksten i celle F3 efterfulgt af mindst 1 tegn mere, kan følgende formel bruges:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Låsning af cellereferencer
For at gøre vores formler lettere at læse har vi vist formlerne uden låste cellereferencer:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Men disse formler fungerer ikke korrekt, når de kopieres og indsættes andre steder i din fil. I stedet skal du bruge låste cellereferencer som denne:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Læs vores artikel om låsning af cellereferencer for at lære mere.
Sum, hvis cellen indeholder specifik tekst ved hjælp af jokertegn i Google Sheets
Disse formler fungerer nøjagtig det samme i Google Sheets som i Excel.
