エクセル間数式で未記入判定の総合計

質問

始めまして、よろしくお願いします。
このようなシートがあります。

　　　　　Ａ　　Ｂ　　Ｃ　　
１　　　　赤　　青　　黄　　
２　
３　　　
４　　　　１　　１　 　14　
５　　　　９　　　　 　　　
６　　　　　　　　 　
７　　　　４　　　　 　
８　　　　１　　-1　　 ４
９　　　　３　　　　　
１０　　　２　　１　　　・
１１　　　・　　・　　　 ・
１２　　　・　　・　　　 ・

　４行に（A４に１入力）データーが入力され、データーは下へスライドコピーされていきます。B列に数字が入る場合（ここでは４行、８行、１０行になります）に、B列の無記入行のA列の計（B5,B6,B7,が無記入なので合計されるA列の値はデーター入力されるA4とB列無記入にあたるA5,A6,A7の計です）をC列に表示されるようなC４に入る関数式を教えていただきたく、よろしくお願いします。

回答No.1

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

C1000セルまで入力し、A1001:C1001セルには何も入力しないとして
そのまま数式に表わすと
C1000セルに
=IF(B1000="","",
SUMIF(B1001:B$1001,"",A1001:A$1001)+A1000
+SUMIF(B1001:B$1001,"<>",A1001:A$1001)-SUM(C1001:C$1001))
上へオートフィル
でよくよく数式を眺めると
C1000セルに
=IF(B1000="","",SUM(A1000:A$1000)-SUM(C1001:C$1001))
上へオートフィル

お礼

回答ありがとうございます。数式を見て、なるほどこういう導き方が確かに有るなと目から鱗。大変助かりました、ありがとうございます。

回答No.6

tom04

こんにちは！
ご希望の関数でなく、VBAでの一例です。

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

Sub test() 'この行から
Dim i, j, k As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 4 Step -1
k = i
Do Until Cells(i, 2) <> ""
i = i - 1
Loop
j = i
Cells(i, 3) = WorksheetFunction.Sum(Range(Cells(j, 1), Cells(k, 1)))
Next i
End Sub 'この行まで

尚、一旦マクロを実行すると元に戻せませんので別Sheetにコピー＆ペーストしてマクロを試してみてください。
m(__)m

お礼

回答ありがとうございます。関数式ではありませんが、VBAの勉強用として参考にさせていただきたいと思います。

回答No.5

KURUMITO

回答No２です。
作業列を使わないでとのことですので次の式をC２セルに入力してからCtr+Shit+Enterで式を確定してから下方にオートフィルドラッグします。

=IF(B2="","",SUM(A2:INDIRECT("A"&IF(COUNT(B$2:B2)=COUNT(B:B),MATCH(10^10,A:A),SMALL(IF(SUBTOTAL(103,INDIRECT("B"&ROW(B$1:B$1000))),ROW(B$1:B$1000)),COUNT(B$1:B1)+2)-1))))

お礼

補足回答ありがとうございます。こちらの入力間違いか、エラーが出てしまいました。参考にさせていただきたいと思います。

回答No.4

popuplt

作業列なしの一例です。

[C4]=IF(B4="","",SUM(A4:INDEX(A4:A12,MATCH(,INDEX(0/(B5:B13<>""),),))))

配列数式なので[CTRL]+[SHIFT]+[ENTER]
下へドラッグ。

B列に数値があれば、その前を合計するという考え方ですので、B列の最後に数値が無ければその前の合計はエラーになります。

お礼

回答ありがとうございます。うまくできました。大変助かりました。ありがとうございます。

回答No.3

imogasi

関数でやるのは難しい課題だ。出来ても相当長い難しい式になると予想される（初心者には意味がわからない式だろう。）
位置が不定であることが問題を複雑にする。
ーー
其れで、作業列を使い、やや？やさしくしてみる（imogasi方式）
例データ
Ａ列からＤ列、Ｄ列は作業列
赤 青 黄 作業列

1 1 14 1
9 1
1
4 1
1 -1 4 2
3 2
2 1 3 3
6 3
Ｄ４セルに　=IF(B4="",D3,MAX($D$3:D3)+1)　といれて下方向に式複写
結果上記Ｄ列。
意味は、Ｂ列で、データ出現までの行を、ひと塊として、上行から連番を振ったもの。
ーー
Ｄ４に　=IF(D4<>D3,SUMIF(D4:D100,D4,A4:A100),"")
意味は、Ｄ列で前の行と数字が変わったときだけ、Ｄ列の数字でＡ列の数をＳＵＭＩＦする。
下方向に式を複写
結果
赤 青 黄 作業列 合計

1 1 14 1 14
9 1
1
4 1
1 -1 4 2 4
3 2
2 1 3 3 8
6 3
ーーーーーーー
質問の文章表現がわかりにくい
「Ｂ列で（次に）数字が現れる手前行までについて、Ａ列の数字を足して、先頭行（Ｂ列に数字のある行）のＣ列に出したい」などどうかな。

お礼

回答ありがとうございます。当方生粋の日本人ですが、いまだに母国語の日本語を上手に使いこなせていないようです。日本語は難しい言語ですね。回答は参考にさせていただきたいと思います。

回答No.2

KURUMITO

２行目から下方にデータが入力されるとして、作業列を作って対応するのがよいでしょう。E２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",MAX(D$1:D1)+1)

C列にはお求めのデータを表示させるとしてC２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",SUM(A2:INDIRECT("A"&IF(COUNTIF(D:D,D2+1)=0,MATCH(10^10,A:A),MATCH(D2+1,D:D,0)-1))))

ここでMATCH(１０^１０,A:A)はA列で数値のデータが入力されている最終の行番号を求めるものです。

お礼

回答ありがとうございます。うまく導き出せましたが、できれば作業列を作らないやり方を考えております。参考にさせて頂きたいと思います。