змест
Нядаўна мы прысвяцілі артыкул адной з самых карысных функцый Excel пад назвай ВПР і паказаў, як яго можна выкарыстоўваць для здабывання неабходнай інфармацыі з базы дадзеных у ячэйку працоўнага ліста. Мы таксама згадвалі, што ёсць два варыянты выкарыстання функцыі ВПР і толькі адзін з іх займаецца запытамі да базы дадзеных. У гэтым артыкуле вы даведаецеся яшчэ адзін менш вядомы спосаб выкарыстання функцыі ВПР у Excel.
Калі вы гэтага яшчэ не рабілі, то абавязкова прачытайце апошнюю артыкул аб функцыі ВПР, таму што ўся інфармацыя ніжэй мяркуе, што вы ўжо знаёмыя з прынцыпамі, апісанымі ў першым артыкуле.
Пры рабоце з базамі даных функцыян ВПР перадаецца унікальны ідэнтыфікатар, які выкарыстоўваецца для ідэнтыфікацыі інфармацыі, якую мы хочам знайсці (напрыклад, код прадукту або ідэнтыфікацыйны нумар кліента). Гэты ўнікальны код павінен прысутнічаць у базе дадзеных, інакш ВПР паведаміць пра памылку. У гэтым артыкуле мы разгледзім гэты спосаб выкарыстання функцыі ВПРкалі ідэнтыфікатар наогул не існуе ў базе дадзеных. Як бы функцыя ВПР пераключыўся ў прыблізны рэжым і выбірае, якія даныя прадастаўляць нам, калі мы хочам нешта знайсці. У пэўных абставінах гэта менавіта тое, што трэба.
Прыклад з жыцця. Ставім задачу
Давайце праілюструем гэты артыкул рэальным прыкладам - разлікам камісій на аснове шырокага дыяпазону паказчыкаў продажаў. Мы пачнем з вельмі простага варыянту, а потым будзем яго паступова ўскладняць, пакуль адзіным рацыянальным рашэннем праблемы не стане выкарыстанне функцыі ВПР. Першапачатковы сцэнар для нашай фіктыўнай задачы наступны: калі прадавец робіць больш за $30000 продажаў у год, то яго камісія складае 30%. У адваротным выпадку камісія складае ўсяго 20%. Аформім гэта ў выглядзе табліцы:
Прадавец уводзіць дадзеныя аб сваіх продажах у ячэйку B1, а формула ў ячэйцы B2 вызначае правільную стаўку камісійных, якую можа чакаць прадавец. У сваю чаргу, атрыманая стаўка выкарыстоўваецца ў ячэйцы B3 для разліку агульнай камісіі, якую павінен атрымаць прадавец (простым множаннем клетак B1 і B2).
Самая цікавая частка табліцы змяшчаецца ў вочку B2 - гэта формула для вызначэння стаўкі камісіі. Гэтая формула змяшчае функцыю Excel пад назвай IF (КАЛІ). Для тых чытачоў, якія не знаёмыя з гэтай функцыяй, я растлумачу, як яна працуе:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Стан гэта аргумент функцыі, які прымае значэнне любога з іх ПРАЎДНЫ КОД (ПРАЎДА), або ХЛУСНЯ (ХЛУСНЯ). У прыведзеным вышэй прыкладзе выраз B1
Ці праўда, што B1 менш, чым B5?
Ці вы можаце сказаць гэта па-іншаму:
Ці праўда, што агульная сума продажаў за год меншая за парогавае значэнне?
Калі мы адкажам на гэтае пытанне ДА (TRUE), тады функцыя вяртаецца значэнне, калі праўда (значэнне, калі TRUE). У нашым выпадку гэта будзе значэнне ячэйкі B6, г.зн. стаўка камісіі, калі агульны аб'ём продажаў ніжэйшы за парог. Калі мы адкажам на пытанне НЕ (FALSE), затым вяртаецца значэнне, калі ілжыва (значэнне, калі FALSE). У нашым выпадку гэта значэнне ячэйкі B7, г.зн. стаўка камісіі, калі агульны аб'ём продажаў перавышае парог.
Як вы можаце бачыць, калі мы возьмем агульны аб'ём продажаў у 20000 2 долараў, мы атрымаем 20% камісійных у ячэйцы B40000. Калі мы ўвядзем значэнне $30, то стаўка камісіі зменіцца на XNUMX%:
Вось як працуе наша табліца.
Ўскладняем задачу
Давайце зробім усё крыху больш складана. Усталюем іншы парог: калі прадавец зарабляе больш за $40000, то камісія павялічваецца да 40%:
Быццам бы ўсё проста і зразумела, але наша формула ў вочку B2 прыкметна ўскладняецца. Калі вы ўважліва паглядзіце на формулу, вы ўбачыце, што трэці аргумент функцыі IF (IF) ператварылася ў яшчэ адну паўнавартасную функцыю IF (КАЛІ). Такая канструкцыя называецца ўкладаннем функцый адна ў адну. Excel з задавальненнем дазваляе гэтыя канструкцыі, і яны нават працуюць, але іх значна цяжэй прачытаць і зразумець.
Мы не будзем паглыбляцца ў тэхнічныя дэталі - чаму і як гэта працуе, а таксама не будзем паглыбляцца ў нюансы напісання ўкладзеных функцый. У рэшце рэшт, гэта артыкул, прысвечаны функцыі ВПР, не поўнае кіраўніцтва па Excel.
У любым выпадку формула ўскладняецца! Што рабіць, калі мы ўвядзем яшчэ адзін варыянт камісійнай стаўкі ў памеры 50% для тых прадаўцоў, якія робяць продажы больш за 50000 60000 долараў. І калі хтосьці прадаў больш за 60 XNUMX долараў, ці заплаціць ён XNUMX% камісійных?
Цяпер формула ў вочку B2, нават калі яна была напісана без памылак, стала зусім нечытэльнай. Я думаю, што мала хто хоча выкарыстоўваць у сваіх праектах формулы з 4 ўзроўнямі ўкладзенасці. Павінен быць больш просты спосаб?!
І такі спосаб ёсць! Функцыя нам дапаможа ВПР.
Для вырашэння праблемы ўжываем функцыю VLOOKUP
Давайце трохі зменім дызайн нашага стала. Мы захаваем тыя ж палі і дадзеныя, але размясцім іх па-новаму, больш кампактна:
Знайдзіце хвілінку і пераканайцеся ў новай табліцы Табліца тарыфаў змяшчае тыя ж дадзеныя, што і папярэдняя табліца парогаў.
Асноўная ідэя - выкарыстоўваць функцыю ВПР вызначыць жаданую тарыфную стаўку па табл Табліца тарыфаў у залежнасці ад аб'ёму продажаў. Звярніце ўвагу, што прадавец можа прадаць тавар на суму, якая не роўная аднаму з пяці парогавых значэнняў у табліцы. Напрыклад, ён мог бы прадаць за $34988, але такой сумы няма. Давайце паглядзім, як функцыя ВПР можа справіцца з такой сітуацыяй.
Устаўка функцыі VLOOKUP
Выберыце ячэйку B2 (куды мы хочам уставіць нашу формулу) і знайдзіце ВПР (VLOOKUP) у бібліятэцы функцый Excel: формулы (формулы) > бібліятэка функцый (Бібліятэка функцый) > Пошук і даведкі (Спасылкі і масівы).
З'явіцца дыялогавае акно Аргументы функцыі (Аргументы функцыі). Запаўняем значэння аргументаў па чарзе, пачынаючы з Пошук_значэння (Пашуковае_значэнне). У гэтым прыкладзе гэта агульная сума продажаў з ячэйкі B1. Пастаўце курсор у поле Пошук_значэння (Пошукавае_значэнне) і абярыце ячэйку B1.
Далей трэба пазначыць функцыі ВПРдзе шукаць дадзеныя. У нашым прыкладзе гэта табліца Табліца тарыфаў. Пастаўце курсор у поле Табліца_масіў (Табліца) і выберыце ўсю табліцу Табліца тарыфаўакрамя загалоўкаў.
Далей нам трэба пазначыць, з якога слупка трэба здабываць дадзеныя, выкарыстоўваючы нашу формулу. Нас цікавіць стаўка камісіі, якая знаходзіцца ў другім слупку табліцы. Таму за аргумент Нумар_слупка (Нумар_слупка) увядзіце значэнне 2.
І, нарэшце, мы ўводзім апошні аргумент - Пошук_дыяпазону (Інтэрвальны_прагляд).
Важна: менавіта выкарыстанне гэтага аргумента робіць розніцу паміж двума спосабамі прымянення функцыі ВПР. Пры працы з базамі даных аргумент Пошук_дыяпазону (range_lookup) заўсёды павінна мець значэнне ХЛУСНЯ (FALSE) для пошуку дакладнага супадзення. У нашым выкарыстанні функцыі ВПР, мы павінны пакінуць гэтае поле пустым або ўвесці значэнне ПРАЎДНЫ КОД (ПРАЎДА). Вельмі важна правільна падабраць гэты варыянт.
Каб было больш зразумела, прадставім ПРАЎДНЫ КОД (ПРАЎДА) у полі Пошук_дыяпазону (Інтэрвальны_прагляд). Хоць, калі пакінуць поле пустым, гэта не будзе памылкай, бо ПРАЎДНЫ КОД гэта значэнне па змаўчанні:
Мы запоўнілі ўсе параметры. Цяпер націскаем OK, і Excel стварае для нас формулу з функцыяй ВПР.
Калі мы паэксперыментуем з некалькімі рознымі значэннямі агульнай сумы продажаў, то пераканаемся, што формула працуе правільна.
заключэнне
Калі функцыя ВПР працуе з базамі даных, аргумент Пошук_дыяпазону (range_lookup) павінен прыняць ХЛУСНЯ (ХЛУСНЯ). І значэнне, уведзенае як Пошук_значэння (Lookup_value) павінна існаваць у базе даных. Іншымі словамі, ён шукае дакладнае супадзенне.
У прыкладзе, які мы разглядалі ў гэтым артыкуле, няма неабходнасці атрымліваць дакладнае супадзенне. Гэта той выпадак, калі функцыя ВПР неабходна пераключыцца ў прыблізны рэжым, каб атрымаць жаданы вынік.
Напрыклад: Мы хочам вызначыць, якую стаўку выкарыстоўваць пры разліку камісійных для прадаўца з аб'ёмам продажаў $34988. Функцыя ВПР вяртае нам значэнне 30%, што з'яўляецца абсалютна правільным. Але чаму формула абрала радок, які змяшчае менавіта 30%, а не 20% ці 40%? Што маецца на ўвазе пад прыблізным пошукам? Давайце разбярэмся.
Калі аргумент Пошук_дыяпазону (interval_lookup) мае значэнне ПРАЎДНЫ КОД (TRUE) або апушчана, функцыя ВПР перабірае першы слупок і выбірае найбольшае значэнне, якое не перавышае значэнне пошуку.
Важны момант: Каб гэтая схема працавала, першы слупок табліцы павінен быць адсартаваны ў парадку ўзрастання.