V tem članku se bomo naučili, kako uporabljati funkcijo IF namesto funkcij SUMPRODUCT in SUMIFS v Excelu.
Scenarij:
Preprosto povedano, pri delu z dolgim razpršenim naborom podatkov moramo včasih najti vsoto številk z nekaterimi merili. Na primer, če najdete vsoto plač v določenem oddelku ali imate več meril glede datuma, imen, oddelkov ali lahko celo oštevilčite podatke, kot so plače pod vrednostjo ali količina nad vrednostjo. Za to običajno uporabite funkcijo SUMPRODUCT ali SUMIFS. Ampak ne bi verjeli, isto funkcijo opravljate z Excelovo osnovno funkcijo IF.
Kako rešiti težavo?
Morate razmišljati, kako je to mogoče, da izvajate logične operacije nad matrikami tabel z uporabo funkcije IF. Če je funkcija IF v Excelu zelo uporabna, vas bo vodila skozi nekaj težkih nalog v Excelu ali katerem koli drugem kodiranju. Funkcija IF preizkusi pogoje na matriki, ki ustrezajo zahtevanim vrednostim, in vrne rezultat kot niz, ki ustreza pravilom True kot 1 in False kot 0.
Za to težavo bomo uporabljali naslednje funkcije:
- Funkcija SUM
- IF funkcija
Potrebovali bomo te zgornje funkcije in nekaj osnovnega občutka delovanja s podatki. logične pogoje za matrike je mogoče uporabiti z logičnimi operaterji. Ti logični operaterji delujejo tako na besedilo kot na številke. Spodaj je splošna formula. { } kodrasti oklepaji so čarobno orodje za izvajanje matričnih formul s funkcijo IF.
Splošna formula:
| { = SUM (IF ((logično_1) * (logično_2) *… * (logično_n), vsota_masa)) } |
Opomba: Za kodraste naramnice ( { } ) Uporaba Ctrl + Shift + Enter pri delu z matrikami ali obsegi v Excelu. To bo privzeto ustvarilo zavite oklepaje po formuli. NE poskušajte kodirati znakov kodrastih zagrad.
Logično 1: preizkusi pogoj 1 v nizu 1
Logično 2: preskusi pogoj 2 v nizu 2 itd
sum_array: matrika, izvede se vsota operacije
Primer:
Vse to je lahko zmedeno za razumevanje. Torej, preizkusimo to formulo tako, da jo zaženemo na spodnjem primeru. Tu imamo podatke o dobavljenih izdelkih v različna mesta skupaj z ustreznimi polji kategorij in količinami. Tu imamo podatke in moramo najti količino piškotkov, poslanih v Boston, kjer je količina večja od 40.
Tabela podatkov in tabela meril sta prikazani na zgornji sliki. Za razumevanje smo uporabili imenovana območja za uporabljene matrike. Spodaj so navedena imenovana območja.
Tukaj:
Mesto, določeno za matriko A2: A17.
Kategorija, določena za matriko B2: A17.
Količina, določena za matriko C2: C17.
Zdaj ste pripravljeni doseči želeni rezultat z uporabo spodnje formule.
Uporabite formulo:
| { = SUM (IF ((Mesto = "Boston") * (Kategorija = "Piškotki") * (Količina> 40), Količina)) } |
Pojasnilo:
- City = "Boston": preveri vrednosti v območju mest, da se ujemajo z "Boston".
- Kategorija = "Piškotki": preveri, ali se vrednosti v razponu kategorij ujemajo s "Piškotki".
- Količina> 40: preveri vrednosti v obsegu količine na ma
- Količina je niz, kjer je potrebna vsota.
- Funkcija IF preveri vsa merila in zvezdica char (*) pomnoži vse rezultate matrike.
= SUM (ČE ({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}))
- Zdaj funkcija IF vrne samo količine, ki ustrezajo 1s, ostalo pa se prezre.
- Funkcija SUM vrne SUM.
Zdaj se količina, ki ustreza 1, le sešteje, da dobimo rezultat.
Kot lahko vidite, se količina 43 vrne, v "Boston" pa so dostavljena tri naročila piškotkov s količinami 38, 36 in 43. Potrebovali smo vsoto količine, kjer je količina nad 40. Torej formula vrne samo 43. Zdaj uporabite druga merila, da dobite SUM Količina za mesto: "Los Angeles" & Kategorija: "Palice" & Količina je manjša od 50.
Uporabite formulo
| { = SUM (IF ((Mesto = "Los Angeles") * (Kategorija = "Palice") * (Količina <50), Količina)) } |
Kot lahko vidite, formula kot rezultat vrne vrednosti 86. To je vsota dveh naročil, ki izpolnjujeta pogoje s količino 44 in 42. Ta članek prikazuje, kako zamenjati ugnezdeno formulo IF z enim IF v formuli matrike. To lahko uporabimo za zmanjšanje kompleksnosti kompleksnih formul. To težavo pa je mogoče enostavno rešiti s funkcijo SUMIFS ali SUMPRODUCT.
Uporaba funkcije SUMPRODUCT:
Funkcija SUMPRODUCT vrne vsoto ustreznih vrednosti v matriki. Tako bomo matrike vrnili 1s a vrednosti stavka True in 0s vrednosti False stavka. Torej bo zadnja vsota ustrezna, če vse trditve držijo.
Uporabite formulo:
| = SUMPRODUCT ( - (Mesto = "Boston"), - (Kategorija = "Piškotki"), - (Količina> 40), Količina) |
-: operacija za pretvorbo vseh TRUE v 1s in False v 0.
Funkcija SUMPRODUCT ponovno preveri SUM količine, ki jo vrne funkcija SUM in IF, pojasnjena zgoraj.
Podobno je za drugi primer enak rezultat.
Kot lahko vidite, lahko funkcija SUMPRODUCT izvede isto nalogo.
Tu so vse opombe glede uporabe formule.
Opombe:
- Matrika sum_v formuli deluje samo s številkami.
- Če formula vrne napako #VALUE, preverite, ali morajo biti v formuli naviti zaviti oklepaji, kot je prikazano v primerih v članku.
- Negacija (-) char spremeni vrednosti, TRUE ali 1s v FALSE ali 0s in FALSEs ali 0s v TRUEs ali 1s.
- Operacije, podobne ( = ), manj kot enako ( <= ), večji kot ( > ) ali ni enako () je mogoče izvesti v uporabljeni formuli, samo s številkami.
Upam, da je ta članek o tem, kako uporabljati funkcijo IF namesto SUMPRODUCT in SUMIFS v Excelu, razložljiv. Več člankov o formulah za seštevanje najdete tukaj. Če so vam bili naši blogi všeč, jih delite s svojimi začetniki na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
SUM, če je datum med : Vrne SUM vrednosti med danimi datumi ali obdobjem v Excelu.
Vsota, če je datum večji od navedenega datuma: Vrne SUM vrednosti po danem datumu ali obdobju v Excelu.
2 načina seštevanja po mesecih v Excelu: Vrne SUM vrednosti v določenem mesecu v Excelu.
Kako sešteti več stolpcev s pogojem: Vrne SUM vrednosti v več stolpcih s pogojem v excelu.
Priljubljeni članki:
50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati tFunkcija VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.