В тази статия ще научим как да търсим n -то съвпадение в таблица, използвайки функцията INDEX & MATCH.
Сценарий:
Например, трябва да намерим числовото n -то съвпадение и съответните му резултати от таблицата. Нуждаем се от някаква обща формула, която помага при намирането на n -то съвпадение, за да получим необходимата стойност.
Как да решим проблема?
За да разберем първо формулата, трябва да преразгледаме малко следните функции
- INDEX функция
- MATCH функция
Сега ще направим формула, използвайки горните функции. Функцията MATCH ще върне индекса на най -ниското съвпадение от диапазона. Функцията INDEX приема индекса на реда като аргумент и връща съответните му необходими резултати. Тази функция намира
Обща формула:
= INDEX (данни, MATCH (МАЛКИ (диапазон, n), диапазон, тип на съвпадение), колонен номер))
данни : масив от стойности в таблицата без заглавки
диапазон : lookup_array за най -ниското съвпадение
н : число, n -то съвпадение
match_type : 1 (точно или следващото най -малко) или 0 (точно съвпадение) или -1 (точен или следващ по големина)
col_num: номер на колона, необходима стойност за извличане от колоната на таблицата.
Пример:
Горните твърдения могат да бъдат объркващи за разбиране. Така че нека разберем това, като използваме формулата в пример
Тук имаме таблица с подробности за световния континент. Трябва да намерим кода на държавата от дадената таблица от стойността за търсене като име на държава.
Именован диапазон, използван за таблица (E5: I10) и диапазон (I5: I10).
Тук вместо това използвахме именования диапазон за масив от референтни клетки, тъй като е лесен за разбиране. Трябва да търсим всички подробности, където Стойността на цената е минимална или най -ниска в диапазона.
Използвайте горната формула, за да получите първия детайл, който е капитал
Използвайте формулата в клетката G6:
= INDEX (таблица, MATCH ( МАЛКИ (диапазон, 1) ,диапазон, 0 ) , 4 )
Назовани диапазони
маса (E5: I10)
обхват (I5: I10)
Обяснение:
- Функцията SMALL намира първото най -ниско съвпадение в диапазона и връща стойността на функцията MATCH.
- Функцията MATCH съвпада с точното съвпадение на минималната стойност в ценовия диапазон и връща индекса на реда си във функцията INDEX.
- Функцията INDEX намира капиталната стойност с индекс ROW и четвърта колона в таблицата с име.
Формулата връща всички резултати за първата най -ниска стойност. Сега копирайте формулата, за да получите втората най -малка или трета най -ниска, просто трябва да промените n аргумента при SMALL функция, за да получите различните резултати.
Сега просто променете стойността n и получете резултатите, както е показано по -долу.
Както можете да видите от горната снимка, получихме всички подробности, съответстващи на n -та стойност в таблицата. Извлечете подробности от таблицата, като използвате горната формула.
Пример:
Можете също да използвате функцията LARGE, за да получите n -то съвпадение от върха или n -то най -високо съвпадение от диапазона и да върне съответните му резултати.
Обща формула:
= INDEX (данни, MATCH (LARGE (range, n), range, match_type), col_num)
данни : масив от стойности в таблицата без заглавки
диапазон : lookup_array за най -високо съвпадение
н : число, n -то съвпадение
match_type : 1 (точно или следващото най -малко) или 0 (точно съвпадение) или -1 (точен или следващ по големина)
col_num: номер на колона, необходима стойност за извличане от колоната на таблицата.
Пример:
Горните твърдения могат да бъдат объркващи за разбиране. Така че нека разберем това, като използваме формулата в пример
Тук имаме таблица с подробности за световния континент. Трябва да намерим кода на държавата от дадената таблица от стойността за търсене като име на държава.
Именован диапазон, използван за таблица (E5: I10) и диапазон (I5: I10).
Тук вместо това използвахме именования диапазон за масив от референтни клетки, тъй като е лесен за разбиране. Трябва да търсим всички подробности, където Стойността на цената е максимална или най -висока в диапазона.
Използвайте горната формула, за да получите първия детайл, който е капитал
Използвайте формулата в клетката G6:
= INDEX (таблица, MATCH ( ГОЛЯМ (диапазон, 1) ,диапазон, 0 ) , 4 )
Назовани диапазони
маса (E5: I10)
обхват (I5: I10)
Обяснение:
- LARGE функцията намира първото най -високо съвпадение в диапазона и връща стойността на функцията MATCH.
- Функцията MATCH съвпада с точното n -то съвпадение на стойността в ценовия диапазон и връща индекса на реда си във функцията INDEX.
- Функцията INDEX намира капиталната стойност с индекс ROW и четвърта колона в таблицата с име.
Формулата връща всички резултати за първата най -ниска стойност. Сега копирайте формулата, за да получите втората най -малка или трета най -ниска, просто трябва да промените n аргумента при SMALL функция, за да получите различните резултати.
Сега просто променете стойността n и получете резултатите, както е показано по -долу.
Както можете да видите от горната снимка, получихме всички подробности, съответстващи на n -та стойност в таблицата. Извлечете подробности от таблицата, като използвате горната формула.
Ето някои наблюдения, използващи горната формула.
Бележки:
- Стойността на n не може да бъде по -малка от 1 или по -голяма от дължината на диапазона
- Функцията връща грешката #NA, ако аргументът на масива за търсене към функцията MATCH не е със същата дължина на масива от таблици.
- Формулата връща грешка, ако lookup_value не съвпада със стойността в таблицата lookup_array.
- Функцията съвпада с точната стойност, тъй като аргументът тип съвпадение на функцията MATCH е 0.
- Стойностите за търсене могат да бъдат дадени като препратка към клетка или директно като се използва кавичка (") във формулата като аргументи.
Надявам се, че сте разбрали как да търсите n -то съвпадение в таблица, използвайки функцията INDEX & MATCH. Разгледайте още статии за стойността на търсене в Excel тук. Моля, не се колебайте да заявите вашите запитвания по -долу в полето за коментари. Ние със сигурност ще ви помогнем.
Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта
Използвайте INDEX и MATCH за търсене на стойност : INDEX & MATCH функция за търсене на стойност според нуждите.
Обхват SUM с INDEX в Excel : Използвайте INDEX функцията, за да намерите SUM на стойностите, както е необходимо.
Как да използвате функцията INDEX в Excel : Намерете INDEX на масива, като използвате функцията INDEX, обяснена с пример.
Как да използвате функцията MATCH в Excel : Намерете MATCH в масива, като използвате стойността INDEX вътре в функцията MATCH, обяснена с пример.
Как да използвате функцията LOOKUP в Excel : Намерете стойността за търсене в масива, като използвате функцията LOOKUP, обяснена с пример.
Как да използвате функцията VLOOKUP в Excel : Намерете стойността за търсене в масива, като използвате функцията VLOOKUP, обяснена с пример.
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
Присъединете се към собствено и фамилно име в Excel
