[Excel 取得と変換] Power Query でクロス集計表・マトリックス表を表形式・テーブル形式に変換する

前回に引き続き、これも今は Power BI で簡単にできる、Excelユーザーにとっての長年の夢、というか、実務でよく直面する課題です。
(この話題の2018年更新版の投稿はこちらです。https://road2cloudoffice.blogspot.jp/2018/03/blog-post.html）

クロス集計表をリスト形式・テーブル形式に変換したい、というものです。これ、Power Query だと一瞬でできるんですよ。

この変換には、Power Query の機能を使いますので、Excel 2016 であればデータタブの [取得と変換]、Excel 2013 であれば Power Query をマイクロソフトのサイトからダウンロード、Excel 2010 の Professional Plus (企業向けのライセンス）ならば Excel 2013 同様に Power Query をダウンロードし利用することで、解決できます。

以下のようなクロス集計表(マトリックス表）あります。

ここから、ある日突然「A001だけの4月の合計と、地域を数値の大きい順番で・・・」など言われるわけですよね。できないことはないんですが、関数を駆使したり、コピーしたり、と手間と時間がかかります。

もし、このデータを以下のようなリスト形式で管理していたら、ピボットテーブル操作だけで完了しますよね。

クロス集計表から、このようなリスト形式のテーブルを作成するのは手間と時間がかかる、数が多ければVBAをつかって展開していました。

Power Query (Excel 2016 では 取得と変換)を使ったテーブルへの変換はアニメーションGIFでみられるほど「一瞬です」（笑）。

手順は以下です。

1) クロス集計表をテーブルにします。テーブルにする方法はアクティブセルをクロス集計表の中にいれて Cｔｒｌ＋T または L、 リボンからは同じくアクティブセルをクロス集計表にいれて、[挿入]の[テーブル]グループの[テーブル]をクリックします。

2) テーブル形式にするのは、Power Query(取得と変換)で読み込みためです。テーブル内にアクティブセルをいれて、Power Query または取得と変換の [テーブルから] をクリックします。

3) テーブルになったクロス集計表を読み込んだクエリ エディターが立ち上がります。地域の列で null になっているセルがあります。

この null へ入力するデータはその上にあるデータのコピーになります。
このような場合、[変換]タブの[任意の列]グループの[フィル]の[下]を使います。

4) 4月から9月までの列を選択します。4月の列選択は [4月] の見出しをクリックします。Shift キーを押しながら [9月] の見出しをクリックすると、4月から9月までの列を選択することができます。

5) 選択した状態で、[変換]タブの[任意の列]グループの[列のピボット解除]をクリックします。これでクロス集計表がリスト形式のテーブルに展開されます。

6) 展開された列名を「月」に変更して、[閉じて読み込む]でワークシート上にテーブルを作成します。

この手順が上のアニメーションGIFになります。

なお、このテーブルの作成に使ったクエリを削除すると、元のクロス集計表とリンクしていないテーブルになります。クロス集計表の更新はあまりお勧めできませんが、クエリを保持しておけば、クロス集計表の数値が変わっても、[更新] でクエリ結果のテーブルを更新することもできます。

Read More

ピボットテーブル レポート間のグループ化の動きについて

先日、Power BI の勉強会に参加して、最初の小一時間、Power Query や Power Pivot といったPower BI アドインの紹介をしました。
その紹介で Excel ユーザーにとって、昔からこれができたらいいのに！という鉄板ネタがあるんですよ、それが Power BI で解決できるんです、という話をしたのですが、これも Excel 2007 以降で「これ知っていたらいいのに」と思うもののひとつで、間接的に Power BI に関連するものがあります。

それは、1つのデータ ソース（表やテーブル）から作成した複数のピボットテーブル レポートで発生する、「グループ化の連動」への対応です。

日付のグループ化が2つのピボットテーブルで連動する

ピボットテーブル レポート（以降、ピボットテーブル）は、表形式のセル範囲やテーブルを指定して作成します。その際に Excel は「ピボットキャッシュ」を作成し、そこからピボットテーブルが作られます。Excel 2003 まではピボットキャッシュを共有するかどうかウィザードで聞かれたのですが、Excel 2007 以降はウィザードが無くなり、ピボットキャッシュは共有されるのが既定となりました。

このグループ化が連動してしまう挙動は、上述のピボットキャッシュを共有しているからです。
Excel 2003 までは、この挙動が問題ならウィザードに対して「共有しない」を選べばよい、が対応方法でした。Excel 2007 以降はこのウィザードがリボンからなくなってしまったため、ちょっとした「裏技」的な方法で Excel 2003 まであったピボットテーブル ウィザードを立ち上げて対応しましょう、と紹介されているところが多いですし、マイクロソフトのサポートの記事にもそれがあります。

ピボットテーブル レポート間のデータ キャッシュの共有解除 (support.office.com)

ピボットキャッシュを共有することにより、ブックのサイズを小さくし、使用するメモリーを少なくする効果はありますが、ピボットテーブルの良さに気づき、複数のピボットテーブルを使ってレポートを作成して、ある一つのピボットテーブルのグループ化を解除したら、すべてのピボットテーブルのグループ化が解除される、なんてことは「どうしてそうなるんだろう？」と最初は悩みました。

この Alt+D, P で 2003 まで使用していたピボットレポート ウィザードを使って、範囲を変えて、キャッシュを作り直して、また範囲を戻す、なんてことを、全てのピボットテーブルに適用することなく、解決する方法があります。

ピボットテーブルを作成する際に、データ モデルに追加するだけでいいのです。

[このデータをデータ モデルに追加する] は、通常、リレーションシップ、Power Pivot で使われます。
Power Pivot を使わなくても、このチェックを入れることで、ピボットキャッシュはそれぞれ別に作られるので、複数のピボットテーブル間で発生するグループ化の連動を避けることができます。

データ モデルに追加する方法で3つのピボットテーブルを作成したブックでピボットキャッシュの数を調べると、ちゃんと 3 と出てきました。

ブックのサイズですが、サンプルデータは5列からなる1万件のデータで以下のようになりました。

10000件のテーブルのみ　３１５KB
ピボットキャッシュ共有で3つのピボットテーブル　４２７KB
ピボットキャッシュ共有解除の３つのピボットテーブル　６３７KB
データ モデルに追加した３つのピボットテーブル　５６２KB
(ピボットテーブルはいずれも空にしてます）

データ モデルを追加するとサイズが大きくなりそうな気がしますが、非常に高い圧縮技術を使ってコンパクトにしているという技術情報がマイクロソフトから出ています。

Create a memory-efficient Data Model using Excel and the Power Pivot add-in
上記の日本語版（機械翻訳）

データをデータ モデルへ追加して、ピボットキャッシュの共有をしない設定は、ピボットテーブル作成時のみです。一度作成してしまったピボットテーブルのピボットキャッシュの共有を解除するには、Alt + D, P で 2003 のウィザードを使うか、新しくピボットテーブルを作り直すことになります。

また、[ピボットテーブル ツール] の [分析] または [オプション] タブの [データソースの変更] を使って、Alt + D, P でやるようなデータソース範囲の変更、ピボットキャッシュを別にして、また、データソース範囲を元に戻す、という方法をとっても、ピボットキャッシュは再共有されます。データソースを元のテーブルや範囲に戻すと、再度、ピボットキャッシュを共有します。
このあたりは、どうしても「共有」を前提としたいようですね。

[追記 2016/5/9] 過去にこのブログでも紹介していますが、データ モデルに追加することでピボットテーブルの集計フィールド、集計アイテムの機能が使えなくなります。同様の機能は Power Pivot の計算列、メジャーで行います。集計フィールドを使う人は注意してください。
データ モデルに追加しても「日付のグループ化」ができるようになったのが大きいですね。

Read More