Students Save 30%! Learn & create with unlimited courses & creative assets Students Save 30%! Save Now
Advertisement
  1. Computer Skills
  2. Office
Computers

Як атрымаць дадзеныя з табліцы з дапамогай ВПР, MATCH і INDEX

by
Difficulty:IntermediateLength:LongLanguages:
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

Belarusian (беларуская мова) translation by Tatsiana Bochkareva (you can also view the original English article)

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

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

прэміум параметры

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

Напрыклад, вы можаце:

Excel scripts and plugins on Envato Market
Сцэнары Excel і убудоў на рынку Envato

Screencast

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

выкарыстанне ВПР

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

unique identifiers for vlookup
Унікальны ідэнтыфікатар павінен быць як серыйны нумар, у якім няма двух аднолькавыя ў той жа табліцы.

сінтаксіс

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

= ВПР (значэнне пошуку, дыяпазон табліцы, нумар слупка, [ісціна / хлусня])

Вось што азначаюць гэтыя аргументы:

  • Пошук значэння. Клетка, якая мае унікальны ідэнтыфікатар.
  • Дыяпазон табл. Дыяпазон вочак, які мае ідэнтыфікатар ў першым слупку, а затым астатняй часткі дадзеных у іншых слупках.
  • Нумар слупка. Нумар слупка, дадзеныя, якія вы шукаеце. Не зразумейце, што зблытаць з літарай слупкі. У прыведзенай вышэй ілюстрацыі, стан у калонцы 4.
  • True / False. Гэты аргумент з'яўляецца неабавязковым. Праўда азначае, што набліжаная матч з'яўляецца прымальным, і Ілжывае азначае, што толькі дакладнае супадзенне з'яўляецца прымальным.

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

syntax of vlookup
Сінтаксіс функцыі ВПР

Вызначыць імя дыяпазону для стварэння абсалютнай спасылкі

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

Вызначэнне дыяпазону імёнаў у Excel

  1. Перад уваходам у формулу, перайдзіце ў табліцу зыходных дадзеных.
  2. Вылучыце ўсе вочкі ад A4 (загалоўка для замовы # слупка) ўніз праз H203. Хуткі спосаб зрабіць гэта, гэта націснуць A4, а затым націсніце Ctrl-Shift-End (Ctrl-Shift-End на Mac).
  3. Націсніце ўнутры Name Box вышэй слупка А (Name Box цяпер адлюстроўвае A4).
  4. Тып дадзеных, затым націсніце Enter.
  5. Цяпер вы можаце выкарыстоўваць дадзеныя імя ў формулу замест $ A $ 4: $ H $ 203.
Name box displaying a range name
Скрынка імя звычайна адлюстроўваецца бягучы адрас вочка. Націсніце ўнутры яго і ўвядзіце імя для вызначэння дыяпазону.

Вызначэнне імя дыяпазону ў Google табліцу

У Google табліцу вызначэння імя трохі адрозніваецца.

  1. Націсніце на першы загаловак слупка з вашых зыходных дадзеных, а затым націсніце Ctrl-Shift-стрэлка направа (Ctrl-Shift-стрэлка направа на Mac). То выбірае радок загалоўкаў слупкоў.
  2. Націсніце Ctrl-Shift-стрэлка ўніз (Ctrl-Shift-стрэлка ўніз на Mac). Гэта выбірае фактычныя дадзеныя.
  3. Націсніце на меню Дадзеныя, а затым выберыце Найменныя і абароненыя дыяпазоны.
  4. У полі Імя і абароненыя дыяпазоны поле справа, тып дадзеных, а затым націсніце кнопку Гатова.
Data menu in Google Sheets
Вызначэнне імя дыяпазону ў Google табліцу

ўвод Формула

Каб увесці формулу, перайдзіце на ліст продажаў Сумы і націсніце на B5.

Увядзіце формулу:

= ВПР (А5, дадзеныя, 8, FALSE)

Націсніце Enter.

entering the vlookup function
Ўвод функцыі ВПР

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

mouse pointer on the autofill dot
Калі вы змесціце паказальнік мышы на кропку ў правым ніжнім куце клеткі, яна становіцца перакрыжаванне аўтазапаўнення

Двойчы пстрыкніце, каб запоўніць значэння ўніз калоны.

worksheet with data after using autofill
Двойчы пстрыкніце перакрыжаванне аўтазапаўнення, каб скапіяваць формулу ўніз па калонцы

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

выкарыстанне MATCH

Функцыя MATCH гэта не вяртае значэнне дадзеных для вас; Вы забяспечваеце значэнне, якое вы шукаеце, і функцыя вяртае пазіцыю гэтага значэння. Гэта як пытацца, дзе # 135 Main Street, і атрымаць адказ, што гэта чацвёртае будынак па вуліцы.

сінтаксіс

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

= ПОИСКПОЗ (значэнне пошуку, дыяпазон табліцы, [тып адпаведнасці])

аргументы:

  • Пошук значэння. Клетка, якая мае унікальны ідэнтыфікатар.
  • Дыяпазон табл. Дыяпазон вочак вы шукаеце.
  • Тып адпаведнасці. Неабавязкова. Гэта, як вызначыць, наколькі блізка ад матчу вы хочаце, наступным чынам:

Наступнае самае высокае значэнне

-1

Значэння павінны быць у парадку змяншэння.

мэтавае значэнне

0

Значэння могуць быць у любым парадку.

Наступнае самае нізкае значэнне

1

Тып па змаўчанні. Значэння павінны быць у парадку ўзрастання.

Як з дапамогай функцыі ВПР, вы, верагодна, знайсці функцыю MATCH прасцей у выкарыстанні, калі вы ўжываеце імя дыяпазону. Перайсці да ліста Крыніца дадзеных абярыце з В4 (загаловак слупка для замовы #) у ніжняй частцы старонкі націсніце ў поле Імя вышэй калонцы А, і называюць яго ORDER_NUMBER. Звярніце ўвагу, што значэння ў парадку ўзрастання.

name box with one column defined
Найменны дыяпазон можа быць толькі адзін слупок, толькі адзін радок, ці нават толькі адна клетка

Перайдзіце на ўкладку запалак працоўнага ліста. У B5, увядзіце функцыю MATCH:

= ПОИСКПОЗ (А5, ORDER_NUMBER, 1)

entering the match function
Ўвод функцыі MATCH

Калі вы не вызначылі імя дыяпазону, вы б напісаць функцыю, як:

= Match (брат, Osourk дадзеных Brother: DONC 03,0)

У любым выпадку, вы можаце ўбачыць, што гэта ў 14-м становішчы (што робіць яго 13-га парадку).

completed match function
Вынік функцыі MATCH

выкарыстанне INDEX

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

сінтаксіс

Сінтаксіс функцыі індэкс з'яўляецца:

= Індэкс (дыяпазон дадзеных, нумар радка, [нумар слупка])

аргументы:

  • Дыяпазон дадзеных. Гэтак жа, як і дзве іншыя функцыі, гэта табліца дадзеных.
  • Нумар радка. Нумар радка дадзеных, якія не абавязкова з'яўляецца радок ліста. Калі дыяпазон табліцы пачынаецца на радку 10 ліста, то гэта радок # 1.
  • Нумар слупка. Нумар слупка дыяпазону дадзеных. Калі дыяпазон пачынаецца на калонцы Е, гэта слупок # 1.

дакументацыя ў Excel пакажа вам, што нумар слупка аргумент з'яўляецца неабавязковым, але нумар радка з'яўляецца свайго роду дадатковым, таксама. Калі дыяпазон табліца ўтрымлівае ні радок або адзін слупок, вам не прыйдзецца выкарыстаць іншы аргумент.

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

Увядзіце формулу:

= Індэкс (дадзеныя, A6, B6)

entering the index function
ўвод функцыі індэкса

Яна вяртае прозвішча кліента: Strevell. Змена значэння А6 і В6, а вынік C6 будзе паказваць розныя вынікі (звярніце ўвагу, што многія радкі маюць адны і тыя ж стану і назвы прадуктаў).

выснову

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

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.