Funktsioon Excel INDEX - Valeminäited + TASUTA video

Funktsioon Excel INDEX (näited + video)

Millal kasutada funktsiooni Excel INDEX

Funktsiooni Excel INDEX saab kasutada siis, kui soovite tabeli andmetest väärtuse tuua ja teil on andmepunkti rea- ja veerunumber. Näiteks võite allolevas näites kasutada funktsiooni INDEX, et saada füüsikas märke „Tom”, kui teate andmekogumis olevat rea ja veeru numbrit.

Mida see tagastab

See tagastab määratud rea ja veeru numbri tabeli väärtuse.

Süntaks

= INDEX (massiiv, rea number, [veerg]]
= INDEX (massiiv, rea number, [veerg], [pindala number])

Funktsioonil INDEX on 2 süntaksit. Esimest kasutatakse enamikul juhtudel, kuid kolmepoolsete otsingute korral kasutatakse teist (kaetud näites 5).

Sisendargumendid

• massiiv - a lahtrite vahemik või massiivikonstant.
• rea_number - rea number, millest väärtus tuleb hankida.
• [col_num] - veeru number, millest väärtus tuleb hankida. Kuigi see on valikuline argument, kuid kui rea_numbrit pole esitatud, tuleb see esitada.
• [ala_number] - (Valikuline) Kui massiivi argument koosneb mitmest vahemikust, kasutatakse seda numbrit viite valimiseks kõigist vahemikest.

Lisamärkused (igavad asjad… aga oluline teada)

• Kui rea või veeru number on 0, tagastab see vastavalt kogu rea või veeru väärtused.
• Kui funktsiooni INDEX kasutatakse lahtri viite (näiteks A1 :) ees, tagastab see väärtuse asemel lahtri viite (vt näiteid allpool).
• Kõige sagedamini kasutatakse koos funktsiooniga MATCH.
• Erinevalt funktsioonist VLOOKUP saab funktsioon INDEX tagastada väärtuse otsinguväärtuse vasakult poolt.
• Funktsioonil INDEX on kaks vormi - massiivivorm ja viitevorm
  • „Massiivivorm” on koht, kus saate väärtuse antud tabeli ridade ja veergude arvu alusel.
  • „Võrdlusvorm” on koht, kus on mitu tabelit ja te kasutate tabeli valimiseks argumenti area_num ning seejärel toote selle väärtuse rea ja veeru numbri abil (vt reaalajas näidet allpool).

Funktsioon Excel INDEX - näited

Siin on kuus näidet Exceli INDEX -funktsiooni kasutamise kohta.

Näide 1 - Tomi märkide leidmine füüsikast (kahepoolne otsing)

Oletame, et teil on allpool näidatud andmekogum:

Tomi märkide leidmiseks füüsikast kasutage järgmist valemit:

= INDEKS ($ B $ 3: $ E $ 10,3,2)

See INDEX -valem määrab massiivi järgmiselt: $ B $ 3: $ E $ 10, millel on kõigi subjektide märgid. Seejärel kasutab see rea numbrit (3) ja veerunumbrit (2) Tomi märkide toomiseks füüsikas.

Näide 2 - LOOKUPi väärtuse muutmine dünaamiliseks, kasutades funktsiooni MATCH

Rea ja veeru numbri käsitsi määramine ei pruugi alati õnnestuda. Teil võib olla tohutu andmekogum või soovite muuta selle dünaamiliseks, et see tuvastaks automaatselt lahtrites määratud nime ja/või teema ning annaks õige tulemuse.

Midagi, nagu allpool näidatud:

Seda saab teha kombinatsiooni funktsiooniga INDEX ja MATCH.

Siin on valem, mis muudab otsinguväärtused dünaamilisteks:

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

Ülaltoodud valemis kasutatakse rea numbri ja veeru numbri kodeerimise asemel dünaamiliseks muutmiseks funktsiooni MATCH.

• Dünaamilise rea numbri annab valemi järgmine osa - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). See skaneerib õpilaste nimed ja tuvastab otsinguväärtuse (antud juhul $ G $ 5). Seejärel tagastab see andmestiku otsinguväärtuse rea numbri. Näiteks kui otsingu väärtus on Matt, tagastab see 1, kui Bob, siis 2 ja nii edasi.
• Dünaamilise veeru numbri annab valemi järgmine osa - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). See skaneerib teemade nimesid ja tuvastab otsinguväärtuse (antud juhul $ 4 $). Seejärel tagastab see andmestiku otsinguväärtuse veeru numbri. Näiteks kui otsingu väärtus on matemaatika, tagastab see 1, kui see on füüsika, siis 2 ja nii edasi.

Näide 3 - rippmenüüde kasutamine otsinguväärtustena

Ülaltoodud näites peame andmed käsitsi sisestama. See võib olla aeganõudev ja veaohtlik, eriti kui teil on suur otsinguväärtuste loend.

Sellistel juhtudel on hea mõte luua otsinguväärtuste ripploend (sel juhul võivad need olla õpilaste nimed ja ained) ning seejärel lihtsalt loendist valida. Valiku põhjal värskendaks valem tulemust automaatselt.

Midagi, nagu allpool näidatud:

See on hea armatuurlaua komponent, kuna teil võib olla tohutu andmekogum, mille tagaosas on sadu õpilasi, kuid lõppkasutaja (oletame, et õpetaja) saab kiiresti mõne aine õpilase hinded kätte, tehes lihtsalt valikud rippmenüüst.

Kuidas seda teha:

Sel juhul kasutatav valem on sama, mida näites 2.

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

Otsinguväärtused on teisendatud ripploenditeks.

Exceli ripploendi loomiseks toimige järgmiselt.

• Valige lahter, kuhu soovite ripploendi. Selles näites soovime G4 -s õpilaste nimesid.
• Avage Andmed -> Andmetööriistad -> Andmete valideerimine.
• Andmete valideerimise dialoogi väljal seadete vahekaardil valige rippmenüüst Luba loend Loend.
• Valige allikas $ A $ 3: $ A $ 10
• Klõpsake nuppu OK.

Nüüd on teil lahtris G5 ripploend. Sarnaselt saate H4 -s ainete jaoks selle luua.

Näide 4 - tagastage väärtused kogu realt/veerult

Ülaltoodud näidetes oleme kasutanud funktsiooni Excel INDEX, et teha kahesuunaline otsing ja saada üks väärtus.

Mis siis, kui soovite saada kõik õpilase hinded. See võimaldab teil leida selle õpilase maksimaalse/minimaalse punktisumma või kõikide ainete punktisummad.

Lihtsa inglise keeles soovite kõigepealt saada kogu õpilase (ütleme Bob) tulemuste rea ja seejärel nende väärtuste sees kindlaks teha kõrgeima punktisumma või kõigi tulemuste summa.

Siin on trikk.

Funktsioonis Excel INDEX, kui sisestate veeru number on 0, tagastab see kogu rea väärtused.

Nii et selle valem oleks järgmine:

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)

Nüüd see valem. kui seda kasutatakse sellisena, tagastab see #VALUE! viga. Kuigi see kuvab vea, tagastab see taustaprogrammis massiivi, millel on kõik Tomi hinded - {57,77,91,91}.

Kui valite valemi redigeerimisrežiimis ja vajutate klahvi F9, näete massiivi, mille see tagastab (nagu allpool näidatud):

Samamoodi tagastab otsinguväärtuse põhjal veerunumbri 0 (või tühjaks jäetud) korral kõik otsinguväärtuse rea väärtused

Nüüd, et arvutada Tomi kogupunktid, saame lihtsalt kasutada ülaltoodud valemit funktsiooni SUM raames.

= SUMMA (INDEKS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))

Sarnastel ridadel saame kõrgeima punktisumma arvutamiseks kasutada MAX/LARGE ja minimaalse arvutamiseks MIN/SMALL.

Näide 5 - Kolmesuunaline otsing INDEX/MATCH abil

Funktsioon Excel INDEX on loodud kolmepoolse otsingu tegemiseks.

Mis on kolmepoolne otsing?

Ülaltoodud näidetes oleme kasutanud ühte tabelit, kus on erinevate ainete õpilaste hinded. See on näide kahepoolsest otsimisest, kuna kasutame tulemuse toomiseks kahte muutujat (õpilase nimi ja teema).

Oletame, et aasta pärast on õpilasel kolm erinevat eksamitaset: ühikutest, vahe- ja lõpueksam (see oli mul üliõpilasena).

Kolmesuunaline otsing oleks võimalus saada õpilase hinded kindlaksmääratud eksamitasemelt konkreetse aine kohta. See muudaks selle kolmepoolseks otsinguks, kuna on kolm muutujat (õpilase nimi, õppeaine nimi ja eksami tase).

Siin on näide kolmest otsest:

Ülaltoodud näites saate lisaks õpilase nime ja aine nime valimisele valida ka eksami taseme. Eksami taseme põhjal tagastab see ühest kolmest tabelist sobiva väärtuse.

Siin on lahtris H4 kasutatav valem:

= INDEX (($ 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 = "Vaheaeg", 2,3)))

Jagame selle valemi, et mõista, kuidas see toimib.

See valem sisaldab nelja argumenti. INDEX on üks neist Exceli funktsioonidest, millel on rohkem kui üks süntaks.

= INDEX (massiiv, rea number, [veeru number])
= INDEX (massiiv, rea number, [veerg], [pindala number])

Siiani oleme ülaltoodud näites kasutanud esimest süntaksit, kuid kolmepoolse otsingu tegemiseks peame kasutama teist süntaksit.

Nüüd vaatame valemi iga osa teise süntaksi alusel.

• massiiv - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): ühe massiivi kasutamise asemel oleme sel juhul kasutanud sulgudes kolme massiivi.
• row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): funktsiooni MATCH kasutatakse õpilase nime asukoha leidmiseks lahtris $ G $ 4 õpilase nime loendis.
• col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): funktsiooni MATCH kasutatakse subjekti nime asukoha leidmiseks lahtris $ H $ 3 subjekti nime loendis.
• [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): Pindala numbri väärtus ütleb funktsioonile INDEX, millise massiivi valida. Selles näites on meil esimeses argumendis kolm massiivi. Kui valite rippmenüüst Unit Test, tagastab IF funktsioon 1 ja INDEX funktsioonid valivad kolmest massiivist 1. massiivi (mis on $ B $ 3: $ E $ 7).

Näide 6 - viite loomine funktsiooni INDEX abil (dünaamilised nimega vahemikud)

See on Exceli funktsiooni INDEX üks metsik kasutamine.

Võtame lihtsa näite.

Mul on nimede loend, nagu allpool näidatud:

Nüüd saan kasutada lihtsat funktsiooni INDEX, et saada nimekirjast perekonnanimi.

Siin on valem:

= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

See funktsioon lihtsalt loendab tühjade lahtrite arvu ja tagastab selle loendi viimase üksuse (see töötab ainult siis, kui loendis pole tühikuid).

No mis siin maagiast tuleb.

Kui asetate valemi lahtri viite ette, tagastab valem sobiva väärtusega lahtri viite (väärtuse enda asemel).

= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

Oodate, et ülaltoodud valem tagastab = A2: “Josh” (kus Josh on loendi viimane väärtus). Kuid see tagastab = A2: A9 ja seega saate massiivi nimesid, nagu allpool näidatud:

Üks praktiline näide, kus see tehnika võib aidata, on dünaamiliste nimega vahemike loomine.

See on selles õpetuses. Olen püüdnud hõlmata peamisi näiteid funktsiooni Excel INDEX kasutamisest. Kui soovite sellesse loendisse rohkem näiteid lisada, andke mulle sellest kommentaaride osas teada.

Märkus: Olen proovinud seda õpetust tõestada, kuid kui leiate vigu või kirjavigu, andke mulle sellest teada 🙂

Funktsioon Excel INDEX - videoõpetus

• Funktsioon Excel VLOOKUP.
• Funktsioon Excel HLOOKUP.
• Exceli kaudne funktsioon.
• Funktsioon Excel MATCH.
• Funktsioon Excel OFFSET.

Samuti võivad teile meeldida järgmised Exceli õpetused:

• VLOOKUP vs. INDEX/MATCH
• Exceli indeksivaste
• Otsimis- ja tagastamisväärtused terves reas/veerus.