Отримати п-е збіг за допомогою INDEX

Щоб знайти n-е збіг значення з діапазону та отримати відповідні йому дані, можна використовувати формулу на основі ІНДЕКС, ROW, МАЛИЙ і IF функції.

Як отримати інформацію, пов’язану з n-м збігом значення з INDEX?

Щоб отримати інформація про 3-й матч Усейна Болта у таблиці, як показано вище, ви можете використовувати разом функції ROW і IF, щоб отримати масив, який складається з номерів рядків усіх значень Усейна Болта. Потім скористайтеся функцією SAMLL, щоб отримати номер рядка n-го відповідного значення, яке ви вказали. Нарешті, передайте результат у функцію INDEX, щоб отримати відповідну інформацію в тому ж рядку.

Родовий синтаксис

=INDEX(return_range,SMALL(IF(lookup_array=lookup_value,ROW(lookup_array-ROW(INDEX(lookup_array,1,1))+1),n))

√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у .

Щоб отримати інформація про 3-й матч Усейна Болта, скопіюйте або введіть формули нижче в клітинки G8 і G9 і натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у щоб отримати результати:

Рік (осередок G8)
= ІНДЕКС (B6: B12,МАЛИЙ(ЯКЩО(C6: C12=G5,РОВ(C6: C12-РОВ(ІНДЕКС(C6: C12,1,1))+1),G6))
Час (осередок G9)
= ІНДЕКС (D6: D12,МАЛИЙ(ЯКЩО(C6: C12=G5,РОВ(C6: C12-РОВ(ІНДЕКС(C6: C12,1,1))+1),G6))

Пояснення формули

Тут ми використовуємо формулу нижче як приклад:

=INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))

• РЯД (C6:C12): Функція ROW повертає номери рядків кожної клітинки в діапазоні C6: C12 в такому масиві: {6;7;8;9;10;11;12}.
• РЯДК(ІНДЕКС(C6:C12,1,1)): Функції INDEX і ROW повертають номер першого рядка діапазону пошуку C6: C12 на цьому робочому аркуші Excel, який є 6.
• ЯКЩО(C6:C12=G5,РЯД (C6:C12)-РЯДК(ІНДЕКС(C6:C12,1,1))+ 1) = ЯКЩО(C6:C12=G5,{6;7;8;9;10;11;12}-6+ 1): Функція IF перевіряє, чи є кожне значення в діапазоні C6: C12 відповідає значенню в комірці G5, Усейн Болт. Якщо так, функція поверне результат «відповідний номер рядка-6+1»; В іншому випадку поверніть FALSE. Отже, результати будуть у вигляді такого масиву: {ХИБНІСТЬ;2;3;4;ХИБНІСТЬ;ХИБНІСТЬ;ХИБНІСТЬ}.
• Маленький(ЯКЩО(C6:C12=G5,РЯД (C6:C12)-РЯДК(ІНДЕКС(C6:C12,1,1))+ 1),G6) = Маленький({ХИБНІСТЬ;2;3;4;ХИБНІСТЬ;ХИБНІСТЬ;ХИБНІСТЬ},G6): Фрагмент повертає 3rd (значення в комірці G6) найменше значення з масиву, яке є 4.
• ІНДЕКС(B6:B12,Маленький(ЯКЩО(C6:C12=G5,РЯД (C6:C12)-РЯДК(ІНДЕКС(C6:C12,1,1))+ 1),G6)) = ІНДЕКС(B6:B12,4) Функція INDEX повертає 4го значення в діапазоні року B6: B12, Яка є Пекін 2008.

Супутні функції

Пов’язані формули