Funkcija VLOOKUP vienmēr ir bijusi viena no Excel jaudīgākajām funkcijām. Tas ļauj meklēt vērtības tabulas pirmajā kolonnā un atgriezt vērtības no laukiem labajā pusē. Bet programmā Excel ir arī funkcija XLOOKUP, kas ļauj meklēt vērtību jebkurā kolonnā vai rindā un atgriezt datus no jebkuras citas kolonnas.
Kā darbojas XLOOKUP
Funkciju XLOOKUP ir daudz vieglāk izmantot nekā funkciju VLOOKUP, jo tā vietā, lai norādītu rezultātu kolonnas vērtību, varat norādīt visu diapazonu.
Funkcija arī ļauj meklēt gan kolonnā, gan rindā, nosakot vērtību krustojošā šūnā.
Funkcijas XLOOKUP parametri ir šādi:
=XLOOKUP (uzmeklēšanas_vērtība, uzmeklēšanas_masīvs, atgriešanas_masīvs, [match_mode], [search_mode])
- lookup_value: vērtība, kuru vēlaties meklēt
- lookup_array: masīvs (kolonna), kurā vēlaties meklēt
- return_array: rezultāts (kolonna), kuru vēlaties izgūt no
- match_mode (neobligāti): atlasiet precīzu atbilstību (0), precīzo atbilstību vai nākamo mazāko vērtību (-1) vai aizstājējzīmes atbilstību (2).
- search_mode (neobligāti): atlasiet, vai meklēt, sākot ar pirmo vienumu kolonnā (1), ar pēdējo kolonnas vienumu (-1), bināro meklēšanu augošā secībā. (2) vai binārā meklēšana dilstošā secībā (-2).
Tālāk ir norādīti daži no visizplatītākajiem meklējumiem, ko varat veikt, izmantojot funkciju XLOOKUP.
Kā meklēt vienu rezultātu, izmantojot XLOOKUP
Vienkāršākais veids, kā izmantot XLOOKUP, ir meklēt vienu rezultātu, izmantojot datu punktu no vienas kolonnas.
-
Šajā izklājlapas paraugā ir pārdošanas pārstāvju iesniegto pasūtījumu saraksts, tostarp prece, vienību skaits, izmaksas un kopējais pārdošanas apjoms.
Image -
Ja vēlaties atrast pirmo izpārdošanu sarakstā, ko iesniedzis konkrēts tirdzniecības pārstāvis, varat izveidot funkciju XLOOKUP, kas kolonnā Rep meklē vārdu. Funkcija atgriezīs rezultātu no kolonnas Kopā. XLOOKUP funkcija šim ir:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: norāda uz Rep Name meklēšanas šūnu
- C2:C44: šī ir kolonna Rep, kas ir uzmeklēšanas masīvs
- G2:G33: šī ir kolonna Kopā, kas ir atgriešanas masīvs
- 0: atlasa precīzu atbilstību
- 1: atlasa pirmo atbilstību rezultātos
-
Kad nospiežat Enter un ierakstāt tirdzniecības pārstāvja nosaukumu, šūnā Kopējais rezultāts tiks parādīts pirmais šī tirdzniecības pārstāvja rezultāts tabulā.
Image -
Ja vēlaties meklēt pēdējo izpārdošanu (tā kā tabula ir sakārtota pēc datuma apgrieztā secībā), mainiet pēdējo XLOOKUP argumentu uz - 1, kas sāksies. meklēšanu no pēdējās šūnas uzmeklēšanas masīvā un tā vietā sniedziet jums šo rezultātu.
Image -
Šajā piemērā ir parādīta līdzīga meklēšana, ko varētu veikt ar funkciju VLOOKUP, izmantojot kolonnu Rep kā uzmeklēšanas tabulas pirmo kolonnu. Tomēr XLOOKUP ļauj meklēt jebkuru kolonnu abos virzienos. Piemēram, ja vēlaties atrast tirdzniecības pārstāvi, kurš pārdeva pirmo Binder pasūtījumu gadā, izmantojiet šādu funkciju XLOOKUP:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: norāda uz vienumu meklēšanas šūnu
- D2:D44: šī ir vienumu kolonna, kas ir uzmeklēšanas masīvs
- C2:C44: šī ir kolonna Rep, kas ir atgriešanas masīvs pa kreisi no uzmeklēšanas masīva
- 0: atlasa precīzu atbilstību
- 1: atlasa pirmo atbilstību rezultātos
-
Šoreiz rezultāts būs tā tirdzniecības pārstāvja vārds, kurš pārdeva gada pirmo stiprinājuma pasūtījumu.
Image
Veikt vertikālo un horizontālo saskaņošanu ar XLOOKUP
Vēl viena XLOOKUP iespēja, ko VLOOKUP nespēj, ir iespēja veikt gan vertikālu, gan horizontālu meklēšanu, kas nozīmē, ka varat meklēt vienumu kolonnā un arī visā rindā.
Šī dubultās meklēšanas funkcija efektīvi aizstāj citas Excel funkcijas, piemēram, INDEX, MATCH vai HLOOKUP.
-
Šajā izklājlapas piemērā katra tirdzniecības pārstāvja pārdošanas apjomi ir sadalīti pa ceturkšņiem. Ja vēlaties skatīt trešā ceturkšņa pārdošanas apjomu konkrētam tirdzniecības pārstāvim bez XLOOKUP funkcijas, šāda veida meklēšana būtu sarežģīta.
Image -
Ar XLOOKUP funkciju šāda veida meklēšana ir vienkārša. Izmantojot šo funkciju XLOOKUP, varat meklēt trešā ceturkšņa pārdošanas datus konkrētam tirdzniecības pārstāvim:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: norāda uz Rep meklēšanas šūnu
- B2:B42: šī ir vienumu kolonna, kas ir kolonnu uzmeklēšanas masīvs
- K2: norāda uz ceturkšņa meklēšanas šūnu
- C1:H1: Šis ir rindu uzmeklēšanas masīvs
- C2:H42: Šis ir uzmeklēšanas masīvs dolāra summai katrā ceturksnī
Šī ligzdotā XLOOKUP funkcija vispirms identificē pārdošanas pārstāvi, un nākamā funkcija XLOOKUP identificē vēlamo ceturksni. Atgriešanas vērtība būs šūna, kurā šīs divas pārtver.
-
Šīs formulas rezultāts ir ceturkšņa pirmā peļņa pārstāvim ar vārdu Tompsons.
Image
Funkcijas XLOOKUP izmantošana
Funkcija XLOOKUP ir pieejama tikai Office Insider abonentiem, taču drīzumā tā tiks ieviesta visiem Microsoft 365 abonentiem.
Ja vēlaties pats pārbaudīt funkciju, varat kļūt par Office Insider. Atlasiet File > Account, pēc tam atlasiet nolaižamo izvēlni Office Insider, lai abonētu.
Kad pievienosities programmai Office Insider, jūsu instalētā Excel versija saņems visus jaunākos atjauninājumus, un jūs varat sākt izmantot funkciju XLOOKUP.
