Funcția Excel INDEX - Exemple de formule + Video GRATUIT

Funcția Excel INDEX (Exemple + Video)

Când utilizați funcția Excel INDEX

Funcția Excel INDEX poate fi utilizată atunci când doriți să preluați valoarea dintr-o dată tabelară și aveți numărul rândului și numărul coloanei punctului de date. De exemplu, în exemplul de mai jos, puteți utiliza funcția INDEX pentru a obține semnele „Tom” în Fizică atunci când cunoașteți numărul rândului și numărul coloanei din setul de date.

Ce returnează

Returnează valoarea dintr-un tabel pentru numărul rândului și numărul coloanei specificate.

Sintaxă

= INDEX (matrice, rând_num, [col_num])
= INDEX (matrice, rând_num, [col_num], [zonă_num])

Funcția INDEX are 2 sintaxi. Prima este utilizată în majoritatea cazurilor, totuși, în cazul căutărilor în trei direcții, se folosește a doua (prezentată în Exemplul 5).

Argumente de intrare

• matrice - A gama de celule sau o constantă de matrice.
• rând_num - numărul rândului din care urmează să fie preluată valoarea.
• [col_num] - numărul coloanei din care urmează să fie preluată valoarea. Deși acesta este un argument opțional, dar dacă rândul_num nu este furnizat, trebuie dat.
• [area_num] - (Opțional) Dacă argumentul matricei este alcătuit din mai multe intervale, acest număr va fi utilizat pentru a selecta referința din toate intervalele.

Note suplimentare (Lucruri plictisitoare … Dar important de știut)

• Dacă numărul rândului sau numărul coloanei este 0, acesta returnează valorile întregului rând sau, respectiv, al coloanei.
• Dacă funcția INDEX este utilizată în fața unei referințe de celulă (cum ar fi A1 :) returnează o referință de celulă în loc de o valoare (a se vedea exemplele de mai jos).
• Cel mai utilizat pe scară largă împreună cu funcția MATCH.
• Spre deosebire de VLOOKUP, funcția INDEX poate returna o valoare din stânga valorii de căutare.
• Funcția INDEX are două forme - Formular de matrice și Formular de referință
  • „Formular de matrice” este locul în care preluați o valoare bazată pe numărul de rând și coloană dintr-un tabel dat.
  • „Formular de referință” este locul în care există mai multe tabele și folosiți argumentul area_num pentru a selecta tabelul și apoi a prelua o valoare în cadrul acestuia folosind numărul rândului și al coloanei (a se vedea exemplul live de mai jos).

Funcția Excel INDEX - Exemple

Iată șase exemple de utilizare a funcției Excel INDEX.

Exemplul 1 - Găsirea mărcilor lui Tom în fizică (o căutare în două direcții)

Să presupunem că aveți un set de date așa cum se arată mai jos:

Pentru a găsi notele lui Tom în Fizică, utilizați formula de mai jos:

= INDEX ($ B $ 3: $ E $ 10,3,2)

Această formulă INDEX specifică matricea ca $ B $ 3: $ E $ 10 care are notele pentru toți subiecții. Apoi folosește numărul rândului (3) și numărul coloanei (2) pentru a prelua semnele lui Tom în fizică.

Exemplul 2 - Realizarea dinamicii valorii LOOKUP folosind funcția MATCH

Este posibil să nu fie întotdeauna posibil să specificați manual numărul rândului și numărul coloanei. Este posibil să aveți un set de date uriaș sau doriți să îl faceți dinamic, astfel încât să identifice automat numele și / sau subiectul specificat în celule și să dea rezultatul corect.

Ceva așa cum se arată mai jos:

Acest lucru se poate face folosind o combinație a funcției INDEX și MATCH.

Iată formula care va face valorile de căutare dinamice:

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

În formula de mai sus, în loc să codificăm numărul rândului și numărul coloanei, funcția MATCH este utilizată pentru a-l face dinamic.

• Numărul de rând dinamic este dat de următoarea parte a formulei - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Scanează numele studenților și identifică valoarea de căutare ($ G $ 5 în acest caz). Apoi returnează numărul rândului valorii de căutare din setul de date. De exemplu, dacă valoarea de căutare este Matt, va returna 1, dacă este Bob, va returna 2 și așa mai departe.
• Numărul de coloană dinamic este dat de următoarea parte a formulei - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Scanează numele subiectului și identifică valoarea de căutare ($ H $ 4 în acest caz). Apoi returnează numărul coloanei valorii de căutare din setul de date. De exemplu, dacă valoarea de căutare este Matematică, va returna 1, dacă este Fizică, va returna 2 și așa mai departe.

Exemplul 3 - Utilizarea listelor drop-down ca valori de căutare

În exemplul de mai sus, trebuie să introducem manual datele. Acest lucru ar putea fi consumator de timp și predispus la erori, mai ales dacă aveți o listă imensă de valori de căutare.

O idee bună în astfel de cazuri este să creați o listă verticală a valorilor căutării (în acest caz, ar putea fi nume și subiecte ale studenților) și apoi să alegeți pur și simplu din listă. Pe baza selecției, formula va actualiza automat rezultatul.

Ceva așa cum se arată mai jos:

Acest lucru face o componentă bună a tabloului de bord, deoarece puteți avea un set de date uriaș cu sute de studenți în partea din spate, dar utilizatorul final (să spunem un profesor) poate obține rapid notele unui student într-o disciplină prin simpla selecție din derulantul.

Cum se realizează acest lucru:

Formula utilizată în acest caz este aceeași folosită în exemplul 2.

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

Valorile de căutare au fost convertite în liste derulante.

Iată pașii pentru a crea lista verticală Excel:

• Selectați celula în care doriți lista derulantă. În acest exemplu, în G4, vrem numele studenților.
• Accesați Date -> Instrumente de date -> Validare date.
• În caseta Dialog validare date, în fila Setări, selectați Listă din meniul derulant Permiteți.
• În sursă, selectați $ A $ 3: $ A $ 10
• Faceți clic pe OK.

Acum veți avea lista derulantă în celula G5. În mod similar, puteți crea unul în H4 pentru subiecți.

Exemplul 4 - Valori returnate dintr-un întreg rând / coloană

În exemplele de mai sus, am folosit funcția Excel INDEX pentru a efectua o căutare bidirecțională și pentru a obține o singură valoare.

Acum, ce se întâmplă dacă doriți să obțineți toate notele unui student. Acest lucru vă poate permite să găsiți scorul maxim / minim al elevului respectiv sau notele totale obținute la toate disciplinele.

În limba engleză simplă, doriți să obțineți mai întâi întregul rând de scoruri pentru un student (să spunem Bob) și apoi, în acele valori, identificați cel mai mare scor sau totalul tuturor scorurilor.

Iată trucul.

În funcția Excel INDEX, când introduceți fișierul numărul coloanei ca 0, va returna valorile întregului rând.

Deci formula pentru aceasta ar fi:

= INDEX ($ B $ 3: $ E $ 10 $, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)

Acum această formulă. dacă este folosit ca atare, ar returna #VALUE! eroare. În timp ce afișează eroarea, în backend, returnează o matrice care are toate scorurile pentru Tom - {57,77,91,91}.

Dacă selectați formula în modul de editare și apăsați F9, veți putea vedea matricea pe care o returnează (așa cum se arată mai jos):

În mod similar, pe baza valorii căutării, atunci când numărul coloanei este specificat ca 0 (sau este lăsat necompletat), returnează toate valorile din rând pentru valoarea căutării

Acum, pentru a calcula scorul total obținut de Tom, putem folosi pur și simplu formula de mai sus în cadrul funcției SUM.

= SUMĂ (INDEX ($ B $ 3: $ E $ 10 $, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))

Pe linii similare, pentru a calcula cel mai mare scor, putem folosi MAX / LARGE și pentru a calcula minim, putem folosi MIN / SMALL.

Exemplul 5 - Căutare în trei direcții folosind INDEX / MATCH

Funcția Excel INDEX este construită pentru a gestiona căutările în trei direcții.

Ce este o căutare în trei direcții?

În exemplele de mai sus, am folosit un tabel cu scoruri pentru studenți la diferite discipline. Acesta este un exemplu de căutare bidirecțională, deoarece folosim două variabile pentru a obține scorul (numele elevului și subiectul).

Acum, să presupunem că într-un an, un student are trei niveluri diferite de examene, testul de unitate, jumătatea perioadei și examenul final (asta am avut când eram student).

O căutare în trei direcții ar fi capacitatea de a obține notele unui student pentru un subiect specificat de la nivelul de examen specificat. Aceasta ar face o căutare în trei direcții, deoarece există trei variabile (numele studentului, numele subiectului și nivelul de examinare).

Iată un exemplu de căutare în trei direcții:

În exemplul de mai sus, în afară de selectarea numelui și a subiectului elevului, puteți selecta și nivelul examenului. Pe baza nivelului de examen, returnează valoarea potrivită de la unul dintre cele trei tabele.

Iată formula utilizată în celula H4:

= INDEX (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0), MATCH ($ H 3 $, $ B $ 2: $ E $ 2,0), IF ($ H $ 2 = "Test de unitate", 1, IF ($ H $ 2 = "Midterm", 2,3)))

Să descompunem această formulă pentru a înțelege cum funcționează.

Această formulă ia patru argumente. INDEX este una dintre acele funcții din Excel care are mai mult de o sintaxă.

= INDEX (matrice, rând_num, [col_num])
= INDEX (matrice, rând_num, [col_num], [zonă_num])

Până acum, în tot exemplul de mai sus, am folosit prima sintaxă, dar pentru a efectua o căutare în trei direcții, trebuie să folosim a doua sintaxă.

Acum să vedem fiecare parte a formulei bazată pe a doua sintaxă.

• matrice - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): În loc să folosim o singură matrice, în acest caz, am folosit trei matrice între paranteze.
• row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): funcția MATCH este utilizată pentru a găsi poziția numelui studentului în celula $ G $ 4 din lista numelui studentului.
• col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): funcția MATCH este utilizată pentru a găsi poziția numelui subiectului în celula $ H $ 3 din lista numelui subiectului.
• [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): Valoarea numărului de zonă indică funcției INDEX ce matrice să selecteze. În acest exemplu, avem trei matrici în primul argument. Dacă selectați Unit Test din meniul derulant, funcția IF returnează 1 și funcțiile INDEX selectează prima matrice din cele trei tablouri (care este $ B $ 3: $ E $ 7 $).

Exemplul 6 - Crearea unei referințe utilizând funcția INDEX (domenii dinamice numite)

Aceasta este o utilizare sălbatică a funcției Excel INDEX.

Să luăm un exemplu simplu.

Am o listă de nume așa cum se arată mai jos:

Acum pot folosi o funcție simplă INDEX pentru a obține numele de familie din listă.

Iată formula:

= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

Această funcție numără pur și simplu numărul de celule care nu sunt goale și returnează ultimul element din această listă (funcționează numai atunci când nu există spații goale în listă).

Acum, ceea ce aici vine magia.

Dacă puneți formula în fața unei referințe de celulă, formula va returna o referință de celulă cu valoarea potrivită (în locul valorii în sine).

= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

Vă așteptați ca formula de mai sus să revină = A2: „Josh” (unde Josh este ultima valoare din listă). Cu toate acestea, returnează = A2: A9 și, prin urmare, veți obține o serie de nume așa cum se arată mai jos:

Un exemplu practic în care această tehnică poate fi utilă este crearea unor intervale denumite dinamice.

Asta este în acest tutorial. Am încercat să abordez exemple majore de utilizare a funcției Excel INDEX. Dacă doriți să vedeți mai multe exemple adăugate la această listă, anunțați-mă în secțiunea de comentarii.

Notă: Am încercat din răsputeri să citesc acest tutorial, dar în cazul în care găsiți erori sau greșeli de ortografie, vă rugăm să ne anunțați 🙂

Funcția Excel INDEX - Tutorial video

• Funcția Excel VLOOKUP.
• Funcția Excel HLOOKUP.
• Funcția Excel INDIRECT.
• Funcția Excel MATCH.
• Funcția Excel OFFSET.

Vă pot plăcea, de asemenea, următoarele tutoriale Excel:

• VLOOKUP vs. INDICE / MATCH
• Potrivire index Excel
• Căutați și returnați valorile într-un întreg rând / coloană.