Exceli otsingu tabelid on nimega tabelid, mida kasutatakse funktsiooniga vlookup mis tahes andmete leidmiseks, kui meil on palju andmeid ja me ei tea, kust otsida, võime selle tabeli valida ja sellele nime anda ning vlookupi kasutamise ajal funktsioon selle asemel, et anda viide, võime väärtuse otsimiseks sisestada viitena tabeli nime. Sellist tabelit tuntakse Excelis otsingu tabelina.
Kuidas Excelis otsingu tabelit luua?
Otsimisfunktsioonid on excelis elupäästjad. Saadaoleva väärtuse või otsinguväärtuse põhjal saame tuua muud sellega seotud andmed erinevatest andmetabelitest. Excelis on VLOOKUP kõige sagedamini kasutatav otsingufunktsioon.
Selles artiklis käsitleme mõnda olulist otsingufunktsiooni Excelis ja ka seda, kuidas Excelis otsingu tabelit luua. Olulised otsimisfunktsioonid on VLOOKUP & HLOOKUP, V tähistab vertikaalset otsingut ja H tähistab horisontaalset otsingut. Andmete otsimiseks tabelist on meil ka funktsioon LOOKUP.
Nende otsimisfunktsioonide abil saame hankida saadaolevate andmete muu teabe nii erinevatelt töölehtedelt kui ka erinevatest töövihikutest.
# 1 - Looge otsingu tabel funktsiooni VLOOKUP abil
Nagu ma ütlesin, on VLOOKUP traditsiooniline otsimisfunktsioon, mida kõik kasutajad regulaarselt kasutavad. Näitame teile, kuidas selle otsimisfunktsiooni abil väärtusi otsida.
- Otsiväärtus pole muud kui saadaolev väärtus. Selle väärtuse põhjal proovime andmeid tuua teisest tabelist.
- Table Array on lihtsalt peamine tabel, kus kogu teave asub.
- Col Index Num on ainult see, millisest tabeli massiivi veerust me andmeid tahame. Siinkohal peame mainima veeru numbrit.
- Vahemiku otsing pole midagi muud kui see, kas otsite täpset vastet või ligikaudset vastet. Kui otsite täpset vastet, siis on argument FALSE või 0; kui otsite ligikaudset vastet, siis on argument TRUE või 1.
Funktsiooni VLOOKUP näide: Oletame, et allpool on teie toodete müügi andmed ja nende müügisumma.
Nüüd lahtris D2 on teil üks toote ID ja selle toote ID abil peate müügi väärtuse tooma VLOOKUPi abil.
1. samm: rakendage funktsioon VLOOKUP ja avage kõigepealt valem.
2. samm: esimene argument on väärtus LOOKUP. Otsiväärtus on meie põhi- või saadaolev väärtus. Nii et valige viitena lahter D2.
3. samm: järgmine on tabeli massiiv; see pole midagi muud kui meie põhitabel, kus asuvad kõik andmed. Seega valige tabelimassiiv A2 kuni B11.
4. samm: nüüd vajutage funktsiooniklahvi F4, et see oleks absoluutne Exceli viide. See lisab valitud lahtrisse dollari sümboli.
5. samm: järgmine argument on veeru indeksi number valitud tabelist, millisest veerust te tegelikult andmeid otsite. Sel juhul oleme valinud kaks veergu, ja me vajame andmeid 2 nd veerus nii mainida 2 argumendina.
6. samm: nüüd on viimane argument vahemiku otsing, st otsingu tüüp. Kuna me vaatame täpset vastet, valige FALSE või sisestage argumendiks null.
7. samm: sulgege sulg ja vajutage sisestusklahvi. Meil peaks olema toote ID Prd 5 müügiväärtus.
8. samm: mis siis, kui me tahame toote müügiandmeid, kui Prd6. Muidugi võime otse siseneda, kuid see pole õige lähenemine. Pigem saame luua Excelis rippmenüü ja lubada kasutajal rippmenüüst valida. Vajutage lahtris D2 alt = "" + A + V + V; see on kiirklahv, mis on otseteeklahv andmete valideerimise loomiseks Excelis.
9. samm: valige rippmenüüst Luba: Luba.
10. samm: allikas: valige loend Toote ID vahemikus A2 kuni A11.
11. samm: klõpsake nuppu OK. Nüüd on meil kõigi toodete loend lahtris D2.
# 2 - Kasutage funktsiooni LOOKUP Exceli tabeli LOOKUP loomiseks
VLOOKUPi asemel võime alternatiivina kasutada ka funktsiooni LOOKUP Excelis. Vaatame funktsiooni LOOKUP valemit.
- Otsiväärtus on baasväärtus või saadaolev väärtus.
- Otsinguvektor pole muud kui põhitabeli otsinguväärtuse veerg.
- Tulemusvektor pole midagi muud kui nõuab põhitabeli veergu.
Rakendame valemi funktsiooni LOOKUP loogika mõistmiseks.
1. samm: avage otsingufunktsioon kohe.
2. samm: otsingu väärtus on toote ID, seega valige lahter D2.
3. samm: otsinguvektor pole muud kui põhitabeli veerg ProductId. Seega valige vahemikuks A1 kuni A11.
4. samm: järgmisena tulemusvektor, see pole midagi muud kui see, millisest tulbast me andmeid vajame. Sel juhul soovime ajavahemikust B1 kuni B11 andmete hankimist.
5. samm: sulgege sulg ja vajutage valemi sulgemiseks sisestusklahvi. Meil peaks olema valitud toote ID müügiväärtus.
6. samm: erineva tulemuse nägemiseks muutke toote ID-d.
# 3 - Kasutage funktsiooni INDEX + MATCH
Funktsiooniga VLOOKUP saab andmeid tuua vasakult paremale, kuid Exceli funktsiooni INDEX funktsiooni ja MATCH valemi abil saame andmeid tuua kõikjalt, et luua LOOKUP Exceli tabel
1. samm: avage esmalt valem INDEX.
2. samm: esimese argumendi jaoks valige põhitabeli tulemuste veerg.
3. samm: rea numbri saamiseks peame rakendama funktsiooni MATCH. Funktsiooni MATCH leiate pildi alt.
4. samm: sulgege sulg ja sulgege valem. Meil on tulemused.
Asjad, mida meeles pidada
- Otsimine peaks olema sama, mis Exceli põhitabelis.
- VLOOKUP töötab vasakult paremale, mitte paremalt vasakule.
- Funktsioonis LOOKUP peame lihtsalt valima tulemuse veeru, erinevalt VLOOKUPist ei pea mainima veeru indeksi numbrit.
