Kā izmantot Excel XLOOKUP funkciju -

Kopsavilkums

Excel XLOOKUP funkcija ir mūsdienīgs un elastīgs vecāku funkciju, piemēram, VLOOKUP, HLOOKUP un LOOKUP, aizstājējs. XLOOKUP atbalsta aptuvenu un precīzu atbilstību, aizstājējzīmes (*?) Daļējai atbilstībai un uzmeklēšanu vertikālos vai horizontālos diapazonos.

Mērķis

Uzmeklēšanas vērtības diapazonā vai masīvā

Atgriešanās vērtība

Atbilstošās masīva (-u) vērtība (-as)

Sintakse

= XLOOKUP (uzmeklēšana, uzmeklēšanas_zīmējums, atdošanas_izdevums, (nav atrasts), (atbilstības_režīms), (meklēšanas_raksts)

Argumenti

  • uzmeklēšana - uzmeklēšanas vērtība.
  • lookup_array - meklējamais masīvs vai diapazons.
  • return_array - masīvs vai diapazons, lai atgrieztos.
  • not_found - (pēc izvēles) Vērtība, kas jāatgriež, ja nav atrasta atbilstība.
  • match_mode - (pēc izvēles) 0 = precīza atbilstība (noklusējums), -1 = precīza atbilstība vai nākamā mazākā, 1 = precīza atbilstība vai nākamā lielākā, 2 = aizstājējzīme.
  • search_mode - (pēc izvēles) 1 = meklēšana no pirmās (noklusējums), -1 = meklēšana no pēdējās, 2 = binārā meklēšana augošā, -2 = binārā meklēšana lejupejošā.

Versija

Excel 365

Lietošanas piezīmes

XLOOKUP ir mūsdienīgs funkcijas VLOOKUP aizstājējs. Tā ir elastīga un daudzpusīga funkcija, kuru var izmantot visdažādākajās situācijās.

XLOOKUP var atrast vērtības vertikālos vai horizontālos diapazonos, var veikt aptuvenas un precīzas atbilstības, kā arī daļējas atbilstības gadījumā aizstāj aizstājējzīmes (*?). Turklāt XLOOKUP var meklēt datus, sākot no pirmās vērtības vai pēdējās vērtības (skatiet atbilstības veidu un meklēšanas režīma informāciju zemāk). Salīdzinot ar vecākām funkcijām, piemēram, VLOOKUP, HLOOKUP un LOOKUP, XLOOKUP piedāvā vairākas galvenās priekšrocības.

Nav atrasts ziņojums

Ja XLOOKUP nevar atrast atbilstību, tas atgriež kļūdu # N / A, tāpat kā citas atbilstības funkcijas programmā Excel. Atšķirībā no citām atbilstības funkcijām, XLOOKUP atbalsta izvēles argumentu ar nosaukumu not_found, kuru var izmantot, lai novērstu # N / A kļūdu, ja tā citādi parādās. Tipiskas not_found vērtības var būt "Not found", "No match", "No result" utt. Sniedzot not_found vērtību, pievienojiet tekstu divkāršās pēdiņās ("").

Piezīme. Esiet piesardzīgs, ja not_found ievadījāt tukšu virkni (""). Ja atbilstība nav atrasta, XLOOKUP # N / A vietā neparādīs neko. Ja vēlaties atrast kļūdu # N / A, ja atbilstība nav atrasta, pilnībā izlaidiet argumentu.

Spēles veids

Pēc noklusējuma XLOOKUP veiks precīzu atbilstību. Spēles uzvedību kontrolē izvēles arguments ar nosaukumu match_type, kuram ir šādas iespējas:

Spēles veids Uzvedība
0 (noklusējums) Precīza spēle. Atgriezīsies # N / A, ja nebūs atbilstības.
-1 Precīza atbilstība vai nākamais mazāks vienums.
1 Precīza atbilstība vai nākamais lielāks vienums.
2 Aizstājējzīmju spēle (*,?, ~)

Meklēšanas režīms

Pēc noklusējuma XLOOKUP sāks atbilstību no pirmās datu vērtības. Meklēšanas uzvedību kontrolē izvēles arguments ar nosaukumu search_mode , kas nodrošina šādas opcijas:

Meklēšanas režīms Uzvedība
1 (noklusējums) Meklēt no pirmās vērtības
-1 Meklēt no pēdējās vērtības (reversā)
2 Binārās meklēšanas vērtības sakārtotas augošā secībā
-2 Binārās meklēšanas vērtības sakārtotas dilstošā secībā

Binārie meklējumi ir ļoti ātri, taču dati ir jāšķiro pēc nepieciešamības. Ja dati netiek kārtoti pareizi, binārā meklēšana var atgriezt nederīgus rezultātus, kas izskatās pilnīgi normāli.

1. piemērs - pamata precīzā atbilstība

Pēc noklusējuma XLOOKUP veiks precīzu atbilstību. Šajā piemērā XLOOKUP tiek izmantots, lai izgūtu pārdošanas apjomu, pamatojoties uz precīzu Movie atbilstību. H5 formula ir:

=XLOOKUP(H4,B5:B9,E5:E9)

Detalizētāks skaidrojums šeit.

2. piemērs - pamata aptuvenā atbilstība

Lai iespējotu aptuvenu atbilstību, norādiet argumenta "match_mode" vērtību. Šajā piemērā XLOOKUP tiek izmantots, lai aprēķinātu atlaidi, pamatojoties uz daudzumu, kurai nepieciešama aptuvena atbilstība. Formula F5 nodrošina -1 vienumam match_mode, lai iespējotu aptuvenu atbilstību uzvedībai "precīza atbilstība vai nākamā mazākā":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Detalizētāks skaidrojums šeit.

3. piemērs - vairākas vērtības

XLOOKUP vienā un tajā pašā mačā var vienlaikus atgriezt vairāk nekā vienu vērtību. Tālāk sniegtajā piemērā parādīts, kā XLOOKUP var konfigurēt, lai atgrieztu trīs atbilstības vērtības ar vienu formulu. Formula C5 ir:

=XLOOKUP(B5,B8:B15,C8:E15)

Ievērojiet, ka atgriešanās masīvā (C8: E15) ir 3 kolonnas: Pirmais, Pēdējais, Departaments. Visas trīs vērtības tiek atgrieztas un izplūst diapazonā C5: E5.

4. piemērs - divvirzienu meklēšana

XLOOKUP var izmantot, lai veiktu divvirzienu meklēšanu, ievietojot vienu XLOOKUP citā. Zemāk esošajā piemērā "iekšējais" XLOOKUP izgūst visu rindu (visas Glass vērtības), kas tiek atgriezta "ārējā" XLOOKUP kā atgriešanas masīvs. Ārējā XLOOKUP atrod atbilstošo grupu (B) un kā gala rezultātu atgriež atbilstošo vērtību (17.25).

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Sīkāka informācija šeit.

5. piemērs - ziņojums nav atrasts

Tāpat kā citas uzmeklēšanas funkcijas, ja XLOOKUP neatrod vērtību, tā atgriež kļūdu # N / A. Lai parādītu pielāgotu ziņojumu # N / A vietā, norādiet izvēles argumenta "nav atrasts" vērtību, kas ievietots pēdiņās (""). Piemēram, lai parādītu “Not found”, kad nav atrasta atbilstoša filma, pamatojoties uz zemāk esošo darblapu, izmantojiet:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Jūs varat pielāgot šo ziņojumu, kā vēlaties: "Nav atbilstības", "Filma nav atrasta" utt.

6. piemērs - sarežģīti kritēriji

Izmantojot spēju dabiski apstrādāt masīvus, XLOOKUP var izmantot ar sarežģītiem kritērijiem. Šajā piemērā XLOOKUP atbilst pirmajam ierakstam, kur: konts sākas ar "x" un reģions ir "austrumi" un mēnesis nav aprīlis:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Sīkāka informācija: (1) vienkāršs piemērs, (2) sarežģītāks piemērs.

XLOOKUP priekšrocības

XLOOKUP piedāvā vairākas svarīgas priekšrocības, īpaši salīdzinot ar VLOOKUP:

  • XLOOKUP var meklēt datus pa labi vai pa kreisi no uzmeklēšanas vērtībām
  • XLOOKUP var atgriezt vairākus rezultātus (3. piemērs iepriekš)
  • XLOOKUP pēc noklusējuma ir precīza atbilstība (VLOOKUP pēc noklusējuma ir aptuvena)
  • XLOOKUP var strādāt ar vertikāliem un horizontāliem datiem
  • XLOOKUP var veikt apgriezto meklēšanu (no pēdējās uz pirmo)
  • XLOOKUP var atgriezt veselas rindas vai kolonnas, nevis tikai vienu vērtību
  • XLOOKUP var strādāt ar masīviem dabiski, lai piemērotu sarežģītus kritērijus

Piezīmes

  1. XLOOKUP var strādāt gan ar vertikāliem, gan horizontāliem blokiem.
  2. XLOOKUP atgriezīs # N / A, ja uzmeklēšanas vērtība nav atrasta.
  3. Pārlūkošanas_masīvs jābūt dimensija saderīga ar return_array argumentu, pretējā XLOOKUP atgriezīsies #VALUE!
  4. Ja starp darbgrāmatām tiek izmantots XLOOKUP, abām darbgrāmatām jābūt atvērtām, pretējā gadījumā XLOOKUP atgriezīs #REF !.
  5. Tāpat kā funkcija INDEX, arī XLOOKUP atgriež atsauci.

Saistītie videoklipi

XLOOKUP pamata piemērs Šajā videoklipā mēs iestatīsim funkciju XLOOKUP ar pamata piemēru. Atbilstoši pilsētas nosaukumam mēs iegūsim valsti un iedzīvotājus. XLOOKUP pamata aptuvenā atbilstība Šajā videoklipā mēs iestatīsim funkciju XLOOKUP, lai veiktu aptuvenu atbilstību, lai aprēķinātu uz daudzumu balstītu atlaidi. XLOOKUP ar loģisko loģiku Šajā video mēs apskatīsim, kā izmantot funkciju XLOOKUP ar Būla loģiku, lai piemērotu vairākus kritērijus. XLOOKUP ar vairākām uzmeklēšanas vērtībām Šajā videoklipā mēs iestatīsim XLOOKUP, lai dinamiskā masīvā atgrieztu vairākas vērtības, vienas uzmeklēšanas vērtības vietā norādot uzmeklēšanas vērtību diapazonu.

Interesanti raksti...