Šī apmācība parāda, kā lietot Izcilael SUMIF un SUMIFS Funkcijajiem programmā Excel un Google izklājlapas, lai apkopotu datus, kas atbilst noteiktiem kritērijiem.
SUMIF funkciju pārskats
Funkciju SUMIF programmā Excel varat izmantot, lai summētu šūnas, kurās ir noteikta vērtība, summētu šūnas, kas ir lielākas vai vienādas ar vērtību utt.
(Ievērojiet, kā parādās formulas ievades)
SUMIF funkciju sintakse un argumenti:
| 1 | = SUMIF (diapazons, kritēriji, [summas diapazons]) |
diapazons - Šūnu diapazons, pret kuru vēlaties piemērot kritērijus.
kritērijiem - Kritēriji, ko izmanto, lai noteiktu, kuras šūnas pievienot.
summas_diapazons - [pēc izvēles] Šūnas, kas jāpievieno. Ja summas diapazons tiek izlaists, diapazona šūnas tiek saskaitītas kopā.
Kas ir SUMIF funkcija?
Funkcija SUMIF ir viena no vecākajām funkcijām, ko izmanto izklājlapās. To izmanto, lai skenētu šūnu diapazonu, pārbaudot konkrētu kritēriju un pēc tam saskaitot vērtības diapazonā, kas atbilst šīm vērtībām. Sākotnējā SUMIF funkcija bija ierobežota tikai ar vienu kritēriju. Pēc 2007. gada tika izveidota funkcija SUMIFS, kas pieļauj dažādus kritērijus. Lielākā daļa vispārējā lietojuma starp abiem paliek nemainīgi, taču sintaksē ir dažas būtiskas atšķirības, kuras mēs apspriedīsim šajā rakstā.
Ja vēl neesat to izdarījis, varat pārskatīt lielu daļu līdzīgas struktūras un piemērus rakstā COUNTIFS.
Pamata piemērs
Apskatīsim šo reģistrēto pārdošanas sarakstu un vēlamies uzzināt kopējos ienākumus.
Tā kā mums bija izdevumi, negatīvā vērtība, mēs nevaram veikt tikai pamatsummu. Tā vietā mēs vēlamies summēt tikai tās vērtības, kas ir lielākas par 0. “Lielāks par 0” ir mūsu kritēriji SUMIF funkcijā. Mūsu formula, kā to pateikt
| 1 | = SUMIF (A2: A7, "> 0") |
Divu kolonnu piemērs
Lai gan sākotnējā SUMIF funkcija tika izstrādāta, lai ļautu jums piemērot kritēriju skaitļu diapazonam, kuru vēlaties summēt, lielāko daļu laika jums būs jāpiemēro viens vai vairāki kritēriji citām kolonnām. Apskatīsim šo tabulu:
Tagad, ja mēs izmantojam sākotnējo SUMIF funkciju, lai uzzinātu, cik banānu mums ir (uzskaitīti šūnā D1), mums būs jānorāda vēlamais diapazons summa kā pēdējais arguments, un tā būtu mūsu formula
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Tomēr, kad programmētāji galu galā saprata, ka lietotāji vēlas sniegt vairāk nekā vienu kritēriju, tika izveidota funkcija SUMIFS. Lai izveidotu vienu struktūru, kas derētu vairākiem kritērijiem, SUMIFS pieprasa vispirms norādīt summu diapazonu. Mūsu piemērā tas nozīmē, ka formulai ir jābūt
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
PIEZĪME. Abām formulām ir vienāds rezultāts un tās var izskatīties līdzīgas, tāpēc pievērsiet uzmanību tam, kura funkcija tiek izmantota, lai pārliecinātos, ka visi argumenti ir uzskaitīti pareizā secībā.
Darbs ar datumiem, vairāki kritēriji
Strādājot ar datumiem izklājlapā, lai gan datumu ir iespējams ievadīt tieši formulā, vislabāk ir datumu iekļaut šūnā, lai jūs varētu vienkārši atsaukties uz šūnu šūnā. Piemēram, tas palīdz datoram zināt, ka vēlaties izmantot datumu 27.5.2020, nevis skaitli 5 dalot ar 27, dalot ar 2022. gadu.
Apskatīsim nākamo tabulu, kurā reģistrēts vietnes apmeklētāju skaits ik pēc divām nedēļām.
Mēs varam norādīt diapazona sākuma un beigu punktus, kurus vēlamies apskatīt D2 un E2. Mūsu formula, lai summētu apmeklētāju skaitu šajā diapazonā, varētu būt šāda:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Ņemiet vērā, kā mēs varējām apvienot “=” salīdzinājumus ar šūnu atsaucēm, lai izveidotu kritērijus. Turklāt, lai gan abi kritēriji tika piemēroti vienam un tam pašam šūnu diapazonam (A2: A7), diapazons ir jāraksta divreiz, vienu reizi katrā kritērijā.
Vairākas kolonnas
Izmantojot vairākus kritērijus, varat tos piemērot tam pašam diapazonam, kā mēs to darījām iepriekšējā piemērā, vai arī varat tos piemērot dažādiem diapazoniem. Apvienosim mūsu datu paraugus šajā tabulā:
Mēs esam iestatījuši dažas šūnas, lai lietotājs šūnās E2 līdz G2 ievadītu to, ko vēlas meklēt. Tādējādi mums ir vajadzīga formula, kas saskaitīs februārī savākto ābolu kopskaitu. Mūsu formula izskatās šādi:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS ar OR tipa loģiku
Līdz šim visi izmantotie piemēri ir bijuši salīdzināmi ar AND, un mēs meklējam rindas, kas atbilst visiem mūsu kritērijiem. Tagad mēs izskatīsim gadījumu, kad vēlaties meklēt iespēju, ka rinda atbilst vienam vai otram kritērijam.
Apskatīsim šo pārdošanas sarakstu:
Mēs vēlētos saskaitīt kopējos pārdošanas apjomus gan Ādamam, gan Bobam. Lai to izdarītu, jums ir pāris iespējas. Vienkāršākais ir pievienot divus SUMIFS kopā, piemēram:
| 1 | = SUMIFS (B2: B7, A2: A7, "Ādams")+SUMIFS (B2: B7, A2: A7, "Bobs") |
Šeit mēs esam likuši datoram aprēķināt mūsu individuālos rādītājus un pēc tam tos saskaitīt.
Mūsu nākamā iespēja ir piemērota, ja jums ir vairāk kritēriju diapazonu, piemēram, ja nevēlaties, lai visa formula būtu jāpārraksta atkārtoti. Iepriekšējā formulā mēs manuāli teicām datoram pievienot divus dažādus SUMIFS kopā. Tomēr to var izdarīt, ierakstot savus kritērijus masīvā, piemēram:
| 1 | = SUM (SUMIFS (B2: B7, A2: A7, {"Ādams", "Bobs"})) |
Paskatieties, kā masīvs ir veidots cirtainās iekavās. Kad dators novērtēs šo formulu, tas zinās, ka mēs vēlamies aprēķināt SUMIFS funkciju katram mūsu masīva vienumam, tādējādi izveidojot skaitļu masīvu. Ārējā SUM funkcija pēc tam ņems šo skaitļu masīvu un pārvērtīs to par vienu skaitli. Veicot formulas novērtējumu, tas izskatītos šādi:
| 123 | = SUM (SUMIFS (B2: B7, A2: A7, {"Ādams", "Bobs"}))= SUM (27401, 43470)= 70871 |
Mēs iegūstam tādu pašu rezultātu, bet mēs varējām formulēt formulējumu nedaudz lakoniskāk.
Darbs ar sagatavēm
Dažreiz jūsu datu kopā ir tukšas šūnas, kuras jums jāatrod vai jāizvairās. To kritēriju noteikšana var būt nedaudz sarežģīta, tāpēc apskatīsim citu piemēru.
Ņemiet vērā, ka šūna A3 ir patiesi tukša, savukārt šūnā A5 ir formula, kas atgriež nulles garuma virkni “”. Ja mēs vēlamies atrast kopējo summu patiesi tukšas šūnas, mēs izmantotu kritēriju “=”, un mūsu formula izskatītos šādi:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
No otras puses, ja mēs vēlamies iegūt summu visām šūnām, kuras vizuāli izskatās tukšas, mēs mainīsim kritērijus uz “”, un formula izskatās šādi
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Apvērsīsim to: ko darīt, ja vēlaties atrast tukšo šūnu summu? Diemžēl pašreizējais dizains neļaus jums izvairīties no nulles garuma virknes. Jūs varat izmantot kritēriju “”, bet, kā redzams piemērā, tas joprojām ietver vērtību no 5. rindas.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Ja jums nav jāskaita šūnas, kurās ir nulles garuma virknes, apsveriet iespēju izmantot funkciju LEN SUMPRODUCT
SUMIF Google izklājlapās
Funkcija SUMIF Google izklājlapās darbojas tieši tāpat kā programmā Excel:
