☰

解決策1

tag-icon • tag-icon microsoft-excel worksheet-function
解決策1

値の行があり、空白セルを除いた最初の 3 つの値の平均を求めたいです。例:

  ABCDEFG
 12 空白 8 空白 7 9 4

最初の 3 つの空白でないセルの平均は 9 になるはずですが、これを計算できる数式が見つかりません。誰か助けてくれませんか?

答え1

でA2入力：

=IF(A1="",0,1)

そしてB2入力：

=IF(OR(B1="",SUM($A$2:A2)=3),0,1)

コピーします。次に別のセルに次のように入力します。

=SUMPRODUCT((1:1)*(2:2))/3

ここに画像の説明を入力してください

答え2

あなたが探しているのは平均関数。式によれば構文と使用法ExcelのAVERAGE関数の場合:

範囲またはセル参照引数にテキスト、論理値、または空のセルそれらの値は無視されますが、値がゼロのセルは含まれます。

数式は次=AVERAGE(A1:E1)を返します9:

ここに画像の説明を入力してください

答え3

解決策1

ブルートフォース: 必要な平均値は次のように計算できます。

=IF(COUNT($A1:$C1)=3, 平均($A1:$C1),
 IF(COUNT($A1:$D1)=3、平均($A1:$D1)、
 IF(COUNT($A1:$E1)=3, 平均($A1:$E1),
 IF(COUNT($A1:$F1)=3、平均($A1:$F1)、
 IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1), "おっと")))))

(1 行バージョン:)
 =IF(COUNT($A1:$C1)=3, AVERAGE($A1:$C1), IF(COUNT($A1:$D1)=3, AVERAGE($A1:$D1), IF(COUNT($A1:$E1)=3, AVERAGE($A1:$E1), IF(COUNT($A1:$F1)=3, AVERAGE($A1:$F1), IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1),"oops")))))

これは、3 つの数字を含む行の最短の初期サブセットを探し、その平均を計算します。これは、列の数が多い場合に扱いにくくなるだけでなく、IF()ネスト制限がかなり低い (と思われる) ため、実際にはすぐに失敗する可能性があります。

解決策 1.9

フィネス。

まだ完成していないので、これを「1.9」と呼んでいます。

SMALL(IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),""), {1,2,3})

（配列数式で使用）行1の最初の3つの数字の列番号を検索します。

IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),"")

部分は次のように評価されます

IF( {TRUE,FALSE,TRUE,FALSE,TRUE,TRUE,TRUE}, {1,2,3,4,5,6,7}, "" )

これは次のように要約される。

{ 1, "", 3, "", 5, 6, 7 }

そして、SMALL(…, {1,2,3})上記の式は を生成する{ 1, 3, 5 }。これは単純に言うと次のようになる。

=AVERAGE(INDEX($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))

または

=AVERAGE(OFFSET($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))

しかし、それらは (12) の値かA1エラー (#DIV/0!または#REF!) を返します。これをどうやって終わらせるかわかりません。

関連情報