Как найти Nth Происшествие в Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: lookup-formulas

С VLOOKUP мы всегда получаем первое совпадение. То же самое происходит с функцией ИНДЕКС ПОИСКПОЗ. Так как же нам выполнить ВПР во втором, третьем или восьмом совпадении?

В этой статье мы узнаем, как получить N-е вхождение значения в диапазоне.

0055

Общая формула

{=SMALL(IF(range=value,ROW(range)-ROW(first_cell_in_range)+1),n)}

Примечание: это` ссылка: / excel-array-formulas-array-in-excel-formula [array formula] `. Вам нужно ввести его с помощью CTRL + SHIFT + ENTER . Диапазон: диапазон, в котором вы хотите найти n-ю позицию значения *.

Значение: значение, которое вы ищите на n-й позиции в диапазоне. First_cell_in_range: первая ячейка в диапазоне. Если диапазон — A2: A10, тогда первая ячейка в диапазоне — A2. *

n: количество появлений значений.

Давайте посмотрим на примере, чтобы прояснить ситуацию.

===

Пример: найти второе совпадение в Excel

Итак, у меня есть список имен в диапазоне Excel A2: A10. Я назвал этот диапазон именами. Теперь я хочу получить позицию второго появления «Рони» в именах.

0056

На изображении выше мы видим, что он находится на 7-й позиции в диапазоне A2: A10 (имена). Теперь нам нужно получить его позицию с помощью формулы Excel.

Примените приведенную выше общую формулу в C2, чтобы найти второе вхождение Рони в списке.

{=SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2)}

Введите его с помощью CTRL + SHIFT + ENTER ..

0057

И у нас есть ответ. Он показывает 7, что правильно. Если вы измените значение n на 3, это даст 8. Если вы измените значение n больше, чем вхождение значения в диапазоне, оно вернет ошибку #NUM.

===

Как это работает?

Что ж, это довольно просто. Давайте посмотрим каждую часть по очереди.

link: / tips-if-condition-in-excel [IF] (names = «Rony», link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) + 1 *)

В IF, names = «Rony» возвращает массив ИСТИНА и ЛОЖЬ. ИСТИНА, если ячейка в именах диапазонов (A2: A10) совпадает с «Рони». \ {ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ}.

Следующая ссылка: / lookup-and-reference-excel-row-function [ROW] (names) —link: / lookup-and-reference-excel-row-function [ROW] `(A2) +1: ссылка: / lookup-and-reference-excel-row-function [ROW] `(names *):

здесь функция СТРОКА возвращает номер строки каждой ячейки в именах.

\ {2; 3; 4; 5; 6; 7; 8; 9; 10}.

link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) * Затем мы вычитаем строку количество A2 из каждого значения в данном массиве. Это дает нам массив серийных номеров, начиная с 0.

\ {0; 1; 2; 3; 4; 5; 6; 7; 8}.

link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [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}.

Теперь у нас есть формула, решенная для link: / statistics-formulas-excel-small-function [SMALL] (\ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}, 2).

Теперь НАИМЕНЬШИЙ возвращает второе наименьшее значение в диапазоне, равное 7.

Как мы его используем?

Возникает вопрос: в чем польза получения необработанного индекса n-го совпадения? Было бы более полезно, если бы вы могли получить соответствующую информацию из n-го значения. Что ж, это тоже можно сделать. Если мы хотим получить значение из значения соседней ячейки n-го совпадения в именах диапазонов (A2: A10).

{=INDEX(B2:B10, SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2))}

Так что да, ребята, вот как вы можете получить n-е совпадение в диапазоне. Надеюсь, я достаточно объяснил. Если у вас есть какие-либо сомнения относительно этой статьи или любой другой темы, связанной с Excel / VBA, напишите в разделе комментариев ниже.

Статьи по теме:

link: / information-formulas-how-to-a-serial-row-number-in-excel [Как получить порядковый номер строки в Excel]

link: / lookup-formulas-vlookup-top-5-values-with-duplicate-values-using-index-match-in-excel [5 лучших значений Vlookup с повторяющимися значениями с использованием INDEX-MATCH в Excel]

link: / lookup-formulas-vlookup-multiple-values [VLOOKUP Multiple Values]

link: / lookup-formulas-use-index-and-match-to-lookup-value [Использовать ИНДЕКС и ПОИСКПОЗ для поиска значения]

link: / lookup-formulas-lookup-value-with-множественные критерии [Значение поиска с несколькими критериями]