Як виконати двосторонній пошук у Excel 2013

У Excel 2013 може бути таблиця, в якій вам потрібно виконати двосторонній пошук, за допомогою якого частина даних витягується з таблиці пошуку на основі пошуку значення у верхньому рядку (із заголовками стовпців таблиці) і значення в першому стовпці (із заголовками рядків таблиці).

Малюнок ілюструє ситуацію, коли ви використовуєте два значення, дату виробництва та номер деталі, щоб знайти очікуване виробництво. У таблиці «Графік виробництва» на 2013 рік дати виробництва кожної деталі утворюють заголовки стовпців у першому рядку таблиці, а номери деталей утворюють заголовки рядків у першому стовпці таблиці.

Щоб знайти номер деталі, запланованої на виготовлення в певному місяці, вам потрібно скористатися функцією MATCH, яка повертає відносне положення певного значення в діапазоні комірок або масиві. Синтаксис функції MATCH такий:

MATCH(значення_шукання,масив_шукання,[тип_відповідності])

Аргумент lookup_value — це, звичайно, значення, позицію якого ви хочете повернути, коли знайдено збіг, а lookup_array — це діапазон комірок або масив, що містить значення, які ви хочете знайти. Додаткове тіп_сопоставленіе аргумент число 1, 0 або -1, який визначає , як Excel , збігається зі значенням , зазначеним у шукане_значення аргументу в діапазоні , заданому з допомогою просматріваемий_массіва аргументу:

• Використовуйте match_type 1, щоб знайти найбільше значення, яке менше або дорівнює lookup_value . Зауважте, що значення в масиві lookup_array мають бути розміщені в порядку зростання, коли ви використовуєте аргумент 1 match_type . (Excel використовує цей тип відповідності, коли аргумент match_type опущено у функції MATCH.)

• Використовуйте match_type 0, щоб знайти перше значення, яке точно дорівнює lookup_value . Зауважте, що значення в масиві lookup_array можуть бути в будь-якому порядку, якщо ви використовуєте аргумент match_type 0 .

• Використовуйте match_type – 1, щоб знайти найменше значення, яке більше або дорівнює lookup_value . Зауважте, що значення в масиві lookup_array мають бути розміщені в порядку спадання, коли ви використовуєте аргумент –1 match_type .

На додаток до пошуку позиції дати виробництва та номера частини в заголовках стовпців і рядків у таблиці Розклад виробництва, вам потрібно використовувати функцію INDEX, яка використовує відносну позицію рядка та номера стовпця, щоб повернути номер, який буде виготовлено. із самого столу.

Функція INDEX має дві різні форми синтаксису: масив і посилання. Ви використовуєте форму масиву, коли хочете, щоб значення було повернуто з таблиці (як у цьому прикладі), і ви використовуєте форму посилання, коли хочете, щоб посилання поверталося з таблиці.

Синтаксис форми масиву функції INDEX такий:

INDEX(масив,[номер_рядка],[число_стовпця])

Синтаксис довідкової форми функції INDEX такий:

ІНДЕКС(посилання,[номер_рядка],[номер_стовпця],[номер_області])

Масив аргументів форми масиву функції ІНДЕКС є діапазон комірок або константа масиву , який ви хочете використовувати Excel в результаті пошуку. Якщо цей діапазон або константа містить тільки один рядок або стовпець, відповідні ROW_NUM або col_num аргументи НЕ є обов'язковими.

Якщо діапазон або масив константа має більше за один рядок або більше одного стовпчика, і вказати , як ROW_NUM і col_num аргументи, Excel повертає значення в масиві аргументу , який знаходиться на перетині ROW_NUM аргументу і col_num аргументу .

Для функцій MATCH і INDEX у прикладі наступні назви діапазонів були призначені наступним діапазонам комірок:

• table_data до діапазону комірок A2:J6 з виробничими даними плюс заголовками стовпців і рядків

• part_list до діапазону комірок A2:A6 із заголовками рядків у першому стовпці таблиці

• date_list до діапазону комірок A2:J2 із заголовками стовпців у першому рядку таблиці

• part_lookup до клітинки B10, яка містить назву частини, яку потрібно знайти в таблиці

• date_lookup до клітинки B11, яка містить назву дати виробництва, яку потрібно знайти в таблиці

Як показано на малюнку, клітинка B12 містить досить довгу і — на перший погляд — складну формулу, що використовує назви діапазонів, наведені раніше, і поєднує функції INDEX і MATCH:

=ІНДЕКС(дані_таблиці, MATCH(пошук_частини,список_частини), MATCH(пошук_дати,список_дат))