Apvienojot Excel funkciju VLOOKUP ar funkciju COLUMN, varat izveidot uzmeklēšanas formulu, kas atgriež vairākas vērtības no vienas datu bāzes vai datu tabulas rindas. Uzziniet, kā izveidot uzmeklēšanas formulu, kas atgriež vairākas vērtības no viena datu ieraksta.
Šajā rakstā sniegtie norādījumi attiecas uz Excel 2019, 2016, 2013, 2010; un Excel darbam ar Microsoft 365.
Bottom Line
Uzmeklēšanas formula paredz, ka funkcija COLUMN ir jāievieto VLOOKUP iekšpusē. Funkcijas ligzdošana ietver otrās funkcijas ievadīšanu kā vienu no argumentiem pirmajai funkcijai.
Ievadiet apmācības datus
Šajā apmācībā funkcija COLUMN ir ievadīta kā VLOOKUP kolonnas indeksa numura arguments. Pamācības pēdējā darbība ietver uzmeklēšanas formulas kopēšanu uz papildu kolonnām, lai izgūtu papildu vērtības izvēlētajai daļai.
Pirmais solis šajā apmācībā ir datu ievadīšana Excel darblapā. Lai izpildītu šajā apmācībā norādītās darbības, ievadiet tālāk attēlā redzamos datus šādās šūnās:
- Ievadiet augšējo datu diapazonu šūnās D1 līdz G1.
- Ievadiet otro diapazonu šūnās D4 līdz G10.
Šajā apmācībā izveidotie meklēšanas kritēriji un uzmeklēšanas formula ir ievadīti darblapas 2. rindā.
Šajā apmācībā nav iekļauts attēlā redzamais pamata Excel formatējums, taču tas neietekmē uzmeklēšanas formulas darbību.
Izveidot datu tabulai nosauktu diapazonu
Nosaukts diapazons ir vienkāršs veids, kā formulā atsaukties uz datu diapazonu. Tā vietā, lai rakstītu datu šūnu atsauces, ierakstiet diapazona nosaukumu.
Otra nosauktā diapazona izmantošanas priekšrocība ir tā, ka šī diapazona šūnu atsauces nekad nemainās pat tad, ja formula tiek kopēta citās darblapas šūnās. Diapazona nosaukumi ir alternatīva absolūtu šūnu atsaucēm, lai novērstu kļūdas, kopējot formulas.
Diapazona nosaukumā nav iekļauti datu virsraksti vai lauku nosaukumi (kā parādīts 4. rindā), tikai dati.
-
Darblapā iezīmējiet šūnas D5 līdz G10.
Image -
Novietojiet kursoru nosaukuma lodziņā, kas atrodas virs A kolonnas, ierakstiet Table, pēc tam nospiediet Enter. Šūnām D5 līdz G10 ir tabulas diapazona nosaukums.
Image - VLOOKUP tabulas masīva argumenta diapazona nosaukums tiks izmantots vēlāk šajā apmācībā.
Atveriet dialoglodziņu VLOOKUP
Lai gan uzmeklēšanas formulu ir iespējams ierakstīt tieši darblapas šūnā, daudziem cilvēkiem ir grūti saglabāt taisnu sintaksi - īpaši sarežģītai formulai, piemēram, šajā apmācībā izmantotajai.
Kā alternatīvu izmantojiet dialoglodziņu VLOOKUP Function Arguments. Gandrīz visām Excel funkcijām ir dialoglodziņš, kurā katrs funkcijas arguments tiek ievadīts atsevišķā rindā.
-
Atlasiet darblapas šūnu E2. Šī ir vieta, kur tiks parādīti divdimensiju uzmeklēšanas formulas rezultāti.
Image -
Lentē atveriet cilni Formulas un atlasiet Meklēt un atsauce.
Image -
Atlasiet VLOOKUP, lai atvērtu dialoglodziņu Funkciju argumenti.
Image - Funkciju argumentu dialoglodziņš ir vieta, kur tiek ievadīti funkcijas VLOOKUP parametri.
Ievadiet uzmeklēšanas vērtības argumentu
Parasti uzmeklēšanas vērtība atbilst datu laukam datu tabulas pirmajā kolonnā. Šajā piemērā uzmeklēšanas vērtība attiecas uz tās daļas nosaukumu, par kuru vēlaties atrast informāciju. Uzmeklēšanas vērtības pieļaujamie datu veidi ir teksta dati, loģiskās vērtības, skaitļi un šūnu atsauces.
Absolūtās šūnu atsauces
Kad formulas tiek kopētas programmā Excel, šūnu atsauces mainās, lai atspoguļotu jauno atrašanās vietu. Ja tā notiek, D2, šūnas atsauce uzmeklēšanas vērtībai, mainās un rada kļūdas šūnās F2 un G2.
Absolūtās šūnu atsauces nemainās, kad formulas tiek kopētas.
Lai novērstu kļūdas, pārveidojiet šūnas atsauci D2 par absolūtu šūnas atsauci. Lai izveidotu absolūtu šūnas atsauci, nospiediet taustiņu F4. Tādējādi ap šūnas atsauci tiek pievienotas dolāra zīmes, piemēram, $D$2.
-
Dialoglodziņā Funkciju argumenti novietojiet kursoru tekstlodziņā lookup_value. Pēc tam darblapā atlasiet cell D2, lai pievienotu šo šūnas atsauci lookup_value. Šūnā D2 tiks ievadīts daļas nosaukums.
Image -
Nepārvietojot ievietošanas punktu, nospiediet taustiņu F4, lai pārveidotu D2 par absolūto šūnas atsauci $D$2.
Image - Atstājiet atvērtu funkcijas VLOOKUP dialoglodziņu nākamajai apmācības darbībai.
Ievadiet tabulas masīva argumentu
Tabulas masīvs ir datu tabula, ko meklē uzmeklēšanas formula, lai atrastu vajadzīgo informāciju. Tabulas masīvā ir jābūt vismaz divām datu kolonnām.
Pirmajā kolonnā ir uzmeklēšanas vērtības arguments (kas tika iestatīts iepriekšējā sadaļā), savukārt otrā kolonna tiek meklēta pēc uzmeklēšanas formulas, lai atrastu jūsu norādīto informāciju.
Tabulas masīva arguments jāievada kā diapazons, kurā ir datu tabulas šūnu atsauces, vai kā diapazona nosaukums.
Lai pievienotu datu tabulu funkcijai VLOOKUP, novietojiet kursoru dialoglodziņā tekstlodziņā table_array un ierakstiet Table, lai ievadītu šī argumenta diapazona nosaukumu.
Ievietot kolonnas funkciju
Parasti VLOOKUP atgriež datus tikai no vienas datu tabulas kolonnas. Šo kolonnu iestata kolonnas indeksa numura arguments. Tomēr šajā piemērā ir trīs kolonnas, un kolonnas indeksa numurs ir jāmaina, nerediģējot uzmeklēšanas formulu. Lai to paveiktu, ievietojiet funkciju COLUMN funkcijā VLOOKUP kā argumentu Col_index_num.
Gigzdojot funkcijas, programma Excel neatver otrās funkcijas dialoglodziņu, lai ievadītu tās argumentus. Funkcija COLUMN jāievada manuāli. Funkcijai COLUMN ir tikai viens arguments, atsauces arguments, kas ir šūnas atsauce.
Funkcija COLUMN atgriež kolonnas numuru, kas norādīts kā atsauces arguments. Tas pārvērš kolonnas burtu par skaitli.
Lai uzzinātu preces cenu, izmantojiet datus datu tabulas 2. ailē. Šajā piemērā kā atsauce tiek izmantota kolonna B, lai argumentā Col_index_num ievietotu 2.
-
Dialoglodziņā Funkciju argumenti novietojiet kursoru tekstlodziņā Col_index_num un ierakstiet COLUMN(. (Noteikti iekļaujiet atvērto apaļo iekava.)
Image -
Darblapā atlasiet šūna B1, lai ievadītu šīs šūnas atsauci kā atsauces argumentu.
Image - Ierakstiet noslēdzošo apaļo iekavu, lai pabeigtu funkciju COLUMN.
Ievadiet VLOOKUP diapazona uzmeklēšanas argumentu
VLOOKUP arguments Range_lookup ir loģiska vērtība (TRUE vai FALSE), kas norāda, vai VLOOKUP ir jāatrod precīza vai aptuvena atbilstība Lookup_value.
- TRUE vai Izlaists: VLOOKUP atgriež tuvu meklēšanas_vērtības atbilstību. Ja precīza atbilstība netiek atrasta, VLOOKUP atgriež nākamo lielāko vērtību. Dati tabulas_masīva pirmajā kolonnā ir jākārto augošā secībā.
- FALSE: VLOOKUP izmanto precīzu atbilstības vērtību Lookup_value. Ja Table_array pirmajā kolonnā ir divas vai vairākas vērtības, kas atbilst uzmeklēšanas vērtībai, tiek izmantota pirmā atrastā vērtība. Ja precīza atbilstība netiek atrasta, tiek atgriezta kļūda N/A.
Šajā apmācībā tiks meklēta konkrēta informācija par konkrētu aparatūras vienumu, tāpēc diapazona_meklējums ir iestatīts uz FALSE.
Dialoglodziņā Funkciju argumenti novietojiet kursoru tekstlodziņā Range_lookup un ierakstiet False, lai liktu VLOOKUP atgriezt precīzu datu atbilstību.
Atlasiet OK, lai pabeigtu uzmeklēšanas formulu un aizvērtu dialoglodziņu. Šūnā E2 būs kļūda N/A, jo uzmeklēšanas kritēriji nav ievadīti šūnā D2. Šī kļūda ir īslaicīga. Tas tiks labots, kad šīs apmācības pēdējā darbībā tiks pievienoti uzmeklēšanas kritēriji.
Nokopējiet uzmeklēšanas formulu un ievadiet kritērijus
Uzmeklēšanas formula izgūst datus no vairākām datu tabulas kolonnām vienlaikus. Lai to izdarītu, uzmeklēšanas formulai ir jāatrodas visos laukos, no kuriem vēlaties iegūt informāciju.
Lai izgūtu datus no datu tabulas 2., 3. un 4. kolonnas (cena, daļas numurs un piegādātāja nosaukums), ievadiet daļēju nosaukumu kā Lookup_value.
Tā kā dati darblapā ir izkārtoti parastajā shēmā, kopējiet uzmeklēšanas formulu šūnā E2 uz cells F2 un G2 Kad formula tiek kopēta, programma Excel atjaunina relatīvo šūnas atsauci funkcijā COLUMN (šūna B1), lai atspoguļotu formulas jauno atrašanās vietu. Kopējot formulu, programma Excel nemaina absolūtās šūnas atsauci (piemēram, $D$2) un nosaukto diapazonu (tabulu).
Ir vairāk nekā viens veids, kā kopēt datus programmā Excel, taču vienkāršākais veids ir izmantot aizpildīšanas turi.
-
Atlasiet šūnu E2, kur atrodas uzmeklēšanas formula, lai padarītu to par aktīvo šūnu.
Image -
Velciet aizpildīšanas turi pāri uz šūnu G2. Šūnās F2 un G2 tiek parādīta kļūda N/A, kas ir šūnā E2.
Image -
Lai izmantotu uzmeklēšanas formulas informācijas izgūšanai no datu tabulas, darblapā atlasiet šūna D2, ierakstiet Widget un nospiediet Ievadiet.
Image Šūnās E2 līdz G2 tiek parādīta šāda informācija.
- E2: 14,76 $ - logrīka cena
- F2: PN-98769 - logrīka daļas numurs
- G2: Widgets Inc. - logrīku piegādātāja nosaukums
-
Lai pārbaudītu VLOOKUP masīva formulu, šūnā D2 ierakstiet citu daļu nosaukumus un novērojiet rezultātus šūnās E2 līdz G2.
Image - Katrā šūnā, kurā ir uzmeklēšanas formula, ir dažādi dati par meklēto aparatūras vienumu.
Funkcija VLOOKUP ar ligzdotām funkcijām, piemēram, COLUMN, nodrošina efektīvu metodi datu meklēšanai tabulā, izmantojot citus datus kā uzmeklēšanas atsauci.
