Как да извършите двупосочно търсене в Excel 2013

Може да имате таблица в Excel 2013, в която трябва да извършите двупосочно търсене, при което част от данни се извлича от таблицата за справка въз основа на търсене на стойност в горния ред (със заглавията на колоните на таблицата) и стойност в първата колона (със заглавията на редовете на таблицата).

Фигурата илюстрира ситуация, в която бихте използвали две стойности, датата на производство и номера на частта, за да търсите очакваното производство. В таблицата с производствения график за 2013 г. датите на производство за всяка част образуват заглавията на колоните в първия ред на таблицата, а номерата на частите образуват заглавията на редовете в първата колона на таблицата.

За да намерите номера на частта, планирана да бъде произведена през определен месец, трябва да използвате функцията MATCH, която връща относителната позиция на определена стойност в диапазон от клетки или масив. Синтаксисът на функцията MATCH е както следва:

СЪВПАДЕНИЕ(стойност на търсене, масив_на_търсене, [тип_съвпадение])

Аргументът lookup_value е, разбира се, стойността, чиято позиция искате да бъде върната, когато бъде намерено съвпадение, а lookup_array е диапазонът от клетки или масивът, съдържащ стойностите, които искате да съпоставите. Незадължителният аргумент match_type е числото 1, 0 или –1, което указва как Excel съответства на стойността, посочена от аргумента lookup_value , в диапазона, определен от аргумента lookup_array :

• Използвайте match_type 1, за да намерите най-голямата стойност, която е по-малка или равна на lookup_value . Обърнете внимание, че стойностите в lookup_array трябва да бъдат поставени във възходящ ред, когато използвате аргумента 1 match_type . (Excel използва този тип съвпадение, когато аргументът match_type е пропуснат от функцията MATCH.)

• Използвайте match_type 0, за да намерите първата стойност, която е точно равна на lookup_value . Обърнете внимание, че стойностите в lookup_array могат да бъдат в произволен ред, когато използвате аргумента 0 match_type .

• Използвайте match_type – 1, за да намерите най-малката стойност, която е по-голяма или равна на lookup_value . Обърнете внимание, че стойностите в lookup_array трябва да бъдат поставени в низходящ ред, когато използвате аргумента –1 match_type .

В допълнение към търсенето на позицията на датата на производство и номера на частта в заглавията на колоните и редовете в таблицата на производствения график, трябва да използвате функция INDEX, която използва относителната позиция на ред и колона, за да върне номера, който ще бъде произведен от самата маса.

Функцията INDEX следва две различни синтактични форми: масив и препратка. Използвате формата за масив, когато искате стойност, върната от таблицата (както правите в този пример), и използвате формуляра за справка, когато искате препратка да бъде върната от таблицата.

Синтаксисът на формата на масива на функцията INDEX е както следва:

INDEX(масив,[номер_ред],[кол_кола])

Синтаксисът на референтната форма на функцията INDEX е както следва:

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

В масив аргумент на формуляра за масив от функцията INDEX е диапазон от клетки или постоянно масив, който искате да използвате Excel в търсене. Ако този диапазон или постоянна съдържа само един ред или колона, съответните ROW_NUM или col_num аргументи не са задължителни.

Ако гама или масив константа има повече от един ред или повече от една колона, и сте посочили, както ROW_NUM и col_num аргументи, Excel връща стойността в масив аргумента, че се намира в пресечната точка на ред_номер аргумент и 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:

=ИНДЕКС(данни_таблици,МАТЧ(търсене_на_части,списък_части),МАТЧ(търсене_дата,списък_дата))