4 Exceli otsingufunktsiooni arvutustabelite tõhusaks otsimiseks

4 Exceli otsingufunktsiooni arvutustabelite tõhusaks otsimiseks

Enamasti otsib a Microsoft Excel arvutustabel on üsna lihtne. Kui te ei saa selle ridu ja veerge lihtsalt skannida, saate selle otsimiseks kasutada klahvikombinatsiooni Ctrl + F. Kui töötate tõeliselt suure arvutustabeli abil, võib ühe neist neljast otsingufunktsioonist palju aega säästa.





Kui teate, kuidas Excelis otsingu abil otsida, pole vahet, kui suured on teie arvutustabelid, saate Excelist alati midagi leida!



1. Funktsioon VLOOKUP

See funktsioon võimaldab teil määrata veeru ja väärtuse ning tagastab väärtuse teise veeru vastavast reast (kui see pole mõttekas, selgub see hetkega). Kaks näidet selle kohta, kuidas seda teha, on töötaja perekonnanime otsimine töötaja numbri järgi või telefoninumbri leidmine perekonnanime määramisega.





Siin on funktsiooni süntaks:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [lookup_value] on see teave, mis teil juba on. Näiteks kui teil on vaja teada, mis osariigis linn asub, oleks see linna nimi.
  • [table_array] võimaldab määrata lahtreid, milles funktsioon otsimis- ja tagastusväärtusi otsib. Vahemiku valimisel veenduge, et teie massiivi esimene veerg sisaldaks teie otsinguväärtust!
  • [col_index_num] on tagasiväärtust sisaldava veeru number.
  • [vahemiku_vaade] on valikuline argument ja võtab 1 või 0. Kui sisestate 1 või jätate selle argumendi vahele, otsib funktsioon teie sisestatud väärtust või väikseimat numbrit. Nii et alloleval pildil tagastab VLOOKUP, mis otsib SAT -i skoori 652, 646, kuna see on loendis lähim number, mis on väiksem kui 652, ja [range_lookup] on vaikimisi 1.

Vaatame, kuidas seda kasutada. See arvutustabel sisaldab ID -numbreid, ees- ja perekonnanimesid, linna, osariigi ja SAT -i hindeid. Oletame, et soovite leida SAT -skoori perekonnanimega 'Talved'. VLOOKUP teeb selle lihtsaks. Siin on teie kasutatav valem:



=VLOOKUP('Winters', C2:F101, 4, 0)

Kuna SAT -i tulemused on neljas veerg perekonnanime veerust, on 4 veeruindeksi argument. Pange tähele, et kui otsite teksti, on hea [[range_lookup]] väärtuseks määrata 0. Ilma selleta võite saada halbu tulemusi.

Siin on tulemus:



See tagas 651, SAT -i skoori, mis kuulus õpilasele Kennedy Wintersile, kes on reas 92 (kuvatud ülaosas). Nime otsimisel oleks kerimine võtnud palju kauem aega kui süntaksi kiire sisestamine!

Märkused VLOOKUPi kohta

VLOOKUPi kasutamisel on hea meeles pidada mõnda asja. Veenduge, et teie vahemiku esimene veerg sisaldaks teie otsinguväärtust. Kui see pole esimeses veerus, tagastab funktsioon valed tulemused. Kui teie veerud on hästi korraldatud, ei tohiks see olla probleem.



Samuti pidage meeles, et VLOOKUP tagastab ainult ühe väärtuse. Kui oleksite otsinguväärtusena kasutanud sõna „Gruusia”, oleks see tagastanud esimese Gruusiast pärit õpilase skoori ja poleks näidanud, et Gruusias on tegelikult kaks õpilast.

2. Funktsioon HLOOKUP

Kui VLOOKUP leiab teises veerus vastavad väärtused, siis HLOOKUP leiab vastavad väärtused teisest reast. Kuna tavaliselt on kõige lihtsam skaneerida veerupäiseid, kuni leiate õige ja otsitava leidmiseks filtri, on HLOOKUP -i kõige parem kasutada siis, kui teil on tõesti suured arvutustabelid või töötate väärtustega, mis on korraldatud aja järgi .

Siin on funktsiooni süntaks:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [lookup_value] on väärtus, mida teate ja millele soovite vastava väärtuse leida.
  • [table_array] on lahtrid, millest soovite otsida.
  • [rea_indeksinumber] määrab rea, kust tagastamisväärtus pärineb.
  • [vahemiku_vaade] on sama mis VLOOKUPis, jätke see tühjaks, et saada lähim väärtus, kui võimalik, või sisestage 0, et otsida ainult täpseid vasteid.

See arvutustabel sisaldab iga osariigi rida koos SAT -skooriga aastatel 2000–2014. HLOOKUPi abil saate leida 2013. aasta Minnesota keskmise tulemuse. Siin on, kuidas me seda teeme:

=HLOOKUP(2013, A1:P51, 24)

Nagu näete alloleval pildil, tagastatakse skoor:

Minnesotaanlaste keskmine tulemus oli 2013. aastal 1014. Pange tähele, et 2013. aasta ei ole jutumärkides, sest see on number, mitte string. Samuti on 24 pärit Minnesotast, kes on 24. reas.

Siin on kuidas Excelis kaalutud keskmist arvutada .

Märkused HLOOKUPi kohta

Nagu ka VLOOKUPi puhul, peab otsinguväärtus olema teie tabelimassiivi esimesel real. See on HLOOKUPiga harva probleem, kuna tavaliselt kasutate otsingu väärtuse jaoks veeru pealkirja. HLOOKUP tagastab ka ainult ühe väärtuse.

3-4. INDEX ja MATCH funktsioonid

INDEX ja MATCH on kaks erinevat funktsiooni, kuid koos kasutamisel võivad nad suure arvutustabeli otsimise palju kiiremaks muuta. Mõlemal funktsioonil on puudusi, kuid neid kombineerides toetume mõlema tugevustele.

Esiteks, mõlema funktsiooni süntaks:

=INDEX([array], [row_number], [column_number])
  • [massiiv] on massiiv, millest otsite.
  • [rea_number] ja [veeru_number] saab otsingut kitsendada (vaatame selle hetkega üle.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [lookup_value] on otsingutermin, mis võib olla string või number.
  • [lookup_array] on massiiv, milles Microsoft Excel otsingutermini otsib.
  • [vastetüüp] on valikuline argument, mis võib olla 1, 0 või -1. 1 tagastab suurima väärtuse, mis on väiksem või võrdne teie otsinguterminiga. 0 tagastab ainult teie täpse termini ja -1 tagastab väikseima väärtuse, mis on suurem või võrdne teie otsinguterminiga.

Võib -olla pole selge, kuidas me neid kahte funktsiooni koos kasutame, nii et ma toon selle siin välja. MATCH võtab otsingutermini ja tagastab lahtri viite. Alloleval pildil näete, et veeru F väärtuse 646 otsimisel tagastab MATCH 4.

INDEX aga teeb vastupidi: võtab lahtri viite ja tagastab selles sisalduva väärtuse. Siin näete, et kui palutakse veeru City kuues lahter tagasi saata, tagastab INDEX 6. rea väärtuse „Anchorage”.

See, mida me teeme, on need kaks ühendada nii, et MATCH tagastab lahtri viite ja INDEX kasutab seda viidet lahtri väärtuse otsimiseks. Oletame, et mäletate, et oli õpilane, kelle perekonnanimi oli Waters, ja soovite näha, milline oli selle õpilase skoor. Siin on valem, mida kasutame:

mida saate kellegi mac -aadressiga teha?
=INDEX(F:F, MATCH('Waters', C:C, 0))

Märkate, et vaste tüübiks on siin määratud 0. Kui otsite stringi, siis soovite seda kasutada. Selle funktsiooni käivitamisel saame järgmist.

Nagu sisestusest näete, saavutas Owen Waters 1720, see number ilmub funktsiooni käivitamisel. See ei pruugi tunduda kuigi kasulik, kui saate vaadata vaid mõnda veergu, kuid kujutage ette, kui palju aega säästaksite, kui peaksite seda tegema 50 korda suur andmebaasi arvutustabel mis sisaldas mitusada veergu!

Las Exceli otsingud algavad

Microsoft Excelis on palju äärmiselt võimsad funktsioonid andmetega manipuleerimiseks ja eespool loetletud neli kriimustavad lihtsalt pinda. Nende kasutamise õppimine muudab teie elu palju lihtsamaks.

Kui soovite tõesti Microsoft Excelit juhtida, võite tõesti saada kasu Essential Exceli petulehe käepärast hoidmisest!

Pildi krediit: Cico/ Shutterstock

Jaga Jaga Piiksuma E -post Canon vs Nikon: milline kaamera kaubamärk on parem?

Canon ja Nikon on kaameratööstuse kaks suurimat nime. Kuid milline kaubamärk pakub paremat kaamerate ja objektiivide valikut?

Loe edasi Seotud teemad
  • Tootlikkus
  • Arvutustabel
  • Microsoft Excel
  • Otsingu nipid
Autori kohta Ian Buckley(Avaldatud 216 artiklit)

Ian Buckley on vabakutseline ajakirjanik, muusik, esineja ja videoprodutsent, kes elab Saksamaal Berliinis. Kui ta parasjagu ei kirjuta ega laval, nokitseb ta isetehtud elektroonika või koodi kallal, lootuses saada hulluks teadlaseks.

Rohkem Ian Buckleylt

Telli meie uudiskiri

Liituge meie uudiskirjaga, et saada tehnilisi näpunäiteid, ülevaateid, tasuta e -raamatuid ja eksklusiivseid pakkumisi!

Tellimiseks klõpsake siin