Kujutage ette, et töövihikus on mitu identset lehte, mis sisaldavad identseid tabeleid (nt iga kuu osavõtuandmed eraldi lehel). Nüüd soovite luua armatuurlaua, mis näitab kogu kuu külastatavust. Nüüd ei ole hea valik juhtida armatuurlaual iga kuu andmeid korraga. Soovime kuu valimiseks rippmenüüd. Vajame VLOOKUP valemit, mida vaadata valitud kuu lehelt.
Lihtsamalt öeldes vajame muutuvate lehtede otsimiseks otsimisvalemit.
Nagu ülaltoodud gif näitab, kasutame funktsiooni VLOOKUP ja INDIRECT koos, et otsida mitmelt lehelt nende nimede põhjal.
Üldine valem mitme lehe otsimiseks
| =VLOOKUP(otsinguväärtus,KAUDSED(""&lehe_nime_viide&"! lookup_table "), col_index, 0) |
Otsinguväärtus: See on väärtus, mida otsides otsite tabel.
Lehe_nime_viide: See on lehe nime sisaldava lahtri viide.
Otsingtabel: See on tabeli viide, millest soovite otsida lookup_value. See võib olla nimega vahemik, tabel või absoluutne viide. See peaks olema kõigil lehtedel sama.
Col_Index: See on tabeli veerunumber, millest soovite väärtuse hankida. Kui olete VLOOKUP -funktsiooniga tuttav, teate, mis see on.
Nii et hüppame näite juurde.
Näide: Tooge valitud kuu külastatavus
Seega on meil töövihik, mis säilitab minu Exceli õpilaste kohaloleku. Iga kuu andmed salvestatakse eraldi erinevatesse lehtedesse. Lehe nimi on seatud kuude nimele. Praegu on mul kolme kuu andmed, kuid neid tuleb muidugi rohkem.
Soovin koostada aruande, mis näitab valitud kuu kohalolekut. Kuu saab valida ripploendist. Valem peaks saama lehelt automaatselt üles otsida, isegi kui sellele lisatakse uus leht.
Niisiis, koostame ülaltoodud tabeli. Cell G3 koostasime ripploendi abil ripploendi.
Otsingu väärtus on sees B4. Leht_nime_viide on sees G3. Kõigi lehtede otsingulaud on B3: AZ100. Soovime väärtust hankida kahest veerust. Nii et me kirjutame selle valemi C4 -sse ja lohistame selle alla. Samamoodi muudame puuduvate väärtuste korral veeruindeksit.
| =VLOOKUP(B4,KAUDSED("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Kuidas see töötab?
Valem on lahendatud seestpoolt. Kõigepealt vaatame, kuidas see samm -sammult lahendatakse.
Eeldusel, et G3 sisaldab Jaan.
| =VLOOKUP(B4,KAUDSED("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = VLOOKUP (B4, KAUDNE ("Jan! $ B $ 3: $ 100 $"),2,0) |
| = VLOOKUP (B4,Jan! $ B $ 3: $ 100 $,2,0) |
| =7 |
Niisiis, kõigepealt lause "" & $ G $ 3 & "! $ B $ 3: $ Az $ 100" lahendatakse stringiks "Jan! $ B $ 3: $ Az $ 100". Järgmisena teisendab funktsioon INDIRECT selle stringi tegelikuks viiteks. Ja lõpuks saime valemi VLOOKUP (B4, Jan! $ B $ 3: $ Az $ 100,2,0). Ja see lahendab lõpuks 7. Nüüd, kui muudate lehe nime G3 -s, muudetakse väärtuse viite teksti. Ja nii otsite Exceli muutuvatelt lehtedelt üles.
Loodan, et sellest oli teile abi. Kui teil on küsimusi või soovite mõne muu otsingu teha, andke mulle sellest allpool kommentaaride osas teada. Aitan teid hea meelega. Seni jätkake Excelling.
Kasutage kahe või enama otsingutabeli VLOOKUP -i | Mitmest tabelist otsimiseks saame kasutada IFERROR -lähenemist. Mitmest tabelist üles vaatamine võtab vea järgmise tabeli lülitina. Teine meetod võib olla If -lähenemine.
Kuidas Excelis teha tõstutundlikku otsingut? | Exceli funktsioon VLOOKUP ei ole tõstutundlik ja tagastab loendist esimese sobitatud väärtuse. INDEX-MATCH pole erand, kuid seda saab muuta, et muuta see tõstutundlikuks. Vaatame, kuidas…
Otsige Excelis sageli esinevat teksti kriteeriumidega | Otsing ilmub kõige sagedamini tekstis vahemikus, mida kasutame funktsiooni INDEX-MATCH with MODE abil. Siin on meetod.
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 enimkasutatud 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.