Apvienojot Excel VLOOKUP funkciju ar COLUMN funkciju, mēs varam izveidot meklēšanas formulu, kas ļauj atgriezt vairākas vērtības no vienas datu bāzes vai datu tabulas rindas.
Iepriekš attēlā redzamajā piemērā meklēt veidlapu ļauj viegli atgriezt visas vērtības, piemēram, cenu, daļas numuru un piegādātāju, kas saistītas ar dažādiem aparatūras elementiem.
01 no 10
Atgriezties 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ēšanas formulas prasībai, lai funkcija COLUMN būtu ievietota VLOOKUP iekšpusē.
Funkcijas pievienošana ietver otro funkciju ievadīšanu kā vienu no pirmajai funkcijai izvirzītajiem argumentiem .
Šajā apmācībā funkcija COLUMN tiks ievadīta kā VLOOKUP kolonnu rādītāja numura arguments.
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.
Mācību saturs
- Ievadiet apmācības datus
- Datu tabulā nosauktais diapazons
- VLOOKUP funkcijas sākšana
- Ievadiet vērtēšanas argumentu, izmantojot absolūto šūnu atsauces
- Ievadiet tabulas masīvu argumentu
- Ievietot ievietoto COLUMN funkciju
- VLOOKUP funkcijas aizpildīšana
- Meklēšanas formulas kopēšana ar aizpildīšanas rokturi
- Datu iegūšana ar meklēšanas formulu
02 no 10
Ievadiet apmācības datus
Pirmais solis apmācībā ir ievadīt datus Excel darblapā .
Lai sekotu apmācības soļiem, ievadiet tālāk redzamajā attēlā redzamos datus šādās šūnās .
- Ievadiet vislabāko datu diapazonu šūnās no D1 līdz G1
- Ievadiet otro diapazonu no šūnām no D4 līdz G10
Šīs apmācības laikā izveidotie meklēšanas kritēriji un meklēšanas formulas tiks ievadītas darblapas 2. rindā .
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ē .
Apmācības soļi
- Ievadiet datus, kas redzami attēlā iepriekš, šūnās no D1 līdz G10
03 no 10
Datu 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.
Piezīme . Diapazona nosaukums neietver datu nosaukumus vai lauku nosaukumus (4. rinda), bet tikai pašus datus.
Apmācības soļi
- Lai tos atlasītu, iezīmējiet darblapā D5 līdz G10
- Noklikšķiniet uz vārdu lodziņa, kas atrodas virs kolonnas A
- Ierakstiet lodziņā "Tabula" (bez citātiem)
- Nospiediet tastatūras ENTER taustiņu
- Šūnām no D5 līdz G10 tagad ir diapazona nosaukums "Tabula". Mēs izmantosim nosaukumu VLOOKUP tabulas masīvu argumentam vēlāk apmācībā
04 no 10
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 dialoglodziņu VLOOKUP. Gandrīz visās Excel funkcijās ir dialoglodziņš, kas ļauj ievadīt katru no funkciju argumentiem atsevišķā rindā.
Apmācības soļi
- Noklikšķiniet uz darblapas šūnas E2 - vietas, kur tiks parādīti divdimensiju meklēšanas formulas rezultāti
- Noklikšķiniet uz lentas cilnes Formulas
- Lentē noklikšķiniet uz opcijas Lookup & Reference , lai atvērtu nolaižamo sarakstu funkciju
- Sarakstā noklikšķiniet uz VLOOKUP , lai atvērtu funkciju dialoglodziņu
05 no 10
Ievadiet vērtēšanas argumentu, izmantojot absolūto šūnu atsauces
Parasti uzmeklēšanas vērtība atbilst datu laukam datu tabulas pirmajā slejā .
Mūsu piemērā meklējumu vērtība attiecas uz aparatūras daļas nosaukumu, par kuru mēs vēlamies atrast informāciju.
Pieņemamie datu veidi meklēšanas vaicājumam ir šādi:
- teksta dati
- loģiska vērtība (tikai TRUE vai FALSE)
- skaitlis
- šūnu atsauce uz vērtību darblapā
Šajā piemērā mēs ievadīsim šūnu atsauci uz to, kur atradīsies daļas nosaukums - šūnā D2.
Absolūtās šūnu atsauces
Nākamajā apmācības posmā mēs kopēsim meklēšanas formulu šūnā E2 uz šūnām F2 un G2.
Parasti, kad formulas tiek kopētas Excel, šūnu atsauces mainās, lai atspoguļotu to jauno atrašanās vietu.
Ja tas notiks, D2 - šūnas atsauce uz meklēšanas vērtību - mainīsies, jo formula tiek kopēta, radot kļūdas šūnās F2 un G2.
Lai novērstu kļūdas, mēs pārveidosim šūnu atsauci D2 par absolūtu šūnu atsauci .
Absolūtās šūnu atsauces nemainās, kad formulas tiek kopētas.
Absolūtās šūnu atsauces tiek veidotas, nospiežot tastatūras taustiņu F4 . Šādi pievienojot dolāra zīmēm ap šūnu atsauci, piemēram, $ D $ 2
Apmācības soļi
- Dialoglodziņā noklikšķiniet uz rindas " lookup_value"
- Noklikšķiniet uz šūnas D2, lai pievienotu šo šūnas atsauci uz lookup_value līniju. Šī ir šūna, kurā mēs ievadīsim daļu nosaukumu, par kuru mēs meklējam informāciju
- Nenokaujot ievietošanas punktu, nospiediet tastatūras taustiņu F4, lai pārvērstu D2 par absolūto šūnu atsauci $ D $ 2
- Atstājiet dialoglodziņu VLOOKUP funkciju, lai atvērtu nākamajam apmācības posmam
06 no 10
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 posms)
- otro un visas papildu slejas tiks meklētas 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 .
Šajā piemērā mēs izmantosim diapazona nosaukumu, kas izveidots apmācības 3. darbībā.
Apmācības soļi
- Dialoglodziņā noklikšķiniet uz tabulas_ražošanas līnijas
- Ierakstiet "Tabula" (bez pēdiņām), lai ievadītu šī argumenta diapazona nosaukumu
- Atstājiet dialoglodziņu VLOOKUP funkciju, lai atvērtu nākamajam apmācības posmam
07 no 10
COLUMN funkcijas fiksēšana
Parasti VLOOKUP tikai atgriež datus no vienas datu tabulas kolonnas, un šo kolonnu nosaka kolonnu 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 kolonnu indeksa numuru, nemainot mūsu meklēšanas formulu.
Šeit tiek parādīta funkcija COLUMN. Ievadot to kā kolonnas rādītāja numura argumentu, tas mainīsies, jo meklēšanas formulas kopija no šūnas D2 uz šūnām E2 un F2 vēlāk apmācībā.
Ligzdošanas funkcijas
Tādējādi funkcija COLUMN darbojas kā VLOOKUP kolonnu indeksa numurs arguments .
To var paveikt, ievietojot COLUMN funkciju VLOOKUP iekšpusē dialoglodziņa rindiņā Col_index_num .
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.
Tāpēc COLUMN funkcija ir jāievada manuāli līnijā Col_index_num .
Funkcijai COLUMN ir tikai viens arguments - Atsauces arguments, kas ir šūnu atsauce.
COLUMN funkcijas atsauces argumenta izvēle
Funkcijas COLUMN uzdevums ir atgriezt kolonnas numuru, kas norādīts kā Atsauces arguments.
Citiem vārdiem sakot, kolonnas vēstuli pārvērš par skaitli, jo sleja A ir pirmā kolonna, B slejā - otrā un tā tālāk.
Tā kā pirmais datu lauks, kuru mēs vēlamies atgriezt, ir vienības cena, kas ir datu tabulas otrajā slejā, mēs varam izvēlēties atsauces argumentu jebkurai šūnai B slejā kā atsauces argumentu, lai iegūtu numuru 2 Col_index_num arguments.
Apmācības soļi
- Dialoglodziņā VLOOKUP funkcija noklikšķiniet uz līnijas Col_index_num
- Ierakstiet funkciju nosaukuma sleju, kam seko atvērta kārta " ( "
- Noklikšķiniet uz šūnu B1 darblapā, lai ievadītu šo šūnu atsauci kā atsauces argumentu
- Ierakstiet aizvēršanas kronšteinu " ) ", lai pabeigtu COLUMN funkciju
- Atstājiet dialoglodziņu VLOOKUP funkciju, lai atvērtu nākamajam apmācības posmam
08 no 10
Ievadiet VLOOKUP diapazona meklēšanas argumentu
VLOOKUP Range_lookup arguments ir loģiska vērtība (tikai TRUE vai FALSE), kas norāda, vai vēlaties, lai VLOOKUP atrast 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_value, vai, ja precīza atbilstība nav atrasta, VLOOKUP atgriež nākamo lielāko vērtību. Lai to izdarītu, formula Table_array pirmajā slejā ir jāsakārto augošā secībā .
- Ja FALSE, VLOOKUP tiks izmantota precīza atbilstība tikai Lookup_value. Ja Table_array pirmajā slejā ir divas vai vairākas vērtības, kas atbilst meklēšanas vērtībai, tiek izmantota pirmā atrasta vērtība. Ja precīzā atbilstība netiek atrasta, tiek atgriezta # N / A kļūda.
Šajā pamācībā, jo mēs meklējam konkrētu informāciju par konkrētu aparatūras vienību, mēs iestatīsim Range_lookup vienādu ar False .
Apmācības soļi
- Dialoglodziņā noklikšķiniet uz līnijas Range_lookup
- Šajā rindā ierakstiet vārdu Nepareizs , lai norādītu, ka mēs VLOOKUP vēlamies atgriezt precīzo atbilstību meklētajiem datiem
- Noklikšķiniet uz Labi, lai pabeigtu meklēšanas formulu un aizveriet dialoglodziņu
- Tā kā mēs vēl neesam iekļāvuši uzmeklēšanas kritērijus šūnā D2, šūnā E2 parādīsies # N / A kļūda
- Šī kļūda tiks labota, kad mēs pievienosim meklēšanas kritērijus pēdējā apmācības posmā
09 no 10
Meklēšanas formulas kopēšana ar aizpildīšanas rokturi
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 datu nolasīšanas datu 2., 3. Un 4. Ailes dati tiktu iegūti, ti, cena, daļas numurs un piegādātāja vārds, kad mēs ievadām daļas nosaukumu kā Lookup_value.
Tā kā dati ir izkārtoti parastajā rakstā darblapā , mēs varam kopēt meklēšanas formulu šūnā E2 uz šūnām F2 un G2.
Pēc formulas kopēšanas Excel atjauno relatīvo šūnu atsauci COLUMN funkcijā (B1), lai atspoguļotu formulas jauno atrašanās vietu.
Kā arī, Excel nemaina absolūto šūnu atsauci $ D $ 2 un nosaukto diapazona tabulu, jo formula tiek kopēta.
Excel datu kopēšanai ir vairāk nekā viens veids, bet varbūt vienkāršākais veids ir izmantot aizpildīšanas rīku .
Apmācības soļi
- Noklikšķiniet uz šūnas E2, kur atrodas meklēšanas formulas, lai padarītu to par aktīvo šūnu
- Peles kursoru ievietojiet melnajā kvadrāta apakšējā labajā stūrī. Indikators mainīsies uz pluszīmes zīmi " + " - tas ir aizpildīšanas rokturis
- Noklikšķiniet uz kreiso peles pogu un velciet aizpildīšanas rokturi pāri šūnai G2
- Atlaidiet peles pogu, un šūnā F3 ir jāietver divu dimensiju meklēšanas formulas
- Ja tas tiek izdarīts pareizi, šūnās F2 un G2 tagad jāietver arī # N / A kļūda, kas atrodas šūnā E2
10 no 10
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 nosaukumu, kuru vēlaties ielādēt meklējuma_vērtības šūnā (D2), un nospiediet tastatūras ENTER taustiņu.
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.
Apmācības soļi
- Noklikšķiniet uz šūnu D2 darblapā
- Ierakstiet logrīku šūnā D2 un nospiediet tastatūras ENTER taustiņu
- Šūnās no E2 līdz G2 jāparāda šāda informācija:
- 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
- Tālāk pārbaudiet VLOOKUP masīvu formulu, ierakstot citu daļu nosaukumu šūnā D2 un vērojot rezultātus šūnās no E2 līdz G2
Ja tiek parādīts kļūdas ziņojums, piemēram, #REF! parādās šūnās E2, F2 vai G2, šis VLOOKUP kļūdu ziņojumu saraksts var palīdzēt jums noteikt, kur atrodas problēma.