Excelの表で集計範囲が変わってしまう場合の範囲指定の仕方

質問

以下の様な表で、演目の数が毎回変わるので、行の挿入や削除
一部を複写したり、データ削除して行を増減させるのですが、
集計にSUM関数を使っているのですが、集計範囲を直し忘れたり
して、結局いつも手計算で検算しています。マクロとかは、全然
使えないので、どうしたら良いかどなたか教えて下さい。

　　演　　目　　 　　時間
はじまり　　　　　　　12分　　・・・ここは固定
合奏「ぞうさん」　　25分
寸　劇　　　　　　　　30分
ピアノ演奏　　　　　40分
　　小計　　　　１時間47分
手　品　　　　　　　35分
きり絵　　　　　　　25分
　　小計　　　　１時間
　　合計　　　　２時間47分

回答No.4

noname#99913

同じ列に時間と小計・合計が混じっているのが問題だと思います。次のようにしたらどうでしょうか。
　　　Ａ　　　　Ｂ　　　Ｃ
１　　演目　　　時間　　合計
２　　はじまり　１２分　
３　　ぞうさん　２５分
４　　寸劇　　　３０分
５　　ピアノ　　４０分
６　　小計　　　　　　　１時間４７分
７　　手品　　　３５分
８　　切り絵　　２５分
９　　小計　　　　　　　１時間
10　　合計　　　　　　　２時間４７分

合計の式を「=sum(b2:b10)」のように、実際にデータがある範囲より下まで設定しておくと、行を挿入しても大丈夫です。

お礼

お礼が遅くなってすみません。
参考にさせていただきました。

回答No.3

tekuinai

深く考えていない回答をしてしまいました。
途中に小計が入っている状態なんですね。
また文字列として『分』や『時間』を使用されているのですね。

この場合詳しい情報がないので自分なりに考えて関数のみでやってみますと。こういうのはどうでしょうか。
A列は演目
B列は小計、合計を含めた時間
C列にはA列が小計、合計のときは空白　その他は分単位へ変換
D列は小計や合計用の計算
とし、

まず小計のセルB6に以下の式を入力
=SUBSTITUTE(SUBSTITUTE(ROUNDDOWN(D6/60,0)&"時間"&MOD(D6,60)&"分","0時間",""),"0分","")
B6をコピーしB9とB10に貼り付ける。

C2に以下の式を入力
=IF(OR(B2="",A2="小計",A2="合計"),"",VALUE(IF(ISERROR(FIND("分",ASC(B2))),SUBSTITUTE(ASC(B2),"時間","")*60,IF(ISERROR(FIND("時間",ASC(B2))),SUBSTITUTE(ASC(B2),"分",""),MID(ASC(B2),1,FIND("時間",ASC(B2))-1)*60+SUBSTITUTE(MID(ASC(B2),FIND("時間",ASC(B2))+2,2),"分","")))))
C2をコピーしC10まで貼り付ける

D2に以下の式を入力
=IF(A2="小計",SUM($C$1:C1)-SUMPRODUCT(($A$1:A1="小計")*1,$D$1:D1),IF(A2="合計",SUMPRODUCT(($A$1:A1="小計")*1,$D$1:D1),""))
同じくD2をコピーしD10まで貼り付ける。

説明はちょっと省きますが、このような式ではどうでしょうか。
C列D列の表示、非表示はお任せします。

文章能力が低いので分かりにくかったらすみません

お礼

お礼が遅くなりましてすみません。
質問初心者のため、質問の仕方もいま一つなのに、
丁寧にご回答いただきありがとうございました。

回答No.2

Wendy02

こんにちは

Excelで、どのように入力されて、そのように表示されてものか分かりませんが、一例を示しておきます。マクロという方法もないわけではありませんが、時間の入力値の入れ方が分からないので、とても、この質問だけで回答することは出来ません。あくまでも、時間は、00:00:00 のように入れるものとして、話を進めます。

図を見ていただければ分かりますが、非常に複雑です。
まず、この表は、Excel2003 で出来ていますが、Excel2007 でも同様です。
表に数式を入れたら、後は、リストに変換してあげます。
（リスト：データ-->リスト-->リストの作成
　[レ]先頭行をリストの見出しとして使用する

　　[OK] をクリック
-----------------------------------
[リスト]は、単に、行の挿入・削除をしたときに、数式を自動的に入れるものであって、特に、自分で、数式をコピーするなら、そのままでも構いません。[リスト]は、レイアウト的に、あまり自由度が高くありません。
-----------------------------------
C列は入力列です。

B2:
=SUBSTITUTE(SUBSTITUTE(TEXT(IF(OR(A2="合計",A2="小計"),$D2,$C2),"h時間m分"),"0時間",""),"間0分","間")

D2:
=IF(A3="合計",SUM($C$1:C3),IF(A3="小計",SUM($C$1:C3)-SUM($D$1:D2),""))

最後に、入力列と補助列を非表示にしてあげます。
これ以上、複雑な内容になりますと、マクロのほうが、良いかもしれません。ただし、質問の中では、全角と半角の数字が混在しているなど、何のコメントも入っておりませんから、詳しい内容を教えていただけないと提示は出来ません。

お礼

お礼が遅くなり、申し訳ありませんでした。
質問初心者のため、質問の仕方もいま一つなのに、
丁寧にお答えいただいてありがとうございました。

回答No.1

tekuinai

現状がいまいち分からないのですが、sum関数としては
『=sum(B2:B8)』のような式でしょうか?もしそうであれば
『=SUM($B$2:$B$8)』で解決できるのではないでしょうか。
添付資料にEXCELはつけれるのか分からないけど何かしら手がかりを
つけていただければ解答できると思います。
また$の意味は絶対参照。どんだけずれても絶対に参照しますという意味です。
こんな回答でよかったでしょうか。
操作としてはその参照セル位置の入力文字にカーソルを置いてF4を押せば
簡単に$がつきますよ。

お礼

お礼が遅くなり大変申し訳ありませんでした。
参考にさせていただきました。