Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


9600 / 13646 ツリー ←次へ | 前へ→

【26368】効率的なコードにするには…。 あさみ 05/7/2(土) 23:54 質問[未読]
【26370】Re:効率的なコードにするには…。 かみちゃん 05/7/3(日) 11:30 発言[未読]
【26371】Re:効率的なコードにするには…。 あさみ 05/7/3(日) 12:14 お礼[未読]
【26373】Re:効率的なコードにするには…。 かみちゃん 05/7/3(日) 14:36 発言[未読]
【26375】Re:効率的なコードにするには…。 あさみ 05/7/3(日) 20:32 お礼[未読]
【26380】Re:効率的なコードにするには…。 あさみ 05/7/4(月) 1:00 質問[未読]
【26387】Re:効率的なコードにするには…。 かみちゃん 05/7/4(月) 12:52 発言[未読]
【26416】Re:効率的なコードにするには…。 あさみ 05/7/5(火) 2:05 発言[未読]
【26417】Re:効率的なコードにするには…。 かみちゃん 05/7/5(火) 6:39 発言[未読]
【26465】Re:効率的なコードにするには…。 あさみ 05/7/6(水) 7:16 発言[未読]
【26487】Re:効率的なコードにするには…。 かみちゃん 05/7/6(水) 22:53 発言[未読]
【26372】Re:効率的なコードにするには…。 Hirofumi 05/7/3(日) 14:01 回答[未読]
【26374】Re:効率的なコードにするには…。 Hirofumi 05/7/3(日) 17:59 回答[未読]
【26376】Re:効率的なコードにするには…。 あさみ 05/7/3(日) 20:34 お礼[未読]
【26377】Re:効率的なコードにするには…。 Hirofumi 05/7/3(日) 20:54 回答[未読]
【26379】Re:効率的なコードにするには…。 あさみ 05/7/3(日) 22:19 お礼[未読]

【26368】効率的なコードにするには…。
質問  あさみ  - 05/7/2(土) 23:54 -

引用なし
パスワード
   はじめまして。あさみと申します。
db.xls(データベース)から、条件に当てはまるセルの集計を、d.xls(集計表)に反映させたいと考えています。

何とか自分の力で…!と思い、色々な本やサイトを参考にしてみたのですが、自分で考えた方法では、あまりにも処理時間がかかるので、もうすこし効率的なコードにならないかなと思い、こちらに質問させていただきました。

長々ともうしわけありませんが、下記にデータベースの内容と、コードを記入させていただきましたので、有志の皆様、是非お力をおかしください…!

また、下記に記入させていただいたコードは、db.xlsとd.xlsともに、("2005.6")用になっていて、できれば同じコードを使い、Worksheets名を("2005.7")に変えて流用できれば…と思うのですが、思うように反映されません…。
何か良い方法がありましたら、是非ご教授くださいませ。

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

【Book1(データベース)】
  A   B     C   D
1 得意先 営業部門  商品 受注総額
2 AAA     京都      ○     100
3 AAA     京都      ■     200
4 AAA   京都      △     300
5 AAA     大阪      ○     400
6 AAA     大阪      ■     500
7 AAA     大阪      △     500
8 AAA     神戸      ○     400
9 AAA     神戸      ■     300
10 AAA     神戸      △     200
11 BBB     京都      ○     100
12 BBB     京都      ■     600
13 BBB     京都      △     700
14 BBB     大阪      ○     800
15 BBB     大阪      ■     900
(以下続く)


【コード】
'2005.6
Sub 得意先AAAデータ取得6()
''ブック&シート指定
  Dim wbs As Worksheet
  Dim destination As Worksheet
  Application.ScreenUpdating = False
  Set wbs = Workbooks.Open(“C:\Documents and Settings\質問\db.xls").Worksheets("2005.6")
  Set destination = Workbooks("d.xls").Worksheets("2005.6")
  
''AAA&京都&○
With Range("D1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="AAA"
.AutoFilter Field:=2, Criteria1:="京都"
.AutoFilter Field:=3, Criteria1:="○"
End With
Range("D84").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-82]C:R[-1]C)"
   
コピペ6 wbs.Range("D84"), destination.Range("B3")

''AAA&大阪&○
With Range("D1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="AAA"
.AutoFilter Field:=2, Criteria1:="大阪"
.AutoFilter Field:=3, Criteria1:="○"
End With
Range("D84").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-82]C:R[-1]C)"
   
コピペ6 wbs.Range("D84"), destination.Range("C3")

''AAA&神戸&○
With Range("D1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="AAA"
.AutoFilter Field:=2, Criteria1:="神戸"
.AutoFilter Field:=3, Criteria1:="○"
End With
Range("D84").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-82]C:R[-1]C)"
   
コピペ6 wbs.Range("D84"), destination.Range("D3")

''AAA&京都&■
With Range("D1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="AAA"
.AutoFilter Field:=2, Criteria1:="京都"
.AutoFilter Field:=3, Criteria1:="■"
End With
Range("D84").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-82]C:R[-1]C)"
   
コピペ6 wbs.Range("D84"), destination.Range("B4")

(中略)


''作業後に参照元シートを閉じる
wbs.Parent.Close False
Application.ScreenUpdating = True
Set wbs = Nothing
Set destination = Nothing
End Sub

Private Sub コピペ6(ByVal コピー元 As Range, ByVal コピー先 As Range)
  コピー元.Copy
  コピー先.PasteSpecial Paste:=xlPasteValues
  Application.CutCopyMode = False
End Sub

本当に長々ともうしわけありません。
よろしくお願いいたします。

【26370】Re:効率的なコードにするには…。
発言  かみちゃん E-MAIL  - 05/7/3(日) 11:30 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>db.xls(データベース)から、条件に当てはまるセルの集計を、d.xls(集計表)に反映させたいと考えています。

元のデータ、今考えているコードまで載せていただいているのですが、もうひとつ
どういう結果をお望みなのかがわかりません。

サンプルシートを作って検証してみましたが、得意先別(AAAのみでいいのか)、営業部門別、商品別に金額の集計をしたいということでしたら、ピボットテーブル
で手集計ができないでしょうか?
もし、手集計ができるならば、それを「マクロの記録」で記録してみてはいかがでしょうか?

>また、下記に記入させていただいたコードは、db.xlsとd.xlsともに、("2005.6")用になっていて、できれば同じコードを使い、Worksheets名を("2005.7")に変えて流用できれば…と思うのですが、思うように反映されません…。

これは、マクロの先頭で、対象シート名を選択させる、システム日付で処理対象シートを判断させるなどいろいろできます。

※たぶん、私も似たようなことを仕事でしているので、何かお手伝いできるのでは
 と思っています。

【26371】Re:効率的なコードにするには…。
お礼  あさみ  - 05/7/3(日) 12:14 -

引用なし
パスワード
   はじめまして、かみちゃん様。早速のご教授、ありがとうございます。
説明不足で、明確な「望んでいる結果」を伝えられず、申し訳ありません。
かみちゃん様のおっしゃるとおり、得意先別、営業部門別、商品別に金額の集計をとりたいと思っています。
得意先は、全部で4つあり、「得意先毎」の「営業部門別、商品別に金額の集計」をとりたいのです。

【例】d.xls
得意先AAA
     京都    大阪    神戸    合計
○    14    18    14    46
■    14    19    15    48
△    13    19    15    47
合計    41    56    44    141
(以下同じフォームを得意先DDDまでつくりたいと考えています。)

そこで色々考えた結果、思い至ったのが以下の方法で、昨日のせさせていただいたコードも、それに基づいたものです。
1.db.xlsでオートフィルタを掛け、「得意先AAA・京都・○」「得意先AAA・京都・■」…のような条件をつけ、db.xlsの指定レンジに集計結果を出す。
2.db.xlsの指定レンジの集計結果を、d.xlsの対象レンジにコピーさせる。
3.d.xlsで、上記表の「京都・○」から、「神戸・△」までをオートサムで合計させる。

集計=オートフィルタ!と頭が固まっていたため、ピボットテーブルは全然試していませんでした。早速試してみますね!(といっても一から勉強なのですが…(^_^;))

>これは、マクロの先頭で、対象シート名を選択させる、システム日付で処理対象シートを判断させるなどいろいろできます。

いろいろな、方法があるのですね!
対象シート名を選択させる、というのは、具体的にどのようなかんじなのでしょう?ユーザーに選択させるようにする、ということでしょうか…?
また、「システム日付で処理対象シートを判断させる」というのは、今後のことを考えれば是非身に付けておきたい方法です。よろしければサンプルコードなどを、教えていただけませんでしょうか。よろしくお願いいたします。

ちなみに、…マクロの先頭で、対象シート名を指定していたつもりなのですが、出来てなかったんでしょうか、やっぱり。…それとも、方法がちがうのでしょうか?

【26372】Re:効率的なコードにするには…。
回答  Hirofumi  - 05/7/3(日) 14:01 -

引用なし
パスワード
   ピボットテーブルの方が速いと思うけどこんなのも有るよ

Option Explicit

Public Sub Cross()

  Dim i As Long
  Dim lngRow As Long
  Dim lngColumn As Long
  Dim vntOffice As Variant
  Dim wkbData As Workbook
  Dim wksData As Worksheet
  Dim vntData As Variant
  Dim wksResult As Worksheet
  Dim rngResult As Range
  Dim vntResult As Variant
  Dim lngIndex() As Long
  Dim strSheet As String
  Dim lngWrite As Long
  Dim vntItems As Variant
  Dim strProm As String
  
  '出力する列見出しを設定(営業部門名)
  vntOffice = Array("", "京都", "大阪", "神戸", "合計")
  
  'シート名を取得
  strSheet = InputBox("処理するシートを「2005.6」の形で入力して下さい ", _
                  "シート名入力", Format(Date, "yyyy.m"))
  If strSheet = "" Then
    strProm = "マクロがキャンセルされました"
    GoTo Wayout
  End If
  'シートの存在確認
  If SheetsCheck(strSheet, wksResult, ActiveWorkbook) Then
    Set rngResult = wksResult.Cells(2, "B")
  Else
    strProm = "出力先のWorkSheet「" & strSheet & "」が有りません"
    GoTo Wayout
  End If
  
  'ファイルのOpen
  Set wkbData = Workbooks.Open("C:\Documents and Settings\質問\db.xls")
  'シートの存在確認、データの取得
  If SheetsCheck(strSheet, wksData, wkbData) Then
    With wksData.Cells(1, "A")
      'データ行数の取得
      lngRow = .Offset(65536 - .Row).End(xlUp).Row - .Row
      If lngRow <= 0 Then
        strProm = "データ元のデータが有りません"
        wkbData.Close SaveChanges:=False
        GoTo Wayout
      End If
      'データを配列に取得
      vntData = .Offset(1).Resize(lngRow, 4).Value
      wkbData.Close SaveChanges:=False
    End With
  Else
    strProm = "データ元のWorkSheet「" & strSheet & "」が有りません"
    wkbData.Close SaveChanges:=False
    GoTo Wayout
  End If
  
  'データを整列
  ReDim lngIndex(1 To UBound(vntData, 1))
  For i = 1 To UBound(vntData, 1)
    lngIndex(i) = i
  Next i
  For i = 1 To 3
    ShellSort vntData, lngIndex, (i Mod 3) + 1
  Next i
  
'  Application.ScreenUpdating = False
  
  '集計の初期値設定、配列の確保
  lngRow = 0
  ReDim vntResult(UBound(vntOffice) - 1, lngRow), vntItems(lngRow)
  vntOffice(0) = vntData(lngIndex(1), 1)
  vntItems(lngRow) = vntData(lngIndex(1), 3)
  
  '集計
  For i = 1 To UBound(lngIndex)
    lngColumn = ColumnSearch(vntData(lngIndex(i), 2), vntOffice)
    If lngColumn = -1 Then
      strProm = "未登録の営業部門が有りますのでマクロを終了します"
      GoTo Wayout
    Else
      '得意先が替わったら
      If vntOffice(0) <> vntData(lngIndex(i), 1) Then
        '結果を出力
        DataWrite rngResult, lngWrite, vntResult, vntOffice, vntItems
        '集計の初期値設定、配列の確保
        lngRow = 0
        ReDim vntResult(UBound(vntOffice) - 1, lngRow), vntItems(lngRow)
        vntOffice(0) = vntData(lngIndex(i), 1)
        vntItems(lngRow) = vntData(lngIndex(i), 3)
      Else
        '商品を探して、集計
        lngRow = RowSearch(vntData(lngIndex(i), 3), vntItems)
        If lngRow < 0 Then
          lngRow = UBound(vntItems) + 1
          ReDim Preserve vntResult(UBound(vntOffice) - 1, lngRow), vntItems(lngRow)
          vntItems(lngRow) = vntData(lngIndex(i), 3)
        End If
      End If
      vntResult(lngColumn, lngRow) _
          = vntResult(lngColumn, lngRow) + vntData(lngIndex(i), 4)
    End If
  Next i
  DataWrite rngResult, lngWrite, vntResult, vntOffice, vntItems
  
  strProm = "処理が完了しました"
  
Wayout:
  
'  Application.ScreenUpdating = True
  
  Set wkbData = Nothing
  Set wksData = Nothing
  Set wksResult = Nothing
  Set rngResult = Nothing
  
  Beep
  MsgBox strProm
  
End Sub

Private Function SheetsCheck(strMark As String, _
              wksMark As Worksheet, _
              wkbBook As Workbook) As Boolean

  With wkbBook
    For Each wksMark In .Worksheets
      If StrComp(wksMark.Name, strMark) = 0 Then
        SheetsCheck = True
        Exit Function
      End If
    Next wksMark
  End With
  
End Function

Private Function ColumnSearch(vntKey As Variant, _
                vntList As Variant) As Long

  Dim i As Long
  
  ColumnSearch = -1
  For i = 1 To UBound(vntList)
    If vntList(i) = vntKey Then
      ColumnSearch = i - 1
      Exit Function
    End If
  Next i
      
End Function

Private Function RowSearch(ByVal vntKey As Variant, _
              ByVal vntScope As Variant) As Long

  Dim lngLow As Long
  Dim lngHigh As Long
  Dim lngMiddle As Long
  
  lngLow = LBound(vntScope, 1)
  lngHigh = UBound(vntScope, 1)
  
  Do While lngLow <= lngHigh
    lngMiddle = (lngLow + lngHigh) \ 2
    Select Case vntScope(lngMiddle)
      Case Is < vntKey
        lngLow = lngMiddle + 1
      Case Is > vntKey
        lngHigh = lngMiddle - 1
      Case Is = vntKey
        lngLow = lngMiddle + 1
        lngHigh = lngMiddle - 1
    End Select
  Loop
  
  If lngLow = lngHigh + 2 Then
    RowSearch = lngMiddle
  Else
    RowSearch = -1
  End If

End Function

Private Sub DataWrite(rngOutput As Range, _
            lngWrite As Long, _
            vntResult As Variant, _
            vntOffice As Variant, _
            vntItems As Variant)
  Dim i As Long
  Dim j As Long
  Dim lngRow As Long
  Dim lngColumn As Long
  
  lngRow = UBound(vntResult, 2) + 1
  lngColumn = UBound(vntOffice) - 1
  ReDim Preserve vntResult(lngColumn, lngRow)
  For i = 0 To lngRow - 1
    For j = 0 To lngColumn - 1
      vntResult(lngColumn, i) = vntResult(lngColumn, i) + vntResult(j, i)
      vntResult(j, lngRow) = vntResult(j, lngRow) + vntResult(j, i)
    Next j
  Next i
  
  lngColumn = UBound(vntItems) + 1
  ReDim Preserve vntItems(lngColumn)
  vntItems(lngColumn) = "合計"
  
  With rngOutput.Offset(lngWrite)
    .Offset(, -1).Resize(, UBound(vntOffice) + 1).Value = vntOffice
    .Offset(1, -1).Resize(UBound(vntResult, 2) + 1).Value _
        = Application.Transpose(vntItems)
    .Offset(1).Resize(UBound(vntResult, 2) + 1, _
        UBound(vntOffice)).Value _
            = Application.Transpose(vntResult)
  End With
  lngWrite = lngWrite + UBound(vntItems, 1) + 1 + 2

End Sub

Private Sub ShellSort(vntList As Variant, _
          lngIndex() As Long, _
          Optional lngKey As Long = 1)
  Dim i As Long
  Dim j As Long
  Dim lngGap As Long
  Dim lngTmp As Long
  Dim lngTop As Long
  Dim lngEnd As Long
  Dim lngOrder() As Long
  
  lngTop = LBound(vntList, 1)
  lngEnd = UBound(vntList, 1)
  
  ReDim lngOrder(lngTop To lngEnd)
  For i = lngTop To lngEnd
    lngOrder(lngIndex(i)) = i
  Next i
  
  lngGap = 1
  Do While lngGap < (lngEnd - lngTop + 1) \ 3
    lngGap = 3 * lngGap + 1
  Loop
  
  Do Until lngGap = 0
    For i = lngGap + lngTop To lngEnd
      For j = i To lngGap + lngTop Step -lngGap
        If vntList(lngIndex(j - lngGap), lngKey) _
                > vntList(lngIndex(j), lngKey) Then
          lngTmp = lngIndex(j - lngGap)
          lngIndex(j - lngGap) = lngIndex(j)
          lngIndex(j) = lngTmp
        Else
          If vntList(lngIndex(j - lngGap), lngKey) _
                  = vntList(lngIndex(j), lngKey) Then
            If lngOrder(lngIndex(j - lngGap)) _
                    > lngOrder(lngIndex(j)) Then
              lngTmp = lngIndex(j - lngGap)
              lngIndex(j - lngGap) = lngIndex(j)
              lngIndex(j) = lngTmp
            End If
          Else
            Exit For
          End If
        End If
      Next j
    Next i
    lngGap = lngGap \ 3
  Loop

End Sub

【26373】Re:効率的なコードにするには…。
発言  かみちゃん E-MAIL  - 05/7/3(日) 14:36 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>得意先別、営業部門別、商品別に金額の集計をとりたいと思っています。
>得意先は、全部で4つあり、「得意先毎」の「営業部門別、商品別に金額の集計」をとりたいのです。

それであれば、ぜひ、ピボットテーブルを勉強されることをおすすめします。
ご存知でないならば、目からウロコだと思います。私も一番最初はそうでした。
さらに、もしかしたら、VBAを使わなくてもすむかもしれません。
面倒だったら、マクロの記録をしてしまえばいいわけですから。

>ピボットテーブルは全然試していませんでした。早速試してみますね!(といっても一から勉強なのですが…(^_^;))

それであれば、Webの検索で「ピボットテーブル Excel」などで検索すると、いろいろ出てきますが、以下のようなところは参考になるかもしれません。
http://kokoro.kir.jp/excel/pivottable.html
http://www.moug.net/skillup/fclm/fclm08-01.htm
http://www.atmarkit.co.jp/fwin2k/win2ktips/359pivot/pivot.html

こちらでは、ピボットテーブルでのコードができましたが、まずは、手作業による方法をマスターしていただいたほうがよろしいかと思いますので、コードを提示するのは、まだ控えておきます。
必要であれば、おっしゃってください。

>対象シート名を選択させる、というのは、具体的にどのようなかんじなのでしょう?ユーザーに選択させるようにする、ということでしょうか…?

そうです。ユーザーに選択させます。
たとえば、ユーザーフォームを用意して、コンボボックスを配置して、そのコンボボックスには、シート名の一覧をリスト化しておいて、そのリストの中から選択する。
簡単なものであれば、InputBox関数でシート名を入力させる。しかし、この場合だと、シート名が存在するのかどうかのチェックが必要です。

>また、「システム日付で処理対象シートを判断させる」というのは、今後のことを考えれば是非身に付けておきたい方法です。よろしければサンプルコードなどを、教えていただけませんでしょうか。よろしくお願いいたします。

以下のような感じでできます。
ポイントは、
 strSheetName = Format(Now(), "yyyy.m")
です。

Option Explicit

Sub Test()
 Dim strSheetName As String 'システム日付から生成したシート名
 Dim strSheetNameCK As Boolean 'シートの存在チェック
 Dim ws As Worksheet 'すべてのシートを検索する
 
 '現在の日付(Now())から「2005.7」のようなシート名を生成する。
 strSheetName = Format(Now(), "yyyy.m")
 
 strSheetNameCK = False
 '生成したシート名が存在するかどうかのチェック
 For Each ws In Worksheets
  If ws.Name = strSheetName Then
   strSheetNameCK = True
   Exit For
  End If
 Next
 '生成したシート名の存在チェック結果の表示
 If strSheetNameCK Then
  MsgBox "シート " & strSheetName & " は存在します"
 Else
  MsgBox "シート " & strSheetName & " は存在しません"
 End If
End Sub

>ちなみに、…マクロの先頭で、対象シート名を指定していたつもり

そこが「2005.6」に固定されているので、それをユーザーからの入力などにより、変数とすればいいかと思っています。

【26374】Re:効率的なコードにするには…。
回答  Hirofumi  - 05/7/3(日) 17:59 -

引用なし
パスワード
   考え過ぎで、複雑にし過ぎました
もっとコードが簡単に速くなります

Option Explicit

Public Sub Cross2()

  Dim i As Long
  Dim lngRow As Long
  Dim lngColumn As Long
  Dim vntOffice As Variant
  Dim wkbData As Workbook
  Dim wksData As Worksheet
  Dim vntData As Variant
  Dim wksResult As Worksheet
  Dim rngResult As Range
  Dim vntResult As Variant
  Dim strSheet As String
  Dim lngWrite As Long
  Dim vntItems As Variant
  Dim strProm As String
  
  Dim vntFileName As Variant
  
  '出力する列見出しを設定(営業部門名)
  vntOffice = Array("", "京都", "大阪", "神戸", "合計")
  
  'シート名を取得
  strSheet = InputBox("処理するシートを「2005.6」の形で入力して下さい ", _
                  "シート名入力", Format(Date, "yyyy.m"))
  If strSheet = "" Then
    strProm = "マクロがキャンセルされました"
    GoTo Wayout
  End If
  'シートの存在確認
  If SheetsCheck(strSheet, wksResult, ActiveWorkbook) Then
    Set rngResult = wksResult.Cells(2, "B")
  Else
    strProm = "出力先のWorkSheet「" & strSheet & "」が有りません"
    GoTo Wayout
  End If
  
'  Application.ScreenUpdating = False
  
  'ファイルのOpen
  Set wkbData = Workbooks.Open("C:\Documents and Settings\質問\db.xls")
  'シートの存在確認、データの取得
  If SheetsCheck(strSheet, wksData, wkbData) Then
    With wksData.Cells(1, "A")
      'データ行数の取得
      lngRow = .Offset(65536 - .Row).End(xlUp).Row - .Row
      If lngRow <= 0 Then
        strProm = "データ元のデータが有りません"
        wkbData.Close SaveChanges:=False
        GoTo Wayout
      End If
      With .Offset(1).Resize(lngRow, 4)
        .Sort Key1:=.Item(1, 1), Order1:=xlAscending, _
            Key2:=.Item(1, 3), Order2:=xlAscending, _
            Key3:=.Item(1, 2), Order3:=xlAscending, _
            Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom, SortMethod:=xlStroke
        'データを配列に取得
        vntData = .Value
      End With
      wkbData.Close SaveChanges:=False
    End With
  Else
    strProm = "データ元のWorkSheet「" & strSheet & "」が有りません"
    wkbData.Close SaveChanges:=False
    GoTo Wayout
  End If
    
  '集計の初期値設定、配列の確保
  lngRow = 0
  ReDim vntResult(UBound(vntOffice) - 1, lngRow), vntItems(lngRow)
  vntOffice(0) = vntData(1, 1)
  vntItems(lngRow) = vntData(1, 3)
  
  '集計
  For i = 1 To UBound(vntData, 1)
    lngColumn = ColumnSearch(vntData(i, 2), vntOffice)
    If lngColumn = -1 Then
      strProm = "未登録の営業部門が有りますのでマクロを終了します"
      GoTo Wayout
    Else
      '得意先が替わったら
      If vntOffice(0) <> vntData(i, 1) Then
        '結果を出力
        DataWrite rngResult, lngWrite, vntResult, vntOffice, vntItems
        '集計の初期値設定、配列の確保
        lngRow = 0
        ReDim vntResult(UBound(vntOffice) - 1, lngRow), vntItems(lngRow)
        vntOffice(0) = vntData(i, 1)
        vntItems(lngRow) = vntData(i, 3)
      Else
        '商品を探して、集計
        If vntItems(lngRow) <> vntData(i, 3) Then
          lngRow = lngRow + 1
          ReDim Preserve vntResult(UBound(vntOffice) - 1, lngRow), vntItems(lngRow)
          vntItems(lngRow) = vntData(i, 3)
        End If
      End If
      vntResult(lngColumn, lngRow) _
          = vntResult(lngColumn, lngRow) + vntData(i, 4)
    End If
  Next i
  DataWrite rngResult, lngWrite, vntResult, vntOffice, vntItems
  
  strProm = "処理が完了しました"
  
Wayout:
  
'  Application.ScreenUpdating = True
  
  Set wkbData = Nothing
  Set wksData = Nothing
  Set wksResult = Nothing
  Set rngResult = Nothing
  
  Beep
  MsgBox strProm
  
End Sub

Private Function SheetsCheck(strMark As String, _
              wksMark As Worksheet, _
              wkbBook As Workbook) As Boolean

  With wkbBook
    For Each wksMark In .Worksheets
      If StrComp(wksMark.Name, strMark) = 0 Then
        SheetsCheck = True
        Exit Function
      End If
    Next wksMark
  End With
  
End Function

Private Function ColumnSearch(vntKey As Variant, _
                vntList As Variant) As Long

  Dim i As Long
  
  ColumnSearch = -1
  For i = 1 To UBound(vntList)
    If vntList(i) = vntKey Then
      ColumnSearch = i - 1
      Exit Function
    End If
  Next i
      
End Function

Private Sub DataWrite(rngOutput As Range, _
            lngWrite As Long, _
            vntResult As Variant, _
            vntOffice As Variant, _
            vntItems As Variant)
  Dim i As Long
  Dim j As Long
  Dim lngRow As Long
  Dim lngColumn As Long
  
  lngRow = UBound(vntResult, 2) + 1
  lngColumn = UBound(vntOffice) - 1
  ReDim Preserve vntResult(lngColumn, lngRow)
  For i = 0 To lngRow - 1
    For j = 0 To lngColumn - 1
      vntResult(lngColumn, i) = vntResult(lngColumn, i) + vntResult(j, i)
      vntResult(j, lngRow) = vntResult(j, lngRow) + vntResult(j, i)
    Next j
  Next i
  
  lngColumn = UBound(vntItems) + 1
  ReDim Preserve vntItems(lngColumn)
  vntItems(lngColumn) = "合計"
  
  With rngOutput.Offset(lngWrite)
    .Offset(, -1).Resize(, UBound(vntOffice) + 1).Value = vntOffice
    .Offset(1, -1).Resize(UBound(vntResult, 2) + 1).Value _
        = Application.Transpose(vntItems)
    .Offset(1).Resize(UBound(vntResult, 2) + 1, _
        UBound(vntOffice)).Value _
            = Application.Transpose(vntResult)
  End With
  lngWrite = lngWrite + UBound(vntItems, 1) + 1 + 2

End Sub

【26375】Re:効率的なコードにするには…。
お礼  あさみ  - 05/7/3(日) 20:32 -

引用なし
パスワード
   こんばんは、かみちゃん様。お礼が遅くなってしまい、失礼をいたしました。
早速ピボットテーブルを試してみました!
まさに、目からうろこですね!びっくりしました。…一度に集計できるなんて、びっくりです。
あとは、一度も間違えずにピボットテーブルの設定をし終えるか、設定後、間違った部分を修正すれば、出来そうです。練習あるのみ!ですね。

対象シート名を選択させる方法は、いまから試してみます。これさえできれば、なんとか完成です。もしかしたら、またつまづいてしまうかもしれませんが、そのときは、またご助力のほど、お願いいたします。

そして、「システム日付で処理対象シートを判断させる」方法のご教授、ありがとうございます。今回は、対象シート名を選択させる方法で頑張ってみようと思いますが、今後に活かさせていただきますね。

最後になりましたが、本当にありがとうございました。

【26376】Re:効率的なコードにするには…。
お礼  あさみ  - 05/7/3(日) 20:34 -

引用なし
パスワード
   はじめまして、Hirofumi 様。 お礼が遅くなってしまい、失礼をいたしました。

すばらしいコードを、しかも二つもありがとうございます!…なんというか、これぞプログラム!という、いつか自力でできるようになりたいコードが目の前に…。惜しむべくは、書かれている内容が、半分以上読解できないという所でしょうか…。

早速プリントアウトをさせていただいて、コードの勉強をさせていただいております。
大変勉強になります。特に、対象シート名を選択させる方法は、同じくご教授を下さっているかみちゃん様からもお勧めいただいておりましたので、今からためさせていただきたいと思っております。本当にありがとうございました。

…あ、ひとつ、質問をさせていただいてよろしいでしょうか?
この、Hirofumi 様のコードを使用させていただく場合、もし営業部門名を増やすとなると、どこを変更させればよいのでしょう…?

【26377】Re:効率的なコードにするには…。
回答  Hirofumi  - 05/7/3(日) 20:54 -

引用なし
パスワード
   >…あ、ひとつ、質問をさせていただいてよろしいでしょうか?
>この、Hirofumi 様のコードを使用させていただく場合、もし営業部門名を増やすとなると、
>どこを変更させればよいのでしょう>…?

以下の部分に追加するだけで善いです

  '出力する列見出しを設定(営業部門名)
  vntOffice = Array("", "京都", "大阪", "神戸", "合計")

例として、「東京」を追加した場合

   '出力する列見出しを設定(営業部門名)
  vntOffice = Array("", "京都", "大阪", "神戸", "東京", "合計")

各営業部門の出力順は、ここに記述された順番に成りますので、出力したい順位の所へ、追加します
尚、先頭は必ず""とし、最終は必ず"合計"にして下さい

【26379】Re:効率的なコードにするには…。
お礼  あさみ  - 05/7/3(日) 22:19 -

引用なし
パスワード
   こんばんは、Hirofumi 様
早速のご教授、ありがとうございます。

営業部門名は、簡単に増やすことが出来るのですね。すごいです!
はやくこのコードの理解を深めて、自分でも書けるように頑張ります。あとは、商品に対しての条件付けとかが増えた場合(例えば○にたいして、国内・海外みたいに…)に応用することができたら、幅広く使えそうですね。

本当に勉強になります。ありがとうございました。

【26380】Re:効率的なコードにするには…。
質問  あさみ  - 05/7/4(月) 1:00 -

引用なし
パスワード
   こんばんは、かみちゃん様

…申し訳ありません。速攻で、おもいっきりつまづきました…。
もともと、最初にブックとシートを指定するために、「Set wbs」「Set destination」としていしていたのは、オートフィルタを掛けた結果を下記のコードで反映させたかったからなんです。

コピペ6 wbs.Range("D84"), destination.Range("B3")

じつは、d.xls(集計表)は最初から用意されてある会社指定のフォームになるので、ここに、数値だけを入力させる必要があるのです。なので、ピボットテーブルを使ったうえで、このコピーをさせるコードをそのまま使いたいのです。

…となったとき、「コピペ6 wbs.Range("D84"), destination.Range("B3")」をそのままつかうためには、いったいどうやって、ユーザーにシートの選択をさせればいいのでしょう…?

【26387】Re:効率的なコードにするには…。
発言  かみちゃん E-MAIL  - 05/7/4(月) 12:52 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>「コピペ6 wbs.Range("D84"), destination.Range("B3")」をそのままつかうためには、いったいどうやって、ユーザーにシートの選択をさせればいいのでしょう…?

集計は、ピボットテーブルでするということでいいでしょうか?
オートフィルタを何度もするよりずっと早いと思います。
その結果を、d.xls(集計表)のフォーマットにしたがって、値を貼り付けていけばいいわけですよね?

そこで、問題です。
このd.xlsのフォーマットがどうなっているのかが、私には、わかりません。

ピボットテーブルの結果をできる限り、d.xlsのフォーマットに近づければいいと思います。
フォーマットを見せていただければ、割と簡単にできそうな感じがします。
同じようなことを会社でしているので・・・

【26416】Re:効率的なコードにするには…。
発言  あさみ  - 05/7/5(火) 2:05 -

引用なし
パスワード
   こんばんは、かみちゃん様。またまたお返事が遅くなってしまい申し訳ありません。
会社では、インターネットを使える環境にないので、こんな時間になってしまいました。

かみちゃん様のおっしゃるとおり、データの全てをオートフィルタで集計をするよりも、ピポットテーブルで集計した方がはやいです。
ただ、ピボットテーブルの結果をできる限りd.xlsのフォーマットに近づけても、補いきれない部分があり、問題が出てきます。それは、d.xlsのフォーマットが部全体で使っているものであり、db.xlsは課で使っているデータベースだということから発生します。具体的には以下のような問題点があります。

1.d.xlsにある項目が、db.xlsにないものもある。
2.db.xlsの、ピボットテーブルで集計した複数の値を、d.xlsの1つのレンジに反映させなくてはいけない部分がある。(例えば、db.xlsでは「商品○」は、「○A」と「○B」の二つがあるのでピボットテーブル集計では当然「○A」「○B」の項目がでてくるけど、d.xls上では、○として○A,○Bの合計を入力しなくてはいけない。)

なので、集計をピポットテーブルで出した後は、db.xlsのピボットテーブルのそれぞれのレンジをd.xlsの対象レンジへ貼り付けていく作業がひつようになるのです。
そこで、私が試してみたい方法が、次の方法です。

1.db.xlsのピボットテーブルを、新しいシート(sheet1)に作成する。
2.sheet1のピボットテーブルのそれぞれのレンジをd.xlsの対象レンジへ貼り付ける。

「コピペ6 wbs.Range("D84"), destination.Range("B3")」のような方法以外に、何か良い手はあるでしょうか?
本当は、作りたいd.xlsとdb.xlsを直接見ていただきたい気持ちでいっぱいなのですが、さすがにそれは持ち出し禁止ですし…(T_T)
少ない情報しかお伝えできないのがつらいのですが、何か良い方法がありましたら、是非ご教授くださいませ。

【26417】Re:効率的なコードにするには…。
発言  かみちゃん E-MAIL  - 05/7/5(火) 6:39 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>1.d.xlsにある項目が、db.xlsにないものもある。

具体的なことを書いていただかないとわりづらいです。

>2.db.xlsの、ピボットテーブルで集計した複数の値を、d.xlsの1つのレンジに反映させなくてはい
>けない部分がある。(例えば、db.xlsでは「商品○」は、「○A」と「○B」の二つがあるのでピボ
>ットテーブル集計では当然「○A」「○B」の項目がでてくるけど、d.xls上では、○として○A,○B
>の合計を入力しなくてはいけない。)

ピボットテーブルの集計方法次第でどうにでもなります。
たとえば、集計前に、作業列を一時的に作って、○A、○Bは、○とし、○で集計するなど。

>1.db.xlsのピボットテーブルを、新しいシート(sheet1)に作成する。
>2.sheet1のピボットテーブルのそれぞれのレンジをd.xlsの対象レンジへ貼り付ける。
>
>「コピペ6 wbs.Range("D84"), destination.Range("B3")」のような方法以外に、何か良い手はあるでしょうか?

あります。ただし、d.xlsのフォーマット次第です。
なので、Sheet1とd.xlsのフォーマットがわからないと具体的なことを書くことが現時点では難しい
です。

>本当は、作りたいd.xlsとdb.xlsを直接見ていただきたい気持ちでいっぱいなのですが、さすがにそれは持ち出し禁止ですし…(T_T)
>少ない情報しかお伝えできないのがつらいのですが、何か良い方法がありましたら、是非ご教授くださいませ。

もちろん、実際のデータではなくてもいいのですが、サンプルとして、フォーマットさえ、「正確に」
教えていただければ、もう少し具体的なことが言えるのですが・・・

考え方は、あさみさんの考えているとおりでいいかと思います。

【26465】Re:効率的なコードにするには…。
発言  あさみ  - 05/7/6(水) 7:16 -

引用なし
パスワード
   おはようございます、かみちゃん様。

やはり、具体的な例がなければご教授をいただくのは難しいですよね。
サンプルのフォーマットをもう一度考え直してから質問させていただきます。
その時は、またよろしくお願いいたします。

【26487】Re:効率的なコードにするには…。
発言  かみちゃん E-MAIL  - 05/7/6(水) 22:53 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>やはり、具体的な例がなければご教授をいただくのは難しいですよね。
>サンプルのフォーマットをもう一度考え直してから質問させていただきます。

今までのサンプルデータを再度整理してみました。


【db.xls】
   A    B    C   D
1 得意先 営業部門 商品 受注総額
2 AAA    京都   ○     100
3 AAA    京都   ■     200
4 AAA    京都   △     300
5 AAA    大阪   ○     400
6 AAA    大阪   ■     500
7 AAA    大阪   △     500
8 AAA    神戸   ○     400
9 AAA    神戸   ■     300
10 AAA    神戸   △     200
11 BBB    京都   ○     100
12 BBB    京都   ■     600
13 BBB    京都   △     700
14 BBB    大阪   ○     800
15 BBB    大阪   ■     900

【db.xls】ピボットテーブル集計
   A        B   C    D   E   F   G
1
2
3 合計 / 受注総額 得意先 営業部門
4          AAA           BBB     総計
5 商品       京都  神戸   大阪 京都 大阪
6 ■           200   300    500  600  900  2500
7 △           300   200    500  700     1700
8 ○           100   400    400  100  800  1800
9 総計       600   900   1400 1400 1700  6000

【d.xls】得意先AAA
  A    B  C   D  E
     京都 大阪 神戸 合計
1 ○   100  400  400  900
2 ■   200  500  300 1000
3 △   300  500  200 1000
4 合計  600 1400  900 2900

【制約条件】
1.d.xlsにある項目が、db.xlsにないものもある。
2.db.xlsの、ピボットテーブルで集計した複数の値を、d.xlsの1つのレンジに反映させなくてはい
 けない部分がある。(例えば、db.xlsでは「商品○」は、「○A」と「○B」の二つがあるのでピ
 ボットテーブル集計では当然「○A」「○B」の項目がでてくるけど、d.xls上では、○として○A,
 ○Bの合計を入力しなくてはいけない。)

以上のことを整理してさらに疑問が出てきました。
1.d.xlsは、得意先別にシートがわかれているのでしょうか?
2.db.xlsのレコードは、得意先、営業部門、集計した商品で重複しているのでしょうか?
 この例では、重複していないことになります。
3.重複していないのであれば、d.xlsはピボットテーブルから参照するのではなく、VLOOKUP関数を
 使った参照をすることもできます。d.xlsに直接、数式を設定することができないならば、db.xls
 の別シートにd.xlsと同様のフォーマットを用意して、そこで、数式による参照をさせ、結果を
 d.xlsの対象範囲に値貼り付けすることができます。
4.重複しているのであれば、やはりピボットテーブルによる集計を行ない、その集計結果のシート
 をd.xlsからVBAで参照する形になります。
 この参照の考え方ですが、d.xlsのB1は、「京都」の商品「○」ですが、db.xlsのピボットテーブル
 集計のA列から「○」を探し、行番号を取得します。次に5行目から「京都」でかつ得意先が
 「AAA」を探し、列番号を取得します。行番号と列番号が交差したところが値となります。
 もっとも、決まったフォーマットでないならば、こんなややこしいことをしなくてもすむのです
 が、とりあえず、一番複雑な方法で、コードを用意しています。

一度、ご検討ください。

9600 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free