Создание рейтинга в Excel путем сопоставления значений в несовместимых ячейках

Question

У меня есть файл Excel, который выглядит следующим образом:

   A  B  C  D  E        F  G  H  I  J      K  L  M  N  O  
1  Alice                Bob                Charles
2  10 35 54 9 21        71 15 43 75 98     13 35 66 80 20

где каждая группа из 5 столбцов содержит данные о конкретном человеке.

Я хочу построить пять рейтингов на основе значений в 5 столбцах каждой группы. Например, ранжирование столбцов «A, F, K» должно быть «Боб, Чарльз, Алиса» (потому что значение А у Боба равно 71, значение А у Чарльза равно 13, а значение А равно Алисе 10). Точно так же рейтинг «B, G, L» будет «Алиса, Чарльз, Боб» или «Чарльз, Алиса, Боб» (потому что есть связь: 35, 35, 15).

Я полагаю, что я должен использовать смесь INDEX/MATCH, (V)LOOKUP и LARGE, но не знаю, с чего начать. Самое дальнее, что я получил, это что-то вроде

LARGE((A2, F2, K2), 1)
LARGE((A2, F2, K2), 2)
LARGE((A2, F2, K2), 3)

Это (должно) вывести первое, второе и третье по величине значение для диапазона «A2, F2, K2», но я не знаю, как оттуда получить имя человека, связанного с этим значением. У меня есть некоторые проблемы с обобщением примеров, которые я нашел с помощью INDEX/MATCH и функций поиска, в эту нетипичную структуру данных (в группах по пять столбцов).

РЕДАКТИРОВАТЬ: имена (Алиса, Боб, Чарльз) на объединенные ячейки.

Answer 1

Вы можете написать Данные по-другому, чтобы использовать Ранг:

В E2 =RANK($B2,$B2:$D2,0)
В F2 =RANK($C2,$B2:$D2,0)
В G2 =RANK($D2,$B2:$D2,0)

И вы можете перетащить каждую формулу в одном столбце

Answer 2

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

Смотрите скриншот ниже.

Матрица выровнена в диапазоне G7:J12.

Формула в H8

=IF(CHOOSE(1,$A$2,$F$2,$K$2)=LARGE(($A$2,$F$2,$K$2),1),$A$1,IF(CHOOSE(2,$A$2,$F$2,$K$2)=LARGE(($A$2,$F$2,$K$2),1),$F$1,$K$1))

H9

=IF(CHOOSE(1,$B$2,$G$2,$L$2)=LARGE(($B$2,$G$2,$L$2),1),$A$1,IF(CHOOSE(2,$B$2,$G$2,$L$2)=LARGE(($B$2,$G$2,$L$2),1),$F$1,$K$1))

H10

=IF(CHOOSE(1,$C$2,$H$2,$M$2)=LARGE(($C$2,$H$2,$M$2),1),$A$1,IF(CHOOSE(2,$C$2,$H$2,$M$2)=LARGE(($C$2,$H$2,$M$2),1),$F$1,$K$1))

H11

=IF(CHOOSE(1,$D$2,$I$2,$N$2)=LARGE(($D$2,$I$2,$N$2),1),$A$1,IF(CHOOSE(2,$D$2,$I$2,$N$2)=LARGE(($D$2,$I$2,$N$2),1),$F$1,$K$1))

H12

=IF(CHOOSE(1,$E$2,$J$2,$O$2)=LARGE(($E$2,$J$2,$O$2),1),$A$1,IF(CHOOSE(2,$E$2,$J$2,$O$2)=LARGE(($E$2,$J$2,$O$2),1),$F$1,$K$1))

Теперь перетащите соответствующие формулы вправо и внесите небольшие изменения в функцию LARGE. Для второго столбца, т. Е. Col I, второй параметр задайте как 2 как для LARGE, так и для третьего столбца, т. Е. Col J, укажите 3.

Честно откажитесь от этого решения, если у вас есть повторяющиеся строки ниже, и вам нужно сделать это для каждого набора или строк в нем.