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

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

今回使用するExcelシート

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

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

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

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

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

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

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

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

テーブル

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

テーブル化の方法

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

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

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

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

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

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

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

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

数式がわかりやすくなる

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

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

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

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

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

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

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

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

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

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

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

リレーションシップ

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

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

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

図: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. 貼り付け先を指定するとリスト表に変換されて貼り付けられる

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

関連リンク

共有してみる: