Atsisiųskite darbaknygės pavyzdį
Šioje pamokoje bus parodyta, kaip naudoti funkciją SUMIFS ir pakaitos simbolius, norint apibendrinti duomenis, atitinkančius langelius, kuriuose yra tam tikras tekstas „Excel“ ir „Google“ skaičiuoklėse.
Suma, jei tekste yra
Pirma, mes parodysime, kaip apibendrinti duomenis, susijusius su ląstelėmis, kuriose yra tam tikras tekstas, naudojant funkciją SUMIFS.
Funkcija SUMIFS apibendrina duomenų eilutes, atitinkančias tam tikrus kriterijus. Jo sintaksė yra tokia:
Šis pavyzdys apibendrins viską Balai su Valstybės pavadinimas kuriame yra „Dakota“, naudojant funkciją SUMIFS ir * pakaitos simbolį.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
Simbolis * leidžia pakeisti bet kurį skaičių (įskaitant nulį) kitų simbolių.
Šiame pavyzdyje jis naudojamas rasti visas ląsteles, kuriose yra tekstas „Dakota“. Ši paieška neskiria didžiųjų ir mažųjų raidžių, todėl „dakota“ laikoma tokia pat kaip „Dakota“ arba „DAKOTA“. Šiaurės Dakota ir Pietų Dakota yra „Dakota“, todėl yra įskaičiuoti į sumą.
Suma, jei tekstas prasideda
Simbolis * taip pat gali būti naudojamas ieškant langelių, prasidedančių nurodytu tekstu:
| 1 | = SUMIFS (C3: C9, B3: B9, „Naujas*“) |
Niujorkas, Naujasis Džersis, ir Naujasis Meksikas pradėkite nuo „Naujas“ ir todėl įskaičiuoti į sumą. Atminkite, kad naudojant paieškos terminą „Naujas*“ teksto langelis turi prasidėti „Naujas“; nepakanka vien tų simbolių.
Suma, jei tekstas baigiasi
Panašiai galime apibendrinti viską Balai dėl Valstybės baigiasi „o“, naudojant:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Naujasis Meksikas ir Ohajas baigiasi „o“, todėl yra įtraukiami į sumą.
Naudojant ? Pakaitos simbolis
The? simbolis gali būti naudojamas bet kuriam teksto eilutės simboliui pavaizduoti.
Šis pavyzdys randa viską Valstybių vardai pradedant nuo „Naujas“, po kurio eina lygiai 7 simboliai (įskaitant tarpus).
| 1 | = SUMIFS (C3: C9, B3: B9, "Naujas ???????") |
Naujasis Džersis ir Naujasis Meksikas atitinka šiuos kriterijus, bet Niujorkas nėra, nes po „Naujas“ yra tik 5 simboliai Niujorkas.
Atkreipkite dėmesį, kad * ir? pakaitos simboliai gali būti derinami, jei reikia, norint atlikti labai konkrečias paieškos komandas. Šis kitas pavyzdys randa Valstybių vardai kurie prasideda „N“ ir prieš paskutinį eilutės simbolį turi „o“. Tai neįtraukia Naujasis Meksikas; jis prasideda „N“, bet prieš paskutinį simbolį neturi „o“.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Ši funkcija ypač naudinga ieškant produktų kodų, pašto kodų ar serijos numerių, kur kiekvieno simbolio padėtis turi tam tikrą reikšmę.
Naudojant simbolį ~ (Tilde)
Ypatingas simbolis ~ (žinomas kaip tilde) leidžia mums gydyti * arba? simboliai tarsi paprastos teksto vertės ir nesielgia kaip pakaitos simboliai.
Žemiau esančiame pavyzdyje turime apibendrinti Akcijų lygis kai produkto pavadinimas konkrečiai atitinka tekstą „Produktas?“:
| 1 | = SUMIFS (C3: C8, B3: B8, „Produktas ~?“) |
~ Prieš pat * arba a? simbolis paverčia jį teksto verte, todėl paieškos terminas „Produktas ~?“ naudojamas norint rasti tikslią teksto atitiktį „Produktas?“.
SUMIFS pakaitos simbolių ir langelių nuorodų derinimas
Paprastai nėra gera praktika reikšmes koduoti į formules. Vietoj to lankstiau naudoti atskirus langelius, kad būtų galima nurodyti paieškos terminų vertes.
Norėdami sužinoti, ar langeliuose yra tekstas, esantis ląstelėje E3, galime naudoti funkciją SUMIFS su langelio nuoroda ir * pakaitos simboliais:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Atminkite, kad tekstas „Dakota“ buvo pakeistas langelio nuoroda & E3 &, o * simboliai buvo įrašyti į kabutes („“).
Taip pat galima sujungti kelias langelių nuorodas ir pakaitos simbolius. Rasti Valstybių vardai kurie prasideda tekstu langelyje E3 ir yra tekstas langelyje F3, po kurio rašomas dar bent 1 simbolis, galima naudoti šią formulę:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Ląstelių nuorodų užrakinimas
Kad mūsų formules būtų lengviau skaityti, parodėme formules be užrakintų langelių nuorodų:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Tačiau šios formulės neveiks tinkamai, kai bus nukopijuotos ir įklijuotos kitur jūsų faile. Vietoj to turėtumėte naudoti užrakintas langelių nuorodas:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Norėdami sužinoti daugiau, perskaitykite mūsų straipsnį apie ląstelių nuorodų užrakinimą.
Suma, jei langelyje yra konkretaus teksto, naudojant „Google“ skaičiuoklių pakaitos simbolius
Šios formulės „Google“ skaičiuoklėse veikia lygiai taip pat, kaip „Excel“.
