[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『月計・累計の自動計算』（やもめ）
元帳作成で科目ごとのシートが70ほどあり、下記の様に毎月分計と月累計を計算するのですが 計算範囲の行数が不定なので、前もって計算式を挿入しておく事が出来ず手動でSUM計算しています。
何とか自動計算が出来ないかと悩んでいます。
現在の計算は下記の表で （勘定科目によって貸借差引が逆になる科目があります）
期首が6月なので、C11科目ｺｰﾄﾞ55を入力でD11科目に6月分計が表示 借方 I列11 =SUM(I6:I10)+M5 貸方 K列11 =SUM(K6:K10) 差引残高 M列11 =I11-K11 で計算。
7月で途中改ページになると科目ｺｰﾄﾞ1入力、次頁へ繰越表示で I15 =SUM(I12:I14) K15 =SUM(K12:K14) M15 =SUM(M11,I15)-K15
科目ｺｰﾄﾞ2入力で、前頁より繰越表示 M16 =M15
7月分計は、I19 =SUM(I15:I18) K19 =SUM(K15:K18) 7月累計は、I20 =SUM(I11,I19) K20 =SUM(K11,K19) M20 =I20-K20
とこの様に毎月計算しています。 科目によっては、数ページになる科目もあるのでミスも起こります。 可能であれば、科目ｺｰﾄﾞ番号入力で計算される方法をご教授いただけますと幸いです。
A B C D E F G H I J K L M
--------------------------------------------------------------------------------------
4 月 日 科目ｺｰﾄﾞ 科目 取引ｺｰﾄﾞ 取引先 丁数 銀行 借方 空白 貸方 空白 差引残高
--------------------------------------------------------------------------------------
5 1 3 前期繰越 100,000 100,000
--------------------------------------------------------------------------------------
6 6 1 10 売掛金 101 A社 10,000
--------------------------------------------------------------------------------------
7 6 2 10 売掛金 102 B社 20,000
--------------------------------------------------------------------------------------
8 6 10 20 買掛金 5,000
--------------------------------------------------------------------------------------
9 6 10 20 買掛金 7,000
--------------------------------------------------------------------------------------
10 6 30 25 雑益 105 C社 5,000
--------------------------------------------------------------------------------------
11 55 6月分計 135,000 12,000 123,000
--------------------------------------------------------------------------------------
12 7 1 10 売掛金 101 A社 20,000
--------------------------------------------------------------------------------------
13 7 3 10 売掛金 103 H社 30,000
--------------------------------------------------------------------------------------
14 7 10 20 買掛金 4,500
--------------------------------------------------------------------------------------
15 1 次頁へ繰越 50,000 4,500 168,500
--------------------------------------------------------------------------------------
16 2 前頁より繰越 168,500
--------------------------------------------------------------------------------------
17 7 10 20 買掛金 10,000
--------------------------------------------------------------------------------------
18 7 31 10 売掛金 106 K社 20,000
--------------------------------------------------------------------------------------
19 56 7月分計 70,000 14,500
--------------------------------------------------------------------------------------
20 96 7月累計 205,000 26,500 178,500
--------------------------------------------------------------------------------------
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
スマホからなのでとりあえずアイデアだけ。
（もこな２） 2018/09/20(木) 19:38
もこな2さんアイデアありがとうございます。
月計・累計を表外に固定すればSUMIF関数で条件に合った合計を求められると思います。
しかし、表内だとやはりその都度の計算になります。
行数不定でも何とか自動計算できる方法を知りたいです。 （やもめ） 2018/09/20(木) 23:55
> 可能であれば、科目ｺｰﾄﾞ番号入力で計算される方法をご教授いただけますと幸いです。 合計行以外は手入力、合計行は計算ということであればマクロになるのでは？ （ねむねむ） 2018/09/21(金) 09:10
無理やり式でということであれば金額入力列を別にしておいて借方列・貸方列には、月日が入力されたら金額入力列から、 月日入力がなければ合計計算という式を入れるか。 （ねむねむ） 2018/09/21(金) 09:10
ねむねむさん ありがとうございます。 書式の変更は難しく、新たな列となるとM列の後ろに1列付け足すくらいになります。 なのでおっしゃる通りマクロでないと無理かもしれません。
ただマクロ・VBA は、ド素人なので・・・
（やもめ） 2018/09/21(金) 09:56
なんでだろう？
例示の物でいえば、「〇月分計」のA列はブランクになっているので
たとえば、I11セルに「=SUMIF(A:A,6,I:I)」って入れればＯＫなような。
累計のほうは、Ａ列はどうでもよくて、
たとえば、I20セルに「=SUMIF($D$1:$D20,"*月分計",$I$1:$I20)」っていれて
以降、その数式コピペすればいいとおもうので、表外じゃなくてもよくないです？
ちなみに、もうちょっと横着するなら、
「〇月分計」は「=SUMIF(A:A,SUBSTITUTE($D11,"月分計",""),I:I)」みたいに、対象行のＤ列を加工してもよさげにおもいます。
そのほか、提示例が間違っててA列がブランクじゃないということであれば、
各月計のほうは、SUMIFSつかって、D列が"<>*計"以外という条件を加えればいいような・・・
なんか質問を勘違いしてますかね？
（もこな２） 2018/09/21(金) 12:45
例示だとそのようなものはなさそうに見えます。
（というか、各科目段階では”差引計算”しないですよね？）
たぶん月計、累計や繰越行の話だとおもいますけど、借方（貸方）に負の数が立ってたら貸方（借方）に正の数となるよう相殺してから差し引きするって言いたかったんでしょうか？でもそれって、結果同じですよね。
「 100」－「-200」 = 300 「100+200」－「 0」 = 300
表が単式なら科目を見て資産なのか負債なのか判定する必要がありますけど、複式で整理されているから普通に借方から貸方を引けば、資産・負債差額なり、月間損益は計算はできるような・・・・
簿記の話はわからないし、エクセルの話と全然関係ないので深く考えてもしょうが無いのかもですけどなんか気になったので投稿しておきます。
（もこな２） 2018/09/21(金) 15:17
>たとえば、I11セルに「=SUMIF(A:A,6,I:I)」って入れればＯＫなような。 確かにSUM関数より正確に合計を求められますが、毎月締めの際に使う関数がかわるだけで 作業は同じだと思います。
>（勘定科目によって貸借差引が逆になる科目があります） これは、負債勘定科目の締めの計算の際 差引残高+貸方-借方 の計算をしているからです。
>（というか、各科目段階では”差引計算”しないですよね？） ハイ 差引計算しても良いのですが、ブランクにしています。
これらの締めの作業に毎回関数を挿入しないで出来ないものかと考えていましたが難しいですね。
もこな2さん 色々教えて下さりありがとうございます。
（やもめ） 2018/09/21(金) 16:35
1月分計のコードは50？ （mm） 2018/09/26(水) 12:48
(mm)さん返信が遅くなってすみません。 表にあるコードは、取り敢えずのものです。 実際使用コードは３ケタの数字になります。
（やもめ） 2018/09/27(木) 16:17
>可能であれば、科目ｺｰﾄﾞ番号入力で計算される方法をご教授いただけますと幸いです。 >表にあるコードは、取り敢えずのものです。
すくなくとも、科目ｺｰﾄﾞと科目の対応リスト的なものが存在しないと話にならないのでは？
（mm） 2018/09/27(木) 16:53
説明不足ですみません。
コード番号をふった科目リスト表が別BOOKにあり
D列にINDEX関数(配列)を使用しています。
（やもめ） 2018/09/27(木) 17:15
検索していて下記表のような不特定行数の合計を行う方法を見つけました。 今回の質問の参考にしたいと思っていますが、恥ずかしいですコードが理解出来ません。 どなたかコードの解説をして頂けないでしょうか
A B C D E F
1 月 日 取引先 借方 貸方 貸方累計 -------------------------------------------------------- 2 9 1 A社 10,000 -------------------------------------------------------- 3 9 1 B社 50,000 -------------------------------------------------------- 4 9 1 D社 6,000 -------------------------------------------------------- 5 日分計 50,000 16,000 16,000 -------------------------------------------------------- 6 9 4 E社 40,000 --------------------------------------------------------- 7 9 4 F社 40,000 --------------------------------------------------------- 8 9 4 G社 35,000 --------------------------------------------------------- 9 9 4 H社 80,000 --------------------------------------------------------- 10 日分計 75,000 120,000 136,000 --------------------------------------------------------- 11 9 3 A社 75,000 --------------------------------------------------------- 12 9 3 B社 55,000 --------------------------------------------------------- 13 9 3 D社 35,000 ---------------------------------------------------------- 14 9 3 H社 68,000 ---------------------------------------------------------- 15 日分計 130,000 103,000 239,000 ----------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng1 As String, myRng2 As String, i As Long, myRng As String
Dim myRow As Long, myRng3 As String, myRng4 As String
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
myRng = Target.Offset(, 2).Address(0, 0)
myRng2 = Target.Offset(-1, 2).Address(0, 0)
myRng4 = Target.Offset(-1, 1).Address(0, 0)
myRow = Target.Row
i = 1
If Target.Value = "日分計" Then
Do Until myRow - i = 0
With Cells(myRow - i, 3)
If .Value = "日分計" Or .Value = "" Then
myRng1 = Cells(myRow - i + 1, 5).Address(0, 0)
myRng3 = Cells(myRow - i + 1, 4).Address(0, 0)
Exit Do
End If
i = i + 1
End With
Loop
Target.Offset(, 1).Formula = "=SUM(" & myRng3 & ":" & myRng4 & ")"
Target.Offset(, 2).Formula = "=SUM(" & myRng1 & ":" & myRng2 & ")"
Target.Offset(, 3).Formula = "=SUMIF(C2:" & myRng & ",""日分計""," & "E2:" & myRng & ")"
End If
End Sub
（やもめ） 2018/10/02(火) 17:46
なお、Worksheet_Change なら、シートモジュールというところに、記述するものになります。
http://officetanaka.net/excel/vba/beginner/10.htm
（もこな２） 2018/10/02(火) 20:54
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng1 As String, myRng2 As String, i As Long, myRng As String
Dim myRow As Long, myRng3 As String, myRng4 As String
Stop 'ブレークポイントの代わり
'変化があった（入力された）セルがC列以外だったら・・・
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
'変化があった（入力された）セルが1つより多かったら・・・
If Target.Count > 1 Then Exit Sub
myRng = Target.Offset(, 2).Address(0, 0)
myRng2 = Target.Offset(-1, 2).Address(0, 0)
myRng4 = Target.Offset(-1, 1).Address(0, 0)
myRow = Target.Row
''変化があった（入力された）セルの内容が"日分計"であった場合・・・・
If Target.Value = "日分計" Then
i = 1
Do Until myRow - i = 0
With Cells(myRow - i, 3)
If .Value = "日分計" Or .Value = "" Then
myRng1 = Cells(myRow - i + 1, 5).Address(0, 0)
myRng3 = Cells(myRow - i + 1, 4).Address(0, 0)
Exit Do
End If
i = i + 1
End With
Loop
Target.Offset(, 1).Formula = "=SUM(" & myRng3 & ":" & myRng4 & ")"
Target.Offset(, 2).Formula = "=SUM(" & myRng1 & ":" & myRng2 & ")"
Target.Offset(, 3).Formula = "=SUMIF(C2:" & myRng & ",""日分計""," & "E2:" & myRng & ")"
End If
End Sub
（もこな２） 2018/10/03(水) 07:39
もこな2さん 何度もありがとうございます。 ブレークポイントを何回も試してみて理解できたものと 全く？？？のものがあります。 何とか理解できたコードが
'変化があった（入力された）セルが1つより多かったら・・・
If Target.Count > 1 Then Exit Sub
myRng = Target.Offset(, 2).Address(0, 0)
myRng2 = Target.Offset(-1, 2).Address(0, 0)
myRng4 = Target.Offset(-1, 1).Address(0, 0)
myRow = Target.Row
このコードはTarget, Range("C:C")対する行方向と列方向なんですね。
でも下記のコードは、全く？です。
すみません もう一度詳しく解説していただけないでしょうか
''変化があった（入力された）セルの内容が"日分計"であった場合・・・・
If Target.Value = "日分計" Then
i = 1
Do Until myRow - i = 0
With Cells(myRow - i, 3)
If .Value = "日分計" Or .Value = "" Then
myRng1 = Cells(myRow - i + 1, 5).Address(0, 0)
myRng3 = Cells(myRow - i + 1, 4).Address(0, 0)
Exit Do
End If
i = i + 1
End With
（やもめ） 2018/10/03(水) 17:49
＞このコードはTarget, Range("C:C")対する行方向と列方向なんですね。
ちょっと意味がわからないです。もしかして↓のことでしょうか？
Intersect(Target, Range("C:C"))
http://officetanaka.net/excel/vba/tips/tips118.htm
もしそうなら↑が参考になるとおもいますが、端的に言えば、入力された列がC列かどうか判定してるだけです。
Ｃ列以外に入力してステップ実行してみたら、「Exit Sub」の所を通って終わりませんでしたか？
まだ、テストをしていないのであれば、実験してみてください。言っていることが解ると思います。
また、同じくステップ実行をしたのであれば、だいたい解ったかとおもいますが、そのマクロは
Ｃ列のうちどこかのセルに「日分計」という文字が入力されたら、入力したセルの一つ上と
その、さらに上方向に向かって順番に空欄か「日分計」と入力されているセルを探して、
そのセル番地を調べて、入力したセルと同じ行のＤ，Ｅ、Ｆ列に、調べたセル番地を含む数式を
入力（設定）するようになってますよね。
それで、理解できないところはどこなのでしょうか？
提示されたものを見る限り、そんなに難しい動きはしてないようなので、ステップ実行して
変数の変化を見ていけば動きはつかめるように思いますが・・・
（もこな２） 2018/10/03(水) 21:33
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.