Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să utilizați funcția SUMIFS și caracterele wildcard pentru a însuma datele corespunzătoare celulelor care conțin text specific în Excel și Foi de calcul Google.
Suma dacă textul conține
În primul rând, vom demonstra cum să însumăm date legate de celule care conțin text specific folosind funcția SUMIFS.
Funcția SUMIFS însumează rândurile de date care îndeplinesc anumite criterii. Sintaxa sa este:
Acest exemplu va însuma totul Scoruri cu Numele statului care conține „Dakota” utilizând funcția SUMIFS și caracterul * wildcard.
| 1 | = SUME (C3: C9, B3: B9, "* Dakota *") |
Caracterul * permite ca orice număr (inclusiv zero) din alte caractere să-și ia locul.
În acest exemplu, este folosit pentru a găsi toate celulele care includ textul „Dakota”. Această căutare nu este sensibilă la majuscule și minuscule, așa că „dakota” este considerat la fel ca „Dakota” sau „DAKOTA”. Dakota de Nord și Dakota de Sud conțin „Dakota” și, prin urmare, sunt incluse în sumă.
Suma dacă textul începe cu
Caracterul * poate fi folosit și pentru a căuta celule care încep cu textul specificat:
| 1 | = SUME (C3: C9, B3: B9, „Nou *”) |
New York, New Jersey, și New Mexico începe cu „Nou” și, prin urmare, sunt incluse în sumă. Rețineți că, cu termenul de căutare „Nou *”, celula text trebuie să înceapă cu „Nou”; conținerea acestor caractere nu este suficientă.
Suma dacă textul se termină cu
În mod similar, putem rezuma toate Scoruri pentru State care se termină cu „o” folosind:
| 1 | = SUME (C3: C9, B3: B9, "* o") |
New Mexico și Ohio se termină cu „o” și sunt, prin urmare, incluse în sumă.
Folosind? Caracter wildcard
? caracterul poate fi folosit pentru a reprezenta orice caracter dintr-un șir de text.
Acest exemplu găsește toate Numele statului începând cu „Nou”, urmat de exact 7 caractere (inclusiv spații).
| 1 | = SUME (C3: C9, B3: B9, „Nou ???????”) |
New Jersey și New Mexico îndeplinesc aceste criterii, dar New York nu, deoarece există doar 5 caractere care urmează „Nou” în New York.
Rețineți că * și? caracterele wildcard pot fi combinate dacă este necesar pentru a efectua comenzi de căutare foarte specifice. Următorul exemplu găsește Numele statului care încep cu „N” și conțin un „o” înainte de ultimul caracter al șirului. Aceasta exclude New Mexico; începe cu „N”, dar nu are un „o” înainte de ultimul caracter.
| 1 | = SUME (C3: C9, B3: B9, "N * o? *") |
Această funcționalitate este deosebit de utilă atunci când căutați prin coduri de produs, coduri poștale sau numere de serie în care poziția fiecărui caracter are un sens specific.
Folosind caracterul ~ (Tilde)
Caracterul special ~ (cunoscut sub numele de tilde) ne permite să tratăm * sau? caractere ca și cum ar fi valori de text simple și nu se comportă ca metacaractere.
În exemplul de mai jos, trebuie să însumăm Nivelul stocului cand numele produsului se potrivește în mod specific cu textul „Produs?”:
| 1 | = SUME (C3: C8, B3: B8, „Produs ~?”) |
~ Imediat înainte de a * sau a? caracterul îl transformă înapoi într-o valoare text, deci termenul de căutare „Produs ~?” este folosit pentru a găsi o potrivire exactă a textului cu „Produs?”.
Combinarea wildcard-urilor SUMIFS cu referințe celulare
De obicei, nu este o bună practică să codificați valorile în formule. În schimb, este mai flexibil să utilizăm celule separate pentru a specifica valori pentru termenii noștri de căutare.
Pentru a căuta dacă celulele conțin textul păstrat în celula E3, putem utiliza funcția SUMIFS cu o referință de celulă și * metacaractere:
| 1 | = SUME (C3: C9, B3: B9, "*" & E3 & "*") |
Rețineți că textul „Dakota” a fost înlocuit cu referința celulei & E3 & și caracterele * au fost puse în ghilimele („„).
Mai multe referințe de celule și caractere wildcard pot fi, de asemenea, combinate împreună. A găsi Numele statului care încep cu textul din celula E3 și conțin textul din celula F3 urmat de cel puțin încă 1 caracter, se poate utiliza următoarea formulă:
| 1 | = SUME (C3: C9, B3: B9, E3 & "*" & F3 & "? *") |
Blocarea referințelor celulei
Pentru a simplifica citirea formulelor, am arătat formulele fără referințe de celule blocate:
| 1 | = SUMIFI (C3: C9, B3: B9, "*" & E3 & "*") |
Dar aceste formule nu vor funcționa corect atunci când sunt copiate și lipite în altă parte a fișierului. În schimb, ar trebui să utilizați referințe de celule blocate ca aceasta:
| 1 | = SUME ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Citiți articolul nostru despre blocarea referințelor celulei pentru a afla mai multe.
Sumați dacă celula conține text specific utilizând metacaractere în Foi de calcul Google
Aceste formule funcționează exact la fel în Foi de calcul Google ca în Excel.
