Seštejte, če celice vsebujejo določeno besedilo v drugem stolpcu
Ta vadnica vam bo pokazala, kako sešteti vrednosti, če celice vsebujejo specifično ali delno besedilo v drugem stolpcu. Za primer vzemite spodnji obseg podatkov, da dobite skupne količine izdelkov, ki vsebujejo besedilo »T-Shirt«, tako funkcija SUMIF kot funkcija SUMPRODUCT lahko obravnavata to nalogo v Excelu.
- Seštejte vrednosti, če celica vsebuje specifično ali delno besedilo s funkcijo SUMIF
- Seštejte vrednosti, če celica vsebuje specifično ali delno besedilo s funkcijo SUMPRODUCT
Seštejte vrednosti, če celica vsebuje specifično ali delno besedilo s funkcijo SUMIF
Če želite sešteti vrednosti, če celica vsebuje določeno besedilo v drugem stolpcu, lahko uporabite funkcijo SUMIF z nadomestnim znakom (*), splošne sintakse so:
Splošna formula s trdo kodiranim besedilom:
- range: obseg podatkov, ki ga želite oceniti z uporabo meril;
- *text*: merila, na podlagi katerih želite sešteti vrednosti. Tukaj se nadomestni znak * uporablja za iskanje poljubnega števila znakov, da se ujemajo z vsemi elementi, ki vsebujejo določeno besedilo, besedilo postavite med dva znaka *. (Upoštevajte, da morate besedilo in nadomestni znak zajeti v dvojne narekovaje.)
- sum_range: obseg celic z ujemajočimi se številskimi vrednostmi, ki jih želite sešteti.
Splošna formula z referenco celice:
- range: obseg podatkov, ki ga želite oceniti z uporabo meril;
- "*"&cell&"*": merila, na podlagi katerih želite sešteti vrednosti;
- *: nadomestni znak, ki najde poljubno število znakov.
- celica: Celica vsebuje določeno besedilo, ki ga iščete.
- &: Ta operator povezovanja (&) se uporablja za združevanje sklicevanja na celico z zvezdicami.
- sum_range: obseg celic z ujemajočimi se številskimi vrednostmi, ki jih želite sešteti.
Ko poznate osnovno delovanje funkcije, uporabite katero koli od naslednjih formul, ki jih potrebujete, in pritisnite Vnesite ključ do rezultata:
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12) (Use a cell reference)
Opombe: Ta funkcija SUMIF ni občutljiva na velike in male črke.
Seštejte vrednosti, če celica vsebuje specifično ali delno besedilo s funkcijo SUMPRODUCT
Funkcija SUMPRODUCT vam lahko pomaga tudi pri dokončanju tega opravila brez uporabe nadomestnih znakov. Tukaj bi morali funkciji ISNUMBER in SEARCH zaviti v funkcijo SUMPRODUCT, splošna sintaksa je:
- sum_range: obseg celic z ujemajočimi se številskimi vrednostmi, ki jih želite sešteti;
- criteria: merila, na podlagi katerih želite sešteti vrednosti. Lahko je sklic na celico ali specifično besedilo, ki ste ga definirali;
- range: obseg podatkov, ki ga želite oceniti z uporabo meril;
Uporabite katero koli od spodnjih formul v prazno celico in pritisnite Vnesite ključ za vrnitev rezultata:
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12)))) (Use a cell reference)
Razlaga te formule:
=SUMPRODUCT($B$2:$B$12*(ŠTEVILO(ISKANJE("majica",$A$2:$A$12))))
- ISKANJE("majica",$A$2:$A$12): Funkcija SEARCH vrne lokacijo določenega besedila “T-Shirt” iz obsega podatkov A2:A12, tako da boste dobili niz, kot je ta: {5;#VALUE!;#VALUE!;7;#VALUE! ;7;#VREDNOST!;#VREDNOST!;#VREDNOST!;#VREDNOST!;7}.
- ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Ta funkcija ISNUMBER se uporablja za testiranje številskih vrednosti in vrne novo matriko: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
- $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: Za pomnožitev teh dveh nizov matematična operacija prisili vrednosti TRUE in FALSE na 1s in 0s. Torej, množenje dveh nizov bo takole: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1; 0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
- SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):Na koncu funkcija SUMPRODUCT sešteje vse vrednosti v matriki, da dobi rezultat: 1605.
Uporabljena relativna funkcija:
- SUMIF:
- Funkcija SUMIF lahko pomaga pri seštevanju celic na podlagi enega merila.
- SUMPRODUCT:
- S funkcijo SUMPRODUCT lahko pomnožite dva ali več stolpcev ali nizov in nato dobite vsoto izdelkov.
- ŠTEVILKA:
- Excel ISNUMBER funkcija vrne TRUE, če celica vsebuje številko, in FALSE, če ne.
- ISKANJE:
- Funkcija SEARCH vam lahko pomaga najti položaj določenega znaka ali podniza iz danega besedilnega niza
Več člankov:
- Vsota, če se začne z ali se konča z določenim besedilom ali znaki
- Če želite sešteti vrednosti, če se ustrezne celice začnejo z ali končajo z določeno vrednostjo, lahko uporabite funkcijo SUMIF z nadomestnim znakom (*), da jo izvlečete. Ta članek bo podrobno predstavil, kako uporabljati formulo.
- Vsota najmanjših ali spodnjih N vrednosti
- V Excelu nam je enostavno sešteti obseg celic s funkcijo SUM. Včasih boste morda morali sešteti najmanjše ali najnižje številke 3, 5 ali n v obsegu podatkov, kot je prikazano na spodnjem posnetku zaslona. V tem primeru vam lahko SUMPRODUCT skupaj s funkcijo SMALL pomaga rešiti to težavo v Excelu.
- Vsota najmanjših ali spodnjih N vrednosti na podlagi meril
- V prejšnji vadnici smo razpravljali o tem, kako sešteti najmanjše n vrednosti v podatkovnem območju. V tem članku bomo izvedli nadaljnjo napredno operacijo – seštevanje najnižjih vrednosti n na podlagi enega ali več kriterijev v Excelu.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel - vam pomaga izstopati iz množice
Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...
Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)
- Eno sekundo za preklop med desetinami odprtih dokumentov!
- Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
- Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
- Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.
