SUMIF-funktio Excelissä ja summa useilla ehdoilla

Excel on uskomattoman toimiva ohjelma. Jopa sisäänrakennettu ominaisuussarja riittää suorittamaan melkein minkä tahansa tehtävän. Ja monille tuttujen standardien lisäksi on myös sellaisia, joista harvat ovat kuulleet. Mutta samaan aikaan ne eivät lakkaa olemasta hyödyllisiä. Heillä on kapeampi erikoistuminen, eikä niille aina ole tarvetta. Mutta jos tiedät niistä, niin kriittisellä hetkellä ne voivat olla erittäin hyödyllisiä.

Tänään puhumme yhdestä sellaisesta funktiosta − SUMMESLIMN.

Jos käyttäjä kohtaa tehtävän summata useita arvoja keskittyen tiettyihin kriteereihin, on tarpeen käyttää toimintoa SUMMESLIMN. Tätä funktiota käyttävä kaava ottaa nämä ehdot argumenteiksi, summaa sitten ne täyttävät arvot ja sitten löydetty arvo syötetään soluun, johon se kirjoitetaan. 

SUMIFS-toiminnon yksityiskohtainen kuvaus

Ennen kuin harkitset toimintoa SUMMESLIMN, sinun on ensin ymmärrettävä, mikä sen yksinkertaisempi versio on - SUMMESLI, koska tarkastelemamme funktio perustuu siihen. Jokainen meistä todennäköisesti tuntee jo kaksi usein käytettyä toimintoa: SUMMA (suorittaa arvojen summauksen) ja JOS (testaa arvon tiettyä ehtoa vasten).

Jos yhdistät ne, saat toisen toiminnon - SUMMESLI, joka tarkistaa tiedot käyttäjän määrittämien kriteerien perusteella ja laskee vain ne luvut, jotka täyttävät kriteerit. Jos puhumme Excelin englanninkielisestä versiosta, tätä toimintoa kutsutaan nimellä SUMIF. Yksinkertaisesti sanottuna -kielinen nimi on suora käännös englanninkielisestä. Tätä toimintoa voidaan käyttää useisiin eri tarkoituksiin. Erityisesti sitä voidaan käyttää vaihtoehtona VPReli kirjoita ylös

Tärkein ero toiminnon välillä SUMMESLIMN  tavallisesta toiminnosta SUMMESLI on, että käytetään useita kriteerejä. Sen syntaksi on ensi silmäyksellä varsin monimutkainen, mutta tarkemmin tarkasteltuna käy ilmi, että tämän funktion logiikka on hyvin yksinkertainen. Ensin sinun on valittava alue, jolla tiedot tarkistetaan, ja asetettava sitten ehdot, joiden mukaisesti analyysi suoritetaan. Ja tällainen toimenpide voidaan suorittaa melko useissa olosuhteissa.

Itse syntaksi on:

SUMIFS(summa_alue, ehtoväli1, ehto1, [ehto_alue2, ehto2], …)

Sopiviin paikkoihin on tarpeen sijoittaa tiettyyn tapaukseen sopivia soluryhmiä. 

Katsotaanpa argumentteja tarkemmin:

1. Sum_range. Tämä argumentti sekä ehtojen 1 ja ehdon 1 alueet ovat pakollisia. Se on joukko soluja, jotka on laskettava yhteen.
2. Condition_range1. Tämä on alue, jolla kunto tarkistetaan. Se yhdistetään seuraavan argumentin kanssa – Condition1. Kriteeriä vastaavien arvojen summaus suoritetaan edellisessä argumentissa määritettyjen solujen sisällä.
3. Kunto1. Tämä argumentti määrittelee kriteerit, jotka on tarkistettava. Se voidaan asettaa esimerkiksi näin: “> 32”.
4. Ehtoalue 2, ehto 2… Tässä asetetaan seuraavat ehdot samalla tavalla. Jos useampi kuin muutama ehto on määritettävä, ehtoalueen 3 ja ehto 3 argumentit lisätään. Syntaksi on sama seuraaville argumenteille.

Toiminto mahdollistaa jopa 127 ehto- ja alueparin maksimikäsittelyn. 

Voit käyttää sitä usealla alueella kerralla (annamme vain muutaman, lista on itse asiassa vielä pidempi):

1. Kirjanpito. Esimerkiksi toimintoa on hyvä käyttää SUMMESLIMN luoda yhteenvetoraportteja vuosineljänneksittäin esimerkiksi tietyn summan ylittävistä kuluista. Tai luo raportti yhdestä tietyn hintaluokan tuotteesta.
2. Myynnin organisointi. Myös tässä toiminto voi olla erittäin hyödyllinen. Edessämme on esimerkiksi tehtävä summaa vain tietylle asiakkaalle tiettynä ajankohtana myytyjen tuotteiden kustannukset. Ja tällaisessa tilanteessa toiminto SUMMESLIMN voi olla erittäin hyödyllinen.
3. koulutus. Annamme tänään lisää käytännön esimerkkejä tältä alueelta. Sen avulla voit erityisesti saada yhteenvedon oppilaiden arvosanoista. Voit valita yhden oppiaineen tai yksittäisiä arvosanoja. Henkilö voi heti asettaa useita kriteerejä, joiden mukaan arviointi valitaan, mikä on erittäin kätevää ja voi säästää paljon aikaa.

Kuten näet, tämän toiminnon sovellusvalikoima on erittäin laaja. Mutta tämä ei ole sen ainoa ansio. Katsotaanpa vielä muutamia tämän ominaisuuden etuja:

1. Mahdollisuus asettaa useita kriteerejä. Miksi tämä on etu? Voit käyttää normaalia toimintoa SUMMESLI! Ja kaikki koska se on kätevää. Jokaiselle kriteerille ei tarvitse tehdä erillisiä laskelmia. Kaikki toiminnot voidaan ohjelmoida etukäteen, jopa ennen. miten tietotaulukko muodostetaan. Tämä on loistava ajansäästö.
2. Automaatio. Nykyaika on automaation aikakautta. Vain henkilö, joka osaa automatisoida työnsä oikein, voi ansaita paljon. Siksi kyky hallita Excel ja toiminto SUMMESLIMN Erityisesti se on niin tärkeä jokaiselle henkilölle, joka haluaa rakentaa uraa. Kun tiedät yhden toiminnon, voit suorittaa useita toimintoja kerralla yhtenä. Ja tässä siirrymme tämän ominaisuuden seuraavaan etuun.
3. Säästää aikaa. Johtuen siitä, että yksi toiminto suorittaa useita tehtäviä kerralla.
4. Yksinkertaisuus. Huolimatta siitä, että syntaksi on ensisilmäyksellä melko raskas sen tilavuuden vuoksi, itse asiassa tämän toiminnon logiikka on hyvin yksinkertainen. Ensin valitaan tietoalue, sitten arvoalue, joka tarkistetaan tietyn ehdon noudattamisen suhteen. Ja tietysti itse ehto on myös määritettävä. Ja niin monta kertaa. Itse asiassa tämä funktio perustuu vain yhteen loogiseen konstruktiin, mikä tekee siitä yksinkertaisempaa kuin hyvin tunnettu VPR huolimatta siitä, että sitä voidaan käyttää samoihin tarkoituksiin, kun otetaan huomioon myös useampi kriteeri. 

SUMIFS-toiminnon käytön ominaisuudet

Tämän toiminnon käytössä on useita ominaisuuksia, joihin sinun on kiinnitettävä huomiota. Ensinnäkin tämä toiminto jättää huomioimatta alueet, joissa on tekstimerkkijonoja tai nollakohtia, koska näitä tietotyyppejä ei voida yhdistää aritmeettiseen malliin, vaan ne voidaan vain ketjuttaa merkkijonoiksi. Tämä toiminto ei voi tehdä tätä. Sinun on myös kiinnitettävä huomiota seuraaviin ehtoihin:

1. Voit käyttää tämän tyyppisiä arvoja solujen valintaehtoina lisätäksesi edelleen niiden sisältämiä arvoja: numeerisia arvoja, loogisia lausekkeita, soluviittauksia ja niin edelleen. 
2. Jos tekstiä, loogisia lausekkeita tai matemaattisia merkkejä tarkistetaan, tällaiset kriteerit määritetään lainausmerkeillä.
3. Ei saa käyttää yli 255 merkin pituisia termejä.
4. On mahdollista käyttää likimääräisiä kriteerejä arvojen valinnassa jokerimerkkejä käyttämällä. Kysymysmerkkiä käytetään yhden merkin korvaamiseen, ja kertomerkkiä (tähti) tarvitaan useiden merkkien korvaamiseen. 
5. Totuusarvot, jotka ovat summausalueella, muunnetaan automaattisesti numeerisiksi arvoiksi tyypin mukaan. Siten arvo "TRUE" muuttuu yhdeksi ja "FALSE" - nolla. 
6. Jos #ARVO! virhe ilmestyy soluun, se tarkoittaa, että solujen määrä ehto- ja summausalueella on erilainen. Sinun on varmistettava, että näiden argumenttien koot ovat samat. 

Esimerkkejä SUMIFS-toiminnon käytöstä

Toiminto SUMMESLIMN ei niin monimutkaista kuin miltä ensi silmäyksellä näyttää, se osoittautuu. Mutta selvyyden vuoksi katsotaanpa joitain käytännön esimerkkejä toiminnon käyttämisestä SUMMESLIMN. Tämä helpottaa huomattavasti aiheeseen syventämistä.

Kunnon summauksen dynaaminen alue

Aloitetaan siis ensimmäisestä esimerkistä. Oletetaan, että meillä on taulukko, joka sisältää tietoa siitä, kuinka opiskelijat selviytyvät tietyn aineen opetussuunnitelmasta. Arvosanoja on joukko, suoritusta arvioidaan 10 pisteen asteikolla. Tehtävänä on löytää kokeen arvosana niiltä opiskelijoilta, joiden sukunimi alkaa A-kirjaimella ja joiden vähimmäispistemäärä on 5.

Pöytä näyttää tältä.

Jotta voimme laskea kokonaispistemäärän yllä kuvattujen kriteerien perusteella, meidän on sovellettava seuraavaa kaavaa.

Kuvataanpa argumentteja tarkemmin:

1. C3:C14 on summausvalikoimamme. Meidän tapauksessamme se osuu yhteen ehtoalueen kanssa. Siitä valitaan pisteet, joita käytetään summan laskemiseen, mutta vain ne, jotka täyttävät kriteerimme.
2. ”>5” on ensimmäinen ehtomme.
3. B3:B14 on toinen summausalue, joka käsitellään vastaamaan toista kriteeriä. Näemme, että summausalueen kanssa ei ole sattumaa. Tästä päätämme, että summausalue ja ehdon alue voivat olla identtisiä tai eivät. 
4. “A*” on toinen alue, joka määrittää arvosanan valinnan vain niille opiskelijoille, joiden sukunimi alkaa A:lla. Meidän tapauksessamme tähti tarkoittaa mitä tahansa merkkejä. 

Laskelmien jälkeen saamme seuraavan taulukon.

Kuten näet, kaava summasi arvot dynaamisen alueen ja käyttäjän määrittämien olosuhteiden perusteella.

Valikoiva summaus ehtojen mukaan Excelissä

Oletetaan nyt, että haluamme saada tietoa siitä, mitä tavaroita on lähetetty mihinkin maihin viimeisen vuosineljänneksen aikana. Etsi sen jälkeen lähetysten kokonaistulot heinä- ja elokuussa.

Itse pöytä näyttää tältä. 

Lopputuloksen määrittämiseksi tarvitsemme tällaisen kaavan.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Tällä kaavalla suoritettujen laskelmien tuloksena saamme seuraavan tuloksen.

Huomio! Tämä kaava näyttää melko suurelta, vaikka käytimme vain kahta kriteeriä. Jos tietoalue on sama, voit lyhentää kaavan pituutta merkittävästi alla olevan kuvan mukaisesti.

SUMIFS-toiminto, joka summaa useiden ehtojen arvot

Annetaan nyt toinen esimerkki havainnollistamaan. Tässä tapauksessa taulukko pysyy samana kuin edellisessä tapauksessa. 

Käytämme seuraavaa kaavaa (mutta kirjoitamme sen taulukkokaavana, eli syötämme sen näppäinyhdistelmällä CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Toiminnon jälkeen SUMMESLIMN summaa arvojen joukon kaavassa määritettyjen kriteerien perusteella (eli Kiinan ja Georgian maat), tuloksena oleva taulukko summataan tavallisella funktiolla SUMMA, joka kirjoitetaan taulukkokaavana.

Jos ehdot välitettiin taulukkovakiona useammalle kuin yhdelle parille, kaava antaa virheellisen tuloksen.

Katsotaanpa nyt kokonaissummat sisältävää taulukkoa.

Kuten näet, olemme onnistuneet. Sinäkin onnistut varmasti. Suuri menestys tällä alalla. Tämä on hyvin yksinkertainen toiminto, jonka henkilö, joka on juuri astunut Excelin oppimispolulle, ymmärtää. Ja me tiedämme jo sen toiminnon SUMMESLIMN antaa sinun olla tehokas kaikilla toimialoilla kirjanpidosta jopa koulutukseen. Vaikka rakentaisit uraa jollain muulla alalla, jota ei ole kuvattu yllä, tämä ominaisuus auttaa sinua silti ansaitsemaan rahaa. Siksi hän on arvokas.

Mikä tärkeintä, sen avulla voit säästää aikaa, joka on valitettavasti rajallinen resurssi. Vaikuttaa siltä, ​​että kahden toiminnon käyttämiseen on muutama sekunti, mutta kun sinun on suoritettava valtava määrä toistuvia toimintoja, nämä sekuntit laskevat tunteja, jotka voitaisiin käyttää johonkin muuhun. Joten suosittelemme, että harjoittelet tämän ominaisuuden käyttöä. Lisäksi se on uskomattoman yksinkertainen.