Як знайсці дадзеныя з ВПР ў Excel

01 з 03

Знайсці Прыблізныя адпаведнікаў для дадзеных з ВПР ў Excel

Як функцыя ВПР працуе

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

ВПР звычайна вяртае адно поле дадзеных у якасці яго выйсця. Як гэта гэта:

1. Вы задаеце імя або Lookup_Value , які кажа ВПР ў якой радку ці запісы табліцы дадзеных для пошуку патрэбнай інфармацыі
2. Вы ставіце слупок нумар - вядомы як Col_Index_Num - дадзеныя , якія вы шукаць
3. Функцыя шукае lookup_value ў першым слупку табліцы дадзеных
4. ВПР затым знаходзіць і вяртае інфармацыю, якую вы шукаеце з іншага поля той жа запісу з дапамогай прыкладаемага нумар слупка

Сартаванне першых дадзеных

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

Калі дадзеныя не адсартаваныя, ВПР можа вярнуць няправільны вынік.

Сінтаксіс і аргументы ВПР Function ў

Функцыя , у сінтаксісе ставіцца да раскладкі функцыі і ўключае ў сябе функцыю па імені, дужку і аргументы .

Сінтаксіс функцыі ВПР з'яўляецца:

= ВПР (искомое_значение, таблица_массив, номер_столбец, интервальный_просмотр)

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

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

• Таблица_массив павінен утрымліваць, па меншай меры, дзве калонкі дадзеных
• Першы слупок звычайна змяшчае искомое_значение

номер_столбца - (абавязкова) нумар слупка значэнне , якое вы хочаце знайсці.

• Нумарацыя пачынаецца з калонкай SEARCH_KEY ў калонцы 1
• Калі номер_столбца усталяваны на значэнне , якое больш , чым колькасць слупкоў , выбраных у таблица_массив аргументу #REF! памылка вяртаецца функцыяй

интервальный_просмотр - (неабавязкова) паказвае , з'яўляецца Ці ці не сартуецца дыяпазон у парадку ўзрастання.

• Дадзеныя ў першым слупку выкарыстоўваецца ў якасці ключа сартавання
• Лагічнае значэнне - TRUE, або FALSE, з'яўляюцца адзінымі прымальнымі значэннямі
• Калі гэты параметр апушчаны, тое значэнне роўна TRUE, па змаўчанні
• Калі ўсталявана у значэнне TRUE або апушчаны, і першы слупок дыяпазону ня сартуюцца ў парадку ўзрастання, няправільны вынік можа адбыцца
• Калі ўсталявана ў значэнне TRUE або апушчаны , і дакладнае супадзенне для пошуку _value не знойдзены, бліжэйшы матч , які менш па памеры ці значэнне выкарыстоўваецца ў якасці SEARCH_KEY
• Калі ўсталявана значэнне FALSE, ВПР прымае толькі дакладнае супадзенне для пошуку _value. Калі ёсць некалькі значэнняў адпаведнасці, першае значэнне адпаведнасці вяртаецца
• Калі ўсталявана значэнне FALSE , і ніякае значэнне адпаведнасці для SEARCH_KEY не знойдзены, # N / A памылка вяртаецца функцыяй

Прыклад: Знайсці Зніжка Кошт Колькасць Набытыя

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

Прыведзены прыклад паказвае, што зніжка на куплю 19 пунктаў складае 2%. Гэта таму , што слупок Колькасць ўтрымлівае дыяпазоны значэнняў. У выніку, ВПР не можа знайсці дакладнае адпаведнасць. Замест гэтага, набліжаны матч павінен быць знойдзены, каб вярнуць правільную стаўку дысконту.

Для таго, каб знайсці прыблізныя матчы:

• сартаваць дадзеныя ў table_array ў парадку ўзрастання;
• ўсталяваць range_lookup аргумент TRUE ,

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

= ВПР (С2, $ C $ 5: $ D $ 8,2, TRUE),

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

• Выкарыстанне дыялогавага акна часта робіць яго лягчэй правільна ўвесці аргументы функцыі.

Адкрыццё дыялогавага акна ВПР

Крокі, якія выкарыстоўваюцца для ўваходу ў функцыю ВПР, паказаную на малюнку вышэй ў вочка В2 з'яўляюцца:

1. Націсніце на вочка B2 , каб зрабіць яго актыўнай ячэйкі - месца , дзе адлюстроўваюцца вынікі функцыі ВПР
2. Перайдзіце на ўкладку Формулы.
3. Выберыце Lookup & Reference з стужкай , каб адкрыць функцыю выпадальнага спісу
4. Націсніце на ВПРЕ ў спісе , каб адкрыць дыялогавае акно функцыі ў

02 з 03

Увод аргументаў на Excel ВПР функцыя ў

Паказаўшы спасылкі на ячэйкі

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

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

Перавагі выкарыстання, які паказвае ўключаюць:

• Гэта хутчэй, чым набіраць тэкст;
• Меншая колькасць памылак зроблены ўвод правільных спасылак на вочкі.

Выкарыстанне адносных і абсалютных спасылак Cell з аргументамі

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

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

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

Ўвод аргументы функцыі

1. Націсніце на Lookup _value лініі ў дыялогавым акне ВПР
2. Націсніце на вочка С2 у лісце , каб ўвесці гэтую спасылку на вочка ў якасці аргументу SEARCH_KEY
3. Націсніце на лініі таблица_массив дыялогавага акна
4. Вылучыце ячэйкі C5 ў D8 на лісце , каб увайсці ў гэты дыяпазон у якасці аргументу таблица_массив - загалоўкі табліц не ўключаны
5. Націсніце клавішу F4 на клавіятуры , каб змяніць дыяпазон на абсалютныя спасылкі вочак
6. Націсніце на лініі Col_Index_Num дыялогавага акна
7. Калі ласка, увядзіце 2 на гэтай лініі ў якасці аргументу Col_Index_Num, паколькі стаўкі дыскантавання размешчаны ў калонцы 2 аргументу таблица_массив
8. Націсніце на лініі диапазон_просмотра дыялогавага акна
9. Увядзiце словы Праўдзівага ў якасці аргументу диапазон_просмотра
10. Націсніце клавішу Enter на клавіятуры , каб закрыць дыялогавае акно і вярнуцца на працоўны аркуш
11. Адказ 2% (стаўка дыскантавання для колькасці закупленага) павінен з'явіцца ў вочку D2 працоўнага ліста
12. Пры націску на вочка D2, поўнай функцыі = ВПР (С2, $ C $ 5: $ D $ 8,2, TRUE) з'яўляецца ў радку формул над лістом

Чаму ВПР Вяртаецца 2% у якасці выніку

• У прыкладзе, слупок Колькасць не ўтрымлівае дакладнае адпаведнасць для значэння SEARCH_KEY 19.
• Так як аргумент параметр Сартаваць усталяваны ў значэнне TRUE, ВПР будзе знайсці набліжанае адпаведнасці значэнні SEARCH_KEY.
• Бліжэйшы значэнне па памеры , што па - ранейшаму менш , чым значэнне SEARCH_KEY 19 складае 11.
• ВПР, таму, шукае дысконтную працэнт у радку, якая змяшчае 11, і, як следства, вяртае ўліковую стаўку 2%.

03 з 03

Excel ВПР не працуе: # N / A і #REF памылкі

Паведамленні пра памылкі ВПР

Наступныя паведамленні пра памылкі, звязаных з ВПР.

A # N / A ( "значэнне не даступна") Памылка адлюстроўваецца, калі:

• Пошуку _value не знойдзены ў першым слупку аргументу дыяпазону
• Аргумент таблица_массива з'яўляецца недакладным. Напрыклад, аргумент можа ўключаць у сябе пустыя слупкі на левай баку дыяпазону
• Аргумент интервальный_просмотра усталяваны ў FALSE , і дакладнае супадзенне для аргументу SEARCH_KEY не можа быць знойдзена ў першым слупку дыяпазону
• Аргумент интервальный_просмотр усталяваны ў значэнне TRUE , і ўсе значэння ў першым слупку дыяпазону больш , чым SEARCH_KEY

#REF! ( «Спасылка па-за дыяпазону») Памылка адлюстроўваецца, калі:

• Аргумент Col_Index_Num больш , чым колькасць слупкоў у таблица_массиве