Excel

Excel XLOOKUP funkcija

Excel Xlookup Function

Excel XLOOKUP funkcijaKopsavilkums

Funkcija Excel XLOOKUP ir mūsdienīgs un elastīgs aizvietotājs vecākām funkcijām, piemēram, VLOOKUP, HLOOKUP un LOOKUP. XLOOKUP atbalsta aptuvenu un precīzu atbilstību, aizstājējzīmes (*?) Daļējai atbilstībai un meklējumus vertikālā vai horizontālā diapazonā.



Mērķis Uzmeklēšanas vērtības diapazonā vai masīvā Atgrieztā vērtība Atbilstošā (-ās) vērtība (-es) no atgriešanas masīva
  • uzmeklēšana - Uzmeklēšanas vērtība.
  • lookup_array - Masīvs vai diapazons, ko meklēt.
  • return_array - masīvs vai diapazons, kas jāatgriež.
  • nav atrasts - [pēc izvēles] Atgriežamā vērtība, 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ā (noklusējuma), -1 = meklēšana no pēdējās, 2 = binārā meklēšana augošā secībā, -2 = binārā meklēšana dilstošā secībā.
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, ko var izmantot visdažādākajās situācijās.

XLOOKUP var atrast vērtības vertikālā vai horizontālā diapazonā, var veikt aptuvenas un precīzas atbilstības, kā arī atbalsta aizstājējzīmes (*?) Daļējām atbilstībām. Turklāt XLOOKUP var meklēt datus, sākot no pirmās vērtības vai pēdējās vērtības (skatīt atbilstības veidu un meklēšanas režīma informāciju zemāk). Salīdzinot ar vecākām funkcijām, piemēram MEKLĒŠANA , MEKLĒŠANA , un MEKLĒT , XLOOKUP piedāvājumus 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 nav atrasts ko var izmantot, lai ignorētu kļūdu #N/A, ja tā citādi parādītos. Tipiskas vērtības nav atrasts var būt “Nav atrasts”, “Nav atbilstības”, “Nav rezultātu” utt. Norādot vērtību nav atrasts , pievienojiet tekstu pēdiņās ('').

Piezīme. Esiet piesardzīgs, ja not_found ievadāt tukšu virkni (''). Ja neatbilstība netiek atrasta, XLOOKUP #N/A vietā netiks rādīts nekas. Ja vēlaties redzēt kļūdu #N/A, kad neatrodat atbilstību, pilnībā izlaidiet argumentu.



Atbilstības veids

Pēc noklusējuma XLOOKUP veiks precīza atbilstība . Spēles atbilstību kontrolē neobligāts arguments atbilstības_tips, kam ir šādas iespējas:

aprēķiniet laiku starp diviem datumiem Excel
Atbilstības veids Uzvedība
0 (noklusējums) Precīza atbilstība. Atgriež #N/A, ja neatbilst.
-1 Precīza atbilstība vai nākamais mazākais vienums.
1 Precīza atbilstība vai nākamais lielāks vienums.
2 Aizstājējzīme (*,?, ~)

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ē neobligāts arguments search_mode , kas nodrošina šādas iespējas:

Meklēšanas režīms Uzvedība
1 (noklusējums) Meklēt no pirmās vērtības
-1 Meklēšana 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ārā meklēšana ir ļoti ātra, bet dati jāsakārto pēc vajadzības . Ja dati nav 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īza atbilstība

Pēc noklusējuma XLOOKUP veiks precīzu atbilstību. Tālāk redzamajā piemērā XLOOKUP tiek izmantots, lai izgūtu pārdošanas apjomus, pamatojoties uz precīzu atbilstību filmā. H5 formula ir šāda:

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

XLOOKUP - pamata precīzās atbilstības piemērs

Sīkāks skaidrojums šeit .

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

Lai iespējotu aptuvenu atbilstību, norādiet vērtību argumentam 'match_mode'. Zemāk redzamajā piemērā XLOOKUP tiek izmantots, lai aprēķinātu atlaidi, pamatojoties uz daudzumu, kam nepieciešama aptuvena atbilstība. Formulā F5 tiek parādīts -1 spēles_režīmam, lai iespējotu aptuveno atbilstību ar precīzu atbilstību vai nākamo mazāko darbību:

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

XLOOKUP - pamata aptuvenās atbilstības piemērs

Sīkāks skaidrojums šeit .

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

XLOOKUP vienai un tai pašai spēlei var atgriezt vairākas vērtības vienlaicīgi. Tālāk redzamajā piemērā ir parādīts, kā XLOOKUP var konfigurēt tā, lai ar vienu formulu atgrieztu trīs atbilstošas ​​vērtības. C5 formula ir šāda:

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

XLOOKUP - vairāku vērtību piemērs

Ņemiet vērā, ka atgriešanas masīvā (C8: E15) ir 3 slejas: pirmais, pēdējais, departaments. Visas trīs vērtības tiek atdotas un spēle diapazonā C5: E5.

4. piemērs - divvirzienu uzmeklēšana

XLOOKUP var izmantot, lai veiktu divvirzienu meklēšanu ligzdošana viens XLOOKUP citā. Tālāk redzamajā piemērā “iekšējais” XLOOKUP izgūst veselu rindu (visas Glass vērtības), kas tiek atdota “ārējam” XLOOKUP kā atgriešanas masīvs. Ārējais XLOOKUP atrod atbilstošo grupu (B) un atgriež atbilstošo vērtību (17.25) kā gala rezultātu.

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

XLOOKUP - divvirzienu uzmeklēšanas piemērs

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, nevis numuru #N/A, norādiet vērtību neobligātajam argumentam “nav atrasts”, kas iekļauts pēdiņās (“”). Piemēram, lai parādītu “Nav atrasts”, ja nav atrasta atbilstoša filma, pamatojoties uz tālāk norādīto darblapu, izmantojiet:

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

XLOOKUP - piemērs nav atrasts

Jūs varat pielāgot šo ziņojumu pēc saviem ieskatiem: “Nav atbilstības”, “Filma nav atrasta” utt.

ja šūna ir lielāka par citu šūnu, mainiet krāsu

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

Ar iespēju masīvus apstrādāt dabiski, XLOOKUP var izmantot ar sarežģītiem kritērijiem. Tālāk redzamajā 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)

XLOOKUP - sarežģītu kritēriju piemērs

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īdzinājumā ar VLOOKUP:

  • XLOOKUP var meklēt datus pa labi vai pa kreisi uzmeklēšanas vērtībām
  • XLOOKUP var atgriezt vairākus rezultātus (3. piemērs iepriekš)
  • XLOOKUP noklusējuma vērtība ir precīza (VLOOKUP noklusējuma vērtība 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 līdz pirmajai)
  • XLOOKUP var atgriezt visu rindas vai kolonnas , ne tikai viena vērtība
  • XLOOKUP var strādāt masīvi dabiski piemērot sarežģītus kritērijus

Piezīmes

  1. XLOOKUP var strādāt gan ar vertikāliem, gan horizontāliem masīviem.
  2. XLOOKUP atgriezīs #N/A, ja uzmeklēšanas vērtība netiks atrasta.
  3. The lookup_array jābūt izmēriem, kas ir saderīgi ar return_array arguments, pretējā gadījumā XLOOKUP atgriezīs #VALUE!
  4. Ja XLOOKUP tiek izmantots starp darbgrāmatām, abām darbgrāmatām jābūt atvērtām, pretējā gadījumā XLOOKUP atgriezīs #REF !.
  5. Funkcija INDEX , XLOOKUP atgriež a atsauce rezultātā.
XLOOKUP ir jauna funkcija, kas pieejama Excel 365 tikai.


^