Получение первых трех строк, где значение ячейки больше

Question

Борьба с получением строки данных на основе значения в строке. Мои данные структурированы так:

ID  Name   Score  Owner
1   Item1  94%    MD
2   Item2  24%    OM
3   Item3  55%    TM
4   Item4  76%    MD
5   Item5  12%    OM

Чего я хочу добиться, так это иметь возможность вытащить Имя и Владельца, где Счет превышает 65% (или любое другое число).

В настоящее время я использую ссылки на столбцы, моя таблица называется "table2", например. Table2 [Имя] и так далее.

Пробовал использовать INDEX и MATCH, но, похоже, не хочет работать для меня, также пробовал VLOOKUP, но опять-таки, похоже, не работает.

Некоторые формулы, которые я пробовал и корректировал, пытаясь достичь желаемого:

=INDEX(Table2,MATCH(0.32,Table2[Score],-1),0)

=IF(K5="","",INDEX(Table2[Name],Table2[Score]=L5))
$K = 1 to 3 (5=1 in the above), L5 = the score I am looking for.

Любое руководство было бы действительно полезным, проблема, которую я в основном обнаружил с примерами из Интернета, состоит в том, что они используют диапазоны «$ A $ 1:$ A $ 19» и т.д., И когда я заменяю их на именованный диапазон, формула не работает.

Answer 1

Как это устроено:

• Напишите эту формулу массива в ячейке A10 , завершите с помощью Ctrl+Shift+Enter , заполните ее вправо, затем вниз.

    {=IFERROR(INDEX($B$2:$D$6, SMALL(IF((INDEX($B$2:$D$6, , $B$8)>$A$8), MATCH(ROW($B$2:$D$6), ROW($B$2:$D$6)), ""), ROWS(B2:$B$2)), COLUMNS($A$1:A1)),"")}
  

Замечания:

• Это извлечет только 2 записи, так как имеет балл более 65%.

• Ячейка A8 имеет критерии 65%, а B8 имеет позицию столбца 2 (столбец Score).

• При необходимости измените ссылки на ячейки в формуле.

Отредактировано:

INDEX($B$2:$D$6, , $B$8) становится

INDEX($B$2:$D$6, , 2)

и возвращает C2:C6 .

C2:C6 сравнивается с $ A $ 8 (что в данном примере составляет 65%).

MATCH(ROW($B$2:$D$6), ROW($B$2:$D$6)), "")

становится ,, {1;2;3;4;5}, "")

В сочетании с тестом IF на C2:C6 для >$A$8 возвращает строки,

{1;"";"";"";4;""} из Name Column .

Наконец, перетащите Формулу вправо, затем Вниз, чтобы завершить извлечение данных.

Answer 2

Кажется, я ответил на свой собственный вопрос, после того как я написал свой вопрос, я оглянулся на формулу, полученную из https://people.highline.edu/mgirvin/excelisfun.htm, в частности EMT967.xlsx, которая поясняется здесь https://www.youtube.com/watch?v=rKDI-kdBsjY и другой от http://www.get-digital-help.com/2009/09/28/extract-all-rows-from-a-range- критерий соответствия в одном столбце в excel/. Используя эти 2 примера, мне удалось создать требуемую формулу, используя комбинацию INDEX и MATCH.

Для ясности вот что у меня сейчас есть в определенных клетках:

W4

=LARGE(Table2[Score],3)

W5

3

К5 (скопировано 3 ячейки к К8)

=IF(ROWS(K$5:K5)>$W$5,"",ROWS(K$5:K5))

L5 (скопировано 3 ячейки до L8)

=IF(K5="","",LARGE(Table2[Score],K5))

M5 (скопировано 3 ячейки в M8)

=IF(K5="","",INDEX(Table2[Name],MATCH(L5,Table2[Score],0)))

Если я изменю ячейку W5 на что-то другое, например, на 1, лист покажет только самый высокий балл из моего набора данных, 2 покажет 2 и т.д. Если вы хотите, чтобы более 3 копировали K5, L5 и M5 на несколько ячеек, а затем измените число в W5, чтобы заполнить количество строк, в которых находятся формулы.

Я надеюсь, что это поможет кому-то еще, я смущенно провел около 6 часов, пытаясь выучить это и заставить его работать! Но я получил его на работу, так что это что-то.

Обновление: это "работает", но не может обрабатывать уникальные значения, поскольку возвращает только имя, соответствующее заданному баллу.

Обновление 2: исправлено с помощью одного из примеров, представленных ранее. Cell M5 теперь содержит:

=IF(K5="","",INDEX(Table2[Name],AGGREGATE(15,6,(ROW(Table2[Score])

-CELL("row",Table2[Score])+1)/(Table2[Score]=L5),COUNTIF($L$5:L5,L5))))

Теперь это похоже на возможность обрабатывать повторяющиеся значения.