Excel: 行の上位 5 つの値をコンマ区切りのリストに取得する

Question

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

上記のシートを考慮して、Excel/Calc で上位 5 つの数字を取得する方法を探しています。

今のところ私が思いつく唯一の方法は、5つの異なるLARGE関数を使うことです。

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

そうすれば、上位 5 つの数字が返されます。これを単一の数式で実行して、次の結果を返す方法があるかどうか疑問に思っています。

9, 8, 6, 5, 5

単一のセルに？

もう 1 つの質問ですが、印刷する代わりに、別のセルの数字を合計したいとも思っています。

Answer 1

TEXTJOIN() を配列数式として使用できます。

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

配列として入力すると、TEXTJOIN に上位 5 つの最大の数値を返すROW($1:$5)配列が返されます。{1,2,3,4,5}

編集モードを終了するときに、Enter キーではなく Ctrl + Shift + Enter キーで数式を確定すると、配列が強制されます。

TEXTJOIN は Office 365 Excel で利用できます。

合計を取得するには、SUMPRODUCT 内で同じ LARGE を使用できます。

=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) 

あるいはヘルパー細胞。