Excel2007簡単に合計の数式を入力したいです

質問

どう検索していいかも分からず、こちらの質問にも似たようなのがないようでしたので
質問させて頂きました。

右に３つ、下にたくさん（変動します）表が並んでいるんですが、
その途中に、合計の式を挿入したいと思っています。
ところが、項目も変動するもので、どう取り組めばいいか、見当もつきません。
一応、VBAで出来ればと思っています。
（VBAはまだまだ勉強中の身です）

具体的に言うと、

　　　　A　　　｜　B　　　　　　｜　　C　　　 　｜　　　　　D　 　｜　　　E　　　｜F　　　　
1地名　　　　｜個数　　　　　｜地名　　　 　｜　　個数　　　 ｜　地名　　　｜個数
2登　別　　　｜　　　　　　　　｜湯の川　　 　｜　　　　　　　　｜登別　　　　｜
3湯の川　　　｜　　　　　　　　｜北海道合計｜（SUM)　 　　 ｜湯の川　　　｜
4北海道合計｜(SUM)　　　　｜白骨　　　 　｜　　　　　　　　 ｜北海道合計｜（SUM)
5有馬　　　　｜　　　　　　　　｜野沢　　　 　｜　　　　　　　　 ｜有馬　　　 　｜
6皆生　　　　｜　　　　　　　　｜甲信越合計｜（SUM)　　　　｜皆生　　　　　｜
7近畿合計 　｜(SUM)　　　　｜総合計　　 ｜（SUBTOTAL)｜近畿合計　　｜（SUM)　　
8総合計　　　｜(SUBTOTAL)｜　　　　　　 ｜　　　　　　　　 ｜総合計　　 　｜（SUBTOTAL)

・
・
・
101地名　　　　｜　　　個数　　｜地名　　　　｜　　個数　　　　　｜　
102登　別　　　｜　　　　　　　　｜湯の川　　　｜　　　　　　　　　｜
103湯の川　　　｜　　　 　　　　｜北海道合計｜（SUM)　　　　｜
104北海道合計｜(SUM)　　　　｜白骨　　　　｜　　　　　　　　｜
105有馬　　　　｜　　　　　　　　｜野沢　　　　｜　　　　　　　　｜
106皆生　　　　｜　　　　　　　　｜甲信越合計｜（SUM)　　　　｜
107近畿合計　 ｜(SUM)　　　　｜総合計　 　｜（SUBTOTAL)｜　　
108総合計　　　｜(SUBTOTAL)｜　　　　　 　 ｜　　　　　　　　　｜

見辛くて申し訳ありませんが、こんな感じです。
基本的に、横に３つ並んでいますが、一番下の表だけ横に２つや１つになったりします。
表に通し番号はありませんが、一応順に並んでいるので、このままの状態で出来れば、と思います。

作業開始時、B・D・F列は、見出しの「個数」を除いて空白です。
数量は後から入力する仕様で、空白のままで、
地域合計・総合計にのみ数式を入力して完成させたいです。

SUMの集計範囲も、合計セルの位置も毎回上下に変動するので、
頭を抱えています…。
このSUMやSUBTOTALを、なんとか簡単に入力できる方法をご教授下さい。
VBA以外でも構いません。

よろしくお願いいたします！

回答No.9

mar00

すべてSUBTOTAL関数になってしまいますが
SUBTOTAL関数が入力されているセルは計算対象外とされ無視されるのを利用しています。
Sub Macro2()
For i = 2 To 6 Step 2
myRow1 = 0
myRow2 = 0
For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If IsEmpty(Cells(j - 1, i)) Then Exit For
myRow2 = myRow2 - 1
If Right(Cells(j, i - 1), 2) <> "合計" And Cells(j, i - 1) <> "総合計" Then
myRow1 = myRow1 - 1
Else
Cells(j, i).FormulaR1C1 = "=SUBTOTAL(9,R[" & myRow1 & "]C:R[-1]C)"
myRow1 = 0
End If

If Cells(j, i - 1) = "総合計" Then
Cells(j, i).FormulaR1C1 = "=SUBTOTAL(9,R[" & myRow2 & "]C:R[-1]C)"
End If
Next j
Next i
End Sub

お礼

回答ありがとうございます。

まだまだ解決には至っていないのですが、
私自身、これに向き合う時間が取れなくなってきたので、
ひとまず終了とさせていただきます。

また時間がとれるようになれば、改めて質問するかもしれません。
もし、目にとまった際には、お力添えいただければ幸いです。

何度もありがとうございました！！

補足

B4とD3にSUBTOTALは入力されるのですが、そこでマクロ終了してしまいます(>_<)
残念です…。

回答No.8

mu2011

NO1です。
計数範囲が相違している理由です。
数式範囲の確定は「地域合計の範囲は一つ前の地域合計行＋１から今回の地域合計行－１」
までとしていますので２つ目以降の最初の地域合計範囲は総合計行から見出し行も含めて
しまいます。（総合計も同じ）

これは事前に気が付いておりましたが、総合計も見出し行も計数範囲に入りますが、
SUBTOTAL関数はこれらの値を無視できるのでこのまま回答とした次第です。

お礼

回答ありがとうございます。

まだまだ解決には至っていないのですが、
私自身、これに向き合う時間が取れなくなってきたので、
ひとまず終了とさせていただきます。

また時間がとれるようになれば、改めて質問するかもしれません。
もし、目にとまった際には、お力添えいただければ幸いです。

何度もありがとうございました！！

補足

上から一番下の表までが一つの表であれば問題ないのですが、
ご教授いただいたVBAでは、
一番上の表の1行目から、現在の表までの総合計になってしまうのです。
「地域合計の範囲は一つ前の地域合計行＋１から今回の地域合計行－１」にはなっていないようで…

質問分の例でいうと、B104（地域合計）のセルに、=SUBTOTAL(9,$B$2:$B$103)と入力されてしまいます。
総合計も、そのようになってしまいます。

回答No.7

mar00

>8行目で型が一致しないとエラーが出ちゃいます
TOTAL = TOTAL + Cells(j, i)のところでしょうか。
AA=Cells(j, i)をTOTAL = TOTAL + Cells(j, i)の前に追加してください。
TOTALのところを合計に、ALLTOTALを総合計にと全てかえて実行してみて下さい。
これでもダメならTOTAL = TOTAL + Cells(j, i)のCells(j, i)の部分だと思います。

エラーが出た時にAAにマウスをあわせてみて下さい。入力されている数値が表示されるはずです。

お礼

重ねて回答ありがとうございます！

ほんとにややこしくて申し訳ありません…。
またいい方法があれば、ご教授下さい(>_<)

補足

エラーが発生する箇所は、仰る所であってます。
>AA=Cells(j, i)をTOTAL = TOTAL + Cells(j, i)の前に追加してください。
TOTALのところを合計に、ALLTOTALを総合計にと全てかえて実行してみて下さい。

ここまで全部しましたが、やっぱりTOTAL = TOTAL + Cells(j, i)でエラーが出るんです。

ここでいったん諦め、No,1の方の提案下さったVBAを実行し、入った数式を消した後で、mar00様のVBAを実行したら、最後まで終了しました。
ただ…
どこにもデータが入っていないせいか、VBAが終了した後も、どこにも数式が入らないままでした…うぅ、残念…。

たぶん、IsEmptyでしょうか…。
これって、空白かどうかを判別する関数ですよね？

個数が入力された後で集計するのではなく、
入力されたら集計できるように、あらかじめ数式を入れて配布したいのです。
ややこしくてすみません…！

回答No.6

shinkami

もしかして手書きまたはワープロの中心のシステムを
全面委託したのではないでしょうか
そのとき委託先の質が低かったのか、御社の委託窓口の人が
強引強引に押し切ったのか
基幹システム自体も無理だらけのものになっているような気がします。

あなたの立場にもよりますが、基幹システムに携わっている
及びこのレポートを受け取る人との
話し合いが必要ではないでしょうか

失礼を重々承知の上での憶測だけの提案です。

お礼

回答ありがとうございます。

親身に考えて頂き、ありがとうございます。
ずさんなシステムの割に、小さい規模でもないので、
あたしレベルの提案がなかなか上に通らないのが実情です。

全くおっしゃる通り、使いづらいことこの上ないのですが、
徐々に、いろんなシステムが改善されていってるようなので
早くこれを何とかしてくれるよう祈ることしかできそうにありません…。
ありがとうございました。

回答No.5

shinkami

具体例を見る限り,Excell向きじゃないですね

同じ地名、区域名が何度も出てきますが
肝心な項目（日付、営業拠点名、担当名等）を省略していませんか

これに近い表としては
区域ごとの地名の数を最大数に統一（地名の少ない時は空白）して
(SUM)、（SUBTOTAL)の位置を横一列に並べると　(SUM)、（SUBTOTAL)
式は一つ作るとコピペできますね
列を追加して行毎のの合計をして印刷時にこの列をフィルタ（0）は表示しないにかける

お礼

回答ありがとうございます。

ちなみに、Excel向きでないとしたら、どんなソフト向きなんでしょうか？
この編集をしたのち、社内で再配布する分なので、最終的にExcelに戻さないといけないですが
もし簡単にできるものがあるのであれば、教えていただければ嬉しいです。

補足

会社のシステムなので、どうしてもExcelで、この形式でするしかないのです。

システムからダウンロードすると、本文で書いたような表が一つのシートにズラズラと表示されるので、行数をそろえるのが困難です。
下に100個近く表が続いているので、最大数に統一するため手作業をするのはとんでもなく時間がかかります。そもそも、あとから行数をそろえる作業をするなら、合計を手入力してもあまり作業的に変わらないですもんね…泣

各表の一番上には、これ以外の項目もあります。
例えば、Aちらし回収率、Bちらし回収率…とかですね。
ただ、その項目名は毎回固定ではないので、不必要だと思い、
あえて記載していません。

回答No.4

mar00

ご希望のものであるか自信はないのですが、マクロを実行すると図のようになります。
Sub Macro1()
For i = 2 To 6 Step 2
TOTAL = 0
ALLTOTAL = 0
For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If IsEmpty(Cells(j - 1, i)) Then Exit For
If Right(Cells(j, i - 1), 2) <> "合計" And Cells(j, i - 1) <> "総合計" Then
TOTAL = TOTAL + Cells(j, i)
Else
Cells(j, i) = TOTAL
ALLTOTAL = ALLTOTAL + TOTAL
TOTAL = 0
End If

If Cells(j, i - 1) = "総合計" Then
Cells(j, i) = ALLTOTAL
End If
Next j
Next i
End Sub

お礼

追加で回答頂きありがとうございます！
ちょっと今からやってみます…！

補足

回答ありがとうございます！

実行してみたところ、
8行目で型が一致しないとエラーが出ちゃいます(>_<)

やりたいことは、添付してくださった画像、まさにその通り！なので、
明日、会社で検証させていただこうと思います。

Excelのバージョンが違うせい、ということは考えられますか？
2010で実行してみたんですが…関係ないですよね＾＾；

回答No.3

acha51

やはり表の作り方の問題ではないでしょうか。
内容から類推すると品種がいくつかあって、それぞれ地区に配布し、
地域別に集計をするとともに総数を集計するというものでしょうか？

としてであれば一例を添付します
地区の増加は行の挿入で対応すればOKです

お礼

すみません、表はシステム的に固定のフォーマットで、
ここは変更のしようがないのです。

わざわざ画像まで作成していただき、ありがとうございます。

回答No.2

FEX2053

集計範囲をどうやって決めるんですか？

これを、客観的に文章として書き出すことが出来ないと、
式だろうがプログラムだろうが組めません。逆に言えば、
毎回表の状態を見ながら判断するなら、一つ一つ計算式
を入れるしか方法が無いのです。

ですので、表の体裁を変えることを先に考えてください。

例えば10行ごとに合計が入るということにして、項目を
10行単位で入れなおす（足りない場合は途中を空白行
にする）などが考えられます。

他の方法としては「合計範囲を特定するための指示列」
を１列作り、そこにA,B,C,Dなど「合計する行」を入れて、
=SUMIF()関数を使うという手があります。
http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/sumif.htm

この場合の総合計は、縦のデータをSUMIFのセルも含め
合計し、２で割れば出てきます。

お礼

回答ありがとうございます。

残念ながら、少し意図が違うようです。
おわかりでしたら、ご教授お願いします。

補足

F列が途中で改行が入ってしまっているので見辛くなってすみません。

範囲はその都度変わりますが、上の一番左の表で言うと、
A2：B8で一つの表です。
希望しているのは、
B4には=SUM(B2：B3）、B7に=SUM(B5:B6)、
B8に=SUBTOTAL(9,B2:B8)という風に数式を入れたいのです。
別にSUMでもSUBTOTALでも構わないです

すべての表は個別に完結し、B2:B107のように、
上から下までの総合計は不要です。
A2:B8、C2:D7、E2:F8…B102:B108、C102:C107で、
それぞれの地域合計と、総合計を求めるのが目的です。

A,C,E列には最初から地名・合計行名の見出しが入っているので、
B,D,F列の、”A,C,E列に**合計、総合計とある行”に、
数式を入れたいのです。

回答No.1

mu2011

一例です。
合計はすべてSUBTOTAL関数としています。
対象シートタブ上で右クリック→コードの表示→左脳ルコードを貼り付け→Ｆ５キーを押下
マクロ削除は、貼り付けたコードを全削除して下さい。

Sub Sample()
a = Split("*??合計,総合計", ",")
For i = 0 To UBound(a)
Set pos = Cells.Find(What:=a(i), _
lookat:=xlPart)
If pos Is Nothing Then Exit Sub
Set pos1st = pos
st_col = 1
st_row = 2
Do
If pos.Column <> st_col Then st_row = 2: st_col = pos.Column
pos.Offset(0, 1).Formula = _
"=subtotal(9," & _
Cells(st_row, pos.Column + 1).Address & ":" & _
Cells(pos.Row - 1, pos.Column + 1).Address & ")"
st_row = pos.Row + 1
Set pos = Cells.FindNext(pos)
Loop Until pos.Address = pos1st.Address
Next
End Sub

補足

早速の回答ありがとうございます！

恥ずかしながら、半分も理解できなかったですが、
とりあえず、実行してみました。

数式の入った場所自体は、希望していた通り！なのですが、
残念ながら、数式が違うようなのです。
私の書き方が悪かったのですが、地域合計と、個別の表だけの総合計が
知りたいので、
B4には=SUBTOTAL(9,B2：B3）、B7に=SUBTOTAL(9,B5:B6)、
B8に=SUBTOTAL(9,B2:B8)
下の表では、
B13に=SUBTOTAL(9,B102:B103)、B16に=SUBTOTAL(9,B14:B15)
B17に=SUBTOTAL(9,B11:B107)という風に数式を入れたいのです。

あとちょっとな感じがしますので、自分でも調べてみるつもりですが、
もし、簡単に修正が出来るのであれば、そして、もしお時間がおありであれば教えて頂けたら嬉しいです。
（自己解決出来ましたら、下にお礼を入れさせて頂きます）

よろしくおねがいします！