[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『コードを基準に分析表を作成』（すもも）
取引先別に売掛金の比較表を作成します
売掛金の表はシート2で作成してます
シート4にシート2の種別コードと取引先コードを照合し、
それらと同一行に入力されている金額をシート4で比較したいです
またコードがなければシート4に行を追加します
（シート4の6行目と7行目は追加してます）
シート2
A B C D E F G H I J K
1
2 8月
3 種別コード 種別 取引先コード 取引先 売掛金 値引き クレーム 修正前 修正
4 111 あ 789 きき 100 10 20
5 111 あ 456 くく 200 100
6 222 い 789 けけ 300 50
7 333 う 123 ここ 400
シート4
A B C D E F G H I J K L M N O
1
2 6月 8月
3 種別コード 種別 取引先コード 取引先 売掛金 値引き クレーム 修正前 修正 売掛金 値引き クレーム 修正前 修正
4 111 あ 789 きき 500 50 100 10 20
5 111 あ 123 ここ 200
6 111 あ 456 くく 200 100
7 222 い 789 けけ 300 50
8 333 う 123 ここ 200 400
9 444 え 456 くく 150 10
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
やりたいことがわからないです。 １）表の列位置と数値の紐づけがわからないので、下記でよいか確認してください。 ２）「比較」とは具体的に何をするのですか？ ３）シート２の種別コードと取引先コードがシート４と一致した場合、どこに何を転記すればいいですか？ ４）シート２の種別コードと取引先コードがシート４と一致しない場合、どの順番で行を追加すればいいですか？
シート２
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I]
[1]| | | | | | | | |
[2]|8月 | | | | | | | |
[3]|種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正
[4]| 111|あ | 789|きき | 100| 10| 20| |
[5]| 111|あ | 456|くく | 200| 100| | |
[6]| 222|い | 789|けけ | 300| 50| | |
[7]| 333|う | 123|ここ | 400| | | |
シート４
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O]
[1]| | | | | | | | | | | | | | |
[2]|6月 |8月 | | | | | | | | | | | | |
[3]|種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正|売掛金|値引き|クレーム|修正前|修正|
[4]| 111|あ | 789|きき | 500| 50| 100| 10| 20| | | | | |
[5]| 111|あ | 123|ここ | 200| | | | | | | | | |
[6]| 111|あ | 456|くく | 200| 100| | | | | | | | |←追加行？
[7]| 222|い | 789|けけ | 300| 50| | | | | | | | |←追加行？
[8]| 333|う | 123|ここ | 200| 400| | | | | | | | |
[9]| 444|え | 456|くく | 150| 10| | | | | | | | |
（稲葉） 2020/08/11(火) 13:13
ちょっとやり取りしている時間が無くなりそうなので、たたき台だけ・・・ 大前提：シート４は種別コードで並び替えがされている。
シート２にあって、シート４にないものは、種別コードが切り替わるところに追加 シート２にあって、シート４にあるものは、シート２のE:G列 を シート４のJ:L列に転記 シート２にあって、シート４にない、種別コードは、シート４の一番最後に付け足す →例示の7行目。 必要があれば、あとで種別コードで並び替えしてください。
出力結果が以下（例示の６，７行目を抜いたあと実行）
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O]
[1]| | | | | | | | | | | | | | |
[2]|6月 |8月 | | | | | | | | | | | | |
[3]|種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正|売掛金|値引き|クレーム|修正前|修正|
[4]| 111|あ | 456|くく | 200| 100| | | | | | | | |
[5]| 111|あ | 789|きき | 500| 50| | | | 100| 10| 20| | |
[6]| 111|あ | 123|ここ | 200| | | | | | | | | |
[7]| 333|う | 123|ここ | 200| 400| | | | 400| | | | |
[8]| 444|え | 456|くく | 150| 10| | | | | | | | |
[9]| 222|い | 789|けけ | 300| 50| | | | | | | | |
Sub sumomo()
Dim ws売掛金 As Worksheet
Dim ws比較表 As Worksheet
Dim dic As Object
Dim 種別CD As String
Dim 取引先CD As String
Dim i As Long
Dim k As Variant
Dim kk As Variant
Set ws売掛金 = Sheets("Sheet2")
Set ws比較表 = Sheets("Sheet4")
Set dic = CreateObject("Scripting.Dictionary")
'売掛金データを上から順に登録する
With ws売掛金
For i = 4 To .Cells(Rows.Count, "A").End(xlUp).Row
種別CD = .Cells(i, "A").Value
取引先CD = .Cells(i, "C").Value
If Not dic.exists(種別CD) Then Set dic(種別CD) = CreateObject("Scripting.dictionary")
dic(種別CD)(取引先CD) = i
Next i
End With
'比較表と売掛金を照らし合わせる
With ws比較表
For i = .Cells(Rows.Count, "A").End(xlUp).Row To 4 Step -1
種別CD = .Cells(i, "A").Value
取引先CD = .Cells(i, "C").Value
If dic.exists(種別CD) Then
If dic(種別CD).exists(取引先CD) Then
'重複有
.Range("J:L").Rows(i).Value = ws売掛金.Range("E:G").Rows(dic(種別CD)(取引先CD)).Value
dic(種別CD).Remove (取引先CD)
End If
If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then
'種別コードが切り替わる場合（例：3行目と2行目を比較して、異なる場合）
'同じ種別コードを、売掛金の登録順に追加する
For Each k In dic(種別CD).keys
.Rows(i).Insert
.Range("A:G").Rows(i).Value = ws売掛金.Range("A:G").Rows(dic(種別CD)(k)).Value
Next k
dic.Remove (種別CD)
End If
End If
Next i
If dic.Count > 0 Then
'売掛金にあって、比較表にない種別コード
'売掛金の登録順に追加する
For Each kk In dic
For Each k In dic(kk)
.Range("A:G").Rows(.Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Value = ws売掛金.Range("A:G").Rows(dic(kk)(k)).Value
Next k
Next kk
End If
End With
Set dic = Nothing
End Sub
（稲葉） 2020/08/11(火) 14:37
シート2で6月と8月と9月を作成して表にする場合
シート2（6月作成）
|[C]|[D]|[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M]
[5]|6月| |種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正
[6]| | | 111|あ | 123|けけ | 100| | | | 100
[7]| | | 111|あ | 789|きき | 200| | | | 200
[8]| | | 333|う | 456|くく | 300| | | | 300
[9]| | | 333|う | 123|けけ | | | | |
シート4
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I]
[4] | | | | |6月 | | | |
[5] |種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正
[6] | 111|あ | 123|けけ | 100| | | | 100
[7] | 111|あ | 456|くく | | | | |
[8] | 111|あ | 789|きき | 200| | | | 200
[9] | 222|い | 789|きき | | | | |
[10]| 333|う | 123|けけ | | | | |
[11]| 333|う | 456|くく | 300| | | | 300
[12]| 333|う | 789|きき | | | | |
シート2（8月作成）
|[C]|[D]|[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M]
[5] |8月| |種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正
[6] | | | 111|あ | 789|きき | 10| | | | 10
[7] | | | 111|あ | 456|くく | 20| | | | 20
[8] | | | 222|い | 789|きき | 30| | | | 30
[9] | | | 333|う | 123|けけ | 40| | | | 40
[10]| | | 333|う | 789|きき | 50| | | | 50
シート4はこうなります
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O]
[4] | | | | |6月 | | | | |8月 | | | | |
[5] |種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正|売掛金|値引き|クレーム|修正前|修正|
[6] | 111|あ | 123|けけ | 100| | | | 100| | | | | |
[7] | 111|あ | 456|くく | | | | | | 20| | | | 20|⇒追加
[8] | 111|あ | 789|きき | 200| | | | 200| 10| | | | 10|
[9] | 222|い | 789|きき | | | | | | 30| | | | 30|⇒追加
[10]| 333|う | 123|けけ | | | | | | 40| | | | 40|
[11]| 333|う | 456|くく | 300| | | | 300| | | | | |
[12]| 333|う | 789|きき | | | | | | 50| | | | 50|⇒追加
（すもも） 2020/08/12(水) 08:59
シート2（9月作成）
|[C]|[D]|[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M]
[5]|9月| |種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正
[6]| | | 111|あ | 456|くく | 1000| 1000| 1000| 1000|1000
[7]| | | 222|い | 123|けけ | 2000| 2000| 2000| 2000|2000
シート4はこうなります
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T]
[4] | | | | |6月 | | | | |8月 | | | | |9月 | | | | |
[5] |種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正|売掛金|値引き|クレーム|修正前|修正|売掛金|値引き|クレーム|修正前|修正|
[6] | 111|あ | 123|けけ | 100| | | | 100| | | | | | | | | | |
[7] | 111|あ | 456|くく | | | | | | 20| | | | 20| 1000| 1000| 1000| 1000|1000|
[8] | 111|あ | 789|きき | 200| | | | 200| 10| | | | 10| | | | | |
[9] | 222|い | 123|けけ | | | | | | | | | | | 2000| 2000| 2000| 2000|2000|⇒追加
[10]| 222|い | 789|きき | | | | | | 30| | | | 30| | | | | |
[11]| 333|う | 123|けけ | | | | | | 40| | | | 40| | | | | |
[12]| 333|う | 456|くく | 300| | | | 300| | | | | | | | | | |
[13]| 333|う | 789|きき | | | | | | 50| | | | 50| | | | | |
（すもも） 2020/08/12(水) 09:02
＞やりたいことがわからないです。 ＞１）表の列位置と数値の紐づけがわからないので、下記でよいか確認してください。 ＞２）「比較」とは具体的に何をするのですか？ ＞３）シート２の種別コードと取引先コードがシート４と一致した場合、どこに何を転記すればいいですか？ ＞４）シート２の種別コードと取引先コードがシート４と一致しない場合、どの順番で行を追加すればいいですか？
言葉で説明するの難しいですね
１）表は上で投稿したとおりです ２）「比較」とは、 取引先ごとにシート4の表に従って金額を比較します シート4の表の枠組みは既に作成してあって、4行目に一致する6月、8月、9月の表の場所に金額を入力します
シート2のI列〜M列とシート4の5行目の比較する項目（見出し）は一緒です
↓
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S]
[4]| | | | |6月 | | | | |8月 | | | | |9月 | | | |
[5]|種別コード|種別|取引先コード|取引先|売掛金|値引き|クレーム|修正前|修正|売掛金|値引き|クレーム|修正前|修正|売掛金|値引き|クレーム|修正前|修正
３）シート２の種別コードと取引先コードがシート４と一致した場合、
シート2のC5（6月、8月、9月等）と一致する、シート4の4行目の表のコードが一致している行に金額を入力します
8月の場合、シート2の種別コード「111」、取引先コード「789」の売掛金「10」をシート4の種別コードと取引先コードが
一致する8行目の4行目に「8月」とあるJ列からN列までの間の5行目にある売掛金「J列」に入力する
４）シート２の種別コードと取引先コードがシート４と一致しない場合、
コードで最優先されるキーは第1位種別コード、第2位取引先コードです
8月の場合、シート4の種別コード「111」、取引先コード「456」は、種別コード「111」、取引先コード「123」の下の行に
追加します
（すもも） 2020/08/12(水) 10:03
作成月 種別コード 種別 取引先コード 取引先 売掛金 値引き クレーム 修正前 修正 6月 111 あ 123 けけ 100 100 6月 111 あ 789 きき 200 200 6月 333 う 456 くく 300 300 8月 111 あ 789 きき 10 10 8月 111 あ 456 くく 20 20 8月 222 い 789 きき 30 30 8月 333 う 123 けけ 40 40 8月 333 う 789 きき 50 50
（マナ） 2020/08/12(水) 12:00
マナさんの意見に賛成です （稲葉） 2020/08/12(水) 12:40
詳しく教えてもらっていいですか
こないだピポッドテーブルのやり方は勉強したばかりです
ピポッドテーブルをマクロでできるのですか？？
（すもも） 2020/08/12(水) 12:46
Sub main()
Dim dic As Object, c As Range, r As Range, rr As Range, w2 As Worksheet, w4 As Worksheet
Set w2 = Sheets("Sheet2")
Set w4 = Sheets("Sheet4")
Set dic = CreateObject("Scripting.Dictionary")
For Each c In w2.Range("E5:E" & Rows.Count).SpecialCells(2)
Set dic(c.Value & Chr(2) & c.Offset(, 1).Value & Chr(2) & c.Offset(, 2).Value & Chr(2) & c.Offset(, 3).Value) = c.Offset(, 4).Resize(, 5)
Next c
Set r = w4.Cells(5, Columns.Count).End(xlToLeft).Offset(, 1)
For Each c In w4.Range("A5:A" & Rows.Count).SpecialCells(2)
If dic.exists(c.Value & Chr(2) & c.Offset(, 1).Value & Chr(2) & c.Offset(, 2).Value & Chr(2) & c.Offset(, 3).Value) Then
dic(c.Value & Chr(2) & c.Offset(, 1).Value & Chr(2) & c.Offset(, 2).Value & Chr(2) & c.Offset(, 3).Value).Copy _
w4.Cells(c.Row, r.Column)
dic.Remove (c.Value & Chr(2) & c.Offset(, 1).Value & Chr(2) & c.Offset(, 2).Value & Chr(2) & c.Offset(, 3).Value)
End If
Next c
For Each c In w2.Range("E5:E" & Rows.Count).SpecialCells(2)
If dic.exists(c.Value & Chr(2) & c.Offset(, 1).Value & Chr(2) & c.Offset(, 2).Value & Chr(2) & c.Offset(, 3).Value) Then
Set rr = w4.Range("A" & Rows.Count).End(xlUp).Offset(1)
c.Resize(, 4).Copy rr
dic(c.Value & Chr(2) & c.Offset(, 1).Value & Chr(2) & c.Offset(, 2).Value & Chr(2) & c.Offset(, 3).Value).Copy _
w4.Cells(rr.Row, r.Column)
End If
Next c
r.Offset(-1).Value = w2.Range("C5").Value
End Sub
（mm） 2020/08/12(水) 12:52
これだとシート４のA列からD列までで金額が入力されなかったです
しかもコードで最優先されるキーは第1位種別コード、第2位取引先コードなんです
ピポッドテーブルのやり方興味あります
教えてくれませんか？？
（すもも） 2020/08/12(水) 14:15
（すもも） 2020/08/12(水) 14:48
手作業です。それでお望みのレイアウトと違うなら、ピボット案はなしです。
（マナ） 2020/08/12(水) 17:52
ピボットテーブルで集計した場合の表はこんな感じになります。
6月のみ
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I]
[3]| | | | |列ラベル| | | |
[4]| | | | |6月 | | | |
[5]|行ラベル|種別|取引先コード|取引先|売掛金_ |値引き_|クレーム_|修正前_|修正_
[6]| 111|あ | 123|けけ | 100| 0| 0| 0| 100
[7]| 111|あ | 789|きき | 200| 0| 0| 0| 200
[8]| 333|う | 123|けけ | 0| 0| 0| 0| 0
[9]| 333|う | 456|くく | 300| 0| 0| 0| 300
8月含める
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N]
[1] | | | | | | | | | | | | | |
[2] | | | | | | | | | | | | | |
[3] | | | | |列ラベル| | | | | | | | |
[4] | | | | |6月 | | | | |8月 | | | |
[5] |行ラベル|種別|取引先コード|取引先|売掛金_ |値引き_|クレーム_|修正前_|修正_|売掛金_|値引き_|クレーム_|修正前_|修正_
[6] | 111|あ | 123|けけ | 100| 0| 0| 0| 100| | | | |
[7] | 111|あ | 456|くく | | | | | | 20| 0| 0| 0| 20
[8] | 111|あ | 789|きき | 200| 0| 0| 0| 200| 10| 0| 0| 0| 10
[9] | 333|う | 123|けけ | 0| 0| 0| 0| 0| 40| 0| 0| 0| 40
[10]| 333|う | 456|くく | 300| 0| 0| 0| 300| | | | |
[11]| 333|う | 789|きき | | | | | | 50| 0| 0| 0| 50
[12]| 222|い | 789|きき | | | | | | 30| 0| 0| 0| 30
マナさんの提案通りの累積シートを使っています。 累積まではマクロで、ピボットテーブルは一度作成すれば、あとは更新するだけで自動で集計してくれます。
差し支えなければ、シート５を作成してください。
１）累積マクロ シート２のデータをシート５に追加していく
Sub sumomo_take2()
'シート5に月別の累積データ作成
Dim ws月別表 As Worksheet
Dim ws累積表 As Worksheet
Dim コピー範囲 As Range
Set ws月別表 = Sheets("Sheet2")
Set ws累積表 = Sheets("Sheet5")
ws累積表.[A1:J1] = [{"作成月","種別コード","種別","取引先コード","取引先","売掛金","値引き","クレーム","修正前","修正"}]
Set コピー範囲 = ws月別表.Range("M6", ws月別表.Cells(Rows.Count, "E").End(xlUp))
With ws累積表.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(コピー範囲.Rows.Count).Rows
.Columns("A").Value = ws月別表.Range("C5").Value
.Columns("B:J").Value = コピー範囲.Value
End With
MsgBox ws月別表.Range("C5").Value & "月の登録が完了しました"
End Sub
２）累積データに名前を付ける http://office-qa.com/Excel/ex203.htm 名前 ：PV範囲 参照範囲：=OFFSET(Sheet5!$A$1:$J$1,0,0,COUNTA(Sheet5!$A:$A))
３）ピボットテーブルを作成する ａ）リボンの 挿入＞ピボットテーブル ｂ）表示されたダイヤログのテーブル/参照に「PV範囲」 ※２）で作成した範囲をセット 新しいシートを作成とする（シート４を上書きしない様に） ｃ）フィールドリストに下記の項目を、順番通りに置いていく。 列：作成月 行：種別コード、種別、取引先コード、取引先 値：売掛金、値引き、クレーム、修正前、修正 ※この時点では、全然形になってません。
ｄ）見やすいように成形する 項目名 セルに戻って、 データの個数 / 売掛金 を右クリックし、値フィールドの設定をクリック 値フィールドの集計 を データの個数→合計に変更 名前の指定 を データの個数 / 売掛金 → 売掛金_ ※アンダーバー入れてます 同じ手順で、値フィールドに入れた「修正」まで設定する
ｅ）見やすいように成形する 種別コード等 A列 行ラベルの下に、111（種別コード）を右クリック フィールドの設定をクリック 小計を なし レイアウトと印刷タブ をクリックし、 「アイテムのラベルを表形式で表示する」にチェック 「アイテムのラベルを繰り返す」にチェック
B列が追加され、あ（種別） を右クリックし、上記手順を「取引先」まで繰り返す
ｆ）総計が不要の場合 ピボットテーブルの一番下の総計を右クリックして、「総計の削除」 ピボットテーブルの右上の「全体の 売掛金_」を右クリックして、「総計の削除」
これでできます。
（稲葉） 2020/08/13(木) 10:10
シート5を変えました
ws累積表.[A1:K1] = [{"作成年","対象","種別コード","種別","取引先コード","取引先","売掛金","値引き","クレーム","修正前","修正"}]
累積表のC列から貼り付けるにはどこを変えたらいいのでしょうか？？
（すもも） 2020/08/17(月) 13:08
なぜ？ A列からで十分でしょう。 この表は普段見ないわけですから。
まずは指示通り実行して、出来たかどうかだけ返事ください。 （稲葉） 2020/08/17(月) 13:40
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.