Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, kuinka SUMIFS -funktion ja yleismerkkien avulla voidaan laskea yhteen tiedot, jotka vastaavat soluja, jotka sisältävät tiettyä tekstiä Excelissä ja Google Sheetsissä.
Summa, jos teksti sisältää
Ensin osoitamme, kuinka summaa tiettyä tekstiä sisältäviin soluihin liittyvät tiedot SUMIFS -funktion avulla.
SUMIFS -toiminto summaa tietorivit, jotka täyttävät tietyt ehdot. Sen syntaksi on:
Tämä esimerkki summaa kaiken Pisteet kanssa Osavaltion nimi joka sisältää ”Dakota” -komennon SUMIFS -funktiolla ja * -merkillä.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
* -Merkki sallii minkä tahansa määrän (mukaan lukien nolla) muita merkkejä tilalle.
Tässä esimerkissä sitä käytetään löytämään kaikki solut, jotka sisältävät tekstin "Dakota". Tämä haku ei erota kirjainkokoa, joten "dakota" on sama kuin "Dakota" tai "DAKOTA". Pohjois-Dakota ja Etelä-Dakota sisältävät "Dakotaa" ja sisältyvät siksi summaan.
Summa, jos teksti alkaa
* -Merkillä voidaan myös etsiä soluja, jotka alkavat määritetyllä tekstillä:
| 1 | = SUMIFS (C3: C9, B3: B9, "Uusi*") |
New York, New Jerseyja Uusi Meksiko aloita sanalla "uusi" ja sisältyvät siksi summaan. Huomaa, että hakusanalla "Uusi*" tekstisolun tulee alkaa "Uusi"; pelkkä näiden merkkien sisältäminen ei riitä.
Summa, jos teksti päättyy kohtaan
Samoin voimme summata kaikki Pisteet varten Osavaltiot päättyy "o":
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Uusi Meksiko ja Ohio päättyvät "o": een ja sisältyvät siksi summaan.
Käyttämällä ? Jokerimerkki
? merkkiä voidaan käyttää edustamaan mitä tahansa yksittäistä merkkiä tekstimerkkijonossa.
Tämä esimerkki löytää kaiken Osavaltioiden nimet alkaen "Uusi", jota seuraa täsmälleen 7 merkkiä (välilyönnit mukaan lukien).
| 1 | = SUMIFS (C3: C9, B3: B9, "Uusi ???????") |
New Jersey ja Uusi Meksiko täyttävät nämä kriteerit, mutta New York ei, koska kohdassa "Uusi" on vain 5 merkkiä New York.
Huomaa, että * ja? yleismerkkejä voidaan yhdistää tarvittaessa erityisten hakukomentojen tekemiseen. Tämä seuraava esimerkki löytyy Osavaltioiden nimet jotka alkavat "N" ja sisältävät "o" ennen merkkijonon viimeistä merkkiä. Tämä sulkee pois Uusi Meksiko; se alkaa "N", mutta siinä ei ole "o" ennen viimeistä merkkiä.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Tämä toiminto on erityisen hyödyllinen, kun etsit tuotekoodeja, postinumeroita tai sarjanumeroita, joissa kunkin merkin sijainnilla on erityinen merkitys.
Käyttämällä ~ (Tilde) -merkkiä
Erikoismerkki ~ (tunnetaan nimellä a tilde) avulla voimme käsitellä * tai? merkkejä ikään kuin ne olisivat yksinkertaisia tekstiarvoja eivätkä käyttäydy yleismerkkinä.
Alla olevassa esimerkissä meidän on laskettava yhteen Varastotaso kun tuotteen nimi vastaa erityisesti tekstiä "Tuote?":
| 1 | = SUMIFS (C3: C8, B3: B8, "Tuote ~?") |
~ Välittömästi ennen * tai a? merkki muuttaa sen tekstiarvoksi, joten hakutermi "Tuote ~?" käytetään tarkan tekstin vastaavuuden löytämiseen tuotteelle?
SUMIFS -yleismerkkien ja soluviittausten yhdistäminen
Yleensä arvojen kovakoodaaminen kaavoiksi ei ole hyvä käytäntö. Sen sijaan on joustavampaa käyttää erillisiä soluja hakutermimme arvojen määrittämiseen.
Jos haluat etsiä, sisältävätkö solut solussa E3 olevaa tekstiä, voimme käyttää SUMIFS -funktiota soluviittauksen ja * -merkkien kanssa:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Huomaa, että teksti "Dakota" on korvattu soluviitteellä & E3 & ja * -merkit on lainausmerkeillä ("").
Useita soluviittauksia ja jokerimerkkejä voidaan myös yhdistää. Löytää Osavaltioiden nimet jotka alkavat tekstillä solussa E3 ja sisältävät solun F3 tekstin ja sen jälkeen vähintään yhden merkin lisää, voidaan käyttää seuraavaa kaavaa:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Soluviitteiden lukitseminen
Kaavojemme lukemisen helpottamiseksi olemme näyttäneet kaavat ilman lukittuja soluviittauksia:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Mutta nämä kaavat eivät toimi kunnolla, kun kopioit ja liität muualle tiedostoosi. Käytä sen sijaan lukittuja soluviittauksia seuraavasti:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Lue artikkelimme soluviitteiden lukitsemisesta saadaksesi lisätietoja.
Summa, jos solu sisältää tiettyä tekstiä käyttämällä Google Sheetsin yleismerkkejä
Nämä kaavat toimivat täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä.
