Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako koristiti funkciju SUMIFS i zamjenske znakove za zbrajanje podataka koji odgovaraju ćelijama koje sadrže određeni tekst u Excelu i Google tablicama.
Zbroj ako tekst sadrži
Prvo ćemo pokazati kako zbrojiti podatke koji se odnose na ćelije koje sadrže određeni tekst pomoću funkcije SUMIFS.
Funkcija SUMIFS zbraja retke podataka koji zadovoljavaju određene kriterije. Njegova sintaksa je:
Ovaj primjer će zbrojiti sve Bodovi s Naziv države koji sadrži "Dakota" pomoću funkcije SUMIFS i zamjenskog znaka *.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
Znak * omogućuje da bilo koji broj (uključujući nulu) drugih znakova zauzme njegovo mjesto.
U ovom primjeru koristi se za pronalaženje svih ćelija koje sadrže tekst "Dakota". Ovo pretraživanje ne razlikuje velika i mala slova, pa se "dakota" smatra istim kao "Dakota" ili "DAKOTA". Sjeverna Dakota i Južna Dakota sadrže "Dakotu" i stoga su uključene u zbroj.
Zbroj ako tekst počinje s
Znak * također se može koristiti za traženje ćelija koje počinju s navedenim tekstom:
| 1 | = SUMIFS (C3: C9, B3: B9, "Novo*") |
New Yorku, New Jersey, i Novi Meksiko počinju s "Novo" i stoga su uključene u zbroj. Imajte na umu da s pojmom za pretraživanje "Novi*" tekstualna ćelija mora počinjati s "Novo"; jednostavno sadržavanje tih znakova nije dovoljno.
Zbroj ako tekst završi s
Slično, možemo zbrojiti sve Bodovi za Države završava na „o“ koristeći:
| 1 | = ZBIRA (C3: C9, B3: B9, "*o") |
Novi Meksiko i Ohio završavaju s "o" i stoga su uključeni u zbroj.
Koristiti ? Zamjenski znak
The? znak se može koristiti za predstavljanje bilo kojeg pojedinačnog znaka u tekstualnom nizu.
Ovaj primjer pronalazi sve Državna imena počinje s "Novo", nakon čega slijedi točno 7 znakova (uključujući razmake).
| 1 | = SUMIFS (C3: C9, B3: B9, "Novo ???????") |
New Jersey i Novi Meksiko zadovoljavaju ove kriterije, ali New Yorku ne, budući da postoji samo 5 znakova koji slijede "Novo" u New Yorku.
Imajte na umu da * i? zamjenski znakovi mogu se kombinirati ako je potrebno za izradu vrlo specifičnih naredbi pretraživanja. Ovaj sljedeći primjer otkriva Državna imena koji počinju s "N" i sadrže "o" prije posljednjeg znaka niza. Ovo isključuje Novi Meksiko; počinje s "N", ali nema "o" prije posljednjeg znaka.
| 1 | = ZBIRA (C3: C9, B3: B9, "N*o?*") |
Ova je funkcija osobito korisna pri pretraživanju kodova proizvoda, poštanskih brojeva ili serijskih brojeva gdje položaj svakog znaka ima posebno značenje.
Korištenje znaka ~ (Tilda)
Posebni znak ~ (poznat kao a tilda) omogućuje nam liječenje * ili? znakovi kao da su jednostavne tekstualne vrijednosti i ne ponašaju se kao zamjenski znakovi.
U donjem primjeru moramo zbrojiti Razina dionica kada ime proizvoda posebno odgovara tekstu "Proizvod?":
| 1 | = ZBIRA (C3: C8, B3: B8, "Proizvod ~?") |
Znak ~ neposredno prije a * ili a? znak pretvara u tekstualnu vrijednost, pa pojam za pretraživanje "Proizvod ~?" koristi se za pronalaženje točnog teksta koji odgovara "proizvodu?".
Kombiniranje zamjenskih znakova SUMIFS -a s referencama na ćelije
Obično nije dobra praksa tvrdo kodirati vrijednosti u formule. Umjesto toga, fleksibilnije je koristiti zasebne ćelije za navođenje vrijednosti za naše pojmove za pretraživanje.
Za traženje sadrže li ćelije tekst koji se nalazi u ćeliji E3, možemo upotrijebiti funkciju SUMIFS s referencom ćelije i * zamjenskim znakovima:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Imajte na umu da je tekst "Dakota" zamijenjen referencom ćelije & E3 &, a znakovi * stavljeni su u navodnike ("").
Više referenci ćelija i zamjenskih znakova također se mogu kombinirati zajedno. Pronaći Državna imena koji počinju tekstom u ćeliji E3 i sadrže tekst u ćeliji F3 nakon čega slijedi još najmanje 1 znak, može se koristiti sljedeća formula:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Zaključavanje referenci ćelija
Radi lakšeg čitanja formula prikazali smo formule bez zaključanih referenci ćelija:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Ali ove formule neće raditi ispravno ako ih kopirate i zalijepite na drugo mjesto u datoteci. Umjesto toga, trebali biste koristiti zaključane ćelijske reference poput ove:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Pročitajte naš članak o zaključavanju referenci stanica da biste saznali više.
Zbroj ako ćelija sadrži određeni tekst pomoću zamjenskih znakova u Google tablicama
Ove formule rade potpuno isto u Google tablicama kao i u Excelu.
