С VLOOKUP винаги получаваме първия мач. Същото се случва и с функцията INDEX MATCH. И така, как да VLOOKUP втори мач или 3 -ти или n -ти? В тази статия ще научим как да получим N -та поява на стойност в диапазона.
Обща формула
{= МАЛКО (АКО (диапазон = стойност, ROW (диапазон) -ROW (първа_клетка_в_обхват)+1),н)}
Забележка: това е формула на масива. Трябва да го въведете с CTRL + SHIFT + ENTER.
Диапазон: диапазона, в който искате да потърсите нпозиция на стойност.
Стойност: стойността, която търсите нпозиция вдиапазон.
First_cell_in_range: първата клетка вдиапазон. Ако диапазонът е A2: A10, тогава първата клетка в диапазона е А2.
н: на възникване брой стойности.
Нека видим пример, за да изясним нещата.
Пример: Намерете второто съвпадение в Excel
Тук имам този списък с имена в Excel диапазон A2: A10. Нарекох този диапазон като имена. Сега искам да получа позицията на второто появяване на „Рони“ в имена.
На горното изображение можем да видим, че е на 7 -ма позиция в диапазон A2: A10 (имена). Сега трябва да получим позицията му с помощта на формула на Excel.
Приложете горната обща формула в C2, за да потърсите второто появяване на Rony в списъка.
{= МАЛКО (АКО (имена = „Рони“ , ROW (имена) -ROW (A2)+1),2)}
Въведете го с CTRL + SHIFT + ENTER …
И ние имаме отговор. Той показва 7, което е правилно. Ако промените стойността на n на 3, тя ще даде 8. Ако промените стойността на n по -голяма от появата на стойността в диапазона, тя ще върне грешка #NUM.
Как работи?
Е, това е доста лесно. Нека видим всяка част една по една.
АКО (имена = „Рони“ , ROW (имена) -ROW (A2)+1) :
В IF, names = “Rony” връща масив от TRUE и FALSE. ИСТИНСКИ, когато клетка в обхват имена (A2: A10) съответства на „Рони“. {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}.
Следващ ред (имена) -ROW (A2)+1:
ROW (имена): тук функцията ROW връща номера на ред на всяка клетка в имена. {2; 3; 4; 5; 6; 7; 8; 9; 10}.
ROW (имена) -ROW (A2)След това изваждаме номера на ред от A2 от всяка стойност в дадения масив. Това ни дава масив от серийни номера, започващи от 0. {0; 1; 2; 3; 4; 5; 6; 7; 8}.
ROW (имена) -ROW (A2)+1: За да получим серийни номера, започващи от 1, добавяме 1 към всяка стойност в този масив. Това ни дава серийния номер, започващ от 1. {1; 2; 3; 4; 5; 6; 7; 8; 9}.
Сега имаме IF ({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9}). Това решава до {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}.
Сега имаме формула, решена на МАЛКА ({1; FALSE; FALSE; FALSE; FALSE; FALSE;7;8; FALSE},2). Сега SMALL връща втората най -малка стойност в диапазона, която е 7.
Как да го използваме?
Идва въпросът: каква е ползата от получаването на суров индекс на n -то съвпадение? Би било по -полезно, ако можете да извлечете свързана информация от n -та стойност. Е, и това може да се направи. Ако искаме да получим стойност от стойността на n -то съвпадение в съседната клетка в диапазона имена (A2: A10).
{= ИНДЕКС (B2: B10, МАЛКИ (АКО (имена = „Рони“ , ROW (имена) -ROW (A2)+1),2))}
Така че да, момчета, ето как можете да получите n -ти мач в диапазон. Надявам се да съм бил достатъчно обяснителен. Ако имате някакви съмнения относно тази статия или друга тема, свързана с Excel/VBA, пишете в секцията за коментари по -долу.
Как да получите последователен номер на ред в Excel
Vlookup Топ 5 стойности с дублирани стойности, използващи INDEX-MATCH в Excel
VLOOKUP Множество стойности
Използвайте INDEX и MATCH за търсене на стойност
Търсена стойност с множество критерии
Популярни статии:
Функцията VLOOKUP в Excel
COUNTIF в Excel 2016
Как да използвате функцията SUMIF в Excel
