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.

Чтобы получить сумму, мы можем использовать ту же функцию LARGE внутри 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) 

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