Cum să extrageți o valoare dintr-o grilă de referință în funcție de valorile indexului Salvați în Excel

Anonim

În acest articol, vom afla cum să extrageți o valoare dintr-o grilă de referință în funcție de valorile indexului Salvați în Excel

Scenariu :

De multe ori, atunci când lucrăm cu date nedistribuite, trebuie mai întâi să curățăm datele înainte de a lucra la ele. Este nevoie de timp și ai vrut doar să extragi câteva întrebări. Pentru a extrage date, utilizați în general funcția VLOOKUP. Dar când avem date lungi cu multe câmpuri de coloane, atunci devine dezordonat, deoarece funcția VLOOKUP necesită indexul coloanei corecte ca număr, altfel formula returnează eroare. Chiar acolo, mai jos este explicația formulă pentru acest tip de problemă.

Cum se rezolvă problema?

Pentru ca formula să înțeleagă mai întâi, trebuie să revizuim puțin despre următoarele funcții

  1. Funcția INDEX
  2. Funcția MATCH

Acum vom face o formulă folosind funcțiile de mai sus. Funcția de potrivire va returna indexul valorii de căutare1 în câmpul antetului rândului. Și o altă funcție MATCH va returna indexul valorii de căutare 2 în câmpul antet coloană. Numerele index vor fi acum introduse în funcția INDEX pentru a obține valorile sub valoarea de căutare din datele tabelului.

Formula generică:

= INDEX (date, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))

date : matrice de valori în interiorul tabelului fără anteturi

lookup_value1 : valoare de căutat în rândul_antet.

head_headers : Matrice de indexuri de rânduri pentru căutare.

lookup_value1 : valoare de căutat în header_coloană.

header_coloane : coloană Index matrice pentru căutare.

Exemplu:

Toate acestea ar putea fi confuze pentru a înțelege. Să înțelegem cum să folosim funcția folosind un exemplu. Aici avem câteva exemple de date de comandă și trebuie să găsim în tabel datele lipsă legate de valoarea dată.

Aici trebuie să găsim detaliile comenzii precum Cantitatea, regiunea și prețul pentru comanda plasată în data de 01.01.2019.

Folosiți formula:

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

Explicaţie:

  1. Funcția MATCH returnează indexul valorii potrivite în matricea 1D dată (orizontală sau verticală).
  2. MATCH (F4, A1: D1,0) returnează 3 ca index.
  3. MATCH (G3, A2: A11,0) returnează 5 ca Index
  4. Funcția INDEX va returna cel de-al 5-lea element (începând de la rândul 2) în coloana a 3-a.

După cum puteți vedea, cantitatea legată de comanda plasată la 13-01-2019 se dovedește a fi 38. Acum putem găsi regiunea din care a fost plasată comanda folosind formula.

Folosiți formula:

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

După cum puteți vedea, comanda a fost plasată din regiunea Sud. Acum obțineți prețul aferent comenzii plasate

Folosiți formula:

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

După cum puteți vedea, prețul se ridică la 82,84. Ați observat că am extras toate detaliile folosind o singură formulă. Combo-ul INDEX și MATCH este cel mai bun pentru a găsi o valoare din tabel.

Iată toate notele observaționale privind utilizarea formulei.

Note:

  1. Formula funcționează atât pentru text cât și pentru numere.
  2. Funcția returnează eroarea #NA dacă argumentul matricei de căutare la funcția MATCH nu are aceeași lungime a matricei de tabel.
  3. Formula returnează o eroare dacă lookup_value nu se potrivește cu valoarea din tabelul lookup_array.
  4. Funcția se potrivește cu valoarea exactă, deoarece argumentul tipului de potrivire cu funcția MATCH este 0.
  5. Folosiți argumentul -1 pentru mai puțin de, 0 pentru potrivirea exactă și 1 pentru cea mai mare decât potrivirea de căutare.
  6. Valorile de căutare pot fi date ca referință de celulă sau direct folosind simbolul ghilimelei (") în formulă ca argumente.

Sper că ați înțeles Cum să extrageți o valoare dintr-o grilă de referință în funcție de valorile indexului Salvați în Excel. Explorați aici mai multe articole despre valoarea de căutare Excel și funcțiile Excel 2019. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail.

Utilizați INDEX și MATCH pentru a căuta valoarea : Funcția INDEX & MATCH pentru a căuta valoarea după cum este necesar.

Gama SUMĂ cu INDEX în Excel : Utilizați funcția INDEX pentru a găsi SUMA valorilor, după cum este necesar.

Cum se utilizează funcția INDEX în Excel : Găsiți INDEX-ul unui tablou folosind funcția INDEX explicată cu un exemplu.

Cum se utilizează funcția MATCH în Excel : Găsiți MATCH-ul în matrice folosind valoarea INDEX din funcția MATCH explicată cu un exemplu.

Cum se utilizează funcția de căutare în Excel : Găsiți valoarea de căutare în matrice folosind funcția LOOKUP explicată cu un exemplu.

Articole populare:

Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.

Cum se utilizează funcția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.

Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.

Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.