В тази статия ще научим как да търсим стойности в 2d таблица с помощта на функция INDEX-MATCH-MATCH в Excel.
Сценарий:
Да предположим, че трябва да правите много търсения от таблица със стотици колони. В такива случаи използването на различни формули за всяко търсене ще отнеме твърде много време. Какво ще кажете за създаване на формула за динамично търсене, която можете да потърсите чрез предоставената заглавка. Да, можем да направим това. Тази формула се нарича INDEX MATCH MATCH формула или да се каже 2d формула за търсене.
Как да решим проблема?
За да разберем първо формулата, трябва да преразгледаме малко следните функции
- INDEX функция
- MATCH функция
Функцията INDEX връща стойността при даден индекс в масив.
Функцията MATCH връща индекса на първото появяване на стойността в масив (масив с едно измерение).
Сега ще направим формула, използвайки горните функции. Функцията за съвпадение ще върне индекса на стойността за търсене1 в полето за заглавие на ред. И друга функция MATCH ще върне индекса на стойността за търсене2 в полето на заглавието на колоната. Номерата на индекса сега ще бъдат въведени във функцията INDEX, за да получат стойностите под стойността за търсене от данните от 2D таблицата.
Обща формула:
= INDEX (данни, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))
Данни: масив от стойности в таблицата без заглавки
lookup_value1 : стойност за търсене в ред_заглавие.
row_headers : Масив от индекс на редове, за да потърсите нагоре.
lookup_value1 : стойност за търсене в колоната_заглавие.
колони_заглавки : колона Индексен масив за търсене.
Пример:
Горните твърдения могат да бъдат трудни за разбиране. Така че нека разберем това, като използваме формулата в пример
Тук имаме списък с резултати, получени от учениците с техния списък с теми. Трябва да намерим резултата за конкретен студент (Гари) и предмет (социални изследвания), както е показано на снимката по -долу.
Стойността Student1 трябва да съвпада с масив Row_header и Subject value2 трябва да съответства на масива Column_header.
Използвайте формулата в клетката J6:
= INDEX (таблица, MATCH (J5, ред, 0, MATCH (J4, колона, 0)))
Обяснение:
- Функцията MATCH съвпада със стойността на Student в клетката J4 с масива на заглавката на реда и връща нейната позиция 3 като число.
- Функцията MATCH съвпада със стойността на Subject в клетката J5 с масива на заглавката на колоната и връща нейната позиция 4 като число.
- Функцията INDEX взема индексния номер на ред и колона и търси в табличните данни и връща съответстващата стойност.
- Аргументът тип MATCH е фиксиран на 0. Тъй като формулата ще извлече точното съвпадение.
Тук стойностите на формулата са дадени като препратки към клетки и ред_заглавие, таблица и колона_заглавие, дадени като именувани диапазони.
Както можете да видите в горната снимка, получихме резултата, получен от студента Гари в Предмет Социални проучвания като 36.
Това доказва, че формулата работи добре и за съмнения вижте по -долу бележките за разбиране.
Сега ще използваме приблизителното съвпадение с заглавки на редове и заглавки на колони като числа. Приблизителното съвпадение приема само числовите стойности, тъй като няма начин да се приложи към текстовите стойности
Тук имаме цена на стойности според височината и ширината на продукта. Трябва да намерим Цената за конкретна височина (34) и ширина (21), както е показано на снимката по -долу.
Стойността Height1 трябва да съвпада с масива Row_header, а стойността Width2 трябва да съвпада с масива Column_header.
Използвайте формулата в клетката K6:
= ИНДЕКС (данни, MATCH (K4, Height, 1, MATCH (K5, Width, 1)))
Обяснение:
- Функцията MATCH съвпада със стойността на Height в клетката K4 с масива на заглавката на реда и връща нейната позиция 3 като число.
- Функцията MATCH съвпада със стойността на Width в клетка K5 с масива на заглавката на колоната и връща нейната позиция 2 като число.
- Функцията INDEX взема индексния номер на ред и колона и търси в табличните данни и връща съответстващата стойност.
- Аргументът тип MATCH е фиксиран на 1. Тъй като формулата ще извлече приблизителното съвпадение.
Тук стойностите на формулата са дадени като препратки към клетки и ред_заглавие, данни и колона_заглавие, дадени като именувани диапазони, както е споменато в моментната снимка по -горе.
Както можете да видите в горната снимка, имаме цената, получена по височина (34) & Ширина (21) като 53.10. Това доказва, че формулата работи добре и за съмнения вижте бележките по -долу за повече разбиране.
Бележки:
- Функцията връща грешката #NA, ако аргументът на масива за търсене към функцията MATCH е 2D масив, който е полето на заглавката на данните …
- Функцията съвпада с точната стойност, тъй като аргументът тип съвпадение на функцията MATCH е 0.
- Стойностите за търсене могат да бъдат дадени като препратка към клетка или директно като се използва кавичка (") във формулата като аргументи.
Надявам се, че сте разбрали как да използвате Търсене в двумерна таблица, използвайки функцията INDEX & MATCH в Excel. Разгледайте още статии в Excel за търсене на стойност тук. Моля, не се колебайте да заявите вашите запитвания по -долу в полето за коментари. Ние със сигурност ще ви помогнем.
Използвайте INDEX и MATCH за търсене на стойност : INDEX & MATCH функция за търсене на стойност според нуждите.
Обхват SUM с INDEX в Excel : Използвайте INDEX функцията, за да намерите SUM на стойностите, както е необходимо.
Как да използвате функцията SUM в Excel : Намерете SUM на числата, като използвате функцията SUM, обяснена с пример.
Как да използвате функцията INDEX в Excel : Намерете INDEX на масива, като използвате функцията INDEX, обяснена с пример.
Как да използвате функцията MATCH в Excel : Намерете MATCH в масива, като използвате стойността INDEX вътре в функцията MATCH, обяснена с пример.
Как да използвате функцията LOOKUP в Excel : Намерете стойността за търсене в масива, като използвате функцията LOOKUP, обяснена с пример.
Как да използвате функцията VLOOKUP в Excel : Намерете стойността за търсене в масива, като използвате функцията VLOOKUP, обяснена с пример.
Как да използвате функцията HLOOKUP в Excel : Намерете стойността за търсене в масива, като използвате функцията HLOOKUP, обяснена с пример.
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
Присъединете се към собствено и фамилно име в Excel
