Excel - Как вернуть «значение измерения» из набора данных с N-м наибольшим «значением метрики»

Question

Я ищу способ вернуть определенный текст, если их соответствующее значение, найденное в той же строке, является наибольшим или вторым по величине. Я нашел решения онлайн, которые отвечают на некоторые части вопроса, но не на все элементы.

Например, формула max/if и некоторые комбинации vlookup будут работать, если бы не тот факт, что в наборе данных есть дублированный текст в строках, поэтому значения не суммируются должным образом.

Мой набор данных выглядит следующим образом:

По сути, я пытаюсь получить конкретные значения без поворота; Я хочу формулу для:

• Сканируйте размер 1 для фразы "Первый" (поэтому "Второй" опущен)
• Суммируйте все числа "Значение" в измерении 2 иерархии. Это будет означать, что все значения суммируются в метках "Тест 1" или "Тест 2" измерения 2
• Возвращаем метку с наибольшим значением (обратите внимание, что я после текста, поэтому либо "Тест 1", либо "Тест 2" - в зависимости от того, какое значение больше, чем значение)

Это значение, которое будет возвращено (потому что "Тест 1" имеет наибольшее значение из "Первой" группы):

Answer 1

Вставьте следующее:

=INDEX( $D$1:$D$100, MATCH( MAX( ($C$1:$C$100=$A$1)*SUMIFS( $F$1:$F$100, $C$1:$C$100, $C$1:$C$100, $D$1:$D$100, $D$1:$D$100)), ($C$1:$C$100=$A$1)*SUMIFS( $F$1:$F$100, $C$1:$C$100, $C$1:$C$100, $D$1:$D$100, $D$1:$D$100), 0))

Сделайте следующие правки:
Если MAX уже рассчитан (пример содержит Sum of Value), замените аргумент MATCH lookup_value,
MAX( ($C$1:$C$100=$A$1)*SUMIFS( $F$1:$F$100, $C$1:$C$100, $C$1:$C$100, $D$1:$D$100, $D$1:$D$100)) ,
с адресом этой ячейки (например, адрес ячейки 79180 в образце).
$ C $ 1:$ C $ 100 Диапазон данных размера 1 .
$ D $ 1:$ D $ 100 Диапазон данных измерения 2 .
$ F $ 1:$ F $ 100 Диапазон Значение данных.
$ A $ 1 Адрес ячейки, где вычисляется значение Измерения 1 (например, ячейка заголовка строки таблицы результатов, содержащая "Первый").

Нажмите Ctrl - Shift - Enter, чтобы сделать формулу массива.

Чтобы получить второе по величине измерение 2, используйте ту же формулу и в функции MAX обнулите самые высокие значения измерения 2, используя ($D$1:$D$100<>[cell_address_of_above_pasted_formula])* (также добавьте это к обнулению в второй аргумент INDEX чтобы получить другой по величине, когда есть связь). Итак ($C$1:$C$100=$A$1)*SUMIFS... становится ($D$1:$D$100<>[cell_address_of_above_pasted_formula])*($C$1:$C$100=$A$1)*SUMIFS... Как и раньше, если было вычислено второе наибольшее значение, вместо этого редактирования используйте замену MAX упомянутую ранее, но используйте ссылку на ячейку со вторым по величине значением.