Me kõik teame Exceli funktsioonide kuulsat tähte VLOOKUP. Seda kasutatakse tavaliselt kordumatu ID -ga väärtuste otsimiseks. Kuid see pole ainus funktsioon, mida saab kasutada Exceli väärtuste otsimiseks. Väärtuse otsimiseks on palju muid funktsioone ja valemeid. Selles artiklis tutvustan teile kõiki neid Exceli otsingufunktsioone ja valemeid. Mõned on isegi paremad kui Exceli funktsioon VLOOKUP. Niisiis, lugege lõpuni.
1. Funktsioon Exceli VLOOKUP
Esimene Exceli otsingufunktsioon on loomulikult funktsioon VLOOKUP. See funktsioon on kuulus põhjusel. Selle funktsiooni abil saame teha rohkem kui lihtsalt otsida. Kuid selle funktsiooni põhiülesanne on tabeli väärtuste otsimine vasakult paremale.
Funktsiooni VLOOKUP süntaks:
| = VLOOKUP (lookup_value, tabel_massiiv, col_index_number, [vahemiku_vaade]) |
Otsinguväärtus: Väärtus, mille järgi soovite otsida tabelimassiivi esimeses veerus.
Tabelimassiiv: Tabel, kust soovite otsida/otsida
col_index_number: Tabeli massiivi veerunumber, millest soovite tulemusi tuua.
[vahemiku_vaade]: VÄÄR, kui soovite otsida täpset väärtust, TÕENE, kui soovite ligikaudset vastet.
VLOOKUPi eelised:
- Lihtne kasutada.
- Kiire
- Mitmekordne kasutamine
- Sobib kõige paremini väärtuste otsimiseks vertikaalses järjekorras.
Puudused:
- Seda kasutatakse ainult vertikaalse otsingu jaoks
- See tagastab ainult esimese sobitatud väärtuse.
- Staatiline, kuni seda kasutatakse koos MATCH -funktsiooniga.
- Otsinguväärtusest vasakult ei saa otsida väärtusi.
Selle Exceli otsinguvalemi kohta saate üksikasjalikult lugeda siit.
2. Funktsioon Excel HLOOKUP
Funktsioon HLOOKUP on funktsiooni VLOOKUP puuduv osa. Funktsiooni HLOOKUP kasutatakse väärtuste horisontaalseks otsimiseks. Teisisõnu, kui soovite Excelis väärtust otsida veergude väärtuste sobitamise ja ridadelt väärtuste saamiseks, kasutame funktsiooni HLOOKUP. See on täpselt funktsioon VLOOKUP.
HLOOKUPi süntaks
=HLOOKUP(otsinguväärtus, tabelimassiiv, rea indeksi number, [vahemiku_vaade])
- otsingu väärtus: Väärtus, mida otsite.
- Tabelimassiiv: Tabel, milles väärtust otsite.
- Ridaindeksi number: Tabeli rea number, kust soovite andmeid hankida.
- [vahemiku_vaade]: see on vaste tüüp. 0 täpse vaste jaoks ja 1 ligikaudse vaste jaoks.
Funktsiooni HLOOKUP eelised:
- See võib otsida väärtusi horisontaalselt.
- Lihtne kasutada.
- Mitmekordne kasutamine
- Kiire
Puudused:
- Seda kasutatakse ainult horisontaalse otsingu jaoks
- See tagastab ainult esimese sobitatud väärtuse.
- Staatiline, kuni seda kasutatakse koos funktsiooniga MATCH.
- Tabeli otsinguväärtustest kõrgemaid väärtusi ei saa otsida.
Selle Exceli otsingufunktsiooniga saate tutvuda siin.
3. INDEX-MATCH otsinguvalem
Kuhu VLOOKUP ja HLOOKUP ei jõua, võib see valem jõuda. See on Exceli parim otsimisvalem kuni Excel 2016 (XLOOKUP on teel).
INDEX MATCHi üldvalem
= INDEX (Result_Range, MATCH (otsinguväärtus, otsinguvahemik, 0))
Tulemus_vahemik: See on vahemik, kust soovite väärtust hankida.
Otsinguväärtus: See on väärtus, millele soovite vastata.
Otsingu_vahemik:See on vahemik, milles soovite otsinguväärtust sobitada.
INDEX-MATCH otsinguvalemi eelised:
- Saab otsida neljas suunas. See võib otsida väärtusi otsinguväärtusest vasakule ja üles.
- Dünaamiline.
- Rea- või veeruindeksit pole vaja määratleda.
Puudused:
- Uutel kasutajatel võib see olla keeruline.
- Kasutab kahte Exceli funktsiooni koos. Kasutajad peavad mõistma funktsiooni INDEX ja MATCH toimimist.
Selle valemiga saate tutvuda siin.
4: Exceli OFFSET-MATCH otsinguvalem
See on veel üks valem, mida saab kasutada väärtuste dünaamiliseks otsimiseks. See Exceli otsingu valem kasutab funktsiooni OFFSET ankurfunktsioonina ja MATCH etteandefunktsioonina. Seda valemit kasutades saame tabelist väärtusi dünaamiliselt alla laadida, otsides ridadest ja veergudest üles.
Üldine valem,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
StartCell:See on otsingu tabeli alguslahtrid. Oletame, et kui soovite otsida vahemikus A2: A10, on StartCell A1.
RowLookupValue: See on otsinguväärtus, mille soovite leida ridade allStartCell.
RowLookupRange:See on vahemik, kust soovite otsida RowLookupValue. See on allpool olev vahemik StartCell (A2: A10).
ColLookupValue: See on otsinguväärtus, mille soovite leida veergudest (päistest).
ColLookupRange:See on vahemik, kust soovite otsida ColLookupValue. See on StartCelli paremal pool olev vahemik (nagu B1: D1).
Selle Exceli otsingu tehnika eelised:
- Kiire
- Saab otsida horisontaalselt ja vertikaalselt.
- Dünaamiline
Puudused:
- Mõne inimese jaoks keeruline.
- Peate mõistma funktsiooni OFFSET ja MATCH funktsiooni.
Selle otsinguvalemi kohta saate siit üksikasjalikult teada.
5: Excel LOOKUP valem Mitu väärtust
Kõik ülaltoodud otsinguvalemid tagastavad massiivist esimese leitud väärtuse. Kui on mitu mängu, ei tagasta nad teisi. Sellisel juhul rakendub see valem päeva päästmiseks. See valem tagastab loendist kõik sobitatud väärtused, mitte ainult esimese vaste.
See valem kasutab põhifunktsioonidena funktsioone INDEX, ROW ja IF. Funktsiooni IFERROR saab kasutada valikuliselt vigade käsitlemiseks.
Üldine valem
{= INDEX (massiiv, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (lookup_value_range) esimene lahter) +1), ROW (1: 1))}}
Massiiv: Vahemik, kust soovite andmeid hankida.
lookup_value: Teie otsinguväärtus, mida soovite filtreerida.
lookup_value_range: Vahemik, milles soovite otsinguväärtust filtreerida.
Otsinguväärtuste vahemiku esimene lahter: kui teie otsinguväärtuste vahemik on $ A $ 5: $ A $ 100, siis selle $ 5 $.
Tähtis: Kõik peaks olema absoluutselt viidatud. lookup_value võib olla vastavalt nõudele suhteline.
Sisestage see massiivivalemina. Pärast valemi kirjutamist vajutage CTRL+SHIFT+ENTER klahvi, et muuta see massiivivalemiks.
Nagu gifist näha, tagastab see kõik tabelid Exceli tabelist.
Eelised:
- Tagastab mitme sobitatud väärtusega Exceli tabelist.
- Dünaamiline
Puudused:
- See on uue kasutaja jaoks liiga keeruline.
- Kasutab massiivivalemit
- Peate määratlema võimaliku väljundite arvu ja rakendama seda valemit mitme lahtriga massiivivalemina (mitte Excelis 2019 ja 365).
- Aeglane.
Selle valemiga saate üksikasjalikult tutvuda siin.
6: VLOOKUP-CHOOSE Otsingu Exceli valem
Niisiis, enamik inimesi ütleb, et VLOOKUP funktsiooni abil ei ole Excelis võimalik otsida väärtusi vasakult otsinguväärtusest. Mul on kahju öelda, kuid nad eksivad. Me saame otsida Excelis otsinguväärtusest vasakule, kasutades funktsiooni VLOOKUP funktsiooni CHOOSE abil.
Üldine valem:
= VLOOKUP (lookup_value, CHOOSE ({1, 2}, lookup_range, req_range), 2, 0)
lookup_value : väärtus, mida otsida
lookup_range : vahemik, kust otsida lookup_value
req_range : vahemik, kus nõutakse vastavat väärtust
2 : teine veerg, number tähistab req_range
0 : otsige täpset vastet
Selles valemis loome põhimõtteliselt valemi sisse virtuaalse tabeli, kasutades funktsiooni CHOOSE. Funktsioon VALI loob kahe veeru tabeli. Esimene veerg sisaldab otsinguvahemikku ja teine veerg sisaldab tulemuste vahemikku.
Otsingu valemi VLOOKUP-CHOOSE eelised:
- Saate otsida otsinguväärtusest vasakul
- Kiire
- Lihtne
Puudused:
- Funktsiooni VALI kasutatakse harva. Kasutajad peavad aru saama selle toimimisest.
Siit saate teada selle otsinguvalemi kohta.
Nii et jah, poisid, need on erinevad otsingufunktsioonid ja valemid. See pole veel kõik. Excelis võib olla palju rohkem otsinguvalemeid, mida saab luua Exceli valemite erinevate kombinatsioonide abil. Kui teil on mingeid erilisi otsingutehnikaid, jagage neid allpool kommentaaride jaotises. Lisame selle oma artiklisse koos teie nimega.
Loodan, et see oli kasulik ja informatiivne. Kui teil on selle artikli osas kahtlusi, küsige seda allpool kommentaaride osas.
10+ uut funktsiooni Excelis 2019 ja 365: Kuigi Excelis 2016 ja vanemates versioonides saadaolevatest funktsioonidest piisab igasuguste arvutuste ja automaatika väljatöötamiseks, muutuvad valemid mõnikord keeruliseks. Sellised väikesed, kuid olulised asjad lahendatakse Excelis 2019 ja 365.
17 asja Exceli kohta VLOOKUP: VLOOKUP pole lihtsalt otsinguvalem, see on midagi enamat. VLOOKUPil on palju muid võimeid ja seda saab kasutada mitmel otstarbel. Selles artiklis uurime funktsiooni VLOOKUP kõiki võimalikke kasutusviise.
10+ loomingulist täiustatud Exceli diagrammi armatuurlauale: Excel on võimas andmete visualiseerimise tööriist, mille abil saab Excelis hämmastavaid diagramme luua. Neid 15 täiustatud Exceli diagrammi saab kasutada oma kolleegide ja ülemuste lummamiseks.
4 Exceli sihtmärgi ja saavutuste diagrammi Excelis: Eesmärgi ja saavutuste graafikud on iga ettevõtte kõige põhilisemad ja olulisemad graafikud. Seega on parem panna need võimalikult loovalt. Need 4 loomingulist diagrammi võivad panna teie armatuurlauad esitlust kõigutama.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada funktsiooni Excel VLOOKUP| See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Kuidas Excelit kasutada Funktsioon COUNTIF| Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.
