Excelではテーブルとクエリを活用すると楽になる

現在、新入社員向けのExcelトレーニング上級編講座を作成しています。今回の教材はスライド資料、講義用のサンプルファイル、実践課題、そして動画資料の４本立てで構成しており、内容もかなり濃厚なものに仕上げてます。まだ、動画資料が全部アップしていないので不完全ですが、それ以外はようやく用意できました。

その講義項目の中で今回、一番学んでもらいたいものが「テーブルとクエリ」です。実際の実務の現場では驚くほど使われてなかったりします。そして、それが故にミスをしたり、また古い手や資料を参考にして古典的なテクニックで乗り切ってるのが現状です。これを身につければ、今よりもずっと楽になること請け合いです。

Excelで身に付けるべきスキルコース（松）

今回使用するExcelシート

• テーブルとクエリサンプルファイル

テーブルとクエリのいくつかのサンプルがすでに入っています。比較対象とするような例示も含めて以下のようなシート構成です

1. index・matchの事例
2. 名前付き範囲の設定
3. テーブル
4. リレーションシップ機能
5. クエリ
6. マトリックス変換

Power Queryの事例がテーブル以降の例示になります。

面倒くさいバグに遭遇した・・・

テーブルおよびクエリをつかった時にある一定条件でデータを抽出した場合において、Excel2013、Excel2016などで「セルに文字を入力時に、1文字目が勝手に確定される」という現象が発生しています。この問題は過去にもExcelにあったDropboxが原因で起きていたものや、詳細なテキストサービスが原因で起きるもの、IMEの予測変換が原因で起きるものではなく、このテーブル（厳密にはPowerQueryで外部ソースから抽出した時のテーブル）が原因で起きる致命的なバグのようです。macOS版は同じファイルでも問題が起きていません。

具体的な現象は以下の通り

1. 外部のMDBなどからデータをPowerQueryで抽出
2. データの更新に関するオプションにて、「バックグラウンドで更新する」にチェックが入っていると起きます。

この厄介な問題を回避する方法は以下の2パターンです。

1. 対象のテーブルを右クリック⇒テーブル⇒データソースとのリンクを解除で回避（ただし、リンクが切断されることになる）
2. データタブの既存の接続⇒対象右クリックで接続プロパティの編集⇒バックグラウンドで更新するのチェックを外す（親データが更新されても自動更新されない）。

といったちょっと致命的なバグです。「あ」と入力して何もせずに勝手に確定してしまうのがこのバグの症状です。実際に1.の方法で切断を行うと、この症状は起きません。特定のブックでだけ、なんか日本語入力おかしいなという時は、まずテーブルの自動更新オプションを疑ってみてください。

図：厄介で非常にストレスフルなバグ

テーブル

今回のエントリーの中で一番の目玉がテーブル機能です。歴史は意外と古くExcel 2000(コメントより修正しました）より登場したものです。テーブルという名前からわかるように、Accessのようなデータベースのテーブル形式にしてExcelのシートを使う機能であるため、使いこなせれば非常に楽に作業を進めることができます。しかし、Excelをただの画面レイアウトの方眼紙程度にしか捉えてない人だと、その仕組に挫折することがあるようです（Accessで挫折する人が出るのも同じ理由だったりします）。

テーブル化の方法

テーブル化するのは驚くほど簡単です。以下の手順で行います。セルの結合などは使わないように。また、空行などは入れないように注意が必要です。

1. きちんち整形されているデータの範囲を指定する
2. 挿入タブにある「テーブル」をクリック
3. 先頭行を見出しにするにチェックが入っていれば自動でフィルタが付きます
4. これでOKをクリックすればテーブル化完了
5. デザインタブを開き、「テーブル名」にて、テーブルの名前を変更できます。わかりやすい名前をつけておきましょう。
6. 幅も自動修正されるようになります。
7. テーブル解除する場合は、全範囲指定して、右クリック⇒テーブル⇒範囲に変換すれば元に戻ります。

図：テーブルの名前は数式を作る時に利用します

テーブル化で関数の自動補完

テーブル化するメリットの一つがこの「関数の自動補完」。Accessを使ってる人はわかると思いますが、テーブルではセル単位ではなく列単位で関数を管理することになります。よって、１行目の特定のセルに数式を入れると、その列すべてに数式が自動で補完されます。また、補完された数式は基本すべて同じ数式になります（通常の範囲の場合には、セル番地がそれぞれ異なる）。これを構造化参照と呼びます。

そのため、１行目の数式を変更すれば、すべての行の結果が変更されます。また、vlookup関数のような数式の場合、参照先のデータもテーブル化しておけば、「範囲を固定化していなかった為、値がズレて大変なことに」といった事が防げます。なぜならば、テーブルで参照させているからです。

レコードを追加しても自動で数式が補完され拡張されるので、メンテナンスが不要になります。

図：数式の管理がとても楽になる

数式がわかりやすくなる

関数補完でもあるように、数式ではセル番地によるものではなくテーブル名によるものになるため、数式の見通しが非常によくなります。とくに、VLOOKUP関数のような複雑な関数の場合、それが顕著です。今回は、index・matchで縦横クロス検索を例に見てみます。

セル番地ではないので、何をしているのかが非常にわかりやすくなり、苦手なindex・matchの組み合わせもこれならば、堂々と使えるようになるでしょう。

図：テーブルだと参照範囲の見通しが非常によくなる

図：数式入力中はフィールド名が出てくる

ドロップダウンリストの自動修正

Excelでもよく利用するテクニックにドロップダウンリストがあります。項目を手入力するのではなく選択するだけで良いので、入力ミスを減らす手段として常套手段ですね。しかし、このドロップダウンリストはそのリストが増減した場合、手動で手直しをしなければなりません。これがとっても鬱陶しい。

しかし、これもテーブルと「名前付き範囲」の合わせ技を使うと、メンテナンスフリーになります。以下の手順で設定します。

1. データをテーブル化しておく
2. リスト化する範囲を選択し、数式タブ⇒名前の管理を開く
3. 新規作成ボタンをクリック
4. 名前はわかりやすいものをつけておく。そして、OKを押す。今回は車名という範囲を付けました。
5. リスト設置するセルを選択し、データタブ⇒データツール⇒データの入力規則をクリックします。
6. 入力値の種類を「リスト」にして、元の値ではF3キーを押すと、先程つけた名前付範囲が出てくるので選択。
7. OKを押せばドロップダウンリストにテーブル化した名前付き範囲が適用される。
8. 1.のテーブルの最下行に１行データを追加してみる
9. 7.のドロップダウンリストが自動で拡張されてリストが増えてるよ

これは、テーブルの機能の１つで、自動でレコードを追加するとテーブル範囲が自動で拡張する機能を利用したものです。そこに対して名前付き範囲を設定しているので、名前付き範囲も自動で拡張するのです。それをドロップダウンリストのリストに使っているので、データが追加されればされるほど、リストは自動で増えていくのです（もちろん、削除すれば消えてゆきます）。

図：テーブル化と名前付き範囲の合わせ技

図：リストの範囲はセル範囲ではなく名前付き範囲を使用

リレーションシップ

Accessではおなじみのリレーションシップ機能。実はテーブル化するとExcelでも使えるようになります。これを使うことによって、vlookup関数などを使わずとも、２つのテーブル間をひとつなぎにすることが可能になります。クエリを作るのと全く同じ感覚です。但し、選択クエリのようなものではなく、ピボットを作るときに外部テーブルを使う手段になるので、注意。

これはピボットテーブルとして作ることになるので、以下の手順で作成します。

1. データタブ⇒データツール⇒リレーションシップをクリック
2. マスタとなるテーブルを選び、その主たるキーになるフィールドを選びます。
3. 関連テーブルには参照先のテーブルとマスタと同じ値を格納してるフィールドを選びます。
4. OKを押せばリレーションシップが貼られる
5. 挿入タブ⇒ピボットテーブルで新規作成する
6. 複数のテーブルを分析するかどうかを選択のチェックを入れておく
7. ピボットからまず、メインテーブルの値を引っ張ってくる
8. 行、列、値それぞれを設定し、リレーション先の項目も追加しておく
9. すると、２つのテーブルを元にリンクした内容で、ピボットテーブルが作られる。

図：Accessではおなじみですね。

図：2つのテーブルを元に合体したピボットを作れる

Power Queryを活用する

Power Queryとは昔からあるMSQUERYの強化版のようなもので、Excelのテーブルや外部ソースを元にクエリを作って抽出する為のツールで、Excel2016からは標準で搭載されているツールです。2013ではPowerQueryというアドインで実現されていました。この機能を使うと、標準のExcelだけで運用するよりも遥かに楽にデータの抽出を実現できます。

データのフィルタをいくつも用意できる

大本のデータベースシートを用意し、それを元にいくつものフィルタルールを作っておき、抽出が可能です。もちろん外部ソースとしてAccessやMySQLなどから引っ張ってくるのも良いでしょう。クエリはよく使う抽出モデルを用意しておくことで、いわゆるオートフィルタとは違い、テーブルらしく大本のデータが変更されると、影響を受けて抽出内容も自動で反映されます。

1. テーブルの範囲を指定する
2. データタブの「テーブルまたは範囲から」を選ぶとPowerQueryが立ち上がります。
3. プロパティの名前を変更しておく。このプロパティはいくつも持っておくことが可能。
4. フィルタしたり、別のテーブルと連結したりも可能になる（Accessの選択クエリのようなもの）
5. 保存して読み込むボタンの閉じて次に読み込むにて、反映先を指定できる
6. もちろん、クエリではよく使う「グループ化」もすることが可能です。
7. クエリと言ってもSQL文ではないので、詳細エディタを開いてもSQLのように書かれているわけではありません。
8. 作っておいたクエリは、クエリと接続をクリックすればいつでも呼び出せ、編集が可能です。

図：毎回フィルタする必要はない。クエリとして作り込んでおけば良いだけ。

マトリックス変換

データで頂戴！！って言ったら、出されたエクセルデータがいわゆるリスト表ではなく集計された「マトリックス表」だったことが何度もあります。人間にとっては集計されて縦横のクロス集計は見やすいのかもしれませんが、プログラムで扱うデータとしては非常によろしくありません。しかもその表は元データがあって作られたピボットテーブルではなく、sumifsなどで加工されたものだったりするわけです。

これをプログラムで使いやすいリスト表に変換する必要がありますがそのためにVBAを書くのも面倒。ということでPower Queryには「列のピボット解除」という素晴らしい機能があります。

1. マトリックスなテーブルの範囲をPower Queryで開く
2. 列項目の部分だけを選択する
3. 変換⇒列のピボット解除をクリックする
4. ファイル⇒閉じて次に読み込むをクリックする
5. 貼り付け先を指定するとリスト表に変換されて貼り付けられる

図：縦横変換とは違いますよ

Microsoft Queryを活用する

すごーく昔からあるツールがこのMicrosoft Query。Power Queryと違って、その感覚はAccessの選択クエリに非常に近いもので、リレーションシップやら複数のテーブルを組み合わせて、データをテーブルで返してくれる機能です。ただUIがWindows95時代のような古臭さがあって、ちょっとアレですが、柔軟なツールですよ。

こちらのツールは、SQL文なので得意な人は直接修正とかもできるので、Power Queryよりも使いやすいかも。ただ、注意点もあります。

使い方は以下の手順（2016の場合）

1. リボンのメニューより、[データ]⇒「その他のソース」⇒「Microsoft Query」を選びます。
2. ODBC接続設定画面のようなものが出ます。ここにExcel Filesというものがあるのですが、これは旧形式のxls形式しか使えないので、ここでは新規データソースを選び、OKを押します。
3. データソースの名前は適当に。対応するドライバーの選択では、Microsoft Excel Driverでxlsxやxlsmを使えるものを選択します。
4. 接続ボタンを押します
5. 次のダイアログでは、ブックの選択ボタンをクリックします。ExcelのVersionは今回は12.0を選んでいます。
6. ファイルを選択します。この時自分自身のファイルを選ぶ事も可能。
7. OKボタンを押します。
8. データソース標準のテーブル（シート名）を選び、OKを押します。
9. 最初の画面に戻ってくるので、OKを押します。
10. 次に8.で選んだテーブルのうち、使用するフィールド（列名）を選びます。
11. 次へをクリックします。
12. あとはソートの指定や、抽出条件を入れられます。次へをクリック。
13. 最後にそのままExcelにデータを返せますし、設定を拡張子qryとして保存も可能です。ただ、ここでは続けてMicrosoft Queryで編集したいのでMicrosoft Queryでデータの表示またはクエリの編集を行うをクリックし、完了ボタンを押す。
14. するとクエリ編集画面が出てくる。別のシートを足してリレーションシップを組んだり、新たに列を追加したりかなりいろいろなことが可能。
15. データを返すボタンを押すと、範囲指定になりテーブルとしてデータがExcel側へと返ってきます。
16. 再度編集したい場合には、テーブルを選び、[データ]⇒[接続]をクリック
17. プロパティをクリックする
18. 次のダイアログの定義というタブをクリックする。
19. 接続文字列がODBC接続文字列で、コマンド文字列がSQL文です。クエリの編集ボタンをクリックすると、10.の項目が出てくるので、また同じ手順をたどってMicrosoft Queryで編集が可能です。

図：データソース設定。次回以降は選ぶだけで使える

図：クエリ編集画面。Accessのそれに似ている

図：再編集時のプロパティの画面

関連リンク

• ピボットテーブルで行項目をすべての行に表示する
• 実務で使えるExcel入門セミナー 「テーブル」の便利さを知って、どんどん実務を効率化しよう
• 列のピボット解除 (Power Query)