Tämä artikkeli on omistettu VLOOKUP-toiminnot. Se harkitsee vaiheittaiset ohjeet VLOOKUP-toiminnolle, nimeltään "". Tässä artikkelissa tarkastelemme yksityiskohtaisesti kuvausta, syntaksia ja esimerkkejä VLOOKUP-funktiosta Excelissä. Tarkastelemme myös sen käyttöä ja tärkeimpiä virheitä ja miksi. VLOOKUP-toiminto ei toimi.
VLOOKUP-funktion syntaksi ja kuvaus Excelissä
Joten koska tämän artikkelin toinen otsikko on " VLOOKUP-toiminto Excelissä tutille", aloitetaan ottamalla selvää, mikä VLOOKUP-toiminto on ja mitä se tekee? VLOOKUP-toiminto englanniksi VLOOKUP, etsii määritetyn arvon ja palauttaa vastaavan arvon toisesta sarakkeesta.
Kuinka VLOOKUP-toiminto toimii?? Excelin VLOOKUP-toiminto etsii tietoluetteloistasi yksilöllisen tunnisteen perusteella ja antaa sinulle kyseiseen yksilölliseen tunnisteeseen liittyvän tiedon.
VPR:n kirjain "V" tarkoittaa "pystysuoraa". Sitä käytetään erottamiseen VLOOKUP- ja GLOOKUP-toiminnot, joka etsii arvoa taulukon ylimmältä riviltä ("H" tarkoittaa "horisontaalista").
VLOOKUP-toiminto on käytettävissä kaikissa Excel 2016-, Excel 2013-, Excel 2010-, Excel 2007- ja Excel 2003 -versioissa.
Syntaksi VLOOKUP-toiminnot näyttää tältä:
VHAKU(hakuarvo, taulukko, sarakkeen_numero, [välin_haku])
Kuten näet, VLOOKUP-toiminto siinä on 4 parametria tai argumenttia. Kolme ensimmäistä parametria ovat pakollisia, viimeinen on valinnainen.
- haku_arvo on hakuarvo.
Tämä voi olla joko arvo (numero, päivämäärä tai teksti) tai soluviittaus(viittaus hakuarvon sisältävään soluun) tai jonkin muun Excel-funktion palauttama arvo. Esimerkiksi:
- Hae numeroa: =VHAKU(40; A2:B15; 2) - kaava etsii numeroa 40.
- Tekstihaku: =VHAKU("omenat", A2:B15, 2) - kaava etsii tekstiä "omenat". Huomaa, että sisällytät aina tekstiarvot "lainausmerkkeihin".
- Arvon etsiminen toisesta solusta: =VHAKU(C2, A2:B15, 2) - kaava etsii arvoa solusta C2.
- taulukko on kaksi tai useampi tietosarake.
Muista se VLOOKUP-toiminto etsii aina haluttua arvoa taulukon ensimmäisestä sarakkeesta. Taulukkosi voi sisältää erilaisia arvoja, kuten tekstiä, päivämäärää, numeroita tai loogisia arvoja. Arvot kirjainkoolla ei väliä, mikä tarkoittaa, että isoja ja pieniä kirjaimia pidetään samanlaisina.
Joten kaavamme =VHAKU(40, A2:B15, 2) etsii "40" soluista A2-A15, koska A on taulukon A2:B15 ensimmäinen sarake.
- sarakkeen_numero - sen taulukon sarakkeen numero, josta vastaavan rivin arvo palautetaan.
Määritetyn taulukon vasemmanpuoleisin sarake on 1, toinen sarake on 2, kolmas on 3 jne.
4. interval_view määrittää, etsitkö tarkkaa vastaavuutta (EPÄTOSI) vai likimääräistä vastaavuutta (TOSI vai jätetty pois). Tämä viimeinen parametri on valinnainen, mutta erittäin tärkeä.
VLOOKUP-funktio Excel-esimerkeissä
Katsotaanpa nyt joitain käyttötapauksia VLOOKUP-toiminnot todellista dataa varten.
VLOOKUP-toiminto eri arkeilla
Käytännössä VLOOKUP-kaavoja käytetään harvoin tietojen etsimiseen yhdeltä arkilta. Useimmiten sinun on etsittävä ja haettava asiaankuuluvat tiedot toiselta arkilta.
Vastaanottaja käytä VLOOKUP-toimintoa toisesta Excel-arkista, sinun on syötettävä laskentataulukon nimi ja huutomerkki taulukon argumenttiin ennen solualuetta, esimerkiksi =VLOOKUP(40;Sheet2!A2:B15,2). Kaava osoittaa, että hakualue A2:B15 on taulukossa 2.
Arkin nimeä ei tietenkään tarvitse syöttää manuaalisesti. Aloita vain kaavan kirjoittaminen, ja kun kyse on taulukon argumentista, vaihda hakulaskentataulukkoon ja valitse alue hiirellä.
Alla olevassa kuvassa näkyvä kaava etsii "Hinnat"-laskentataulukon sarakkeen A (hakualueen A2:B9 1. sarake) solussa A2 ("Tuote 3") olevaa tekstiä:
VHAKU(A2,hinnat!$A$2:$B$8,2,FALSE)
VLOOKUP-toiminto Excelissä - VHAKU-toiminto eri arkeilla
Nimetyn alueen tai taulukon käyttäminen VLOOKUP-kaavoissa
Jos aiot käyttää samaa hakualuetta useissa VHAKU-kaavoissa, voit luoda ja kirjoittaa sille nimen suoraan VHAKU-kaavasi taulukkoargumentissa.
Luodaksesi nimetyn alueen, valitse solut ja kirjoita mikä tahansa nimi Kaava-paneelin vasemmalla puolella olevaan Nimi-ruutuun.
VLOOKUP-funktio Excelissä – alueen nimeäminen
Nyt voit kirjoittaa seuraavan VLOOKUP-kaavan saadaksesi tuotteen 1 hinnan:
VLOOKUP("Tuote 1";Tuotteet;2)
VHAKU-funktio Excelissä - Esimerkki VHAKU-funktiosta, jolla on alueen nimi
Useimmat Excelin alueen nimet koskevat koko työkirjaa, joten sinun ei tarvitse määrittää laskentataulukon nimeä, vaikka hakualue olisikin eri laskentataulukossa. Tällaiset kaavat ovat paljon ymmärrettävämpiä. Myös nimettyjen alueiden käyttö voi olla hyvä vaihtoehto soluille. Koska nimetty alue ei muutu, kun kaava kopioidaan muihin soluihin, voit olla varma, että hakualue pysyy aina oikeana.
Jos olet muuntanut solualueen monipuoliseksi Excel-taulukoksi (Lisää välilehti --> Taulukko), voit valita hakualueen hiirellä, jolloin Microsoft Excel lisää automaattisesti sarakkeiden nimet tai taulukon nimet kaavaan. :
VLOOKUP-funktio Excelissä - Esimerkki VLOOKUP-funktiosta taulukon nimellä
Koko kaava voi näyttää tältä:
VLOOKUP("Tuote 1"; Taulukko 6[[Tuote]:[Hinta]];2)
tai jopa =VLOOKUP("Tuote 1";Taulukko6;2).
Kuten nimetyt alueet, sarakkeiden nimet ovat vakioita, eivätkä soluviittaukset muutu riippumatta siitä, mihin VLOOKUP-kaava kopioidaan.
VLOOKUP-toiminto useilla ehdoilla
Harkitsemme esimerkki VLOOKUP-funktiosta, jossa on useita ehtoja. Meillä on seuraavat syöttötiedot:
VLOOKUP-toiminto Excelissä - Lähdetietotaulukko
Oletetaan, että meidän on käytettävä VLOOKUP-toiminto useilla ehdoilla. Esimerkiksi tuotteen hinnan etsiminen kahdella kriteerillä: tuotteen nimi ja tyyppi.
- käyttää VLOOKUP-toiminto useilla ehdoilla Alkuun on lisättävä lisäsarake, joka tallentaa tiedot tuotteen nimellä ja tyypillä.
Joten lisää "Hinnat" -sivulle sarake ja kirjoita soluun A2 seuraava kaava:
Tämän kaavan avulla saamme "Tuote"- ja "Tyyppi"-sarakkeiden arvon. Täytä kaikki solut.
Nyt hakutaulukko näyttää tältä:
VLOOKUP-funktio Excelissä - Apusarakkeen lisääminen
- Nyt "Myynti"-arkin soluun C2 kirjoitamme seuraavan VLOOKUP-kaavan:
VHAKU(A2Hinnat!$A$1:$D$8,4, EPÄTOSI)
Täytämme loput solut ja tuloksena saamme kunkin tuotteen hinnat tyypin mukaan:
VLOOKUP-funktio Excelissä - VHAKU-esimerkki useilla ehdoilla
Katsotaan nyt sitä VLOOKUP-toimintovirheet.
Miksi VLOOKUP-toiminto ei toimi?
Tässä artikkelin osassa tarkastelemme miksi VLOOKUP-toiminto ei toimi ja mahdollista VLOOKUP-toimintovirheet.
|
Virhetyyppi |
Aiheuttaa |
Ratkaisu |
|
Haetun sarakkeen sijainti on väärä |
Taulukon sarakkeen, jossa haku suoritetaan, PITÄÄ olla vasemmanpuoleisin.
|
|
|
Pöydän valikoima ei ole kiinteä |
Jos ensimmäinen arvo näytettiin oikein ja VLOOKUP-kaavan venytyksen jälkeen joissakin soluissa havaitaan virhe #N/A, taulukon aluetta ei ole kiinteä.
|
|
|
Tarkkaa vastaavuutta ei löytynyt (jos sisään ajastettu katselu etsi tarkka valittu arvo (0) |
||
|
IN ajastettu katselu suoritetaan lähimmän arvon (1) haku, eikä taulukkoa, jonka mukaan haku suoritetaan, lajiteta. |
Lajittele taulukon ensimmäinen sarake nimien nousevaan järjestykseen. |
|
|
Käytä TYYPPI- tai VÄLILYÖNTI-toimintoja. |
||
|
Sarakenumeron arvo ylittää taulukon sarakkeiden määrän |
Tarkista palautusarvon sisältävä sarakenumero. |
|
|
Kaavasta puuttuu lainaus |
Jos käytät as haluttu arvo ei solulinkkiä, vaan tekstiä, niin se on suljettava lainausmerkein. Esimerkiksi: VLOOKUP("Tuote 1"; Hinnat!$A$2:$B$8,2,0) |
Toivon sitä nytkin dummeille VLOOKUP-toiminto Excelissä tulee selväksi.
Batjanov Denis Vierailevana kirjoittajana hän puhuu tässä viestissä siitä, kuinka löytää tietoja yhdestä Excel-taulukosta ja purkaa se toiseen, ja paljastaa myös kaikki pystynäkymätoiminnon salaisuudet.
Poimintasarakkeen määrittäminen COLUMN-funktiolla
Jos taulukolla, johon haet tietoja VLOOKUP:n avulla, on sama rakenne kuin hakutaulukolla, mutta se sisältää yksinkertaisesti vähemmän rivejä, VLOOKUP voi käyttää COLUMN()-funktiota laskeakseen automaattisesti noudettavat sarakenumerot. Tässä tapauksessa kaikki VLOOKUP-kaavat ovat samat (säädetty ensimmäiselle parametrille, joka muuttuu automaattisesti)! Huomaa, että ensimmäisellä parametrilla on absoluuttinen sarakkeen koordinaatti.
Yhdistelmäavaimen luominen käyttämällä &»|»&
Jos on tarve etsiä useista sarakkeista samanaikaisesti, on hakuun luotava yhdistelmäavain. Jos palautettava arvo ei olisi tekstiä (kuten "Koodi"-kentän tapauksessa), vaan numeerinen, niin kätevämpi SUMIFS-kaava sopisi tähän eikä yhdistelmäsarakeavainta tarvittaisi ollenkaan.
Tämä on ensimmäinen artikkelini Lifehackerille. Jos pidit siitä, kutsun sinut vierailemaan verkkosivustollani, ja luen myös mielelläni kommenteissa VLOOKUP-toiminnon ja vastaavien käytön salaisuuksistasi. Kiitos. :)
Yhteenvetotaulukon kanssa työskentely edellyttää arvojen vetämistä muista taulukoista siihen. Jos taulukoita on paljon, manuaalinen siirto vie valtavasti aikaa, ja jos tietoja päivitetään jatkuvasti, tämä on Sisypho-tehtävä. Onneksi on olemassa VLOOKUP-toiminto, joka tarjoaa mahdollisuuden hakea tietoja automaattisesti. Katsotaanpa konkreettisia esimerkkejä tämän toiminnon toiminnasta.
VLOOKUP-toiminnon nimi tarkoittaa "pystykatselutoimintoa". Englanniksi sen nimi on VLOOKUP. Tämä funktio etsii tietoja tutkittavan alueen vasemmasta sarakkeesta ja palauttaa sitten tuloksena olevan arvon määritettyyn soluun. Yksinkertaisesti sanottuna VLOOKUP antaa sinun järjestää arvot uudelleen yhden taulukon solusta toiseen taulukkoon. Katsotaanpa, kuinka VLOOKUP-toimintoa käytetään Excelissä.
Esimerkki VLOOKUP:n käytöstä
Katsotaanpa, kuinka VLOOKUP-toiminto toimii tietyn esimerkin avulla.
Meillä on kaksi pöytää. Ensimmäinen niistä on ostotaulukko, johon sijoitetaan elintarvikkeiden nimet. Seuraava sarake nimen jälkeen sisältää ostettavan tuotteen määrän. Seuraavaksi tulee hinta. Ja viimeisessä sarakkeessa - tietyn tuotteen oston kokonaiskustannukset, jotka lasketaan soluun jo syötetyn kaavan avulla määrän kertomiseksi hinnalla. Mutta meidän on vain nostettava hintaa käyttämällä VLOOKUP-toimintoa viereisestä taulukosta, joka on hinnasto.
Kuten näette, perunoiden hinta on lisätty taulukkoon hinnastosta. Jotta emme joutuisi käymään läpi niin monimutkaista toimenpidettä muiden tuotenimien kanssa, seisomme yksinkertaisesti täytetyn solun oikeassa alakulmassa niin, että näkyviin tulee risti. Piirrämme tämän ristin taulukon alaosaan.
Näin ollen vedimme kaikki tarvittavat tiedot taulukosta toiseen käyttämällä VLOOKUP-toimintoa.
Kuten näet, VLOOKUP-toiminto ei ole niin monimutkainen kuin miltä näyttää ensi silmäyksellä. Sen käytön ymmärtäminen ei ole kovin vaikeaa, mutta tämän työkalun hallitseminen säästää paljon aikaa työskennellessäsi taulukoiden kanssa.
Microsoft Excelissä on paljon erilaisia toimintoja, jotka helpottavat käyttäjän työtä huomattavasti, ja tässä artikkelissa puhumme yhdestä niistä. Sitä kutsutaan nimellä VLOOKUP, tai jos englanniksi, niin VLOOKUP.
VLOOKUP-toiminto siirtää arvot yhdestä taulukosta tiettyihin soluihin toisessa. Selitän tarkemmin - ensimmäisessä taulukossa valitset arvon, joka sinun on löydettävä toisen vasemmasta sarakkeesta. Jos osumia löytyy, VLOOKUP siirtää arvon tällä rivillä määrittämästäsi sarakkeesta ensimmäiseen taulukkoon. Vaikka määritelmä on hieman hämmentävä, funktion käyttäminen ei ole niin vaikeaa. Katsotaanpa muutamia esimerkkejä.
Koska funktiota käytetään useimmiten sarakkeen täyttämiseen hinnalla, joka on ilmoitettu yksittäisissä hinnastoissa, otetaan seuraava esimerkki. Siellä on esimerkiksi hedelmiä sisältävä pöytä (oranssi), jossa jokaiselle ilmoitetaan kuinka monta kiloa haluamme tilata. Vastaavasti jokaiselle hedelmälle on kirjoitettava hinta toimittajan antamasta hinnastosta (vihreä) ja laskettava sitten, kuinka paljon osto maksaa. Jokaisen tuotteen tarkasteleminen ja tietojen siirtäminen on vaikeaa, varsinkin jos tavararivejä on tuhansia, joten käytämme VLOOKUPia.
Napsauta Hinta-sarakkeen ensimmäisen taulukon ylintä solua ja sitten kaavapalkin "fx"-painiketta avataksesi ohjatun funktion ikkunan.
Seuraavaksi kirjoitamme argumentit ehdotettuihin kenttiin.
Kursivoi kenttään "Search_value" ja korosta ensimmäisessä taulukossa arvo, jota etsimme. Minulla on tämä omena.
"Taulukko" -rivillä sinun on valittava se, josta tiedot otetaan - älä valitse otsikkoa. Huomaa, että vasemmanpuoleisen sarakkeen tulisi koostua etsimistämme arvoista. Eli tarvitsen omenan ja muita hedelmiä, mikä tarkoittaa, että niiden luettelon tulisi olla valitun alueen vasemmassa sarakkeessa.
Jotta kun kaava on kirjoitettu ja venytetty koko sarakkeen poikki, valittu alue ei liiku alaspäin, meidän on tehtävä linkit absoluuttiseksi: valitse tiedot kentästä ja paina "F4". Nyt solujen osoitteesta on tullut absoluuttinen, niihin on lisätty "$" -merkki, eikä alue muutu.
Jos sarakkeen numero on, laita toisessa taulukossa oleva numero siihen sarakkeeseen, josta haluat siirtää tietoja. Hinnastoni koostuu hedelmistä ja hinnasta, tarvitsen toisen, joten laitoin numeron "2".
IN "Aikaväli" Kirjoitamme "FALSE" - jos haluat etsiä tarkkoja hakuja, tai "TRUE" - jos arvot voivat olla likimääräisiä. Esimerkkiämme valitsemme ensimmäisen. Jos et määritä mitään tähän kenttään, toinen valitaan oletuksena. Napsauta sitten "OK".
Kiinnitä tässä huomiota seuraavaan: jos työskentelet numeroiden kanssa ja määrität "True", toinen taulukko (tämä on hinnastomme) on lajiteltava nousevaan järjestykseen. Esimerkiksi haettaessa 5.25 löytyy 5.27 ja tältä riviltä otetaan tiedot, vaikka voi olla myös luku 5.2599 alapuolella - mutta kaava ei katso pidemmälle, koska se luulee alla olevien lukujen olevan vain suurempia .
Miten VLOOKUP toimii? Se ottaa halutun arvon (omena) ja etsii sitä määritetyn alueen (hedelmäluettelo) vasemmanpuoleisesta sarakkeesta. Jos vastaavuus löytyy, arvo otetaan samalta riviltä, vain argumenteissa (2) määritellystä sarakkeesta ja siirretään tarvitsemamme soluun (C2). Kaava näyttää tältä:
VHAKU(A2,$G$2:$H$12,2,EPÄTOSI)
Nyt voit venyttää sitä alas tarvittavan määrän rivejä vetämällä oikeasta alakulmasta.
Kaikki hinnat on siirretty hinnastosta hankintataulukkoon hedelmien nimien mukaisesti.
Jos sinulla on ensimmäisessä taulukossa tuotteiden nimet, jotka eivät ole hinnastossa, minulle nämä ovat vihanneksia, niin näiden kohtien kohdalla VLOOKUP-kaava tuottaa virheen #N/A.
Kun lisäät sarakkeita laskentataulukkoon, funktion Taulukko-argumentin tiedot muuttuvat automaattisesti. Esimerkissä hintaa on siirretty 2 saraketta oikealle. Valitse mikä tahansa solu kaavalla ja katso, että $G$2:$H$12 sijaan se on nyt $I$2:$J$14 .
Selvitetään nyt, kuinka työskennellä VLOOKUP-funktion kanssa Excelissä ja avattavien luetteloiden kanssa. Ensin sinun on tehtävä avattava luettelo. Valitse solu, avaa "Data" yläreunasta ja napsauta painiketta "Tietojen tarkistus".
Avautuvassa "Data Type" -ikkunassa on "Lista", alla ilmoitamme lähdealueen - nämä ovat hedelmien nimet, eli sarake, joka on sekä ensimmäisessä että toisessa taulukossa. Napsauta "OK".
Valitsen F2 ja lisään VLOOKUP-funktion. Ensimmäinen argumentti on tehty lista (F1).
Toinen on toimittajataulukko, jossa on ilmoitetut hinnat. Muista, että tässä vasemman sarakkeen on vastattava avattavan luettelon tietoja.
Siitä tuli kuin haku: valitsemme hedelmän ja VPR löytää sen hinnan hinnastosta.
Jos sinulla oli samat hinnat ja sitten toimittajat antoivat sinulle uuden luettelon, sinun täytyy jotenkin nähdä, mikä on muuttunut. Käsin tekeminen kestää kauan, mutta kyseisen toiminnon avulla kaikki voidaan tehdä erittäin nopeasti. Sinun on lisättävä yksi sarake ja siirrettävä siihen uusia arvoja ja verrattava sitten tietoja.
Napsauta mitä tahansa solua sarakkeessa D ja lisää uusi.
Nimesin sitä Uudeksi hinnaksi - uudet hinnat ovat täällä ja vanhat hinnat näkyvät vasemmalla olevassa sarakkeessa. Uusi taulukkoni sijaitsee toisella taulukolla, jotta ymmärrät kuinka VLOOKUP-toimintoa käytetään, jos tiedot sijaitsevat eri taulukoissa. Valitse lisätystä sarakkeesta ensimmäinen tyhjä solu.
Lisäämme funktion ja määritämme argumentit. Ensinnäkin mitä etsimme, esimerkissä omena (A2). Voit valita alueen uudesta hinnastosta asettamalla kohdistimen "Taulukko"-kenttään ja siirtymällä haluttuun taulukkoon, minulla on "Sheet1".
Käytä hiirtä valitaksesi tarvittavat sarakkeet ja rivit ilman otsikoita.
Seuraavaksi teemme absoluuttisia viittauksia soluihin: "Sheet1!$A$2:$B$12" . Valitse rivi ja paina "F4" lisätäksesi dollarimerkin solujen osoitteisiin. Merkitsemme sarakkeen (2) ja kirjoitamme "FALSE".
Napsauta lopuksi "OK"-painiketta.
Nyt kaksi saraketta uusilla ja vanhoilla hinnoilla sijaitsevat vierekkäin ja voit tehdä joko visuaalisen vertailun tai käyttämällä tiettyjä kaavoja tai ehdollista muotoilua.
Toivon, että olen toimittanut vaiheittaiset ohjeet VLOOKUP-funktion käyttämiseen ja soveltamiseen Excelissä, ja nyt kaikki on sinulle selvää.
Arvioi tämä artikkeli: (13
arvosanat, keskiarvo: 5,00
viidestä)
Verkkovastaava. Korkeakoulututkinto tietoturvasta. Useimpien artikkeleiden ja tietokonelukutaidon oppituntien kirjoittaja
- SUMPRODUCT-toiminnon käyttäminen
- Yhdistää sarakkeiden arvot Kuukausi Ja Kaupunki yhdessä taulukossa: JanMoscow, FebMoscow...
- Yhdistää kaksi taulukkoa kaksisarakkeiseksi taulukoksi.
Me kaikki tiedämme, kuinka hyödyllistä se on. Todennäköisesti puolet kaikista Excelin toiminnoista tehdään sen avulla. Tällä toiminnolla on kuitenkin useita rajoituksia. Esimerkiksi VLOOKUP tarkastelee vain taulukon vasemmanpuoleista saraketta tai etsii vain yhtä ehtoa. Mutta entä jos meidän on palautettava arvo, joka vastaa kahta ehtoa. Tässä tapauksessa meidän on turvauduttava joihinkin temppuihin. Puhumme näistä temppuista tämän päivän artikkelissa.
Joten tarkastelemme neljää vaihtoehtoa korvausfunktion luomiseksi kahdella ehdolla:
No, aloitetaan yksinkertaisimmasta asiasta.
Lisäsarakkeen käyttö
Useimmissa tapauksissa monimutkaisista ongelmista tulee yksinkertaisempia ja helpompia hallita, kun ne pilkotaan pieniksi paloiksi. Sama pätee luotaessa kaavoja Excelissä.
Katsotaanpa klassista esimerkkiä. Meillä on taulukko, jossa myynti kuukausittain ja kaupungeittain. Ja meidän on määritettävä myyntiarvo, joka vastaa kahta ehtoa: kuukausi - helmikuu ja kaupunki - Samara.
VLOOKUP-funktion käyttäminen sen klassisessa muodossa ei auta meitä, koska se voi palauttaa vain arvon, joka vastaa vain yhtä ehtoa. Ylimääräinen sarake auttaa meitä pääsemään tästä tilanteesta, jossa yhdistämme Kuukausi- ja Kaupunki-sarakkeiden arvot. Tee tämä kirjoittamalla kaava =B2&C2 soluun A2 ja laajentamalla tämä kaava soluun A13. Nyt voimme käyttää sarakkeen A arvoja palauttamaan vaadittu arvo. Kirjoitamme kaavan soluun G3:
VHAKU(G1A2:D13;4;0)
Tämä kaava yhdistää solujen G1 ja G2 kaksi ehtoa yhdeksi riviksi ja tarkastelee niitä sarakkeessa A. Kun haluttu ehto on löydetty, kaava palauttaa arvon taulukon A1:D13 neljännestä sarakkeesta, ts. sarakkeessa Myynti.
Uuden hakutaulukon luominen SELECT-toiminnolla
Jos ylimääräisen sarakkeen käyttö ei jostain syystä ole meille vaihtoehto, voimme käyttää .
SELECT-funktion käyttäminen edellyttää uuden taulukon luomista tarkastellaksesi sarakkeiden arvoja Kuukausi Ja Kaupunki jo yhdistetty. Kaavamme näyttää tältä:
VHAKU(G1VALITSE((1,2),B2:B13&C2:C13,D2:D13),2,0)
Tämän kaavan pääkohta on CHOICE((1;2);B2:B13&C2:C13;D2:D13)-osa, joka tekee kaksi asiaa:
Tämän funktion tulos on taulukko, joka näyttää tältä:
Nyt kaava on tullut selkeämmäksi.
TÄRKEÄÄ: Koska käytimme taulukkokaavaa, kun olet syöttänyt kaavan, paina Ctrl+Shift+Enter kertoaksesi ohjelmalle aikeistamme. Tämän näppäinyhdistelmän painamisen jälkeen ohjelma asentaa automaattisesti kiharat aaltosulkeet kaavan alkuun ja loppuun.
INDEX- ja MATCH-toimintojen käyttäminen
Kolmas menetelmä, jota tarkastelemme, sisältää myös taulukkokaavan käytön ja käyttää INDEX- ja SEARCH-funktioita.
Kaava tulee näyttämään tältä.
INDEKSI(D2:D13,MATCH(1,(B2:B13=G1)*(C2:C13=G2),0))
Katsotaanpa, mitä tämän kaavan jokainen osa tekee.
Harkitse ensin funktiota MATCH(1;(B2:B13=G1)*(C2:C13=G2);0). Tässä tapauksessa solun G1 arvoa verrataan peräkkäin kunkin alueen B2:B13 solujen arvoon ja palauttaa arvon TRUE, jos arvot täsmäävät, ja EPÄTOSI, jos ne eivät ole. Sama vertailu tehdään solun G2 arvon ja alueen C2:C13 kanssa. Seuraavaksi vertaamme molempia taulukoita, jotka koostuvat TOSI ja EPÄTOSI. Yhdistelmä TOSI * TOSI antaa tulokseksi 1 (TOSI). Katsotaanpa alla olevaa kuvaa, joka auttaa selittämään toimintaperiaatteen selkeämmin.
Nyt voimme kertoa missä merkkijono, joka täyttää molemmat ehdot, sijaitsee. MATCH-funktio löytää arvon 1 paikan tulostaulukosta ja palauttaa luvun 6, koska 1 esiintyy kuudennella rivillä. Seuraavaksi INDEKSI-funktio palauttaa alueen D2:D13 kuudennen rivin arvon.
Käyttämällä SUMPRODUCTia
Yksi tehokkaimmista Excel-kaavoista. Minulla on jopa erillinen artikkeli omistettu tälle kaavalle. Neljäs tapamme käyttää useita ehtoja on kirjoittaa kaava SUMPRODUCT-funktiolla. Ja se tulee näyttämään tältä:
SUMMA((B2:B13=G1)*(C2:C13=G2);D2:D13)
Tämän kaavan toimintaperiaate on samanlainen kuin edellisen lähestymistavan toimintaperiaate. Luodaan virtuaalinen taulukko, jossa solujen G1 ja G2 arvoja verrataan alueisiin B2:B13 ja C2:C13. Seuraavaksi näitä molempia taulukoita verrataan ja saadaan ykkösten ja nollien matriisi, jossa yksi osoitetaan riville, jossa molemmat ehdot vastaavat. Seuraavaksi tämä virtuaalitaulukko kerrotaan alueella D2:D13. Koska virtuaalitaulukossamme on vain yksi 1 kuudennessa rivissä, kaava palauttaa tuloksen 189.
Tämä toiminto ei toimi, jos tekstiarvoja on alueella D2:D13.
Ymmärtääksesi, miten tämä kaava toimii, suosittelen lukemaan artikkelin SUMPROIZ-funktiosta.
TULOS
Joten mitä menetelmää sinun pitäisi käyttää? Vaikka ne kaikki toimivat luotettavasti, pidän parempana ensimmäistä menetelmää. Päivittäisessä työssäni työskentelen mieluummin tiedostojen kanssa, jotka ovat helposti ymmärrettäviä ja muutettavissa. Molemmat vaatimukset täyttävät ensimmäisen lähestymistavan ehdot.
Kaavojen ymmärtämiseksi paremmin voit tarkastella niitä tämän päivän artikkelissa.
