Tässä artikkelissa opimme kuinka HAKUA arvo kahden numeron välillä Excelissä.
Skenaario:
Taulukosta on helppo hakea arvo yhdellä kriteerillä. Voimme yksinkertaisesti käyttää VLOOKUPia. Mutta mitä voisimme tehdä, jos useat sarakeehdot vastaavat tietojasi ja meidän on haettava useita sarakkeita arvon vastaamiseksi. Opetellaan, miten tämä ongelma voidaan ratkaista käyttämällä Excelin eri hakuversioita
LOOKUP -arvo kahden numeron välillä
VLOOKUP -kaavan avulla
Jos arvoa VLOOKUP ei löydy, jos arvoa ei löydy, viimeinen hakuarvoa pienempi arvo täsmää ja annetun sarakehakemiston arvo palautetaan.
Yleinen kaava LOOKUP -arvoon kahden numeron välillä:
| = VLOOKUP (arvo, taulukko, look_col, 1) |
Ja vielä yksi Vlookupin asia on se, että se etsii arvon sarakkeesta ja jos se ei löydä arvoa sarakejoukosta, se vastaa ja palauttaa arvon, joka on pienempi kuin taulukkotaulukon arvo.
Huomautus: VLOOKUP vastaa oletusarvoisesti likimääräistä vastaavuutta, jos jätämme pois aluehaun muuttujan. Arvioitu vastaavuus on hyödyllinen, kun haluat tehdä likimääräisen vastaavuuden ja kun taulukkotaulukko on lajiteltu nousevaan järjestykseen.
Käyttämällä INDEX- ja MATCH -kaavaa
INDEX- ja MATCH -kaava toimii samalla tavalla kuin edellä, mutta eri syntaksilla. Mutta jos sinun pitäisi valita vain se, jonka toiminnot sinulle sopivat
Yleinen kaava LOOKUP -arvoon kahden numeron välillä
| = INDEKSI (look__range, MATCH (1, INDEX ((kriteerit1 = alue1)*(kriteerit2 = alue2),0,1),0)) |
lookup_range: alue, josta haluat hakea arvon.
Kriteerit1, Kriteerit2, Kriteerit N: Nämä ovat kriteerit, jotka haluat sovittaa alueille 1, alue2 ja alue N. Sinulla voi olla jopa 270 ehtoa - alueparit.
Alue1, alue2, alueN: Nämä ovat alueita, joilla vastaat kriteerejäsi.
Esimerkki:
Kaikkien näiden ymmärtäminen voi olla hämmentävää. Ymmärrämme, miten toimintoa käytetään esimerkin avulla. Tässä meillä on opiskelijakortti ja vastaavat merkit testissä. Nyt meidän piti saada arvosanat jokaiselle oppilaalle katsomalla arvosanojärjestelmätaulukosta.
Tätä varten käytämme VLOOKUP -funktion määritettä, joka on, jos VLOOKUP -funktio ei löydä täsmällistä vastaavuutta, jota se etsii vain taulukosta ja vain jos funktion viimeinen argumentti on joko TOTTA tai 1.
Pisteiden / pistetaulukon on oltava nousevassa järjestyksessä
Käytä kaavaa:
| = HAKU (B2, taulukko, 2, 1) |
Kuinka se toimii?
Vlookup -toiminto etsii arvon 40 merkin sarakkeesta ja palauttaa vastaavan arvon, jos se vastaa. Jos se ei täsmää, funktio etsii pienempää arvoa kuin hakuarvo (eli 40) ja palauttaa tuloksen.
Tässä taulukko_matriisi on nimetty alue as pöytä kaavassa ja B2 annetaan soluviittauksena.
Kuten näette, saimme arvosanan ensimmäiselle oppilaalle. Nyt kaikkien muiden luokkien saamiseksi käytämme pikakuvaketta Ctrl + D tai käytä Excelin vedä alas -vaihtoehtoa.
Tässä ovat kaikki luokan arvosanat, jotka käyttävät VLOOKUP -toimintoa.
LOOKUP -arvo kahden numeron välillä Työntekijä -taulukossa
Meillä on taulukko, joka sisältää tiedot organisaation kaikista työntekijöistä erillisellä taulukolla. Ensimmäinen sarake sisältää näiden työntekijöiden tunnuksen. Olen antanut tämän taulukon nimeksi emp_data.
Nyt hakusivuni on haettava työntekijän tiedot, joiden tunnus on kirjoitettu soluun B3. Olen nimennyt B3: n tunnukseksi.
Ymmärtämisen helpottamiseksi kaikki sarakkeen otsikot ovat täsmälleen samassa järjestyksessä kuin emp_data -taulukko.
Kirjoita nyt alla oleva kaava soluun C3, jotta voit hakea B3: ssa kirjoitetun työntekijän tunnuksen vyöhykkeen.
| = HAKU (ID, Emp_Data, 2,0) |
Tämä palauttaa työntekijän tunnuksen vyöhykkeen 1-1830456593, koska tietokannan sarake 2 sisältää työntekijöiden vyöhykkeen.
Kopioi tämä kaava muihin soluihin ja muuta kaavan sarakkeen numero saadaksesi kaikki työntekijöiden tiedot.
Näet kaikki mainittuun tunnukseen liittyvät tiedot solussa B3. Riippumatta siitä, minkä tunnuksen kirjoitat soluun B3, kaikki tiedot haetaan tekemättä muutoksia kaavaan.
Miten tämä toimii?
Ei mitään hankalaa. Käytämme yksinkertaisesti VLOOKUP -toimintoa tunnuksen etsimiseen ja sitten mainitun sarakkeen noutamiseen. Harjoittele VLOOKUPia käyttämällä tällaisia tietoja ymmärtääksesi VLOOKUPia paremmin.
Hae työntekijöiden tiedot otsikoiden avulla
Yllä olevassa esimerkissä kaikki sarakkeet järjestettiin samaan järjestykseen, mutta joskus saat tietokannan, jossa on satoja sarakkeita. Tällaisissa tapauksissa tämä menetelmä työntekijöiden tietojen noutamiseksi ei ole hyvä. On parempi, jos kaava voi tarkastella sarakkeen otsikkoa ja noutaa tiedot sarakkeesta työntekijätaulukosta.
Joten hakeaksesi taulukosta arvon sarakkeen otsikon avulla, käytämme kaksisuuntaista hakumenetelmää tai sanomme dynaamisen sarakkeen VLOOKUP.
Käytä tätä kaavaa solussa C3 ja kopioi loput solut. Sinun ei tarvitse muuttaa mitään kaavassa, kaikki haetaan thd emp_datasta.
| = HAKU (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Tämä kaava yksinkertaisesti hakee kaikki tiedot vastaavista sarakkeista. Voit sekoittaa raportin otsikot, tämä ei muuta mitään. Riippumatta siitä, mikä otsikko on kirjoitettu yllä olevaan soluun, vastaavat tiedot sisältävät.
Miten tämä toimii?
Tämä on yksinkertaisesti dynaaminen VLOOKUP. Voit lukea siitä täältä. Jos selitän sen täällä, siitä tulee liian suuri artikkeli.
Hae työntekijän tunnus osittaisella haulla
Voi tapahtua, että et muista koko työntekijän henkilötunnusta, mutta haluat silti hakea jonkin henkilötunnuksen tiedot. Tällaisissa tapauksissa osittainen ottelu VLOOKUP on paras ratkaisu.
Jos esimerkiksi tiedän, että jokin tunnus sisältää 2345, mutta en tiedä koko tunnusta. Jos syötän tämän numeron soluun C3, lähtö on kuten.
Emme saa mitään. Koska mikään ei vastaa taulukkoa 2345. Muokkaa yllä olevaa kaavaa näin.
| =VLOOKUP("*"&ID&"*", Emp_Data,OTTELU(C2, Emp_Data_Headers, 0), 0) |
Kopioi tämä koko riville. Ja nyt sinulla on ensimmäisen työntekijän tiedot, jotka sisältävät tämän numeron.
Huomaa, että saamme ensimmäisen tunnuksen, joka sisältää vastaavan numeron Emp Id -sarakkeessa. Jos jokin muu tunnus sisältää saman numeron, tämä kaava ei hae kyseisen työntekijän tietoja.
Jos haluat saada kaikki työntekijätunnukset, jotka sisältävät saman numeron, käytä kaavaa, joka etsii kaikki vastaavat arvot.
Käyttämällä INDEX- ja MATCH -kaavaa
Tässä meillä on tietotaulukko. Haluan hakea asiakkaan nimen käyttämällä varauspäivää, rakentajaa ja aluetta. Tässä on siis kolme kriteeriä ja yksi hakualue.
Kirjoita tämä kaava soluun I4 ja paina Enter.
| = INDEKSI (E2: E16, MATCH (1, INDEKSI ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Kuinka se toimii:
Tiedämme jo, kuinka INDEX- ja MATCH -toiminnot toimivat EXCELissä, joten en aio selittää sitä täällä. Puhumme temppusta, jota käytimme täällä.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Pääosa on tämä. Tämän lausunnon jokainen osa palauttaa joukon tosi epätosi.
Kun totuusarvot kerrotaan, ne palauttavat taulukon 0 ja 1. Kerroin toimii AND -operaattorina. Tiukka, kun kaikki arvot ovat totta vain silloin se palauttaa 1 muu 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Tämä palauttaa kaiken
| {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}*
{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} {101} {101} |
Joka kääntyy
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): INDEX -funktio palauttaa saman taulukon ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) MATCH -funktioksi hakuvaihtoehtona.
MATCH (1, INDEKSI ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH -funktio etsii 1 taulukosta {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Ja palauttaa taulukosta löydetyn ensimmäisen 1 indeksinumeron. Tässä on 8.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): INDEX palaa lopuksi arvo annetulta alueelta (E2: E16) löydetyllä indeksillä (8).
Tässä on kaikki havaintohuomautukset Excelin kaavan avulla
Huomautuksia:
- Pisteet / pisteet -taulukon sarakkeen on oltava nousevassa järjestyksessä, muuten funktio voi antaa väärän tuloksen.
- VLOOKUP -toiminto etsii taulukon taulukon ensimmäisen rivin arvon ja poimii vastaavat arvot vain hakualueen oikealta puolelta.
- Funktion VLOOKUP -funktion viimeinen argumentti on asetettava arvoon TRUE tai 1, jotta saadaan likimääräinen vastaavuus.
- VLOOKUP -funktio palauttaa virheen, jos työkirjan osoite on virheellinen tai väärä.
- VLOOKUP -funktio palauttaa virheen, jos arvoa ei täsmää.
Toivottavasti tämä artikkeli siitä, miten LOOKUP -arvo kahden numeron välillä Excelissä, on selittävä. Täältä löydät lisää artikkeleita arvojen laskemisesta ja niihin liittyvistä Excel -kaavoista. Jos pidit blogeistamme, jaa ne 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.
Tapa käyttää Vlookup -toimintoa tietojen validoinnissa : Rajoita käyttäjiä sallimaan arvot hakutaulukosta käyttämällä Excelin Tietojen vahvistuskaava -ruutua. Tietojen validoinnin kaavaruudun avulla voit valita vaaditun rajoituksen tyypin.
Kuinka hakea uusin hinta Excelissä : On tavallista päivittää hinnat kaikissa yrityksissä, ja uusimpien hintojen käyttäminen kaikissa ostoissa tai myynneissä on välttämätöntä. Nouda uusin hinta Excel -luettelosta käyttämällä LOOKUP -toimintoa. LOOKUP -toiminto hakee viimeisimmän hinnan.
VLOOKUP -toiminto laskea arvosanan Excelissä : Arvosanojen laskeminen IF ja IFS eivät ole ainoita toimintoja, joita voit käyttää. VLOOKUP on tehokkaampi ja dynaamisempi tällaisille ehdollisille laskelmille.
17 asiaa Excel VLOOKUPista : VLOOKUPia käytetään useimmiten haettujen arvojen hakemiseen, mutta VLOOKUP voi tehdä paljon enemmän kuin tämä. Tässä on 17 asiaa VLOOKUPista, jotka sinun pitäisi tietää tehokkaasti.
KATSO ensimmäinen teksti Excelin luettelosta : VLOOKUP -toiminto toimii hyvin jokerimerkillä. Voimme käyttää tätä poimiaksesi ensimmäisen tekstiarvon tietystä Excel -luettelosta. Tässä on yleinen kaava.
LOOKUP -päivämäärä, jossa on luettelon viimeinen arvo : Haemme viimeisimmän arvon sisältävän päivämäärän LOOKUP -toiminnolla. Tämä toiminto etsii solun, joka sisältää vektorin viimeisen arvon, ja käyttää sitten tätä viitettä päivämäärän palauttamiseen.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi : Nopeuta tehtävien suorittamista Excelissä. Nämä pikanäppäimet auttavat sinua parantamaan työn tehokkuutta Excelissä.
VLOOKUP -toiminnon käyttäminen Excelissä : Tämä on yksi Excelin käytetyimmistä ja suosituimmista toiminnoista, jota käytetään eri alueiden ja taulukkojen arvojen etsimiseen.
IF -toiminnon käyttäminen Excelissä : Excelin IF -käsky tarkistaa ehdon ja palauttaa tietyn arvon, jos ehto on TOSI, tai palauttaa toisen arvon, jos EPÄTOSI.
SUMIF -toiminnon käyttäminen Excelissä : Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.
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. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.