Tässä artikkelissa opimme etsimään tarkkoja vastaavuuksia Excelin SUMPRODUCT -funktion avulla.
Skenaario:
Yksinkertaisesti sanottuna, kun työskentelemme tietotaulukoiden kanssa, joudumme joskus etsimään arvot pienillä ja pienillä kirjaimilla Excelissä. Hakutoiminnot, kuten VLOOKUP- tai MATCH -toiminnot, eivät löydä täsmällistä vastaavuutta, koska nämä eivät ole kirjainkoolla merkittäviä toimintoja. Oletetaan, että käsitellään tietoja, joissa kaksi nimeä erotetaan kirjainkoon mukaan, eli Jerry ja JERRY ovat kaksi eri etunimeä. Voit suorittaa tehtäviä, kuten toimintoja, useilla alueilla käyttämällä alla selitettyä kaavaa.
Kuinka ratkaista ongelma?
Tätä ongelmaa varten meidän on käytettävä SUMPRODUCT- ja EXACT -toimintoa. Nyt teemme funktiosta kaavan. Tässä meille annetaan taulukko ja meidän on löydettävä Excelin taulukon tarkkaa vastaavuutta vastaavat arvot. SUMPRODUCT -funktio palauttaa vastaavien TOSI -arvojen SUMMA (1: nä) ja jättää huomiotta FALSE -arvoja (0) vastaavat arvot palautetussa taulukossa
Yleinen kaava:
| = SUMPRODUCT ( - (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: haettava arvo.
lookup_array: hakuarvo matriisille.
return_array: array, palautettu arvo, joka vastaa hakutaulukkoa.
-: Negatiivinen (-) -merkki muuttaa arvot, TRUEs tai 1s arvoksi FALSEs tai 0s ja FALSEs tai 0s arvoon TRUEs tai 1s.
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 siitä, kuinka paljon tuotteita ja hintoja saatiin päivämäärinä. Jos jokin tuote ostetaan kahdesti, sillä on 2 nimeä. Täältä meidän on löydettävä kaikkien olennaisten tavaroiden lukumäärä. Joten sitä varten olemme määrittäneet 2 luetteloa vastaanotetuksi luetteloksi ja olennaiset asiat, jotka vaaditaan kaavan sarakkeessa. Nyt käytämme alla olevaa kaavaa saadaksesi "maidon" määrän taulukosta.
Käytä kaavaa:
| = SUMPRODUCT ( - (EXACT (F5, B3: B11)), (C3: C11)) |
F6: etsi arvo F6 -solusta
B3: B11: hakutaulukko on Kohteet
C3: C11: paluutaulukko on määrä
-: Negatiivinen (-) -merkki muuttaa arvot, TRUEs tai 1s arvoksi FALSEs tai 0s ja FALSEs tai 0s arvoon TRUEs tai 1s.
Tässä alue annetaan soluviitteenä. Hae määrä painamalla Enter -näppäintä.
Kuten näette, 58 on määrä, joka vastaa arvoa "maito" B5 -solussa, ei "maitoa" B9 -solussa. Sinun on etsittävä arvoa "Maito", jos tarvitset määrän "54" C9 -solussa.
Voit tarkistaa arvon "maito" hinnan vasta alla olevan kaavan avulla.
Käytä kaavaa:
| = SUMPRODUCT ( - (EXACT (F5, B3: B11)), (D3: D11)) |
F6: etsi arvo F6 -solusta
B3: B11: hakutaulukko on Kohteet
D3: D11: palautusmatriisi on Hinta
Tässä 58 on hinta, joka vastaa arvoa "maito" B5 -solussa, ei "maitoa" B9 -solussa. Sinun on etsittävä arvoa "Maito", jos tarvitset hinnan "15.58" D9 -solusta.
Ainutlaatuiset arvot hakutaulukossa
Voit myös löytää ainutlaatuiset arvot kaavan avulla. Tässä esimerkkinä käytetty hakuarvo "MUNAT".
Yllä olevassa kuvassa saimme arvot säätämällä samaa kaavaa. Mutta ongelma ilmenee, jos sillä on ainutlaatuinen arvo etkä etsi oikeaa hakuarvoa. Kuten täällä käyttämällä taulukon hakua kaavan arvolla "Leipä".
Kaava palauttaa määrän 0 ja hinnan 0, mutta tämä ei tarkoita, että leivän määrä ja hinta ovat 0. Se vain tarkoittaa, että kaava palauttaa arvon 0, kun etsimääsi arvoa ei löydy. Käytä siis tätä kaavaa vain tarkan haun etsimiseen.
Voit myös suorittaa hakutarkkoja hakuja käyttämällä INDEX- ja MATCH -toimintoa Excelissä. Lue lisää kirjainkokoherkän haun tekemisestä INDEX & MATCH -toiminnolla Excelissä. Voit myös etsiä osittaisia osumia käyttämällä Excelin yleismerkkejä.
Seuraavassa on muutamia huomautuksia alla.
Huomautuksia:
- Kaava toimii vain vain tarkan haun etsimiseen.
- SUMPRODUCT-toiminto pitää muita kuin numeerisia arvoja 0: na.
- SUMPRODUCT -funktio pitää logiikka -arvoa TRUE 1: nä ja False 0: na.
- Argumenttijoukon on oltava samankokoinen, muuten funktio palauttaa virheen.
- SUMPRODUCT -funktio palauttaa palautetun taulukon TRUE -arvoja vastaavan arvon sen jälkeen, kun yksittäiset tuotteet on otettu vastaavassa taulukossa.
- Operaattorit pitävät yhtä paljon 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 tarkan haun etsimisestä SUMPRODUCT -toiminnolla Excelissä on selittävä. Täältä löydät lisää artikkeleita laskentakaavoista. 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.
Käytä INDEX- ja MATCH -toimintoja arvon etsimiseen : INDEX-MATCH-kaavaa käytetään dynaamisen ja tarkan arvon etsimiseen annetusta taulukosta. Tämä on vaihtoehto VLOOKUP -toiminnolle ja se poistaa VLOOKUP -toiminnon puutteet.
Summa OFFSET -ryhmien mukaan rivit ja sarakkeet : OFFSET -toimintoa voidaan käyttää soluryhmän summaamiseen dynaamisesti. Nämä ryhmät voivat olla missä tahansa taulukossa.
Kuinka hakea jokaisen alueen n. Arvo Excelissä: Excelin OFFSET -funktion avulla voimme hakea arvot vuorottelevista riveistä tai sarakkeista. Tämä kaava hyödyntää ROW -funktiota vuorotellen rivien läpi ja COLUMN -funktiota vuorotellen sarakkeissa.
OFFSET -toiminnon käyttäminen Excelissä : OFFSET -toiminto on tehokas Excel -toiminto, jota monet käyttäjät aliarvioivat. Mutta asiantuntijat tietävät OFFSET -toiminnon voiman ja kuinka voimme käyttää tätä toimintoa joidenkin maagisten tehtävien suorittamiseen Excel -kaavassa. Tässä on OFFSET -toiminnon perusteet.
Kuinka käyttää jokerimerkkejä Excelissä : Laske ilmauksia vastaavat solut käyttämällä Excelin yleismerkkejä
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 yleisimmin käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään etsimään arvoa eri alueilta ja arkeilta Excelissä.
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 Excelissä. COUNTIF -toiminto 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 tietyin ehdoin.