Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā izmantot funkciju SUMIFS un aizstājējzīmes, lai summētu datus, kas atbilst šūnām, kurās ir noteikts teksts programmā Excel un Google izklājlapas.
Summa, ja teksts satur
Pirmkārt, mēs parādīsim, kā apkopot datus, kas saistīti ar šūnām, kurās ir konkrēts teksts, izmantojot funkciju SUMIFS.
Funkcija SUMIFS apkopo datu rindas, kas atbilst noteiktiem kritērijiem. Tās sintakse ir šāda:
Šis piemērs apkopos visu Rezultāti ar Valsts nosaukums kas satur “Dakota”, izmantojot funkciju SUMIFS un * aizstājējzīmi.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
Rakstzīme * ļauj ievietot jebkuru citu rakstzīmju skaitu (ieskaitot nulli).
Šajā piemērā tas tiek izmantots, lai atrastu visas šūnas, kurās ir teksts “Dakota”. Šī meklēšana nav reģistrjutīga, tāpēc “dakota” tiek uzskatīta par tādu pašu kā “Dakota” vai “DAKOTA”. Ziemeļdakota un Dienviddakota satur “Dakota” un tāpēc ir iekļauti summā.
Summa, ja teksts sākas ar
Simbolu * var izmantot arī, lai meklētu šūnas, kas sākas ar norādīto tekstu:
| 1 | = SUMIFS (C3: C9, B3: B9, "Jauns*") |
Ņujorka, Ņūdžersija, un Jaunā Meksika sākas ar “Jauns” un tāpēc ir iekļauti summā. Ņemiet vērā, ka ar meklēšanas vienumu “Jauns*” teksta šūnai jāsākas ar “Jauns”; nepietiek tikai ar šo rakstzīmju iekļaušanu.
Summa, ja teksts beidzas ar
Līdzīgi mēs varam apkopot visu Rezultāti priekš Valstis beidzas ar “o”, izmantojot:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Jaunā Meksika un Ohaio beidzas ar “o” un tāpēc ir iekļauti summā.
Izmantojot ? Aizstājējzīme
Vai? rakstzīmi var izmantot, lai attēlotu jebkuru atsevišķu rakstzīmi teksta virknē.
Šis piemērs atrod visu Valstu nosaukumi sākot ar “Jauns”, kam seko tieši 7 rakstzīmes (ieskaitot atstarpes).
| 1 | = SUMIFS (C3: C9, B3: B9, "Jauns ???????") |
Ņūdžersija un Jaunā Meksika atbilst šiem kritērijiem, bet Ņujorka nav, jo aiz “New” ir tikai 5 rakstzīmes Ņujorka.
Ņemiet vērā, ka * un? aizstājējzīmes var kombinēt, ja nepieciešams, lai izveidotu ļoti specifiskas meklēšanas komandas. Šis nākamais piemērs atrod Valstu nosaukumi kas sākas ar “N” un satur “o” pirms virknes pēdējās rakstzīmes. Tas izslēdz Jaunā Meksika; tas sākas ar “N”, bet pirms pēdējās rakstzīmes nav “o”.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Šī funkcija ir īpaši noderīga, meklējot produktu kodus, pasta kodus vai sērijas numurus, kur katras rakstzīmes pozīcijai ir īpaša nozīme.
Izmantojot rakstzīmi ~ (Tilde)
Īpašais raksturs ~ (pazīstams kā a tilde) ļauj mums ārstēt * vai? rakstzīmes tā, it kā tās būtu vienkāršas teksta vērtības un nerīkotos kā aizstājējzīmes.
Zemāk redzamajā piemērā mums jāsummē Krājumu līmenis kad produkta nosaukums īpaši atbilst tekstam “Produkts?”:
| 1 | = SUMIFS (C3: C8, B3: B8, "Produkts ~?") |
~ Tūlīt pirms * vai a? rakstzīme pārvērš to par teksta vērtību, tāpēc meklēšanas vienums “Produkts ~?” tiek izmantots, lai atrastu precīzu teksta atbilstību “Produkts?”.
SUMIFS aizstājējzīmju apvienošana ar šūnu atsaucēm
Parasti nav laba prakse vērtību kodēšanu formulās. Tā vietā ir elastīgāk izmantot atsevišķas šūnas, lai norādītu vērtības mūsu meklēšanas vienumiem.
Lai meklētu, vai šūnās ir šūnā E3 esošais teksts, mēs varam izmantot funkciju SUMIFS ar šūnu atsauci un * aizstājējzīmes:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Ņemiet vērā, ka teksts “Dakota” ir aizstāts ar šūnu atsauci & E3 & un * rakstzīmes ir ievietotas pēdiņās (””).
Var apvienot arī vairākas šūnu atsauces un aizstājējzīmes. Atrast Valstu nosaukumi kas sākas ar tekstu šūnā E3 un satur tekstu šūnā F3, kam seko vēl vismaz 1 rakstzīme, var izmantot šādu formulu:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Šūnu atsauču bloķēšana
Lai mūsu formulas būtu vieglāk lasāmas, mēs esam parādījuši formulas bez bloķētām šūnu atsaucēm:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Bet šīs formulas nedarbosies pareizi, kopējot un ielīmējot citur jūsu failā. Tā vietā jums vajadzētu izmantot šādas bloķētas šūnu atsauces:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Lai uzzinātu vairāk, izlasiet mūsu rakstu par šūnu atsauču bloķēšanu.
Summa, ja šūnā ir konkrēts teksts, izmantojot aizstājējzīmes Google izklājlapās
Šīs formulas Google izklājlapās darbojas tieši tāpat kā programmā Excel.
