Šajā rakstā tiks runāts par to, kā no tabulas iegūt visas vērtības un izgūt tās dažādās šūnās. Tas ir līdzīgi vairāku vērtību meklēšanai.
Vispārīgā formula
{= INDEKSS (nosaukumi, MAZI (JA (grupas = grupas_nosaukums, Rinda (nosaukumi) -MIN (RIJA (nosaukumi))+1), SLEJAS (paplašināmie diapazoni))), “--Lista beigas-”)}
Pārāk daudz funkciju un mainīgo !!!. Apskatīsim, kādi ir šie mainīgie.
Vārdi: Šis ir vārdu saraksts.
Grupas: To grupu saraksts, kurām pieder arī šie vārdi.
Grupas nosaukums: atsauce uz grupas nosaukumu.
Diapazonu paplašināšana: tas ir paplašināms diapazons, ko izmanto, lai iegūtu lielāku skaitu, nokopējot to pa labi.
Piemērs. Izņemiet darbinieku vārdus dažādās slejās atbilstoši viņu uzņēmumam.
Pieņemsim, ka jums ir darbinieku tabula, kas ir sagrupēta atbilstoši viņu uzņēmumam. Pirmajā slejā ir darbinieku vārdi, bet otrajā slejā - uzņēmuma nosaukums.
Tagad mums ir jāiegūst katra darbinieka vārds dažādās slejās atbilstoši viņu uzņēmumam. Citiem vārdiem sakot, mums tie ir jāatgrupē.
Šeit es esmu nosaucis A2: A10 kā darbinieku un B2: B10 kā uzņēmumu, lai formula būtu viegli lasāma.
Ierakstiet šo masīva formulu F2. Izmantojiet CTRL+SHIFT+ENTER, lai ievadītu šo formulu.
{= INDEKSS (darbinieks, SMALL (IF (uzņēmums = $ E2, ROW (darbinieks)) -MIN (ROW (darbinieks))+1), SLEJAS ($ E $ 1: E1))), “-saraksta beigas-” )}
Kopējiet šo formulu visās šūnās. Tas iegūs katru atsevišķo vārdu dažādās kolonnās atbilstoši viņu grupai.
Kā redzat attēlā iepriekš, katrs darbinieks ir nošķirts dažādās šūnās.
Tātad, kā šī formula darbojas?
Lai saprastu formulu, apskatīsim formulu G2
Kas ir = IFERROR (INDEX (Darbinieks, SMALL (IF (Uzņēmums = $ E3, ROW (Darbinieks) -MIN (ROW (Darbinieks))+1), SLEJAS ($ E $ 1: F2))), "-Saraksta beigas-")
Mehānika ir vienkārša un gandrīz tāda pati kā vairāku VLOOKUP formulu. Triks ir iegūt katra darbinieka indeksa numuru no dažādām grupām un nodot to indeksam INDEX. To veic ar šo formulas daļu.
IF (Uzņēmums=$ E3, ROW (Darbinieks) -MIN (ROW (Darbinieks))+1):
Šī daļa atgriež indeksu masīvu un nepatiesu uzņēmuma nosaukumam $ E3, kas satur “Rankwatch”.
{FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}).
Kā? Izjauksim to no iekšpuses.
Šeit mēs saskaņojam uzņēmuma nosaukumu $ E3 ar katru vērtību Uzņēmuma diapazons (uzņēmums = $ E3).
Tas atgriež patiesu un nepatiesu masīvu. {FALSE;PATIESA; FALSE;PATIESA; FALSE; FALSE;PATIESA; FALSE;PATIESA}.
Tagad funkcija IF palaiž TRUE paziņojumus TRUE, kas ir ROW (Employee) -MIN (ROW (Darbinieks))+1. Šī daļa atgriež šo daļu atgriež indeksu masīvu, sākot no 1 līdz skaitlim darbinieki {1; 2; 3; 4; 5; 6; 7; 8; 9}. Funkcija if attēlo vērtības tikai TRUE, kas savukārt dod mums {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Pašreizējā formula ir vienkāršota līdz
= IFERROR (INDEX (Darbinieks, MAZS ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},SLEJAS ($ E $ 1: F2))), "-saraksta beigas-"). Kā mēs zinām, mazā funkcija atgriež n -to mazāko vērtību no masīva. SLEJAS ($ E $ 1: F2) Funkcija SMALL atgriež otro mazāko vērtību no iepriekš minētā masīva, kas ir 4.
Tagad formula ir vienkāršota = IFERROR (INDEX (darbinieks, 4), "-saraksta beigas-"). Tagad funkcija INDEX vienkārši atgriež ceturto nosaukumu no darbinieks masīvs, kas mums dod "Sems”.
Jā, šādā veidā iegūstiet nosaukumus no grupām dažādās kolonnās, izmantojot funkciju INDEX, SMALL, ROW, COLUMNS un IF. Ja jums ir šaubas par šo funkciju vai ja tā jums nedarbojas, dariet man zināmu zemāk esošo komentāru sadaļu.
Lejupielādēt failu:
Saistītie raksti:
VLOOKUP vairākas vērtības
Izmantojiet INDEX un MATCH, lai atrastu vērtību
Uzmeklēšanas vērtība ar vairākiem kritērijiem
Populāri raksti:
Funkcija VLOOKUP programmā Excel
COUNTIF programmā Excel 2016
Kā lietot SUMIF funkciju programmā Excel