Kako pridobiti vrednost iz referenčne mreže glede na vrednosti indeksa Shrani v Excel

Anonim

V tem članku se bomo naučili, kako iz referenčne mreže pridobiti vrednost glede na vrednosti indeksa Shrani v Excelu

Scenarij:

Pri delu z dolgimi nerazpršenimi podatki moramo velikokrat najprej očistiti podatke, preden jih obdelamo. Potrebuje nekaj časa in želeli ste izvleči nekaj poizvedb. Za pridobivanje podatkov običajno uporabljate funkcijo VLOOKUP. Ko pa imamo dolge podatke s številnimi polji stolpcev, postane neurejeno, ker funkcija VLOOKUP zahteva pravilen indeks stolpca kot številko ali pa formula vrne napako. Spodaj je razlaga formule za tovrstne težave.

Kako rešiti problem?

Da bi formula najprej razumela, moramo nekoliko pregledati naslednje funkcije

  1. INDEX funkcija
  2. Funkcija MATCH

Zdaj bomo naredili formulo z uporabo zgornjih funkcij. Funkcija ujemanja bo vrnila indeks iskalne vrednosti1 v polju glave vrstice. Druga funkcija MATCH bo vrnila indeks iskalne vrednosti 2 v polje glave stolpca. Številke indeksov bodo zdaj vnesene v funkcijo INDEX, da dobimo vrednosti pod iskalno vrednostjo iz podatkov tabele.

Splošna formula:

= INDEX (podatki, MATCH (iskalna_vrednost1, vrstice_glavja, 0, MATCH (iskalna_vrednost2, stolpci_glavja, 0)))

podatkov : niz vrednosti znotraj tabele brez glav

lookup_value1 : vrednost za iskanje v naslovu row_header.

row_headers : Matrika vrstice indeksa za iskanje.

lookup_value1 : vrednost za iskanje v glavi stolpca.

stolpci_glavja : stolpec Indeksni niz za iskanje.

Primer:

Vse to je lahko zmedeno za razumevanje. Poglejmo, kako uporabiti funkcijo na primeru. Tukaj imamo nekaj vzorčnih podatkov o naročilu in v tabeli moramo poiskati manjkajoče podatke, povezane z dano vrednostjo.

Tu moramo poiskati podrobnosti naročila, kot so količina, regija in cena za naročilo, oddano 13.01.2019.

Uporabite formulo:

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

Pojasnilo:

  1. Funkcija MATCH vrne indeks ujemajoče se vrednosti v danem 1D nizu (vodoravno ali navpično).
  2. MATCH (F4, A1: D1,0) vrne 3 kot indeks.
  3. MATCH (G3, A2: A11,0) vrne 5 kot indeks
  4. Funkcija INDEX vrne 5. element (od 2. vrstice) v 3. stolpec.

Kot lahko vidite, je količina, povezana z naročilom, danim 13.01.2019, 38. Zdaj lahko po formuli najdemo regijo, iz katere je bilo naročilo oddano.

Uporabite formulo:

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

Kot lahko vidite, je bilo naročilo oddano iz južne regije. Zdaj dobite ceno, povezano z oddanim naročilom

Uporabite formulo:

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

Kot lahko vidite, je cena 82,84. Opazili ste, da smo vse podrobnosti izvlekli z eno formulo. Kombinacijo INDEX in MATCH je najbolje najti eno vrednost iz tabele.

Tu so vse opombe glede uporabe formule.

Opombe:

  1. Formula deluje tako za besedilo kot za številke.
  2. Funkcija vrne napako #NA, če argument matrike iskanja funkciji MATCH ni enake dolžine matrike tabel.
  3. Formula vrne napako, če se lookup_value ne ujema z vrednostjo v tabeli lookup_array.
  4. Funkcija se ujema z natančno vrednostjo, saj je argument vrste ujemanja s funkcijo MATCH 0.
  5. Argument -1 uporabite za manj kot 0, za natančno ujemanje in 1 za večje od ujemanja iskanja.
  6. Iskalne vrednosti so lahko podane kot referenca celice ali pa neposredno uporabljeni navednik (") v formuli kot argumenti.

Upam, da ste razumeli, kako pridobiti vrednost iz referenčne mreže glede na vrednosti indeksa Shrani v Excel. Raziščite več člankov o iskalni vrednosti Excel in funkcijah Excel 2019 tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.

Za iskanje vrednosti uporabite INDEX in MATCH : Funkcija INDEX & MATCH za iskanje vrednosti po potrebi.

Obseg SUM z INDEX v Excelu : S funkcijo INDEX poiščite SUM vrednosti, kot je potrebno.

Kako uporabljati funkcijo INDEX v Excelu : Poiščite INDEX matrike s funkcijo INDEX, razloženo s primerom.

Kako uporabljati funkcijo MATCH v Excelu : Poiščite MATCH v matriki z uporabo vrednosti INDEX znotraj funkcije MATCH, razložene s primerom.

Kako uporabljati funkcijo LOOKUP v Excelu : Poiščite iskalno vrednost v matriki s funkcijo LOOKUP, razloženo s primerom.

Priljubljeni članki:

Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.

Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.

Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.

Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.