Šiame straipsnyje mes sužinosime, kaip naudoti funkciją „IF“ vietoj funkcijų „SUMPRODUCT“ ir „SUMIFS“ programoje „Excel“.
Scenarijus:
Paprastais žodžiais tariant, kai dirbame su ilgu išsklaidytu duomenų rinkiniu, kartais turime rasti skaičių sumą su tam tikrais kriterijais. Pavyzdžiui, nustatant atlyginimų sumą konkrečiame skyriuje arba turint kelis kriterijus pagal datą, pavadinimus, departamentą ar net numerius, pvz., Atlyginimai yra mažesni už vertę arba kiekis viršija vertę. Tam dažniausiai naudojate SUMPRODUCT arba SUMIFS funkciją. Bet jūs netikėtumėte, jūs atliekate tą pačią funkciją su „Excel“ pagrindine funkcija IF.
Kaip išspręsti problemą?
Turite galvoti, kaip tai įmanoma, kad atliktumėte logines operacijas per lentelių masyvus naudodami IF funkciją. IF funkcija „Excel“ yra labai naudinga, ji padės jums atlikti sudėtingas užduotis „Excel“ ar kitomis kodavimo kalbomis. Funkcija IF išbando masyvo sąlygas, atitinkančias reikiamas reikšmes, ir grąžina rezultatą kaip masyvą, atitinkantį tikrąsias sąlygas kaip 1, o klaidingą kaip 0.
Norėdami išspręsti šią problemą, naudosime šias funkcijas:
- SUM funkcija
- IF funkcija
Mums reikės šių aukščiau išvardytų funkcijų ir tam tikro pagrindinio duomenų veikimo pojūčio. logines sąlygas masyvams galima taikyti naudojant loginius operatorius. Šie logikos operatoriai dirba ir su tekstu, ir su skaičiais. Žemiau pateikiama bendra formulė. { } garbanotieji breketai yra stebuklingas įrankis masyvo formulėms su IF funkcija atlikti.
Bendra formulė:
| { = SUM (IF ((loginis_1) * (loginis_2) *… * (loginis_n), sumos_masyvas)) } |
Pastaba: garbanotiems petnešoms ( { } ) Naudokite Ctrl + Shift + Enter kai dirbate su „Excel“ masyvais ar diapazonais. Pagal numatytuosius nustatymus formulėje bus sugeneruoti garbanotieji skliausteliai. NEBANDYKITE koduoti garbanotų skliaustų simbolių.
1 logika: testuoja 1 sąlygą 1 masyve
2 logika: testuoja 2 sąlygą 2 masyve ir pan
sum_array: masyvas, operacijos suma atliekama
Pavyzdys :
Visa tai gali būti painu suprasti. Taigi, išbandykime šią formulę, paleisdami ją žemiau pateiktame pavyzdyje. Čia turime duomenis apie pristatytus produktus į skirtingus miestus kartu su atitinkamais kategorijų laukais ir kiekiais. Čia mes turime duomenis ir turime rasti į Bostoną siunčiamų slapukų kiekį, kurio kiekis yra didesnis nei 40.
Duomenų lentelė ir kriterijų lentelė yra parodyta aukščiau esančiame paveikslėlyje. Norėdami suprasti, naudojamiems masyvams naudojome pavadintus diapazonus. Pavadinti diapazonai yra išvardyti žemiau.
Čia:
Miestas, nustatytas A2 masyvui: A17.
B2 kategorijai apibrėžta kategorija: A17.
Masyvui C2 apibrėžtas kiekis: C17.
Dabar esate pasiruošę gauti norimą rezultatą naudodami žemiau pateiktą formulę.
Naudokite formulę:
| { = SUM (IF ((miestas = "Bostonas") * (kategorija = "slapukai") * (kiekis> 40), kiekis)) } |
Paaiškinimas:
- Miestas = "Bostonas": patikrina miesto diapazono vertes, kad atitiktų "Boston".
- Kategorija = „Slapukai“: patikrina kategorijų diapazono reikšmes, kad jos atitiktų „Slapukus“.
- Kiekis> 40: tikrina kiekių diapazono reikšmes iki ma
- Kiekis turi būti masyvas, kuriame reikalinga suma.
- IF funkcija patikrina visus kriterijus ir žvaigždutė char (*) padaugina visus masyvo rezultatus.
= SUMA (IF ({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}))
- Dabar IF funkcija grąžina tik kiekius, atitinkančius 1s, o likusi dalis ignoruojama.
- SUM funkcija grąžina SUM.
Dabar kiekis, atitinkantis 1, tik susumuoja, kad gautumėte rezultatą.
Kaip matote, grąžinamas 43 kiekis, tačiau į „Boston“ pristatomi trys slapukų užsakymai, kurių kiekis 38, 36 ir 43. Mums reikėjo kiekio sumos, kai kiekis viršija 40. Taigi formulė grąžina tik 43. Dabar naudokite kitus kriterijus, kad gautumėte SUM kiekį mieste: „Los Andželas“ ir kategoriją: „Barai“ ir kiekis būtų mažesnis nei 50.
Naudokite formulę
| { = SUM (IF ((miestas = "Los Andželas") * (kategorija = "barai") * (kiekis <50), kiekis)) } |
Kaip matote, formulė grąžina reikšmes 86. Tai yra 2 užsakymų, atitinkančių sąlygas, kurių kiekis 44 ir 42, suma. Šis straipsnis iliustruoja, kaip pakeisti įdėtą IF formulę vienu IF masyvo formulėje. Tai gali būti naudojama sudėtingoms formulėms sumažinti. Tačiau šią konkrečią problemą galima lengvai išspręsti naudojant SUMIFS arba SUMPRODUCT funkciją.
SUMPRODUCT funkcijos naudojimas:
Funkcija SUMPRODUCT grąžina masyvo atitinkamų verčių sumą. Taigi mes gausime, kad masyvai grąžintų 1s a True teiginio reikšmes ir 0s False teiginio reikšmes. Taigi paskutinė suma bus atitinkama ten, kur visi teiginiai yra teisingi.
Naudokite formulę:
| = SUMPRODUCT ( - (miestas = "Boston"), - (Category = "Cookies"), - (Kiekis> 40), Kiekis) |
-: operacija, naudojama konvertuojant visas TRUEs į 1s ir False į 0.
Funkcija SUMPRODUCT iš naujo tikrina aukščiau paaiškintos SUM ir IF funkcijos grąžintą kiekio SUM.
Panašiai ir antrojo pavyzdžio rezultatas yra tas pats.
Kaip matote, SUMPRODUCT funkcija gali atlikti tą pačią užduotį.
Čia yra visos pastabos, susijusios su formulės naudojimu.
Pastabos:
- Formulėje sum_masas veikia tik su skaičiais.
- Jei formulė pateikia #VALUE klaidą, patikrinkite, ar garbanotieji skliausteliai turi būti formulėje, kaip parodyta straipsnio pavyzdžiuose.
- Neigiant (-) char reikšmės TRUEs arba 1s keičiamos į FALSEs arba 0s, o FALSEs arba 0s į TRUEs arba 1s.
- Operacijos panašios į ( = ), mažesnis nei lygus ( <= ), geresnis negu ( > ) arba nelygi () gali būti atlikta taikant formulę, naudojant tik skaičius.
Tikimės, kad šis straipsnis apie tai, kaip naudoti „IF“ funkciją vietoj „SUMPRODUCT“ ir „SUMIFS“ funkcijų „Excel“, yra aiškinamasis. Daugiau straipsnių apie apibendrinimo formules rasite čia. Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su savo draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Parašykite mums el
Kaip naudoti „SUMPRODUCT“ funkciją programoje „Excel“: Grąžina SUM, padauginus reikšmes keliuose „Excel“ masyvuose.
SUM, jei data yra tarp : Grąžina verčių SUM tarp nurodytų datų ar „Excel“ laikotarpio.
Suma, jei data yra didesnė už nurodytą datą: Grąžina verčių SUM po nurodytos datos ar „Excel“ laikotarpio.
2 būdai suskaičiuoti pagal mėnesį „Excel“: Grąžina verčių SUM per tam tikrą „Excel“ mėnesį.
Kaip suskaičiuoti kelis stulpelius su sąlyga: Grąžina verčių SUM keliuose stulpeliuose su sąlyga „Excel“.
Populiarūs straipsniai:
50 „Excel“ spartusis klavišas produktyvumui padidinti : Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis t„VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.
Kaip naudoti funkciją „COUNTIF“ programoje „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.