Formulas un funkcijas

XLOOKUP

Funkcija XLOOKUP

Izmantojiet funkciju XLOOKUP , lai atrastu vienumus tabulā vai diapazonā pēc rindas. Piemēram, Uzmeklējiet automobiļa cenas cenu, izmantojot daļu numuru, vai atrodiet darbinieka vārdu, pamatojoties uz darbinieka ID. Izmantojot XLOOKUP, varat meklēt vienā kolonnā ar meklējamo terminu un atjaunot rezultātu no vienas rindas citā kolonnā, neatkarīgi no tā, kurā pusē ir kolonna Return.

Jūsu pārlūkprogramma neatbalsta video.

Sintakse

Funkcija XLOOKUP meklē diapazonu vai masīvu un atgriež vienumu, kas atbilst pirmajai atbilstībai, ko atrod. Ja neviena atbilstība nepastāv, tad 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 izlaists, XLOOKUP atgriež tukšās šū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 nav atrasta derīga atbilstība, atdodiet savu piegādāto [if_not_found] tekstu.

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

[match_mode]

Neobligāts

Norādiet atbilstības tipu:

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

-1-precīza atbilstība. Ja nav atrasts, atdodiet nākamo mazāko vienumu.

1. precīza atbilstība. Ja neviens nav atrasts, atdodiet nākamo lielāko vienumu.

2-aizstājējzīmju atbilstība WHERE *,? un ~ ir īpaša nozīme.

[search_mode]

Neobligāts

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

1. Veiciet meklēšanu, sākot ar pirmo vienumu. Šis ir noklusējuma iestatījums.

-1-veikt apvērstu meklēšanu, sākot ar pēdējo vienumu.

2. Veiciet bināro meklēšanu, kuras pamatā ir lookup_array kārtot augošā secībā. Ja nav sakārtoti, tiek atgriezti nederīgi rezultāti.

-2-veikt bināro meklēšanu, kas ir atkarīga no tā, vai lookup_array kārtots dilstošā secībā. Ja nav sakārtoti, tiek atgriezti nederīgi rezultāti.

Piemēri

1. piemērs    izmanto XLOOKUP, lai uzmeklētu valsts nosaukumu diapazonā, un pēc tam atgriezt tā tālruņa valsts kodu. Tajā ietilpst lookup_value (šūna F2), Lookup_array (diapazons B2: B11) un Return_array (diapazons D2: D11) argumenti. Tajā nav iekļauts arguments match_mode , jo XLOOKUP pēc noklusējuma izveido precīzu atbilstību.

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

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

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

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

Funkcijas XLOOKUP, kas tiek izmantota, lai atgrieztu darbinieka vārdu un nodaļu, pamatojoties uz darbinieka IDt. Formula ir: = XLOOKUP (B2, B5: B14, C5: D14; 0; 1)

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

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

Funkcijas XLOOKUP, kas tiek izmantota, lai atgrieztu darbinieka vārdu un nodaļu, pamatojoties uz darbinieka ID ar if_not_found argumentu. Formula ir = XLOOKUP (B2, B5: B14, C5: D14, 0, 1, "darbinieks nav atrasts")

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

4. piemērā    tiek rādīta kolonna C, kas atbilst personas ienākumiem, kas ievadīti šūnā E2, un atrod atbilstošo nodokļu likmi kolonnā B. Tā iestata argumentu if_not_found atgriezt 0 (nulle), ja nekas netiek atrasts. Argumenta match_mode vērtība ir 1, kas nozīmē, ka funkcija meklēs precīzu atbilstību, un, ja tā nevar atrast, tā atgriež nākamo lielāko vienumu. Visbeidzot search_mode arguments tiek iestatīts uz 1, tas nozīmē, ka funkcija meklēs no pirmā vienuma uz pēdējo.

Funkcijas XLOOKUP attēls, kas tiek izmantota, lai atgrieztu nodokļu likmi, pamatojoties uz maksimālajiem ieņēmumiem. Šī ir aptuvena atbilstība. Formula ir: = XLOOKUP (E2; C2: C7; B2: B7; 1; 1)

Piezīme.: LOOKUP_ARRAY XARRAY kolonna ir pa labi no kolonnas RETURN_ARRAY , savukārt VLOOKUP var skatīt tikai no kreisās puses uz labo.

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

5. piemērs    izmanto ligzdotu funkciju XLOOKUP, lai veiktu gan vertikālas, gan horizontālas atbilstības. Vispirms tiek meklēta bruto peļņa kolonnā B, un pēc tam tiek meklēta Cet1 tabulas augšējā rindā (diapazons C5: F5) un visbeidzot atgriež abu krustpunktu vērtību. Tas līdzinās funkciju index un Match izmantošana kopā.

Padoms.: Varat arī izmantot XLOOKUP, lai aizstātu funkciju HLOOKUP .

Funkcija XLOOKUP, kas tiek izmantota, lai atgrieztu horizontālus datus no tabulas, ligzdojot 2 XLOOKUPs. 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 SUMun divas ligzdotas XLOOKUP funkcijas, lai saskaitītu visas vērtības starp diviem diapazoniem. Šajā gadījumā mēs vēlamies summēt vīnogu, banānu vērtības un iekļaut bumbierus, kas ir starp šiem diviem.

XLOOKUP izmantošana ar SUM kopsummu par vērtību diapazonu, kas ir 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, un, kad tas tiek aprēķināts, formula beidzas ar tādu, kas izskatās šādi: = SUM ($E $7: $E $9). Jūs varat redzēt, kā tas darbojas patstāvīgi, atlasot šūnu ar XLOOKUP formulu, kas līdzīga šai, un pēc tam atlasiet formulas , > Formulu auditēšana > novērtētu formulu, un pēc tam atlasiet novērtēt , lai veiktu aprēķinus.

Piezīme.: Microsoft Excel MVP, Bill jelen, lai ieteiktu šo piemēru.

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

Vai nepieciešama papildu palīdzība?

Vienmēr varat vērsties pie speciālista Excel Tech kopienā vai saņemt atbalstu Answers kopienā.

Skatiet arī

Funkcija XMATCH

Excel funkciju saraksts (alfabēta secībā)

Excel funkciju saraksts (pēc kategorijas)

Vai nepieciešama papildu palīdzība?

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

×