2015/07/10

シート別にデータを分ける – VBA/マクロとピボットテーブルの活用

実践ワークシート協会の通常業務では、なるべく「元データ」は Office 365 の SharePoint か、SharePoint Access アプリを経由して SQL Azure に入力して、クラウド上でまとめるようにしています。

生のデータがクラウド上にあれば、あとは Excel のデータ接続を使って生データをワークシートに取り込み、自分の欲しい形に加工すればよいわけです。おおよそオートフィルターかピボットテーブルで目的は達成でき、ちょっと複雑(複数テーブルを使う、列を入れ替える、削除するなど)になれば、Power Query や Power Pivot を使います。

Excelデータ接続
(Office 365 SharePoint Online のリストアイテムの取り込みは OData フィードを使うと幸せになれることが多い。くわしくはこちらを参照)

定型の業務であれば、一度つくったピボットテーブルや表を Excel Services の Excel Web Access を使って SharePoint Online のポータルに貼り付けておけば、ローカル(PC)で Excel を開くことなく状況の確認が可能で、協会のメンバーと情報を共有できます。

Excel ユーザーのための Excel Services – Office 365

強いて言えば、協会のメンバーは全員が Excel を使いこなすことができるので、準備したデータを「あ~したい」、「こ~したい」、と思ったら自分で加工できるのが大きな強みでしょう。

このように協会ではピボットテーブルやオートフィルターを活用・多用するので、最新データは「データ更新」ボタンを押してクラウド上のデータを Excel で確認するか、条件が変われば自分でピボットテーブルやオートフィルターを変更するわけですが、一般的にはそうではなく、日次や月次などで「決まった形」でワークシートを用意する、印刷して提出しなければならないケースを聞くことが多いです。(繰り返しになりますが、協会内部の業務ではこのようなケースはありません。)

例えば、支店別の売り上げとか、製品別の売り上げとか、年月での売り上げでまとめたワークシート。[東京支店][大阪支店][名古屋支店] といったワークシートが並んでいたり、[2015年4月][2015年5月][2015年6月] というようなワークシートが並んでいるブックです。

比較・確認する数字などは定例の会議で確認する項目などはほとんど決まっているのでピボットテーブルを駆使した方が最新データへの更新も考えると楽なのに、、、と思うこともしばしばありますが、もうそうやって何年も十何年もその資料を使っていると、なかなか変更するのも大変です。(変更したほうがいいんですけどね)

だったら、その作り方で楽をしよう、というのが今回のテーマです。Excel VBA と ピボットテーブル の話になります。

グループ化と明細の表示を使う

Excel のピボットテーブルには「明細の表示」という機能があります。通常はピボットテーブルによる集計済みの項目の集計元データを、別のワークシートに作成し内容を確認するためのものです。

PVTSYOSAI

たとえば、日付項目を「年・月」でグループ化したピボットテーブルを作り、年月の集計値をダブルクリックすると、その年のその月のデータだけが「明細の表示」としてワークシートが追加されます。

クラウドやサーバーからデータを Excel に取り込み、年・月のシートに分割する、というような定型の業務がある場合は、オートフィルターを使ってコピーする方法より、このピボットテーブルと明細の表示を使うほうがあるかに手間がかかりません。そして、この手作業を VBA で自動化すれば、一瞬で年月ワークシートを作成することができます。元データは SharePoint や SQL Azure といったサーバーから取ってきますので、その時点での最新データから年月ワークシートを作成することが可能です。

グループ化は日付だけではなく、支店名や、製品グループなどの文字列データでも有効です。ピボットテーブルのグループ化の機能が強力なのは、あらかじめどんなユニークデータがあるかを知っている必要がないことです。この性質を利用して「重複データの削除」のような使い方も可能です。

重複したデータをチェックする - ピボットテーブルの応用

マクロ / VBA でピボットテーブルを作る

実践ワークシート協会代表理事/Office TANAKA の中の人 田中亨曰く、 マクロ記録で恐ろしい(難解な)結果が出る代表格の1つであるピボットテーブルの作成ですが、実は、非常にシンプルなコードでまとめることができます。このあたりは田中亨著の「Excel VBA 逆引き辞典」にシンプルなサンプルコードが紹介されています。

1回かぎりであれば手作業で十分ですが、毎回毎回同じことを繰り返す、作成するシートの数が多いようであれば、VBA を用意してそれを実行するだけで、ある条件でワークシートを作成することを自動化可能になります。

手順としては大きく4つです。

  1. 元の表(テーブル)から条件を設定してピボットキャッシュ、ピボットテーブルを作成する
  2. ピボットテーブルから詳細の表示を使って詳細ワークシートを作成
  3. 作成したワークシートにあるテーブルを並べ替え
  4. 作成したシートの名前を変更

それぞれの手順を見てみましょう。

例として以下のようなテーブルから年月別のワークシートを複数作成します。(サンプルは 10,000件)

datasample

1. 元のテーブルから条件を設定してピボットテーブルを作成する

Sub CreatePivotTable()
   'エラー処理なし。元のデータしかない、状態からのマクロ
   Dim cWS As Worksheet
   Dim nWS As Worksheet
   Set cWS = ActiveSheet  '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
   Set nWS = Sheets.Add   'シートを追加して、nWS とする ActiveSheet は nWS
   nWS.Name = "tmp"       '追加シート nWS の名前を tmp に変更
   Dim D As Range
   Set D = cWS.Range("A1").CurrentRegion  '元のシートの A1 を含むデータの固まりを D とする
   ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1")  'Dからピボットキャッシュとテーブルの作成
   With nWS.PivotTables(1)  'ピボットテーブルのフィールドリスト操作を以下で行う
       .PivotFields("日付").Orientation = xlRowField  '日付を行に設定
       .PivotFields("金額").Orientation = xlDataField  '金額を値に設定
       .ColumnGrand = False  '集計行の総計を非表示
   End With
   Range("A3").Group Periods:=Array(False, False, False, False, True, False, True)  '日付行を年・月でグループ化する。マクロ記録で調べるとよい。
End Sub

実行結果は以下のようになります。

CRTPIVOT2

ピボットテーブルから年月シートを VBA/マクロで作成する

ピボットテーブルができてしまえば、あとは [詳細の表示] に相当するコードでワークシートを追加していきます。大まかな手順は以下になります。

2. ピボットテーブルから[詳細の表示]を使って、各月のワークシートを作成する

3. 作成したワークシートを日付でソートする

4. シートの名前を年月にする

5. 最後にピボットテーブルのシートを消す

ここは一気にいきます。

上記ピボットテーブルを作成したソースに 2, 3, 4, 5 を実現するコードを加えたものが以下です。

Sub CreateMonthlySheets()
   'エラー処理なし。元のデータしかない、状態からのマクロ
   Dim cWS As Worksheet
   Dim nWS As Worksheet
   Set cWS = ActiveSheet  '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
   Set nWS = Sheets.Add   'シートを追加して、nWS とする ActiveSheet は nWS
   nWS.Name = "tmp"       '追加シート nWS の名前を tmp に変更
   Dim D As Range
   Set D = cWS.Range("A1").CurrentRegion  '元のシートの A1 を含むデータの固まりを D とする
   ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1")  'ピボットキャッシュとnWSシートにピボットテーブルを作成
   With nWS.PivotTables(1) 'ピボットテーブルのフィールドリスト操作
       .PivotFields("日付").Orientation = xlRowField  '日付を行に設定
       .PivotFields("金額").Orientation = xlDataField  '金額を値に設定。各年月シート作成のためのフィールドで「金額」そのものには意味はない。
       .ColumnGrand = False  '集計行の総計を非表示
   End With
   Range("A3").Group Periods:=Array(False, False, False, False, True, False, True)  '日付行を年・月でグループ化する。設定はマクロ記録で調べるとよい。
   Set D = nWS.PivotTables(1).DataBodyRange  'ピボットテーブルの値の範囲(金額集計)を D とする
   Dim Item As Range
   For Each Item In D  '金額集計の範囲の数だけ For Each を回す。個々のセル(Range)は Item に入る
       Item.ShowDetail = True  '詳細の表示を行う。このとき追加されたシートがアクティブシートになる
       With ActiveSheet.Sort.SortFields '追加されたシート(アクティブシート)で日付(A2)のソート条件を設定する
            .Clear  'おまじない
            'A2の日付の値で、昇順(xlAscending)、標準(xlSortNormal) でソートする条件を設定する

            .Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       End With
       With ActiveSheet.Sort 'アクティブシートの Sort オブジェクトでソートを実行する
            .SetRange Range("A1").CurrentRegion 'A1を含むデータのかたまり(範囲)を対象とし
            .Header = xlYes '一番上の行はヘッダーとして
            .Orientation = xlTopToBottom '上から下に向かって
            .Apply 'ソートを実行
       End With
       'セル A2 の日付から年月の文字列を作成し、シートの名前にする
       ActiveSheet.Name = Format(ActiveSheet.Range("A2"), "yyyy") & "年" & Format(ActiveSheet.Range("A2"), "m") & "月"
    Next Item
    Application.DisplayAlerts = False 'ピボットテーブルのシートを消す際のアラート表示をオフにする
    nWS.Delete 'ピボットテーブルのシートを消す
    Application.DisplayAlerts = True 'アラート表示を元に戻す
End Sub

10,000件程度であれば、数秒で年月別シートの作成が可能です。

CRTPIVOT4

最近は Power Query や Power Pivot といった機能の追加・拡張が Excel では多いですが、ワークシートを消す、新規に作る、追加するといった作業は VBA でしかできません。
そういった作業の自動化の要望がある限りは VBA がなくなることはないでしょうね。

ただ、ちょっとだけ「手間」を入れると Power Query の機能だけでも上記のことが可能になります。
機会があれば、Power Query で「年月別のシートを作る」手順をご紹介したいと思います。

2015/07/07

Power Query から https 経由で SharePoint の Excel ブックを開く

Power Query (2.23.4035.242 June 2015 Release) で https 経由で SharePoint のドキュメントライブラリに保存されている Excel ブック、および OneDrive for Business にある Excel ブックへのアクセスが可能になっていることを確認しました。(もしかしたら、May 2015 Release からかもしれません、、、、)

Power Query は頻繁に更新されています。数か月前は https 経由で SharePoint や OneDrive for Biz の Excel へのデータ接続はできませんでした。ローカル PC の同期フォルダーを使っているのであれば、同期フォルダー内の Excel ブックにデータ接続可能でした。

今後は同期フォルダーを構成していなくても、https 経由で Excel ブックに接続可能です。(ただし、コンシューマー版の OneDrive へのアクセスは成功していません。OneDrive for Business は Office 365 で提供されている OneDrive です。これは SharePoint をベースにしています)

取り込み手順

使い方はいたって簡単です。

[Power Query] タブの [外部データの取り込み] の [ファイルから] にある [Excel から] を選択します。

PQDCFILEEXCEL

[ファイル名] に SharePoint ドキュメント ライブラリーの URL を入れます。

SPDOCLIBURL

この URL は SharePoint のドキュメント ライブラリーであれば_layouts の前までになります。
OneDrive for Business も同様です。

doclibURL

[開く] ボタンを押すと、SharePoint サイトにある「すべてのサイト コンテンツ」のリストが表示されます。

今回は [ドキュメント] にあるブックを開くので、[ドキュメント] をダブルクリックします。(英語表記は Shared Documents)

allsitecontents

ドキュメント ライブラリにある Excel ブックを選択し、ダブルクリックするか、[開く] ボタンを押します。
すると、Web コンテンツへのアクセス ダイアログが表示されるので、[組織アカウント] を選んで、該当する URL をチェックします。
既定が [匿名] になっているので注意してください。

AccesstoWebContent

組織アカウント(Office 365 の ID)とパスワードでサインインします。

サインインが完了すると、以下のダイアログになるので [接続] をクリックします。

AfterSignIn

ナビゲーター ウィンドウが表示されるので、取り込みたいシートやテーブルを指定します。

PQNavigatorWindows

[編集] ボタンを押せば、クエリ エディタ ウィンドウが表示され、クエリの編集をすることができます。
クエリ エディタ ウィンドウではデータの絞り込みや並べ替え、列の削除や追加、データ型の変換を行い、欲しい形でデータを取り込むことができます。

QueryEditorWindow

[閉じて読み込む] を押せばワークシートにデータが展開されます。特に編集の必要がなければナビゲーター ウィンドウの [読み込む] ボタンでデータの読み込みが可能です。

ImportData

これまで、ファイルサーバー上の Excel ブックへのデータ接続は可能でしたが、https 経由でのデータ接続は少なくとも半年前はできませんでした。これにより、\\サーバー名 でのアクセスと同じように https:// で SharePoint 上にある Excel ブックへの接続が可能になりました。

何がうれしいの?

真っ先に思い浮かべたのが「Excel アンケート」です。

アンケートの共有をしている間は、ローカルの PC/Excel でブックを編集モードで開くことができません。

locked

アンケート集計が溜まってくると、ピボットテーブルを使って分析したくなるのですが、Excel Online ではピボットテーブルをゼロから作ることができません。
こんなときには Power Query を使って、SharePoint にある Excel ブックをクエリで読み込むことでピボットテーブルを作り、データ分析が可能になります。もちろん [データ更新] によって、最新のデータにすることも可能です。

次に使えるのは外部ブックのテーブル オブジェクトへのリンクです。これは SharePoint のドキュメント ライブラリーにある Excel ブックに限らず、ローカル PC やファイルサーバーでも同様です。

ここに、外部ブックの範囲に対して VLOOKUP を使ってデータを参照しているブックがあるとします。範囲の場合は再計算によって「その時の」外部ブックのデータを取り込む(=更新)することが可能です。

このブログで過去に紹介しているように、VLOOKUP や INDEX、MATCH などの「範囲指定」する関数にとって、範囲を「テーブル」にすることで、データの増減に自動的に対応可能になることは、テーブルを使う最大のメリットなのですが、テーブルの場合は外部リンクは使えないのです。このことはマイクロソフトの技術記事でも紹介されています。

Excel テーブル 数式で構造化参照を使う(support.office.com)

ここで「構造化参照を活用するヒント」に以下の記述があります。

“他のブックの Excel テーブルへの外部リンクを含むブックを使用する     ブックに別のブックの Excel テーブルへの外部リンクが含まれている場合は、リンクを含む「リンク先」ブックの #REF! エラーを回避するため、そのリンクされた「リンク元」ブックを開いておく必要があります。リンク先ブックを最初に開くと、#REF! エラーが表示され、その後リンク元ブックを開くと、エラーは解決します。リンク元ブックを最初に開くと、エラー コードは表示されません。” (引用おわり)

つまり、リンク先ブックを開いておかなければ、別のブックのテーブルへリンクしている数式は #REF! エラーになります。

これを回避するために、リンクを使わず、Power Query を使うことで #REF! エラーを回避しつつ、データ更新によって最新データを参照することが可能になります。

この機能拡張によって、Office 365 の SharePoint や OneDrive for Business を Excel の保存先として、従来のファイルサーバーのように使うことが可能になりました。

 

毎月にようにリリースされる新しい Power Query については、英語ですが Office Blogs で新リリースごとに紹介されています。

https://blogs.office.com/

Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018