30 функцый Excel за 30 дзён: LOOKUP

Учора на марафоне 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)

30 функцый Excel за 30 дзён: LOOKUP

Прыклад 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)

30 функцый Excel за 30 дзён: LOOKUP

Тлумачэнне: У гэтай формуле замест аргумента пошук_вектару (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)

30 функцый Excel за 30 дзён: LOOKUP

Пакінуць каментар