EXCEL不連続数字データにて指定数出現までの小計

質問

10,なし,20,30,なし,なし,40,50,60・・・
上記のようなデータがあったとして

数字データ4つ出現までの小計を求めたい場合
10+20+30+40=100と出力されるセルを作りたいのですが

データのあるセル範囲をA1からA10とした場合、どのような式を入力すれば良いのでしょうか？
教えてください

回答No.1

tom04 · ベストアンサー

こんばんは！
一例です。

表示したいセルに
=SUM(INDIRECT("A1:A"&SMALL(IF(A1:A10<>"",ROW(A1:A10)),4)))

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！

この画面からコピー＆ペーストする場合は
表示したいセルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。m(_ _)m

お礼

無事できました、ありがとうございます。
完璧な解答でしたので後にベストアンサーを選ばせていただきます。

同様にA1からJ1などもCOLUMN関数を使い、文字に変換すれば出来るかとやってみたところどうにも上手くいきませんでした
よろしければ教えていただけると幸いです

回答No.2

tom04

No.1です。

＞同様にA1からJ1などもCOLUMN関数を使い、文字に変換すれば出来るかとやってみたところどうにも上手くいきませんでした

とありましたので・・・

前回の数式はSUM関数で、仮にA7セルに4個目のデータがある場合
=SUM(A1:A7)
としたいために、「7」を取得するためにINDIRECT関数であのような数式にしました。

今度は列方向で同様にしたい！というコトですが、
列番号取得は前回の方法では無理ですね！
R1C1参照形式でINDIRECT関数が使用できるかどうか、検証していませんが・・・
他の方法としては
表示したいセルに
=SUM(INDIRECT("A1:"&INDEX({"A","B","C","D","E","F","G","H","I","J"},SMALL(IF(A1:J1<>"",COLUMN(A1:J1)),4))&1))
今回も配列数式になりますので、Shift＋Ctrl＋Enterで確定してください。

※　列数が少ない場合は上記数式でもなんとか関数でやってみよう！という気になりますが、
もっと列数が増えた場合はやはりVBAの方が簡単ですかねぇ～！

一例ですので試してみてください。
A2セルに表示するようにしています。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim j As Long, cnt As Long
Range("A2") = ""
For j = 1 To 10
On Error Resume Next '←まじない
If Cells(1, j) <> "" Then
cnt = cnt + 1
Range("A2") = Range("A2") + Cells(1, j)
If cnt = 4 Then
Exit For
End If
End If
Next j
If cnt < 4 Then
MsgBox "データ数は、" & cnt & "個です。"
End If
End Sub 'この行まで

尚、コードではデータ数（数値数）が4個に満たない場合はメッセージを表示するようにしています。
関数ではエラーになると思います。

この程度ですが参考になりますかね？m（_ _）m

お礼

マクロまで教えていただき感謝です。
数値の変更もしやすいし、大量データにも困らないのでマクロを使わせていただきます。
非常に参考になりました、ありがとうございました。