Office TANAKA - 取得と変換[「テーブルまたは範囲から」の挙動]

「取得と変換」の中でも、ひんぱんに使うであろう「テーブルまたは範囲から」は、シート上のテーブルまたはセル範囲を、Power Queryエディタに読み込むコマンドです。

しかし、この「テーブルまたは範囲から」には、ちょっと変な挙動がありますので、ご注意を。以下、解説します。

2020年5月追記
下記で解説している「一度でもオートフィルタを設定した範囲は、Power Queryエディタに読み込めない」という挙動は、どうやら解消されたようです。どのタイミングで修正されたのかは不明ですが、Office Insider Version 2006(ビルド12920.20000)では直っていました。なお、せっかく書いたので(笑)、下記コンテンツはこのまま残しておきます。

元データがテーブル形式だったら

Power Queryエディタに読み込む元データが、すでにテーブル形式になっているのなら簡単です。特に問題なく一発で読み込まれます。

このとき、クエリの名前として登録される"テーブル1"は、このテーブルに設定されている名前です。テーブルの名前は、一般的に[テーブルデザイン]タブ左端にある[テーブル名]ボックスで指定します。

でも本当は、すべてのテーブルに、Excelの"名前機能"によって重複しない名前が設定されています。その名前は[名前の管理]ダイアログボックスで確認できます。

元データが通常のセル範囲だったら

問題はこっちです。

まずは、普通にやってみます。表の中にアクティブセルを置いて[テーブルまたは範囲から]をクリックします。

実行すると、確認の[テーブルの作成]ダイアログボックスが表示されて、アクティブセルがある表をテーブル形式に変換します。

もちろん、ここで[OK]ボタンをクリックすれば、表がテーブル形式に変換され、そのテーブルがPower Queryエディタに読み込まれます。

では、変な挙動をお見せします。まず、表の中にアクティブセルを置いて、[データ]タブの[フィルター]ボタンをクリックします。要するにオートフィルタを設定するわけです。もちろん、Ctrl + Shift + Lのショートカットキーを押しても同じです。

オートフィルタ矢印ボタン(▼ボタン)が表示されました。この状態で、先と同じように、アクティブセルを表の中に置いて[テーブルまたは範囲から]をクリックしてみます。

実行すると、今度はテーブルに変換するための確認ダイアログボックスが表示されず、いきなりPower Queryエディタが起動します。しかも、エラーです。

こうなると、再び[フィルター]ボタンをクリックするなどして、▼ボタンを消してもダメです。ずっとエラーのままです。対応策として、自分で表をテーブル形式に変換してから[テーブルまたは範囲から]を実行すればいいのですけど、そもそもなぜエラーになるのでしょう。

エラー画面に表示されている「FilterDatabase」がヒントです。実はオートフィルタを設定すると、オートフィルタによって絞り込まれる表全体に、Excelは「シート名!_FilterDatabase」という名前を設定します。オートフィルタは高機能ですから、Excelの内部的には、そうした仕組みが必要なのでしょう。ただし「シート名!_FilterDatabase」は非表示の名前として設定されるので[名前の管理]ダイアログボックスにはリストアップされません。

非表示の名前は、VBAを使うと確認できます。

Sub Macro()
    With ThisWorkbook.Names(1)
        MsgBox .Name & vbCrLf & .RefersTo
    End With
End Sub

つまり、次のような流れです。

[テーブルまたは範囲から]が実行されると、Excelはまず、アクティブセルがある表全体に名前が設定されているかどうかを確認します。もし設定されていなかったら、表全体をテーブル形式にしてもらいます。テーブルには必ず名前が設定されますので、その名前を使ってPower Queryエディタに読み込みます。もし表全体に名前が設定されていたら、その名前を使ってPower Queryエディタに読み込みます。今回は「Sheet1!_FilterDatabase」という名前が設定されていたので、これを使って読み込もうとしたのですが、この名前は非表示だったために読み込めなかったと。だからエラーです。

このように、Power Queryエディタがブック内のデータを読み込むときは、セル範囲に設定されている"名前"を使います。テーブルには必ず"重複しない名前"が設定されているはずですから都合がいいです。でも、それを利用することで、テーブルではない通常のセル範囲だって、われわれが明示的に名前を設定しておけば、テーブル形式に変換せず、そのまま読み込めるということです。やってみましょう。

ここでは「空欄セルを同じ値で埋める」でご紹介した変な表でやってみます。通常の手順では、この変な表を一度テーブル形式に変換してからPower Queryエディタに読み込みます。でも、そうすると、この変な表のレイアウトが崩れます。もし、この変な表をそのまま残しておきたいのでしたら、この変な表に自分で名前を設定します。

変な表を崩さずに読み込めました。名前は表全体に設定してください。表内の一部分だけに名前を設定すると、そこしか読み込まれません。

さて、今回の「一度オートフィルタを設定するとPower Queryエディタで読み込めなくなる」問題ですが、簡単な解決策は見当たりません。もちろん、その表をテーブル形式に変換できるのなら、それがベストです。テーブルにしてください。そうすれば、何も問題なくPower Queryエディタを使えます。でも、実務では、いろいろな事情から、テーブルにはできないケースもあります。まあ、たいていはExcelの正しい使い方をしていないのが原因なのですが。でも、そんなこと言ってもしかたないです。その表をテーブルにできないのでしたら、たとえば次のようにする手もあります。

オートフィルタを設定すると、その表全体に名前が設定されます。

たとえ、オートフィルタを解除して、▼ボタンを消しても、名前定義は残ったままです。

1つのワークシートに、オートフィルタは1つしか設定できません。これは、この名前定義による仕様です。でも、1つしか設定できないのですから、同じシート内で、どこか別のところにオートフィルタを設定してやれば、今度はそちらに名前が設定されます。すると、今まで設定されていた範囲からは、名前定義がクリアされます。

名前定義がクリアされちゃえば、Power Queryエディタで読み込めます。エラーにはなりません。しかし、この方法だと、いったん元データからオートフィルタを解除しなければなりません。もし、それが可能でしたら、そうしてください。困るのは、オートフィルタを設定したままで、Power Queryエディタに読み込む方法です。これが思いつきません。オートフィルタを設定している状態では、必ず非表示の名前が設定されています。これは、どうしようもありません。そして、非表示の名前が設定されていると、Power Queryエディタでは読み込めません。ちなみに、この表に新しく、自分で別の名前を定義してもダメです。やってみましたが、Power Queryエディタは非表示の名前を使おうとしてエラーになります。もちろん、VBAを使えば何とかなりますが、にしてもけっこう難しいですし、自分のブックを別ブックとして読み込むという荒技もありますけど、現実的ではありませんね。なので、上で書いたように、簡単な解決策は見当たらないです。

本稿を書いていて感じましたが、「取得と変換」という機能は、原則として"外部"のデータを扱う仕組みなのではないかと。基幹システムとか、サーバーとか、Webとか、別ファイルとか。それを前提として設計されているのではないかなと。でも、ブック内のでデータも扱いたい。一見すると簡単そうに思えますが、Excelには長年の歴史によって蓄積されてきた"さまざまな事情"があります。今回の「非表示の名前」も、そのひとつです。そうした事情が、新機能にとっては逆に足かせとなるのではないかと。そんな風に感じました。最後に一言だけアドバイスすれば、だからみんな、これからはテーブルを使いましょう！ってことですね。