Знайсці некалькі палёў дадзеных з Excel ВПР

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

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

01 з 10

Вяртанне некалькіх значэнняў з Excel ВПР

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

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

Ўкладанне функцыі ўключае ў сябе ўводзе другой функцыі ў якасці аднаго з аргументаў для першай функцыі.

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

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

Навучальны дапаможнік Змест

• Увод дадзеных Tutorial
• Стварэнне найменнага дыяпазону для табліцы дадзеных
• Запуск функцыі ВПР
• Ўвод Аргумент Lookup значэнне з дапамогай Абсалютныя спасылкі на ячэйкі
• Ўвод табліцы масіў аргументаў
• Ўвод укладзенай COLUMN функцыі
• Завяршэнне функцыі ВПР
• Капіяванне Удакладняючае Формула з бліск ручкай
• Атрыманне дадзеных з Lookup формулы

02 з 10

Калі ласка, увядзіце дадзеныя Tutorial

Першы крок у падручніку для ўводу дадзеных у Excel табліцу .

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

• Увядзіце верхні дыяпазон дадзеных у вочках D1 да G1
• Увядзіце другі дыяпазон у вочках D4 да G10

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

Падручнік не ўключае фарматаванне відаць на малюнку, але гэта не паўплывае, як працуе формула пошуку.

Інфармацыя пра параметры фарматавання , аналагічных тым , якія бачылі вышэй, даступныя ў гэтым Basic Excel Фарматаванне Падручнік .

падручнік крокі

1. Калі ласка, увядзіце дадзеныя, як паказана на малюнку вышэй у клеткі D1 да G10

03 з 10

Стварэнне найменнага дыяпазону для табліцы дадзеных

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

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

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

Заўвага: Імя дыяпазон не ўключае ў сябе загалоўкі або імёны палёў для дадзеных (радок 4) , але толькі самі дадзеныя.

падручнік крокі

1. Вылучыце ячэйкі D5 на G10 у лісце, каб выбраць іх
2. Націсніце на назву Box , размешчанай вышэй слупок A
3. Тып «Табліца» (без двукоссяў) у поле Імя
4. Націсніце клавішу ENTER на клавіятуры
5. Клеткі D5 да G10 цяпер маюць назву дыяпазону «Табліца». Мы будзем выкарыстоўваць імя для ВПР аргументу табліцы масіва пазней у падручніку

04 з 10

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

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

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

падручнік крокі

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

05 з 10

Ўвод Аргумент Lookup значэнне з дапамогай Абсалютныя спасылкі на ячэйкі

Звычайна значэнне пошуку супадае з полем дадзеных у першым слупку табліцы дадзеных.

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

Дапушчальныя тыпы дадзеных для пошуку значэння з'яўляюцца:

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

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

Абсалютныя спасылкі на ячэйкі

На больш познім этапе ў падручніку, мы скапіюем формулу падстаноўкі ў вочку E2 для вочак F2 і G2.

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

Калі гэта адбудзецца, D2 - спасылка на вочка для значэння пошуку - будзе мяняцца , як формула капіюецца ствараюць памылкі ў клетках F2 і G2.

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

спасылкі Абсалютныя вочкі не змяняюцца, калі формулы капіююцца.

Спасылкі Абсалютныя ячэйкі ствараюцца націскам клавішы F4 на клавіятуры. Гэта дадае знакі даляра вакол спасылак на вочкі , такіх як $ D $ 2

падручнік крокі

1. Націсніце на Lookup_Value лініі ў дыялогавым акне
2. Націсніце на вочка D2 , каб дадаць спасылку на вочка ў Lookup_Value лініі. Гэта клетка, дзе мы будзем уводзіць назву дэталі, пра якія мы шукаем інфармацыю
3. Ня перамяшчаючы курсор, націсніце клавішу F4 на клавіятуры , каб пераўтварыць D2 ў абсалютнай спасылкі на вочка $ D $ 2
4. Пакіньце дыялогавае акно функцыі ВПР адкрытым для наступнага кроку ў кіраўніцтве

06 з 10

Ўвод табліцы масіў аргументаў

Масіў табліцы з'яўляецца табліца дадзеных аб тым , што формула пошуку ажыццяўляе пошук , каб знайсці інфармацыю , якую мы хочам.

Масіў табліца павінна ўтрымліваць , па меншай меры , дзве калонкі дадзеных .

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

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

Для гэтага прыкладу мы будзем выкарыстоўваць імя дыяпазону, створанае на кроку 3 ўрока.

падручнік крокі

1. Націсніце на таблица_массив лініі ў дыялогавым акне
2. Тып «Табліца» (без двукоссяў), каб увесці імя дыяпазону для гэтага аргументу
3. Пакіньце дыялогавае акно функцыі ВПР адкрытым для наступнага кроку ў кіраўніцтве

07 з 10

Ўкладанне калону функцыі

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

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

Гэта дзе функцыя COLUMN ўваходзіць. Пры ўводзе яго ў якасці нумара індэкса слупка аргументу, яна будзе мяняцца , як формула пошуку капіюецца з вочка D2 ў вочкі E2 і F2 пазней у падручніку.

укладзеныя функцыі

Функцыі слупка, такім чынам, дзейнічае як індэкс слупка колькасці ВПР ў аргумент .

Гэта дасягаецца за кошт ўкладзенасці функцыі COLUMN ўнутры ВПР ў радку Col_Index_Num дыялогавага акна.

Ўвод COLUMN функцыі ўручную

Пры ўкладзенасці функцый Excel не дазваляе адкрываць другі функцыі ў дыялогавае акно для ўводу аргументаў.

Функцыя COLUMN, таму неабходна ўвесці ўручную ў радку Col_Index_Num.

Функцыя COLUMN мае толькі адзін аргумент - спасылка аргумент , які з'яўляецца спасылкай на вочка.

Выбар слупка Функцыя Референс аргументу

Праца функцыі слупка з'яўляецца вярнуць нумар слупка зададзенага ў якасці спасылкавага аргументу.

Іншымі словамі, ён пераўтворыць літары слупка ў выглядзе ліку з калонкі А з'яўляецца першы слупок, слупок У другі і так далей.

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

падручнік крокі

1. У дыялогавым акне функцыі ВПР, націсніце на лініі Col_Index_Num
2. Увядзіце слупок імя функцыі адкрытай круглай дужкі «(»
3. Націсніце на вочка B1 ў лісце , каб ўвесці гэтую спасылку на вочка ў якасці спасылкавага аргументу
4. Увядзіце зачыняюць круглыя дужкі «)» для завяршэння функцыі COLUMN
5. Пакіньце дыялогавае акно функцыі ВПР адкрытым для наступнага кроку ў кіраўніцтве

08 з 10

Ўвод аргумент ВПР Range Lookup

Диапазон_просмотра аргумент ВПР з'яўляецца лагічнае значэнне (ПРАЎДА або ХЛУСНЯ толькі) , што паказвае на тое, ці вы хочаце ВПР знайсці дакладнае або прыблізнае супадзенне з Lookup_Value.

• Калі TRUE або калі гэты аргумент апушчаны, то функцыя ВПР вяртае альбо дакладнае адпаведнасць з Lookup_Value, або, калі дакладнае супадзенне не знойдзена, ВПР вяртае наступнае найбольшую значэнне. Для формулы , каб зрабіць гэта, дадзеныя ў першым слупку table_array павінны быць адсартаваныя ў парадку ўзрастання .
• Калі FALSE, ВПР будзе выкарыстоўваць толькі дакладнае супадзенне з Lookup_Value. Пры наяўнасці двух або больш значэнняў у першым слупку table_array, што адпавядае значэнню пошуку, першае знойдзенае значэнне выкарыстоўваецца. Калі дакладнае супадзенне не знойдзена, то # N / вяртаецца памылка.

У гэтым кіраўніцтве, так як мы шукаем канкрэтную інфармацыю аб канкрэтным апаратным пункце мы ўсталюем диапазон_просмотр роўным false.

падручнік крокі

1. Націсніце на лініі диапазон_просмотра ў дыялогавым акне
2. Увядзiце словы ілжыва ў гэтым радку , каб паказаць , што мы хочам , каб ВПР вярнуць дакладнае супадзенне дадзеных мы шукаем
3. Націсніце OK, каб завяршыць формулу падстаноўкі і зачыніць дыялогавае акно
4. Так як мы яшчэ не ўвялі крытэрыі падстаноўкі ў вочка D2 # N / памылка будзе прысутнічаць у вочку Е2
5. Гэтая памылка будзе выпраўленая, калі мы дадамо крытэры пошуку ў апошнім кроку урока

09 з 10

Капіяванне Удакладняючае Формула з бліск ручкай

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

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

У гэтым уроку мы хочам, каб атрымаць дадзеныя з слупкоў 2, 3 і 4 табліцы дадзеных - гэта цана, нумар дэталі і імя пастаўшчыка, калі мы ўводзім імя дэталі як Lookup_Value.

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

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

Акрамя таго, у Excel не змяняе абсалютнай спасылкі ячэйкі $ D $ 2 і названы дыяпазон табліцы , як формула капіюецца.

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

падручнік крокі

1. Націсніце на вочка Е2 - дзе знаходзіцца формула пошуку - зрабіць яе актыўнай
2. Навядзіце паказальнік мышы на чорны квадрат ў правым ніжнім куце. Паказальнік зменіцца на знак плюс "+" - гэта заліванне ручка
3. Націсніце левую кнопку мышы і перацягнуць маркер запаўнення праз да вочка G2
4. Адпусціце кнопку мышы і вочка F3 павінна ўтрымліваць двухмерны пошукавай формулы
5. Калі ўсё зроблена правільна, то клеткі F2 і G2 зараз павінны таксама ўтрымліваць # N / A памылку, якая прысутнічае ў вочку Е2

10 з 10

Ўвод крытэрыяў Lookup

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

Для гэтага увядзіце імя элемента, які вы хочаце атрымаць у клетку Lookup_Value (D2) і націсніце клавішу ENTER на клавіятуры.

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

падручнік крокі

1. Націсніце на вочка D2 у лісце
2. Увядзіце віджэт ў вочка D2 і націсніце клавішу ENTER на клавіятуры
3. Наступная інфармацыя павінна адлюстроўвацца ў вочках Е2 G2:
   • E2 - $ 14,76 - цана фішкі
   • F2 - PN-98769 - нумар дэталі для фішкі
   • G2 - Фішкі Inc. - імя пастаўшчыка віджэтаў
4. Праверце формулу масіва ВПР далей ўводзіць імя іншых частак у вочку D2 і назіраючы вынікі ў клетках E2 для G2

Калі паведамленне пра памылку , такіх як #REF! з'яўляецца ў клетках E2, F2, або G2, гэты спіс паведамленняў пра памылкі ВПР можа дапамагчы вам вызначыць, дзе праблема.