エクセルマクロ・VBA達人養成塾 小川です。
最近ある受講生の方から受けた質問をベースに、ピボットテーブルを作るマクロについて解説しています。
エクセルで、重複しないリストを作り、集計表を作成したい – Excelテクニック
「ピボットテーブルを作るマクロ」を自動記録で作ってみる – Excel Macro, VBA
「ピボットテーブル」が作られる仕組みと「ピボットキャッシュ」 – Excelテクニック
ピボットテーブルを作成するマクロの部品を大まかに分割してみる – Excelマクロ・VBA
お題は、こんな感じ。
下に示した表がある。
|A列 |B列 |C列 |D列 |E列 |
--------------------------------------------------------------
1 行目 |ＩＤ |受信日時 |企業名|内容 |タイプ |
--------------------------------------------------------------
2 行目 |1 |2012/2/1 |A社 |××支払利息 |Comment |
--------------------------------------------------------------
3 行目 |2 |2012/2/2 |B社 |×□制作料 |Flash |
--------------------------------------------------------------
4 行目 |3 |2012/2/3 |C社 |×□賃貸料 |Periodical|
--------------------------------------------------------------
5 行目 |4 |2012/2/4 |A社 |×凹委託費用 |Report |
--------------------------------------------------------------
6 行目 |5 |2012/2/5 |C社 |×凹実施料 |Comment |
--------------------------------------------------------------
7 行目 |6 |2012/2/6 |A社 |□凹商品売却 |Report |
--------------------------------------------------------------
8 行目 |7 |2012/2/7 |C社 |□凸保険料 |Flash |
--------------------------------------------------------------
9 行目 |8 |2012/2/8 |B社 |△×支払利息 |Comment |
--------------------------------------------------------------
10行目 |9 |2012/2/9 |C社 |△□不動産取引|Flash |
--------------------------------------------------------------
11行目 |10 |2012/2/10|A社 |△○制作料 |Report |
--------------------------------------------------------------
12行目 |11 |2012/2/11|A社 |△凹調査料 |Comment |
--------------------------------------------------------------
13行目 |12 |2012/2/12|B社 |○×荷役料 |Comment |
--------------------------------------------------------------
14行目 |13 |2012/2/13|E社 |○□リース料 |Comment |
--------------------------------------------------------------
15行目 |14 |2012/2/14|B社 |○□代行費用 |Flash |
--------------------------------------------------------------
16行目 |15 |2012/2/15|C社 |凹×リース料 |Flash |
--------------------------------------------------------------
17行目 |16 |2012/2/16|F社 |凹□支払利息 |Flash |
--------------------------------------------------------------
18行目 |17 |2012/2/17|A社 |凸×作成料 |Comment |
--------------------------------------------------------------
19行目 |18 |2012/2/18|B社 |凸×制作料 |Flash |
--------------------------------------------------------------
20行目 |19 |2012/2/19|A社 |凸△賃貸料 |Flash |
--------------------------------------------------------------
21行目 |20 |2012/2/20|B社 |凸凸制作料 |Periodical|
--------------------------------------------------------------
このとき、以下のように、どの対象のどのタイプが何回出現したかを集計したい。
|A列 |B列 |C列 |D列 |E列 |F列 |
-----------------------------------------------------------
1 行目 |企業名|合計 |Flash|Comment|Periodical|Report|
-----------------------------------------------------------
2 行目 |A社 |7 |3 |4 |0 |0 |
-----------------------------------------------------------
3 行目 |B社 |6 |3 |2 |1 |0 |
-----------------------------------------------------------
4 行目 |C社 |5 |3 |1 |1 |0 |
-----------------------------------------------------------
5 行目 |D社 |0 |0 |0 |0 |0 |
-----------------------------------------------------------
6 行目 |E社 |1 |0 |1 |0 |0 |
-----------------------------------------------------------
7 行目 |F社 |1 |1 |0 |0 |0 |
-----------------------------------------------------------
で、前回までの記事です。
エクセルで、重複しないリストを作り、集計表を作成したい – Excelテクニック
「ピボットテーブルを作るマクロ」を自動記録で作ってみる – Excel Macro, VBA
「ピボットテーブル」が作られる仕組みと「ピボットキャッシュ」 – Excelテクニック
ピボットテーブルを作成するマクロの部品を大まかに分割してみる – Excelマクロ・VBA
では、今日も、もう一歩進めてみませふ。
以下の一行について、大まかな構成が見えてきました。
ということです。
では、まずは、「 ActiveWorkbook.PivotCaches.Add 」について。
以下、「 PivotCaches.Add 」について、ヘルプで調べて調べてみました。
..と言われても読む気がしないかと思いますので。
僕のほうで、ポイントと思しきところをテキトウに見つくろってきました ヾ(´ー｀)ﾉ
以下を参照してください。
ピボットテーブル キャッシュを PivotCaches コレクションに追加します。PivotCache オブジェクトを返します。
expression.Add(SourceType, SourceData)
とのこと。
「 expression 」のところでは、ピボットキャッシュのコレクションを指定します。
ここでは、「今開いているエクセルファイル内の、ピボットキャッシュのコレクション」を指します。
「コレクション」の概念については、説明が大変なのでここでは解説しません。
すっきり理解したい方は、達人養成塾の「Excelマクロ・VBA発展編１」でコレクションについて詳しく解説しているので、セミナーに出てください。
SouceTypeでは、元データがどんな形式のものか、を指定します。
元データがエクセルシート上にあるデータなら、「 SourceType:=xlDatabase 」としておけばOK。
SourceData で、具体的に、元データがどこにあるのかを指定します。
ということで。
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=”main!R1C1:R21C5″)
について、何を言わんとしているかを解説すると。
「今開いているエクセルファイル内の、ピボットキャッシュのコレクション」を追加します。
ただし、追加するデータがどんな形式のものかというと、エクセルシート上にあるデータで、具体的に言うと、メインシートの、セルA1～E21ね」
と、言っているわけです。
あ、そうそう。ついでに書くと。
R1C1 → セルA1 のこと
R21C5 → セル E21 のこと
です。
R1 なら1行目、 R2 なら2行目、R3 なら3行目、 … 。
C1 なら1列目、 C2 なら2列目、C3 なら3列目、 … 。
という意味です。自動記録でマクロを書くと、ときどきこんなのが出てきます。
なお、 SouceData については、文字列でなく、セル範囲をオブジェクトとして指定することもできます。
つまり、以下の記載方法でもOK。
次回は、またこの続きを。
それでは ヾ(´ー｀)ﾉ
