Excel: получение первых пяти значений из строки в список через запятую

Question

+-----+-----+-----+-----+-----+-----+-----+-----+
|     | A   | B   | C   | D   | E   | F   | G   |
+-----+-----+-----+-----+-----+-----+-----+-----+
| 1   | 5   | 6   | 5   | 9   | 4   | 8   | 3   |
+-----+-----+-----+-----+-----+-----+-----+-----+

Учитывая вышеупомянутый лист. Я ищу способ в Excel/Calc, чтобы получить 5 лучших чисел.

Пока я могу думать только о том, чтобы сделать это, используя 5 различных БОЛЬШИХ функций.

=LARGE(A1:G1, 1)
=LARGE(A1:G1, 2)
=LARGE(A1:G1, 3)
=LARGE(A1:G1, 4)
=LARGE(A1:G1, 5)

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

9, 8, 6, 5, 5

в одну клетку?

Другой вопрос, вместо того, чтобы распечатывать их, я также хотел бы СУММИТЬ числа в другой ячейке.

Answer 1

Вы можете использовать TEXTJOIN() в качестве формулы массива.

=TEXTJOIN(",",TRUE,LARGE(A1:G1,ROW($1:$5)))

ROW($1:$5) при вводе в виде массива возвращает массив {1,2,3,4,5} возвращая пять самых больших чисел в TEXTJOIN.

Вы заставляете массив, подтверждая формулу с помощью Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования.

TEXTJOIN доступен в Office 365 Excel.

Чтобы получить сумму, мы можем использовать тот же самый БОЛЬШОЙ внутри SUMPRODUCT:

=SUMPRODUCT(LARGE(A1:G1,ROW($1:$5)))

Для старых версий вы застряли с:

=LARGE(A1:G1, 1) & "," & LARGE(A1:G1, 2) & "," & LARGE(A1:G1, 3) & "," & LARGE(A1:G1, 4) & "," & LARGE(A1:G1, 5) 

Или вспомогательные клетки.