Šajā rakstā mēs uzzināsim, kā programmā Excel izmantot funkciju SUMPRODUCT un SUMIFS, nevis IF.
Scenārijs:
Vienkārši sakot, strādājot ar garu izkliedētu datu kopu, dažreiz mums ir jāatrod skaitļu summa ar dažiem kritērijiem. Piemēram, atrodot algu summu noteiktā nodaļā vai ja ir vairāki kritēriji attiecībā uz datumu, nosaukumiem, nodaļām vai pat var saskaitīt datus, piemēram, algas zem vērtības vai daudzums virs vērtības. Šim nolūkam jūs parasti izmantojat SUMPRODUCT vai SUMIFS funkciju. Bet jūs neticētu, jūs veicat to pašu funkciju ar Excel pamatfunkcijas IF funkciju.
Kā atrisināt problēmu?
Jums ir jādomā, kā tas ir iespējams, lai veiktu loģiskas darbības ar tabulu masīviem, izmantojot IF funkciju. IF funkcija programmā Excel ir ļoti noderīga, tā palīdzēs jums veikt dažus sarežģītus uzdevumus programmā Excel vai citās kodēšanas valodās. IF funkcija pārbauda nosacījumus masīvā, kas atbilst nepieciešamajām vērtībām, un atgriež rezultātu kā masīvu, kas atbilst patiesajiem nosacījumiem kā 1 un False kā 0.
Lai atrisinātu šo problēmu, mēs izmantosim šādas funkcijas:
- SUM funkcija
- IF funkcija
Mums būs nepieciešamas šīs iepriekš minētās funkcijas un dažas pamata datu darbības izjūtas. loģiskos nosacījumus masīvos var piemērot, izmantojot loģiskos operatorus. Šie loģikas operatori strādā gan ar tekstu, gan cipariem. Zemāk ir vispārīga formula. { } cirtainās breketes ir burvju rīks, lai veiktu masīva formulas ar IF funkciju.
Vispārējā formula:
| { = SUM (JA ((loģisks_1) * (loģisks_2) *… * (loģisks_n), summas_masīvs)) } |
Piezīme: cirtainām breketēm ( { } ) Izmantot Ctrl + Shift + Enter strādājot ar masīviem vai diapazoniem programmā Excel. Tas pēc noklusējuma formulā ģenerēs cirtainās breketes. Nemēģiniet kodēt cirtainās breketes.
Loģika 1: pārbauda 1. nosacījumu 1. masīvā
Loģiskais 2: pārbauda 2. nosacījumu 2. masīvā un tā tālāk
sum_array: masīvs, tiek veikta operācijas summa
Piemērs :
To visu var būt mulsinoši saprast. Tātad, pārbaudīsim šo formulu, palaižot to zemāk redzamajā piemērā. Šeit mums ir dati par piegādātajiem produktiem uz dažādām pilsētām, kā arī atbilstošie kategorijas lauki un daudzumi. Šeit mums ir dati, un mums jāatrod uz Bostonu nosūtīto sīkdatņu daudzums, ja to daudzums ir lielāks par 40.
Datu tabula un kritēriju tabula ir parādīta iepriekš redzamajā attēlā. Lai saprastu, izmantotajiem masīviem mēs izmantojām nosauktos diapazonus. Nosauktie diapazoni ir uzskaitīti zemāk.
Šeit :
Masīvai A2 definētā pilsēta: A17.
Masīvam B2 definētā kategorija: A17.
Masīvam C2 definētais daudzums: C17.
Tagad jūs esat gatavs iegūt vēlamo rezultātu, izmantojot zemāk esošo formulu.
Izmantojiet formulu:
| { = SUM (JA ((pilsēta = "Boston") * (kategorija = "sīkfaili") * (daudzums> 40), daudzums)) } |
Paskaidrojums:
- City = "Boston": pārbauda pilsētas diapazona vērtības, lai tās atbilstu "Boston".
- Kategorija = "Sīkfaili": pārbauda kategorijas diapazona vērtības, lai tās atbilstu "Sīkfailiem".
- Daudzums> 40: pārbauda vērtības daudzuma diapazonā līdz ma
- Daudzums ir masīvs, kur nepieciešama summa.
- Funkcija IF pārbauda visus kritērijus un zvaigznīte char (*) reizina visus masīva rezultātus.
= SUM (JA ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Tagad IF funkcija atgriež tikai daudzumus, kas atbilst 1s, un pārējie tiek ignorēti.
- Funkcija SUM atgriež SUM.
Tagad daudzums, kas atbilst 1, tiek summēts tikai, lai iegūtu rezultātu.
Kā redzat, daudzums 43 tiek atgriezts, bet uz Bostonu tiek piegādāti trīs sīkfailu pasūtījumi, kuru daudzums ir 38, 36 un 43. Mums bija nepieciešama daudzuma summa, ja daudzums pārsniedz 40. Tātad formula atgriež tikai 43. Tagad izmantojiet citus kritērijus, lai iegūtu SUM daudzumu pilsētai: "Losandželosa" un kategorija: "Bāri" un daudzums ir mazāks par 50.
Izmantojiet formulu
| { = SUM (JA ((pilsēta = "Losandželosa") * (kategorija = "bāri") * (daudzums <50), daudzums)) } |
Kā redzat, formula atgriež vērtības 86. Kas ir 2 pasūtījumu summa, kas atbilst nosacījumiem ar daudzumu 44 un 42. Šis raksts parāda, kā ligzdotu IF formulu aizstāt ar vienu IF masīva formulā. To var izmantot, lai samazinātu sarežģītību sarežģītās formulās. Tomēr šo problēmu var viegli atrisināt, izmantojot funkciju SUMIFS vai SUMPRODUCT.
Funkcijas SUMPRODUCT izmantošana:
Funkcija SUMPRODUCT atgriež atbilstošo vērtību summu masīvā. Tātad mēs panāksim, ka masīvi atgriež 1s a patiesā paziņojuma vērtības un 0s - nepatiesā paziņojuma vērtības. Tātad pēdējā summa būs atbilstoša tur, kur visi apgalvojumi ir patiesi.
Izmantojiet formulu:
| = SUMPRODUCT ( - (City = "Boston"), - (Category = "Cookies"), - (Daudzums> 40), Daudzums) |
-: darbība, ko izmanto, lai pārvērstu visus TRUE par 1s un False uz 0.
Funkcija SUMPRODUCT atkārtoti pārbauda SUM un IF funkcijas atgrieztā daudzuma SUM.
Līdzīgi otrajam piemēram, rezultāts ir tāds pats.
Kā redzat, SUMPRODUCT funkcija var veikt to pašu uzdevumu.
Šeit ir visas novērošanas piezīmes par formulas izmantošanu.
Piezīmes:
- Formulas summa_masīvs darbojas tikai ar skaitļiem.
- Ja formula atgriež kļūdu #VALUE, pārbaudiet, vai cirtainās figūriekavas ir jābūt formulā, kā parādīts raksta piemēros.
- Nolieguma (-) simbols maina vērtības TRUEs vai 1s uz FALSEs vai 0s un FALSEs vai 0s uz TRUEs vai 1s.
- Darbības, piemēram, ir vienādas ar ( = ), kas ir mazāks par ( <= ), lielāks nekā ( > ) vai nav vienāds ar (), var veikt, izmantojot formulu, izmantojot tikai skaitļus.
Ceru, ka šis raksts par to, kā programmā Excel izmantot funkciju SUMPRODUCT un SUMIFS, ir paskaidrojošs. Vairāk rakstu par kopsavilkuma formulām meklējiet šeit. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē
Funkcijas SUMPRODUCT izmantošana programmā Excel: Atgriež SUM pēc vērtību reizināšanas vairākos Excel masīvos.
SUM, ja datums ir starp : Atgriež vērtību SUM starp Excel datumiem vai periodu.
Summa, ja datums ir lielāks par norādīto: Atgriež vērtību SUM pēc Excel noteiktā datuma vai perioda.
2 veidi, kā apkopot summu pa mēnešiem programmā Excel: Atgriež Excel summu noteiktā noteiktā mēnesī.
Kā apkopot vairākas kolonnas ar nosacījumu: Atgriež vērtību SUM vairākās kolonnās ar nosacījumu programmā Excel.
Populāri raksti:
50 Excel saīsne produktivitātes paaugstināšanai : Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā lietot tVLOOKUP funkcija programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā programmā Excel izmantot funkciju COUNTIF : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.