В тази статия ще научим как да търсите най -близкото съвпадение, като използвате функцията INDEX & MATCH в Excel.
Сценарий:
Например, трябва да намерим числово най -близкото съвпадение и съответните му резултати от таблицата. Нуждаем се от някаква фиксирана формула, която помага при намирането на най -близкото съвпадение, за да получим необходимата стойност.
Как да решим проблема?
За да разберем първо формулата, трябва да преразгледаме малко следните функции
- INDEX функция
- MATCH функция
Сега ще направим формула, използвайки горните функции. Функцията MATCH ще върне индекса на най -близкото съвпадение от диапазона. Функцията INDEX приема индекса на реда като аргумент и връща съответните му необходими резултати.
Обща формула:
{= INDEX (данни, MATCH (MIN (ABS (данни - стойност)), ABS (данни - стойност), 0))}}
данни : масив от стойности на дата
стойност : дадена стойност на датата, където резултатът е най -близо до
match_type : 1 (точно или следващото най -малко) или 0 (точно съвпадение) или -1 (точен или следващ по големина)
НЕДЕЙ използвайте къдрави скоби ръчно. Къдрави скоби, приложени с помощта на Ctrl + Shift + Enter на мястото на просто Въведете.
Пример:
Горните твърдения могат да бъдат трудни за разбиране. Така че нека разберем това, като използваме формулата в пример
Тук имаме някои случайни стойности на дата и дадена стойност на дата.
Именован диапазон, използван за данни (D4: D12).
Тук вместо това използвахме именования диапазон за масив от референтни клетки, тъй като е лесен за разбиране. Трябва да потърсим датата, която е най -близо до датата в клетка G6.
Използвайте горната формула, за да получите най -близката дата.
Използвайте формулата:
{ = INDEX (данни, MATCH (MIN (ABS (данни - G6)), ABS (данни - G6), 0)) }
НЕ използвайте ръчно къдрави скоби. Къдрави скоби, приложени с помощта на Ctrl + Shift + Enter на мястото на просто Въведете.
Обяснение:
- Функцията ABS връща положителното цяло число след изваждането, извършено над (данни - G6) стойности на дата.
- ABS (данни - G6) прави масив, като изважда всички стойности на датата от дадената стойност в клетката G6. Върнатият масив е.
{ 160 ; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }
- MIN (ABS (данни - G6)) връща най -близката стойност от масива, върнат от горната стъпка.
= INDEX (данни, MATCH (6, {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))
- Функцията MATCH съответства на най -близкото съвпадение на минималната стойност във върнатия масив и връща индекса на реда си на функцията INDEX.
- Функцията INDEX намира стойността, върнала индекса ROW.
данни с имена диапазон, използвани за масива с дати. Сега използвайте Ctrl + Shift + Enter на мястото на просто Enter top получите резултата, тъй като това е формула за масив. Формулата на масива е формула, при която масив от стойности преминава от един резултат на функция като аргумент към външна функция.
Както можете да видите от горната снимка, получихме най -близкото съвпадение от дадената дата в таблицата. Извлечете подробности от таблицата, като използвате горната формула.
Ето някои наблюдения, използващи горната формула.
Бележки:
- Функцията връща грешката #NA, ако аргументът на масива за търсене към функцията MATCH не е със същата дължина на масива от таблици.
- Формулата връща грешка, ако lookup_value не съвпада със стойността в таблицата lookup_array.
- Функцията съвпада с точната стойност, тъй като аргументът тип съвпадение на функцията MATCH е 0.
- Стойностите за търсене могат да бъдат дадени като препратка към клетка или директно като се използва кавичка (") във формулата като аргументи.
- НЕ използвайте ръчно къдрави скоби. Къдрави скоби, приложени с помощта на Ctrl + Shift + Enter на мястото на просто Въведете иначе връща грешка.
Надявам се, че сте разбрали как да търсите най -ниското или най -малкото съвпадение, като използвате функцията INDEX & MATCH в Excel. Разгледайте още статии за стойността на търсене в Excel и функциите на Excel 2019 тук. Моля, не се колебайте да заявите вашите запитвания по -долу в полето за коментари. Ние със сигурност ще ви помогнем.
Ако ви харесаха нашите блогове, споделете го с приятелите си във 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
Ако с условно форматиране
Ако с заместващи символи
Функцията CONCAT на Excel 2019
Функцията Excel 2019 MAXIFS
