Arvon hakeminen viiteverkosta hakemistojen arvojen mukaan Tallenna Exceliin

Anonim

Tässä artikkelissa opimme kuinka hakea arvo viiteverkosta hakemistojen arvojen mukaan Tallenna Exceliin

Skenaario:

Monta kertaa, kun käsittelemme pitkiä hajauttamattomia tietoja, meidän on ensin puhdistettava tiedot ennen niiden käsittelyä. Se vie aikaa ja halusit vain poimia joitakin kyselyitä. Tietojen poimimiseen käytetään yleensä VLOOKUP -toimintoa. Mutta kun meillä on pitkiä tietoja, joissa on monia sarakekenttiä, se muuttuu sotkuiseksi, koska VLOOKUP -toiminto vaatii oikean sarakeindeksin numerona, tai muuten kaava palauttaa virheen. Siellä alla on kaavan selitys tällaiselle ongelmalle.

Kuinka ratkaista ongelma?

Jotta kaava ymmärtäisi ensin, meidän on tarkistettava hiukan seuraavia toimintoja

  1. INDEX -toiminto
  2. MATCH -toiminto

Nyt teemme kaavan käyttämällä yllä olevia funktioita. Vastaavuustoiminto palauttaa haun arvon1 indeksin rivin otsikkokenttään. Ja toinen MATCH -funktio palauttaa sarakeotsikkokentän hakuarvon 2 indeksin. Indeksinumerot syötetään nyt INDEX -funktioon, jotta taulukon tiedoista saadaan hakuarvon alla olevat arvot.

Yleinen kaava:

= INDEKSI (data, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))

tiedot : taulukon sisällä oleva taulukko ilman otsikoita

haun_arvo1 : rivin_otsikosta haettava arvo.

rivin_otsikot : Rivihakemiston taulukko haettavaksi.

haun_arvo1 : sarakkeen_otsikosta haettava arvo.

column_headers : sarake Hakemisto Hakutaulukko.

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 esimerkkejä tilaustiedoista ja meidän on löydettävä puuttuvat tiedot, jotka liittyvät annettuun arvoon taulukosta.

Täältä meidän on löydettävä tilaustiedot, kuten määrä, alue ja hinta 13.1.2019 tehdylle tilaukselle.

Käytä kaavaa:

= INDEKSI (A2: D11, MATCH (G3, A2: A11, 0), MATCH (F4, A1: D1, 0))

Selitys:

  1. MATCH -funktio palauttaa annetun 1D -taulukon (vaaka- tai pystysuora) sovitetun arvon indeksin.
  2. MATCH (F4, A1: D1,0) palauttaa indeksin 3.
  3. MATCH (G3, A2: A11,0) palauttaa 5 indeksinä
  4. INDEX -funktio palauttaa kolmannen sarakkeen viidennen elementin (alkaen 2. rivistä).

Kuten näette, 13.1.2019 tehtyyn tilaukseen liittyvä määrä on 38. Nyt löydämme kaavan avulla alueen, jolta tilaus tehtiin.

Käytä kaavaa:

= INDEKSI (A2: D11, MATCH (G3, A2: A11, 0), MATCH (F5, A1: D1, 0))

Kuten näette, tilaus tehtiin eteläiseltä alueelta. Hanki nyt tilaukseen liittyvä hinta

Käytä kaavaa:

= INDEKSI (A2: D11, MATCH (G3, A2: A11, 0), MATCH (F6, A1: D1, 0))

Kuten näette, hinta on 82,84. Huomasit, että poimimme kaikki yksityiskohdat yhdellä kaavalla. INDEX- ja MATCH -yhdistelmä on paras löytää yksi arvo taulukosta.

Tässä on kaikki huomautukset kaavan käytöstä.

Huomautuksia:

  1. Kaava toimii sekä tekstille että numeroille.
  2. Funktio palauttaa #NA -virheen, jos hakutoiminto -argumentti MATCH -funktiolle ei ole saman pituinen taulukkotaulukosta.
  3. Kaava palauttaa virheen, jos lookup_value ei vastaa taulukon look__ray arvoa.
  4. Funktio vastaa tarkkaa arvoa, koska hakutyypin argumentti MATCH -funktiolle on 0.
  5. Käytä argumenttia -1 pienemmälle kuin, 0 täsmälliselle haulle ja 1 suuremmalle kuin hakuhaku.
  6. Hakuarvot voidaan antaa soluviittauksena tai suoraan käyttämällä lainausmerkkiä (") kaavassa argumentteina.

Toivottavasti ymmärsit kuinka hakea arvo viiteverkosta indeksiarvojen mukaan Tallenna Exceliin. Tutustu muihin artikkeleihin Excel -haun arvosta ja Excel 2019 -toiminnoista täältä. Jos pidit blogistamme, 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.

Käytä INDEX- ja MATCH -toimintoja arvon etsimiseen : INDEX & MATCH -funktio arvon etsimiseen tarpeen mukaan.

SUM -alue ja INDEX Excelissä : Käytä INDEX -toimintoa löytääksesi tarvittavien arvojen SUMMA.

INDEX -toiminnon käyttäminen Excelissä : Etsi taulukon INDEX käyttämällä INDEX -funktiota, joka on selitetty esimerkillä.

MATCH -toiminnon käyttäminen Excelissä : Etsi MATCH taulukosta käyttämällä INDEX -arvoa MATCH -funktion sisällä esimerkin avulla.

LOOKUP -toiminnon käyttäminen Excelissä : Etsi hakuarvo taulukosta käyttämällä esimerkillä selitettyä LOOKUP -funktiota.

Suosittuja artikkeleita:

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.

VLOOKUP -toiminnon käyttäminen Excelissä : Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja taulukoilta.

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.