ブック内の複数テーブルを操作する
取得と変換には「テーブルまたは範囲から」というコマンドがあります。
実行すると、シート上にあるテーブルまたは、テーブルになっていないセル範囲を、Power Queryエディタに読み込むことができます。ちなみに、この「テーブルまたは範囲から」には少しクセというか、特徴というか、変な挙動があります。詳しくは「「テーブルまたは範囲から」の挙動」をご覧ください。さて、このコマンドでテーブルをPower Queryエディタに読み込めるのですけど、じゃ、ブック内に存在する複数のテーブルを読み込むには、どうしたらいいでしょう。これには、少しコツが必要ですので解説します。
複数テーブルの結合
まずは、次のようなケース。
左のテーブルが「テーブル1」で右のテーブルが「テーブル2」です。実務の場合、一般的にはこういうとき、それぞれのテーブルを別々のシートに作ることが多いですが、ここでは解説のために同じシートでやります。さて、この2つのテーブルを結合して、次のようにしてみます。
もちろん、元データが編集されたり増減したら対応します。こういうのも、普通は別シートにやりますが、ここは結果が分かりやすいように同じシートに作ります。まずは、1つめのテーブル内にアクティブセルを移動して[テーブルまたは範囲から]を実行します。
実行すると、テーブル1がPower Queryエディタに読み込まれます。
次の手順は、いろいろな方法があります。ここでは、その中のひとつをご紹介します。これがベストということではありません。このように、テーブル1だけを読み込んだあと、何なら必要な編集や加工をして(今回は何もしませんが)[閉じて次に読み込む]を実行します。
[データのインポート]ダイアログボックスが表示されますので、表示したいセルを選択します。
[OK]ボタンをクリックすると、指定したセルにテーブル1が表示されます。
さあ、2つのめのテーブルを読み込みましょう。同じように、テーブル2の中にアクティブセルを移動して[テーブルまたは範囲から]を実行します。
実行するとテーブル2のクエリが作成されます。もし、もっと多くのテーブルを扱いたいのなら、この操作を繰り返します。ここが面倒くさいところですね。ブック内に存在するすべてのテーブルをリストアップして「これと、これと、これを読み込んで」みたいにできたら簡単なんですけど。
先に読み込んだテーブル1を選択します。
[クエリの追加]をクリックします。
表示される[追加]画面の[追加するテーブル]リストを開き「テーブル2」を選択します。
[OK]ボタンをクリックすると、先のテーブル1の下にテーブル2が追加されます。
テーブル2のクエリは、まだ保存というか、どこにどう読み込むかを指定していませんから、[閉じて次に読み込む]を実行します。
表示される[データのインポート]ダイアログボックスで「接続の作成のみ」を選択します。ここで指定したのは、テーブル2のクエリです。
[OK]ボタンをクリックすると、2つのテーブルが結合されます。元のテーブル1やテーブル2に編集や追加などを行うと、結合したテーブルも変化します。
テーブル2のクエリは接続のみを作成し、シート上には読み込んでいません。
すべてのテーブルに関して接続のみを作成し、それらをまとめるクエリを別に作るという手もありますが、テーブルの数が少ないのでしたら、この手順が簡単かと。
ブック内の複数テーブルでVLOOKUP関数的なことをする
「VLOOKUP関数と同じことをする」で解説したように、[クエリのマージ]を使うと、VLOOKUP関数的なことができます。今回は、ブック内に存在する複数のテーブルでやってみます。
左が「テーブル1」で右が「テーブル2」です。左のテーブル1がいわゆる"データ"です。そして、右のテーブル2が"商品マスタ"みたいなイメージです。上記で解説した手順で、テーブル1をPower Queryエディタに読み込みます。
先のように、またこのテーブル1だけをシート上に読み込んでおいてもいいのですけど、今回は違う手順でやってみましょうか。どちらがいいではなく、いろいろな手順をご紹介したいからです。[閉じて次に読み込む]を実行します。
表示される[データのインポート]ダイアログボックスで「接続の作成のみ」を選択します。
テーブル1のクエリは、接続のみが作成されシート上には表示されていません。
上記で解説した手順で、テーブル2もPower Queryエディタに読み込みます。
テーブル1のクエリを選択します。
ここからは「VLOOKUP関数と同じことをする」で解説した[クエリのマージ]をやります。詳しくは、そちらのページをご覧ください。なお今回は、列の削除や移動もしています。一応、流れを画像でご紹介します。
完了です。[閉じて次に読み込む]を実行します。
表示される[データのインポート]ダイアログボックスで指定するのは、まだ読み込んでいないテーブル2ですから、ここも[接続の作成のみ]を選択します。
どちらも、接続のみが作成されて、シート上には表示されていません。
テーブル1を右クリックして[読み込み先]を実行します。
[テーブル]を選択して、読み込むセルを指定します。
[OK]ボタンをクリックすると、指定したセルに読み込まれます。
セルH1に読み込まれた結果を見て分かるように、元のテーブル1とは並び順が異なっています。最初、こうした結果にはすごく違和感がありました。これ、不具合じゃねーの？って。でも、データベース系の専門家に話を聞くと、そもそもデータベースの世界では、保管されているデータの"並び順"は重要ではないと。必要なデータが存在していることが大事であって、それをどう見せるか、どう表現するかは、また別の話だと。なるほど、言われてみれば納得です。私は表計算派の人間ですから、どうしてもデータの"見た目"が気になります。だから並び順が変わったことに対して"何で？"という感覚を持ったのですが、でも、それは単なる"何で？"であって、もし、元のテーブル1と同じ並び順で表示したいのなら、インデックスを振って並べ替えれば済む話です。私の感じた"何で？"は、"(今までのExcelでは変わらなかったのに)何で？"であり、"(同じ並び順になるはず、ならなければおかしいのに)何で？"ではないです。「取得と変換」は、データベースの機能です。であるのなら、今まで感じていた"Excelだったらこうなるはず"という考えに固執するのは、よくないでしょうね。それでは、新しい技術や知識を活用することができないです。すみません、ちょっと話が横道にズレてしまいました。でも、クエリの結果が、必ずしも元のデータと同じ並び順にはならないということは、覚えておいた方がいいですし、そんなこと大騒ぎするようなことではないです。
さて、せっかく「数量」と「単価」があるのですから、ここは一般的に考えて「数量×単価」の計算式を追加しましょう。それには、2つの考え方があります。
Power Queryエディタで式を追加する
Power Queryエディタ内で、計算式の列を追加することもできます。まずは、[クエリと接続]ペインの「テーブル1」をダブルクリックするか、「テーブル1」を右クリックして[編集]を実行します。
Power Queryエディタが起動したら、[列の追加]タブの[カスタム列]をクリックします。この操作で追加される列は、常に右端ですから、[カスタム列]をクリックするときに、どの列が選択されていてもかまいません。
実行すると[カスタム列]画面が開きます。これは、後でやってもいいですが、まず新しく追加する列の名称を入力します。ここでは「売上」としました(①)。次に、[使用できる列]リストで「数量」をクリックして選択します(②)。下にある[挿入]ボタンをクリックすると(③)、[カスタム列の式]ボックスに「=[数量]」が挿入されます(④)。
あるいは[カスタム列の式]ボックスに直接記述することも可能です。現在「=[数量]」と入力されていますので、その後ろに乗算の「*」を入力し、続けて列名を囲む左角括弧「[」を入力します。実行すると、ここで使用できる列名がリストアップされます。
上下の矢印キーで[単価]を選択し、EnterキーまたはTabキーを押すと、カーソル位置に列名が挿入されます。
[カスタム列]画面の[OK]ボタンをクリックすると、新しく[売上]列が追加されます。
これで完成です。計算式の[売上]列が追加されました。でも、ここまでくると、Excelユーザーなら当然「3桁ごとの桁区切り書式」を設定したくなります。ところが、Power Queryエディタには、そうした書式を設定するボタンがありません。いや、もしかするとどこかにあるかもしれませんし、Power QueryやPower BI系の偉い人なら「それは、こうすればできるよ」と言うかもしれません。でも、少なくとも、パッと見た範囲にはありません。こういうところなんですよね～Excelユーザーがイラッとするのは。もういいです。ないんでしょ？期待してません。なので、このまま[閉じて読み込む]をクリックします。
書式は、Excel側で設定します。
いや、こういうところだと思いますよ、「取得と変換」を使いこなすポイントは。何でもかんでも、全部Power Queryエディタだけでやろうとしない。Excel側で簡単にできることは、Excelでやる。そうしたバランス感覚が重要なのだと思います。われわれは、Power Queryのスペシャリストになりたいのではありません。業務を遂行したいだけです。確かに、Power Queryはすごい機能を持ってると思いますよ。でも、われわれはPower Queryだけを使っているのではなく、Excelを使っているんです。Excelには便利な機能がたくさんあります。そして、それらの機能をわれわれはよく知っています。Excelではできないこと。Excelだけでは大変なことはPower Queryに任せます。でも逆に、Excelで簡単に実現できることだったら、Excel側でやります。その発想でいくと、今回の「数量×単価」の計算式だって、何も苦労して、よく分からないPower Queryエディタでやらなくてもいいです。他にも方法はあります。
Excel側で式を追加する
こっちの方が、何百倍も簡単です。計算式の列が作られていない状態のテーブルに、計算式を入力します。
書式を設定してタイトルを入力します。
ね、こっちの方が何千倍も簡単でしょ。もちろん、クエリを更新しても追加した計算式の列は削除されません。ちなみに、計算式が削除されない件に関しては「テーブル状態の保持」をご覧ください。