Yksi keskustelupalstan yleisimmistä kysymyksistä on, kuinka SumIf-toimintoa käytetään kahdella eri ehdolla. Valitettavasti vastaus on, että SumIf ei pysty käsittelemään kahta erilaista ehtoa.
Kahden ehdon suorittamiseksi sinun on käytettävä melko monimutkaista matriisikaavaa. Ehdollisen summan ohjatun lisäosan avulla voit syöttää nämä monimutkaiset kaavat helposti.
Tässä on Excel-laskentataulukko, jossa on sarakkeita tuotteille, myyntiedustajille ja myynnille. Tiedot ovat soluissa A2: C29.
Jos haluat laskea yhteen myynnin, yksinkertainen SUM () -toiminto toimii. =SUM(C2:C29).
Monet huippulaiset löytävät SumIf-toiminnon. Tämän toiminnon avulla on melko helppo selvittää tuotteen ABC kokonaismyynti.=SUMIF(A2:A29,E2,C2:C29)
On myös helppo selvittää myyntiedustajan Joen myynti kokonaismyynnistä =SUMIF(B2:B29,E2,C2:C29).
Oletat sitten, että on mahdollista selvittää Joen tekemän ABC-tuotteen kokonaismyynti. SumIf-funktiolla ei kuitenkaan ole mitään keinoa tehdä tätä. On käynyt ilmi, että sinun on käytettävä melko monimutkaista taulukkoa tai täsmähakukoneen kaavaa.
Tunnustetaan tosiasia - Sum-kaava on Excel 101. SumIf-kaava ei ole kaukana monimutkaisuudesta. CSE-kaava Joen tekemän ABC-myynnin laskemiseksi on kuitenkin riittävä, jotta edes pääni pyörisi.
Hyvä uutinen - Microsoft tarjoaa ohjatun ehdollisen summan, jonka avulla jopa aloittelija voi kirjoittaa monimutkaisia ehdollisia kaavoja, jotka perustuvat 1, 2 tai useampaan ehtoon. Ohjattu ehdollisen summan velho on apuohjelma. Voit lisätä tämän toiminnon Exceliin siirtymällä Työkalut-valikkoon ja valitsemalla Apuohjelmat. Valitse Apuohjelmat-valintaikkunassa Ohjatun ehdollisen summan valintaruutu ja valitse OK. On mahdollista, että tarvitset asennus-CD: tä tässä vaiheessa, koska Microsoft ei sisälly ohjattua toimintoa oletusasennukseen.
Kun apuohjelma on otettu käyttöön onnistuneesti, Työkalut-valikon alaosassa on Conditonal Sum… -valinta.
Valitse yksi solu tietojoukostasi ja valitse Työkalut - Ehdollinen summa. Olettaen, että tietosi on muotoiltu hienosti yhdellä otsikkorivillä, Excel arvaa oikein tietojesi alueen. Valitse Seuraava.
Valitse vaiheessa 2 summautuva sarake. Tällöin ohjattu toiminto arvasi jo, että haluat summata ensimmäisen (ja ainoan) numeerisen sarakkeen - Myynti. Valintaikkunan keskellä on kolme pudotusvalikkoa. Nämä sattuu pitämään paikkansa ensimmäisen ehdon kohdalla - Tuote on yhtä suuri kuin ABC, joten valitse Lisää ehto -painike.
Sitten voit lisätä toisen ehdosi. Tässä tapauksessa haluat määrittää, että myyntiedustaja on Joe. Valitse ensimmäisen avattavan valikon nuoli. Excel tarjoaa aakkosellisen luettelon käytettävissä olevista sarakkeiden nimistä. Valitse myyntiedustaja.
Keskipudotusvalikko on oikea, mutta täydellisyyden vuoksi voit nähdä, että olisit voinut valita yhtä, pienemmän, suuremman, pienemmän tai yhtä suuren, suuremman tai yhtä suuren tai ei yhtä.
Valitse kolmannesta avattavasta valikosta Joe.
Valitse Lisää ehto -painike.
Olet nyt valmis siirtymään vaiheeseen 3. Paina Seuraava-painiketta.
Vaiheessa 3 sinulla on kaksi vaihtoehtoa. Ensimmäisessä valinnassa ohjattu toiminto syöttää yhden kaavan, jonka arvot "ABC" ja "Joe" on kovakoodattu kaavaan. Se antaa sinulle vastauksen, mutta ei ole mahdollisuutta muuttaa kaavaa helposti. Toisella valinnalla Excel asettaa uuden solun, jonka arvo on "ABC", ja uuden solun, jonka arvo on "Joe". Kolmas solu sisältää kaavan, joka tekee ehdollisen summan näiden kahden arvon perusteella. Tällä vaihtoehdolla voit kirjoittaa soluihin uusia arvoja, jotta näet Adamin myymät XYZ: n kokonaismäärät.
Ohjattu toiminto kysyy sitten mistä haluat ABC-arvon. Valitse solu ja valitse Seuraava. Toista, kun ohjattu toiminto pyytää sinua valitsemaan solun Joelle ja kaavan.
Kun valitset Viimeinen viimeisessä vaiheessa, Excel luo hieman toisen (mutta kelvollisen) version täsmähakukoneen kaavasta.
Tämä kaava laskee, että Joe myi 33 338 dollaria ABC: stä.
Jos vaihdat tuotteen syöttösolun ABC: stä DEF: ään, kaava lasketaan uudelleen osoittamaan, että Joe myi 24 478 dollaria DEF: ää.
Ohjattu ehdollisen summan veloitus asettaa monimutkaiset kaavat hyvin kaikkien Excel-omistajien ulottuville.
Lisäinformaatio:Jos haluat rakentaa taulukon, joka näyttää kunkin tuotteen myynnin kustakin edustajasta, on olemassa erityinen "hoito ja ruokinta", joka sinun on tiedettävä näistä kaavoista. Kirjoita kukin myyntiedustaja alueen yläosaan. Kirjoita kukin tuote alueen vasempaan sarakkeeseen. Muokkaa ohjatun toiminnon tarjoamaa kaavaa. Alla olevassa kuvassa kaava osoittaa tuotetta soluun E6. Tämän viitteen on todellakin oltava $ E6. Jos jätät viitteen E6: ksi ja kopioit kaavan sarakkeeseen G, kaava näyttää F6: n E6: n sijasta ja tämä olisi väärin. Dollarimerkin lisääminen ennen E: tä E6: ssa varmistaa, että kaava tarkastelee aina sarakkeessa E olevaa tuotetta. Kaava osoittaa myös myyntiedustajaa solussa F5. Tämän viitteen on todellakin oltava 5 dollaria. Jos jätit viitteen F5: ksi ja kopioit alas riville 7,F5-viite muuttuu F6: ksi, mikä ei ole oikein. Dollarimerkin lisääminen ennen rivinumeroa lukitsee rivinumeron ja viite osoittaa aina riville 5.
Kirjoita muokkaustilassa (valitse solu ja muokkaa painamalla F2) kirjoittamalla $ ennen E. Kirjoita dollarin merkki ennen F5: n 5: tä. Älä paina Enter-näppäintä vielä!
Tämä kaava on erityinen kaavan tyyppi. Jos painat Enter-näppäintä, saat arvon 0, mikä ei ole oikein.
Enter-kirjoittamisen sijaan pidä Ctrl- ja Shift-näppäimiä painettuna, kun painat Enter. Tätä taikayhdistelmää C trl + S hift + E nter kutsun näitä CSE-kaavoiksi.
Viimeinen huomio on ennen kaavan kopiointia muuhun taulukkoon. Haluat ehkä kopioida F6 ja liittää tiedostoon F6: G8. Jos yrität tätä, Excel antaa sinulle hämmentävän "Et voi muuttaa osan matriisista" -viestin. Excel valitti, että et voi täsmähakukoneen kaavaa liittää alueelle, joka sisältää alkuperäisen täsmähakukoneen kaavan.
Tämän kiertäminen on helppoa. Kopio F6. Liitä kohtaan F7: F8.
Kopio F6: F8. Liitä kohtaan G6: G8. Sinulla on taulukko täsmähakukoneen kaavoista, jotka esittävät yhteensä kaksi ehtoa.
