Kā lietot XLOOKUP funkciju programmā Excel 2016

Programma Excel 2016 Office 365 abonentiem operētājsistēmās Windows un Mac tagad atbalsta jaunu XLOOKUP funkciju, kas tiek uzskatīta par ievērojami vienkāršāku un daudzpusīgāku ļoti populārās (bet bieži vien ļaunprātīgās) vertikālās uzmeklēšanas funkcijas VLOOKUP (nezinu, kas XLOOKUP ir X) aizstājējs. apzīmē; plašs, varbūt?).

Tiem no jums, kas vēl nav pazīstami ar VLOOKUP (kas tiek uzskatīta par trešo visbiežāk izmantoto funkciju tūlīt pēc SUM un AVERAGE), šī funkcija veic meklēšanu vertikāli pēc rindas norādītās uzmeklēšanas tabulas kreisajā kolonnā no augšas uz leju, līdz tā atrod vērtību uzmeklēšanas kolonna, kas apzīmēta ar nobīdes skaitli, kas atbilst vai pārsniedz to, kuru meklējat. Lai gan funkcijai VLOOKUP ir ļoti noderīga, lai atrastu konkrētus vienumus garā datu tabulas sarakstā vai kolonnā jūsu darblapā, tai ir vairāki ierobežojumi, kas nav koplietoti šai jaunajai uzmeklēšanas funkcijai, piemēram, XLOOKUP:

• Pēc noklusējuma tiek meklētas precīzas atbilstības jūsu uzmeklēšanas vērtībai uzmeklēšanas diapazonā
• Var tabulā meklēt gan vertikāli (pēc rindas), gan horizontāli (pēc kolonnas), tādējādi aizstājot nepieciešamību izmantot HLOOKUP funkciju, veicot meklēšanu horizontāli pēc kolonnas
• Var meklēt pa kreisi vai pa labi, lai uzmeklēšanas diapazonam jūsu uzmeklēšanas tabulā nav jāatrodas kolonnā pa kreisi no tās, kas norādīta kā atgriešanas diapazons, lai funkcija darbotos.
• Ja tiek izmantota precīzās atbilstības noklusējuma vērtība, tas darbojas pat tad, ja uzmeklēšanas diapazona vērtības nav sakārtotas noteiktā secībā
• Var meklēt no apakšējās rindas uz augšu uzmeklēšanas masīva diapazonā, izmantojot izvēles meklēšanas režīma argumentu

Funkcijai XLOOKUP ir pieci iespējamie argumenti, no kuriem pirmie trīs ir obligāti un pēdējie divi nav obligāti, izmantojot šādu sintaksi:

XLOOKUP ( uzmeklējamā_vērtība , pārlūkošanas_masīvs , return_array , [ match_mode ], [ search_mode ])

Nepieciešamais arguments lookup_value apzīmē vērtību vai vienumu, kuru meklējat. Nepieciešamais look_up masīva arguments apzīmē šūnu diapazonu, kurā jāmeklē šī uzmeklēšanas vērtība, un return_array arguments apzīmē šūnu diapazonu, kurā ir vērtība, kuru vēlaties atgriezt, kad Excel atrod precīzu atbilstību.

* Ņemiet vērā, ka, funkcijā XLOOKUP norādot argumentus lookup_array un return_array, abiem diapazoniem ir jābūt vienāda garuma, pretējā gadījumā programma Excel atgriezīs vērtību #VALUE! kļūda jūsu formulā. Tas ir vēl jo vairāk iemesls, kāpēc, definējot šos argumentus, ir jāizmanto noteiktas datu tabulas diapazonu vai kolonnu nosaukumi, nevis jānorāda uz tiem vai jāieraksta to šūnu atsauces .

Izvēles arguments match_mode var ietvert jebkuru no šīm četrām vērtībām:

• 0 precīzai atbilstībai (noklusējums, tāds pats kā tad, ja nav norādīts arguments match_mode )
• -1 precīzai atbilstībai vai nākamajai mazākai vērtībai
• 1 — precīzai atbilstībai vai nākamajai lielākai vērtībai
• 2 daļējai atbilstībai, izmantojot aizstājējzīmes, kas pievienotas šūnas atsaucei argumentā lookup_value

Izvēles search_mode arguments var būt jebkura no šīm četrām vērtībām:

• 1, lai meklētu no pirmā līdz pēdējam, tas ir, no augšas uz leju (noklusējums, tāds pats kā tad, ja nav norādīts neviens meklēšanas_mode arguments)
• -1, lai meklētu no pēdējās uz pirmo, tas ir, no apakšas uz augšu
• 2 binārajai meklēšanai augošā secībā
• -2 binārajai meklēšanai dilstošā secībā

Labākais veids, kā izprast jaunās funkcijas XLOOKUP jaudu un daudzpusību, ir redzēt to darbībā Excel darblapā. Nākamajā attēlā man ir darblapa ar vienkāršu 2019. gada pārdošanas datu tabulu, kas sakārtota pa valstīm. Lai izmantotu XLOOKUP, lai atgrieztu kopējo pārdošanas apjomu no šīs tabulas šūnā E4, pamatojoties uz valsti, kuru ievadāt darblapas šūnā D4, veiciet šādas darbības:

Novietojiet šūnas kursoru darblapas šūnā E4

Noklikšķiniet uz opcijas Meklēt un atsauce cilnē Formulas, pēc tam uz XLOOKUP nolaižamās izvēlnes apakšdaļā, lai atvērtu dialoglodziņu Funkciju argumenti.

Darblapā noklikšķiniet uz šūnas D4, lai teksta lodziņā Lookup_value argumentu ievadītu tās atsauci uz šūnu.

Nospiediet Tab, lai atlasītu argumentu Lookup_array tekstlodziņu, pēc tam noklikšķiniet uz šūnas A4 un turiet nospiestu taustiņu Shift, vienlaikus nospiežot taustiņu Ctrl un lejupvērsto bultiņu, lai atlasītu A4:A8 kā meklēšanas diapazonu (jo diapazons A3:B8 ir definēts kā Excel datu tabula, Tabula 1 [Valsts] parādās tekstlodziņā diapazona A4:A8 vietā).

Nospiediet Tab, lai atlasītu argumentu Return_array tekstlodziņu, pēc tam noklikšķiniet uz šūnas B4 un turiet nospiestu taustiņu Shift, vienlaikus nospiežot taustiņu Ctrl un lejupvērsto bultiņu, lai atlasītu B4:B8 kā diapazonu, kurā ir vērtības, kas jāatgriež, pamatojoties uz meklēšanas rezultātiem (kas parādās kā 1. tabula [Kopējie pārdošanas apjomi] tekstlodziņā).

Noklikšķiniet uz Labi, lai ievadītu XLOOKUP formulu šūnā E4.

Formulas izveide ar XLOOKUP šūnā E4, kas atgriež pārdošanas apjomu, pamatojoties uz šūnā D4 ievadīto valsti.

Excel ievada XLOOKUP formulu darblapas šūnā E4 un kā rezultātu atgriež 4900, jo Kostarika pašlaik ir ievadīta uzmeklēšanas šūnā D4, un, kā redzat 2019. gada pārdošanas tabulā, tas patiešām ir kopējais pārdošanas apjoms šajā valstī.

Tā kā XLOOKUP darbojas no labās puses uz kreiso tikpat labi kā no kreisās uz labo pusi, jūs varat izmantot šo funkciju, lai atgrieztu valsti no šīs pārdošanas tabulas, pamatojoties uz konkrētu pārdošanas rādītāju. Nākamajā attēlā parādīts, kā to izdarīt. Šoreiz jūs izveidojat formulu XLOOKUP šūnā D4 un apzīmējat šūnā E4 ievadīto vērtību (šajā gadījumā 11 000) kā argumentu lookup_value.

Turklāt jūs ievadāt -1 kā argumentu match_mode, lai ignorētu funkcijas precīzās atbilstības noklusējuma vērtību, lai programma Excel atgrieztu valsti ar precīzu atbilstību pārdošanas vērtībai, kas ievadīta uzmeklēšanas šūnā E4, vai valstij ar nākamo zemāko kopējo pārdošanas apjomu (Meksika ar 10 000 USD šajā gadījumā, jo šajā tabulā nav nevienas valsts ar 11 000 USD no kopējā pārdošanas apjoma). Ja šai formulai nav norādīts arguments match_mode, programma Excel kā rezultātu atgrieztu #NA, jo šajā pārdošanas tabulā nav precīzas atbilstības ar 11 000 USD.

Formulas izveide ar XLOOKUP šūnā D4, kas atgriež valsti, pamatojoties uz šūnā E4 ievadītajiem pārdošanas apjomiem

Tā kā funkcija XLOOKUP ir vienlīdz ērta, meklējot gan horizontāli pēc kolonnas, gan vertikāli pēc rindas, varat to izmantot, lai izveidotu formulu, kas veic divvirzienu meklēšanu (aizvietojot nepieciešamību izveidot formulu, kas apvieno funkcijas INDEX un MATCH kā pagātnē). Nākamajā attēlā, kurā ir 2019. gada ražošanas grafika tabula detaļu numuriem no AB-100 līdz AB-103 no aprīļa līdz decembrim, ir parādīts, kā tas tiek darīts.

Formulas izveide ar ligzdotām XLOOKUP funkcijām, lai atgrieztu detaļai saražoto vienību skaitu noteiktā mēnesī

Šūnā B12 es izveidoju šādu formulu:

=XLOOKUP(daļa_uzmeklēšana,$A$3:$A$6,XLOOKUP(datuma_uzmeklēšana,$B$2:$J$2,$B$3:$J$6))

Šī formula sākas, definējot funkciju XLOOKUP, kas vertikāli meklē pēc rindas precīzu atbilstību daļas ierakstam, kas veikts šūnā ar nosaukumu part_lookup (šūna B10, šajā gadījumā) ražošanas tabulas šūnu diapazonā $A$3:$A$6. . Tomēr ņemiet vērā, ka šīs sākotnējās funkcijas LOOKUP arguments return_array pats par sevi ir otrā funkcija XLOOKUP.

Šī otrā, ligzdotā funkcija XLOOKUP meklē šūnu diapazonu $B$2:$J$2 horizontāli pēc kolonnas, lai atrastu precīzu atbilstību datuma ierakstam, kas veikts šūnā ar nosaukumu date_lookup (šūna B11, šajā gadījumā). Šīs otrās, ligzdotās funkcijas XLOOKUP arguments return_array ir $B$3:$J$6 — visu tabulas ražošanas vērtību šūnu diapazons.

Šīs formulas darbības veids ir tāds, ka Excel vispirms aprēķina otrās, ligzdotās funkcijas XLOOKUP rezultātu, veicot horizontālu meklēšanu, kas šajā gadījumā atgriež masīvu 19. jūnija kolonnas šūnu diapazonā D3: D6 (ar vērtībām: 438, 153, 306 un 779) kā rezultātu. Šis rezultāts, savukārt, kļūst par argumentu return_array oriģinālajai funkcijai XLOOKUP, kas veic vertikālu meklēšanu pēc rindas, lai atrastu precīzu atbilstību daļas numura ierakstam, kas veikts šūnā B11 (nosaukts part_lookup). Tā kā šajā piemērā šī part_lookup šūna satur AB-102, formula atgriež tikai 19. jūnija ražošanas vērtību 306 no otrās, nākamās funkcijas XLOOKUP rezultāta.

Tur jums tas ir! Pirmais ieskats XLOOKUP — jaudīgā, daudzpusīgā un diezgan viegli lietojamā jaunā uzmeklēšanas funkcija, kas var veikt ne tikai vienas vērtības uzmeklēšanu, ko veic funkcijas VLOOKUP un HLOOKUP, bet arī divvirzienu vērtību meklēšanu, ko veic, apvienojot INDEX un MATCH funkcijas.

* Diemžēl funkcija XLOOKUP nav saderīga ar iepriekšējām Microsoft Excel versijām, kas atbalsta tikai funkcijas VLOOKUP un HLOOKUP, vai saderīga ar pašreizējām versijām, kurās tā vēl nav iekļauta kā viena no uzmeklēšanas funkcijām, piemēram, Excel 2019 un Excel Online . Tas nozīmē, ka, ja koplietojat darbgrāmatu, kurā ir XLOOKUP formulas, ar kolēģiem vai klientiem, kuri izmanto Excel versiju, kurā nav iekļauta šī jaunā uzmeklēšanas funkcija, visas šīs formulas atgriezīs #NAME? kļūdu vērtības, atverot tās darblapu.