[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

Excel 2016 のピボットテーブルの自動時刻グループ化機能のグループ化を解除する

Excel 2016 の追加更新機能で Automatic Time Grouping (時刻のグループ化) 機能があります。

日付データをピボットテーブルのフィールドリストにあるレイアウトセクションの列や行にドロップすると、年・四半期・月に最初からグループ化してくれる機能です。

それまでは、ピボットテーブルの行や列のラベルフィールド上で、右クリックメニュー（コンテキストメニュー）から [グループ化] を選んで、年や月でグループ化していました。

日付の場合は、この操作なしにグループ化されるようになりました。

とはいえ、会計年度が1月～12月でない場合が多い日本の場合は、四半期のグルーピングが邪魔ではないでしょうか。実践ワークシート協会の会計年度は10月～9月なので、一般的に多い4月～3月ではないのですが、それにしても第1四半期は1月～3月ではありません。

ということで、この四半期のグループ化を外すことが多いのですが、これまで、自動的にグループ化され作成されたフィールドをレイアウトセクションから外す、という作業をするか、ラベルフィールド上のコンテキストメニューで [グループ解除] を選択していました。

この解除作業、上記の手順ではなく、日付フィールドをピボットテーブルに配置して自動時刻のグループ化された直後に Ctrl + Z で解除されることを知りませんでした・・・

以下は配置直後に Ctrl+Z をしています。

操作直後の Ctrl + Z で解除って、他にもあります。

ハイパーリンクです。

メールアドレスが多いでしょう。もちろん、URLのハイパーリンクも、入力直後の Ctrl+Z で解除されます。

ハイパーリンクはオートコレクトのオプション設定で「オフ」にすることができます。

では、時刻のグループ化をオフにするオプションがあるか調べたところ、Excelのオプションにはオン・オフをする項目はありませんでした。

どうしてもオフにしたい、という場合はレジストリの変更となります。（今の時代にそれですか！）

Windows版Excel 2016のピボットテーブルで時間グループ化をオフにする
https://support.office.com/ja-jp/article/6be5afed-348c-4db2-9f87-5ac262d67b3f

2016/10追記 さすがに Excel のオプションでオフにできるようになりました。

以上、Excel 2016で追加されたピボットテーブルの時間グループ化/時刻のグループ化機能の話題でした。

Read More

Power BI Desktop - Excel とどこが違うのか(2)

Excel ユーザーの観点からの Power BI Desktop その2です。

Power BI アドイン for Excel で、Power Query や Power Pivot を使っている Excel ユーザーにとって、Power BI Desktop はなかなか「使う場面が無い」という状況かもしれません。

ところが、データを加工する、保管する、という目的ではなく、「レポートを作成しメンバーと共有する」ということを最大の目的とすると、Microsoft の製品やサービスでは Power BI サービス (https://powerbi.microsoft.com/ja-jp/) の利用を検討することになるでしょう。この BI レポートの活用のエリアは Microsoft 以外では「脱Excel」を掲げて様々なサービスがしのぎを削っている分野でもあります。(他には Tableau や Oracle の BICS など、BIツールで多数あります。）

そんな観点から「違いを明確にする」というよりも Excel ユーザーのための Power BI Desktop 的なコンテンツになってしまいました。

前回は、テーブルのビジュアルを使って、テーブルを表示する、という手順（というか、考え方ですね）をご紹介しました。データを表示させるためだけに「視覚化」と呼ばれるビジュアルのパーツを使うので、Excel ユーザーにとっては慣れない「考え方」と思います。
ページレベルフィルターを使ってデータを絞り込んでいましたが、フィルターではなく「スライサー」を使うことで、レポート上でデータの絞り込み条件を変更することができます。このあたりになってくると、テーブル機能やオートフィルター機能というよりも、当たり前ですが、考え方や作り方は Power View に相当近くなりますよね。

今回ご紹介するのもグラフではなくて生のデータを表示させるビジュアルである「マトリックス」です。

これは「クロス集計表」と呼ばれるものにも近く、、Excel ではピボットテーブル機能を使って作成する人も多い、データ分析や集計をする人にとっては人気の機能です。

ひとつ言えることは、この Excel の分析機能である「ピボットテーブル機能」と同じ感覚で、Power BI Desktop のビジュアルの「マトリックス」を使うと、かなりストレスが溜まってしまい、本末転倒な状況や作業になるということです。

ピボットテーブルと Power BI Desktop 「マトリックス」の違い

Excel ユーザーがピボットテーブルを「操作」している時は、ピボットテーブルのフィールドリストからフィールドを選択し列にしたり行にしたり、シリアル値として持っている日付をグループ化したり、値フィールドの集計の方法や計算の種類を使って、より数字の特徴がわかるような表を作成しようとしています。（もちろん、作った表に条件付き書式を適用して、、、ということもやります）

この Excel ピボットテーブルの「より数字(データ）の特徴がわかるような表」というのは、Power BI Desktop や Power BI Service では様々なビジュアルを使って作られた「レポート」であり、分析はレポートを使って行われるべきもの、という考えが根底にあります。そのための「ライブ ダッシュボード」であり「対話型レポート」なのでしょう。

ピボットテーブルに似た、でも、Power BI Desktop のただ１つのビジュアルに過ぎない「マトリックス」に、ピボットテーブルと同じものを期待してはいけないと、ある日気が付いたのです（笑）。

Excel のピボットテーブルの作成で普通に利用する「値フィールドの設定」ダイアログボックスでできることは、実は Power BI Desktop 系では「メジャー」と呼ばれるものができることに相当します。[集計方法]タブにある合計や個数のカウントといった計算は、メジャーでは「暗黙的なメジャー」と呼ばれているもので、このレベルであれば、Power Pivot や Power BI Desktop でも簡単に用意できます。

[計算の種類]タブでは、行・列の比率、日付を含めた基準値に対する差や比率、累計といった計算方法が「あらかじめ用意されて」います。この選択してオプションを設定するだけで複雑な計算がすぐにできるのが Excel ピボットテーブルのメリットです。同じようなことを計算メジャーを使って Power BI Desktop で実現するには DAX で数式を作成する必要があります。

DAX (Data Analysis Expressions) についてはこちらをどうぞ。
クイック スタート：30分で学ぶDAXの基礎　(support.office.microsoft)

複雑な、込み入った計算は DAX を使って自由に数式を作成する優位性はありますが、[計算の種類]にあるようなワークシート上のデータを前提とした、よく使う分析までも１から DAX によるメジャーの作成となると入口の敷居があがります。

そのため、「なんで Excel のシリアル値を使った日付データなら、簡単に日付のグループ化ができるのに、マトリックスだとできないだ！」とイライラしても仕方ないんです。「Excel だと表示形式変えたら January じゃなくて 01 とかに簡単になるのに、なんでめんどくさいんだ」と言ってもしょうがないんです、、、

特に、、、Excel ユーザーが Power BI Desktop や Service を使うと「日付」の扱いの違いに驚きます。というか、冷静に考えると Excel のシリアル値を使った日付の処理と表示形式による表示方法、というのが、ちょっと便利すぎるのかもしれません。

私の経験から、ピボットテーブルを使いこなしてきたユーザーにとって、Power BI はある程度割り切りが必要じゃないか、と感じています。


たぶん、マトリックスを使った「クロス集計表」を Power BI Desktop ではあまり多用しないでしょう。Excel でピボットテーブルによる分析に慣れている人は、データの特徴的なものは Excel のピボットテーブルであらかじめ検討し、より、効果的にその特徴を他のメンバーと共有するために、適切なグラフのビジュアルを選び視覚化して、ダッシュボードを作成する、 そこで Power BI Desktop と Power BI Service を使う、というものです。

レポート共有はしたい、かつ、ピボットテーブルをメインにしたい

そんなあなた（私？）に朗報です。それが、Power BI publisher for Excel です。
ここのブログでも紹介しました。

Power BI publisher for Excel （日本語版）のインストール
http://road2cloudoffice.blogspot.jp/2016/04/power-bi-publisher-for-excel.html

大好きな(笑)ピボットテーブルを Power BI Service のダッシュボードにピン留するのも「ワンクリック」です。

 Power BI Service のダッシュボードには指定した範囲が画像として共有されます。

ですが、やはり Power BI Service でダッシュボードを作り、他のメンバーと共有するのであれば、対話型でライブなダッシュボードを作成したほうがいいでしょうね。

Read More

Power BI Desktop - Excel とどこが違うのか

Power BI アドイン for Excel で、Power Query や Power Pivot を使っている Excel ユーザーにとって、Power BI Desktop はなかなか「使う場面が無い」という状況かもしれません。

ところが、データを加工する、保管する、という目的ではなく、「レポートを作成しメンバーと共有する」ということを最大の目的とすると、Microsoft の製品やサービスでは Power BI サービス (https://powerbi.microsoft.com/ja-jp/) の利用を検討することになるでしょう。この BI レポートの活用のエリアは Microsoft 以外では「脱Excel」を掲げて様々なサービスがしのぎを削っている分野でもあります。(他には Tableau や Oracle の BICS など、BIツールで多数あります。）

方や、、、圧倒的なユーザー数を誇る Excel でも Power View を使って Power BI で利用できる「レポート」を作成できるとはいえ、Power BI アドイン for Excel は、Office のバージョンに注意してもエディションが Professional Plus が必要なものがあったり、Office 365 ProPlus の Excel しかサポートされていないものがある、Power View の扱いがどうにも(Silverlightだったり、リボンになかったり）、、、といった状況から、無料ツールである Power BI Desktop の利用機会が増えるか、Power BI Desktop と同じ使用感の Power BI Service (Web) 上での[レポートの編集]を使う場面が増えてくるようにも感じられます。(いや、、、はっきり言って使いづらいですよ。Office 365 ProPlus = Office 365 の E3 以上 ならいいんですけど、パッケージの Office/Excel とか大変です。） 

そこで、いざ、Excel でピボットテーブルを使ってレポートを作っていたユーザーが、Power BI Service の Web 上のレポート編集ツールや Power BI Desktop を使ってレポートを作ろうとすると、勝手が違うので戸惑ってしまいます。

ええ、私が最初そうでしたから（笑

このブログの(長い)タイトルが「Excelユーザーのための、、、」なので、Excelユーザー視点で、Power BI Desktop での表や、グラフの作り方（＝ビジュアルの使い方）を考えてみたいと思います。

Excel ブックのデータをそのまま使える Power BI

Power Query（Excel 2016～だと [取得と変換] )と同様の機能が Power BI Desktop でも使えるので、データの取得と変換を Power BI Desktop でも同じことができるのですが、ここでは話を簡単にするために、様々なデータソース（SharePoint や Access や SQL Server など）から Excel にデータを集めて、現在は Excel で分析用のデータを収集しているとしましょう。

そのデータをそのまま Power BI Desktop で使えるのが魅力です。

ローカルのPCや、OneDrive、 SharePoint のドキュメントライブラリなど、さまざまな場所にあるブックをデータの取得先として指定、編集できるのも大きな魅力ですが、今回は、分析・グラフ化を主眼にしますので、すでに、データセットとして Power BI Desktop に取り込んでいる、というところから話を始めます。

「ビジュアル」（視覚化）の種類を知らないと話にならない

データはそこにある。そして、そのデータを「どう見せたいのか」。これが決まっていないと話にならない、、、Power BI Desktop では、そんな感じがします。

Excel では多種多様なグラフを作成することができます。

Power BI Desktop の既定のビジュアルの種類は以下です。

ここで、きれいなグラフを作ろう、Power BI 的にはスライサーとかを使った対話型のグラフのレポート（Power View に相当）を作ろう、となると思いますが、Excel ユーザーはそこから入ると、ちょっと最初の１～２歩を飛ばしている感があります。

たとえば、Power BI Desktop で「オートフィルター」で絞り込んだ生データを作るのはどうするか？この普通に Excel でやるデータ加工を Power BI Desktop でやってみます。

「テーブル」すらビジュアル

ワークシートのテーブルで生データを確認しながら、オートフィルターをかけてデータを絞り込む、、、という作業が当たり前な Excel からすると、「え？」と思うかもしれませんが、「絞り込んだ後の結果を見る」ということですら、「ビジュアル」を作る必要があります。

データセットにフィルター(絞り込み）をかけてしまうのはいただけません。他の絞り込み条件ができなくなるからです。データセットはなるべく必要最小限で加工した「生データ」を持っているべきと言えます。

ということで、Power BI Desktop のビジュアル = 視覚化 を見ると、「テーブル」があります。

このテーブルを選択すると以下のようなビジュアルのテンプレートが作成されます。

まさに、、、テーブルのような表の様相ですよね。
実際のデータは以下のような Excel のテーブルがデータセットとして登録されています。

では、東京だけのデータセット（Excelブック)と同じ表（テーブル）を作ってみましょう。
作る手順が以下です。（え～それ？と思うでしょうが、これが、最初はできない、、、笑）

1) [レポート]を選んだ状態で、視覚化(ビジュアル）で [テーブル] をクリックしてテーブルのテンプレートを作る
2) データセットの列の左からの順番で [フィールド]の列項目をその順番通りに選ぶ（順番が大事）
3) ページ(=ワークシート的)で絞り込みの条件を設定したいのであれば、ページレベルフィルターに、フィールドにある [場所] をドロップして、東京を選択

という手順を以下のアニメーションGIFで。途中で表示のフォントサイズを変えたりしていますが、キーとなる手順はわかると思います。

日付データに関しては、いろいろなオプションや勝手にやっちゃってる感じの処理がいっぱいありますが、まぁ、こんな感じの操作になります。このように、テーブルや表というグラフじゃないデータ表現も、確実に、完全に、Power BI でやることができます（やる人がいるかどうかは別ですがｗ）

これは、単純な表・テーブルですが、これをピボットテーブルような「クロス集計表」も当然できます。ただ、ピボットテーブルじゃないんです。ビジュアルでいう「マトリックス」になるんです。

グラフのビジュアルもインパクト大で重要ですが、このような「数字に表」をまずは自由に扱えること。これが Excel ユーザーが安心して Power BI Desktop や Service を使える前提となるのではないでしょうか。

次回は上述のピボットテーブルっぽい「マトリックス」を取り上げたいと思います。

Read More

Power BI publisher for Excel （日本語版）のインストール

Power BI publisher for Excel が、2016年4月7日 公開で バージョン 2.33.3232.21561 になりました。（ダウンロードセンターではこのバージョンが表示されますが、インストールした後で Excel で確認すると、バージョン 2.33.3272.21901 でさらに更新されているようです）

日本語版アドインもあり、そのインストーラーもダウンロードできます。ただ、日本語のダウンロードページから言語設定をせずにダウンロードすると英語版がダウンロードされるので、優先言語を日本語に設定してダウンロードする必要がありますので気を付けてください。

Power BI ダウンロード ページ
https://powerbi.microsoft.com/ja-jp/downloads/

右下に「Power BI publisher for Excel」があります。
そこから Power BI publisher for Excel のダウンロードページに移りますが、日本語のアドインを使いたい場合は、[ダウンロード(32ビット)] のボタンをクリックせずに、[優先言語でのダウンロード]をクリックしてください。

Download Center のページに移るので、日本語(Japanese)を選択して、[Download] をクリックします。

通常は32bit版を選ぶことになるでしょう。ファイル名に ja-JP があり、32bit という文字があるファイルを選びます。

ダウンロードした msi ファイルを実行します。インストールが始まります。

セットアップウィザードの指示に従い、インストールを完了させます。

Excel を起動すると [Power BI] タブがあり、日本語のリボン コマンドを確認することができます。

このアドインにより、Excel のデータを Power BI サービスで公開・共有する方法が2つになりました。

一つは [発行 – Power BI への発行] から [クラウドに保存] でブック全体を OneDrive や Power BI サイトに保存し、Power BI サイトでデータを可視化するグラフなどのパーツを含むをレポートを作成、レポートのパーツからダッシュボードにピン留めする、これまでの方法。


そして、2つ目は Power BI publisher for Excel アドインをインストールした Excel の [Power BI]タブを使って、ワークシート上のグラフやセル範囲を「ピン留め」する方法。 Power BI サービスにサインインしていれば、そのアカウントで利用できる「ダッシュボード」のリストが表示され、ピン留めするダッシュボードを選ぶことができます。

ブックはクラウドに保存する必要はなく、ローカルPCにあっても Power BI サービスで共有することができます。ただし、その場合は明示的にローカルPC側から 「ピン マネージャー」を使ってピン留めされたグラフなどを更新しないと、Power BI サービス側のデータは最新にならないことに注意してください。

自動更新が必須ではなく、手軽に Excel のデータ、グラフなどを共有したい場合、Power BI publisher for Excel は非常に導入しやすいソリューションですね。

Read More

[Excel] テーブルと Excel VBA

ワークシート関数を使う時、これまでの「範囲」の変化に対応することができるテーブル機能は Excel 2007 以降の最大の機能拡張、、、なんてことも言われています。

過去にこのブログでもいくつかテーブル機能について紹介しました。

テーブルのすすめシリーズ
http://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
http://road2cloudoffice.blogspot.jp/2014/11/office-365-sharepoint.html

機能、関数で多くのメリットを提供するテーブルですが、Excel VBA の世界ではどうでしょう？

まず、表としての範囲が、テーブルというオブジェクト（ListObject オブジェクト）になるため、最終行＋空白問題（上から End モードで下に行ったとき、空白があったら止まる）、もしくは最終行＋ごみ問題（行の一番下から上に上がっても、途中にごみがあればそこで止まる）は基本的になくなります。

というのも、テーブルではテーブルの行数は End モードを使わずとも、テーブルのプロパティで確認することができるからです。テーブルの行数は ListRows.Count でわかります。

ここで標準的なテーブルを実践ワークシート協会の田中亨が推奨する「黄金テンプレート」で処理するコードを見てみると以下のようになります。

サンプルテーブル

サンプルテーブル

テーブルでも表の処理同様の VBA コードを書くことができます。
名前が田中だったら、個数X単価を合計に代入する、といった処理だと、田中亨の黄金テンプレートで考えると、

範囲はどこか？　

行は 3 から 12。もちろん、最終行の 12 が可変だとしても、End モードで特定します。

条件はなにか？

条件は、名前のセルが "田中" だったら、ですね。

処理は何か？

合計セルに個数X単価を代入する、です。

そうすると VBA のコードは以下のようになりますね。

Sub sample1()
    Dim i As Long
    For i = 3 To Range("B2").End(xlDown).Row
        If Cells(i, 2) = "田中" Then
            Cells(i, 5) = Cells(i, 3) * Cells(i, 4)
        End If
    Next i
End Sub

これをテーブルのオブジェクトを使って、テーブルらしく書いてみると、上記の表形式とは大きく違う点があります。それは「列番号」であり、「行番号」の考え方になります。

テーブル オブジェクトの世界では、ワークシートの「A列」や「D列」は意味がなく、あくまでテーブル内での列順序の番号です。上の例でいえば、名前の列は、B列で列番号2ではなく、あくまで名前フィールドであり、フィールドの番号はテーブル内で「1」になります。

さらに、テーブルというオブジェクトは「ListRowsコレクション」からデータ部分が構成されています。テーブルの行の各1行は ListRow オブジェクトとして扱うことができます。このことより、「何行あるか、わからないけど、For Each を使えば、そのテーブルのすべての行（ListRowsコレクション）を扱うことができる」として処理可能になります。

そのサンプル コードが以下です。

Sub sample2()
    ''1 名前, 2 個数, 3 単価, 4 合計　←テーブル内での順序
    Dim tb As ListObject
    Set tb = ActiveSheet.ListObjects("TestTable")  ''テーブル名がTestTableだった場合
    Dim R As ListRow
    For Each R In tb.ListRows
        If R.Range(1) = "田中" Then
            R.Range(4) = R.Range(2) * R.Range(3)
        End If
    Next R
End Sub

たぶん、R.Range(1) という書き方ってあまり使っていなかったと思います。

この R.Range(1) という書き方は、以下の書き方と同じものです。

R.Range.Item(1)

R.Range.Cells(1)

おもしろいですね。

VBAセミナースタンダードで田中亨がよく「上級者は2次元配列は使いません。だって、Excel VBA にはワークシートがあるんですから！」と言いますが、上記の表記はその流れです。（正確にはこれは一次元配列ですけど）

For Each でとってくる R は一次元配列（的なもの)です。（要素の型が違うので配列とは言えませんが、便宜上お許しを）この1次元配列は1列または一行で複数セルを選択した「セル範囲」のイメージです。今はテーブルなので、以下のような横方向の1行のセル範囲です。

[[名前] [個数] [単価] [合計] ]

この順序です。ですから、名前は (1) で、個数が (2)、、、となるわけです。

その表記方法が R.Range(1) だったり、R.Range.Item(1) だったり、R.Range.Cells(1) になるわけです。セル範囲の Range オブジェクトを配列のイメージとして扱っているんですね。

繰り返しますが、ポイントは A列、B列といったワークシート上の絶対的な番地では無い！ということです。

上記の例はサンプルなので処理や条件がシンプルなものですが、これが複雑になってくると、Cells(行, 列) でセルを指定するやり方と、R.Range(要素) で処理すべきセルを指定する方法のどちらがいいかはだんだん変わってきます。

その意味から、変数を使って、テーブルの列順序を定義すると、さらに可読性が高まります。（IMEオン・オフの操作があるので日本語変数がいいわけではありませんが、サンプルとして定義します）

Sub sample3()

    Dim tb As ListObject

    Set tb = ActiveSheet.ListObjects("TestTable")

    Dim 名前 As Long, 個数 As Long, 単価 As Long, 合計 As Long

    名前 = tb.ListColumns("名前").Index

    個数 = tb.ListColumns("個数").Index

    単価 = tb.ListColumns("単価").Index

    合計 = tb.ListColumns("合計").Index

    

    Dim R As ListRow

    For Each R In tb.ListRows

        If R.Range(名前) = "田中" Then

            R.Range(合計) = R.Range(個数) * R.Range(単価)

        End If

    Next R

End Sub

たった5～6行の処理のための変数宣言と変数設定で7行は、、、と思いますが、通常はもっと条件や処理が複雑になるので、これだけで「行数が多い、無駄だ」と判断するのは早計だと思います。 For Each から Next R までの処理の「可読性」を見てください。実にわかりやすい表記になっています。

たぶん、テーブル ListObject オブジェクトを VBA で扱うのは、このオブジェクト変数を使ってテーブルを設定し、テーブル内のフィールドの順序を変数として設定していくのが一番良い方法だと思います。

ここでテーブル機能で重要な「構造化参照」が出てきてませんよね？

いろいろ試した結果、この「構造化参照」は VBA を編集するツールである VBE で実装されていないため、インテリセンスのような形で、文字通りの「構造化参照」という機能は実装されていないようです。テーブル名を指定すれば、そのテーブルにあるフィールドが表示される、、、ような機能は VBE には無い、ということです。

では、VBE に期待しないで表記したらどうなるでしょう？

ここでも Excel の Range オブジェクトの実装、、、「すげー」となるわけです（笑

VBE では構造化参照のメリット享受できないので使う理由がないのですが、無理やりやると以下のようなコードを書くことができます。

Sub sample4()

    ''1 名前, 2 個数, 3 単価, 4 合計　←テーブル内での順序

    Dim R As Range

    For Each R In Range("TestTable").Rows

        If R.Cells(1) = "田中" Then

            R.Cells(4) = R.Cells(2) * R.Cells(3)

        End If

    Next R

End Sub

テーブルを ListObject として宣言しない分だけ行数が短いですが、R.Cells(1) などを番号ではなくフィールド名で、、、となると、宣言や設定のためそれなりに行数が増えます。さらに、構造化参照が VBE でサポートされていないために、複雑、長くなればなるほどめんどうなことが発生してきます。(*1 最後に追記してます）

以上のことより、通常、テーブルを VBA で扱うのであれば、テーブルは ListRow のかたまりであり、テーブルを ListRows コレクションとして処理するのが王道でしょう。

そのときの For Each として１つ１つ処理するのは ListRow オブジェクトであり、その ListRow オブジェクトの扱いは「一次元j配列」的になる、と言えるでしょうね。

今後、データ接続でテーブル形式でデータを取り込むことが多くなります。そのときは、上記の考え方でテーブルをマクロで扱うことも多くなると思います。

(*1) 構造化参照が少なすぎるとおもったので追記。
構造化参照方式で以下のような表記が可能です。

Range("テーブル1[名前]")

上記でテーブル１の名前フィールドのデータ部分の範囲を指し示すことができます。
しかし、この Range(構造化参照) は、あくまで Range による範囲（つまり、named range) のため、ListObject との関連がありません。

そのため、Range("テーブル[名前]").Column は、ワークシート上の列番号を返します。


[PR] VBAセミナーを受けた後は、これさえあれば何もいらない。

Read More