[[20211004161637]]　『ピポットテーブルを使ったマクロについて』（JIN）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

　

『ピポットテーブルを使ったマクロについて』（JIN）

エクセルのマクロを使用したピポットテーブルについて

これは、マクロの記録で取得したコードになります。
ピポットテーブルを使用すること自体ほぼ初めてです。
範囲の設定："Sheet1!R1C1:R44143C10"
この範囲が毎回違います。範囲を指定しない方法はありますか？

そして、ハショれるところがあれば省きたいです。
純粋な記録のみのコード記述なのでどれが無駄な場所か分かりません。
省かないでも使えると思いますが、今後の為にも省ける（無駄な場所）があれば教えてください。

Sub Macro2()
Application.ScreenUpdating = False 

    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R44143C10", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet2!R3C1", TableName:="ピボットテーブル3", DefaultVersion:=6
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("ピボットテーブル3")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("ピボットテーブル3").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("管理番号")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("処理日")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("納期")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("作成日")
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("作成者")
        .Orientation = xlRowField
        .Position = 5
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("作成者名")
        .Orientation = xlRowField
        .Position = 6
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("得意先")
        .Orientation = xlRowField
        .Position = 7
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("宛名")
        .Orientation = xlRowField
        .Position = 8
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("宛名名称")
        .Orientation = xlRowField
        .Position = 9
    End With
    With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("摘要")
        .Orientation = xlRowField
        .Position = 10
    End With
    ActiveSheet.PivotTables("ピボットテーブル3").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル3").PivotFields("摘要"), "個数 / 摘要", xlCount
    ActiveSheet.PivotTables("ピボットテーブル3").RowAxisLayout xlTabularRow
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("管理番号").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("処理日").Subtotals = Array(False _
        , False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("納期").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("作成日").Subtotals = Array(False _
        , False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("作成者").Subtotals = Array(False _
        , False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("作成者名").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("得意先").Subtotals = Array(False _
        , False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("宛名").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("宛名名称").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("摘要").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
End Sub

行いたいのは、「Ａ列の管理番号」をキーにした個数のピポットです。
集計後に
処理日 |納期 |作成日 |作成者|作成者名|得意先|宛名|宛名名称|摘要|個数(←これは別途追加します)
で表を作成したのです。

元データのレイアウトは下記になります。 

     |[A]     |[B]     |[C]     |[D]     |[E]   |[F]     |[G]   |[H] |[I]     |[J]       
 [1] |管理番号|処理日  |納期    |作成日  |作成者|作成者名|得意先|宛名|宛名名称|摘要      
 [2] |1RAFL   |20210831|20210901|20210831| 20908|山田    |MG112 |  42|企業１  |大至急    
 [3] |1RAFF   |20210831|20210902|20210831| 19901|荒井    |M2111 |  56|企業２  |          
 [4] |1RAFE   |20210831|20210901|20210831| 41511|平井    |M9024 |JA  |企業３  |          
 [5] |1RAFC   |20210831|20210907|20210831| 19901|荒井    |M2172 |7X  |企業４  |          
 [6] |1RAFB   |20210831|20210902|20210831| 41303|吉村    |M2948 |YN  |企業５  |９／２必着
 [7] |1RAFA   |20210831|20210901|20210831| 19902|安保    |M4163 |ZZ  |企業６  |          
 [8] |1RAFA   |20210831|20210901|20210831| 19902|安保    |M4163 |ZZ  |企業６  |          
 [9] |1RAFA   |20210831|20210901|20210831| 19902|安保    |M4163 |ZZ  |企業６  |          
 [10]|1RAF9   |20210831|20210902|20210831| 19901|荒井    |M2394 |ZZ  |企業７  |          
 [11]|1RAF8   |20210831|20210902|20210831| 20908|萩原    |M2366 |ZZ  |企業８  |          
 [12]|1RAF7   |20210831|20210902|20210831| 41511|平井    |M1685 |T6  |企業９  |          
 [13]|1RAF6   |20210831|20210901|20210831| 19212|樋田    |M4172 |MB  |企業１０|          
 [14]|1RAF5   |20210831|20210901|20210831| 19902|安保    |M4172 |MB  |企業１０|          
 [15]|1RAF5   |20210831|20210901|20210831| 19902|安保    |M4172 |MB  |企業１０|          
 [16]|1RAF5   |20210831|20210901|20210831| 19902|安保    |M4172 |MB  |企業１０|          
 [17]|1RAF5   |20210831|20210901|20210831| 19902|安保    |M4172 |MB  |企業１０|          
 [18]|1RAF4   |20210831|20210901|20210831| 19507|中村    |M1654 |ZZ  |企業１１|          
 [19]|1RAF4   |20210831|20210901|20210831| 19507|中村    |M1654 |ZZ  |企業１１|

どうぞ良い方法があれば教えてください。

＜ 使用 Excel：unknown、使用 OS：unknown ＞

よく見たわけではありませんが

毎回、新規にピボットを作成するのでしょうか。
だとしたら、作成場所は、新規シートで間違いないですか？

（マナ） 2021/10/04(月) 17:46

↑マクロ実行するたびに、ピボットが追加されますが、
それでよいのかという確認です。

＞範囲の設定："Sheet1!R1C1:R44143C10"
＞この範囲が毎回違います。

行数（レコード数）が変動するという意味ですか。
それとも、シート（or ブック）も変わるのでしょうか。

（マナ） 2021/10/04(月) 19:35

毎回、新規にピボットを作成します。
毎回５万行ぐらいのデータがありそれをピポッドで集計しようとしてます。
他に集計のいい方法があれば別ですが…
データ⇒小計で行ったらエクセルがフリーズしたので…

できるならば、別に名前を付けたシート「元データ貼付場所」などにして
「Sheet1」にピポッドの結果を表示する方法にしたいと思っています。

＞範囲の設定："Sheet1!R1C1:R44143C10"
＞この範囲が毎回違います。
シート（or ブック）は、変わりません。
レコード数のみの変動です。
４万以上のデータになるので…

御返信ありがとうございます、よろしくお願い致します。
（JIN） 2021/10/05(火) 14:46

＞シート（or ブック）は、変わりません。

＞シート「元データ貼付場所」などにして
＞「Sheet1」にピポッドの結果を表示する

ということは、大元のデータは他の場所にあるのでしょうか
ピボットのデータソースを、その外部データにできないですか。
そうできれば、データソースを変更するだけで
毎回、ピボットを作成しなくても
Sheet1に作成したピボットは自動で更新されるはずですが。

（マナ） 2021/10/05(火) 17:02

間違っていたらすみません。

会社の環境で別の場所からデータをダウンロードしてきます。
毎回新しいエクセルにデータがＤＬされます。

そのデータを「元データ貼付場所」に張り付けてからピポッドするイメージです。

ピポッドを作成しといて、データのみを張り付けて反映させる事が出来るのでしょうか？
（JIN） 2021/10/06(水) 14:24 

 >ピポッドを作成しといて、データのみを張り付けて反映させる事が出来るのでしょうか？
 できますね
 試してみたらいいですよ


 PowerQuery使えば貼り付けの作業も不要になると思いますけど
（´・ω・`） 2021/10/06(水) 14:30

 こんにちわ　＾＾
別案です。。。連想配列を使った、集計です。5万行、10列くらいなら
お時間も、そんなに、ピボットと変わらないかも。。。＾＾；
m(_ _)m
Sub OneInstanceMain()
    Dim i             As Long
    Dim j             As Long
    Dim zD            As Object
    Dim v()           As Variant
    Dim w()           As Variant
    Dim md()          As Variant
    Dim r             As Range
    Dim t             As Double
    t = Timer
    Set zD = CreateObject("Scripting.Dictionary")
    With Worksheets("Sheet1")
        v = .Cells(1).CurrentRegion.Value
    End With
    For i = 2 To UBound(v, 1)
        If v(i, 1) <> "" Then
            ReDim w(1 To UBound(v, 2))
            If Not zD.Exists(v(i, 1)) Then
                For j = 1 To UBound(v, 2)
                    w(j) = v(i, j)
                Next
                w(UBound(w)) = 1
                zD(v(i, 1)) = w
            Else
                w = zD(v(i, 1))
                w(UBound(w)) = w(UBound(w)) + 1
                zD(v(i, 1)) = w
            End If
        End If
    Next
    md = Array("管理番号", "処理日", "納期", "作成日", "作成者", _
               "作成者名", "得意先", "宛名", "宛名名称", "個数")
    With Worksheets("Sheet2")
        .UsedRange.Delete
        .Cells(1, 1).Resize(, UBound(md) + 1) = md
        .Cells(2, 1).Resize(zD.Count, UBound(md) + 1) = Application.Index(zD.items, 0, 0)
        Set r = .Cells(1).CurrentRegion
        r.Sort key1:=r.Columns(1), order1:=xlAscending, Header:=xlYes
    End With
    Erase v, w, md
    zD.RemoveAll
    MsgBox "終了　" & Format(Int(Timer - t) / 24 / 60 / 60, "hh : mm : ss") & _
                      Format((Timer - t) - Int(Timer - t), ".000") & " 秒"
End Sub
（隠居Ｚ） 2021/10/06(水) 14:56
隠居Ｚさん、毎度すみません。
そして、凄いっす．．．
処理早すぎ

小計でやった時は処理が止まりました…
ピポッドは初めて使ったので迷宮に突入している状態でした…

自分では全く理解できませんがありがとうございました。
「連想配列」この言葉も初めて聞きました…

本当にありがとうございました。
（JIN） 2021/10/06(水) 16:22

（´・ω・`）さん、PowerQuery？
う〜ん、ピポッドは完全に初心者なので…
分からな過ぎました。

ピポッドを作成しといて、データのみを張り付けて反映させる まずはこれを行ってみます。
（JIN） 2021/10/06(水) 16:24
＞ピポッドを作成しといて、データのみを張り付けて反映させる まずはこれを行ってみます。

ピポッドに慣れる意味でも、それがよいと思います。 

 　
以下は、その次のステップに進む際の、参考まで。
 　
ピボットを作成するときに、
元データに、「外部データソース」を指定できます。
この場合、ダウンロードしたエクセルファイルを指定します。
http://www4.synapse.ne.jp/yone/excel2010/excel2010_pivot_gaibu.html
 　
そうすれば、ピボットを更新するだけで、
最新のデータが反映されます。
更新操作しなくても、起動時に自動で更新という設定も可能。
（たぶんデフォルトの設定が、そうなっています）
 　
ただし元データファイルは、フォルダを固定にし、
ファイル名も固定にする必要があります。
つまり、ファイルを差し替えるという運用です。
 　
Power Queryでも、同様にできます。
\ファイル名は固定にしなくても
フォルダ内の最新ファイルを元データにする
といったこともPower Queryなら可能になります。
 　

（マナ） 2021/10/06(水) 20:34

フォルダ内の最新ファイルを元データにする

これ良いですね…
試してみます。

本当に色々とありがとうございました。

勉強します。
（JIN） 2021/10/07(木) 17:51

コメント返信：

[ 一覧(最新更新順) ]

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.