Note: The other languages of the website are Google-translated. Back to English

Ինչպես օգտագործել նոր և առաջադեմ XLOOKUP ֆունկցիան Excel-ում (10 օրինակ)

Excel-ը նոր է XLOOKUP Excel-ի որոնման ամենահզոր և ամենահեշտ գործառույթն է: Անդադար ջանքերի շնորհիվ Microsoft-ը վերջապես թողարկեց այս XLOOKUP ֆունկցիան՝ փոխարինելու VLOOKUP, HLOOKUP, INDEX+MATCH և այլ որոնման գործառույթները:

Այս ձեռնարկում մենք ձեզ ցույց կտանք որոնք են XLOOKUP-ի առավելությունները և ինչպես կարող եք ստանալ այն և կիրառել այն տարբեր որոնման խնդիրներ լուծելու համար:

Ինչպե՞ս ստանալ XLOOKUP:

XLOOKUP ֆունկցիայի շարահյուսություն

XLOOKUP ֆունկցիայի օրինակներ

Ներբեռնեք XLOOKUP նմուշի ֆայլը

Ինչպե՞ս ստանալ XLOOKUP:

Հետո XLOOKUP ֆունկցիա is հասանելի է միայն in Excel Microsoft 365-ի համար, Excel 2021, եւ Excel ցանցի համար, կարող եք թարմացնել ձեր Excel-ը հասանելի տարբերակին՝ XLOOKUP ստանալու համար:

XLOOKUP ֆունկցիայի շարահյուսություն

XLOOKUP ֆունկցիան փնտրում է տիրույթ կամ զանգված և այնուհետև վերադարձնում է առաջին համապատասխանող արդյունքի արժեքը. The շարահյուսություն XLOOKUP ֆունկցիան հետևյալն է.

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

xlookup ֆունկցիա 1

Արձանագրություններ.

  1. Lookup_value (պարտադիր): արժեքը, որը դուք փնտրում եք: Այն կարող է լինել table_array տիրույթի ցանկացած սյունակում:
  2. Lookup_array (պարտադիր): զանգվածը կամ տիրույթը, որտեղ փնտրում եք որոնման արժեքը:
  3. Return_array (պարտադիր): զանգվածը կամ միջակայքը, որտեղից ցանկանում եք ստանալ արժեքը:
  4. If_not_found (ըստ ցանկության): արժեքը, որը պետք է վերադարձվի, երբ վավեր համընկնում չի գտնվել: Դուք կարող եք հարմարեցնել տեքստը [if_not_found]-ում՝ ցույց տալու համար, որ համընկնում չկա:
    Հակառակ դեպքում վերադարձի արժեքը լռելյայն կլինի #N/A:
  5. Match_mode (ըստ ցանկության)այստեղ կարող եք նշել, թե ինչպես պետք է համապատասխանեցնել lookup_value-ը lookup_array-ի արժեքներին:
    • 0 (կանխադրված) = Ճշգրիտ համընկնում: Եթե ​​համընկնում չի գտնվել, վերադարձրեք #N/A:
    • -1 = Ճշգրիտ համընկնում: Եթե ​​համընկնում չի գտնվել, վերադարձրեք հաջորդ ավելի փոքր արժեքը:
    • 1 = Ճշգրիտ համընկնում: Եթե ​​համընկնում չի գտնվել, վերադարձրեք հաջորդ ավելի մեծ արժեքը:
    • 2 = Մասնակի համընկնում: Օգտագործեք նիշերը, ինչպիսիք են *, ? and ~ a wildcard համընկնում վարել.
  6. Search_mode (ըստ ցանկության)Այստեղ դուք կարող եք նշել կատարման որոնման կարգը:
    • 1 (կանխադրված) = Որոնեք lookup_value-ը առաջին կետից մինչև վերջին տարրը lookup_array-ում:
    • -1 = Որոնեք lookup_value-ը վերջին կետից մինչև առաջին կետը: Այն օգնում է, երբ Ձեզ անհրաժեշտ է ստանալ վերջին համապատասխանող արդյունքը lookup_array-ում:
    • 2 = Կատարել երկուական որոնում, որը պահանջում է lookup_array տեսակավորված աճման կարգով: Եթե ​​չտեսակավորվի, վերադարձի արդյունքը անվավեր կլինի:
    • -2 = Կատարեք երկուական որոնում, որը պահանջում է lookup_array դասավորված նվազման կարգով: Եթե ​​չտեսակավորվի, վերադարձի արդյունքը անվավեր կլինի:

համար մանրամասն տեղեկություններ XLOOKUP ֆունկցիայի փաստարկների մասինխնդրում եմ արեք հետևյալը.

1. Մուտքագրեք շարահյուսության տակ դատարկ բջիջի մեջ, խնդրում ենք նկատի ունենալ, որ անհրաժեշտ է մուտքագրել փակագծի միայն մի կողմը:

=XLOOKUP(

xlookup ֆունկցիա 2

2. Մամուլ Ctrl + A, Ապա հուշագրի տուփ հայտնվում է, որը ցույց է տալիս Ֆունկցիայի փաստարկները. Իսկ փակագծի մյուս կողմն ավարտվում է ավտոմատ կերպով։

xlookup ֆունկցիա 3

3. Ներքև քաշեք տվյալների վահանակը, ապա դուք կարող եք տեսնել բոլորը վեց ֆունկցիայի փաստարկներ XLOOKUP-ի:

xlookup ֆունկցիա 4 >>> xlookup ֆունկցիա 5

XLOOKUP ֆունկցիայի օրինակներ

Համոզված եմ, որ դուք այժմ տիրապետում եք XLOOKUP ֆունկցիայի հիմնական սկզբունքներին: Եկեք սուզվենք հենց մեջ գործնական օրինակներ XLOOKUP-ի:

Օրինակ 1. Ճշգրիտ համընկնում

Կատարեք ճշգրիտ համընկնում XLOOKUP-ի հետ

Երբևէ հիասթափվե՞լ եք, որովհետև VLOOKUP-ն օգտագործելիս ստիպված էիք նշել համընկնման ճշգրիտ ռեժիմը: Բարեբախտաբար, այս դժվարությունն այլևս գոյություն չունի, երբ փորձեք զարմանալի XLOOKUP գործառույթը: Լռելյայնորեն, XLOOKUP-ը ստեղծում է ճշգրիտ համընկնում.

Հիմա, ենթադրենք, որ դուք ունեք գրասենյակային պարագաների գույքագրման ցուցակ, և դուք պետք է իմանաք մեկ ապրանքի միասնական գինը, ասենք մկնիկը, խնդրում ենք անել հետևյալը.

xlookup ֆունկցիա 6

Մուտքագրեք ստորև բերված բանաձևը F2 դատարկ բջիջի մեջ և սեղմեք կոճակը Մտնել արդյունքը ստանալու բանալին:

=XLOOKUP(E2,A2:A10,C2:C10)

xlookup ֆունկցիա 7

Այժմ դուք գիտեք մկնիկի միավորի գինը առաջադեմ XLOOKUP բանաձևով: Քանի որ համընկնումի կոդը լռելյայնորեն սահմանված է ճշգրիտ համընկնում, դուք պետք չէ այն նշել: Շատ ավելի հեշտ և արդյունավետ, քան VLOOKUP-ը:

Ընդամենը մի քանի կտտոց՝ ճշգրիտ համընկնում ստանալու համար

Հավանաբար, դուք օգտագործում եք Excel-ի ավելի ցածր տարբերակ և դեռևս պլան չունեք նորացնել Excel 2021 կամ Microsoft 365: Այս դեպքում ես խորհուրդ կտամ հարմար հատկանիշ - Փնտրեք արժեք ցուցակի բանաձևում of Excel- ի համար նախատեսված գործիքներ. Այս հատկանիշով դուք կարող եք արդյունք ստանալ առանց բարդ բանաձևերի կամ XLOOKUP-ի հասանելիության:

Օ-ովur Excel հավելում Installed, խնդրում ենք անել հետևյալը.

1. Սեղմեք բջիջը՝ համապատասխան արդյունքը տեղադրելու համար:

2. Գնալ Կուտոլս էջանշանը, սեղմեք Բանաձևի օգնական, ապա սեղմեք Բանաձևի օգնականը բացվող ցանկում.

xlookup ֆունկցիա 8

3. Մեջ Բանաձևերի օգնական երկխոսության տուփ, խնդրում ենք կարգավորել հետևյալ կերպ.

  • ընտրել Փնտրել է Բանաձևի տեսակ բաժին;
  • Է Ընտրեք բանաձևի բաժինըընտրեք Valueանկում արժեք փնտրեք;
  • Է Փաստարկների մուտքագրման բաժինխնդրում եմ արեք հետևյալը.
    • Է Table_array տուփ, ընտրեք տվյալների տիրույթը, որը պարունակում է որոնման արժեքը և արդյունքի արժեքը.
    • Է Lookup_value տուփ, ընտրեք ձեր որոնվող արժեքի բջիջը կամ միջակայքը: Խնդրում ենք նկատի ունենալ, որ այն պետք է լինի table_array-ի առաջին սյունակում;
    • Է Սյունակ տուփ, ընտրեք այն սյունակը, որտեղից կվերադարձնեք համապատասխան արժեքը:

xlookup ֆունկցիա 9

4. Սեղմեք OK կոճակը՝ արդյունքը ստանալու համար:

xlookup ֆունկցիա 10

Կտտացրեք Excel- ի համար Kutools- ը ներբեռնելու համար ՝ 30-օրյա անվճար փորձաշրջանի համար.


Օրինակ 2. Մոտավոր համընկնում

Կատարեք մոտավոր համընկնում XLOOKUP-ի հետ

Գործարկել ան մոտավոր որոնում, Դուք պետք է հինգերորդ արգումենտում համընկնման ռեժիմը սահմանեք 1 կամ -1. Երբ ճշգրիտ համընկնում չի գտնվել, այն վերադարձնում է հաջորդ ավելի մեծ կամ փոքր արժեքը.

Այս դեպքում դուք պետք է իմանաք ձեր անձնակազմի եկամուտների հարկային դրույքաչափերը: Աղյուսակի ձախ կողմում 2021 թվականի Դաշնային եկամտահարկի փակագծերն են: Ինչպե՞ս կարող եք ստանալ ձեր անձնակազմի հարկային դրույքաչափը E սյունակում: Մի անհանգստացեք: Խնդրում ենք անել հետևյալը.

1. Մուտքագրեք ստորև բերված բանաձևը E2 դատարկ բջիջի մեջ և սեղմեք կոճակը Մտնել արդյունքը ստանալու բանալին:
Այնուհետև փոխեք վերադարձված արդյունքի ձևաչափումը, ինչպես ձեզ անհրաժեշտ է:

=XLOOKUP(D2,B2:B8,A2:A8,,1)

xlookup ֆունկցիա 11 >>> xlookup ֆունկցիա 12

√ Նշում. Չորրորդ արգումենտը [If_not_found] կամընտիր է, ուստի ես պարզապես բաց եմ թողնում այն:

2. Այժմ դուք գիտեք D2 բջիջի հարկային դրույքաչափը: Մնացած արդյունքները ստանալու համար, Դուք պետք է փոխակերպեք lookup_array-ի և return_array-ի բջիջների հղումները բացարձակի.

  • Կրկնակի սեղմեք E2 բջիջը՝ ցույց տալու բանաձևը =XLOOKUP(D2,B2:B8,A2:A8,,1);
  • Բանաձևում ընտրեք որոնման միջակայքը B2:B8, սեղմեք F4 ստեղնը ստանալ $B$2:$B$8;
  • Բանաձևում ընտրեք վերադարձի միջակայքը A2:A8, սեղմեք F4 ստեղնը $A$2:$A$8 ստանալու համար;
  • Սեղմեք Մտնել կոճակ՝ E2 բջիջի արդյունքը ստանալու համար:
xlookup ֆունկցիա 13 >>> xlookup ֆունկցիա 14

3. Հետո ներքև քաշեք լրացման բռնակը բոլոր արդյունքները ստանալու համար:

xlookup ֆունկցիա 15

√ Նշում.

  • Սեղմելով F4 ստեղնը ստեղնաշարի վրա թույլ է տալիս փոխել բջջային հղումը բացարձակ հղումի տողից և սյունակից առաջ դոլարի նշաններ ավելացնելով:
  • Բացարձակ հղում կիրառելուց հետո փնտրելու և վերադարձնելու տիրույթը, մենք փոխեցինք բանաձևը E2 բջիջում այս տարբերակին.

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

  • Երբ դուք քաշեք լրացման բռնակը E2 բջիջից ներքև, բանաձեւերը E սյունակի յուրաքանչյուր բջիջում են փոխվել է միայն lookup_value-ի առումով.
    Օրինակ, E13-ի բանաձևը այժմ վերածվել է հետևյալի.

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

Օրինակ 3. Վայրի նշանների համընկնում

Կատարեք Wildcard համընկնում XLOOKUP-ի հետ

Նախքան մենք նայենք XLOOKUP նիշերի համընկնման գործառույթ, նախ տեսնենք ինչ են wildcards.

Microsoft Excel-ում, Wildcards-ը նիշերի հատուկ տեսակ է, որը կարող է փոխարինել ցանկացած նիշ. Դա մասնավորապես օգտակար երբ ցանկանում եք կատարել համընկնումների մասնակի որոնումներ:

Գոյություն ունեն երեք տեսակի մոլորակներ. աստղանիշ (*), հարցական նշան (?), եւ tilde (~).

  • Աստղանիշը (*) ներկայացնում է տեքստի ցանկացած թվով նիշ;
  • Հարցական նշանը (?) նշանակում է տեքստի ցանկացած նիշ;
  • Tilde (~) օգտագործվում է նիշերը (*, ? ~) բառացի նիշերի վերածելու համար: Տեղադրեք tilde (~) նիշերի առջև՝ այս գործառույթը կատարելու համար.

Շատ դեպքերում, երբ մենք կատարում ենք XLOOKUP wildcard համընկնման ֆունկցիան, մենք օգտագործում ենք աստղանիշ (*) նիշը: Հիմա եկեք տեսնենք, թե ինչպես է աշխատում wildcard համընկնումը:

Ենթադրենք, որ դուք ունեք 50 խոշորագույն ամերիկյան ընկերությունների ֆոնդային շուկայի կապիտալացման ցուցակը, և ցանկանում եք իմանալ մի քանի ընկերությունների շուկայական կապիտալիզացիան, բայց ընկերությունների անունները կարճ են, սա կատարյալ սցենար է նիշերի համընկնման համար: Խնդրում եմ քայլ առ քայլ հետևեք ինձ՝ հնարքն անելու համար:

xlookup ֆունկցիա 16

√ Նշում․ նիշերի համընկնումը կատարելու համար ամենակարևորը հինգերորդ արգումենտը [match_mode]-ը 2-ի սահմանելն է։

1. Մուտքագրեք ստորև բերված բանաձևը դեպի H3 դատարկ բջիջ և սեղմեք կոճակը Մտնել արդյունքը ստանալու բանալին:

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

xlookup ֆունկցիա 17 >>> xlookup ֆունկցիա 18

2. Այժմ դուք գիտեք H3 բջիջի արդյունքը: Մնացած արդյունքները ստանալու համար անհրաժեշտ է շտկել lookup_array-ը և return_array-ը կուրսորը զանգվածի մեջ դնելով և սեղմելով F4 ստեղնը։ Այնուհետև H3-ում բանաձևը դառնում է.

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. Լցման բռնակը քաշեք ներքև բոլոր արդյունքները ստանալու համար:

xlookup ֆունկցիա 19

√ Նշում.

  • H3 բջիջում բանաձևի lookup_արժեքը «*»&G3&»* է։ Մենք աստղանիշի նշանը (*) միացնել G3 արժեքին օգտագործելով միջանկյալ (&).
  • Չորրորդ արգումենտը [If_not_found] կամընտիր է, ուստի ես պարզապես բաց եմ թողնում այն:
Օրինակ 4. Նայեք ձախ

Նայեք դեպի ձախ՝ օգտագործելով XLOOKUP

Մեկ VLOOKUP-ի թերությունը այն է, որ դա է սահմանափակված է որոնումներ կատարել որոնման սյունակի աջ կողմում. Եթե ​​փորձեք փնտրել որոնման սյունակում մնացած արժեքները, կստանաք #N/A սխալ: Մի անհանգստացեք: XLOOKUP-ը կատարյալ որոնման գործառույթ է այս խնդիրը լուծելու համար:

XLOOKUP նախատեսված է արժեքներ փնտրելու համար ձախը կամ աջը որոնումների սյունակից: Այն չունի սահմանափակումներ և բավարարում է Excel-ի օգտատերերի կարիքները: Ստորև բերված օրինակում մենք ձեզ ցույց կտանք հնարքը:

Ենթադրենք, դուք ունեք հեռախոսային կոդերով երկրների ցանկ, և ցանկանում եք փնտրել երկրի անվանումը հայտնի հեռախոսային կոդով:

xlookup ֆունկցիա 20

Մենք պետք է որոնենք C սյունակը և վերադարձնենք արժեքը A սյունակում: Խնդրում ենք անել հետևյալը.

1. Մուտքագրեք ստորև բերված բանաձևը G2 դատարկ բջիջի մեջ:

=XLOOKUP(F2,C2:C11,A2:A11)

2. Սեղմեք Մտնել արդյունքը ստանալու բանալին:

xlookup ֆունկցիա 21

√ Նշում․ «XLOOKUP look to left» ֆունկցիան կարող է փոխարինել Index-ին և Match-ին՝ ձախ կողմում գտնվող արժեքներ փնտրելու համար:

Փնտրեք արժեքը աջից ձախ մի քանի կտտոցով

Նրանց, ովքեր չեն ցանկանում հիշել բանաձևերը, խորհուրդ կտամ այստեղ օգտակար հատկություն - Փնտրեք աջից ձախ of Excel- ի համար նախատեսված գործիքներ. Այս ֆունկցիայի միջոցով դուք կարող եք մի քանի վայրկյանում կատարել որոնում աջից ձախ:

Օ-ովur Excel հավելում Installed, խնդրում ենք անել հետևյալը.

1. Գնալ Կուտոլս ներդիր Excel- ում, գտեք Super Փնտրելեւ սեղմեք Փնտրեք աջից ձախ բացվող ցուցակում:

xlookup ֆունկցիա 22

2. Մեջ Փնտրեք աջից ձախ երկխոսության վանդակում, դուք պետք է կազմաձևեք հետևյալ կերպ.

  • Է Որոնման արժեքներ և ելքային տիրույթի բաժին, նշեք որոնման միջակայքը և ելքային տիրույթ;
  • Է Տվյալների տիրույթի բաժին, մուտքագրում տվյալների տիրույթ, ապա նշեք հիմնական սյունակը և վերադարձի սյունակ;

xlookup ֆունկցիա 23

3. Սեղմեք OK կոճակը՝ արդյունքը ստանալու համար:

xlookup ֆունկցիա 24

Կտտացրեք Excel- ի համար Kutools- ը ներբեռնելու համար ՝ 30-օրյա անվճար փորձաշրջանի համար.


Օրինակ 5. Ուղղահայաց կամ Հորիզոնական որոնում

Կատարեք ուղղահայաց կամ հորիզոնական որոնում XLOOKUP-ով

Որպես Excel-ի օգտվողներ, դուք կարող եք ծանոթ լինել VLOOKUP և HLOOKUP գործառույթներին: VLOOKUP-ը սյունակում ուղղահայաց նայելն է և HLOOKUP-ը անընդմեջ հորիզոնական նայելն է.

Հիմա նոր XLOOKUP-ը համատեղում է երկուսն էլ, ինչը նշանակում է ուղղահայաց որոնում կամ հորիզոնական որոնում կատարելու համար անհրաժեշտ է օգտագործել միայն մեկ շարահյուսություն. Հանճարեղ, չէ՞։

Ստորև բերված օրինակում մենք ցույց կտանք, թե ինչպես եք օգտագործում միայն մեկ XLOOKUP շարահյուսություն՝ որոնումները ուղղահայաց կամ հորիզոնական գործարկելու համար:

Ուղղահայաց որոնում կատարելու համար, մուտքագրեք ստորև բերված բանաձևը E2 դատարկ բջիջում սեղմեք կոճակը Մտնել արդյունքը ստանալու բանալին:

=XLOOKUP(E1,A2:A13,B2:B13)

xlookup ֆունկցիա 25

Հորիզոնական որոնում կատարելու համար, մուտքագրեք ստորև բերված բանաձևը P2 դատարկ բջիջում սեղմեք կոճակը Մտնել արդյունքը ստանալու բանալին:

=XLOOKUP(P1,B1:M1,B2:M2)

xlookup ֆունկցիա 26

Ինչպես դուք կարող եք տեսնել, շարահյուսությունը նույնն է. The միայն տարբերություն երկու բանաձևերի միջև այն է, որ դուք մուտքագրեք սյունակ մուտքագրելիս ուղղահայաց որոնման մեջ տող հորիզոնական որոնման մեջ:

Օրինակ 6. Երկկողմանի որոնում

Կատարեք երկկողմանի որոնում XLOOKUP-ով

Դուք դեռ օգտագործում եք INDEX և MATCH ֆունկցիաներ որոնել արժեքը երկչափ միջակայքում: Փորձիր բարելավված XLOOKUP ձեր գործն ավարտին հասցնելու համար ավելի հեշտությամբ.

XLOOKUP-ը կարող է կատարել կրկնակի որոնում, գտնելով ան հատում երկու արժեքներից. Ըստ nesting մեկը XLOOKUP-ը մյուսի ներսում, ներսի XLOOKUP-ը կարող է վերադարձնել մի ամբողջ տող կամ սյունակ, այնուհետև այս վերադարձված տողը կամ սյունակը մուտքագրվում է արտաքին XLOOKUP որպես վերադարձի զանգված:

Ենթադրենք, դուք ունեք տարբեր առարկաների ուսանողների գնահատականների ցուցակ, ցանկանում եք իմանալ Քիմի Քիմիա առարկայի գնահատականը:

xlookup ֆունկցիա 43

Տեսնենք, թե ինչպես ենք մենք օգտագործում կախարդական XLOOKUP-ը հնարք կատարելու համար:

    • Մենք գործարկում ենք «ներքին» XLOOKUP-ը՝ մուտքագրվող սյունակի արժեքները վերադարձնելու համար: XLOOKUP(H2,B1:E1,B2:E10) կարող է ստանալ մի շարք Քիմիայի գնահատականներ:
    • Մենք «ներքին» XLOOKUP-ը տեղադրում ենք «արտաքին» XLOOKUP-ի ներսում՝ օգտագործելով «ներքին» XLOOKUP-ը որպես վերադարձի զանգված ամբողջական բանաձևում:
    • Այնուհետև գալիս է վերջնական բանաձևը.

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

  • Մուտքագրեք բանաձեւը վերեւում H3 դատարկ բջիջի մեջ սեղմեք կոճակը Մտնել կոճակը՝ արդյունքը ստանալու համար:

xlookup ֆունկցիա 27

Կամ դուք կարող եք անել այլ կերպ, օգտագործեք «ներքին» XLOOKUP՝ վերադարձնելու ամբողջ շարքի արժեքները, որոնք բոլորը Kim-ի առարկայական գնահատականներն են: Այնուհետև օգտագործեք «արտաքին» XLOOKUP՝ Քիմի բոլոր առարկայական դասարանների մեջ Քիմիայի գնահատականը փնտրելու համար:

    • Մուտքագրեք ստորև բերված բանաձևը H4 դատարկ բջիջում և սեղմեք կոճակը Մտնել կոճակը՝ արդյունքը ստանալու համար:

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

xlookup ֆունկցիա 28

XLOOKUP-ի երկկողմանի որոնման գործառույթը նաև իր ուղղահայաց և հորիզոնական որոնման գործառույթի կատարյալ պատկերացումն է: Եթե ​​ցանկանում եք, փորձեք:

Օրինակ 7. Անհատականացրեք չգտնված հաղորդագրությունը

Անհատականացրեք չգտնված հաղորդագրությունը՝ օգտագործելով XLOOKUP

Ճիշտ այնպես, ինչպես մյուս որոնման գործառույթները, երբ XLOOKUP գործառույթը չի կարող գտնել համապատասխանությունԷ, #N/A սխալի հաղորդագրություն կվերադարձվի։ Excel-ի որոշ օգտվողների համար դա կարող է շփոթեցնող լինել: Բայց լավ նորությունն այն է, որ սխալների մշակում հասանելի է XLOOKUP ֆունկցիայի չորրորդ փաստարկը.

Հետ ներկառուցված [if_not_found] արգումենտ, կարող եք նշել հատուկ հաղորդագրություն #N/A արդյունքը փոխարինելու համար. Մուտքագրեք ձեզ անհրաժեշտ տեքստը ընտրովի չորրորդ արգումենտում և կցեք տեքստը կրկնակի չակերտներ (").

Օրինակ, Դենվեր քաղաքը չի գտնվել, ուստի XLOOKUP-ը վերադարձնում է #N/A սխալի հաղորդագրությունը: Բայց չորրորդ փաստարկը «No Match» տեքստով հարմարեցնելուց հետո բանաձեւը սխալի հաղորդագրության փոխարեն կցուցադրի «No Match» տեքստը:

Մուտքագրեք ստորև բերված բանաձևը F3 դատարկ բջիջում և սեղմեք կոճակը Մտնել կոճակը՝ արդյունքը ստանալու համար:

=XLOOKUP(E2,A2:A11,C2:C11,"No Match")

xlookup ֆունկցիա 29

Անհատականացրեք #N/A սխալը հարմար հատկանիշով

Ձեր հատուկ հաղորդագրությամբ #N/A սխալը արագորեն վերացնելու համար, Excel- ի համար նախատեսված գործիքներ is կատարյալ գործիք Excel-ում ձեզ օգնելու համար: Իր ներկառուցվածով Փոխարինեք 0-ը կամ #N/A-ը դատարկ կամ Հատուկ արժեք հատկանիշով, կարող եք նշել չգտնված հաղորդագրությունը առանց բարդ բանաձևերի կամ XLOOKUP-ի հասանելիության:

մեր Excel հավելում տեղադրված է, խնդրում ենք անել հետևյալը.

1. Գնալ Կուտոլս ներդիր Excel- ում, գտեք Super Փնտրելեւ սեղմեք 0-ը կամ # N / A- ը փոխարինեք դատարկով կամ որոշակի արժեքով բացվող ցուցակում:

xlookup ֆունկցիա 30

2. Մեջ Փոխարինեք 0 կամ #N/A-ը դատարկ կամ Հատուկ արժեք երկխոսության վանդակով, դուք պետք է կազմաձևեք հետևյալ կերպ.

  • Է Որոնման արժեքներ և ելքային տիրույթի բաժինընտրեք որոնման միջակայքը և ելքային տիրույթ;
  • Ապա ընտրեք Փոխարինել 0 կամ #N/A հատուկ արժեքով տարբերակը, մուտքագրել տեքստը դու նախընտրում ես;
  • Է Տվյալների տիրույթի բաժին, ընտրեք տվյալների տիրույթ, ապա նշեք հիմնական սյունակ և վերադարձված սյունակ.

xlookup ֆունկցիա 31

3. Սեղմեք OK կոճակը՝ արդյունքը ստանալու համար: Հարմարեցված հաղորդագրությունը կցուցադրվի, երբ համընկնում չգտնվի:

xlookup ֆունկցիա 32

Կտտացրեք Excel- ի համար Kutools- ը ներբեռնելու համար ՝ 30-օրյա անվճար փորձաշրջանի համար.


Օրինակ 8. Բազմաթիվ արժեքներ

Վերադարձրեք մի քանի արժեքներ XLOOKUP-ով

Ուրիշ առավելություն XLOOKUP-ը նրա կարողությունն է վերադարձնել բազմաթիվ արժեքներ միաժամանակ նույն հանդիպման համար: Առաջին արդյունքը ստանալու համար մուտքագրեք մեկ բանաձև, այնուհետև վերադարձված այլ արժեքներ թափթփած ավտոմատ կերպով հարևան դատարկ բջիջների մեջ:

Ստորև բերված օրինակում դուք ցանկանում եք ստանալ «FG9940005» ուսանողական ID-ի մասին ամբողջ տեղեկատվությունը: Խնդիրն այն է, որ բանաձևում ընդգրկույթ տրամադրվի որպես վերադարձի զանգված՝ մեկ սյունակի կամ տողի փոխարեն: Այս դեպքում վերադարձվող զանգվածի տիրույթը B2:D9 է՝ ներառյալ երեք սյունակ:

Մուտքագրեք ստորև բերված բանաձևը G2 դատարկ բջիջում սեղմեք կոճակը Մտնել բանալին՝ բոլոր արդյունքները ստանալու համար:

=XLOOKUP(F2,A2:A9,B2:D9)

xlookup ֆունկցիա 33

Բոլոր արդյունքի բջիջները ցուցադրում են նույն բանաձևը: Դու կարող ես խմբագրել կամ փոփոխել բանաձեւը առաջին խցում, Սակայն այլ բջիջներում բանաձևը խմբագրելի չէ. Դուք կարող եք տեսնել, որ բանաձևի բարը գորշացել, այսինքն՝ դրանում ոչ մի փոփոխություն չի կարող կատարվել։

xlookup ֆունկցիա 34

Ընդհանուր առմամբ, XLOOKUP-ի բազմակի արժեքների ֆունկցիան ա օգտակար բարելավում VLOOKUP-ի համեմատ: Դուք ազատ եք յուրաքանչյուր բանաձևի համար յուրաքանչյուր սյունակի համարը առանձին նշելուց: Մեծ մատը վեր!

Օրինակ 9. Բազմաթիվ չափանիշներ

Կատարեք բազմաչափ չափանիշների որոնում՝ օգտագործելով XLOOKUP

Ուրիշ զարմանալի նոր առանձնահատկություն XLOOKUP-ը նրա կարողությունն է որոնում մի քանի չափանիշներով, Խաբեությունն այն է, որ կապակցված Որոնման արժեքներ և որոնման զանգվածներ ի հետ «&» օպերատոր բանաձևում առանձին: Եկեք պատկերացնենք ստորև բերված օրինակով:

Մենք պետք է իմանանք միջին կապույտ ծաղկամանի գինը։ Այս դեպքում համընկնում փնտրելու համար պահանջվում է երեք որոնման արժեք (չափանիշ): Մուտքագրեք բանաձեւը ստորեւ I2 դատարկ բջիջում, ապա սեղմեք կոճակը Մտնել արդյունքը ստանալու բանալին:

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

xlookup ֆունկցիա 35

√ Նշում. XLOOKUP-ը կարող է ուղղակիորեն մշակել զանգվածները: Կարիք չկա բանաձևը հաստատել Control + Shift + Enter-ով:

Բազմապայմանների որոնում արագ մեթոդով

Կա որեւէ ավելի արագ և հեշտ Ինչպե՞ս իրականացնել բազմաչափ չափանիշներով որոնում, քան XLOOKUP-ը excel-ում: Excel- ի համար նախատեսված գործիքներ տրամադրում զարմանալի հատկություն - Բազմապայմանների որոնում. Այս հատկանիշով դուք կարող եք գործարկել բազմաթիվ չափանիշների որոնում ընդամենը մի քանի կտտոցով:

մեր Excel հավելում տեղադրված է, խնդրում ենք անել հետևյալը.

1. Գնալ Կուտոլս ներդիր Excel- ում, գտեք Super Փնտրելեւ սեղմեք Բազմապայմանների որոնում բացվող ցուցակում:

xlookup ֆունկցիա 36

2. Մեջ Բազմ պայմանի որոնում երկխոսության տուփխնդրում եմ արեք հետևյալը.

  • Է Որոնման արժեքներ և ելքային միջակայք բաժին, ընտրեք որոնման արժեքի միջակայք եւ ելքային տիրույթ;
  • Է Տվյալների տիրույթի բաժինԽնդրում ենք կատարել հետևյալ գործողությունները.
    • Ընտրել համապատասխան հիմնական սյունակներ որոնք պարունակում են որոնման արժեքները մեկ առ մեկ՝ պահելով կոճակը Ctrl բանալին Բանալի սյունակ տուփ;
    • նշեք սյունակ որը պարունակում է վերադարձված արժեքները Վերադարձի սյունակ տուփ.

xlookup ֆունկցիա 37

3. Սեղմեք OK կոճակը՝ արդյունքը ստանալու համար:

xlookup ֆունկցիա 38

√ Նշում.

  • Փոխարինել #N/A սխալի արժեքը նշված արժեքով բաժինը պարտադիր չէ երկխոսության վանդակում, կարող եք նշել այն, թե ոչ:
  • Հիմնական սյունակում մուտքագրված սյունակների թիվը պետք է հավասար լինի Փնտրման արժեքների վանդակում մուտքագրված սյունակների թվին, և երկու վանդակներում էլ չափանիշների հերթականությունը պետք է համապատասխանի մեկը մյուսին:

Կտտացրեք Excel- ի համար Kutools- ը ներբեռնելու համար ՝ 30-օրյա անվճար փորձաշրջանի համար.


Օրինակ 10. Գտեք արժեքը վերջին համընկնման հետ

Ստացեք վերջին համապատասխան արդյունքը XLOOKUP-ով

Գտնել վերջին համապատասխանող արժեքը Excel-ում սահմանեք վեցերորդ փաստարկ XLOOKUP ֆունկցիայի մեջ որոնել հակառակ հերթականությամբ.

Լռելյայնորեն, XLOOKUP-ում որոնման ռեժիմը դրված է 1-ի, Որը որոնել առաջինից մինչև վերջ. Բայց լավ բանը XLOOKUP այն է, որ որոնման ուղղությունը կարող է փոխվել. XLOOKUP-ն առաջարկում է ընտրովի [որոնման ռեժիմ] փաստարկ վերահսկելու որոնման կարգը: Պարզապես սահմանեք որոնման ռեժիմը վեցերորդ փաստարկում -1, որոնման ուղղությունը փոխվում է որոնելու վերջինից առաջինը:

Խնդրում ենք տեսնել ստորև բերված օրինակը: Մենք ուզում ենք իմանալ տվյալների բազայում Էմմայի վերջին վաճառքը:

Մուտքագրեք բանաձեւը ստորեւ G2 դատարկ բջիջում, այնուհետև սեղմեք կոճակը Մտնել արդյունքը ստանալու բանալին:

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

xlookup ֆունկցիա 39

√ Նշում. Չորրորդ և հինգերորդ արգումենտներն այս դեպքում կամընտիր են և բաց թողնված: Մենք միայն ընտրովի վեցերորդ արգումենտը սահմանեցինք -1:

Հեշտությամբ փնտրեք վերջին համընկնող արժեքը զարմանալի գործիքի միջոցով

Այն դեպքում, երբ դուք մուտք չունեք XLOOKUP, ինչպես նաև չեք ցանկանում հիշել բարդ բանաձևերը, կարող եք կիրառել Որոնում ներքևից վերև գործառույթ of Excel- ի համար նախատեսված գործիքներ այն իրականացնելու համար հեշտությամբ.

մեր Excel հավելում տեղադրված է, խնդրում ենք անել հետևյալը.

1. Գնալ Կուտոլս ներդիր Excel- ում, գտեք Super Փնտրելեւ սեղմեք Որոնում ներքևից վերև բացվող ցուցակում:

xlookup ֆունկցիա 40

2. Մեջ Փնտրեք ներքևից վերև երկխոսության տուփ, դուք պետք է կազմաձևեք հետևյալ կերպ.

  • Է Որոնման արժեքներ և ելքային տիրույթի բաժին, ընտրեք որոնման տիրույթ և ելքային տիրույթ;
  • Է Տվյալների տիրույթի բաժին, ընտրեք տվյալների տիրույթ, ապա նշեք հիմնական սյունակ և վերադարձի սյունակ.

xlookup ֆունկցիա 41

3. Սեղմեք OK կոճակը՝ արդյունքը ստանալու համար:

xlookup ֆունկցիա 42

√ Նշում. Փոխարինել #N/A սխալի արժեքը նշված արժեքով բաժինը պարտադիր չէ երկխոսության վանդակում, կարող եք նշել այն, թե ոչ:

Կտտացրեք Excel- ի համար Kutools- ը ներբեռնելու համար ՝ 30-օրյա անվճար փորձաշրջանի համար.


Ներբեռնեք XLOOKUP նմուշի ֆայլը

XLOOKUP Օրինակներ.xlsx

Առնչվող հոդվածներ քանակը:


  • Super Formula Bar (հեշտությամբ խմբագրեք տեքստի և բանաձևի բազմաթիվ տողեր); Ընթերցանության դասավորությունը (հեշտությամբ կարդալ և խմբագրել մեծ թվով բջիջներ); Տեղադրել ֆիլտրացված տիրույթում...
  • Միաձուլել բջիջները / տողերը / սյունակները և տվյալների պահում; Պառակտված բջիջների պարունակությունը; Միավորել կրկնօրինակ տողերն ու գումարը / միջինը... Կանխել կրկնօրինակ բջիջները; Համեմատեք միջակայքերը...
  • Ընտրեք Կրկնօրինակ կամ Եզակի Շարքեր; Ընտրեք դատարկ շարքեր (բոլոր բջիջները դատարկ են); Super Find և Fuzzy Find շատ աշխատանքային գրքույկներում; Պատահական ընտրություն ...
  • Actշգրիտ պատճեն Բազմաթիվ բջիջներ ՝ առանց բանաձևի հղումը փոխելու; Ավտոմատ ստեղծեք հղումներ դեպի մի քանի թերթեր; Տեղադրեք փամփուշտներ, Տուփեր և ավելին ...
  • Սիրված և արագ ներդիր բանաձևեր, Ընդգրկույթներ, գծապատկերներ և նկարներ; Ryածկագրել բջիջները գաղտնաբառով; Ստեղծեք փոստային ցուցակ և նամակներ ուղարկել ...
  • Քաղվածք տեքստ, Տեքստ ավելացնել, հեռացնել ըստ դիրքի, Հեռացնել տարածությունը; Ստեղծել և տպել էջային ենթագոտիներ; Փոխարկել բջիջների բովանդակության և մեկնաբանությունների միջև...
  • Սուպեր զտիչ (պահպանել և կիրառել ֆիլտրի սխեմաները այլ թերթերի վրա); Ընդլայնված տեսակավորում ըստ ամիս / շաբաթ / օր, հաճախականություն և ավելին; Հատուկ զտիչ համարձակ, շեղատառով ...
  • Միավորել աշխատանքային տետրերը և աշխատանքային թերթերը; Միավորել աղյուսակները ՝ հիմնված հիմնական սյունակների վրա; Տվյալները բաժանեք մի քանի թերթերի; Խմբաքանակի փոխակերպում xls, xlsx և PDF...
  • Առանցք սեղանի խմբավորում ըստ շաբաթվա համարը, շաբաթվա օրը և ավելին ... Showույց տալ ապակողպված, կողպված բջիջները տարբեր գույներով; Նշեք այն բջիջները, որոնք ունեն բանաձև / անուն...
kte էջանիշը 201905
  • Միացնել ներդիրներով խմբագրումը և ընթերցումը Word, Excel, PowerPoint- ով, Հրատարակիչ, Access, Visio և Project:
  • Բացեք և ստեղծեք բազմաթիվ փաստաթղթեր նույն պատուհանի նոր ներդիրներում, այլ ոչ թե նոր պատուհաններում:
  • Բարձրացնում է ձեր արտադրողականությունը 50%-ով և նվազեցնում մկնիկի հարյուրավոր սեղմումները ձեզ համար ամեն օր:
officetab ներքևում
Տեսակավորել մեկնաբանությունները ըստ
մեկնաբանություններ (0)
Դեռևս գնահատականներ չկան: Եղիր առաջինը, ով կգնահատի:
Առայժմ ոչ մի մեկնաբանություն չկա

Հետեւեք մեզ

Հեղինակային իրավունքի © 2009 - www.extendoffice.com. | Բոլոր իրավունքները պաշտպանված են. Powered by ExtendOffice. | | Կայքի քարտեզ
Microsoft- ը և Office- ի պատկերանշանը հանդիսանում են Microsoft Corporation- ի ապրանքային նշաններ կամ գրանցված ապրանքային նշաններ ԱՄՆ-ում և (կամ) այլ երկրներում:
Պաշտպանված է Sectigo SSL- ի կողմից