1241612447 【ExcelVBA】Book1の科目毎・タイトル毎に、Book2へ転記するマクロです。画像をご覧いただけますか。


≪Book1≫
科目ごとにシートが分かれています。
実際には、約50科目分あります。
シート名と[科目]列は、同じ文字列です。
[タイトル]の項目は、科目によってばらばらです。

≪Book2≫
給与毎・タイトル毎に、金額の合計を表示させます。



〔質問①〕
この作業をVBAに頼らずに行うとしたら、ピボットテーブルとVLOOKUPを使うのが効率のいい方法だと思ったので、
Book1内に、シート毎に新規シートにピボットテーブルを作成するマクロをつくるところまではできました。
しかし、どうやってVLOOKUPに持っていったらいいか全く見当がつきません。助けてください。



〔質問②〕
質問①のやりかたは邪道でしょうか。ほかにきれいなやりかたがあったら教えてください。
目的が達成できて、シンプルなコードであれば、①の方法にこだわりはありません。



下記①と②のどちらかでも構いませんので、ご教示いただけないでしょうか。よろしくお願いいたします。

回答の条件
  • 1人3回まで
  • 登録:2009/05/06 21:20:50
  • 終了:2009/05/13 21:25:02

ベストアンサー

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912009/05/06 23:38:01

ポイント35pt

どのような方法でも処理ができればよいと思いますので、

「質問①のやりかたは邪道」ということはないと思いますが、

マクロを使用した例です(ご期待に反していたらすみません)。


Book1 の標準モジュールに下記を置き、実行してみてください。

Option Explicit

'--------------------------------------------------------
Sub MakeSumBook()
'--------------------------------------------------------
    Dim srcWB As Workbook
    Set srcWB = ThisWorkbook
    
    Dim dstWB As Workbook
    Set dstWB = Workbooks.Add()
    
    Dim srcWS As Worksheet
    Dim dstWS As Worksheet
    Set dstWS = dstWB.Worksheets(1)

    dstWS.Range("A1:C1") = Array("科目", "タイトル", "タイトルごとの合計")
'--- 各シートごとに処理
    For Each srcWS In srcWB.Worksheets
        addWSSum srcWS, dstWS
    Next
End Sub

'--------------------------------------------------------
Sub addWSSum(srcWS As Worksheet, dstWS As Worksheet)
'--------------------------------------------------------
    Dim startRow As Long
    startRow = dstWS.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Dim lastRow As Long
    lastRow = srcWS.Range("C" & Rows.Count).End(xlUp).Row
    
    
'--- タイトルを検索
    Dim myDic As Object
    Set myDic = CreateObject("Scripting.Dictionary")
    
    Dim c As Variant
    For Each c In srcWS.Range("C2:C" & lastRow)
        If Not c = Empty Then
            If Not myDic.Exists(CStr(c)) Then
                myDic.Add CStr(c), Null
            End If
        End If
    Next
        
    Dim myKey As Variant
    myKey = myDic.Keys
    
    dstWS.Range("B" & startRow).Resize(myDic.Count) _
        = Application.WorksheetFunction.Transpose(myKey)
    Set myDic = Nothing
        
'--- タイトル毎に集計
    Dim dstLastRow
    dstLastRow = dstWS.Range("B" & Rows.Count).End(xlUp).Row
    
    Dim r As Long
    For r = startRow To dstLastRow
        dstWS.Cells(r, "A").Value = srcWS.Name
        dstWS.Cells(r, "C").Value = WorksheetFunction.SumIf( _
            srcWS.Range("C2").Resize(lastRow - 1, 1), _
            dstWS.Cells(r, "B").Value, _
            srcWS.Range("B2").Resize(lastRow - 1, 1))
    Next
    
'--- 小計列を追加
    dstWS.Cells(r, "A").Value = srcWS.Name
    dstWS.Cells(r, "B").Value = "小計"
    dstWS.Cells(r, "C").Formula = "=SUM( C" & startRow & ":C" & dstLastRow & ")"
End Sub
id:ykdmmm

連想配列!そんな便利ものがあるんですか。

クロス集計=ピボットテーブルしか思い浮かびませんでした。

理解できるよう、勉強します。

2009/05/07 07:45:12

その他の回答(1件)

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912009/05/06 23:38:01ここでベストアンサー

ポイント35pt

どのような方法でも処理ができればよいと思いますので、

「質問①のやりかたは邪道」ということはないと思いますが、

マクロを使用した例です(ご期待に反していたらすみません)。


Book1 の標準モジュールに下記を置き、実行してみてください。

Option Explicit

'--------------------------------------------------------
Sub MakeSumBook()
'--------------------------------------------------------
    Dim srcWB As Workbook
    Set srcWB = ThisWorkbook
    
    Dim dstWB As Workbook
    Set dstWB = Workbooks.Add()
    
    Dim srcWS As Worksheet
    Dim dstWS As Worksheet
    Set dstWS = dstWB.Worksheets(1)

    dstWS.Range("A1:C1") = Array("科目", "タイトル", "タイトルごとの合計")
'--- 各シートごとに処理
    For Each srcWS In srcWB.Worksheets
        addWSSum srcWS, dstWS
    Next
End Sub

'--------------------------------------------------------
Sub addWSSum(srcWS As Worksheet, dstWS As Worksheet)
'--------------------------------------------------------
    Dim startRow As Long
    startRow = dstWS.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Dim lastRow As Long
    lastRow = srcWS.Range("C" & Rows.Count).End(xlUp).Row
    
    
'--- タイトルを検索
    Dim myDic As Object
    Set myDic = CreateObject("Scripting.Dictionary")
    
    Dim c As Variant
    For Each c In srcWS.Range("C2:C" & lastRow)
        If Not c = Empty Then
            If Not myDic.Exists(CStr(c)) Then
                myDic.Add CStr(c), Null
            End If
        End If
    Next
        
    Dim myKey As Variant
    myKey = myDic.Keys
    
    dstWS.Range("B" & startRow).Resize(myDic.Count) _
        = Application.WorksheetFunction.Transpose(myKey)
    Set myDic = Nothing
        
'--- タイトル毎に集計
    Dim dstLastRow
    dstLastRow = dstWS.Range("B" & Rows.Count).End(xlUp).Row
    
    Dim r As Long
    For r = startRow To dstLastRow
        dstWS.Cells(r, "A").Value = srcWS.Name
        dstWS.Cells(r, "C").Value = WorksheetFunction.SumIf( _
            srcWS.Range("C2").Resize(lastRow - 1, 1), _
            dstWS.Cells(r, "B").Value, _
            srcWS.Range("B2").Resize(lastRow - 1, 1))
    Next
    
'--- 小計列を追加
    dstWS.Cells(r, "A").Value = srcWS.Name
    dstWS.Cells(r, "B").Value = "小計"
    dstWS.Cells(r, "C").Formula = "=SUM( C" & startRow & ":C" & dstLastRow & ")"
End Sub
id:ykdmmm

連想配列!そんな便利ものがあるんですか。

クロス集計=ピボットテーブルしか思い浮かびませんでした。

理解できるよう、勉強します。

2009/05/07 07:45:12
id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692009/05/07 10:59:16

ポイント35pt

>Book2の、A、B列は既に存在していて

となるとVBAを使わない数式での方法でもできそうです。


まず、Book1にBook2の集計シートをコピーしたものを作ります。

A列B列は既にあるので、D列を作業列として

C2の数式

=IF(B2="小計",SUM(INDIRECT("D"& IF(ISNA(MATCH("小計",INDIRECT("B1:B"&ROW()-1),0)+1),2,MATCH("小計",INDIRECT("B1:B"&ROW()-1),0)+1) &":D"&ROW()-1)),D2)

D2の数式

=SUM(IF(INDIRECT(A2&"!C2:C1000")=B2,INDIRECT(A2&"!B2:B1000"),0))

として、Ctrl+Shilft+Enterで{}で囲い配列数式にします。

(1000というのは各シートの最大行よりも大きい数字を指定します)

後は、C2とD2を下方向にコピーします。


最後にできたABC列をBook2に値をコピーしたり、リンク貼り付けなどをすればいいです。

id:ykdmmm

理解できるよう、勉強します。

2009/05/11 07:46:09
  • id:ykdmmm
    勉強不足で申し訳ありません。
    Book2の、A、B列は既に存在していて、C列に記入していく場合はどのようにしたらいいでしょうか。
  • id:Mook
    Book2のA列がBook1のシート名、B列がその中のタイトルがあるということでしょうか。
    その場合すでに、列挙されているタイトルとBook1の各シートのタイトルの過不足は考慮
    しなくてよいということでしょうか。

    であれば、集計部分だけの適用でできそうですね。
    仕様が確認できれば、修正を回答いたします。
  • id:ykdmmm
    申し訳ありません、まだお二人の回答を消化しきれていません。もう少しお待ちいただけないでしょうか。
  • id:SALINGER
    わかりづらい数式で申し訳ありません。
    D2の数式は配列数式を使ったものでわかりづらいですが、A列がそのままシート名になるのでこれだけで個々の合計が出ます。
    ただ、小計という行があるのでそれに対応させたのがC2の数式です。
    C2の数式では小計の範囲を求めてSUMで合計させています。
    配列数式についてはこちらを参考に。
    http://pc.nikkeibp.co.jp/pc21/special/hr/
  • id:ykdmmm
    >Book2のA列がBook1のシート名、B列がその中のタイトルがあるということでしょうか。

    その通りです。


    >その場合すでに、列挙されているタイトルとBook1の各シートのタイトルの過不足は考慮
    >しなくてよいということでしょうか。

    Book1は、ひと月に1つ発生します。
    Book2は、1年に1シート使用します。

    例えば、来月のBook1[給与]シートには、タイトル「B」は存在しないかもしれません。
    また、Book2にないタイトルが、Book1に入力されることはありません。
    (Book1への入力は、Book2のタイトルのリストをもとに、入力規則で行います。)



    ・・・ご質問の意図に沿っているでしょうか。おかしなところがありましたら、ご指摘ください。
  • id:Mook
    こんな感じでどうでしょうか。
    Book2 のシート名は連続していることを前提にしています。

    Option Explicit

    '--------------------------------------------------------
    Sub updateBook()
    '--------------------------------------------------------
    '---- ★★★集計ファイルを指定
      Dim dstWS As Worksheet
      Set dstWS = Workbooks("Book2.xls").Worksheets("集計")

      Dim srcWB As Workbook
      Set srcWB = ThisWorkbook
      
      Dim lastRow As Long
      lastRow = dstWS.Cells(Rows.Count, "A").End(xlUp).Row

      Dim srcLastRow As Long
      Dim r As Long
      Dim mr As Long
      Dim pws As String
      For r = 2 To lastRow
        With srcWB.Worksheets(dstWS.Cells(r, "A").Value)
          If pws <> dstWS.Cells(r, "A").Value Then
            pws = dstWS.Cells(r, "A").Value
            mr = r
          End If
          srcLastRow = .Cells(Rows.Count, "B").End(xlUp).Row
          If dstWS.Cells(r, "B").Value = "小計" Then
            dstWS.Cells(r, "C").Formula = "=SUM(C" & mr & ":C" & r - 1 & ")"
          Else
            dstWS.Cells(r, "C").Value = WorksheetFunction.SumIf( _
              .Range("C2").Resize(srcLastRow - 1, 1), _
              dstWS.Cells(r, "B").Value, _
              .Range("B2").Resize(srcLastRow - 1, 1))
          End If
        End With
      Next
    End Sub
  • id:ykdmmm
    Mook様、SALINGER様

    ご回答いただき、ありがとうございました。

    SALINGER様の数式を急ぎの報告で使用し、
    Mook様のマクロで、無事完成させることができました!

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません