Apvienojot ExcelVLOOKUP funkcija arSLEJA funkcija, mēs varam izveidot meklēšanas formulu, kas ļauj atgriezt vairākas vērtības no vienas datu bāzes vai datu tabulas rindas.
01 no 10Atgriezties vairākās vērtībās ar programmu Excel VLOOKUP
Izmantojot tālāk uzskaitītos soļus, izveidota uzmeklēšanas formula, kas redzama attēlā virs tā, atgriež vairākas vērtības no viena datu ieraksta.
Meklējuma formula prasa, lai SLEJA funkcija ir ievietota iekšpusē VLOOKUP. Funkcijas pievienošana ietver otro funkciju ievadīšanu kā vienu no pirmajai funkcijai izvirzītajiem argumentiem.
Šajā apmācībā, SLEJA funkcija tiek ievadīta kā kolonnu indekss numurs arguments par VLOOKUP. Pēdējais apmācības posms ietver meklēšanas lodziņa kopēšanu ar papildu slejām, lai iegūtu atlasītās daļas papildu vērtības.
Ievadiet apmācības datus
Pirmais solis apmācībā ir ievadīt datus Excel darblapā. Lai sekotu apmācības soļiem, ievadiet attēlus, kas parādīti attēlā iepriekš, šādās šūnās:
- Ievadiet augšējo datu diapazonu šūnas D1 uz G1.
- Ievadiet otro diapazonu šūnas D4 uz G10.
Šajā apmācībā tiks izveidoti meklēšanas kritēriji un meklēšanas formulas 2. rinda no darblapas.
Apmācība neietver attēlu, kas redzams attēlā, taču tas neietekmēs to, kā meklēšanas formulas darbojas. Informācija par formatēšanas opcijām, kas līdzīgas iepriekš minētajām, ir pieejama šajā Excel formāta sagatavošanas pamatlicenē.
03 no 10Datu tabulā nosauktais diapazons
Nosauktais diapazons ir vienkāršs veids, kā norādīt uz formulas datu diapazonu. Tā vietā, lai ierakstītu datu šūnu atsauces, jūs varat vienkārši ierakstīt diapazona nosaukumu.
Otra priekšrocība nosaukta diapazona izmantošanai ir tāda, ka šūnu atsauces šai diapazonam nekad nemainās pat tad, ja formula tiek kopēta citās darblapas šūnās. Tāpēc diapazona nosaukumi ir alternatīva absolūto šūnu atsaucēm, lai novērstu kļūdas, nokopējot formulas.
Diapazona nosaukums neietver datu nosaukumus vai lauku nosaukumus (4. rinda), bet tikai paši dati.
- Izcelt šūnas D5 uz G10 darblapā tos atlasīt.
- Noklikšķiniet uz Vārda lodziņš atrodas virs aile A.
- Tips Tabula iekšā Vārda lodziņš.
- Nospiediet Ievadiet tastatūras taustiņš.
- Cells D5 uz G10 tagad ir diapazona nosaukums Tabula. Mēs izmantosim vārdu VLOOKUP galda masīvs arguments vēlāk apmācībā.
VLOOKUP dialoglodziņa atvēršana
Lai gan ir iespējams vienkārši ievadīt mūsu meklēšanas formulu tieši darblapas šūnā, daudziem cilvēkiem ir grūti paturēt sintaksi taisnīgi, it īpaši sarežģītas formulas gadījumā, piemēram, tā, kuru mēs izmantojam šajā apmācībā.
Šajā gadījumā alternatīva ir izmantot VLOOKUP dialoglodziņš. Gandrīz visās Excel funkcijās ir dialoglodziņš, kas ļauj ievadīt katru no funkciju argumentiem atsevišķā rindā.
Apmācības soļi
- Klikšķiniet uz šūnu E2 no darblapas - vieta, kur tiks parādīti divdimensiju meklēšanas formulas rezultāti.
- Noklikšķiniet uz Cilne Formulas no lente.
- Noklikšķiniet uz Meklēt un atsauce iespēja lente lai atvērtu nolaižamo izvēlni.
- Klikšķiniet uz VLOOKUP sarakstā, lai atvērtu funkciju dialoglodziņu.
Ievadiet vērtējuma vērtējuma argumentu
Parasti, uzmeklēšana vērtība atbilstu datu laukam datu tabulas pirmajā slejā. Mūsu piemērā, uzmeklēšana vērtība attiecas uz aparatūras daļas nosaukumu, par kuru mēs vēlamies atrast informāciju. Pieļaujamie datu veidi uzmeklēšana vērtība ir teksta dati, loģiskās vērtības, skaitļi un šūnu atsauces.
Absolūtās šūnu atsauces
Parasti, kad formulas tiek kopētas Excel, šūnu atsauces mainās, lai atspoguļotu to jauno atrašanās vietu. Ja tā notiek, D2, šūnu atsauce uz uzmeklēšana vērtība, mainīsies, jo formula tiek kopēta, radot kļūdas šūnas F2 un G2.
Absolūtās šūnu atsauces nemainās, kad formulas tiek kopētas.
Lai novērstu kļūdas, mēs pārveidosim šūnu atsauci D2 par absolūtu šūnu atsauci. Absolūtās šūnu atsauces tiek veidotas, nospiežot F4 tastatūras taustiņš. Šādi pievienojot dolāra zīmes ap šūnu atsauci, piemēram, $ D $ 2.
- Noklikšķiniet uz lookup_value dialoglodziņā.
- Klikšķiniet uz šūna D2 lai pievienotu šo šūnu atsauci uz lookup_value line - tā ir šūna, kurā mēs ievadīsim daļu nosaukumu, par kuru mēs meklējam informāciju
- Nenorādot ievietošanas punktu, nospiediet F4 taustiņš uz tastatūras, lai konvertētu D2 uz absolūto šūnu atsauci $ D $ 2.
- Atstājiet VLOOKUP funkciju dialoglodziņš, kas atvērts nākamajam apmācības posmam.
Ievadiet tabulas masīvu argumentu
Tabulas masīvs ir datu tabula, kuru meklēšanas formulas meklēšana tiek veikta, lai atrastu vajadzīgo informāciju.Tabulas masīvā jābūt vismaz divām datu kolonnām.
Pirmajā kolonnā ir apskatīšanas vērtības arguments (iepriekšējais apmācības solis), bet otrā kolonna tiks meklēt pēc meklēšanas formulas, lai atrastu norādīto informāciju.
Tabulas masīva arguments ir jāieraksta kā diapazons, kurā ir datu tabulas šūnu atsauces vai kā diapazona nosaukums.
- Noklikšķiniet uz table_array dialoglodziņā.
- Tips Tabula lai ievadītu šī argumenta diapazona nosaukumu.
- Atstājiet VLOOKUP funkciju dialoglodziņš, kas atvērts nākamajam apmācības posmam.
COLUMN funkcijas fiksēšana
Parasti VLOOKUP tikai atgriež datus no vienas datu tabulas kolonnas un šo sleju nosaka kolonnas indeksa numurs arguments.
Tomēr šajā piemērā mums ir trīs kolonnas, no kurām mēs vēlamies atgriezt datus, tādēļ mums ir nepieciešams veids, kā viegli mainīt kolonnas indeksa numurs bez rediģēšanas mūsu meklēšanas formulu - tas ir, kur SLEJA funkcija nāk
Ievadot to kā kolonnas indeksa numurs arguments mainīsies, jo meklēšanas lodziņš ir nokopēts no šūnu D2 uz šūnas E2 un F2 vēlāk apmācībā.
Ligzdošanas funkcijas
The SLEJA funkcija darbojas kā VLOOKUPs kolonnas indeksa numurs arguments; to var paveikt, ligzdojot SLEJA funkcija iekšpusē VLOOKUP iekš Col_index_num dialoglodziņa līnija.
Manuāla COLUMN funkcijas ievadīšana manuāli
Kad ligzdošanas funkcijas, Excel neļauj mums atvērt otrās funkcijas dialoglodziņu, lai ievadītu savus argumentus. The SLEJA tāpēc funkcija ir jāievada manuāli Col_index_num līnija The SLEJA funkcijai ir tikai viens arguments - Atsauce arguments, kas ir šūnu atsauce.
COLUMN funkcijas atsauces argumenta izvēle
The SLEJA funkcijas uzdevums ir atgriezt kolonnas numuru, kas norādīts kā Atsauce arguments. Citiem vārdiem sakot, kolonnas burtu pārvērš par skaitli ar aile A ir pirmā kolonna sleja B otrais un tā tālāk.
Kopš pirmā datu lauka, ko mēs vēlamies atgriezt, ir vienības cena, kas ir datu tabulas otrajā slejā. Mēs varam izvēlēties šūnas atsauci jebkurai šūnai iekšā sleja B kā Atsauce Arguments, lai iegūtu numuru 2 priekš Col_index_num arguments.
- Iekš VLOOKUP funkciju dialoglodziņš, noklikšķiniet uz Col_index_num līnija
- Ievadiet funkcijas nosaukumu sleja kam seko a atvērt apaļo kronšteinu.
- Klikšķiniet uz šūna B1 darblapā ievadīt šai šūnas atsauci kā Atsauce arguments.
- Ievadiet a aizverot kronšteinu lai pabeigtu SLEJA funkcija
- Atstājiet VLOOKUP funkciju dialoglodziņš, kas atvērts nākamajam apmācības posmam.
Ievadiet VLOOKUP diapazona meklēšanas argumentu
VLOOKUPs Range_lookup arguments ir loģiska vērtība (TRUE vai FĀDS), kas norāda, vai vēlaties VLOOKUP lai atrastu precīzu vai aptuvenu atbilstību Lookup_value.
- Ja TRUE vai ja šis arguments ir izlaists: VLOOKUP atgriež vai nu precīzu atbilstību Lookup_valueVai, ja precīzā atbilstība nav atrasta VLOOKUP atgriež nākamo lielāko vērtību. Formulā to izdarīt, dati pirmajā slejā Table_array jāsakārtē augošā secībā.
- Ja FĀDS: VLOOKUP izmantos tikai precīzu atbilstību Lookup_value. Ja pirmajā slejā ir divas vai vairākas vērtības Table_array kas atbilst meklēšanas vērtībai, tiek izmantota pirmā atrasta vērtība. Ja precīzu atbilstību nav atrasts, a # N / A kļūda tiek atgriezta.
Šajā apmācībā, jo mēs meklējam konkrētu informāciju par konkrētu aparatūras vienību, mēs to iestatīsim Range_lookup vienāds ar Nepatiesa.
- Noklikšķiniet uz Range_lookup dialoglodziņā.
- Ierakstiet vārdu Nepatiesa šajā rindā, lai norādītu, ka mēs gribam VLOOKUP lai atgrieztu precīzu atbilstību meklētajiem datiem.
- Klikšķis labi lai pabeigtu meklēšanas formulu un aizveriet dialoglodziņu.
- Tā kā mēs vēl neesam iekļāvuši meklēšanas kritērijus šūna D2 an # N / A kļūda būs klāt šūnu E2.
- Šī kļūda tiks labota, kad mēs pievienosim meklēšanas kritērijus pēdējā apmācības posmā.
Meklēšanas formulas kopēšana
Uzmeklēšanas formulas mērķis ir vienlaikus izgūt datus no vairākām datu tabulas kolonnām. Lai to izdarītu, meklēšanas veidlapai ir jāatrodas visos laukos, no kuriem mēs gribam informāciju.
Šajā apmācībā mēs vēlamies, lai no tiem izgūtu datus slejas 2, 3, un 4 no datu tabulas - tas ir cena, daļas numurs un piegādātāja vārds, kad mēs ievadām daļēju nosaukumu kā Lookup_value.
Tā kā dati ir pareizi rakstīti darblapā, mēs varam kopēt meklēšanas formulu šūnu E2 uz šūnas F2 un G2.
Formulā tiek kopēts, Excel atjaunina relatīvo šūnu atsauci SLEJA funkcija (šūna B1), lai atspoguļotu formulas jauno atrašanās vietu.
Kā arī, Excel nemaina absolūtu šūnu atsauci $ D $ 2 un nosauktais diapazons Tabula kā formula ir nokopēta.
Excel datu kopēšanai ir vairāk nekā viens veids, bet iespējams, ka vienkāršākais veids ir, izmantojot Aizpildiet rokturi.
- Klikšķiniet uz šūnu E2, kur atrodama meklēšanas formula, lai padarītu to par aktīvo elementu.
- Ievietojiet peles rādītājs pāri melns kvadrāts apakšējā labajā stūrī - rādītājs mainīsies uz pluszīmes, lai apzīmētu aizpildīšanas rokturi.
- Noklikšķiniet uz kreisās peles pogas un velciet aizpildīšanas rokturi pāri līdz šūnā G2.
- Atbrīvot peles pogu un šūnu F3 vajadzētu ietvert divdimensiju meklēšanas formulu.
- Ja tas tiek izdarīts pareizi, šūnas F2 un G2 tagad arī būtu jāietver # N / A kļūda, kas atrodas šūnu E2.
Meklēt kritēriju ievadīšana
Kad uzmeklēšanas formula ir nokopēta vajadzīgajās šūnās, to var izmantot, lai izgūtu informāciju no datu tabulas. Lai to izdarītu, ierakstiet objekta nosaukums jūs vēlaties ielādēt Lookup_value šūna (D2) un nospiediet Ievadiet tastatūras taustiņš.
Kad tas ir izdarīts, katrai šūnai, kurā ir meklēšanas formulas, ir jābūt citam datu vienumam par aparatūras vienumu, kuru meklējat.
- Klikšķiniet uz šūnu D2 darblapā.
- Tips Logrīks uz šūnu D2 un nospiediet Ievadiet tastatūras taustiņš.
- Turpmāk norādītā informācija ir jāuzrāda šūnas E2 uz G2.
- E2: 14,76 Ls - logrīka cena
- F2: PN-98769 - logrīka daļas numurs
- G2: Widgets Inc. - piegādātāja nosaukums logrīkiem
- Pārbaudi VLOOKUP masīvu formulu tālāk, ierakstot citu daļu nosaukumu šūnu D2 un novērojot rezultātus šūnas E2 uz G2
