змест
У другой частцы нашага падручніка па функцыі ВПР (VLOOKUP) у Excel, мы прааналізуем некалькі прыкладаў, якія дапамогуць вам накіраваць усю моц ВПР для вырашэння самых амбіцыйных задач Excel. У прыкладах мяркуецца, што ў вас ужо ёсць базавыя веды аб тым, як працуе гэтая функцыя. Калі няма, магчыма, вам будзе цікава пачаць з першай часткі гэтага падручніка, у якой тлумачыцца сінтаксіс і асновы выкарыстання. ВПР. Што ж, пачнем.
Пошук у Excel па некалькіх крытэрыях
функцыя ВПР у Excel - сапраўды магутны інструмент для выканання пошуку пэўнага значэння ў базе дадзеных. Аднак ёсць істотнае абмежаванне - яго сінтаксіс дазваляе шукаць толькі адно значэнне. Што рабіць, калі вы хочаце шукаць па некалькіх умовах? Рашэнне вы знойдзеце ніжэй.
Прыклад 1: Пошук па 2 розных крытэрах
Дапусцім, у нас ёсць спіс заказаў, і мы хочам знайсці Колькасць тавару (Колькасць), на аснове двух крытэрыяў - Імя кліента (Кліент) и назва прадукту (Прадукт). Справа ўскладняецца тым, што кожны з пакупнікоў заказваў некалькі відаў тавару, як відаць з табліцы ніжэй:
рэгулярная функцыя ВПР не будзе працаваць у гэтым сцэнары, таму што ён верне першае знойдзенае значэнне, якое адпавядае зададзенаму значэнню пошуку. Напрыклад, калі вы хочаце даведацца колькасць тавару прысмакіпа замове пакупніка Джэрэмі Хіл, запішыце наступную формулу:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– гэтая формула верне вынік 15адпаведны прадукту яблыкі, таму што гэта першае значэнне, якое супадае.
Ёсць просты абыходны шлях - стварыць дадатковы слупок, у якім аб'яднаць усе патрэбныя крытэрыі. У нашым прыкладзе гэта слупкі Імя кліента (Кліент) и назва прадукту (Прадукт). Не забывайце, што аб'яднаны слупок заўсёды павінен быць самым левым слупком у дыяпазоне пошуку, бо гэта левы слупок, у якім функцыя ВПР глядзіць уверх, калі шукае значэнне.
Такім чынам, вы дадаеце ў табліцу дапаможны слупок і капіруеце наступную формулу ва ўсе яе вочкі: =B2&C2. Калі вы хочаце, каб радок быў больш чытэльным, вы можаце падзяліць аб'яднаныя значэнні прабелам: =B2&» «&C2. Пасля гэтага вы можаце выкарыстоўваць наступную формулу:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Дзе клетка B1 змяшчае аб'яднанае значэнне аргумента искомое_значение (пошукавае_значэнне) і 4 – Аргумент номер_столбца (column_number), г.зн. нумар слупка, які змяшчае дадзеныя, якія трэба атрымаць.
Прыклад 2: VLOOKUP па двух крытэрыях з праглядам табліцы на іншым аркушы
Калі вам неабходна абнавіць галоўную табліцу (Main table), дадаўшы дадзеныя з другой табліцы (Lookup table), якая знаходзіцца на іншым аркушы або ў іншай кнізе Excel, то вы можаце сабраць патрэбнае значэнне непасрэдна ў формуле, якую вы ўстаўляеце. у галоўную табліцу.
Як і ў папярэднім прыкладзе, вам спатрэбіцца дапаможны слупок у табліцы пошуку з камбінаванымі значэннямі. Гэты слупок павінен быць самым левым у дыяпазоне пошуку.
Такім чынам, формула з ВПР можа быць так:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Тут слупкі B і C змяшчаюць імёны кліентаў і назвы прадуктаў, адпаведна, і спасылку Заказы!$A&$2:$D$2 вызначае табліцу для пошуку на іншым аркушы.
Каб зрабіць формулу больш чытэльнай, вы можаце даць назву дыяпазону прагляду, і тады формула будзе выглядаць значна прасцей:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Каб формула працавала, значэнні ў крайнім левым слупку табліцы, якую вы разглядаеце, павінны быць аб'яднаны сапраўды гэтак жа, як і ў крытэрах пошуку. На малюнку вышэй мы аб'ядналі значэнні u2bu2band і паставілі прабел паміж імі, такім жа чынам, як вам трэба зрабіць у першым аргументе функцыі (BXNUMX& «» & CXNUMX).
Памятайце! функцыя ВПР абмежаваны 255 сімваламі, ён не можа шукаць значэнне, якое змяшчае больш за 255 сімвалаў. Майце гэта на ўвазе і пераканайцеся, што даўжыня жаданага значэння не перавышае гэты ліміт.
Згодны, што даданне дапаможнай калонкі - не самае элегантнае і не заўсёды прымальнае рашэнне. Вы можаце зрабіць тое ж самае без дапаможнага слупка, але для гэтага спатрэбіцца значна больш складаная формула з камбінацыяй функцый індэкс (ІНДЭКС) і МАТЧ (Больш выкрыта).
З дапамогай VLOOKUP здабываем 2-е, 3-е і г.д
Вы ўжо ведаеце, што ВПР можа вярнуць толькі адно адпаведнае значэнне, дакладней, першае знойдзенае. Але што, калі гэта значэнне паўтараецца некалькі разоў у прагляданым масіве, і вы хочаце атрымаць 2-е ці 3-е з іх? Што рабіць, калі ўсе каштоўнасці? Праблема здаецца складанай, але рашэнне ёсць!
Выкажам здагадку, што адзін слупок табліцы змяшчае імёны кліентаў (Customer Name), а другі слупок змяшчае прадукты (Product), якія яны купілі. Давайце паспрабуем знайсці 2-й, 3-й і 4-ы прадметы, набытыя дадзеным кліентам.
Самы просты спосаб - дадаць дапаможны слупок перад слупком Імя кліента і запоўніце яго імёнамі кліентаў з нумарам паўтарэння кожнага імя, напрыклад, Джон Доу 1, Джон Доу 2 і г. д. Мы будзем рабіць трук з нумарацыяй з дапамогай функцыі COUNTIF (COUNTIF), улічваючы, што імёны кліентаў знаходзяцца ў слупку B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Пасля гэтага вы можаце выкарыстоўваць звычайную функцыю ВПРзнайсці патрэбны парадак. Напрыклад:
- Знаходзіць 2-я тавар па замове заказчыка Дэн Браўн:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Знаходзіць 3-я тавар па замове заказчыка Дэн Браўн:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Фактычна, вы можаце ўвесці спасылку на ячэйку ў якасці значэння пошуку замест тэксту, як паказана на наступным малюнку:
Калі вы толькі шукаеце 2-e паўтарэнне, вы можаце зрабіць гэта без дапаможнага слупка, стварыўшы больш складаную формулу:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
У гэтай формуле:
- $F$2 – ячэйка з прозвішчам пакупніка (нязменна, звярніце ўвагу – спасылка абсалютная);
- $ B $ – калона Імя кліента;
- Table4 – Ваш стол (гэтым месцам можа быць і звычайны шэраг);
- 16 долараў ЗША – канчатковая ячэйка вашай табліцы або дыяпазону.
Гэтая формула знаходзіць толькі другое адпаведнае значэнне. Калі вам трэба атрымаць астатнія паўторы, выкарыстоўвайце папярэдняе рашэнне.
Калі вам патрэбны спіс усіх супадзенняў - функцыя ВПР гэта не памочнік, бо ён вяртае толькі адно значэнне за раз - кропка. Але ў Excel ёсць функцыя індэкс (INDEX), які лёгка справіцца з гэтай задачай. Як будзе выглядаць такая формула, вы даведаецеся на наступным прыкладзе.
Атрымаць усе паўторы патрэбнага значэння
Як ужо згадвалася вышэй, ВПР не можа атрымаць усе паўтаральныя значэнні з адсканаванага дыяпазону. Для гэтага вам спатрэбіцца крыху больш складаная формула, якая складаецца з некалькіх функцый Excel, такіх як індэкс (ІНДЭКС), МАЛЫ (МАЛЫ) і ROW (ЛІНІЯ)
Напрыклад, прыведзеная ніжэй формула знаходзіць усе паўторы значэння з ячэйкі F2 у дыяпазоне B2:B16 і вяртае вынік з тых жа радкоў у слупку C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Увядзіце гэту формулу масіва ў некалькі суседніх вочак, напрыклад у ячэйкі F4: F8як паказана на малюнку ніжэй. Колькасць вочак павінна быць роўная або большая за максімальна магчымую колькасць паўтораў шуканага значэння. Не забывайце націскаць Ctrl + Shift + Enterкаб правільна ўвесці формулу масіва.
Калі вам цікава зразумець, як гэта працуе, давайце крыху паглыбімся ў дэталі формулы:
Частка 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – параўнаць значэнне ў вочку F2 з кожным са значэнняў у дыяпазоне B2:B16. Калі супадзенне знойдзена, то выраз РАДКА(C2:C16)-1 вяртае нумар адпаведнага радка (значэнне -1 дазваляе не ўключаць радок загалоўка). Калі супадзенняў няма, функцыя IF (IF) вяртае пусты радок.
Вынік функцыі IF (КАЛІ) будзе такі гарызантальны масіў: {1,"",3,"",5,"","","","","","",12,"","",""}
Частка 2:
ROW()-3
СТРОКА()-3
Вось функцыя ROW (LINE) выконвае ролю дадатковага лічыльніка. Паколькі формула капіюецца ў ячэйкі F4:F9, мы адымаем лік 3 з выніку функцыі, каб атрымаць значэнне 1 у камеры F4 (радок 4, адняць 3), каб атрымаць 2 у камеры F5 (радок 5, адняць 3) і гэтак далей.
Частка 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
функцыя МАЛЫ (МАЛЫ) вяртаецца н-ой найменшае значэнне ў масіве даных. У нашым выпадку, якую пазіцыю (з найменшай) вярнуць, вызначае функцыя ROW (ЛІНІЯ) (гл. частку 2). Такім чынам, для клеткі F4 функцыя МАЛЫ ({масіў};1) Вяртае 1-я (найменшы) элемент масіва, г.зн 1. Для клеткі F5 Вяртае 2-я найменшы элемент у масіве, г.зн 3, І г.д.
Частка 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
функцыя індэкс (INDEX) проста вяртае значэнне пэўнай ячэйкі ў масіве С2: С16. Для клеткі F4 функцыя INDEX($C$2:$C$16) вернецца яблыкіДля F5 функцыя INDEX($C$2:$C$16) вернецца прысмакі і гэтак далей.
Частка 5:
IFERROR()
ЕСЛИОШИБКА()
Нарэшце, мы змяшчаем формулу ўнутр функцыі КАЛІПАМЫЛКА (IFERROR), таму што вы наўрад ці будзеце задаволены паведамленнем пра памылку #AT (#N/A), калі колькасць ячэек, у якія капіюецца формула, меншая за колькасць паўтаральных значэнняў у дыяпазоне, які праглядаецца.
XNUMXD пошук па вядомым радку і слупку
Выкананне XNUMXD пошуку ў Excel прадугледжвае пошук значэння па вядомым нумары радка і слупка. Іншымі словамі, вы здабываеце значэнне ячэйкі на скрыжаванні пэўнага радка і слупка.
Такім чынам, давайце звернемся да нашай табліцы і напішам формулу з функцыяй ВПР, дзе можна знайсці інфармацыю пра кошт прададзеных у сакавіку цытрын.
Ёсць некалькі спосабаў выканаць XNUMXD пошук. Праверце варыянты і выберыце той, які вам больш за ўсё падыходзіць.
Функцыі VLOOKUP і MATCH
Вы можаце выкарыстоўваць кучу функцый ВПР (VLOOKUP) і БОЛЬШ ВЫКЛЮЧАНЫ (MATCH), каб знайсці значэнне на скрыжаванні палёў назва прадукту (радок) і месяц (слупок) разгляданага масіва:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Прыведзеная вышэй формула - звычайная функцыя ВПР, які шукае дакладнае супадзенне значэння "Лімоны" ў ячэйках ад A2 да A9. Але паколькі вы не ведаеце, у якім слупку знаходзяцца сакавіцкія распродажы, вы не зможаце ўсталяваць нумар слупка для трэцяга аргумента функцыі. ВПР. Замест гэтага выкарыстоўваецца функцыя БОЛЬШ ВЫКЛЮЧАНЫкаб вызначыць гэты слупок.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
У перакладзе на чалавечую мову гэтая формула азначае:
- Шукаем персанажаў «Мар» - аргумент искомое_значение (значэнне_пошуку);
- Пошук у ячэйках ад A1 да I1 - аргумент пошукавы_масіў (масіў_прагляду);
- Вяртанне дакладнага супадзення - аргумент тып_супадзення (тып_супадзення).
Выкарыстанне 0 у трэцім аргументе вы кажаце функцыі БОЛЬШ ВЫКЛЮЧАНЫ шукайце першае значэнне, якое дакладна адпавядае таму значэнню, якое вы шукаеце. Гэта эквівалентна значэнню ХЛУСНЯ (ХЛУСНЯ) для чацвёртага аргумента ВПР.
Вось як вы можаце стварыць формулу двухбаковага пошуку ў Excel, таксама вядомую як двухмерны пошук або двухбаковы пошук.
Функцыя SUMPRODUCT
функцыя SUMPRODUCT (SUMPRODUCT) вяртае суму здабыткаў выбраных масіваў:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Функцыі INDEX і MATCH
У наступным артыкуле я падрабязна растлумачу гэтыя функцыі, так што пакуль вы можаце проста скапіяваць гэтую формулу:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Найменныя дыяпазоны і аператар перасячэння
Калі вам не падабаюцца ўсе гэтыя складаныя формулы Excel, вам можа спадабацца гэты візуальны і запамінальны спосаб:
- Выберыце табліцу, адкрыйце ўкладку формулы (Формулы) і націсніце Стварыць з выбару (Стварыць з вылучэння).
- Пастаўце галачкі Верхні шэраг (на радку вышэй) і Левы слупок (у калонцы злева). Microsoft Excel будзе прызначаць імёны дыяпазонам значэнняў у верхнім радку і левым слупку вашай электроннай табліцы. Цяпер вы можаце шукаць, выкарыстоўваючы гэтыя імёны непасрэдна без стварэння формул.
- У любую пустую клетку запішыце =назва_радка_назва_слупка, напрыклад, так:
=Лімоны Мар
... ці наадварот:
=Мар Лімонс
Памятайце, што імёны радкоў і слупкоў павінны быць падзеленыя прабелам, які ў дадзеным выпадку працуе як аператар перасячэння.
Калі вы ўводзіце імя, Microsoft Excel паказвае падказку са спісам адпаведных імёнаў, як і пры ўводзе формулы.
- прэс Уводзіць і праверыць вынік
Увогуле, які б з вышэйзгаданых метадаў вы ні абралі, вынік двухмернага пошуку будзе аднолькавым:
Выкарыстанне некалькіх VLOOKUP ў адной формуле
Здараецца, што галоўная табліца і табліца пошуку не маюць ніводнага агульнага слупка, і гэта не дазваляе выкарыстоўваць звычайную функцыю ВПР. Аднак ёсць іншая табліца, якая не змяшчае цікавую для нас інфармацыю, але мае агульны слупок з асноўнай табліцай і табліцай пошуку.
Давайце паглядзім на наступны прыклад. У нас ёсць галоўная табліца са слупком SKU (новы), куды вы хочаце дадаць слупок з адпаведнымі цэнамі з іншай табліцы. Акрамя таго, у нас ёсць 2 табліцы пошуку. Першы (Табліца пошуку 1) змяшчае абноўленыя лічбы SKU (новы) і назвы тавараў, а другая (Справочная табліца 2) – назвы тавараў і старыя нумары Артыкул (стары).
Каб дадаць цэны з другой табліцы пошуку ў галоўную табліцу, вы павінны выканаць дзеянне, вядомае як падвойнае ВПР або ўкладзеныя ВПР.
- Напісаць функцыю ВПР, які знаходзіць назву прадукту ў табліцы Пошукавая табліца 1выкарыстанне SKU, як патрэбнае значэнне:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Тут New_SKU – найменны дыяпазон $A:$B у табліцы Пошукавая табліца 1, 2 – гэта слупок B, які змяшчае назвы тавараў (гл. малюнак вышэй)
- Напішыце формулу для ўстаўкі цэн з табліцы Пошукавая табліца 2 на аснове вядомых назваў прадуктаў. Для гэтага ўстаўце формулу, якую вы стварылі раней, у якасці значэння пошуку для новай функцыі ВПР:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Тут цана – найменны дыяпазон $A:$C у табліцы Пошукавая табліца 2, 3 гэта слупок C, які змяшчае цэны.
На малюнку ніжэй паказаны вынік, які вяртаецца створанай намі формулай:
Дынамічная падстаноўка даных з розных табліц з дапамогай VLOOKUP і INDIRECT
Спачатку давайце ўдакладнім, што мы маем на ўвазе пад выразам «Дынамічная падстаноўка даных з розных табліц», каб пераканацца, што мы правільна разумеем адзін аднаго.
Бываюць сітуацыі, калі маецца некалькі лістоў з дадзенымі аднаго фармату, і неабходна атрымаць неабходную інфармацыю з пэўнага ліста ў залежнасці ад значэння, якое ўводзіцца ў дадзенае вочка. Я думаю, што прасцей растлумачыць гэта на прыкладзе.
Уявіце, што ў вас ёсць справаздачы аб продажах для некалькіх рэгіёнаў з аднолькавымі прадуктамі і ў адным фармаце. Вы хочаце знайсці лічбы продажаў для пэўнага рэгіёну:
Калі ў вас усяго два такіх справаздачы, то можна выкарыстоўваць ганебна простую формулу з функцыямі ВПР и IF (IF), каб выбраць патрэбную справаздачу для пошуку:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
дзе:
- $D$2 - гэта ячэйка, якая змяшчае назву прадукту. Звярніце ўвагу, што мы выкарыстоўваем тут абсалютныя спасылкі, каб пазбегнуць змены значэння пошуку пры капіраванні формулы ў іншыя ячэйкі.
- $D3 — ячэйка з назвай вобласці. Мы выкарыстоўваем абсалютную спасылку на слупок і адносную спасылку на радок, таму што плануем скапіяваць формулу ў іншыя ячэйкі ў тым жа слупку.
- FL_Салes и CA_Sales – назвы табліц (або названых дыяпазонаў), якія змяшчаюць адпаведныя справаздачы аб продажах. Вы, вядома, можаце выкарыстоўваць звычайныя назвы лістоў і спасылкі на дыяпазон вочак, напрыклад "Ліст FL"!$A$3:$B$10, але найменныя дыяпазоны нашмат зручней.
Аднак, калі такіх табліц шмат, функцыя IF не лепшае рашэнне. Замест гэтага вы можаце выкарыстоўваць функцыю ускосна (УСКОСНАЯ), каб вярнуць патрэбны дыяпазон пошуку.
Як вы, напэўна, ведаеце, функцыя ускосна выкарыстоўваецца для вяртання спасылкі, зададзенай тэкставым радком, што якраз тое, што нам зараз трэба. Такім чынам, смела замяняйце ў прыведзенай вышэй формуле выраз на функцыю IF звязаць з функцыяй ускосна. Вось такая камбінацыя ВПР и ускосна выдатна працуе з:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
дзе:
- $D$2 – гэта ячэйка з назвай тавару, яна не змяняецца дзякуючы абсалютнай спасылцы.
- $D3 гэта ячэйка, якая змяшчае першую частку назвы рэгіёну. У нашым прыкладзе гэта FL.
- _Продажы – агульная частка назвы ўсіх названых дыяпазонаў або табліц. У спалучэнні са значэннем у ячэйцы D3 яно ўтварае поўнае імя неабходнага дыяпазону. Ніжэй прыведзены некаторыя падрабязнасці для тых, хто пачатковец у гэтай функцыі ускосна.
Як працуюць INDIRECT і VLOOKUP
Спачатку дазвольце мне нагадаць вам сінтаксіс функцыі ускосна (УСКОСНА):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Першым аргументам можа быць спасылка на ячэйку (стыль A1 або R1C1), імя дыяпазону або тэкставы радок. Другі аргумент вызначае, які стыль спасылкі ўтрымліваецца ў першым аргументе:
- A1калі аргумент ёсць ПРАЎДНЫ КОД (TRUE) або не ўказана;
- R1C1, Калі FЯК Э (ХЛУСНЯ).
У нашым выпадку спасылка мае стыль A1, таму вы можаце пакінуць другі аргумент і засяродзіцца на першым.
Такім чынам, давайце вернемся да нашых справаздач аб продажах. Калі вы памятаеце, то кожная справаздача - гэта асобная табліца, размешчаная на асобным аркушы. Каб формула працавала правільна, вы павінны назваць свае табліцы (або дыяпазоны), і ўсе імёны павінны мець агульную частку. Напрыклад, так: CA_Sales, FL_Sales, TX_Sales і гэтак далей. Як бачыце, «_Sales» прысутнічае ва ўсіх назвах.
функцыя ускосна злучае значэнне ў слупку D і тэкставы радок «_Sales», тым самым кажучы ВПР у якой табліцы шукаць. Калі ячэйка D3 змяшчае значэнне «FL», формула будзе шукаць у табліцы FL_Sales, калі “CA” – у табл CA_Sales і гэтак далей.
Вынік функцый ВПР и ускосна будзе наступным:
Калі дадзеныя знаходзяцца ў розных кнігах Excel, вам трэба дадаць назву кнігі перад названым дыяпазонам, напрыклад:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Калі функцыя ускосна спасылаецца на іншую працоўную кнігу, гэтая працоўная кніга павінна быць адкрыта. Калі ён зачынены, функцыя паведаміць пра памылку. #REF! (#SSYL!).