集計表の作成（自動転記）

質問

集計表への自動転記の方法を教えてください。
方法は可能であれば関数を用いた方法でお願い致します。
マクロを使用して作成する場合でも構いません。

※1枚目の画像
完成後の集計表となります。
データ一覧（CSV)より、データを集計し、結果を転記する。

売上(2)については、指定のセルを転記するのではなく、
別にある対象リストに該当するもののみを集計し、転記する。

※2枚目の画像
データ一覧（CSV)です。

よろしくお願い致します。

回答No.4

30246kiku

おじゃまします

> マクロを使用して作成する場合でも構いません。
という事なので、考えてみました。

以下に記述しますが、変更場所を局所化してみました。
変更する場所は

vA = Array( _
　　　　Array("取引先"), _
　　　　Array("売上", "売上", ""), _
　　　　Array("売上(1)～(3)", "売上(1),売上(2),売上(3)", ""), _
　　　　Array("売上(1)～(3)-(4)", "売上(1),売上(2),売上(3)", "売上(4)"), _
　　　　Array("売上(2)", "売上(2),売上(3),売上(4)", ""), _
　　　　Array("売上(3)", "売上(3),売上(4)", ""), _
　　　　Array("売上(4)", "売上(1),売上(2),売上(3),売上(4)", "") _
　　)

の所だけです。

　　vA = Array( _
　　　　　　Array("取引先"), _

部分は固定で、それ以降環境に合わせて変更していきます。
意味的には

　　　　　　Array("売上(1)～(3)", "売上(1),売上(2),売上(3)", ""), _

なら、列見出しは「売上(1)～(3)」で、
「売上(1)」,「売上(2)」,「売上(3)」の３つを加算
（名称に出てくる文字列をカンマ区切りで）
その後の "" で、減算するものをカンマ区切りで・・・　なければ ""

　　　　　　Array("売上(1)～(3)-(4)", "売上(1),売上(2),売上(3)", "売上(4)"), _

は、売上(1),売上(2),売上(3)を加算して、売上(4)を減算

提示された図での設定では、

　　vA = Array( _
　　　　　　Array("取引先"), _
　　　　　　Array("売上", "売上", ""), _
　　　　　　Array("売上(2)", "売上(1),売上(2),売上(3)", "") _
　　　　)

とでも設定すれば良いのでしょうか？
※※ 投稿で○数字がカッコ数字に変わるみたいです

Public Sub Samp1()
　　Dim dic As Object, dicW As Object
　　Dim vA As Variant, vD As Variant
　　Dim vK As Variant, v As Variant
　　Dim i As Long, j As Long, k As Long

　　vA = Array( _
　　　　　　Array("取引先"), _
　　　　　　Array("売上", "売上", ""), _
　　　　　　Array("売上(1)～(3)", "売上(1),売上(2),売上(3)", ""), _
　　　　　　Array("売上(1)～(3)-(4)", "売上(1),売上(2),売上(3)", "売上(4)"), _
　　　　　　Array("売上(2)", "売上(2),売上(3),売上(4)", ""), _
　　　　　　Array("売上(3)", "売上(3),売上(4)", ""), _
　　　　　　Array("売上(4)", "売上(1),売上(2),売上(3),売上(4)", "") _
　　　　)

　　Application.ScreenUpdating = False
　　Set dicW = CreateObject("Scripting.Dictionary")
　　For i = 1 To UBound(vA)
　　　　k = 1
　　　　For j = 1 To 2
　　　　　　For Each vK In Split(vA(i)(j), ",")
　　　　　　　　v = dicW(vK)
　　　　　　　　If (IsArray(v)) Then
　　　　　　　　　　ReDim Preserve v(UBound(v) + 1)
　　　　　　　　Else
　　　　　　　　　　ReDim v(0)
　　　　　　　　End If
　　　　　　　　v(UBound(v)) = i * k
　　　　　　　　dicW(vK) = v
　　　　　　Next
　　　　　　k = -1
　　　　Next
　　Next
　　Set dic = CreateObject("Scripting.Dictionary")
　　vD = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Resize(, 4)
　　For i = 1 To UBound(vD)
　　　　If (Not dic.Exists(vD(i, 1))) Then
　　　　　　dic.Add vD(i, 1), CreateObject("Scripting.Dictionary")
　　　　End If
　　　　vK = dicW(vD(i, 2))
　　　　If (IsArray(vK)) Then
　　　　　　For j = 0 To UBound(vK)
　　　　　　　　k = Abs(vK(j))
　　　　　　　　v = dic(vD(i, 1))(k)
　　　　　　　　If (Not IsArray(v)) Then ReDim v(1)
　　　　　　　　If (vK(j) > 0) Then
　　　　　　　　　　v(0) = v(0) + vD(i, 3)
　　　　　　　　　　v(1) = v(1) + vD(i, 4)
　　　　　　　　Else
　　　　　　　　　　v(0) = v(0) - vD(i, 3)
　　　　　　　　　　v(1) = v(1) - vD(i, 4)
　　　　　　　　End If
　　　　　　　　dic(vD(i, 1))(k) = v
　　　　　　Next
　　　　End If
　　Next

　　Worksheets.Add
　　Cells(1, 1) = vA(0)(0)
　　For i = 1 To UBound(vA)
　　　　With Cells(1, i * 2)
　　　　　　.Value = vA(i)(0)
　　　　　　.Offset(, 1).Value = "販売数"
　　　　End With
　　Next
　　i = 2
　　For Each vK In dic.Keys
　　　　Cells(i, 1).Value = vK
　　　　For Each v In dic(vK).Keys
　　　　　　Cells(i, v * 2).Resize(, 2) = dic(vK)(v)
　　　　Next
　　　　i = i + 1
　　Next
　　With Cells(1.1).CurrentRegion
　　　　With .Rows(1)
　　　　　　.Interior.ColorIndex = 15
　　　　　　.HorizontalAlignment = xlCenter
　　　　End With
　　　　.Columns(1).HorizontalAlignment = xlCenter
　　　　.Borders.LineStyle = xlContinuous
　　　　.EntireColumn.AutoFit
　　End With
　　Set dicW = Nothing
　　Set dic = Nothing
　　Application.ScreenUpdating = True
End Sub

※ 処理対象はアクティブシートで、結果はシートを追加して表示します。

※ 不具合/不都合あれば、修正してください。

回答No.3

tom04

続けてお邪魔します。

No.2の補足
＞画像ではSheet2のB2セルに
＞=SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B$1)
＞こちらの条件で「売上」とすると、データ一覧から「売上」だけではなく、「売上高」を合算してしまいます。

前回の数式はSheet2のB1セルの項目名（売上）というものを
SUMIFS関数の「条件範囲2」の「条件2」をSheet1のB1セル（項目名の「売上」）にしていました。
もしかして、Sheet2のB1セル項目名が「売上高」になっていませんか？

それはさておき、「売上」だけを表示したい場合はSheet2のB2セルの数式を
=SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,"売上")
としてみてください。

※　アップされている画像通りにSheet2の数値が表示されるためには
Sheet1のB列が「売上」となっている行だと判断しての数式です。
（売上-A・売上-B　などは除外します）

今度はどうでしょうか？m(_ _)m

お礼

ご回答ありがとうございます。
確認したところ、セル項目名が「売上高」となっていました。。
無事、データ抽出することが出来ました！
ありがとうございますm(_ _)m

回答No.2

tom04

No.1です。
補足の
＞「売上-○」とつくものが30個以近くある場合は・・・
について、

とりあえず前回の画像でSheet2のD・E列だけでやってみました。
一番簡単なのは作業用の列を設ける方法だと思います。

↓の画像でSheet2に集計したいデータをG列に羅列しておきます。
そしてSheet1の作業列F2セルに
=IF(COUNTIF(Sheet2!G:G,B2),1,"")
という数式を入れフィルハンドルでずぃ～～～！っと下へコピー！
これを利用して、Sheet2のD2セルに
=SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2,Sheet1!$F:$F,1)
という数式を入れ、となりのE2セルまでコピー　→　そのまま下へコピー！
これで画像のような感じになります。

※　今回はSheet2のA・B列には手を加えていませんが、
（A・B列はSheet1の「取引先」別の「売上」と「販売数」だけを表示すれば良いと思いますので）
A・B列も同様のコトをしたい場合は
作業列を増やすのが簡単だと思います。

※　作業列が目障りであれば非表示にしておきます。

こんな感じではどうでしょうか？m(_ _)m

補足

ご回答ありがとうございます。

何度も申し訳ありません。。
もう1点確認させてください。

＞画像ではSheet2のB2セルに
＞=SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B$1)
こちらの条件で「売上」とすると、データ一覧から「売上」だけではなく、「売上高」を合算してしまいます。
こちらは「売上」と完全に一致するデータのみ抽出することは可能でしょうか？

回答No.1

tom04

こんばんは！
画像のある「売上(2)」の右側列「販売数」は何を表示させれば良いか判らないのですが、
とりあえず、売上(1)～売上(3)の「販売数」を表示するようにしてみました。

↓の画像で左側が元データの「Sheet1」とし、右側の「Sheet2」に表示するとします。

画像ではSheet2のB2セルに
=SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B$1)
という数式を入れ、隣りのC2セルまでフィルハンドルでコピー！
続いてD2セルに
=SUM(SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,"売上(1)"),SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,"売上(2)"),SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,"売上(3)"))
という数式を入れこれも隣りのE2セルまでコピー！
最後にB2～E2セルを範囲指定　→　E2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　実際の計算がお示しの画像の数値と異なるので、間違っていたらごめんなさいね。m(_ _)m

補足

ご回答ありがとうございます。
大変助かりましたm(_ _)m

追加でご質問よろしいでしょうか？
名称の「売上-A」「売上-B」etc　の合計値の計算式ですが、「売上-○」とつくものが30個以近くある場合は、上記のような計算式ですと、長くなってしまうので、該当する対象リストを作成し、参照し、集計対象とする方法はありますでしょうか？