Ta članek bo govoril o tem, kako dobiti vse vrednosti iz tabele in jih pridobiti v različnih celicah. To je podobno iskanju več vrednosti.
Splošna formula
{= INDEX (imena, SMALL (IF (skupine = ime_skupine, ROW (imena) -MIN (ROW (imena))+1), COLUMNS (širjenje obsegov)))), “--List se konča--”)}
Preveč funkcij in spremenljivk !!!. Poglejmo, kaj so te spremenljivke.
Imena: To je seznam imen.
Skupine: Seznam skupin, ki jim pripadajo tudi ta imena.
Ime_skupine: sklic na ime skupine.
Razširitev obsegov: to je razširjeni obseg, ki se uporablja za pridobivanje vse večjega števila pri kopiranju v desno.
Primer: Izvlecite imena zaposlenih v različne stolpce glede na njihovo podjetje.
Recimo, da imate tabelo zaposlenih, ki je razvrščena glede na njihovo podjetje. Prvi stolpec vsebuje imena zaposlenih, drugi stolpec pa ime podjetja.
Zdaj moramo vnesti ime vsakega zaposlenega v različne stolpce glede na njihovo podjetje. Z drugimi besedami, moramo jih razgraditi.
Tu sem poimenoval A2: A10 kot zaposlenega in B2: B10 kot podjetje, zato je to formulo lahko berljivo.
To formulo matrike zapišite v F2. Za vnos te formule uporabite CTRL+SHIFT+ENTER.
{= INDEX (Zaposleni, MALO (ČE (Podjetje = $ E2, ROW (Zaposleni) -MIN (ROW (Zaposleni)))+1), STOLPCI ($ E $ 1: E1))), “--List se konča--” )}
Kopirajte to formulo v vse celice. Izvleklo bo vsako posamezno ime v različne stolpce glede na njihovo skupino.
Kot lahko vidite na zgornji sliki, je vsak zaposleni ločen v različnih celicah.
Torej, kako deluje ta formula?
Za razumevanje formule poglejmo formulo v G2
Kar je = IFERROR (INDEX (Zaposleni, MALO (ČE (Podjetje = E3 USD, ROW (Zaposleni) -MIN (ROW (Zaposleni))+1), STOLPCI ($ E $ 1: F2))), "-Seznam se konča-")
Mehanika je preprosta in skoraj enaka več formuli VLOOKUP. Trik je v tem, da dobite indeksno številko vsakega zaposlenega iz različnih skupin in jo posredujete formuli INDEX. To se naredi s tem delom formule.
IF (Podjetje=E3 USD, ROW (Zaposleni) -MIN (ROW (Zaposleni))+1):
Ta del vrne niz indeksov in false za ime podjetja v $ E3, ki vsebuje "Rankwatch".
{FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Kako? Zrušimo ga od znotraj.
Tu se ime podjetja v E3 ujema z vsako vrednostjo v Obseg podjetja (Podjetje = E3 USD).
To vrne polje true in false. {FALSE;PRAV; FALSE;PRAV; FALSE; FALSE;PRAV; FALSE;PRAV}.
Zdaj funkcija IF izvaja izjave TRUE za TRUE, kar je ROW (zaposleni) -MIN (ROW (Zaposleni))+1. Ta del vrne ta del vrne niz indeksov, ki se začnejo od 1 do števila zaposleni {1; 2; 3; 4; 5; 6; 7; 8; 9}. Funkcija if prikaže vrednosti samo za TRUE, kar nam daje {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Sedanja formula je poenostavljena na
= IFERROR (INDEX (Zaposleni, SMALL ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},STOLPCI ($ E $ 1: F2))), "-Seznam se konča-"). Kot vemo, majhna funkcija vrne n -to najmanjšo vrednost iz matrike. STOLPCI ($ E $ 1: F2) to vrne 2. Funkcija SMALL vrne drugo najmanjšo vrednost iz zgornjega niza, to je 4.
Zdaj je formula poenostavljena = IFERROR (INDEX (zaposleni, 4), "-Seznam se konča-"). Zdaj funkcija INDEX preprosto vrne četrto ime iz zaposlenega matrika, ki nam daje "Sam”.
Torej, ja, tako izvlečemo imena iz skupin v različnih stolpcih s funkcijami INDEX, SMALL, ROW, COLUMNS in IF. Če dvomite o tej funkciji ali če vam ne deluje, mi sporočite spodnji razdelek s komentarji.
Prenesite datoteko:
Povezani članki:
VLOOKUP Več vrednosti
Za iskanje vrednosti uporabite INDEX in MATCH
Iskalna vrednost z več merili
Priljubljeni članki:
Funkcija VLOOKUP v Excelu
COUNTIF v Excelu 2016
Kako uporabljati funkcijo SUMIF v Excelu