Excelで経費帳簿をつけていますが簡単にできる演算を教えてください。

質問

Excelで経費帳簿をつけていますが簡単にできる演算を教えてください。
シート'1月分'からシート'12月分'まで12シートあります。

例えばシート'1月分'にはA30に日付、Ｇ30に 交通 という文字を入れ、Ｉ30に金額を書いています。
またA36に日付、Ｇ36に 交通 という文字を入れ、Ｉ36に金額を書いています。
ほかにもシート内にあります。
Ｇ84に 交通費小計 という文字を入れ、Ｉ84にその月の小計金額を書いています。
Ｉ84=SUMIF(G1:G75,"交通",I:I)
G1からG75までのなかに　交通　という文字があれば、その当該のI列の金額のSUMを計算せよ
という演算でうまくいっています。

小計金額のセルは各月のシートとも項目の数が異なるために統一したセルとしていません。
たとえばシート'1月分'ではＩ84、シート'2月分'ではＩ84、シート'3月分'ではＩ88、
シート'4月分'ではＩ79、という具合です。

各月の交通費小計を足したものを、まとめとしてシート'年合計'に入れます。
シート'年合計'の　Ｄ29に　交通費計　という文字を入れ、Ｆ29に1月から12月までの交通費小計を足したもの入れています。

とりあえず、今のところ
Ｆ29=='1月分'!I84+'2月分'!I84+'3月分'!I88+'4月分'!I79+'5月分'!I86+'6月分'!I81+'7月分'!I78+'8月分'!I78+'9月分'!I78+'10月分'!I77+'11月分'!I78+'12月分'!I106
でいちいち各月のシートを開けて足していっています。

それより、もっとうまい具合にできないものかと
Ｆ29=SUMIF('1月分:12月分'!G:G,"交通費小計",'1月分:12月分'!I:I)
の演算を入れましたが、＃VALUE！とそのセルに表示され、うまく出来ません。
なぜでしょうか。

そんな感じで簡単にできる演算はないでしょうか。
エクセルは2003です。

回答No.4

fmajin · ベストアンサー

フォーマットを変更してみては、いかがでしょう？

たとえば、「1月」～「12月」シートの上部に、「交通費」などの科目ごとの小計を表示する集計欄を別途用意しておく。毎月のデータ数は異なるでしょうが、明細データの上に用意しておくことで、各月シートのフォーマットを統一することができると思います。
こうすれば、通常のSUM関数で、「=SUM('1月分:12月分'!B2)」みたいにして、単純な3D参照で合計が計算できますよね。

もうひとつの方向性として、「年合計」シートのほうで、無理に1つのセルで合計を計算しようとせず、いったん各月シートごとの小計を表示させておいて、それを合計する。
表のフォーマットがわかりませんが、たとえば、「年合計」シートのF1～Q1セルに「1月分」～「12月分」というシート名を入力しておく。で、D29には「交通費小計」（交通費計？　どっちも登場してるけど）と入力しておいてあることを前提にすると、F29セルには次のような数式を入力する。

=VLOOKUP($D29,INDIRECT(F$1&"!G:I"),3,0)

で、この数式をQ29セルまでドラッグしてコピーすれば、各月シートの小計が表示できる。あとは、R29セルで、これらのセルをSUM関数で合計すればいい。F～Q列がどうしても目障りなら、列を非表示にすればいいでしょう。無理に1つのセルで合計しようとするから、数式も長くなって面倒になるのではないかと思います。

お礼

二つの提案をありがとうございます。

データ数が変化する下側に配置せず、増減のない上側に定位置を設定する案は思いつきませんでした。
合計はデータの下流に配置するのが習慣的な流れになっています。
演算の易化を採るか、習慣性を採るかということになりますね。

２案目は一旦、年合計シートに参照させてから計算させるというもので、ワンステップ挟むのが
厭われたんですが、うまく演算はできました。

今の条件で短く、簡単な計算式がきっとあるだろうと思い質問しましたが、上級者の方々でも、
できないということがわかりました。

やはり計算しやすいような配置替えを検討したいと思います。
各月のシートに小計を入れず、年合計のシートに入れる方法にも思い至りました。

回答No.3

mar00

関数だと長くなるのでマクロを作ってみました。

Sub Macro1()
Sheets("シート年合計").Select
Application.ScreenUpdating = False
TOTAL = 0
For INP = 1 To 12
Sheets(INP).Select
Cells.Find(What:="交通費小計", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
GYOU = ActiveCell.Row
RETU = ActiveCell.Column
TOTAL = TOTAL + Cells(GYOU, RETU + 2)
Next INP
Sheets("シート年合計").Select
Range("F29") = TOTAL
Application.ScreenUpdating = True
End Sub

For INP=1 TO 12というのは１月のシートが１番前にあること
2～１２月まで連続して並んでいる事を前提としてます。
１月のシート前に１つシートがあれば2to13として下さい。
もし間違っているところがあったら困るので原本のブックとは別の名前で
保存してから実行して下さい。

お礼

すみませんが、マクロは聞いたことはありますが使い方を知りません。
挑戦してみました。

原本のブックとは別の名前で保存して、入れたいセルを選択。
ツール→マクロ→マクロ名→適当な名前をつけても実行ボタンがグレーアウトしていて前に進めませんでした。

気になるの　"シート年合計"　というシートはなく　"年合計"　というシートがありますから
それに変更する必要がありそうだと思ったくらいです。

時間をかけて作っていただいたのに心苦しく思っています。
ありがとうございます。

回答No.2

hallo-2007

シートとデータの構成が悪いと思います。
>シート'1月分'からシート'12月分'まで12シートあります。
一枚のシートに上から順にひたすら縦方向にデータを入力していきます。
>例えばシート'1月分'にはA30に日付、Ｇ30に 交通 という文字を入れ、Ｉ30に金額を書いています。
>またA36に日付、Ｇ36に 交通 という文字を入れ、Ｉ36に金額を書いています。
A31～G35までは、何が入力されているのでしょうか。
基本は、1行に1件のデータを横方向に入力していきます。
帳票類を印刷したい、表示したい構成があると思いますが、
別途の印刷する帳票の形式のシートを準備して、必要な金額をデータのシートから引っ張ってくる、計算してくれる様に準備します。
最初から印刷などに必要な形式を作って、そこに金額を入れるのではなく
データを入れるシートと印刷などで使用するシートと分けて考えてください。
すっきりしたものになりますし、応用範囲も広がります。

お礼

私的にはみやすくわかり易いような構成にしました。
「ひたすら縦方向にデータを入力してい」く方法も浮かびましたが今の方を採用しました。
A31～G35には品名、品目、取引先などがあります。
提案をありがとうございます。

回答No.1

kagakusuki

数式を毎年入力し直さなければならない事が問題なのであって、数式が長くなる事は問題ではないのですね。
　それならば

Ｆ29=SUMIF(1月分!$G:$G,"交通費小計",1月分!$I:$I)+SUMIF(2月分!$G:$G,"交通費小計",2月分!$I:$I)+SUMIF(3月分!$G:$G,"交通費小計",3月分!$I:$I)+SUMIF(4月分!$G:$G,"交通費小計",4月分!$I:$I)+SUMIF(5月分!$G:$G,"交通費小計",5月分!$I:$I)+SUMIF(6月分!$G:$G,"交通費小計",6月分!$I:$I)+SUMIF(7月分!$G:$G,"交通費小計",7月分!$I:$I)+SUMIF(8月分!$G:$G,"交通費小計",8月分!$I:$I)+SUMIF(9月分!$G:$G,"交通費小計",9月分!$I:$I)+SUMIF(10月分!$G:$G,"交通費小計",10月分!$I:$I)+SUMIF(11月分!$G:$G,"交通費小計",11月分!$I:$I)+SUMIF(12月分!$G:$G,"交通費小計",12月分!$I:$I)

　又は、

Ｆ29=SUMIF(1月分!$G:$G,"交通費",1月分!$I:$I)+SUMIF(2月分!$G:$G,"交通費",2月分!$I:$I)+SUMIF(3月分!$G:$G,"交通費",3月分!$I:$I)+SUMIF(4月分!$G:$G,"交通費",4月分!$I:$I)+SUMIF(5月分!$G:$G,"交通費",5月分!$I:$I)+SUMIF(6月分!$G:$G,"交通費",6月分!$I:$I)+SUMIF(7月分!$G:$G,"交通費",7月分!$I:$I)+SUMIF(8月分!$G:$G,"交通費",8月分!$I:$I)+SUMIF(9月分!$G:$G,"交通費",9月分!$I:$I)+SUMIF(10月分!$G:$G,"交通費",10月分!$I:$I)+SUMIF(11月分!$G:$G,"交通費",11月分!$I:$I)+SUMIF(12月分!$G:$G,"交通費",12月分!$I:$I)

で宜しいのではないかと思います。
　尚、

Ｆ29=VLOOKUP("交通費小計",1月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",2月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",33月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",4月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",5月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",6月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",7月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",8月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",9月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",10月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",11月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",12月分!$G:$I,3,FALSE)

でも可能ですが、G列に"交通費小計"と入力されているセルが無いシートがある場合には、エラーとなります。

お礼

３点を提示していただきありがとうございます。

おっしゃる通り、数式を毎年入力し直さなければならない手間を省きたいのが第1義ではありますが
できれば短めの簡便化をめざしています。

１点目
=SUMIF(1月分!$G:$G,"交通費小計",1月分!$I:$I)+・・・・
を入れたらシングルクウォーテーションが以下のように付いた状態になりました。
=SUMIF('1月分'!$G:$G,"交通費小計",'1月分'!$I:$I)+・・・・
結果はうまく演算できました。
また　$G:$G　は　G:G　でもいいんですよね。

2点目
こちらも' 'が付きました。
=SUMIF('1月分'!$G:$G,"交通費",'1月分'!$I:$I)+・・・
こちらはうまく演算できず　\0　という結果でした。

3点目
こちらも' 'が付きましたが結果は演算できませんでした。
下の33月分を直したら上手くできました。
=VLOOKUP("交通費小計",1月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",2月分!$G:$I,3,FALSE)+VLOOKUP("交通費小計",33月分!$G:$I,3,FALSE)+・・・

小計が\0であってもG列には各月とも"交通費小計"文字を入れてありますから大丈夫でした。