[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『未収金の消込作業』（やもめ）
勘定元帳を手書きからPCでの入力に切り替える事になり、勘定元帳BOOKの中の未収金 シートで 下記のような表を作成したいと思っています。 入金になったらJ列に入金月を自動で表示させて消込をしたいのですが方法がわからず困っています。 どなたかご教授頂けないでしょうか。 それと、表中でJ列に残とありますが一部入金になったときの表示で残金が入金になったらその月を表示（手書きの場合）ですが、これも自動で出来るのでしょうか？
本当に厚かましいお願いですが宜しくお願いします。
A B C D E F G H I J K L M
月 日 科目ｺｰﾄﾞ 科目 取引ｺｰﾄﾞ取引先 丁数 銀行 借方 空白 貸方 空白 差引残高
-- -- ---- ------ ---- ------ ---- -- ---- -- ---- -- --------
4 1 01 前期繰越 10 ABC社 20-01 30,000 4
4 1 01 前期繰越 12 EFG社 20-05 15,000 4
4 1 01 前期繰越 16 SSS社 20-10 50,000 残
4 10 02 普通預金 12 EFG社 20-05 15,000
4 30 02 普通預金 10 ABC社 20-01 30,000
4 30 05 完成工事高 11 DDD社 21-02 20,000 6
4 30 05 完成工事高 15 HHH社 21-03 15,000
-------------------------------------------------------------------------------
00 4月計 130,000 45,000 85,000
5 10 02 普通預金 16 SSS社 20-10 30,000
. . . . . . . . .
. . . . . . . . .
6 20 03 現金 11 DDD社 21-02 20,000
-------------------------------------------------------------------------------
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
>入金になったらJ列に入金月を自動で表示させて消込をしたいのですが方法がわからず困っています。
入金は完成工事未収入金の減額になりますので、J列ではなくL列になるのでは？ （カリーニン） 2015/08/29(土) 14:15
そもそも、入力はどのように行っているのでしょう？ 直接セルに入力しているのでしょうか？ それとも入力フォームがあり、そこからVBAなどで 各勘定にデータを飛ばしているのでしょうか？ （カリーニン） 2015/08/29(土) 14:33
残金が0になった場合を判定したい、ということであれば、人名勘定を 使う、という手もあります。売掛金全体を把握するためには別の手間が かかりますが、取引先ごとの残高を把握するのは簡単になると思います。 （カリーニン） 2015/08/29(土) 14:41
カリーニンさん有難うございます。 ご指摘の通り貸借が違いますね。お恥ずかしいです・・ 未収金が貸方で未収入金が借方、消込月はL列です。
入力は直接入力で、D列、F列はコード表からリンクしています。 あと、消込判定でG列の番号が重複することもあるので判定条件は EもしくはF・G・Kの条件一致になります。
すみません宜しくお願いします。
（やもめ） 2015/08/29(土) 18:00
たとえば、前繰が20,000あり、15,000入金で残5,000の状態で 完成工事高10,000、その後10,000入金したら前繰分は全て入金済み ですが、期首からの累計としては未収入金は残ってますよね？ こういう場合はどうするのでしょう？ （カリーニン） 2015/08/29(土) 22:50
>入金になったらJ列に入金月を自動で表示させて消込をしたいのですが方法がわからず困っています。
>それと、表中でJ列に残とありますが一部入金になったときの表示で残金が入金になったらその月を表示（手書きの場合）ですが、これも自動で出来るのでしょうか？
この二つのことは同じことを言ってるように見えるのですが、どうちがうのでしょう？ （カリーニン） 2015/08/29(土) 23:06
お返事が遅くなりすみません。
作成の表ですが貸借間違えていませんでした。 振替伝票（手書き）では入金がされた場合、借方科目：普通預金、貸方科目：未収金なので 元帳への転記は入金額は貸方になります。
期首からの累計ですが毎月締めには月計・累計は計算されます。
>二つのことは同じことを言ってるように見えるのですが、どうちがうのでしょう？
まだ元帳を手書きで記帳しているので締め時、差引残高との金額確認や未収金の取引先の確認が 入金月と残と記されていると分かりやすので、PCに切り替える際も同じように出来ればと思いました。
”一部入金になったときの表示で残金が入金になったらその月を表示をする”が自動表示出来なければ 全額入金になった月表示だけでも良いのでご教授ください。 （やもめ） 2015/08/30(日) 10:16
他の回答者から回答が付かないですね。
ここはエクセルのQ&Aサイトであって経理関係のQ&Aサイトではないので、 表に対する説明がほとんどないので会計に関する知識がないと何のことやら さっぱりわからないと思います。
もっと表に対する説明、こういう表があって、どういう処理を してこういう表にしたい、という詳しい説明をしたら回答が付きやすいと 思いますよ。
他の方の質問も参考にしてみてください。
※私自身、まだ質問の全体を把握できているわけではありません。 現在質問の意図を解析中です。 （カリーニン） 2015/08/30(日) 12:45
経理関係の質問をしたのでは無いですが、説明不足のようですね。
表に関しては、ほぼ完成表に近いです。 処理については、入金になった月、日、科目ｺｰﾄﾞ、 科目、 取引ｺｰﾄﾞ、取引先、丁数、貸方が入力されたら 取引ｺｰﾄﾞ、取引先 、 丁数(重複番号あり) 、借方 で一致する行を探して合致すればJ列に入金月を表示する。 ただ、丁数の番号が重複する事もあるのでそんな事も含めてどの様に自動表示するのか方法を教えて頂きたいと思いました。
エクセルはまだまだ勉強不足ですが、PCへの切り替えまで期間も少しあるので 他の方のQ&Aで参考に出来るものを検索してみます。 有難うございました。 （やもめ） 2015/08/30(日) 14:08
＞消込判定でG列の番号が重複することもあるので判定条件は ＞ EもしくはF・G・Kの条件一致になります。
同一条件において 貸方の方は、分割入金になることは珍しくないでしょうけど、 借方の方も、何回かに分けて増加することもあるんですか？ （あるとするとちょっと厄介だなぁと思っているんですが・・）
あと、「前払い」と云うか「払い過ぎ」と云うか、 そんな余分な入金も実際はあったりするんでしょうか？
（半平太） 2015/08/30(日) 23:31
>他の方の質問も参考にしてみてください。
これは、「質問のしかた」を参考にしてください、というつもり で書きました。 （カリーニン） 2015/08/31(月) 00:07
質問の勘定の説明がないので、おそらく、ですが、質問の 勘定は「完成工事未収入金」と思われます。 一般の売掛金 に相当します。
下記は一般的な会計処理です。よって、企業によって使う 勘定科目などが違ってくる可能性があります。
建設業会計には、「工事進捗基準」と「工事完成基準」が あり、いずれにせよ、完成した、と見做された部分については 売上(完成工事高)を認識し、同時に債権を同額認識します。
一般の前受金に相当するのは「未成工事受入金」になります。
売上(完成工事高)に対して前受金(未成工事受入金)が少ない 場合は債権である売掛金(完成工事未収入金)を認識します。
もし、入金があった場合、完成工事未収入金の残高がある 場合は、まず完成工事未収入金を消込み、余った場合は未 成工事受入金として処理します。
逆に、売上(完成工事高)を認識したときに未成工事受入金 の残高がある場合は、まず未成工事受入金を消込み、足り ない場合は完成工事未収入金を認識します。
（カリーニン） 2015/08/31(月) 01:55
SUMIFS関数です。参考になると思います。 http://www.becoolusers.com/excel/sumifs.html
これに絶対参照と相対参照を組み合わせることになると思います。 （カリーニン） 2015/08/31(月) 08:37
半平太さん、有難うございます。
>借方の方も、何回かに分けて増加することもあるんですか？
借方は金額が決定したものなので増加する事はありません。
>「前払い」と云うか「払い過ぎ」と云うか、 そんな余分な入金も実際はあったりするんでしょうか？
絶対に無いとは言えませんが、処理の科目が変わるのでこの未収金シートには影響がありません。
カリーニンさん、詳しく説明頂いて有難うございます。 SUMIFS関数も参考にして試してみたいと思います。 （やもめ） 2015/08/31(月) 10:37
＞ 借方は金額が決定したものなので増加する事はありません。
それを前提のマクロ案です。 ※他プログラムを流用していますので、見づらい部分があります。ご容赦を・・・ m(__)m
「J1セル」を右クリックすると消込を行います。(そのセルを右クリックしない限り、幾らデータを入力しても変化しません・・念の為)
このセルを右クリックすると最新消込状態にアップデートされる <サンプル 結果図> ↓ 行 _A_ _B_ ____C____ _____D_____ ____E____ ___F___ __G__ __H__ ___I___ ___ J ___ ___K___ _L_ ____M____ 1 月 日 科目ｺｰﾄﾞ 科目 取引ｺｰﾄﾞ 取引先 丁数 銀行 借方 消込実行 貸方 差引残高 2 4 1 1 前期繰越 10 ABC社 20-01 30,000 2 30,000 3 4 1 1 前期繰越 12 EFG社 20-05 15,000 4 15,000 4 4 1 1 前期繰越 16 SSS社 20-10 50,000 残 50,000 5 4 10 2 普通預金 12 EFG社 20-05 15,000 -15,000 6 4 30 2 普通預金 10 ABC社 20-01 26,000 -26,000 7 4 30 5 完成工事高 11 DDD社 21-02 20,000 6 20,000 8 4 30 5 完成工事高 15 HHH社 21-03 15,000 残 15,000 9 10 0 4月計 130,000 41,000 11 5 10 2 普通預金 16 SSS社 20-10 30,000 -30,000 12 13 6 30 2 普通預金 10 ABC社 20-01 3,000 -3,000 14 6 20 3 現金 11 DDD社 21-02 20,000 -20,000 15 7 10 2 普通預金 16 SSS社 20-10 7,777 -7,777 16 2 10 2 普通預金 10 ABC社 20-01 1,000 -1,000
’標準モジュールに貼り付け －－－－－－－－－
Public Type DT
lastRow As Long
numOfRows As Long
numOfCols As Long
valOfData As Variant
End Type
Public Function DataInWsh(Wsh As Worksheet, FirstRow As Long, colRange As String) As DT
Dim lastRow As Long
With Wsh
lastRow = .Columns(colRange).Cells(.rows.count, 1).End(xlUp).Row
DataInWsh.lastRow = lastRow
DataInWsh.numOfRows = Abs(lastRow - FirstRow) + 1
DataInWsh.valOfData = Intersect(.Columns(colRange), .rows(FirstRow & ":" & lastRow)).Value
DataInWsh.numOfCols = UBound(DataInWsh.valOfData, 2)
End With
End Function
Public Function isTitleOnly(Wsh As Worksheet, FirstRow As Long, colNum As String) As Boolean
isTitleOnly = Wsh.Cells(Wsh.rows.count, colNum).End(xlUp).Row <= FirstRow
End Function
’勘定シートモジュールに貼り付け －－－－－－－－－－－－－－－－－－－－－ ’(シート見出しを右クリックして、「コードの表示」を選ぶと出てくるモジュール
Private Const ItemColRange As String = "A:K" Private Const strKeys As String = "6,7"
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(0, 0) = "J1" Then
Cancel = True
消込
End If
End Sub
Private Sub 消込()
Dim temp
Dim result()
Dim RDkey
Dim DTA As DT
Dim NN As Long, KK As Long, QQ As Long
Dim Dic As Object 'Dictionary
Dim IDkey
Dim DataStored
Dim detailsByMonth(1 To 12)
Dim Mpos '月位置
Dim KeysOrder
Dim Mon
KeysOrder = Split(strKeys, ",") '複数のキー列を分割する
If Not isTitleOnly(Me, 1, "A") Then '処理データが存在する場合
Set Dic = CreateObject("Scripting.dictionary")
DTA = DataInWsh(Me, 2, ItemColRange) '2行目から吸い上げ
For NN = 1 To DTA.numOfRows
If DTA.valOfData(NN, 5) <> "" And DTA.valOfData(NN, 11) > 0 Then '社名コード,貸方残あるもののみ処理
IDkey = DTA.valOfData(NN, KeysOrder(0)) 'Dic用キーを合成する
For QQ = 1 To UBound(KeysOrder)
IDkey = IDkey & "!" & DTA.valOfData(NN, KeysOrder(QQ))
Next
If Dic.Exists(IDkey) Then
DataStored = Dic(IDkey)
Mpos = DTA.valOfData(NN, 1) + IIf(DTA.valOfData(NN, 1) > 3, -3, 9)
DataStored(Mpos) = DataStored(Mpos) + DTA.valOfData(NN, 11)
Dic(IDkey) = DataStored
Else
Mpos = DTA.valOfData(NN, 1) + IIf(DTA.valOfData(NN, 1) > 3, -3, 9)
detailsByMonth(Mpos) = DTA.valOfData(NN, 11)
Dic(IDkey) = detailsByMonth
Erase detailsByMonth
End If
ElseIf DTA.valOfData(NN, 11) < 0 Then
MsgBox "貸方のマイナス値は想定しておりません at " & NN + 1 & "行目"
End
End If
Next NN
End If
If Dic.Count > 0 Then
For Each RDkey In Dic.Keys '月別入金累計残高Arrayを作成
DataStored = Dic(RDkey)
DataStored(1) = DataStored(1) * 1
For KK = 2 To 12
DataStored(KK) = DataStored(KK) + DataStored(KK - 1)
Next KK
Dic(RDkey) = DataStored
Next
temp = Dic.Items
ReDim result(1 To DTA.numOfRows, 1 To 1)
'順次消込処理
For NN = 1 To DTA.numOfRows
If DTA.valOfData(NN, 5) <> "" And DTA.valOfData(NN, 9) <> "" Then '社名コード,借方残あるもののみ処理
result(NN, 1) = "残"
IDkey = DTA.valOfData(NN, KeysOrder(0)) 'Dic用キーを合成する
For QQ = 1 To UBound(KeysOrder)
IDkey = IDkey & "!" & DTA.valOfData(NN, KeysOrder(QQ))
Next
If Dic.Exists(IDkey) Then
DataStored = Dic(IDkey)
With Application
If .Max(DataStored) >= DTA.valOfData(NN, 9) Then
Mon = .Frequency(DTA.valOfData(NN, 9), DataStored)
Mon = .Match(1, Mon, 0) '該当月の位置をチェック
result(NN, 1) = Mon + 3
If result(NN, 1) > 12 Then
result(NN, 1) = result(NN, 1) - 12
End If
End If
End With
End If
End If
Next NN
Me.Range("J2").Resize(DTA.numOfRows).Value = result
Dic.RemoveAll
Else
MsgBox "処理データはありません｡"
End If
End Sub
（半平太） 2015/08/31(月) 12:11
半平太さん、早速の回答有難うございます。 短時間でこの様なコードが書けるなんてスゴイです。 エクセル初心者で業務のかたわら勉強している私にとって かなりハードルの高い回答ですが貼り付けて試してみます。
結果報告まで少しお時間ください。頑張ります。 （やもめ） 2015/08/31(月) 13:10
ご報告します。コードを試して望んでいた通りの表示に出来たので本当にビックリしましたが 重複番号（丁数）のところで入金になっていないのに入金月が表示されていました。 何故なのでしょうか？
行 A B C D E F G H I J K L M
1 月 日 科目ｺｰﾄﾞ 科目 取引ｺｰﾄﾞ取引先 丁数 銀行 借方 消込実行 貸方 空白 差引残高
2 -- -- ---- ------ ---- ------ ---- -- ---- --- ---- -- --------
3 4 1 01 前期繰越 10 ABC社 20-01 30,000 4
4 4 1 01 前期繰越 16 SSS社 20-10 15,000 残
5 4 1 01 前期繰越 12 EFG社 20-05 50,000 4
6 4 1 01 前期繰越 12 EFG社 20-05 10,500 4
7 4 1 01 前期繰越 12 EFG社 20-05 40,000 4
8 4 1 01 前期繰越 12 EFG社 20-05 20,000 4←入金になっていないのに表示される
9 4 1 01 前期繰越 15 HHH社 21-03 15,000 残
10 4 30 02 普通預金 12 EFG社 20-05 50,000
11 4 30 02 普通預金 12 EFG社 20-05 10,500
12 4 30 02 普通預金 12 EFG社 20-05 40,000
13 4 30 02 普通預金 10 ABC社 20-01 30,000
14
00 4月計 180,500 130,500 50,000
（やもめ） 2015/08/31(月) 20:42
＞ 6 4 1 01 前期繰越 12 EFG社 20-05 10,500 4 ＞ 7 4 1 01 前期繰越 12 EFG社 20-05 40,000 4 ＞ 8 4 1 01 前期繰越 12 EFG社 20-05 20,000 4←入金になっていないのに表示される
6～8行目のデータは全て同種(同キー）の未収金ですので、 最初に確認させて頂いたこれに反していると云うのが私の解釈です。(分割増加の解釈がスレ違ったかも知れません） ↓ ＞ ＞借方の方も、何回かに分けて増加することもあるんですか？ ＞ 借方は金額が決定したものなので増加する事はありません。
同じキーの繰越データをどうしても分割する必要あるのでしたら、 更に枝番を追加するなどして別種になるよう工夫して頂けたら、と思います。
それは出来ないと云うことであれば、 私には理解できない事態なので、ここでドロップアウトさせて頂きます。
（半平太） 2015/08/31(月) 21:49
半平太さんすみません。違った解釈で捉えてました。 社内処理上、各工事毎に番号をふっていますが、少額工事は件数が非常に多いので工事をした月の数字が入った (例：4月工事なら20-804、5月20-805）と1つの番号で管理しています。
でもそうですよね。実際は別々に請け負った工事でも同じ番号・取引先だと金額増加になりますね。
手書きだと気付かないところです。
ご提案して頂いたように枝番をふってこのコードを活用させて頂きます。
本当に有難うございました。 （やもめ） 2015/08/31(月) 23:43
＞社内処理上、各工事毎に番号をふっていますが、少額工事は件数が非常に多いので工事をした月の数字が入った ＞ (例：4月工事なら20-804、5月20-805）と1つの番号で管理しています。 ＞実際は別々に請け負った工事でも同じ番号・取引先だと金額増加になりますね。
あれー？ そうなると、繰越未収金以外でも、借方が"分割増加"することがあるって事なんですか？
それなら、それ用に対応を考えてもいいですよ。 ただし、その方がもっと事務効率になると云うことが明らかならですけど。
・・・と云うのは、現方式は、工事毎(または請求書番号毎)に1対1に対応させた消込ではないですよね？
そんな風にアバウトでいいなら、いっその事、丁数の照合も止めて、 どこの会社からが幾ら入金されたかだけ貸方に入力し、 上から順に借方の明細を消し込んで行っても同じことじゃないですか？ （アバウトの度合は異なりますが、精密ではない事には変わりないです）
そうすれば、いちいち丁数を考える必要がない分、貸方の金額入力事務が もっと簡単になると思うのですけど、如何でしょう？
（半平太） 2015/09/01(火) 09:19
>そうなると、繰越未収金以外でも、借方が"分割増加"することがあるって事なんですか？
期が変るので頭の番号が変わりますが、ハイあります。 毎月末に完成しているが工事代金が未収になっているのを完成工事高の科目で借方に計上します。
>いっその事、丁数の照合も止めて、 どこの会社からが幾ら入金されたかだけ貸方に入力し、 上から順に借方の明細を消し込んで行っても同じことじゃないですか？
結果としては同じだと思います。ただこの表でどの工事とどの取引先からの入金の有無が確認しやすくなると思います。 効率は良くないかもしれませんが、少額工事に関しては提案して頂いた枝番を付けて別種扱いにします。
その方がより分かりやすいので・・・色々考えて頂いたり良いアドバイスを頂けて感謝です。
（やもめ） 2015/09/01(火) 14:41
＞効率は良くないかもしれませんが、少額工事に関しては提案して頂いた枝番を付けて別種扱いにします。
そちらで納得されているのでしたら、何も言う必要はないのかも知れませんが、 現バージョンは、(インプットミスであっても）丁数に重複があると十分な入金がないにも拘らず消込をしてしまうので、 丁数のデータ入力者に掛かる(ミスは出来ないとの）精神的負担が大きすぎると考えざるを得ません。
そこで、十分な資金が無いときは、トータルで過剰消込みを防止できないか考えてみましたが、 旨いロジックが見つかりませんでした。m(__)m
１．金額に不一致があった場合、充当順の決定が難しい。
２．洗い替え方式のため、先月は消込が○月だったのに、
今月消込を行うと△月に変わってしまう。(多分、こういうのは拙いと思われます)
・・・と云うことで挫折しました。お許しを m(__)m
（半平太） 2015/09/01(火) 20:41
本当に本当にありがとうございました。
>十分な資金が無いときは、トータルで過剰消込みを防止できないか考えてみましたが、 旨いロジックが見つかりませんでした。
今回、作成して頂いたコードはこれから切り替えていく業務に大変役立ますし 現在の手書きからすると効率は断然良くなります。
なので私やこの業務をする者にとってとても負担軽減になりますよ。
厚かましいお願いをしたにも拘わらず、とても親切に対応して下さったことに感謝しています。
今後も色々質問（お願い含む）すると思いますが、何卒ご教授宜しくお願いします。
（やもめ） 2015/09/02(水) 13:54
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.