柔軟に働き方を選ぶ時代に必要なこと >>

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

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

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


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


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

「集計表の作成(自動転記)」の質問画像

このQ&Aに関連する最新のQ&A

A 回答 (4件)

おじゃまします



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

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

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)」の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


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

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

続けてお邪魔します。



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
    • good
    • 0
この回答へのお礼

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

お礼日時:2014/07/31 17:58

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
「集計表の作成(自動転記)」の回答画像2

この回答への補足

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

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

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

補足日時:2014/07/29 17:45
    • good
    • 0

こんばんは!


画像のある「売上(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
「集計表の作成(自動転記)」の回答画像1

この回答への補足

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

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

補足日時:2014/07/29 09:03
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング