Excel関数、VBA

質問

A列に数字を入力するとB列に合計が出るようにしたい。

但し、条件があります。

数字の入力範囲はA1からA20まで。

B列の合計はA列の数字が連続した部分の最後に表示。

例
A列 B列
10
10
10 30

20 20

10
30 40

上記のように数字の入力は1行だけの時もあり、5行の時もあるので次の空欄までの合計をB列に表示したい。

VBでも関数でも構いませんので、どなたか詳しい方教えて下さい。

回答No.3

mt2008 · ベストアンサー

こんな感じでも良いんじゃないでしょうか。
1行目は項目名等が入るとして、B2に↓を入れて下方向にコピー
=IF(OR(A2="",A3<>""),"",SUM(A$1:A2)-SUM(B$1:B1))

お礼

mt2008さん、試してみました。

うまくできました！

関数で出来そうな気がしてたのですが、実際にやってみるとなかなかうまくできなく、VB勉強中の私にはまだVBでは作れないので、よい教材になると思い質問してみました。

同僚が仕事で１から１０まで時間をかけて入力してたので、これでかなり仕事が楽になります。

私もmt2008さんのおかげでよい勉強になりました。

シンプルで無駄がなく美しい関数ですね！

早速使わせていただきます。

mt2008さん、ありがとうございました。

回答No.4

eden3616

なるほど。No3 mt2008さんの回答がスマートですね。

1行目にも対応できるようにB1からオートフィルするとして

=IF(AND(ROW()=1,A1<>"",A2=""),A1,IF(AND(A1<>"",A2=""),SUM(INDIRECT("A1:A"&ROW()))-SUM(INDIRECT("B1:B"&ROW()-1)),""))

て感じでしょうかね。

お礼

eden3616さん、丁寧に教えていただきありがとうございます。

私にはちょっと難しいようです。

参考にさせていただき、勉強に役立てたいと思います。

eden3616さん、ありがとうございました。

回答No.2

eden3616

VBAの手順で訂正です（箇条番号の付け間違えで、内容は同じです）

(1)標準モジュールに以下のコードを貼付
(2)B1に「=kei(A1)」を入力
(3)B1の数式をA列の最終行までオートフィル

関数の処理に対しての補足です

「=kei(A1)」のA1は対象のセルを指定してください。
対象のセルより上側に連続する値の合計を返します。
対象のセルが1行目または、対象の一つ上のセルが空欄であれば対象のセルの値を合計として返します。

関数での処理、VBAでの処理どちらもA列の最終行は空白である必要があります。
（回答No1添付画像ではA10及びA21のセルが空白）

回答No.1

eden3616

とりあえず二通り。

■VBAのユーザー定義関数の場合

(3)標準モジュールに以下のコードを貼付
(1)B1に「=kei(A1)」を入力
(2)B1の数式をA列の最終行までオートフィル

Function kei(対象のセル As Range)
Dim tar As Range
Set tar = 対象のセル
If Not (Len(tar) > 0 And Len(tar.Offset(1, 0)) = 0) Then kei = "": Exit Function
If tar.Row = 1 Or tar = "" Then kei = tar.Value: Exit Function
If tar.Offset(-1, 0) = "" Then
kei = tar.Value
Else
kei = WorksheetFunction.Sum(Range(tar.End(xlUp), tar))
End If
End Function

■作業列を使用した関数の場合

(1)B1のセルに「=IF(AND(A1<>"",A2=""),SUMIF(C:C,C1,A:A),"")」
(2)C1のセルに「=IF(ROW()=1,0,IF(A1<>"",OFFSET(C1,-1,0),OFFSET(C1,-1,0)+1))」
(3)B1とC1の数式をA列の最終行までオートフィル