Tässä artikkelissa opimme käyttämään IF -funktiota SUMPRODUCT- ja SUMIFS -funktioiden sijaan Excelissä.
Skenaario:
Yksinkertaisesti sanottuna, kun työskentelemme pitkän hajallaan olevan tietojoukon kanssa, meidän on joskus löydettävä numeroiden summa, jossa on joitain kriteereitä. Esimerkiksi tietyn osaston palkkasumman löytäminen tai useita kriteerejä päivämäärälle, nimille, osastolle tai jopa voi numeroida tietoja, kuten palkat arvon alapuolella tai määrä arvon yläpuolella. Tätä varten käytät yleensä SUMPRODUCT- tai SUMIFS -toimintoa. Mutta et usko, suoritat saman toiminnon Excelin perustoiminnon IF -toiminnolla.
Kuinka ratkaista ongelma?
Sinun täytyy ajatella, miten tämä on mahdollista, jos haluat suorittaa loogisia toimintoja taulukkotaulukkojen yli IF -funktion avulla. IF -toiminto Excelissä on erittäin hyödyllinen, se vie sinut läpi vaikeita tehtäviä Excelissä tai muilla koodauskielillä. IF -funktio testaa taulukon edellytykset, jotka vastaavat vaadittuja arvoja, ja palauttaa tuloksen True -ehtoja vastaavana taulukkona 1 ja False 0: na.
Tätä ongelmaa varten käytämme seuraavia toimintoja:
- SUM -toiminto
- IF -toiminto
Edellytämme edellä mainittuja toimintoja ja tietotekniikan perustaitoja. matriisien loogisia ehtoja voidaan soveltaa loogisilla operaattoreilla. Nämä logiikkaoperaattorit työskentelevät sekä tekstin että numeroiden parissa. Alla on yleinen kaava. { } curly braces on taikatyökalu, jolla voidaan suorittaa taulukkokaavoja IF -funktiolla.
Yleinen kaava:
| { = SUMMA (JOS ((looginen_1) * (looginen_2) *… * (looginen_n), summa_matriisi)) } |
Huomautus: Kiharat olkaimet ( { } ) Käytä Ctrl + Vaihto + Enter kun työskentelet matriisien tai alueiden kanssa Excelissä. Tämä luo oletusarvoisesti kaarevat aaltosulkeet kaavaan. ÄLÄ yritä koodata kiharoita hakasulkeita.
Looginen 1: testaa taulukon 1 ehtoa 1
Looginen 2: testaa ehtoa 2 taulukolla 2 ja niin edelleen
sum_array: array, operaatio summa suoritetaan
Esimerkki:
Kaikkien näiden ymmärtäminen voi olla hämmentävää. Joten testataan tätä kaavaa suorittamalla se alla olevassa esimerkissä. Tässä meillä on tietoja eri kaupunkeihin toimitetuista tuotteista sekä vastaavat kategoriakentät ja määrät. Tässä meillä on tiedot ja meidän on löydettävä Bostoniin lähetettyjen evästeiden määrä, jossa määrä on yli 40.
Tietotaulukko ja kriteeritaulukko näkyvät yllä olevassa kuvassa. Ymmärtämisen vuoksi käytimme käytettyjen matriisien nimettyjä alueita. Nimetyt alueet on lueteltu alla.
Täällä:
Taulukolle A2 määritetty kaupunki: A17.
Ryhmälle B2 määritelty luokka: A17.
Taulukolle C2 määritetty määrä: C17.
Nyt olet valmis saamaan halutun tuloksen alla olevan kaavan avulla.
Käytä kaavaa:
| { = SUMMA (JOS ((Kaupunki = "Boston") * (Luokka = "Evästeet") * (Määrä> 40), Määrä)) } |
Selitys :
- City = "Boston": tarkistaa kaupunkialueen arvot vastaamaan "Bostonia".
- Luokka = "Evästeet": tarkistaa luokka -alueen arvot vastaamaan "Evästeitä".
- Määrä> 40: tarkistaa Määrä -alueen arvot maksi
- Määrä on taulukko, jossa vaaditaan summa.
- IF -toiminto tarkistaa kaikki ehdot ja tähti -merkki (*) moninkertaistaa kaikki taulukon tulokset.
= SUMMA (JOS ({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}))
- Nyt IF -funktio palauttaa vain 1: tä vastaavat määrät ja loput jätetään huomiotta.
- SUM -funktio palauttaa summan.
Nyt 1: tä vastaava määrä laskee vain tuloksen saamiseksi.
Kuten näette, määrä 43 palautetaan, mutta "Bostonille" toimitetaan kolme evästetilausta, joiden määrä on 38, 36 ja 43. Tarvitsimme summan, jossa määrä on yli 40. Kaava palauttaa siis vain 43. Käytä nyt muita kriteerejä saadaksesi kaupungin SUM -määrän: "Los Angeles" ja luokka: "Baarit" ja määrä alle 50.
Käytä kaavaa
| { = SUMMA (JOS ((Kaupunki = "Los Angeles") * (Luokka = "Baarit") * (Määrä <50), Määrä)) } |
Kuten näette, kaava palauttaa arvot 86 tuloksena. Mikä on kahden tilauksen summa, jotka täyttävät ehdot, joilla on määrä 44 ja 42. Tämä artikkeli havainnollistaa, kuinka sisäkkäinen IF -kaava korvataan yhdellä IF: llä taulukkokaavassa. Tätä voidaan käyttää monimutkaisuuden vähentämiseen monimutkaisissa kaavoissa. Tämä ongelma voidaan kuitenkin helposti ratkaista SUMIFS- tai SUMPRODUCT -toiminnolla.
SUMPRODUCT -toiminnon käyttö:
SUMPRODUCT -funktio palauttaa taulukon vastaavien arvojen summan. Joten saamme taulukot palauttamaan 1s a True -lausearvot ja 0s False -lausearvoihin. Joten viimeinen summa vastaa, missä kaikki väitteet ovat totta.
Käytä kaavaa:
| = SUMPRODUCT ( - (City = "Boston"), - (Category = "Cookies"), - (Määrä> 40), Määrä) |
-: toiminto, jolla kaikki TOSI muutetaan 1: ksi ja epätosi 0: ksi.
SUMPRODUCT -toiminto tarkistaa uudelleen SUM- ja IF -funktion palauttaman määrän SUMMA.
Vastaavasti toisessa esimerkissä tulos on sama.
Kuten näet, SUMPRODUCT -toiminto voi suorittaa saman tehtävän.
Tässä on kaikki huomautukset kaavan käytöstä.
Huomautuksia:
- Kaavan summa_matriisi toimii vain numeroiden kanssa.
- Jos kaava palauttaa arvon #VALUE, tarkista, onko kaarevien housunkannattimien oltava kaavassa artikkelin esimerkkien mukaisesti.
- Negatiivinen (-) -merkki muuttaa arvot, TRUEs tai 1s arvoksi FALSEs tai 0s ja FALSEs tai 0s arvoon TRUEs tai 1s.
- Toiminnot, kuten yhtä kuin ( = ), alle yhtä suuri kuin ( <= ), suurempi kuin ( > ) tai ei ole yhtä kuin () voidaan suorittaa kaavassa, jossa käytetään vain numeroita.
Toivottavasti tämä artikkeli IF -toiminnon käyttämisestä SUMPRODUCT- ja SUMIFS -funktion sijasta Excelissä on selittävä. Lisää artikkeleita Summing -kaavoista löydät täältä. Jos pidit blogeistamme, jaa se ystävillesi Facebookissa. Voit myös seurata meitä Twitterissä ja Facebookissa. Haluaisimme kuulla sinusta, kerro meille, kuinka voimme parantaa, täydentää tai innovoida työtämme ja parantaa sitä sinulle. Kirjoita meille sähköpostitse
SUMPRODUCT -toiminnon käyttäminen Excelissä: Palauttaa summan Excelin useiden matriisien arvojen kertomisen jälkeen.
SUM, jos päivämäärä on välillä : Palauttaa Excelissä annettujen päivämäärien tai ajanjaksojen välisten arvojen SUMMA.
Summa, jos päivämäärä on suurempi kuin annettu päivämäärä: Palauttaa arvojen SUMMAN annetun päivämäärän tai jakson jälkeen Excelissä.
2 tapaa laskea yhteen kuukausittain Excelissä: Palauttaa arvojen SUMMA tietyn kuukauden aikana Excelissä.
Kuinka laskea useita sarakkeita ehdolla: Palauttaa useiden sarakkeiden arvojen summan, joiden ehto on Excel.
Suosittuja artikkeleita:
50 Excel -pikakuvake tuottavuuden lisäämiseksi : Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
Kuinka käyttää tVLOOKUP -toiminto Excelissä : Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja taulukoilta.
COUNTIF -toiminnon käyttäminen Excelissä : Laske arvot olosuhteilla tämän hämmästyttävän toiminnon avulla. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä : Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.