Excel関数で結合セルに合わせて合計を出す方法

質問

Excelの関数に関する質問です。

画像のような表を作成しており
B列の数値を、A列の区分ごとに合計し、C列に表示させる、
という関数がないか探しています。

今のところ、C列にSUM関数を手入力し、
表を作成しているのですが、
C列に関数を入力し、この手間を省けないかと考えています。

C列の表示位置はIF関数等で表示できると考えたのですが、
肝心の数値の合計の方法が分からず、困っています。

結合セルの行番号を求めれば解決するかと調べたのですが、
適切な方法を見つけることができませんでした。

区分の区切り位置や区分の数は毎回変化しますので、
オートフィルをかけてもエラーにならないような式を教えていただけますと、
大変助かります。

宜しくお願い致します。

回答No.8

kagakusuki

もし、御質問欄の添付画像の様に、「区分」欄に入力されている値が数値データである場合には、まず、各「区分」を入力するセルの書式設定の表示形式を[文字列]に変更してから、再度、区分を入力し直して下さい。
(もし、実際に入力されている区分の名称が、最初から文字列データである場合には上記の操作は不要です)
　次に、「区分が入力されている最も下の結合セルであるA15:A16」の1つ下のセルであるA17セルに、「総計」或いは空白スペース等の何らかの文字列を入力して下さい。(数値は不可)
　その上で、C2セルに次の関数を入力してから、C2セルをコピーして、C3～C16の範囲に貼り付けて下さい。

=IF($A2="","",SUM($B2:INDEX($B:$B,MATCH("*?",$A3:INDEX($A:$A,ROWS($A:$A)),0)+ROW()-1)))

回答No.7

bunjii

回答No.4の追加として次の式を提言します。

=IF(A2="","",SUM(B2:OFFSET(B2,IF(COUNT(A3:A$1000)>0,SUM(MATCH(MIN(A3:A$1000),A$1:A$1000,0),-ROW()-1),1000-ROW()-1),0)))
この式は作業用の列を省くためにOFFSET関数で次の集計範囲の先頭を検出してその手前の行までを集計する手法です。
最下の集計区分では次の区分が検出できませんので有効な行の最大値までを集計範囲としています。
上記の式では有効の最大行を1000としていますので実用に合う行数に変更して検証してください。

回答No.6

MackyNo1

>表の書き方が悪かったようで申し訳ないのですが、結合セル部分はA列のみの予定です。

A列だけが結合されているならC2セルに以下の式を入力すれば、右クリックでオートフィルして「書式なしコピー」するだけでご希望の表示になっていると思います。

IF(A2="","",=SUM(B2:INDEX(B:B,MIN(INDEX((A3:A9="")*1000+ROW(A3:A9)-1,))))）

回答No.5

keithin

マクロにちょっと問題があったので、次の通りに回答を修正します。失礼しました。

ご相談のような事は次のような関数を利用する事で出来ます。

手順：
ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

public function sumleft(a,b)
application.volatile
sumleft = application.sum(b.resize(a.mergearea.rows.count, 1))
end function

Ｃ列の空セルをまとめて選び、
=sumlfet(A2,B2)
と記入してコントロールキーを押しながらＥｎｔｅｒで一斉入力する。

関数の使い方：
=sumleft(Ａ列の計算したい結合セルの先頭セル，対応するＢ列の先頭セル）

回答No.4

bunjii

>区分の区切り位置や区分の数は毎回変化しますので、オートフィルをかけてもエラーにならないような式を教えていただけますと、大変助かります。
区分集計用の指標となる値を作業用の列に作成すれば目的に合う集計ができます。

作業用にD列を使う場合
D2=IF(A2="",D1,A2)

区分ごとの合計
C2=IF(A2="","",SUMIF(D2:D16,A2,B2:B16))

D2を下へオートフィルでコピーします。
C2も同様にオートフィルでコピーします。
A列の有効範囲をコピーでクリップボードへ取り込み、C列へ貼り付けオプションの書式のみを指定すれば結合状態と罫線がA列と同じになります。

回答No.3

MackyNo1

例示のレイアウトならオートフィルの操作では無理ですが、以下のようなコピー貼り付けの操作で一括して結合セルに合計値を表示させることができます。

どこかの空白列の2行目に以下の式を入力し、右クリック「コピー」、C列の結合セル範囲全体を選択し、右クリック「形式を選択して貼り付け」で「数式」にチェックを入れて「OK」します。

=SUM($B2:INDEX($B:$B,MIN(INDEX(($A3:$A9="")*1000+ROW($A3:$A9)-1,))))

ただし上記の数式は結合セルが最大7個までですので、それよりも大きいときは適宜$A3:$A9の範囲を大きくしてください。

お礼

ご回答ありがとうございます。
表の書き方が悪かったようで申し訳ないのですが、結合セル部分はA列のみの予定です。
INDEX関数は苦手としておりましたので、大変勉強になりました。
皆さんのご回答を元に、自分でも試行錯誤してみようと思います。

回答No.2

suo2k

D列とか使っても大丈夫ですか？

まずD2に

=IF(A2<>"",B2,D1+B2)

と入力し、下へオートフィルします。

次にC2に

=IF(A3<>"",D2,"")

と入力してこれも下へ。

そいでもってA列の最後の下（画像ではA17）に
なんでもいいので何か入れてください。

A17とD列は表示が気になるなら文字色を白にするとか非表示にするとかで対応してください。
あと合計の数値が出てくる位置が若干画像とは異なりますが・・・いいかな？

お礼

ご回答ありがとうございます。
表以外の部分にデータがあっても大丈夫ですので、教えて頂いた方法で何とかできそうです！
質問させて頂いたのはわからなかった部分だけで、実際にはその他の計算もして表示させなければならないのですが、やっとゴールが見えてきました。

回答No.1

keithin

そういう計算ができる関数はありません。

手順：
ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

public function sumleft()
with application.caller
sumleft = application.sum(.offset(0, -1).resize(.mergearea.rows.count, 1))
end with
end function

Ｃ列の各セルに
=sumleft()
と記入する。

＃Ｃ列の空セルをまとめて選び、
=sumlfet()
と記入してコントロールキーを押しながらＥｎｔｅｒで一斉入力する。

＞オートフィルをかけてもエラーにならない

関数以前の問題で、数の異なるバラバラしたセル結合のセル範囲にオートフィルすること自体、出来ません。

お礼

ご回答ありがとうございます。
VBAの使用も考えましたが、利用者全員が理解しやすいようにしたかったのです。関数で利用出来るよう表の形式を変えられないか検討してみます。