Yksi ongelma, joka tulee esiin paljon Excelissä, on laskenta tai summaus useiden TAI-ehtojen perusteella. Esimerkiksi, sinun on ehkä analysoitava tietoja ja laskettava tilaukset Seattlessa tai Denverissä kohteille, jotka ovat punaisia, sinisiä tai vihreitä? Tämä voi olla yllättävän hankalaa, joten luonnollisesti se on hyvä haaste!
Haaste
Alla olevat tiedot edustavat tilauksia, yksi tilaus riviä kohden. On kolme erillistä haastetta.
Mitkä kaavoissa F9, G9 ja H9 laskevat tilaukset oikein seuraavin ehdoin:
- F9 - T-paita tai huppari
- G9 - (paita tai huppari) ja (punainen, sininen tai vihreä)
- H9 - (paita tai huppari) ja (punainen, sininen tai vihreä) ja (Denver tai Seattle)
Vihreää varjostusta käytetään ehdollisella muotoilulla, ja se osoittaa kunkin sarakkeen TAI-ehtoryhmän vastaavat arvot.
Mukavuutesi vuoksi käytettävissä ovat seuraavat nimetyt alueet:
kohde = B3: B16
väri = C3: C16
kaupunki = D3: D16
Taulukko on liitteenä. Jätä vastauksesi alla kommentteina!
Vastaa (laajenna napsauttamalla)Ratkaisuni käyttää SUMPRODUCTia ISNUMBERin ja MATCHin kanssa näin:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Mikä laskee tilaukset missä…
- Tuote on (T-paita tai huppari) ja
- Väri on (punainen, sininen tai vihreä) ja
- Kaupunki on (Denver tai Seattle)
Useat ihmiset ehdottivat myös samaa lähestymistapaa. Pidän tästä rakenteesta, koska se skaalautuu helposti käsittelemään useampia ehtoja ja toimii myös soluviittausten kanssa (kovakoodattujen arvojen sijaan). Soluviittausten kanssa kaavassa H9 on:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Tämän kaavan avain on ISNUMBER + MATCH -rakenne. MATCH on asetettu "taaksepäin" - hakuarvot tulevat tiedoista, ja taulukon kriteerejä käytetään. Tuloksena on yksi sarakeryhmä joka kerta, kun MATCHia käytetään. Tämä taulukko sisältää joko # N / A-virhettä (ei vastaavuutta) tai numeroita (osuma), joten ISNUMBER-arvoa käytetään muuntamaan totuusarvoiksi TOSI ja EPÄTOSI. Matriisien kertominen yhdessä pakottaa TOSI EPÄTOSI -arvot 1: ksi ja 0: ksi, ja SUMPRODUCTin sisällä oleva viimeinen taulukko sisältää 1: n, jossa rivit täyttävät ehdot. SUMPRODUCT summaa sitten taulukon ja palauttaa tuloksen.
