Šiame straipsnyje bus kalbama apie tai, kaip iš lentelės suderinti visas vertes ir gauti jas skirtingose ląstelėse. Tai panašu į kelių verčių paiešką.
Bendra formulė
{= INDEX (vardai, SMALL (IF (grupės = grupės_pavadinimas, ROW (pavadinimai)) -MIN (ROW (vardai))+1), STulpeliai (plečiami diapazonai))), „-Sąrašo pabaiga-“)}
Per daug funkcijų ir kintamųjų !!!. Pažiūrėkime, kokie yra šie kintamieji.
Vardai: Tai yra vardų sąrašas.
Grupės: Grupės, kuriai priklauso šie vardai, sąrašas.
Grupės pavadinimas: grupės pavadinimo nuoroda.
Diapazonų išplėtimas: tai yra besiplečiantis diapazonas, naudojamas kopijuojant į dešinę gauti vis didesnį skaičių.
Pavyzdys: išskirkite darbuotojų vardus skirtinguose stulpeliuose pagal jų įmonę.
Tarkime, kad turite lentelę darbuotojų, sugrupuotų pagal jų įmonę. Pirmame stulpelyje yra darbuotojų vardai, o antrame - įmonės pavadinimas.
Dabar turime gauti kiekvieno darbuotojo vardą skirtinguose stulpeliuose pagal jų įmonę. Kitaip tariant, turime juos išgrupuoti.
Čia aš pavadinau A2: A10 kaip darbuotoją ir B2: B10 kaip įmonę, kad formulė būtų lengvai skaitoma.
Parašykite šią masyvo formulę F2. Norėdami įvesti šią formulę, naudokite CTRL+SHIFT+ENTER.
{= INDEX (darbuotojas, SMALL (IF (Company = $ E2, ROW (Employee)) -MIN (ROW (Employee))+1), COLUMNS ($ E $ 1: E1))), „-Sąrašo pabaiga-“ )}
Nukopijuokite šią formulę į visus langelius. Jis išskleis kiekvieną atskirą pavadinimą skirtinguose stulpeliuose pagal jų grupę.
Kaip matote aukščiau esančiame paveikslėlyje, kiekvienas darbuotojas yra atskirtas skirtingose ląstelėse.
Taigi, kaip veikia ši formulė?
Norėdami suprasti formulę, pažvelkime į formulę G2
Kas yra = IFERROR (INDEX (Darbuotojas, SMALL (IF (Įmonė = $ E3, ROW (Darbuotojas) -MIN (ROW (Darbuotojas))+1), STulpeliai ($ E $ 1: F2))), "-Sąrašo pabaiga-")
Mechanika yra paprasta ir beveik tokia pati kaip kelių VLOOKUP formulių. Triukas yra gauti kiekvieno darbuotojo indekso numerį iš skirtingų grupių ir perduoti jį INDEX formulei. Tai atliekama naudojant šią formulės dalį.
IF (Bendrovė=E3 USD, ROW (Darbuotojas) -MIN (ROW (Darbuotojas))+1):
Šioje dalyje pateikiamas įmonės pavadinimo indeksų ir klaidingų duomenų masyvas E3 USD, kuriame yra „Rankwatch“.
{FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Kaip? Išardykime jį iš vidaus.
Čia mes suderiname įmonės pavadinimą E3 USD su kiekviena reikšme Įmonės diapazonas (Įmonė = $ E3).
Pateikiama teisingų ir klaidingų matricų. {FALSE;TIESA; NETIESA;TIESA; FALSE; FALSE;TIESA; NETIESA;TIESA}.
Dabar IF funkcija vykdo TRUE teiginius TRUE, tai yra ROW (Employee) -MIN (ROW (Darbuotojas))+1. Ši dalis grąžina šią dalį grąžina indeksų masyvą, pradedant nuo 1 iki skaičiaus darbuotojų {1; 2; 3; 4; 5; 6; 7; 8; 9}. Funkcija „if“ parodo tik TRUE reikšmes, o tai savo ruožtu suteikia mums {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Dabartinė formulė supaprastinta iki
= IFERROR (INDEX (darbuotojas, SMALL ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},STulpeliai ($ E $ 1: F2))), "-sąrašo pabaiga-"). Kaip žinome, maža funkcija grąžina n -ąją mažiausią reikšmę iš masyvo. SKILTYS ($ E $ 1: F2) tai grąžina 2. Funkcija SMALL grąžina antrąją mažiausią reikšmę iš aukščiau esančio masyvo, ty 4.
Dabar formulė supaprastinta = IFERROR (INDEX (darbuotojas, 4), "-sąrašo pabaiga-"). Dabar funkcija INDEX tiesiog grąžina ketvirtąjį pavadinimą darbuotojas masyvas, kuris mums suteikia "Semas”.
Taip, taip išskirkite pavadinimus iš grupių skirtinguose stulpeliuose, naudodami funkciją INDEX, SMALL, ROW, COLUMNS ir IF. Jei turite kokių nors abejonių dėl šios funkcijos arba ji jums netinka, praneškite man toliau pateiktą komentarų skiltį.
Atsisiųsti failą:
Susiję straipsniai:
VLOOKUP Kelios vertės
Norėdami ieškoti vertės, naudokite INDEX ir MATCH
Paieškos vertė su keliais kriterijais
Populiarūs straipsniai:
„VLOOKUP“ funkcija „Excel“
COUNTIF „Excel 2016“
Kaip naudoti „SUMIF“ funkciją „Excel“