змест
Учора на марафоне 30 функцый Excel за 30 дзён мы атрымалі задавальненне ад функцыі РЭПТ (Паўтор), ствараючы дыяграмы ўнутры ячэйкі і выкарыстоўваючы іх для простага падліку. Зараз панядзелак, і нам зноў час надзець шапку мысляра.
На 16-ы дзень марафону мы будзем вывучаць функцыю ШУКАЦЬ (ПРАГЛЯД). Гэта блізкі сябар ВПР (VLOOKUP) і ГПР (GPR), але ён працуе крыху па-іншаму.
Такім чынам, вывучаем тэорыю і правяраем функцыю на практыцы ШУКАЦЬ (ПРАГЛЯД). Калі ў вас ёсць дадатковая інфармацыя або прыклады выкарыстання гэтай функцыі, падзяліцеся імі ў каментарыях.
Функцыя 16: LOOKUP
функцыя ШУКАЦЬ (LOOKUP) вяртае значэнне з аднаго радка, аднаго слупка або з масіва.
Як я магу выкарыстоўваць функцыю LOOKUP?
функцыя ШУКАЦЬ (LOOKUP) вяртае вынік у залежнасці ад значэння, якое вы шукаеце. З яго дапамогай вы зможаце:
- Знайдзіце апошняе значэнне ў слупку.
- Знайдзіце апошні месяц з адмоўнымі продажамі.
- Пераўтварыце дасягненні студэнтаў з працэнтаў у літарныя адзнакі.
ПАСЛЯ сінтаксісу
функцыя ШУКАЦЬ (LOOKUP) мае дзве сінтаксічныя формы - вектар і масіў. У вектарнай форме функцыя шукае значэнне ў зададзеным слупку або радку, а ў форме масіва яна шукае значэнне ў першым радку або слупку масіва.
Вектарная форма мае наступны сінтаксіс:
LOOKUP(lookup_value,lookup_vector,result_vector)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)
- искомое_значение (lookup_value) – можа быць тэкстам, лічбай, лагічным значэннем, назвай або спасылкай.
- пошук_вектару (lookup_vector) – Дыяпазон, які складаецца з аднаго радка або аднаго слупка.
- выніковы_вектар (вектар_вынікаў) – дыяпазон, які складаецца з аднаго радка або аднаго слупка.
- дыяпазоны аргументаў пошук_вектару (вектар пошуку) і выніковы_вектар (вектар_вынікаў) павінен быць аднолькавага памеру.
Форма масіва мае наступны сінтаксіс:
LOOKUP(lookup_value,array)
ПРОСМОТР(искомое_значение;массив)
- искомое_значение (lookup_value) – можа быць тэкстам, лічбай, лагічным значэннем, назвай або спасылкай.
- пошук выконваецца па памернасці масіва:
- калі ў масіве слупкоў больш, чым радкоў, то пошук адбываецца ў першым радку;
- калі колькасць радкоў і слупкоў аднолькавая або радкоў больш, то пошук адбываецца ў першым слупку.
- функцыя вяртае апошняе значэнне са знойдзенага радка/слупка.
Пасткі ПРАГЛЯД (ПРАГЛЯД)
- У функцыі ШУКАЦЬ (ПРАГЛЯД) няма магчымасці пошуку дакладнага супадзення, якое знаходзіцца ў ВПР (VLOOKUP) і ў ГПР (GPR). Калі значэння пошуку няма, то функцыя верне максімальнае значэнне, якое не перавышае значэння пошуку.
- Масіў або вектар, які шукаецца, павінен быць адсартаваны ў парадку ўзрастання, інакш функцыя можа вярнуць няправільны вынік.
- Калі першае значэнне ў масіве/вектары, якое шукаецца, большае за значэнне пошуку, то функцыя згенеруе паведамленне пра памылку #AT (#Н/Д).
Прыклад 1: Пошук апошняга значэння ў слупку
У выглядзе функцыі масіва ШУКАЦЬ (LOOKUP) можна выкарыстоўваць для пошуку апошняга значэння ў слупку.
Даведка Excel паказвае значэнне 9,99999999999999E + 307 як найбольшы лік, які можна запісаць у клетку. У нашай формуле ён будзе зададзены ў якасці патрэбнага значэння. Мяркуецца, што такая вялікая колькасць не будзе знойдзена, таму функцыя верне апошняе значэнне ў слупку D.
У дадзеным прыкладзе лічбы ў слупку D дазволена не сартаваць, акрамя таго, могуць трапляцца тэкставыя значэння.
=LOOKUP(9.99999999999999E+307,D:D)
=ПРОСМОТР(9,99999999999999E+307;D:D)
Прыклад 2: Знайдзіце апошні месяц з адмоўным значэннем
У гэтым прыкладзе мы будзем выкарыстоўваць вектарную форму ШУКАЦЬ (ПРАГЛЯД). Слупок D змяшчае значэнні продажаў, а слупок E - назвы месяцаў. У некаторыя месяцы справы ішлі не так, і ў вочках са значэннямі продажаў з'яўляліся адмоўныя лічбы.
Каб знайсці апошні месяц з адмоўным лікам, формула с ШУКАЦЬ (LOOKUP) будзе правяраць для кожнага значэння продажаў, што яно менш 0 (няроўнасць у формуле). Далей дзелім 1 у выніку мы атрымліваем або адно 1, або паведамленне пра памылку #DIV/0 (# РАЗДЗЕЛ/0).
Так як шуканае значэнне ёсць 2 не знойдзены, функцыя абярэ апошні знойдзены 1, і вярнуць адпаведнае значэнне са слупка E.
=LOOKUP(2,1/(D2:D8<0),E2:E8)
=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)
Тлумачэнне: У гэтай формуле замест аргумента пошук_вектару (lookup_vector) выраз падстаўлены 1/(D2:D8<0), які ўтварае ў аператыўнай памяці кампутара масіў, які складаецца з 1 і значэнні памылак #DIV/0 (# РАЗДЗЕЛ/0). 1 паказвае, што адпаведная ячэйка ў дыяпазоне D2:D8 змяшчае значэнне, меншае за 0, і памылка #DIV/0 (#DIV/0) – тое, што больш або роўна 0. У выніку наша задача - знайсці апошняга 1 у створаны віртуальны масіў, і на падставе гэтага вярнуць назву месяца з дыяпазону E2:E8.
Прыклад 3: пераўтварэнне паспяховасці студэнтаў з працэнтаў у літарныя адзнакі
Раней мы ўжо вырашалі падобную задачу з дапамогай функцыі ВПР (ВПР). Сёння мы будзем выкарыстоўваць функцыю ШУКАЦЬ (VIEW) у вектарнай форме для пераўтварэння дасягненняў студэнтаў з працэнтаў у літарныя адзнакі. У адрозненне ад ВПР (VLOOKUP) для функцыі ШУКАЦЬ (ПРАГЛЯД) Не мае значэння, ці ёсць працэнты ў першым слупку табліцы. Вы можаце выбраць абсалютна любы слупок.
У наступным прыкладзе балы знаходзяцца ў слупку D, адсартаваныя ў парадку ўзрастання, а адпаведныя ім літары знаходзяцца ў слупку C, злева ад слупка, у якім праводзіцца пошук.
=LOOKUP(C10,D4:D8,C4:C8)
=ПРОСМОТР(C10;D4:D8;C4:C8)