Funkcija XLOOKUP

Izmantojiet funkciju XLOOKUP , lai tabulā vai diapazonā meklētu datus pēc rindas. Piemēram, uzmeklējiet kādu automašīnas detaļu pēc detaļas numura vai atrodiet darbinieka vārdu, pamatojoties uz darbinieka ID. Izmantojot XLOOKUP, vienā kolonnā varat meklēt meklējamo vārdu un atgriezt rezultātu no tās pašas rindas citā kolonnā neatkarīgi no tā, kurā pusē ir atgrieztā kolonna.

Piezīme.: Funkcija XLOOKUP nav pieejama programmā Excel 2016 Excel 2019. Tomēr var rasties situācija, kad izmantojat darbgrāmatu programmā Excel 2016 vai Excel 2019 ar funkciju XLOOKUP, ja to ir izveidojis kāds cits, izmantojot jaunāku Excel versiju.

Jūsu pārlūkprogramma neatbalsta video. Instalējiet Microsoft Silverlight, Adobe Flash Player vai Internet Explorer 9.

Sintakse

Funkcija XLOOKUP meklē diapazonā vai masīvā un pēc tam atgriež vienumu, kas atbilst pirmajai atrastā atbilstībai. Ja nav atbilstības, XLOOKUP var atgriezt tuvāko (aptuveno) atbilstību.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments	Apraksts
uzmeklējamā_vērtība Nepieciešams*	Meklējamā vērtība Ja XLOOKUP tiek izlaista, tā atgriež tukšas šūnas, ko tā atrod lookup_array*.
uzmeklēšanas_masīvs. Obligāts	Meklējamais masīvs vai diapazons
return_array Obligāts	Atgriežamais masīvs vai diapazons
[if_not_found] Neobligāts	Ja netiek atrasta derīga atbilstība, atgriež [if_not_found] jūsu ievadīto tekstu. Ja netiek atrasta derīga atbilstība un trūkst [if_not_found], tiek #N/A .
[match_mode] Neobligāts	Norādiet atbilstības tipu: 0 — precīza atbilstība. Ja nekas netiek atrasts, #N/A. Šis ir noklusējuma iestatījums. -1 — precīza atbilstība. Ja nekas netiek atrasts, atgriezt nākamo mazāko vienumu. 1. Precīza atbilstība. Ja nav atrasts, atgrieziet nākamo lielāko vienumu. 2 . Aizstājējzīme atbilst, kur *, ? un ~ ir īpaša nozīme.
[search_mode] Neobligāts	Norādiet meklēšanas režīmu, ko izmantot: 1. Veiciet meklēšanu, sākot no pirmā vienuma. Šis ir noklusējuma iestatījums. -1 . Veiciet apgrieztu meklēšanu, sākot no pēdējā vienuma. 2. Veiciet bināru meklēšanu, kas ir lookup_array, kad dati tiek kārtoti augošā secībā. Ja dati netiek kārtoti, tiek atgriezti nederīgi rezultāti. -2 - Binārā meklēšana, kas ir atkarīga no lookup_array tiek kārtots dilstošā secībā. Ja dati netiek kārtoti, tiek atgriezti nederīgi rezultāti.

Arguments

Apraksts

uzmeklējamā_vērtība

Nepieciešams*

Meklējamā vērtība *Ja XLOOKUP tiek izlaista, tā atgriež tukšas šūnas, ko tā atrod lookup_array.

uzmeklēšanas_masīvs.

Obligāts

Meklējamais masīvs vai diapazons

return_array

Obligāts

Atgriežamais masīvs vai diapazons

[if_not_found]

Neobligāts

Ja netiek atrasta derīga atbilstība, atgriež [if_not_found] jūsu ievadīto tekstu.

Ja netiek atrasta derīga atbilstība un trūkst [if_not_found], tiek #N/A .

[match_mode]

Neobligāts

Norādiet atbilstības tipu:

0 — precīza atbilstība. Ja nekas netiek atrasts, #N/A. Šis ir noklusējuma iestatījums.

-1 — precīza atbilstība. Ja nekas netiek atrasts, atgriezt nākamo mazāko vienumu.

1. Precīza atbilstība. Ja nav atrasts, atgrieziet nākamo lielāko vienumu.

2 . Aizstājējzīme atbilst, kur *, ? un ~ ir īpaša nozīme.

[search_mode]

Neobligāts

Norādiet meklēšanas režīmu, ko izmantot:

1. Veiciet meklēšanu, sākot no pirmā vienuma. Šis ir noklusējuma iestatījums.

-1 . Veiciet apgrieztu meklēšanu, sākot no pēdējā vienuma.

2. Veiciet bināru meklēšanu, kas ir lookup_array, kad dati tiek kārtoti augošā secībā. Ja dati netiek kārtoti, tiek atgriezti nederīgi rezultāti.

-2 - Binārā meklēšana, kas ir atkarīga no lookup_array tiek kārtots dilstošā secībā. Ja dati netiek kārtoti, tiek atgriezti nederīgi rezultāti.

Piemēri

1. piemērā izmantota funkcija XLOOKUP, lai diapazonā uzmeklē valsts/reģiona nosaukumu un pēc tam atgrieztu valsts/reģiona kodu. Tajā ietilpst lookup_value (šūna F2), lookup_array (diapazons B2:B11) un return_array (diapazons D2:D11). Nav iekļauts funkcijas match_mode, jo XLOOKUP pēc noklusējuma veido precīzu atbilstību.

Funkcijas XLOOKUP piemērs, kas tiek izmantota, lai atgrieztu darbinieku vārdu un nodaļu, pamatojoties uz darbinieka ID. Formula ir =XLOOKUP(B2,B5:B14,C5:C14)

Piezīme.: Funkcija XLOOKUP izmanto uzmeklēšanas masīvu un atgriezto masīvu, savukārt funkcija VLOOKUP izmanto vienu tabulas masīvu, kam seko kolonnas indeksa numurs. Ekvivalentā VLOOKUP formula šajā gadījumā būtu: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

2 . piemērs uzmeklē darbinieka informāciju, pamatojoties uz darbinieka ID numuru. Atšķirībā no VLOOKUP, XLOOKUP var atgriezt masīvu ar vairākiem vienumiem, lai viena formula varētu atgriezt gan darbinieka vārdu, gan nodaļu no šūnām C5:D14.

Tās funkcijas XLOOKUP piemērs, kas tiek izmantota, lai atgrieztu darbinieka vārdu un nodaļu, pamatojoties uz darbinieka ID. Formula ir: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

3 . piemērs pievieno if_not_found argumentu iepriekšējā piemērā.

Tās funkcijas XLOOKUP piemērs, kas tiek izmantota, lai atgrieztu darbinieku vārdu un nodaļu, pamatojoties uz darbinieka ID, if_not_found argumentu. Formula ir =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Darbinieks nav atrasts")

———————————————————————————

4 . piemērs kolonnā C meklē personas ienākumus, kas ievadīti šūnā E2, un atrod atbilstošu nodokļu likmi kolonnā B. Tas iestata argumentu if_not_found atgriezt 0 (nulle), ja nekas nav atrasts. Arguments match_mode iestatīts uz 1, kas nozīmē, ka funkcija meklēs precīzu atbilstību, un, ja funkcija tādu nevar atrast, tā atgriež nākamo lielāko vienumu. Visbeidzot arguments search_mode iestatīts uz 1, kas nozīmē, ka funkcija meklēs no pirmā vienuma līdz pēdējai.

Tās funkcijas XLOOKUP attēls, kas tiek izmantota, lai atgrieztu nodokļu likmi, pamatojoties uz maksimālo ienākumu. Tā ir aptuvena atbilstība. Formula ir: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

Piezīme.: XARRAY kolonnas lookup_array atrodas pa labi no return_array , savukārt funkcija VLOOKUP var izskatīties tikai no kreisās puses uz labo.

———————————————————————————

5. piemērs izmanto ligzdotu funkciju XLOOKUP, lai nodrošinātu vertikālu un horizontālu atbilstību. Vispirms kolonnā B meklē Bruto peļņa, pēc tam tabulas augšējā rindā meklē Cet1 (diapazons C5:F5) un visbeidzot atgriež vērtību abu krustpunktā. Tas ir līdzīgi funkciju INDEX un MATCH vienlaicīgi izmantot.

Padoms.: Funkciju HLOOKUP var arī izmantot, lai aizstātu funkciju HLOOKUP .

Funkcijas XLOOKUP attēls, kas tiek izmantots, lai atgrieztu horizontālus datus no tabulas, ligzdot 2 XLOOKUP. Formula ir: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

Piezīme.: Formula šūnās D3:F3 ir: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17)).

———————————————————————————

6. piemērs izmanto funkciju SUM un divas ligzdotas XLOOKUP funkcijas, lai saskaittu visas vērtības starp diviem diapazoniem. Šajā gadījumā mēs vēlamies summēt vērtības no ciemiem, banāniem un iekļaut bumbierus, kas atrodas starp tiem.

XLOOKUP izmantošana kopā ar SUM, lai saskaitāmu vērtību diapazonu, kas atrodas starp divām atlasēm

Formula šūnā E3 ir: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

Kā tas darbojas? XLOOKUP atgriež diapazonu, tāpēc, veicot aprēķinus, formula beigās izskatās šādi: =SUM($E$7:$E$9). Varat redzēt, kā tas darbojas jūsu vietā, atlasot šūnu ar XLOOKUP formulu, kas līdzīga šai, pēc tam atlasiet Formulas > Formulu auditēšana > Novērtēt formulu un pēc tam atlasiet Novērtēt, lai veiktu aprēķinu.

Piezīme.: Paldies Microsoft Excel MVP , Bill Jelen, ka ieteiks šo piemēru.

———————————————————————————

Skatiet arī

Vienmēr varat pajautāt speciālistam Excel tech kopienā vai saņemt atbalstu kopienās.

Funkcija XMATCH

Excel funkciju saraksts (pēc alfabēta)

Excel funkciju saraksts (pēc kategorijas)

Sintakse

Piemēri

Skatiet arī

Nepieciešama papildu palīdzība?

Vēlaties vairāk opciju?

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!