Acest articol va vorbi despre cum să obțineți toate valorile potrivite dintr-un tabel și să le recuperați în diferite celule. Acest lucru este similar cu căutarea mai multor valori.
Formula generică
{= INDEX (nume, MICI (IF (grupuri = nume_grup, ROW (nume) -MIN (ROW (nume)) + 1), COLUMNE (intervale extinse))), „--List Ends-”)}
Prea multe funcții și variabile !!!. Să vedem care sunt aceste variabile.
Nume: Aceasta este lista numelor.
Grupuri: Lista grupului căruia îi aparțin și aceste nume.
Numele Grupului: referința numelui grupului.
Intervalele de extindere: acesta este intervalul în expansiune care este folosit pentru a obține un număr tot mai mare atunci când este copiat în dreapta.
Exemplu: extrageți numele angajaților în diferite coloane în funcție de compania lor.
Să presupunem că aveți un tabel de angajați care este grupat în funcție de compania lor. Prima coloană conține nume de angajați, iar a doua coloană conține numele companiei.
Acum trebuie să obținem numele fiecărui angajat în coloane diferite, în funcție de compania lor. Cu alte cuvinte, trebuie să le dezgrupăm.
Aici, am numit A2: A10 ca angajat și B2: B10 ca companie, astfel încât formula să fie ușor de citit.
Scrieți această formulă matrice în F2. Utilizați CTRL + SHIFT + ENTER pentru a introduce această formulă.
{= INDEX (Angajat, MIC (IF (Compania = E2 $, ROW (Angajat) -MIN (ROW (Angajat)) + 1), COLUMNE ($ E $ 1: E1))), „--Lista se încheie -” )}}
Copiați această formulă în toate celulele. Acesta va extrage fiecare nume individual în diferite coloane în funcție de grupul lor.
După cum puteți vedea în imaginea de mai sus, fiecare angajat este separat în celule diferite.
Deci, cum funcționează această formulă?
Pentru a înțelege formula, să analizăm formula din G2
Care este = IFERROR (INDEX (Angajat, MIC (DACĂ (Companie = $ E3,RÂND(Angajat) -MIN (ROW (Angajat)) + 1), COLUMNE ($ E $ 1: F2))), "- Lista se încheie -")
Mecanica este simplă și aproape la fel ca formula VLOOKUP multiplă. Trucul este să obțineți numărul de index al fiecărui angajat din diferite grupuri și să îl transmiteți la formula INDEX. Acest lucru se face prin această parte a formulei.
DACĂ(Companie=$ E3,RÂND(Angajat) -MIN (ROW (Angajat))+1):
Această parte returnează o serie de indexuri și false pentru numele companiei în $ E3, care conține „Rankwatch”.
{FALS; 2; FALS; 4; FALS; FALS; 7; FALS; 9}.
Cum? Să-l dărâmăm din interior.
Aici potrivim numele companiei în $ E3 cu fiecare valoare în Gama companiei (Compania = E3 $).
Aceasta returnează o serie de adevărate și false. {FALSE;ADEVĂRAT;FALS;ADEVĂRAT; FALS; FALS;ADEVĂRAT;FALS;ADEVĂRAT}.
Acum funcția IF rulează instrucțiunile TRUE pentru TRUE, care este ROW (Angajat) -MIN (ROW (Angajat)) + 1. Această parte returnează această parte returnează o serie de indici începând de la 1 la numărul de angajați {1; 2; 3; 4; 5; 6; 7; 8; 9}. Funcția if afișează valorile doar pentru ADEVĂRAT care, la rândul nostru, ne dă {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Formula curentă este simplificată la
= IFERROR (INDEX (Angajat, MIC ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},COLOANE ($ E $ 1: F2))), "- Lista se încheie -"). După cum știm, funcția mică returnează cea de-a noua cea mai mică valoare dintr-o matrice. COLOANE ($ E $ 1: F2) aceasta returnează 2. Funcția SMALL returnează a doua cea mai mică valoare din matricea de mai sus, care este 4.
Acum formula este simplificată = IFERROR (INDEX (Angajat, 4), "- Lista se încheie -"). Acum, funcția INDEX returnează pur și simplu al patrulea nume de la angajat matrice care ne oferă „Sam”.
Deci da, așa se extrag nume din grupuri din diferite coloane folosind funcția INDEX, SMALL, ROW, COLUMNS și IF. Dacă aveți vreo îndoială cu privire la această funcție sau dacă nu funcționează pentru dvs., spuneți-mi secțiunea de comentarii de mai jos.
Descărcare fișier:
Articole similare:
VLOOKUP Valori multiple
Utilizați INDEX și MATCH pentru a căuta valoarea
Căutați valoarea cu mai multe criterii
Articole populare:
Funcția VLOOKUP în Excel
COUNTIF în Excel 2016
Cum se utilizează funcția SUMIF în Excel