01 з 03
Знайсці дадзеныя налева
Excel Left Пошук Формула Агляд
Функцыя ВПР ў Excel выкарыстоўваецца для пошуку і вяртання інфармацыі з табліцы дадзеных на аснове значэння пошуку , якія вы выбіраеце.
Як правіла, ВПР патрабуе , каб значэнне падстаноўкі , каб быць у самой левай калонцы табліцы дадзеных, а функцыя вяртае іншую вобласць дадзеных , размешчаных у той жа радку справа ад гэтага значэння.
Аб'яднаўшы ВПР з функцыяй ВЫБРАЦЬ ; Аднак левая формула пошуку можа быць створана , які будзе:
- Дазволю ет значэнне пошуку, каб быць выбрана з любога слупка ў табліцы дадзеных
- вяртаць інфармацыю, размешчаную ў любым слупку злева ад значэння пошуку
Прыклад: Выкарыстанне ВПР і выбраць функцыі ў формуле злева падстановак
Падрабязна выкладзеныя ніжэй, крокі стварыць формулу левай падстаноўкі відаць на малюнку вышэй.
формула
= ВПР ($ D $ 2, ВЫБРАЦЬ ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)дазваляе знайсці частка, якая падаецца ад розных кампаній, пералічаных у калонцы 3 табліцы дадзеных.
Праца функцыі ВЫБРАЦЬ ў формуле, каб падмануць ВПР, мяркуючы, што слупок-фактычна слупок 1. У выніку, назва кампаніі можа быць выкарыстана ў якасці значэння пошуку, каб знайсці імя той частцы, якая пастаўляецца кожную кампанію.
Падручнік крокі - Увод дадзеных падручнік
- Калі ласка, увядзіце наступныя загалоўкі ў вочкі, названыя: D1 - Пастаўшчык E1 - Частка
- Увядзіце табліцу дадзеных відаць на малюнку вышэй у клеткі D4 да F9
- Радкі 2 і 3 пакінутыя пустымі, каб прыстасаваць крытэры пошуку і левую формулу падстаноўкі , створаныя падчас гэтага ўрока
Пачынаючы з левым ўдакладнялі Формулы - Адкрыццё дыялогавага акна ВПРА
Хоць можна проста ўвесці формулу вышэй , непасрэдна ў вочка F1 ў лісце, многія людзі адчуваюць цяжкасці з сінтаксісам формулы.
Альтэрнатыва ў дадзеным выпадку з'яўляецца выкарыстанне ВПР дыялогавага акна . Амаль усе функцыі Excel маюць дыялогавае акно, якое дазваляе ўводзіць кожны з аргументаў функцыі на асобным радку.
падручнік крокі
- Націсніце на вочка Е2 з ліста - месца , дзе будуць адлюстроўвацца вынікі левай формулы пошуку
- Націсніце на ўкладцы Формулы ў стужцы
- Націсніце на опцыі пошуку і спасылкі ў стужцы , каб адкрыць функцыю выпадальнага спісу
- Націсніце на ВПРЕ ў спісе , каб адкрыць дыялогавае акно функцыі ў
02 з 03
Увод аргументаў на дыялогавым акне ВПР - Націсніце, каб павялічыць малюнак
Аргументы ВПР ў
Функцыя , у аргументах з'яўляюцца значэннем , выкарыстоўваным функцыяй для вылічэнні выніку.
У дыялогавым акне ёсць функцыя, імя кожнага аргументу знаходзіцца на асобным радку, за якім варта поле, у якім, каб увесці значэнне.
Калі ласка, увядзіце наступныя значэнні для кожнага з аргументаў ВПРА па правільнай лініі дыялогавага акна, як паказана на малюнку вышэй.
пошук Значэнне
Значэнне пошуку з'яўляецца полем інфармацыі, якая выкарыстоўваецца для пошуку масіва табліцы. ВПР вяртае яшчэ адно поле дадзеных з адной і той жа радку, што і значэнне пошуку.
Гэты прыклад выкарыстоўвае спасылку на вочка ў месцы, дзе назва кампаніі будзе ўведзены ў табліцу. Перавагай гэтага з'яўляецца тое, што яна робіць яго лёгка змяніць назву кампаніі без рэдагавання формулы.
падручнік крокі
- Націсніце на Lookup_Value лініі ў дыялогавым акне
- Націсніце на вочка D2 , каб дадаць спасылку на вочка ў радку Lookup_Value
- Націсніце клавішу F4 на клавіятуры , каб зрабіць спасылку на вочка абсалютная - $ D $ 2
Заўвага: спасылкі Абсалютных клетак выкарыстоўваюцца для пошуку значэння і аргументаў табліцы масіва для прадухілення памылак , калі формула пошуку капіююцца ў іншыя вочкі ў лісце.
Табліца Масіў: Увод Выбар функцыі
Аргумент табліцы масіва з'яўляецца блокам сумежных дадзеных, з якога здабываецца канкрэтная інфармацыя.
Як правіла, ВПР выглядае толькі справа ад аргументу значэння пошуку , каб знайсці дадзеныя ў масіве табліцы. Для таго, каб прымусіць яго глядзець налева, ВПР павінен быць падманным шляхам перастаноўкі слупкоў у масіве табліцы, выкарыстоўваючы функцыю ВЫБРАЦЬ.
У гэтай формуле функцыя ВЫБРАЦЬ выконвае дзве задачы:
- ён стварае масіў табліцы, які толькі дзве калонкі шырынёй - слупкі D і F
- ён змяняе справа налева парадак слупкоў у масіве табліцы, так што слупок F прыходзіць першым і слупок D з'яўляецца другім
Падрабязная інфармацыя аб тым , як функцыя ВЫБРАЦЬ выконвае гэтыя задачы можна знайсці на старонцы 3 урока .
падручнік крокі
Заўвага: Пры ўводзе функцыі ўручную, кожны з аргументаў функцыі павінны быць падзеленыя коскі «».
- У дыялогавым акне функцыі ВПР, націсніце на лініі таблица_массив
- Калі ласка, увядзіце наступную функцыю ВЫБРАЦЬ
- ВЫБРАЦЬ ({1,2}, $ F: $ F, $ D: $ D)
Калонка Парадкавы нумар
Як правіла, нумар слупка індэкса ўказвае на тое, які слупок масіва табліцы змяшчае дадзеныя, якія вы пасля. У гэтай формуле; Аднак, гэта ставіцца да парадку слупкоў, усталяваных з дапамогай функцыі ВЫБРАЦЬ.
Функцыя ВЫБРАЦЬ стварае масіў табліцы, якое два слупка ў шырыні слупка F з першым наступным колоночной D. Так як шуканая інфармацыя - назва часткі - у калонцы D, значэнне індэкса слупка аргументу павінны быць ўстаноўлена на 2.
падручнік крокі
- Націсніце на лініі Col_Index_Num ў дыялогавым акне
- Калі ласка, увядзіце 2 у гэтым радку
дыяпазон прагляду
Диапазон_просмотра аргумент ВПР з'яўляецца лагічнае значэнне (ПРАЎДА або ХЛУСНЯ толькі) , што паказвае на тое, ці вы хочаце ВПР знайсці дакладнае ці набліжанага адпаведнасці значэнні пошуку.
- Калі TRUE або калі гэты аргумент апушчаны, то функцыя ВПР вяртае альбо дакладнае адпаведнасць з Lookup_Value, або, калі дакладнае супадзенне не знойдзена, ВПР вяртае наступнае найбольшую значэнне. Для формулы , каб зрабіць гэта, дадзеныя ў першым слупку table_array павінны быць адсартаваныя ў парадку ўзрастання .
- Калі FALSE, ВПР будзе выкарыстоўваць толькі дакладнае супадзенне з Lookup_Value. Пры наяўнасці двух або больш значэнняў у першым слупку table_array, што адпавядае значэнню пошуку, першае знойдзенае значэнне выкарыстоўваецца. Калі дакладнае супадзенне не знойдзена, то # N / вяртаецца памылка.
У гэтым кіраўніцтве, так як мы шукаем канкрэтнае імя часткі, диапазон_просмотр будзе ўсталяваны ў значэнні False , так што толькі дакладныя супадзення вяртаюцца па формуле.
падручнік крокі
- Націсніце на лініі диапазон_просмотра ў дыялогавым акне
- Увядзiце словы ілжыва ў гэтым радку , каб паказаць , што мы хочам , каб ВПР вярнуць дакладнае супадзенне дадзеных мы шукаем
- Націсніце кнопку OK, каб завяршыць левую формулу падстаноўкі і зачыніць дыялогавае акно
- Бо мы яшчэ не ўвялі назву кампаніі ў вочку D2, у # N / A памылка павінна прысутнічаць у вочку Е2
03 з 03
Тэставанне Формулы левы Удакладняючае
Вяртаючыся да дадзеных з дапамогай формулы налева Lookup
Для таго, каб высветліць, якія кампаніі пастаўляюць якія дэталі, увядзіце імя кампаніі ў вочку D2 і націсніце клавішу ENTER на клавіятуры.
Частка назвы будзе адлюстроўвацца ў вочку Е2.
падручнік крокі
- Націсніце на вочка D2 ў вашым працоўным аркушы
- Увядзіце Gadgets Plus ў вочка D2 і націсніце клавішу ENTER на клавіятуры
- Тэкст Gadgets - частка пастаўляецца гаджэт кампаніі Plus - павінен адлюстроўвацца ў вочку E2
- Праверце формулу падстановак далей, увёўшы іншыя назвы кампаній у вочку D2 і адпаведнае імя часткі з'явіцца ў вочку E2
Паведамленні пра памылкі ВПР
Калі ў вочку Е2, першы чэк на арфаграфічныя памылкі ў вочку D2 паведамленне пра памылку , такіх як # N / A з'яўляецца.
Калі напісанне не праблема, гэты спіс паведамленняў пра памылкі ВПР можа дапамагчы вам вызначыць, дзе праблема.
Ламаючы ВЫБРАЦЬ Пасада ў
Як ужо згадвалася, у гэтай формуле, функцыя ВЫБРАЦЬ мае два працоўныя месцы:
- ён стварае масіў табліцы, які толькі дзве калонкі шырынёй - слупкі D і F
- ён змяняе справа налева парадак слупкоў у масіве табліцы, так што слупок F прыходзіць першым і слупок D з'яўляецца другім
Стварэнне двух слупкоў табліцы масіва
Сінтаксіс для функцыі ВЫБРАЦЬ з'яўляецца:
= ВЫБРАЦЬ (номер_индекса, Значение1, значение2, ... Value254)
Функцыя ВЫБРАЦЬ звычайна вяртае адно значэнне з спісу значэнняў (Value1 да Value254) на аснове індэкса ўведзенага нумары.
Калі індэкс роўны 1, то функцыя вяртае Value1 са спісу; калі нумар індэкса 2, то функцыя вяртае Value2 з спісу і гэтак далей.
Пры ўводзе некалькіх нумароў індэксаў; Аднак, функцыя будзе вяртаць некалькі значэнняў у любым парадку. Як ВЫБРАЦЬ вяртаць некалькі значэнняў робіцца шляхам стварэння масіва .
Ўвод масіва ажыццяўляецца шляхам акружэння лікаў, уведзеных у фігурныя дужкі або дужкі. Два нумары ўведзеныя для нумара індэкса: {1,2}.
Варта адзначыць, што ВЫБРАЦЬ не абмяжоўваецца стварэннем табліцы ў два слупкі. Шлях ўключэння дадатковага колькасці ў масіве - такія, як {1,2,3} - і дадатковы дыяпазон у аргуменце значэння, табліца тры слупка можа быць створана.
Дадатковыя слупкі дазволіць вам вярнуць іншую інфармацыю з левай формулы пошуку, проста змяняючы індэкс слупка нумар аргумент ВПР да нумара слупка, які змяшчае патрэбную інфармацыю.
Змяненне парадку слупкоў з функцыяй ВЫБРАЦЬ
У функцыі ВЫБРАЦЬ , якая выкарыстоўваецца ў гэтай формуле: ВЫБРАЦЬ ({1,2}, $ F: $ F, $ D: $ D), дыяпазон для слупка F у спісе перад калонцы D.
Так як функцыя ВЫБРАЦЬ ўсталёўвае табліцу масіва ВПР - крыніцы дадзеных для гэтай функцыі - пераключэнне парадку слупкоў у функцыі ВЫБРАЦЬ атрымлівае перадаецца разам з ВПР.
Цяпер, наколькі ВПР занепакоены, масіў табліцы толькі дзве калонкі шырынёй з калонкі F на левай і слупка D справа. Паколькі слупок F змяшчае назва кампаніі, якую мы хочам знайсці, а так як слупок D змяшчае назву дэталяў, ВПР будзе ў стане выконваць свае звычайныя абавязкі падстановак ў пошуку дадзеных, якая знаходзіцца злева ад значэння пошуку.
У выніку, ВПР можа выкарыстоўваць назву кампаніі, каб знайсці частка іх пастаўку.