4 Excelove funkcije iskanja za učinkovito iskanje po preglednicah

4 Excelove funkcije iskanja za učinkovito iskanje po preglednicah

Večino časa iščete a Microsoft Excel preglednica je precej enostavna. Če ne morete preprosto poiskati vrstic in stolpcev, ga lahko poiščete s kombinacijo tipk Ctrl + F. Če delate z zelo veliko preglednico, lahko prihranite veliko časa za uporabo ene od teh štirih funkcij iskanja.





Ko boste vedeli, kako iskati v Excelu z iskanjem, ni pomembno, kako velike bodo vaše preglednice, vedno boste lahko našli nekaj v Excelu!



1. Funkcija VLOOKUP

Ta funkcija vam omogoča, da določite stolpec in vrednost ter vrne vrednost iz ustrezne vrstice drugega stolpca (če to ni smiselno, bo v kratkem postalo jasno). Dva primera, kjer lahko to storite, sta iskanje priimka zaposlenega po številki njegovega zaposlenega ali iskanje telefonske številke z navedbo priimka.





Tukaj je sintaksa funkcije:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [lookup_value] je podatek, ki ga že imate. Na primer, če morate vedeti, v kakšnem stanju je mesto, bi bilo to ime mesta.
  • [matrika_tabel] vam omogoča, da določite celice, v katerih bo funkcija iskala vrednosti za vrnitev in vrnitev. Ko izbirate obseg, se prepričajte, da je prvi stolpec v vašem nizu tisti, ki bo vseboval vašo iskalno vrednost!
  • [col_index_num] je številka stolpca, ki vsebuje vrnjeno vrednost.
  • [range_lookup] je izbirni argument in traja 1 ali 0. Če vnesete 1 ali izpustite ta argument, funkcija poišče vrednost, ki ste jo vnesli, ali naslednjo najnižjo številko. Tako bo na spodnji sliki VLOOKUP, ki išče oceno SAT 652, vrnil 646, saj je to najbližje število na seznamu, ki je manjše od 652, [range_lookup] pa je privzeto 1.

Poglejmo, kako lahko to uporabite. Ta preglednica vsebuje identifikacijske številke, ime in priimek, mesto, državo in ocene SAT. Recimo, da želite najti oceno SAT osebe s priimkom 'Winters'. VLOOKUP olajša delo. Tukaj je formula, ki bi jo uporabili:



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

Ker so rezultati SAT četrti stolpec od stolpca s priimkom, je 4 argument indeksa stolpca. Upoštevajte, da je pri iskanju besedila dobra nastavitev [range_lookup] na 0. Brez tega lahko dobite slabe rezultate.

Tukaj je rezultat:



Vrnilo je 651, rezultat SAT, ki pripada študentu po imenu Kennedy Winters, ki je v vrstici 92 (prikazano v zgornjem vložku). Za brskanje po iskanju imena bi trajalo veliko dlje kot za hiter vnos skladnje!

Opombe o VLOOKUP -u

Ko uporabljate VLOOKUP, si je dobro zapomniti nekaj stvari. Prepričajte se, da je prvi stolpec v vašem obsegu tisti, ki vključuje vašo iskalno vrednost. Če ni v prvem stolpcu, bo funkcija vrnila napačne rezultate. Če so vaši stolpci dobro organizirani, to ne bi smelo biti problem.



Upoštevajte tudi, da bo VLOOKUP kdaj vrnil samo eno vrednost. Če bi za iskalno vrednost uporabili 'Georgia', bi se vrnil rezultat prvega študenta iz Gruzije in ne bi kazalo, da sta v resnici dva študenta iz Gruzije.

2. Funkcija HLOOKUP

Če VLOOKUP najde ustrezne vrednosti v drugem stolpcu, HLOOKUP najde ustrezne vrednosti v drugi vrstici. Ker je običajno najlažje pregledati naslove stolpcev, dokler ne najdete pravega in s filtrom poiščete tisto, kar iščete, je HLOOKUP najbolje uporabiti, če imate res velike preglednice ali delate z vrednostmi, ki so urejene po času. .

Tukaj je sintaksa funkcije:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [lookup_value] je vrednost, za katero veste in želite poiskati ustrezno vrednost.
  • [matrika_tabel] so celice, v katerih želite iskati.
  • [vrstica_indeks_številka] podaja vrstico, iz katere bo vrnjena vrednost.
  • [range_lookup] je enako kot v VLOOKUP -u, pustite polje prazno, če želite, da dobite najbližjo vrednost, ali vnesite 0, če želite poiskati samo natančna ujemanja.

Ta preglednica vsebuje vrstico za vsako državo skupaj z oceno SAT v letih 2000–2014. S pomočjo HLOOKUP -a lahko poiščete povprečno oceno v Minnesoti v letu 2013. Tako bomo to storili:

=HLOOKUP(2013, A1:P51, 24)

Kot lahko vidite na spodnji sliki, se rezultat vrne:

Minnesotans je leta 2013 v povprečju dosegel oceno 1014. Upoštevajte, da leto 2013 ni v narekovajih, ker je številka in ne niz. Tudi 24 prihaja iz Minnesote, ki je v 24. vrsti.

Tukaj je kako izračunati tehtano povprečje v Excelu .

Opombe o HLOOKUP -u

Tako kot pri VLOOKUP mora biti vrednost iskanja v prvi vrstici matrike tabel. Pri HLOOKUP je to redko težava, saj boste za iskalno vrednost običajno uporabili naslov stolpca. HLOOKUP vrne tudi samo eno vrednost.

3-4. Funkcije INDEX in MATCH

INDEX in MATCH sta dve različni funkciji, vendar lahko skupaj poenostavita iskanje po veliki preglednici. Obe funkciji imata pomanjkljivosti, a z njihovo kombinacijo bomo gradili na prednostih obeh.

Najprej pa sintaksa obeh funkcij:

=INDEX([array], [row_number], [column_number])
  • [niz] je matrika, v kateri boste iskali.
  • [vrstica_številka] in [številka_ stolpca] lahko uporabite za zožitev iskanja (to bomo pogledali v kratkem.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [lookup_value] je iskalni izraz, ki je lahko niz ali številka.
  • [lookup_array] je matrika, v kateri bo Microsoft Excel iskal iskalni izraz.
  • [match_type] je izbirni argument, ki je lahko 1, 0 ali -1. 1 vrne največjo vrednost, ki je manjša ali enaka iskalnemu izrazu. 0 bo vrnilo samo vaš natančen izraz, -1 pa vrnilo najmanjšo vrednost, ki je večja ali enaka iskalnemu izrazu.

Morda ne bo jasno, kako bomo skupaj uporabljali ti dve funkciji, zato jo bom predstavil tukaj. MATCH vzame iskalni izraz in vrne sklic na celico. Na spodnji sliki lahko vidite, da pri iskanju vrednosti 646 v stolpcu F MATCH vrne 4.

INDEX pa dela nasprotno: vzame referenco celice in v njej vrne vrednost. Tukaj lahko vidite, da INDEX, ko mu pove, naj vrne šesto celico stolpca Mesto, vrne 'Sidrišče', vrednost iz 6. vrstice.

To, kar bomo storili, je združiti oboje, tako da MATCH vrne sklic na celico, INDEX pa to referenco uporabi za iskanje vrednosti v celici. Recimo, da se spomnite, da je bil študent z imenom Waters in želite videti, kakšen je bil rezultat tega študenta. Tukaj je formula, ki jo bomo uporabili:

miška windows 10 se premika sama
=INDEX(F:F, MATCH('Waters', C:C, 0))

Opazili boste, da je vrsta ujemanja tukaj nastavljena na 0. Ko iščete niz, boste to želeli uporabiti. Evo, kaj dobimo, ko zaženemo to funkcijo:

Kot lahko vidite iz priloge, je Owen Waters dosegel 1720, število, ki se pojavi, ko zaženemo funkcijo. Morda se vam to ne zdi tako koristno, če si ogledate le nekaj stolpcev, a zamislite si, koliko časa bi prihranili, če bi to morali narediti 50 -krat na velika preglednica zbirke podatkov ki je vseboval nekaj sto stolpcev!

Naj se iskanje v Excelu začne

Microsoft Excel ima veliko izredno močne funkcije za manipuliranje s podatki, zgoraj našteti štirje pa samo opraskajo površino. Če se jih naučite uporabljati, vam bo življenje veliko olajšano.

Če resnično želite obvladati Microsoft Excel, bi imeli res koristno, če bi imeli Essential Excel Cheat Sheet pri roki!

Zasluga za sliko: Cico/ Shutterstock

Deliti Deliti Cvrkutati E-naslov Canon proti Nikon: katera znamka fotoaparata je boljša?

Canon in Nikon sta dve največji imeni v industriji fotoaparatov. Toda katera blagovna znamka ponuja boljšo ponudbo fotoaparatov in objektivov?

Preberite Naprej Sorodne teme
  • Produktivnost
  • Preglednica
  • Microsoft Excel
  • Iskalni triki
O avtorju Ian Buckley(216 objavljenih člankov)

Ian Buckley je samostojni novinar, glasbenik, izvajalec in video producent, ki živi v Berlinu v Nemčiji. Ko ne piše ali na odru, se ukvarja z elektroniko ali kodo DIY v upanju, da bo postal nor znanstvenik.

Več od Iana Buckleyja

Naročite se na naše novice

Pridružite se našemu glasilu za tehnične nasvete, ocene, brezplačne e -knjige in ekskluzivne ponudbe!

Kliknite tukaj, da se naročite