Funkcija Excel INDEX (piemēri + video)
Kad izmantot Excel INDEX funkciju
Funkciju Excel INDEX var izmantot, ja vēlaties iegūt vērtību no tabulas datiem un jums ir datu punkta rindas numurs un kolonnas numurs. Piemēram, zemāk redzamajā piemērā jūs varat izmantot funkciju INDEX, lai fizikā iegūtu “Tom” atzīmes, ja zināt datu kopas rindas un kolonnas numuru.
Ko tas atgriež
Tas atgriež tabulas vērtību norādītajam rindas numuram un kolonnas numuram.
Sintakse
= INDEKSS (masīvs, rindas_numurs, [kolonnas_numurs])
= INDEKSS (masīvs, rindas_numurs, [kolonnas_numurs], [apgabala_numurs])
Funkcijai INDEX ir 2 sintakse. Pirmais tiek izmantots lielākajā daļā gadījumu, tomēr trīsvirzienu uzmeklēšanas gadījumā tiek izmantots otrais (apskatīts 5. piemērā).
Ievades argumenti
- masīvs - a šūnu diapazons vai masīva konstante.
- rindas_numurs - rindas numurs, no kura jāiegūst vērtība.
- [kolonnas_numurs] - kolonnas numurs, no kura jāiegūst vērtība. Lai gan tas nav fakultatīvs arguments, bet, ja rindas_numurs nav norādīts, tas ir jānorāda.
- [apgabala_numurs] - (Neobligāti) Ja masīva argumentu veido vairāki diapazoni, šis skaitlis tiktu izmantots, lai atlasītu atsauci no visiem diapazoniem.
Papildu piezīmes (garlaicīgas lietas… bet svarīgi zināt)
- Ja rindas vai kolonnas numurs ir 0, tas atgriež attiecīgi visas rindas vai kolonnas vērtības.
- Ja funkcija INDEX tiek izmantota šūnas atsauces priekšā (piemēram, A1 :), tā atgriež šūnas atsauci vērtības vietā (skatiet piemērus zemāk).
- Visbiežāk tiek izmantots kopā ar funkciju MATCH.
- Atšķirībā no VLOOKUP, funkcija INDEX var atgriezt vērtību no meklēšanas vērtības kreisās puses.
- Funkcijai INDEX ir divas formas - masīva forma un atsauces forma
- “Masīva forma” ir vieta, kur jūs iegūstat vērtību, pamatojoties uz konkrētās tabulas rindu un kolonnu skaitu.
- “Atsauces veidlapa” ir vieta, kur ir vairākas tabulas, un jūs izmantojat argumentu area_num, lai atlasītu tabulu un pēc tam ielādētu tajā vērtību, izmantojot rindu un kolonnu numuru (skatiet tiešo piemēru zemāk).
Funkcija Excel INDEX - piemēri
Šeit ir seši Excel INDEX funkcijas izmantošanas piemēri.
1. piemērs - Toma zīmju atrašana fizikā (divvirzienu uzmeklēšana)
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:
Lai atrastu Toma atzīmes fizikā, izmantojiet šādu formulu:
= INDEKSS ($ B $ 3: $ E $ 10,3,2)
Šī INDEX formula nosaka masīvu kā $ B $ 3: $ E $ 10, kuram ir atzīmes par visiem priekšmetiem. Tad tas izmanto rindas numuru (3) un kolonnas numuru (2), lai iegūtu Toma atzīmes fizikā.
2. piemērs - MEKLĒŠANAS vērtības dinamiska padarīšana, izmantojot funkciju MATCH
Ne vienmēr ir iespējams manuāli norādīt rindas numuru un kolonnas numuru. Iespējams, jums ir milzīga datu kopa, vai arī vēlaties to padarīt dinamisku, lai tā automātiski identificētu šūnās norādīto vārdu un/vai priekšmetu un sniegtu pareizu rezultātu.
Kaut kas, kā parādīts zemāk:
To var izdarīt, izmantojot INDEX un MATCH funkcijas kombināciju.
Šeit ir formula, kas padarīs uzmeklēšanas vērtības dinamiskas:
= INDEKSS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Iepriekš minētajā formulā rindas numura un kolonnas numura kodēšanas vietā tiek izmantota funkcija MATCH, lai padarītu to dinamisku.
- Dinamisko rindu numuru nosaka šāda formulas daļa - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Tas skenē studentu vārdu un identificē uzmeklēšanas vērtību (šajā gadījumā USD 5 USD). Pēc tam tas atgriež uzmeklēšanas vērtības rindas numuru datu kopā. Piemēram, ja uzmeklēšanas vērtība ir Matt, tā atgriezīs 1, ja tas būs Bobs, tā atgriezīs 2 un tā tālāk.
- Dinamiskais kolonnas numurs tiek dots ar šādu formulas daļu - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Tas skenē subjektu nosaukumus un identificē uzmeklēšanas vērtību (šajā gadījumā USD 4 USD). Pēc tam tas atgriež datu kopas uzmeklēšanas vērtības kolonnas numuru. Piemēram, ja uzmeklēšanas vērtība ir matemātika, tā atgriezīs 1, ja fizika - 2 un tā tālāk.
3. piemērs. Nolaižamo sarakstu izmantošana kā uzmeklēšanas vērtības
Iepriekš minētajā piemērā mums dati ir jāievada manuāli. Tas varētu aizņemt daudz laika un radīt kļūdas, it īpaši, ja jums ir milzīgs uzmeklēšanas vērtību saraksts.
Šādos gadījumos laba ideja ir izveidot uzmeklēšanas vērtību nolaižamo sarakstu (šajā gadījumā tas varētu būt studentu vārdi un priekšmeti) un pēc tam vienkārši izvēlēties no saraksta. Pamatojoties uz atlasi, formula automātiski atjauninātu rezultātu.
Kaut kas, kā parādīts zemāk:
Tas ir labs informācijas paneļa komponents, jo jums var būt milzīga datu kopa ar simtiem studentu aizmugurē, bet gala lietotājs (teiksim, skolotājs) var ātri iegūt skolēna atzīmes mācību priekšmetā, vienkārši veicot atlasi no nolaižamais.
Kā to izdarīt:
Šajā gadījumā izmantotā formula ir tāda pati kā 2. piemērā.
= INDEKSS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Uzmeklēšanas vērtības ir pārveidotas nolaižamajos sarakstos.
Lai izveidotu Excel nolaižamo sarakstu, veiciet tālāk norādītās darbības.
- Atlasiet šūnu, kurā vēlaties nolaižamo sarakstu. Šajā piemērā G4 mēs vēlamies studentu vārdus.
- Dodieties uz Dati -> Datu rīki -> Datu validācija.
- Lodziņā Datu validācijas dialoglodziņš iestatījumu cilnē nolaižamajā izvēlnē Atļaut atlasiet Saraksts.
- Avotā atlasiet $ A $ 3: $ A $ 10
- Noklikšķiniet uz Labi.
Tagad šūnā G5 būs nolaižamais saraksts. Līdzīgi jūs varat izveidot vienu H4 priekšmetiem.
4. piemērs. Atgriezt vērtības no visas rindas/kolonnas
Iepriekš minētajos piemēros mēs esam izmantojuši funkciju Excel INDEX, lai veiktu divvirzienu uzmeklēšanu un iegūtu vienu vērtību.
Ko darīt, ja vēlaties iegūt visas studenta atzīmes? Tas var ļaut jums atrast šī studenta maksimālo/minimālo punktu skaitu vai kopējās atzīmes visos mācību priekšmetos.
Vienkāršā angļu valodā vispirms vēlaties iegūt visu studenta (teiksim, Bobs) punktu skaitu un pēc tam šajās vērtībās noteikt augstāko punktu skaitu vai visu rezultātu kopsummu.
Šeit ir triks.
Programmā Excel INDEX funkcija, ievadot Kolonnas numurs ir 0, tas atgriezīs visas šīs rindas vērtības.
Tātad formula tam būtu šāda:
= INDEKSS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Tagad šī formula. ja to izmantos, tas atgriezīs #VALUE! kļūda. Kamēr tiek parādīta kļūda, aizmugurē tā atgriež masīvu, kurā ir visi Toma rādītāji - {57,77,91,91}.
Ja izvēlēsities formulu rediģēšanas režīmā un nospiedīsit F9, varēsit redzēt masīvu, ko tas atgriež (kā parādīts zemāk): 
Līdzīgi, pamatojoties uz to, kāda ir uzmeklēšanas vērtība, ja kolonnas numurs ir norādīts kā 0 (vai atstāts tukšs), tas atgriež visas uzmeklēšanas vērtības rindas vērtības
Tagad, lai aprēķinātu Toma iegūto kopējo punktu skaitu, mēs varam vienkārši izmantot iepriekš minēto formulu SUM funkcijā.
= SUMMA (INDEKSS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
Līdzīgās rindās, lai aprēķinātu augstāko punktu skaitu, mēs varam izmantot MAX/LARGE un minimālo - MIN/SMALL.
5. piemērs - Trīsceļu meklēšana, izmantojot INDEX/MATCH
Funkcija Excel INDEX ir veidota, lai apstrādātu trīsvirzienu meklējumus.
Kas ir trīsvirzienu uzmeklēšana?
Iepriekš minētajos piemēros esam izmantojuši vienu tabulu ar punktu skaitu studentiem dažādos priekšmetos. Šis ir divvirzienu uzmeklēšanas piemērs, jo mēs iegūstam divus mainīgos, lai iegūtu rezultātu (studenta vārdu un priekšmetu).
Pieņemsim, ka pēc gada studentam ir trīs dažādi eksāmenu līmeņi - vienības tests, vidusposma eksāmens un gala eksāmens (tas bija man, kad es biju students).
Trīs virzienu uzmeklēšana būtu iespēja iegūt studenta atzīmes par noteiktu priekšmetu no noteiktā eksāmena līmeņa. Tas padarītu to trīspusēju, jo ir trīs mainīgie (studenta vārds, priekšmeta nosaukums un eksāmena līmenis).
Šeit ir trīs virzienu meklēšanas piemērs:
Iepriekš minētajā piemērā varat izvēlēties ne tikai studenta vārdu un priekšmeta nosaukumu, bet arī eksāmena līmeni. Pamatojoties uz eksāmena līmeni, tas atgriež atbilstošo vērtību no vienas no trim tabulām.
Šeit ir šūnā H4 izmantotā formula:
= INDEKSS (($ 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 = "Unit Test", 1, IF ($ H $ 2 = "Midterm", 2,3)))
Sadalīsim šo formulu, lai saprastu, kā tā darbojas.
Šajā formulā ir četri argumenti. INDEX ir viena no funkcijām programmā Excel, kurai ir vairāk nekā viena sintakse.
= INDEKSS (masīvs, rindas_numurs, [kolonnas_numurs])
= INDEKSS (masīvs, rindas_numurs, [kolonnas_numurs], [apgabala_numurs])
Līdz šim visā iepriekš minētajā piemērā mēs esam izmantojuši pirmo sintaksi, bet, lai veiktu trīspusēju meklēšanu, mums jāizmanto otrā sintakse.
Tagad apskatīsim katru formulas daļu, pamatojoties uz otro sintaksi.
- masīvs - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): tā vietā, lai izmantotu vienu masīvu, šajā gadījumā mēs esam izmantojuši trīs masīvus iekavās.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): funkcija MATCH tiek izmantota, lai atrastu studenta vārda pozīciju šūnā $ G $ 4 studenta vārda sarakstā.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): funkcija MATCH tiek izmantota, lai atrastu subjekta nosaukuma pozīciju šūnā $ H $ 3 subjekta nosaukumu sarakstā.
- [apgabala_numurs] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): apgabala skaitļa vērtība norāda funkcijai INDEX, kuru masīvu izvēlēties. Šajā piemērā pirmajā argumentā ir trīs masīvi. Ja nolaižamajā izvēlnē atlasāt vienības testu, funkcija IF atgriež 1, un funkcijas INDEX izvēlas 1. masīvu no trim masīviem (kas ir $ B $ 3: $ E $ 7).
6. piemērs. Atsauces izveide, izmantojot funkciju INDEX (dinamiskie nosauktie diapazoni)
Šī ir viena no funkcijām Excel INDEX.
Ņemsim vienkāršu piemēru.
Man ir vārdu saraksts, kā parādīts zemāk:
Tagad es varu izmantot vienkāršu funkciju INDEX, lai iegūtu uzvārdu sarakstā.
Šeit ir formula:
= INDEKSS ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Šī funkcija vienkārši saskaita tukšo šūnu skaitu un atgriež pēdējo vienumu no šī saraksta (tā darbojas tikai tad, ja sarakstā nav tukšu vietu).
Tagad, ko šeit nāk burvība.
Ja ievietojat formulu šūnas atsauces priekšā, formula atgriež atbilstošās vērtības šūnas atsauci (nevis pašu vērtību).
= A2: INDEKSS ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Jūs varētu sagaidīt, ka iepriekš minētā formula atgriezīsies = A2: “Džošs” (kur Džošs ir pēdējā vērtība sarakstā). Tomēr tas atgriežas = A2: A9, un tādējādi jūs iegūstat vārdu masīvu, kā parādīts zemāk:
Viens praktisks piemērs, kur šī metode var būt noderīga, ir dinamisku nosaukto diapazonu izveidošana.
Tas ir šajā apmācībā. Esmu mēģinājis aprakstīt galvenos Excel INDEX funkcijas izmantošanas piemērus. Ja vēlaties, lai šim sarakstam tiktu pievienoti vēl citi piemēri, informējiet mani komentāru sadaļā.
Piezīme: Esmu mēģinājis visu iespējamo, lai izlasītu šo pamācību, taču, ja atrodat kādas kļūdas vai pareizrakstības kļūdas, lūdzu, informējiet mani 🙂
Funkcija Excel INDEX - video apmācība
- Excel VLOOKUP funkcija.
- Excel HLOOKUP funkcija.
- Excel netiešā funkcija.
- Excel MATCH funkcija.
- Excel OFFSET funkcija.
Jums varētu patikt arī šādas Excel apmācības:
- VLOOKUP vs. INDEKSS/MATCH
- Excel indeksa atbilstība
- Meklēt un atgriezt vērtības visā rindā/kolonnā.
