Chiquilin Site■16.05.02_Excel：条件を指定して抽出する３（パラメータクエリ編）
ChiquilinSite
今回はパラメータクエリを使った抽出方法を紹介します。クエリでやるなら Power Queryのほうが設定自体は簡単です。ただ「セル編集と同時に実行」を実現しようと思ったら VBAで Changeイベントを仕込む必要があります。 Microsoft Query(以降 MsQuery）は既に時代遅れになりつつありますが使用感は数式とほぼ変わりません。セル編集と同時に実行できます。
尚 どうしても数式でやりたい人は「
抽出データを詰めて表示する」を参考にして下さい。
今回は 社員リストから部署名を検索キーにして部署別社員リストを抽出します。
保存場所：C:\sumple\入社リスト.xlsx
A列：入社日
B列：社員番号
C列：部署
D列：課
E列：氏名
テーブル名：T_社員リスト
★その１：外部データ接続の仕方
セルに入力した条件を使って自動でデータを抽出したい場合 接続の仕方はいくつかあります。ODBC接続を作成するか MsQueryを使って接続するか Power Queryを使うかです。今回は 「セル入力と同時に」が目的なのと Excelのブックを外部接続するつもりなので MsQuery経由で抽出してしまいます。セルの値と連動させず単に抽出するだけなら [既存の接続]→[参照]から Excelのブックを指定してもいいです。
[データ]→[その他のデータ ソース]→[Microsoft Query]
→[Excel Files*]を選択して[OK]
指定の Excelファイル（「C:\sumple」の「Book1.xlsx」）を選択して[OK]
指定のシート（「社員リスト」）を選択し「>」をクリック※
※ここで抽出項目を細かく指定する必要はありません。
※もし何も抽出項目が表示されない時は 慌てず[オプション]
をクリックし [システム テーブル]のチェックを外して[OK]
→チェックを入れて[OK] を繰り返してみてください。
クエリの列に項目が追加されたら
[次へ]→[次へ]→[次へ]→[完了]
データを返す先を指定して[OK]※
※今回はひとまず「抽出」シートの A1を指定
ここから[プロパティ]をクリックして更に編集しても問題ありませんが 接続に失敗した時は最初からやり直しになるので 慣れない内はここで一度データを返しておくことをお勧めします。
最後に接続名と抽出されたテーブルを分かりやすい名前に変更しましょう。
◆接続名の変更
[デザイン]→[外部のテーブル データ]の[更新]→[プロパティ]
もしくは
[データ]→[すべて更新]の[接続のプロパティ]
接続名を好きな名前に変更して[OK]
今回は「部署別リスト」に変更。
◆テーブル名の変更
[数式]→[名前の管理]
作成されたテーブルに自動で振られた「テーブル_????」のような名前になっているものを選択し[編集]から好きな名前に変更して[OK]
今回は「T_部署別リスト」に変更。
おまけ。この状態のままだと抽出を更新する度に列の幅に自動調整が入ってしまいます。それで問題ないというなら結構ですが 列幅を固定したい場合は下記の設定が必要です。
[デザイン]→[外部のテーブル データ]の[プロパティ]
もしくは
[データ]→[接続]の[プロパティ]
→[列の幅を調整する]のチェックを外して[OK]
これで勝手に列幅が変わるのを避けられます。
★その２：SQLで抽出コードを記入する
ここからは実際にコードを触っていきます。
[デザイン]→[外部のテーブル データ]の[更新]の[接続のプロパティ]
もしくは
[データ]→[すべての更新]の[接続のプロパティ]
「定義」タブを選択
ここで「コマンド文字列」を見ると 既に SQLのコマンドが入っているのが確認できるはずです。実は MsQueryで接続を作成すると ここに勝手に SQLのコマンド文字列が作成されています。これを書き換えるだけで抽出条件を変更することができます。
SELECT `社員リスト$`.入社日, `社員リスト$`.社員番号, `社員リスト$`.部署, `社員リスト$`.課, `社員リスト$`.氏名
FROM `C:\sumple\入社リスト.xlsx`.`社員リスト$` `社員リスト$`
最初はこのようになっているはずです。ただ少々見づらいので まずはこれを整理しましょう。「`」は「[」「]」に置き換えられますし 「`社員リスト$`.」のところは単純な抽出では必要ありませんから省くことができます。
SELECT 入社日, 社員番号, 部署, 課, 氏名
FROM [社員リスト$A:E]
これで随分すっきりしました。SQLの書き方は勉強して頂くしかありませんが 基本的な書き方は以下のようになります。
SELECT：どの項目名を表示させるかを指定します。
FROM：ブックのどの部分（シート）と接続させるかを指定します。
WHERE：条件を指定します
他にもありますが 基本はこの３つだけです。
抽出されたデータを見ると データがない行まで抽出されてしまっています。ついでなのでここで条件を指定して空白行を取り除いてしまいましょう。
SELECT 入社日, 社員番号, 部署, 課, 氏名
FROM [社員リスト$A:E]
WHERE 氏名 Is Not Null
これで氏名列に入力がない行は抽出されなくなります。「Is Not Null」は「空白（ナル）ではない」という意味です。
★その３：セルをパラメータに設定する
いよいよ肝心なパラメータの指定（検索条件指定）に入ります。先にも書きましたが SQLで条件を指定する時は「WHERE」を使います。この条件には「AND」「OR」を使って複数条件を指定することもできます。その条件に「?」を使うことで パラメータとしてセルの値を利用することができます。コマンド文字列に下記の一文を追加します。
SELECT 入社日, 社員番号, 部署, 課, 氏名
FROM [社員リスト$A:E]
WHERE 氏名 Is Not Null
AND Format(部署,'0') = Format(?, '0')
→[OK]
コマンド文字列に「?」が追加された状態で「OK」を押すと パラメータダイアログが開きます。今回は G2セルをパラメータとして利用することにします。
[次のセルから値を取得する]を選択
[パラメーター1]に「G2」を指定し
[この値/参照を今後の更新に使用する]にチェック
→[この値が変わるときに自動的に更新する]にチェック
→[OK]
これで抽出完了です。
「一致する時」を指定する場合 本来は「=」を使いますが 今回のようにセルをパラメータに指定する場合 「部署 = ?」としてしまうと そのセルが空白の時にNull値のエラーが出てしまいます。「Format」を挟むことで「Null」が文字列に変換されるので何もない時でもエラーが出なくなります。前方一致検索で問題ないなら「AND 部署 Like ? + '%'」としてもいいです。「%」は Excelの関数でいうところの「*」と同じ扱いのワイルドカードです。
ワイルドカードとは別に注意点があります。Excelの数式では文字列を「"」で囲みますが コマンド文字列では「'」を使います。
今回の抽出方法(パラメータクエリ）を使うと フィルタの詳細設定などの既存機能と違って セルの値を修正したと同時にデータを自動的に抽出し直すようになります。また「UNION ALL」や「INNER JOIN」「OUTER JOIN」を利用することで 複数の元データを結合して利用することもできます。つまりここだけでリレーションシップを実現できるようになります。
Power Queryならもっと簡単にパラメータクエリを作れるんですが「セルに入力すると同時に」をやろうと思ったら changeイベントと組み合わせる必要があります。「今更こんな方法 誰が使うか」といわれそうですが 何かと応用が利きますので覚えておいて損はないと思います。
