Šajā apmācībā ir sniegts detalizēts demonstrējums par to, kā lietot SUMIF un SUMIFS funkcijas Google izklājlapās ar formulām un piemēriem.
SUMIF ir viena no Google izklājlapu matemātiskajām funkcijām, ko izmanto šūnu nosacīti summēšanai. Būtībā funkcija SUMIF meklē konkrētu nosacījumu šūnu diapazonā un pēc tam summē vērtības, kas atbilst dotajam nosacījumam.
Piemēram, jums ir izdevumu saraksts Google lapās un vēlaties apkopot tikai tos izdevumus, kas pārsniedz noteiktu maksimālo vērtību. Vai arī jums ir pasūtījuma preču saraksts un to atbilstošās summas, un jūs vēlaties uzzināt tikai konkrētas preces kopējo pasūtījuma summu. Šeit noder SUMIF funkcija.
SUMIF var izmantot, lai summētu vērtības, pamatojoties uz skaitļa nosacījumu, teksta nosacījumu, datuma nosacījumu, aizstājējzīmēm, kā arī pamatojoties uz tukšām un netukšām šūnām. Google izklājlapām ir divas funkcijas vērtību summēšanai, pamatojoties uz kritērijiem: SUMIF un SUMIFS. Funkcija SUMIF saskaita skaitļus, pamatojoties uz vienu nosacījumu, savukārt SUMIFS summē skaitļus, pamatojoties uz vairākiem nosacījumiem.
Šajā apmācībā mēs paskaidrosim, kā Google izklājlapās izmantot funkcijas SUMIF un SUMIFS, lai summētu skaitļus, kas atbilst noteiktam(-iem) nosacījumam(-iem).
SUMIF funkcija Google izklājlapās — sintakse un argumenti
Funkcija SUMIF ir tikai funkciju SUM un IF kombinācija. Funkcija IF skenē noteiktā nosacījuma šūnu diapazonu, un pēc tam funkcija SUM summē skaitļus, kas atbilst šūnām, kas atbilst nosacījumam.
Funkcijas SUMIF sintakse:
Funkcijas SUMIF sintakse pakalpojumā Google izklājlapas ir šāda:
=SUMF(diapazons, kritēriji, [summa_diapazons])Argumenti:
diapazons - Šūnu diapazons, kurā mēs meklējam kritērijiem atbilstošās šūnas.
kritērijiem – Kritēriji, kas nosaka, kuras šūnas jāpievieno. Kritēriju var balstīt uz skaitli, teksta virkni, datumu, šūnas atsauci, izteiksmi, loģisko operatoru, aizstājējzīmi, kā arī citām funkcijām.
summa_diapazons – Šis arguments nav obligāts. Tas ir datu diapazons ar vērtībām, kas jāsaskaita, ja atbilstošais diapazona ieraksts atbilst nosacījumam. Ja neiekļaujat šo argumentu, tā vietā tiek summēts “diapazons”.
Tagad apskatīsim, kā izmantot funkciju SUMIF, lai summētu vērtības ar dažādiem kritērijiem.
SUMIF funkcija ar skaitļu kritērijiem
Varat summēt skaitļus, kas atbilst noteiktiem kritērijiem šūnu diapazonā, kritēriju noteikšanai izmantojot kādu no tālāk norādītajiem salīdzināšanas operatoriem.
- lielāks par (>)
- mazāks par (<)
- lielāks vai vienāds ar (>=)
- mazāks vai vienāds ar (<=)
- vienāds ar (=)
- nav vienāds ar ()
Pieņemsim, ka jums ir šāda izklājlapa un jūs interesē kopējais pārdošanas apjoms, kas ir 1000 vai vairāk.
Lūk, kā varat ievadīt SUMIF funkciju:
Vispirms atlasiet šūnu, kurā vēlaties rādīt summas izvadi (D3). Lai summētu skaitļus B2:B12, kas ir lielāki vai vienādi ar 1000, ierakstiet šo formulu un nospiediet "Enter":
=SUMF(B2:B12,">=1000",B2:B12)Šajā formulas piemērā argumenti diapazons un summa_diapazons (B2:B12) ir vienādi, jo pārdošanas skaitļi un kritēriji tiek piemēroti vienam un tam pašam diapazonam. Un mēs ievadījām skaitli pirms salīdzināšanas operatora un iekļāvām to pēdiņās, jo kritēriji vienmēr ir jāiekļauj dubultpēdiņās, izņemot šūnas atsauci.
Formula meklēja skaitļus, kas ir lielāki vai vienādi ar 1000, un pēc tam saskaitīja visas atbilstošās vērtības un parādīja rezultātu šūnā D3.
Tā kā diapazona un summas_diapazona argumenti ir vienādi, jūs varat sasniegt to pašu rezultātu bez sum_range argumentiem formulā, piemēram:
=SUMF(B2:B12,">=1000")
Vai arī varat norādīt šūnas atsauci (D2), kurā ir skaitlis, nevis skaitļa kritēriji, un kritērija argumentā apvienot salīdzināšanas operatoru ar šo šūnas atsauci:
=SUMF(B2:B12,">="&D2)Kā redzat, salīdzināšanas operators joprojām tiek ievadīts pēdiņās, un operators un šūnas atsauce ir savienoti ar & zīmi. Un jums nav jāiekļauj atsauce uz šūnām pēdiņās.
Piezīme: Atsaucoties uz šūnu, kurā ir ietverti kritēriji, šūnas vērtībā neatstājiet atstarpes sākumā vai beigās. Ja jūsu vērtībai ir nevajadzīga atstarpe pirms vai pēc vērtības norādītajā šūnā, tad formula atgriezīs “0”.
Varat arī izmantot citus loģiskos operatorus tādā pašā veidā, lai kritēriju argumentā izveidotu nosacījumus. Piemēram, lai summētu vērtības, kas mazākas par 500:
=SUMF(B2:B12,"<500")
Summa, ja skaitļi ir vienādi
Ja vēlaties pievienot skaitļus, kas ir vienādi ar noteiktu skaitli, kritērija argumentā varat ievadīt tikai skaitli vai arī skaitli ar vienādības zīmi.
Piemēram, lai summētu atbilstošās pārdošanas summas (sleja B) daudzumiem (sleja C), kuru vērtības ir vienādas ar 20, izmēģiniet kādu no šīm formulām:
=SUMF(C2:C12,"=20",B2:B12)
=SUMF(C2:C12,"20",B2:B12)
=SUMF(C2:C12,E2,B2:B12)
Lai summētu skaitļus B kolonnā ar skaitli, kas nav vienāds ar 20 C ailē, izmēģiniet šo formulu:
=SUMF(C2:C12,"20",B2:B12)
SUMIF funkcija ar teksta kritērijiem
Ja vēlaties saskaitīt skaitļus šūnu diapazonā (kolonnā vai rindā), kas atbilst šūnām, kurām ir konkrēts teksts, varat vienkārši iekļaut šo tekstu vai šūnu, kurā ir teksts, SUMIF formulas kritērija argumentā. Lūdzu, ņemiet vērā, ka teksta virkne vienmēr ir jāiekļauj dubultpēdiņās (" ").
Piemēram, ja vēlaties kopējo pārdošanas apjomu “Rietumu” reģionā, varat izmantot tālāk norādīto formulu.
=SUMIF(C2:C13,"Rietumi",B2:B13)Šajā formulā funkcija SUMIF meklē vērtību “Rietumi” šūnu diapazonā C2:C13 un saskaita atbilstošo pārdošanas vērtību kolonnā B. Pēc tam parāda rezultātu šūnā E3.
Varat arī atsaukties uz šūnu, kurā ir teksts, nevis izmantot tekstu kritērija argumentā:
=SUMF(C2:C12,E2,B2:B12)
Tagad iegūsim visu reģionu kopējos ieņēmumus, izņemot “Rietumus”. Lai to izdarītu, mēs izmantosim ne vienāds ar operatoru () formulā:
=SUMF(C2:C12,""&E2,B2:B12)
SUMIF ar aizstājējkartēm
Iepriekš minētajā metodē funkcija SUMIF ar teksta kritērijiem pārbauda diapazonu pret precīzi norādīto tekstu. Pēc tam skaitļi tiek summēti ar precīzu tekstu un ignorēti visi pārējie skaitļi, tostarp daļēji saskaņota teksta virkne. Lai summētu skaitļus ar daļēji atbilstošām teksta virknēm, jums savos kritērijos ir jāpielāgo viena no tālāk norādītajām aizstājējzīmēm.
?(jautājuma zīme) tiek izmantota, lai atbilstu jebkurai atsevišķai rakstzīmei jebkurā teksta virknes vietā.*(zvaigznīte) tiek izmantots, lai atrastu atbilstošus vārdus kopā ar jebkuru rakstzīmju secību.~(tilde) izmanto, lai saskaņotu tekstus ar jautājuma zīmi (?) vai zvaigznīti (*).
Mēs šo produktu un to daudzuma izklājlapas piemēru summēsim skaitļus ar aizstājējzīmēm:
Zvaigznīte (*) Aizstājējzīme
Piemēram, ja vēlaties summēt visu Apple produktu daudzumus, izmantojiet šo formulu:
=SUMIF(A2:A14,"Ābols*",B2:B14)Šī SUMIF formula atrod visus produktus, kuru sākumā ir vārds “Apple” un jebkāds rakstzīmju skaits pēc tā (apzīmēts ar “*”). Kad atbilstība ir atrasta, tā apkopo Daudzums skaitļi, kas atbilst atbilstošajām teksta virknēm.
Kritērijās ir iespējams izmantot arī vairākas aizstājējzīmes. Varat arī ievadīt aizstājējzīmes ar šūnu atsaucēm, nevis tiešo tekstu.
Lai to izdarītu, aizstājējzīmes ir jāieliek pēdiņās (“”) un jāsavieno ar šūnas atsauci(-ēm):
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Šī formula saskaita visu to produktu daudzumus, kuros ir vārds “Redmi”, neatkarīgi no tā, kur virknē vārds atrodas.
Jautājuma zīme (?) Aizstājējzīme
Varat izmantot jautājuma zīmi (?), lai saskaņotu teksta virknes ar jebkuru atsevišķu rakstzīmi.
Piemēram, ja vēlaties atrast visu Xiaomi Redmi 9 variantu daudzumus, varat izmantot šo formulu:
=SUMIF(A2:A14;Xiaomi Redmi 9?,B2:B14)Iepriekš minētā formula meklē teksta virknes ar vārdu “Xiaomi Redmi 9”, kam seko jebkura atsevišķa rakstzīme, un summē atbilstošo Daudzums cipariem.
Tilde (~) Aizstājējzīme
Ja vēlaties saskaņot faktisko jautājuma zīmi (?) vai zvaigznītes rakstzīmi (*), ievietojiet tildes (~) rakstzīmi pirms aizstājējzīmes formulas nosacījuma daļā.
Lai pievienotu daudzumus kolonnā B ar atbilstošo virkni, kuras beigās ir zvaigznītes zīme, ievadiet tālāk norādīto formulu.
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Lai B kolonnā pievienotu daudzumus, kuriem A slejā tajā pašā rindā ir jautājuma zīme (?), izmēģiniet tālāk norādīto formulu.
=SUMIF(A2:A14,"~?",B2:B14)
SUMIF funkcija ar datuma kritērijiem
Funkcija SUMIF var arī palīdzēt nosacīti summēt vērtības, pamatojoties uz datuma kritērijiem – piemēram, skaitļiem, kas atbilst noteiktam datumam, pirms datuma vai pēc datuma. Varat arī izmantot jebkuru no salīdzināšanas operatoriem ar datuma vērtību, lai izveidotu datuma kritērijus skaitļu summēšanai.
Datums jāievada Google izklājlapu atbalstītajā datuma formātā vai kā šūnas atsauce, kas satur datumu, vai izmantojot datuma funkciju, piemēram, DATE() vai TODAY().
Mēs izmantosim šo izklājlapas piemēru, lai parādītu, kā darbojas funkcija SUMIF ar datuma kritērijiem:
Pieņemsim, ka iepriekš minētajā datu kopā vēlaties summēt pārdošanas apjomus, kas notikuši 2019. gada 29. novembrī vai pirms tā, varat pievienot šos pārdošanas numurus, izmantojot funkciju SUMIF, vienā no šiem veidiem:
=SUMIF(C2:C13,"<=2019. gada 29. novembris",B2:B13)Iepriekš minētā formula pārbauda katru šūnu no C2 līdz C13 un atbilst tikai tām šūnām, kurās ir datumi, kas ir 2019. gada 29. novembris (29.11.2019.) vai agrāk. Un pēc tam summē pārdošanas apjomu, kas atbilst atbilstošajām šūnām no šūnu diapazona B2:B13, un parāda rezultātu šūnās E3.
Formulā datumu var norādīt jebkurā Google izklājlapu atpazītā formātā, piemēram, “2019. gada 29. novembris”, “2019. gada 29. novembris” vai “2019. gada 29. novembris” utt. Atcerieties datuma vērtību, un operatoram ir vienmēr jāliek pēdiņās.
Varat arī izmantot funkciju DATE() kritērijos, nevis tiešo datuma vērtību:
=SUMF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
Vai arī formulas kritēriju daļā varat izmantot šūnas atsauci, nevis datumu:
=SUMF(C2:C13,"<="&E2,B2:B13)
Ja vēlaties pievienot pārdošanas apjomus, pamatojoties uz šodienas datumu, kritērija argumentā varat izmantot funkciju TODAY().
Piemēram, lai summētu visas pārdošanas summas šodienas datumā, izmantojiet šo formulu:
=SUMF(C2:C13,ŠODIEN(),B2:B13)
SUMIF funkcija ar tukšām vai netukšām šūnām
Dažreiz jums var būt nepieciešams summēt skaitļus šūnu diapazonā ar tukšām vai netukšām šūnām tajā pašā rindā. Šādos gadījumos varat izmantot funkciju SUMIF, lai summētu vērtības, pamatojoties uz kritērijiem, kur šūnas ir tukšas vai nav.
Summa, ja tukša
Pakalpojumā Google izklājlapas ir divi kritēriji, lai atrastu tukšas šūnas: “” vai “=”.
Piemēram, ja vēlaties summēt visu pārdošanas apjomu, kas satur nulles garuma virknes (vizuāli izskatās tukša) C slejā, izmantojiet pēdiņas bez atstarpēm formulā:
=SUMF(C2:C13,"",B2:B13)
Lai summētu visu pārdošanas apjomu B slejā ar pilnām tukšām šūnām C slejā, kā kritēriju iekļaujiet “=”.
=SUMF(C2:C13,"=",B2:B13)
Summa, ja nav tukša:
Ja vēlaties summēt šūnas, kurās ir jebkura vērtība (nav tukša), kā kritēriju formulā varat izmantot “”.
Piemēram, lai iegūtu kopējo pārdošanas apjomu ar jebkuriem datumiem, izmantojiet šo formulu:
=SUMF(C2:C13,"",B2:B13)
SUMIF, pamatojoties uz vairākiem kritērijiem ar VAI loģiku
Kā mēs esam redzējuši līdz šim, funkcija SUMIF ir paredzēta skaitļu summēšanai, pamatojoties tikai uz vienu kritēriju, taču ir iespējams summēt vērtības, pamatojoties uz vairākiem kritērijiem, izmantojot SUMIF funkciju Google izklājlapās. To var izdarīt, apvienojot vairāk nekā vienu SUMIF funkciju vienā formulā ar VAI loģiku.
Piemēram, ja vēlaties summēt pārdošanas apjomu “Rietumu” reģionā vai “Dienvidu” reģionā (VAI loģika) norādītajā diapazonā (B2:B13), izmantojiet šo formulu:
=SUMIF(C2:C13,"Rietumi",B2:B13)+SUMIF(C2:C13,"Dienvidi",B2:B13)Šī formula summē šūnas, ja vismaz viens no nosacījumiem ir TRUE. Tāpēc to sauc par "OR loģiku". Tas arī summēs vērtības, kad būs izpildīti visi nosacījumi.
Pirmā formulas daļa pārbauda diapazonu C2:C13 tekstam “Rietumi” un summē vērtības diapazonā B2:B13, kad atbilstība ir izpildīta. Otrā daļa pārbauda teksta vērtību “Dienvidi” tajā pašā diapazonā C2:C13 un pēc tam summē vērtības ar atbilstošo tekstu tajā pašā summas_diapazonā B2:B13. Pēc tam abas summas tiek saskaitītas kopā un parādītas šūnā E3.
Ja ir izpildīts tikai viens kritērijs, tas atgriež tikai šīs summas vērtību.
Varat arī izmantot vairākus kritērijus, nevis tikai vienu vai divus. Un, ja izmantojat vairākus kritērijus, labāk ir izmantot šūnas atsauci kā kritēriju, nevis rakstīt formulā tiešo vērtību.
=SUMF(C2:C13,E2,B2:B13)+SUMF(C2:C13,E3,B2:B13)+SUMF(C2:C13,E4,B2:B13)
SUMIF ar VAI loģiku pievieno vērtības, ja ir izpildīts vismaz viens no norādītajiem kritērijiem, bet, ja vēlaties summēt vērtības tikai tad, kad ir izpildīti visi norādītie nosacījumi, jums ir jāizmanto tā jaunā brāļa funkcija SUMIFS().
SUMIFS funkcija Google izklājlapās (vairāki kritēriji)
Ja izmantojat funkciju SUMIF, lai summētu vērtības, pamatojoties uz vairākiem kritērijiem, formula var kļūt pārāk gara un sarežģīta, un jūs varat kļūdīties. Turklāt SUMIF ļaus jums summēt vērtības tikai vienā diapazonā un tad, ja kāds no nosacījumiem ir TRUE. Šeit parādās SUMIFS funkcija.
Funkcija SUMIFS palīdz summēt vērtības, pamatojoties uz vairākiem atbilstības kritērijiem vienā vai vairākos diapazonos. Un tas darbojas uz UN loģikas, kas nozīmē, ka tas var summēt vērtības tikai tad, ja ir izpildīti visi norādītie nosacījumi. Pat ja viens nosacījums ir nepatiess, tas atgriezīs “0”.
Funkciju SUMIFS sintakse un argumenti
Funkcijas SUMIFS sintakse ir šāda:
=SUMIFS(summa_diapazons, kritēriju_diapazons1, kritērijs1, [kritēriju_diapazons, ...], [kritērijs2, ...])kur,
- summa_diapazons — Šūnu diapazons, kurā ir vērtības, kuras vēlaties summēt, kad ir izpildīti visi nosacījumi.
- kritēriju_diapazons1 — Tas ir šūnu diapazons, kurā pārbaudāt kritērijus1.
- kritērijs 1 - Tas ir nosacījums, kas jums jāpārbauda pret kritēriju_diapazons1.
- kriteria_range2, kritērijs2,…– papildu novērtēšanas diapazoni un kritēriji. Un jūs varat pievienot formulai vairāk diapazonu un nosacījumu.
Mēs izmantosim datu kopu nākamajā ekrānuzņēmumā, lai parādītu, kā funkcija SUMIFS darbojas ar dažādiem kritērijiem.
SUMIFS ar teksta nosacījumiem
Varat summēt vērtības, pamatojoties uz diviem dažādiem teksta kritērijiem dažādos diapazonos. Piemēram, pieņemsim, ka vēlaties uzzināt piegādātās Telts preces kopējo pārdošanas apjomu. Šim nolūkam izmantojiet šo formulu:
=SUMIFS(D2:D13,A2:A13"Telts",C2:C13"Piegādāts")Šajā formulā mums ir divi kritēriji: “Telts” un “Piegādāts”. Funkcija SUMIFS pārbauda vienumu “Telts” (1. kritērijs) diapazonā A2:A13 (kritēriju_diapazons1) un pārbauda statusu “Piegādāts” (2. kritērijs) diapazonā C2:C13 (kritēriju_diapazons2). Ja abi nosacījumi ir izpildīti, tas summē atbilstošo vērtību šūnu diapazonā D2:D13 (summa_diapazons).
SUMIFS ar skaitļu kritērijiem un loģiskajiem operatoriem
Varat izmantot nosacījumu operatorus, lai izveidotu nosacījumus ar skaitļiem funkcijai SUMIFS.
Lai Kalifornijas štatā (CA) uzzinātu kopējo pārdošanas apjomu vairāk nekā 5 preces daudzumam, izmantojiet šo formulu:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13"CA")Šai formulai ir divi nosacījumi: “>5” un “CA”.
Šī formula pārbauda daudzumus (Qty), kas ir lielāki par 5 diapazonā D2:D13, un pārbauda stāvokli “CA” diapazonā B2:B13. Un, ja abi nosacījumi ir izpildīti (tas nozīmē, ka tie ir vienā rindā), summa tiek summēta E2:E13.
SUMIFS ar datuma kritērijiem
Funkcija SUMIFS ļauj arī pārbaudīt vairākus nosacījumus vienā diapazonā, kā arī dažādos diapazonos.
Pieņemsim, ka vēlaties pārbaudīt piegādāto preču kopējo pārdošanas apjomu pēc 31.05.2021 un pirms 2021.10.06. datuma, tad izmantojiet šo formulu:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Iepriekš minētajai formulai ir trīs nosacījumi: 31/5/2021, 10/5/2021 un Piegādāts. Tā vietā, lai izmantotu tiešās datuma un teksta vērtības, mēs atsaucāmies uz šūnām, kas satur šos kritērijus.
Formula pārbauda datumus pēc 31.05.2021 (G1) un datumus pirms 10/6/2021 (G2) tajā pašā diapazonā D2:D13 un pārbauda statusu “Piegādāts” starp šiem diviem datumiem. Pēc tam summējiet saistīto summu diapazonā E2:E13.
SUMIFS ar tukšām un netukšām šūnām
Dažreiz, iespējams, vēlēsities atrast vērtību summu, kad attiecīgā šūna ir tukša vai nav. Lai to izdarītu, varat izmantot vienu no trim iepriekš apspriestajiem kritērijiem: “=”, “” un “”.
Piemēram, ja vēlaties tikai summēt “Telts” preču daudzumu, kurām piegādes datums vēl nav apstiprināts (tukšas šūnas), varat izmantot “=” kritērijus:
=SUMIFS(D2:D13,A2:A13,"Telts",C2:C13,"=")Formula meklē vienumu “Telts” (1. kritērijs) A slejā ar atbilstošām tukšām šūnām (2. kritērijs) C slejā un pēc tam summē atbilstošo summu D slejā. “=” apzīmē pilnīgi tukšu šūnu.
Lai atrastu “Telts” preču summu, kurām ir apstiprināts piegādes datums (nevis tukšas šūnas), izmantojiet “” kā kritēriju:
=SUMIFS(D2:D13,A2:A13"Telts",C2:C13"")Mēs tikko nomainījām “=” pret “” šajā formulā. Tas atrod telts vienumu summu ar netukšām šūnām kolonnā C.
SUMIFS ar VAI Logic
Tā kā funkcija SUMIFS darbojas uz UN loģikas, tā summē tikai tad, ja ir izpildīti visi nosacījumi. Bet ko darīt, ja vēlaties summēt vērtību, pamatojoties uz vairākiem kritērijiem, ja ir izpildīts kāds no kritērijiem. Viltība ir izmantot vairākas SUMIFS funkcijas.
Piemēram, ja vēlaties saskaitīt “Velosipēdu bagāžnieka” VAI “Mugursomas” pārdošanas apjomu, ja to statuss ir “Pasūtīts”, izmēģiniet šo formulu:
=SUMIFS(D2:D13,A2:A13"Velosipēdu bagāžnieks",C2:C13"Pasūtīts") +SUMIFS(D2:D13,A2:A13"Mugursoma",C2:C13"Pasūtīts")Pirmā SUMIFS funkcija pārbauda divus kritērijus “Velosipēdu bagāžnieks” un “Pasūtīts” un summē summas vērtības kolonnā D. Pēc tam otrā SUMIFS pārbauda divus kritērijus “Mugursoma” un “Pasūtīts” un summē summas vērtības kolonnā D. Un tad , abas summas tiek saskaitītas un parādītas uz F3. Vienkāršiem vārdiem sakot, šī formula summē, kad tiek pasūtīts “Velosipēda bagāžnieks” vai “Mugursoma”.
Tas ir viss, kas jums jāzina par SUMIF un SUMIFS funkcijām Google izklājlapās.