Excel Пошук Формула з некалькімі крытэрамі

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

Формула масіва ўключае ў сябе ўкладанне MATCH функцыі ўнутры INDEX функцыі.

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

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

01 з 09

Увод дадзеных Tutorial

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

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

• Увядзіце верхні дыяпазон дадзеных у вочках D1 да F2
• Увядзіце другі дыяпазон у вочках D5 да F11

Радкі 3 і 4 пакінутыя пустымі для таго , каб прыстасаваць формулу масіва , створанага падчас гэтага ўрока.

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

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

02 з 09

Запуск функцыі INDEX

Функцыя індэкс з'яўляецца адным з нямногіх у Excel, які мае некалькі формаў. Функцыя мае форму масіва і спасылка на форму.

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

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

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

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

1. Націсніце на вочка F3 , каб зрабіць яго актыўную вочка . Тут мы будзем уводзіць укладзеную функцыю.
2. Націсніце на ўкладцы Формулы ў стужках меню.
3. Выберыце Lookup і заданні на стужцы , каб адкрыць функцыю расчыняецца спісу.
4. Націсніце на INDEX ў спісе , каб адкрыць Select Arguments дыялогавага акна .
5. Выберыце масіў, ROW_NUM, col_num параметр ў дыялогавым акне.
6. Націсніце кнопку OK, каб адкрыць дыялогавае акно функцыі INDEX.

03 з 09

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

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

Для гэтага ўрока гэты аргумент будзе наш прыклад базы дадзеных .

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

1. У функцыі INDEX дыялогавым акне , націсніце на лінейны масіў.
2. Вылучыце ячэйкі D6 на F11 ў лісце , каб увесці дыяпазон у дыялогавым акне.

04 з 09

Запуск функцыі ўкладаннямі MATCH

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

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

У гэтым падручніку, укладзеная функцыя MATCH і яго аргументы будуць уведзены ў другой радку дыялогавага акна функцыі INDEX - лініі ROW_NUM.

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

Ўвод Lookup_Value аргументу функцыі вызначэння супадзенні ў

Першы крок у выхадзе на укладзеную функцыю MATCH, каб увайсці ў аргумент искомое_значение.

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

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

Гэта робіцца шляхам канкатэнацыі або аб'яднання двух або больш спасылак на вочкі разам , выкарыстоўваючы Ампэрсанд «&».

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

1. У дыялогавым акне функцыі INDEX, націсніце на лініі ROW_NUM.
2. Увядзіце матч імя функцыі адкрытай круглай дужкі «(»
3. Націсніце на вочка D3 , каб ўвесці гэтую спасылку на вочка ў дыялогавае акно.
4. Увядзіце Ампэрсанд «&» пасля таго, як спасылкі на вочка D3 для таго , каб дадаць другую спасылку на вочка.
5. Націсніце на вочка Е3 ўвесці гэтую другую спасылку на вочка ў дыялогавае акно.
6. Увядзіце коску «» пасля таго, як спасылкі на вочка E3 , каб завяршыць ўвод Lookup_Value аргументу функцыі адпаведнасці ў.
7. Пакіньце дыялогавае акно функцыі індэкс адкрытым для наступнага кроку ў падручніку.

На апошнім этапе ўрока ў Lookup_values ​​будзе ўведзены ў клеткі D3 і E3 працоўнага ліста.

05 з 09

Даданне Lookup_Array для MATCH функцыі

Гэты этап ахоплівае даданне просматриваемый_массива аргументу для укладзенай функцыі MATCH.

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

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

Каб ўвесці некалькі масіваў мы зноў выкарыстоўваем Ампэрсанд «&» , каб аб'яднаць масівы разам.

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

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

1. Націсніце на лініі ROW_NUM пасля коскі , каб змясціць курсор у канцы бягучага запісу.
2. Вылучыце ячэйкі D6 для D11 ў лісце , каб увайсці ў дыяпазон. Гэта першы масіў функцыя пошуку.
3. Увядзіце Ампэрсанд «&» пасля таго, як спасылкі на ячэйкі D6: D11 , таму што мы хочам, каб функцыя пошуку двух масіваў.
4. Вылучыце клеткі E6 для Е11 на лісце, каб увайсці ў дыяпазон. Гэта другі масіў функцыя пошуку.
5. Увядзіце коску «» пасля таго, як спасылкі на вочка E3 , каб завяршыць ўвод Lookup_Array аргументу функцыі адпаведнасці ў.
6. Пакіньце дыялогавае акно функцыі індэкс адкрытым для наступнага кроку ў падручніку.

06 з 09

Даданне тыпу матчу і Завяршэнне MATCH функцыі

Трэці і апошні аргумент функцыі MATCH з'яўляецца аргументам тип_сопоставления.

Гэты аргумент кажа Excel, як адпавядаць Lookup_Value са значэннямі ў Lookup_Array. Магчымыя наступныя варыянты: 1, 0 або -1.

Гэты аргумент з'яўляецца неабавязковым. Калі яна апушчана функцыя выкарыстоўвае значэнне па змаўчанні 1.

• калі тип_сопоставления = 1 або апушчана: ПОИСКПОЗ знаходзіць найбольшае значэнне, якое менш або роўна искомое_значение. Дадзеныя Lookup_Array павінны быць адсартаваныя ў парадку ўзрастання.
• калі тип_сопоставления = 0: ПОИСКПОЗ знаходзіць першае значэнне, якое ў дакладнасці роўна искомое_значение. Дадзеныя Lookup_Array могуць быць адсартаваныя ў любым парадку.
• калі тип_сопоставления = -1: ПОИСКПОЗ знаходзіць найменшае значэнне, якое больш або роўна искомое_значение. Дадзеныя Lookup_Array павінны быць адсартаваныя ў парадку змяншэння.

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

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

1. Пасля коскі ў радку ROW_NUM увядзіце нуль "0" , так як мы хочам , каб укладзеная функцыя вяртала дакладныя супадзення з пункту гледжання мы ўступаем у вочках D3 і E3.
2. Увядзіце зачыняе круглыя дужкі «)» для завяршэння функцыі MATCH.
3. Пакіньце дыялогавае акно функцыі індэкс адкрытым для наступнага кроку ў падручніку.

07 з 09

Назад да INDEX функцыі

Цяпер, калі функцыя MATCH будзе зроблена , мы будзем рухацца да трэцяй радку адкрытага дыялогавага акна і ўвядзіце апошні аргумент для функцыі INDEX.

Гэта трэці і апошні аргумент з'яўляецца аргументам номер_столбца , які кажа Excel нумар слупка ў дыяпазоне D6 да F11 , дзе ён будзе знайсці інфармацыю , мы хочам , вернутую функцыя. У гэтым выпадку пастаўшчык тытана віджэтаў.

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

1. Націсніце на лініі номер_столбца ў дыялогавым акне.
2. Калі ласка, увядзіце нумар тры "3" (без двукоссяў) на гэтай лініі , так як мы шукаем дадзеныя ў трэцім слупку дыяпазону D6 да F11.
3. Ня Націсніце кнопку OK або зачыніць дыялогавае акно функцыі INDEX. Ён павінен заставацца адкрытым для наступнага кроку ў падручніку - стварэнне формулы масіва .

08 з 09

Стварэнне формулы масіва

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

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

Стварэнне формулы масіва ў Excel ажыццяўляецца націскам клавішы CTRL, SHIFT і ENTER клавішы на клавіятуры адначасова.

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

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

1. З дыялогавае акно завершана да гэтага часу адкрыты з папярэдняга этапу дадзенага кіраўніцтва, націсніце і ўтрымлівайце клавішу CTRL і SHIFT клавішы на клавіятуры , затым націсніце і адпусціце кнопку ENTER.
2. Калі ўсё зроблена правільна, то дыялогавае акно зачыніцца , і # N / A памылка з'явіцца ў вочку F3 - вочка , у якой мы ўвайшлі ў функцыі.
3. # Н / памылка з'яўляецца ў вочках F3 , так як клеткі D3 і Е3 з'яўляюцца пустымі. D3 і E3 з'яўляюцца клеткамі, дзе мы распавядалі функцыю, каб знайсці Lookup_values ​​за крок 5 падручніка. Пасля таго, як дадзеныя будуць дададзеныя ў гэтых двух вочак, памылка будзе заменена інфармацыяй з базы дадзеных .

09 з 09

Даданне крытэрыяў пошуку

Апошні крок у падручніку, каб дадаць ўмова пошуку ў нашу табліцу.

Як ужо згадвалася ў папярэднім кроку, мы хочам, каб яны супадалі з умовамі віджэтаў з калонкі 1 і тытана з калонкі 2.

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

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

1. Націсніце на вочка D3.
2. Увядзіце фішкі і націсніце клавішу Enter на клавіятуры.
3. Націсніце на вочка Е3.
4. Увядзіце Titanium і націсніце клавішу Enter на клавіятуры.
5. Імя пастаўшчыка Widgets Inc. з'явіцца ў вочку F3 - месцазнаходжанне функцыі , паколькі яна з'яўляецца адзіным пастаўшчыком ў спісе , які прадае Titanium Widgets.
6. Пры націску на вочку F3 поўнай функцыі
   {= Індэкс (D6: F11, MATCH (D3 & Е3, D6: D11 & Е6: Е11, 0), 3)}
   з'яўляецца ў радку формул над лістом .

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