1. ProgrammatūraMicrosoft OfficeExcelKā lietot funkciju XLOOKUP programmā Excel 2016

Autors: Gregs Hārvijs

Excel 2016 Office 365 abonentiem operētājsistēmās Windows un Mac tagad atbalsta jaunu XLOOKUP funkciju, kas tiek nosaukta par ievērojami vienkāršāku un daudzpusīgāku ļoti populārās (vēl bieži ļaundabīgās) vertikālās meklēšanas funkcijas VLOOKUP nomaiņu (nezinu, kas XOOKUP ir X apzīmē; iespējams, ekstensīvs?).

Tiem no jums, kas vēl nav pazīstami ar VLOOKUP (tiek uzskatīta par trešo visbiežāk izmantoto funkciju uzreiz pēc SUM un AVERAGE), šī funkcija meklē vertikāli pa rindām norādītās uzmeklēšanas tabulas kreisākajā kolonnā no augšas uz leju, līdz tā atrod vērtību uzmeklēšanas kolonna, kas apzīmēta ar nobīdes numuru, kas atbilst vai pārsniedz jūsu meklēto. Lai arī funkcija VLOOKUP ir ārkārtīgi noderīga, lai noteiktu atsevišķus vienumus tabulas garā sarakstā vai datu tabulas ailē, tai ir vairāki ierobežojumi, kas nav kopīgi ar šo jauno uzmeklēšanas funkciju, piemēram, XLOOKUP:

  • Noklusējumi, lai meklēšanas diapazonā atrastu precīzas jūsu meklēšanas vērtības atbilstības Var tabulā meklēt gan vertikāli (pēc rindas), gan horizontāli (pēc kolonnas), tādējādi aizstājot vajadzību izmantot funkciju HLOOKUP, meklējot horizontāli pēc kolonnas Var meklēt pa kreisi vai pa labi, lai meklēšanas diapazonam jūsu meklēšanas tabulā nebūtu jāatrodas kolonnā pa kreisi no tās, kas norādīta kā atgriešanās diapazons, lai funkcija darbotos Ja tiek izmantota precīza atbilstības noklusējuma funkcija, tā darbojas arī tad, ja vērtības meklēšanas diapazonā nav sakārtotas noteiktā secībā Var meklēt no apakšējās rindas uz augšu meklēš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 nepieciešami, bet pēdējie divi nav obligāti, izmantojot šādu sintakse:

XLOOKUP (uzmeklēšanas_vērtība, uzmeklēšanas_masīvs, atgriešanās_arājs, [spēles_mode], [meklēšanas_mode])

Nepieciešamais lookup_value arguments norāda vērtību vai vienumu, kuru meklējat. Nepieciešamais look_up masīva arguments norāda šūnu diapazonu, kas jāmeklē šai uzmeklēšanas vērtībai, un return_array arguments apzīmē šūnu diapazonu, kas satur vēlamo vērtību, ja Excel tiek atrasts precīzs.

* Ņemiet vērā, kad XLOOKUP funkcijā iezīmējat lookup_array un return_array argumentus, abiem diapazoniem jābūt vienāda garuma, pretējā gadījumā Excel atgriezīs #VALUE! kļūda jūsu formulā. Tas ir vēl jo vairāk tāpēc, ka, definējot šos argumentus, nevis norādot tos vai ierakstot viņu šūnu atsaucēs, norādīto datu tabulas diapazonu nosaukumus vai kolonnu nosaukumus izmantojat.

Izvēles mačs_mode arguments var saturēt kādu no šīm četrām vērtībām:

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

Neobligāts arguments_mode var saturēt jebkuru 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, kad nav norādīts meklēšanas_mode arguments) -1, lai meklētu pēdējais līdz pirmajam, tas ir, no apakšas uz augšu 2 binārai meklēšanai augošā secībā -2 binārai meklēšanai dilstošā secībā

Labākais veids, kā izprast jaunās XLOOKUP funkcijas 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ījāt darblapas D4 šūnā, rīkojieties šādi:

  1. Novietojiet šūnas kursoru darblapas E4 Noklikšķiniet uz opcijas Meklēt un atsauces cilnē Formulas un pēc tam XLOOKUP nolaižamās izvēlnes apakšdaļā, lai atvērtu dialoglodziņu Funkcijas argumenti. Darblapā noklikšķiniet uz šūnas D4, lai ievadītu tās šūnas atsauci argumentu meklēšanas lodziņā_vērtība. Nospiediet tabulēšanas taustiņu, lai atlasītu teksta lodziņu Arhitekta argumentu meklēšana, pēc tam noklikšķiniet uz šūnas A4 un turiet nospiestu taustiņu Shift, nospiežot Ctrl uz leju, lai atlasītu diapazonu A4: A8 kā meklējamo diapazonu (jo diapazons A3: B8 tiek definēts kā Excel datu tabula, 1. tabula [Valsts] parādās tekstlodziņā diapazona A4: A8) vietā. Nospiediet taustiņu Tab, lai atlasītu teksta lodziņu Return_array, pēc tam noklikšķiniet uz šūnas B4 un turiet nospiestu taustiņu Shift, nospiežot Ctrl uz leju, lai atlasītu B4: B8 kā diapazonu, kas satur vērtības, kas jāatgriež, pamatojoties uz meklēšanas rezultātiem (kas parādās kā 1. tabula [Kopējais pārdošanas apjoms] tekstlodziņā).

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

XLOOKUP formulas izveidošana

Excel ievada XLOOKUP formulu darblapas šūnā E4 un rezultātā iegūst 4900, jo Kostarika šobrīd ir ievadīta uzmeklēšanas šūnā D4 un, kā jūs varat redzēt 2019. gada pārdošanas tabulā, tas patiešām ir kopējais pārdošanas apjoms šai valstij.

Tā kā XLOOKUP darbojas no labās uz kreiso pusi tikpat labi kā no kreisās uz labo, varat šo funkciju izmantot tikpat labi, lai atgrieztos valsti no šīs pārdošanas tabulas, pamatojoties uz konkrētu pārdošanas skaitli. Šis attēls parāda, kā jūs to darāt. Šoreiz šūnā D4 jūs izveidojat formulu XLOOKUP un šūnā E4 ievadīto vērtību (šajā gadījumā - 11 000) norādāt kā lookup_value argumentu.

Turklāt kā match_mode argumentu jūs ievadāt -1, lai ignorētu funkcijas precīzās atbilstības noklusējumu, lai Excel atgrieztu valsti ar precīzu atbilstību pārdošanas vērtībai, kas ievadīta uzmeklēšanas šūnā E4, vai valsti, kurai ir nākamais zemākais kopējais pārdošanas apjoms (Meksika ar Šajā gadījumā USD 10 000, jo šajā tabulā nav nevienas valsts ar 11 000 USD no kopējā pārdošanas apjoma). Neizveidojot šai formulai atbilstošu modeli, Excel rezultātā iegūs #NA, jo šajā pārdošanas tabulā nav precīza atbilstība 11 000 USD.

XLOOKUP formula D4

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

ligzdotas XLOOKUP funkcijas

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

= XLOOKUP (part_lookup, $ A $ 3: $ A $ 6, XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

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

Šī otrā ligzdotā funkcija XLOOKUP horizontāli pēc kolonnas meklē šūnu diapazonu $ B $ 2: $ J $ 2, lai iegūtu precīzu atbilstību datuma ierakstam, kas veikts šūnā ar nosaukumu date_lookup (šajā gadījumā šūna B11). Return_array arguments šai otrajai, ligzdotajai XLOOKUP funkcijai 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 XLOOKUP funkcijas rezultātu, veicot horizontālu meklēšanu, kas šajā gadījumā atgriež masīvu šūnas diapazonā D3: D6 no kolonnas Jun-19 (ar vērtībām: 438, 153, 306 un 779). Šis rezultāts, savukārt, kļūst par return_array argumentu sākotnējai XLOOKUP funkcijai, kas vertikāli meklē pēc rindas precīzu atbilstību detaļas numura ierakstam, kas veikts šūnā B11 (nosaukts part_lookup). Tā kā šajā piemērā šajā part_lookup šūnā ir AB-102, formula atgriež tikai Jun-19 produkcijas vērtību 306 no otrās, nākamās XLOOKUP funkcijas rezultāta.

Tur jums tas ir! Pirmais skatījums uz XLOOKUP - jaudīgu, daudzpusīgu un diezgan ērti lietojamu jauno uzmeklēšanas funkciju, kas var ne tikai veikt vienas vērtības meklēšanu, ko veic funkcijas VLOOKUP un HLOOKUP, bet arī divpusējus vērtību meklēšanu, apvienojot INDEX un MATCH funkcijas, kā arī.

* Diemžēl XLOOKUP funkcija nav savietojama ar iepriekšējām Microsoft Excel versijām, kas atbalsta tikai funkcijas VLOOKUP un HLOOKUP, vai ir savietojama ar pašreizējām versijām, kuras to vēl neietver kā vienu no savām meklēšanas funkcijām, piemēram, Excel 2019 un Excel Online. Tas nozīmē, ka, ja jūs kopīgojat darbgrāmatu ar XLOOKUP formulām 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.