Բովանդակություն
Այս փոքրիկ ձեռնարկը բացատրում է, թե ինչպես կատարել գործառույթ VPR- ը (VLOOKUP) մեծատառերի նկատմամբ զգայուն, ցույց է տալիս մի քանի այլ բանաձևեր, որոնք Excel-ը կարող է որոնել մեծատառերի նկատմամբ զգայուն ձևով և մատնանշում է յուրաքանչյուր ֆունկցիայի ուժեղ և թույլ կողմերը:
Կարծում եմ, Excel-ի յուրաքանչյուր օգտվող գիտի, թե ինչ գործառույթ է կատարում ուղղահայաց որոնումը: Ճիշտ է, դա ֆունկցիա է VPR- ը. Այնուամենայնիվ, քչերը գիտեն դա VPR- ը մեծատառերի զգայուն չէ, այսինքն՝ նրա համար փոքրատառ և մեծատառ նիշերը նույնական են:
Ահա մի արագ օրինակ, որը ցույց է տալիս անկարողությունը VPR- ը ճանաչել ռեգիստրը. Ենթադրենք խցում A1 պարունակում է «հաշիվ» արժեքը և բջիջը A2 – «Բիլ», բանաձև.
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… կդադարեցնի իր որոնումը «օրինագծում», քանի որ այդ արժեքը առաջինն է ցուցակում և կհանի արժեքը բջիջից B1.
Հետագայում այս հոդվածում ես ձեզ ցույց կտամ, թե ինչպես դա անել VPR- ը գործի զգայուն: Բացի այդ, մենք կսովորենք ևս մի քանի գործառույթ, որոնք կարող են Excel-ում կատարել մեծատառերի զգայուն որոնումներ:
Մենք կսկսենք ամենապարզից. VIEW (ՓՆՏՐԵԼ) և SUMPRODUCT- ը (SUMPRODUCT), որոնք, ցավոք, ունեն մի քանի էական սահմանափակումներ։ Հաջորդը, մենք ավելի մանրամասն կանդրադառնանք մի փոքր ավելի բարդ բանաձևին INDEX+MATCH (INDEX+MATCH), որն աշխատում է անթերի ցանկացած իրավիճակում և ցանկացած տվյալների բազայում:
VLOOKUP ֆունկցիան մեծատառերի զգայուն է
Ինչպես արդեն գիտեք, սովորական գործառույթը VPR- ը մեծատառերի զգայուն չէ: Այնուամենայնիվ, կա մի միջոց, որը թույլ է տալիս զգայուն դարձնել այն: Դա անելու համար հարկավոր է աղյուսակում ավելացնել օժանդակ սյունակ, ինչպես ցույց է տրված հետևյալ օրինակում։
Ենթադրենք սյունակում B կան ապրանքի նույնացուցիչներ (Նյութ) և ցանկանում եք սյունակներից հանել ապրանքի գինը և համապատասխան մեկնաբանությունը C и D. Խնդիրն այն է, որ նույնացուցիչները պարունակում են ինչպես փոքր, այնպես էլ մեծատառ նիշեր: Օրինակ, բջջային արժեքները B4 (001Tvci3u) և B5 (001Tvci3U) տարբերվում են միայն վերջին նիշի դեպքում, u и U համապատասխանաբար:
Ինչպես կարող եք պատկերացնել, սովորական որոնման բանաձեւը
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
կվերադառնա $ 90, քանի որ արժեքը 001Tvci3u ավելի վաղ գտնվում է որոնման տիրույթում 001Tvci3U. Բայց դա այն չէ, ինչ մեզ պետք է, այնպես չէ՞:
Գործառույթով որոնելու համար VPR- ը Excel-ի դեպքում դուք պետք է ավելացնեք օգնական սյունակ և լրացնեք դրա բջիջները հետևյալ բանաձևով (որտեղ B-ն որոնման սյունակն է).
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Այս բանաձևը բաժանում է ցանկալի արժեքը առանձին նիշերի, փոխարինում է յուրաքանչյուր նիշ իր կոդով (օրինակ՝ փոխարենը A 65-ին, փոխարենը a կոդը 97) և այնուհետև միավորում է այս կոդերը թվերի եզակի շղթայի մեջ:
Դրանից հետո մենք օգտագործում ենք պարզ գործառույթ VPR- ը դեպքերի զգայուն որոնման համար՝
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Ֆունկցիայի ճիշտ շահագործում VPR- ը Գործի զգայունությունը կախված է երկու գործոնից.
- Օգնական սյունակը պետք է լինի դիտելի տիրույթի ամենաձախ սյունակը:
- Ձեր փնտրած արժեքը իրական արժեքի փոխարեն պետք է պարունակի նիշերի կոդը:
Ինչպես ճիշտ օգտագործել CODE ֆունկցիան
Օժանդակ սյունակի բջիջներում տեղադրված բանաձևը ենթադրում է, որ ձեր որոնման բոլոր արժեքներն ունեն նույն թվով նիշ: Եթե ոչ, ապա դուք պետք է իմանաք ամենափոքր և ամենամեծ թվերը և ավելացնեք նույնքան հնարավորություններ ԻՐԱER (IFERROR) քանի նիշ է տարբերությունը ամենակարճ և ամենաերկար որոնված արժեքի միջև:
Օրինակ, եթե որոնման ամենակարճ արժեքը 3 նիշ է, իսկ ամենաերկարը 5 նիշ, օգտագործեք այս բանաձևը.
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Գործառույթի համար ՊՍՏՐ (MID) Դուք տրամադրում եք հետևյալ փաստարկները.
- 1-ին փաստարկ – տեքստ (տեքստ) տեքստն է կամ բջջային հղումը, որը պարունակում է արդյունահանվող նիշերը (մեր դեպքում դա B2 է)
- 2-ին փաստարկ – սկիզբ_թիվ (start_position) այն նիշերից առաջինի դիրքն է, որը պետք է հանվի: դու մտնում ես 1 առաջին գործառույթում ՊՍՏՐ, 2 - երկրորդ գործառույթում ՊՍՏՐ եւ այլն:
- 3-ին փաստարկ – num_chars (նիշերի_թիվ) – Նշում է տեքստից հանվող նիշերի քանակը: Քանի որ մեզ անընդհատ անհրաժեշտ է ընդամենը 1 նիշ, բոլոր գործառույթներում մենք գրում ենք 1.
ՍԱՀՄԱՆԱՓԱԿՈՒՄՆԵՐ: ֆունկցիա VPR- ը Excel-ում մեծատառերի զգայուն որոնումների համար լավագույն լուծումը չէ: Նախ, անհրաժեշտ է օժանդակ սյունակի ավելացում: Երկրորդ, բանաձևը լավ է աշխատում միայն այն դեպքում, եթե տվյալները միատարր են, կամ հայտնի է որոնված արժեքների նիշերի ճշգրիտ թիվը: Եթե դա ձեր դեպքը չէ, ապա ավելի լավ է օգտագործել լուծումներից մեկը, որը մենք ցույց ենք տալիս ստորև:
LOOKUP ֆունկցիա՝ մեծատառերի զգայուն որոնման համար
ֆունկցիա VIEW (ՓՆՏՐԵԼ) կապված VPR- ը, սակայն դրա շարահյուսությունը թույլ է տալիս որոնումներ կատարել մեծատառերի նկատմամբ՝ առանց օժանդակ սյունակ ավելացնելու։ Դա անելու համար օգտագործեք VIEW ֆունկցիայի հետ համակցված EXACT- ը (ՃՈՒՇՏ):
Եթե վերցնենք տվյալները նախորդ օրինակից (առանց օժանդակ սյունակի), ապա հետևյալ բանաձևը կհաղթահարի առաջադրանքը.
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Բանաձևի որոնումներ տիրույթում A2: A7 ճշգրիտ համընկնում բջջային արժեքի հետ F2 մեծատառերի զգայուն և վերադարձնում է արժեքը նույն տողի B սյունակից:
նման VPR- ըֆունկցիա VIEW աշխատում է հավասարապես տեքստի և թվային արժեքների հետ, ինչպես կարող եք տեսնել ստորև ներկայացված սքրինշոթում.
Կարեւոր. Գործառույթի համար VIEW ճիշտ աշխատել, որոնման սյունակում արժեքները պետք է դասավորվեն աճման կարգով, այսինքն՝ ամենափոքրից մինչև ամենամեծը:
Թույլ տվեք համառոտ բացատրել, թե ինչպես է գործում գործառույթը EXACT- ը վերը նշված բանաձևում, քանի որ սա հիմնական կետն է:
ֆունկցիա EXACT- ը համեմատում է տեքստի երկու արժեքները 1-ին և 2-րդ արգումենտներում և վերադարձնում է TRUE, եթե դրանք միանգամայն նույնն են, կամ FALSE, եթե դրանք չեն: Մեզ համար կարևոր է, որ գործառույթը EXACT- ը գործի զգայուն:
Տեսնենք, թե ինչպես է աշխատում մեր բանաձևը ԴԻՏԵԼ+ՃԻՇՏ:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- ֆունկցիա EXACT- ը համեմատում է բջջային արժեքը F2 բոլոր տարրերով սյունակում A (A2: A7): Վերադարձնում է TRUE, եթե գտնվի ճշգրիտ համընկնում, հակառակ դեպքում՝ FALSE:
- Քանի որ դուք տալիս եք առաջին ֆունկցիայի փաստարկը VIEW արժեքը TRUE, այն հանում է համապատասխան արժեքը նշված սյունակից (մեր դեպքում՝ սյունակ B) միայն այն դեպքում, եթե հայտնաբերվում է ճշգրիտ համընկնում, մեծատառերի զգայուն:
Հուսով եմ, որ այս բացատրությունը պարզ էր, և այժմ դուք հասկանում եք հիմնական գաղափարը: Եթե այո, ապա դուք որևէ դժվարություն չեք ունենա այլ գործառույթների հետ կապված, որոնք մենք հետագայում կվերլուծենք, քանի որ. նրանք բոլորն աշխատում են նույն սկզբունքով:
ՍԱՀՄԱՆԱՓԱԿՈՒՄՆԵՐ: Փնտրման սյունակի տվյալները պետք է տեսակավորվեն աճման կարգով:
SUMPRODUCT – գտնում է տեքստային արժեքներ, մեծատառերի զգայուն, բայց վերադարձնում է միայն թվեր
Ինչպես արդեն հասկացաք վերնագրից. SUMPRODUCT- ը (SUMPRODUCT) Excel-ի մեկ այլ ֆունկցիա է, որը կօգնի ձեզ կատարել մեծատառերի զգայուն որոնում, բայց կվերադարձնի միայն թվային արժեքներ: Եթե այս տարբերակը չի համապատասխանում ձեզ, ապա կարող եք անմիջապես անցնել փաթեթին INDEX+MATCH, որը լուծում է տալիս ցանկացած դեպքի և տվյալների ցանկացած տեսակի համար։
Նախ, թույլ տվեք համառոտ բացատրել այս ֆունկցիայի շարահյուսությունը, սա կօգնի ձեզ ավելի լավ հասկանալ ստորև նշված մեծատառերի զգայուն բանաձևը:
ֆունկցիա SUMPRODUCT- ը բազմապատկում է տրված զանգվածների տարրերը և վերադարձնում արդյունքների գումարը։ Շարահյուսությունն այսպիսի տեսք ունի.
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Քանի որ մեզ անհրաժեշտ է մեծատառով որոնում, մենք օգտագործում ենք ֆունկցիան EXACT- ը (ՃԻՇՏ) նախորդ օրինակից որպես բազմապատկիչներից մեկը.
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Ինչպես հիշում եք, EXACT- ը համեմատում է բջջային արժեքը F2 բոլոր տարրերով սյունակում A. Վերադարձնում է TRUE, եթե գտնվի ճշգրիտ համընկնում, հակառակ դեպքում՝ FALSE: Մաթեմատիկական գործողություններում Excel-ը ընդունում է TRUE որպես 1, և FALSE-ի համար 0Հետագա SUMPRODUCT- ը բազմապատկում է այս թվերը և գումարում արդյունքները:
Զրոները չեն հաշվվում, քանի որ բազմապատկելիս միշտ տալիս են 0. Եկեք ավելի սերտ նայենք, թե ինչ է տեղի ունենում, երբ ճշգրիտ համընկնում է սյունակում A հայտնաբերվել և վերադարձվել է 1… Գործառույթ SUMPRODUCT- ը բազմապատկում է սյունակի թիվը B on 1 և վերադարձնում է արդյունքը՝ ճիշտ նույն թիվը: Դա պայմանավորված է նրանով, որ մյուս ապրանքների արդյունքները զրո են, և դրանք չեն ազդում ստացված գումարի վրա:
Ցավոք, գործառույթը SUMPRODUCT- ը չի կարող աշխատել տեքստային արժեքների և ամսաթվերի հետ, քանի որ դրանք չեն կարող բազմապատկվել: Այս դեպքում դուք կստանաք սխալի հաղորդագրություն #VALUE! (#ԱՐԺԵՔ!) ինչպես խցում F4 ստորև նկարում.
ՍԱՀՄԱՆԱՓԱԿՈՒՄՆԵՐ: Վերադարձնում է միայն թվային արժեքներ:
INDEX + MATCH – մեծատառերի զգայուն որոնում ցանկացած տվյալների տեսակի համար
Վերջապես, մենք մոտ ենք անսահմանափակ և գործի զգայուն որոնման բանաձևին, որն աշխատում է ցանկացած տվյալների հավաքածուի հետ:
Այս օրինակը վերջինն է, ոչ թե այն պատճառով, որ լավագույնը թողնվում է դեսերտ պատրաստելու համար, այլ որովհետև նախորդ օրինակներից ստացված գիտելիքները կօգնեն ձեզ ավելի լավ և արագ հասկանալ տառերի նկատմամբ զգայուն բանաձևը: INDEX+MATCH (ԻԴԵՔՍ+ՀԱՄԱԽՄԲՈՒՄ):
Ինչպես հավանաբար կռահեցիք, գործառույթների համադրություն ԱՎԵԼԻ ԲԱՑՎԱԾ и INDEX օգտագործվում է Excel-ում որպես ավելի ճկուն և հզոր այլընտրանք VPR- ը. VLOOKUP-ի փոխարեն INDEX-ի և MATCH-ի օգտագործումը հոդվածում հիանալի կերպով կբացատրվի, թե ինչպես են այս գործառույթները միասին աշխատում:
Ես պարզապես ամփոփեմ հիմնական կետերը.
- ֆունկցիա ԱՎԵԼԻ ԲԱՑՎԱԾ (MATCH) որոնում է արժեք տվյալ տիրույթում և վերադարձնում դրա հարաբերական դիրքը, այսինքն՝ տողի և/կամ սյունակի համարը.
- Հաջորդը, գործառույթը INDEX (INDEX) վերադարձնում է արժեք նշված սյունակից և/կամ տողից:
Բանաձևին INDEX+MATCH կարող է որոնել մեծատառերի զգայունությամբ, դրան պետք է միայն մեկ ֆունկցիա ավելացնել: Դժվար չէ կռահել, թե ինչ է դա նորից EXACT- ը (ՃՈՒՇՏ):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Այս բանաձեւում EXACT- ը աշխատում է այնպես, ինչպես ֆունկցիայի հետ համատեղ VIEW, և տալիս է նույն արդյունքը.
Նշենք, որ բանաձեւը INDEX+MATCH գանգուր փակագծերի մեջ փակցված է զանգվածի բանաձև, և դուք պետք է այն լրացնեք սեղմելով Ctrl + Shift + Մուտք.
Ինչո՞ւ է INDEX+MATCH-ը լավագույն լուծումը մեծատառերի նկատմամբ զգայուն որոնման համար:
Փաթեթի հիմնական առավելությունները INDEX и ԱՎԵԼԻ ԲԱՑՎԱԾ:
- Չի պահանջում ավելացնել օժանդակ սյունակ, ի տարբերություն VPR- ը.
- Չի պահանջում որոնման սյունակը տեսակավորել, ի տարբերություն VIEW.
- Աշխատում է բոլոր տեսակի տվյալների հետ՝ թվեր, տեքստեր և ամսաթվեր:
Այս բանաձեւը կատարյալ է թվում, այնպես չէ՞: Իրականում դա այդպես չէ։ Եվ ահա թե ինչու։
Ենթադրենք, որ որոնման արժեքի հետ կապված վերադարձի արժեքի սյունակի բջիջը դատարկ է: Ի՞նչ արդյունք կբերի բանաձևը: Ոչ? Տեսնենք, թե իրականում ինչ բանաձև է վերադարձնում.
Վա՜յ, բանաձևը վերադարձնում է զրո: Սա չի կարող մեծ խնդիր լինել, եթե դուք աշխատում եք մաքուր տեքստային արժեքներով: Այնուամենայնիվ, եթե աղյուսակը պարունակում է թվեր, ներառյալ «իրական» զրոները, դա խնդիր է դառնում:
Փաստորեն, բոլոր մյուս որոնման բանաձևերը (VLOOKUP, LOOKUP և SUMPRODUCT), որոնք մենք քննարկել ենք ավելի վաղ, նույն կերպ են վարվում: Բայց դուք ցանկանում եք կատարյալ բանաձեւ, այնպես չէ՞:
Բանաձևը մեծատառով զգայուն դարձնելու համար INDEX+MATCH կատարյալ, դրեք այն ֆունկցիայի մեջ IF (IF), որը կփորձարկի վերադարձվող արժեքով բջիջը և դատարկ արդյունք կվերադարձնի, եթե այն դատարկ է.
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Այս բանաձեւում.
- B վերադարձի արժեքներով սյունակ է
- 1+ թիվ է, որը շրջում է ֆունկցիայի կողմից վերադարձված բջջի հարաբերական դիրքը ԱՎԵԼԻ ԲԱՑՎԱԾ, բջջի իրական հասցեով։ Օրինակ՝ մեր ֆունկցիայի մեջ ԱՎԵԼԻ ԲԱՑՎԱԾ տրված է որոնման զանգված A2: A7, այսինքն՝ բջջի հարաբերական դիրքը A2 կամք 1, քանի որ այն առաջինն է զանգվածում։ Բայց բջջի իրական դիրքը A2 սյունակում է 2, ուստի ավելացնում ենք 1տարբերությունը լրացնելու և ֆունկցիան ունենալու համար Անկախ (ԱՆՈՒՂԻՂ) վերցրեց արժեքը ցանկալի բջիջից:
Ստորև բերված նկարները ցույց են տալիս շտկված մեծատառերի զգայուն բանաձևը INDEX+MATCH Գործողության մեջ. Այն վերադարձնում է դատարկ արդյունք, եթե վերադարձված բջիջը դատարկ է:
Ես բանաձեւը վերագրեցի սյունակների մեջ B:Dսքրինշոթի վրա բանաձևի տողը տեղավորելու համար:
Բանաձևը վերադառնում է 0եթե վերադարձված բջիջը զրո է պարունակում:
Եթե ցանկանում եք հղումը INDEX и ԱՎԵԼԻ ԲԱՑՎԱԾ ցուցադրվում է որոշ հաղորդագրություն, երբ վերադարձի արժեքը դատարկ է, կարող եք այն գրել բանաձևի վերջին չակերտներում («»), օրինակ՝ այսպես.
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
