Функцыя ВПР. Выкарыстанне функцыі ВПР. Excel - ВПР

Прыкладная праграма Excel папулярная дзякуючы сваёй даступнасці і прастаце, бо не патрабуе асаблівых ведаў і навыкаў. Таблічны выгляд прадастаўлення інфармацыі зразумелы любому карыстальніку, а шырокі набор інструментаў, якія ўключаюць "Майстар функцыі", дазваляе праводзіць любыя маніпуляцыі і разлікі з прадастаўленымі дадзенымі.

Адной з шырока вядомых формул Excel з'яўляецца вертыкальны прагляд. Выкарыстанне функцыі ВПР на першы погляд здаецца даволі складаным, але гэта толькі спачатку.

Як працуе ВПР Excel

Пры працы з формулай ВПР варта ўлічваць, што яна вырабляе пошук шуканага значэння выключна па слупках, а не па радках. Для прымянення функцыі патрабуецца мінімальная колькасць слупкоў - два, максімальнае адсутнічае.

Функцыя ВПР вырабляе пошук зададзенага крытэра, які можа мець любы фармат (тэкставы, лікавы, грашовы, па даце і часу і т. Д.) Ў табліцы. У выпадку знаходжання запісы яна выдае (падстаўляе) значэнне, занесенае ў той жа радку, але з шуканага слупка табліцы, то ёсць адпаведнае зададзенаму крытэру. Калі шуканае значэнне не знаходзіцца, то выдаецца памылка # Н / Д (у англамоўным варыянце # N / А).

неабходнасць выкарыстання

Функцыя ВПР прыходзіць на дапамогу аператару, калі патрабуецца хутка знайсці і прымяніць у далейшых разліках, аналізе або прагнозе пэўнае значэнне з табліцы вялікіх памераў. Галоўнае пры выкарыстанні дадзенай формулы - сачыць, каб зададзеная вобласць пошуку была правільна абраная. Яна павінна ўключаць усе запісы, гэта значыць, пачынаючы з першай па апошняй.

Самы часты выпадак прымянення ВПР (функцыя Excel) - гэта параўнанне або даданне дадзеных, якія знаходзяцца ў двух табліцах, пры выкарыстанні пэўнага крытэра. Прычым дыяпазоны пошуку могуць быць вялікімі і змяшчаць тысячы палёў, размяшчацца на розных лістах або кнігах.

Паказаная функцыя ВПР, як карыстацца ёю, як праводзіць разлікі, у якасці прыкладу на малюнку вышэй. Тут разглядаецца табліца памераў рознічных продажаў у залежнасці ад рэгіёна і мэнэджэра. Крытэрыем пошуку служыць канкрэтны менеджэр (яго імя і прозвішча), а шуканым значэннем з'яўляецца сума яго продажаў.

У выніку працы функцыі ВПР (VLOOKUP) фармуецца новая табліца, у якой канкрэтнаму шуканага мэнэджару хутка супастаўляецца яго сума продажаў.

Алгарытм запаўнення формулы

Размешчана формула ВПР ва ўкладцы "Майстар функцый" і падзеле "Спасылкі і масівы". Дыялогавае акно функцыі мае наступны выгляд:

Аргументы на формулу ўносяцца ў парадку чаргі:

• Шуканае значэнне - тое, што павінна знайсці функцыя, і варыянтамі якога з'яўляюцца значэння ячэйкі, яе адрас, імя, зададзенае ёй аператарам. У нашым выпадку - гэта прозвішча і імя мэнэджэра.
• Табліца - дыяпазон радкоў і слупкоў, у якім шукаецца крытэрый.
• Нумар слупка - яго парадкавы лік, у якім размяшчаецца сума продажаў, то ёсць вынік працы формулы.
• Інтэрвальныя прагляд. Ён месціць значэнне альбо ХЛУСНЯ, альбо ПРАЎДА. Прычым ХЛУСНЯ вяртае толькі дакладнае супадзенне, ПРАЎДА - дазваляе пошук прыблізнага значэння.

Прыклад выкарыстання функцыі

Функцыя ВПР прыклад выкарыстання можа мець наступны: пры вядзенні спраў гандлёвага прадпрыемства ў табліцах Excel ў Стоўбцах А запісана найменне прадукцыі, а ў калонцы У - адпаведная цана. Для складання прапановы ў Стоўбцах З трэба адшукаць кошт на пэўны прадукт, якую патрабуецца вывесці ў калонцы Д.

Формула, запісаная ў Д, будзе выглядаць так: = ВПР (С1; А1: В5; 2; 0), то есть = ВПР (шуканае значэнне; дыяпазон дадзеных табліцы; парадкавы нумар слупка; 0). У якасці чацвёртага аргументу замест 0 можна выкарыстоўваць ХЛУСНЯ.

Для запаўнення табліцы прапановы атрыманую формулу неабходна скапіяваць на ўвесь слупок Д.

Замацаваць вобласць працоўнага дыяпазону дадзеных можна пры дапамозе абсалютных спасылак. Для гэтага ўручную прастаўляюцца знакі $ перад літарнымі і лікавымі значэннямі адрасоў крайніх левых і правых вочак табліцы. У нашым выпадку формула прымае выгляд: = ВПР (С1; $ А $ 1: $ У $ 5; 2; 0).

Памылкі пры выкарыстанні

Функцыя ВПР не працуе, і тады з'яўляецца паведамленне ў Стоўбцах вываду выніку пра памылку (# N / A альбо # Н / Д). Гэта адбываецца ў такіх выпадках:

1. Формула ўведзена, а слупок шуканых крытэрыяў не запоўнены (у дадзеным выпадку калонка С).
2. У слупок З ўнесена значэнне, якое адсутнічае ў калонцы А (у дыяпазоне пошуку дадзеных). Для праверкі наяўнасці шуканага значэння варта вылучыць слупок крытэрыяў і ва ўкладцы меню "Праўка" - "Знайсці" ўставіць дадзены запіс, запусціць пошук. Калі праграма не знаходзіць яго, значыць яно адсутнічае.
3. Фарматы вочак калонак А і С (шуканых крытэрыяў) розныя, напрыклад, у адной - тэкставы, а ў другой - лікавы. Змяніць фармат вочка можна, калі перайсці ў рэдагаванне вочкі (F2). Такія праблемы звычайна ўзнікаюць пры імпартаванні дадзеных з іншых прыкладных праграм. Для пазбягання падобнага роду памылак у формулу ВПР ёсць магчымасць убудоўваць наступныя функцыі: значыліся або ТЭКСТ. Выкананне дадзеных алгарытмаў аўтаматычна пераўтворыць фармат вочак.
4. У кодзе функцыі прысутнічаюць недрукаваныя знакі або прабелы. Тады варта ўважліва праверыць формулу на наяўнасць памылак ўводу.
5. Зададзены прыблізны пошук, то ёсць чацвёрты аргумент функцыі ВПР мае значэнне 1 або ПРАЎДА, а табліца не Адсартавана па ўзыходзячым значэнні. У гэтым выпадку слупок шуканых крытэрыяў патрабуецца адсартаваць па ўзрастанні.

Прычым пры арганізацыі новай зводнай табліцы зададзеныя шуканыя крытэрыі могуць знаходзіцца ў любым парадку і паслядоўнасці і не абавязкова ўмяшчацца поўным спісам (частковая выбарка).

Асаблівасці выкарыстання ў якасці інтэрвальнай прагляду 1 або ісціна

Памылка пад №5 з'яўляецца даволі распаўсюджанай і наглядна намаляваная на малюнку ніжэй.

У дадзеным прыкладзе спіс імёнаў згодна нумарацыі адсартаваны ня па ўзрастанні, а па спадальным значэнні. Прычым у якасці інтэрвальнай прагляду выкарыстаны крытэрый ПРАЎДА (1), які адразу перапыняе пошук пры выяўленні значэння большага, чым шуканае, таму выдаецца памылка.

Што датычыцца прымянення 1 або ісціна ў чацвёртым аргуменце трэба сачыць, каб слупок з пазовам крытэрамі быў адсартаваны па ўзрастанні. Пры выкарыстанні 0 або ХЛУСНІ дадзеная неабходнасць адпадае, але таксама адсутнічае тады магчымасць інтэрвальнай прагляду.

Проста варта ўлічваць, што асабліва важна сартаваць інтэрвальныя табліцы. Інакш функцыя ВПР будзе выводзіць у вочкі няправільныя дадзеныя.

Іншыя нюансы пры працы з функцыяй ВПР

Для выгоды працы з такой формулай можна азагаловіў дыяпазон табліцы, у якой праводзіцца пошук (другі аргумент), як гэта паказана на малюнку.

У дадзеным выпадку вобласць табліцы продажаў азагалоўленая. Для гэтага вылучаецца табліца, за выключэннем загалоўкаў слупкоў, і ў поле імя (злева пад панэллю ўкладак) прысвойваецца ёй назву.

Іншы варыянт - азагаловіў - мае на ўвазе вылучэнне дыяпазону дадзеных, потым пераход у меню "Устаўка" - "Назва" - "Прысвоіць".

Для таго каб выкарыстоўваць дадзеныя, размешчаныя на іншым лісце рабочай кнігі, пры дапамозе функцыі ВПР, неабходна ў другім аргуменце формулы прапісаць размяшчэнне дыяпазону дадзеных. Напрыклад, = ВПР (А1; Лист2! $ А $ 1: $ У $ 5; 2; 0), дзе Лист2! - з'яўляецца спасылкай на патрабаваны ліст кнігі, а $ А $ 1: $ У $ 5 - адрас дыяпазону пошуку дадзеных.

Прыклад арганізацыі навучальнага працэсу з ВПР

Даволі зручна ў Excel ВПР-функцыю прымяняць не толькі фірмам, якія займаюцца гандлем, але і навучальным установам для аптымізацыі працэсу супастаўлення вучняў (студэнтаў) з іх ацэнкамі. Прыклады дадзеных задач паказаны на малюнках ніжэй.

Існуюць дзве табліцы са спісамі студэнтаў. Адна з іх ацэнкамі, другая паказвае ўзрост. Неабходна супаставіць абедзве табліцы так, каб нароўні з узростам навучэнцаў выводзіліся і іх ацэнкі, гэта значыць ўвесці дадатковы слупок ў другім спісе.

Функцыя ВПР выдатна спраўляецца з рашэннем гэтай задачы. У слупку G пад загалоўкам "Ацэнкі" запісваецца адпаведная формула: = ВПР (Е4, В3: С13, 2, 0). Яе трэба скапіяваць на ўсю калонку табліцы.

У выніку выканання функцыя ВПР выдасць ацэнкі, атрыманыя пэўнымі студэнтамі.

Прыклад арганізацыі пошукавай сістэмы з ВПР

Яшчэ адзін прыклад прымянення функцыі ВПР - гэта арганізацыя пошукавай сістэмы, калі ў базе дадзеных паводле зададзенага крытэру варта знайсці адпаведнае яму значэнне. Так, на малюнку паказаны спіс з мянушкамі жывёл і іх прыналежнасць да вызначанага выгляду.

Пры дапамозе ВПР ствараецца новая табліца, у якой лёгка знайсці па мянушцы жывёльнага яго выгляд. Актуальныя падобныя пошукавыя сістэмы пры працы з вялікімі спісамі. Для таго каб ўручную не пераглядаць усе запісы, можна хутка скарыстацца пошукам і атрымаць патрабаваны вынік.