Выкарыстанне функцыі VLOOKUP для падстаноўкі значэнняў

Каму лянота ці няма часу чытаць – глядзіце відэа. Падрабязнасці і нюансы ў тэксце ніжэй.

Пастаноўка задачы

Такім чынам, у нас ёсць дзве табліцы - стол заказаў и прайс-ліст:

Задача - аўтаматычна падстаўляць цэны з прайс-ліста ў табліцу заказаў, арыентуючыся на назву тавару, каб потым можна было разлічыць кошт.

Рашэнне

У наборы функцый Excel у катэгорыі Спасылкі і масівы (Пошук і спасылка) ёсць функцыя ВПР (ВПР).Гэтая функцыя шукае зададзенае значэнне (у нашым прыкладзе гэта слова «Яблыкі») у крайнім левым слупку названай табліцы (прайс-ліста), рухаючыся зверху ўніз і, знайшоўшы яго, адлюстроўвае змесціва суседняй ячэйкі. (23 руб.) Схематычна працу гэтай функцыі можна прадставіць так:

Для зручнасці далейшага выкарыстання функцыі зрабіце адразу адно - назавіце дыяпазон вочак у прайсе сваім імем. Для гэтага вылучыце ўсе ячэйкі прайс-ліста, акрамя «шапкі» (G3: H19), абярыце ў меню Уставіць – Імя – Прысвоіць (Insert - Name - Define) або націсніце CTRL + F3 і ўвядзіце любое імя (без прабелаў), напрыклад цана… Цяпер, у будучыні, вы можаце выкарыстоўваць гэтае імя для спасылкі на прайс-ліст.

Цяпер мы выкарыстоўваем функцыю ВПР… Выберыце ячэйку, у якую ён будзе ўведзены (D3), і адкрыйце ўкладку Формулы – устаўка функцый (Формулы — Уставіць функцыю)… У катэгорыі Спасылкі і масівы (Пошук і спасылка) знайсці функцыю ВПР (ВПР) і націсніце OK… З'явіцца акно для ўводу аргументаў для функцыі:

Выкарыстанне функцыі VLOOKUP для падстаноўкі значэнняў

Запаўняем іх па чарзе:

  • Пажаданае значэнне (Шукаць значэнне) – назва тавару, які функцыя павінна знайсці ў крайнім левым слупку прайс-ліста. У нашым выпадку слова «Яблыкі» з ячэйкі B3.
  • табліца (Масіў табліц) – табліца, з якой бяруцца жаданыя значэння uXNUMXbuXNUMXbar, гэта значыць наш прайс-ліст. Для даведкі мы выкарыстоўваем нашу ўласную назву «Кошт», прыведзеную раней. Калі вы не далі імя, вы можаце проста выбраць стол, але не забудзьцеся націснуць кнопку F4каб замацаваць спасылку са знакамі даляра, таму што ў адваротным выпадку ён будзе спаўзаць уніз пры капіраванні нашай формулы ў астатнія вочкі ў слупку D3:D30.
  • Нумар_слупка (Індэксны нумар слупка) – парадкавы нумар (не літара!) слупка ў прайс-лісце, з якога мы будзем браць значэнні цэн. Першы слупок прайс-ліста з назвамі пранумараваны нумарам 1, таму нам патрэбна цана са слупка з нумарам 2.
  • інтэрвал_пошуку (Пошук дыяпазону) – у гэтае поле можна ўвесці толькі два значэнні: FALSE або TRUE:
      • Калі ўведзена значэнне 0 or ХЛУСНІЦЬ (ХЛУСНЯ), то фактычна гэта азначае, што дазволены толькі пошук дакладнае супадзенне, г.зн. калі функцыя не знаходзіць у прайс-лісце нестандартны тавар, указаны ў табліцы замоў (калі ўведзена, напрыклад, «Какос»), яна выдае памылку #N/A (няма дадзеных).
      • Калі ўведзена значэнне 1 or TRUE, (Праўда), то гэта азначае, што вы дазваляеце пошук не дакладнага, а прыблізнае супадзенне, г.зн. у выпадку з «какосам» функцыя паспрабуе знайсці прадукт з назвай, максімальна блізкай да «какос», і верне цану за гэтую назву. У большасці выпадкаў такая прыблізная замена можа згуляць з карыстальнікам, падставіўшы кошт не таго тавару, які быў на самай справе! Такім чынам, для большасці рэальных бізнес-задач прыблізны пошук лепш не дапускаць. Выключэннем з'яўляюцца выпадкі, калі мы шукаем лічбы, а не тэкст - напрыклад, пры разліку скідак па кроках.

Усё! Засталося націснуць OK і скапіяваць уведзеную функцыю ва ўвесь слупок.

# N / A памылкі і іх падаўленне

функцыя ВПР (ВПР) вяртае памылку #N/A (#Н/Д) калі:

  • Дакладны пошук уключаны (аргумент Інтэрвальны выгляд = 0) і патрэбнага імя няма табліца.
  • Грубы пошук уключаны (Інтэрвальны выгляд = 1), Але табліца, у якім вядзецца пошук, не адсартаваны ў парадку ўзрастання назваў.
  • Фармат ячэйкі, адкуль паходзіць неабходнае значэнне імя (напрыклад, B3 у нашым выпадку), і фармат вочак першага слупка (F3: F19) табліцы адрозніваюцца (напрыклад, лікавы і тэкставы ). Асабліва характэрны гэты выпадак пры выкарыстанні лічбавых кодаў (нумароў рахункаў, ідэнтыфікатараў, дат і інш.) замест тэкставых назваў. У гэтым выпадку вы можаце выкарыстоўваць функцыі Ч и TEXT для пераўтварэння фарматаў дадзеных. Гэта будзе выглядаць прыкладна так:

    =VLOOKUP(ТЭКСТ(B3),цана,0)

    Вы можаце прачытаць больш пра гэта тут.

  • Функцыя не можа знайсці неабходнае значэнне, таму што код змяшчае прабелы або нябачныя недрукаваныя сімвалы (разрывы радкоў і г.д.). У гэтым выпадку вы можаце выкарыстоўваць тэкставыя функцыі TRIM (ТРЫМ) и PRINT(ЧЫСТЫ) каб выдаліць іх:

    =VLOOKUP(ПРЫСТРОЙКІ(ЧЫСТЫ(B3)),цана,0)

    =VLOOKUP(CLEAN(CLEAN(B3));цана;0)

Каб здушыць паведамленне пра памылку # N / A (#Н/Д) у выпадках, калі функцыя не можа знайсці дакладнае супадзенне, вы можаце выкарыстоўваць функцыю КАЛІПАМЫЛКА (КАЛІПАМЫЛКА)… Так, напрыклад, гэтая канструкцыя перахоплівае любыя памылкі, створаныя VLOOKUP, і замяняе іх нулямі:

= КАЛІПАМЫЛКА (ВПР (B3, цана, 2, 0), 0)

= КАЛІПАМЫЛКА (ВПР (B3; цана; 2; 0); 0)

PS

Калі вам трэба атрымаць не адно значэнне, а адразу ўвесь набор (калі іх некалькі розных), то прыйдзецца пашаманіць з формулай масіва. або скарыстайцеся новай функцыяй XLOOKUP з Office 365.

 

  • Палепшаная версія функцыі VLOOKUP (VLOOKUP 2).
  • Хуткі разлік скідак крокаў (дыяпазону) з дапамогай функцыі VLOOKUP.
  • Як зрабіць «левы VLOOKUP» з дапамогай функцый INDEX і MATCH
  • Як з дапамогай функцыі VLOOKUP запаўняць формы дадзенымі са спісу
  • Як выцягнуць з табліцы не першае, а адразу ўсе значэння
  • Функцыі VLOOKUP2 і VLOOKUP3 з надбудовы PLEX

 

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