桂馬の学習ノード: 2020-08

2020-08-16

[IAmTimCorey]15 Visual Studio Editor Tips

這是一連串的文章，關於我所知道的C#推廣者 Tim Corey 頻道 IAmTimCorey 的內容。
這同時是我的學習筆記。以下的中文用詞，大部分是個人慣用語。一般來說會附上英文原詞，方便大家對照。

原始連結: https://www.youtube.com/watch?v=qv6ZflueASY

這次的題目，是有關那個號稱地表最強IDE的Visual Studio在設定上的一些小技巧與內容。

2020-08-05

Excelに外部データベースを自動取り入れする方法

ここで使用する技は、次の使用状況を前提に考えた：

- 参照するキーはExcel上もともとある資料

- 外部データベース（今回はSQL Serverを例にする）には、すでに必要な資料を整理してある（つまり、DB内のTableのJOIN等は、Viewなどの形で、すでに整理してある）

使用する技の中身を明かすと、次のように分解する：

1. 必要なパラメータをそれぞれTableにする（Queryを掛けるときには必要）

2. それぞれのパラメータをクエリに変換。（後ほど詳しく説明）

3. 本当にデータをクエリし、必要のパラメータフィルタはひとまず「定数」で設定。

4. 上記定数のパラメータをそれぞれクエリに入れ替え。

それでは、ステップ分けて説明する。

パラメータをテーブルに設定

例として、MicrosoftのNorthwindデータベースをSqlServerのLocalDBに構築した前提で進む。目標としては[Order Details Extended]というViewの中で、OrderIDが同一するものを抽出する。

では、まずは[OrderID]をTableに設定する。A1のセルにOrderIDと入力し、例えの内容として、"10254"をA2に入力する。

A1のセルをクリックして、[挿入] - [テーブル]をクリックして、該当範囲をテーブルに変換する。ショートカットは[Ctrl-T]を押しても同じようなものが出る。注意すべきのは、「先頭行をテーブルの見出しとして使用する」にチェックを入れること。Excelの設計上、必ず一番上のマスにタイトルを入れるので、自分でタイトルを入れないと、Excelが勝手にタイトルを付けておくようになってしまう。

以上で、パラメータをテーブルに変換する手順が終わる。

パラメータをクエリに変換

次に、それぞれのパラメータテーブルをクエリに変換していく。

テーブルの一部をクリックして、[データ] - (取得と変換) - [テーブルから]をクリックする。とすると、Power Queryエディターが出てくる。

先ずはタイトルの部分を注目。ここでは「数値」としてマスの内容を認識しているはず。どっちにしよう、SQLクエリを掛けるのに、「文字」タイプが一番ベスト。ゆえに、ここは一度文字へ変換しておく。また、テーブルが複数欄になってしまった場合、ここで一度整理整頓を行う。一つのパラメータは一つの欄だけにすることが大事。

そして、1欄目のところに右クリックして、「ドリルダウン」をクリックする。とすると、ちょっと変な形になるが、使用上支障はないので、無視する。右側にプロパティが出るので、ここで名前をQueryOrderIDとする。

そのままの形で、ホーム - 閉じて次に読み込む　(閉じて読み込むの裏にある)をクリックする。読み込み先が聞かれてくるが、「接続の作成のみ」として読み込み。

上記の繰り返して、すべてのパラメータをクエリへ変換しておく。

一度本クエリを行う

データ⇒新しいクエリ⇒データベースから⇒SqlServerデータベースからをクリック、接続のダイアログを出す。サーバーは　(localdb)\MSSQLLocalDB で、データベースは Northwind となる。

実際に接続するときには、毎回接続情報を聞いてくるが、そこは各自に解決してください…

今回接続したいテーブル（実はView）は前述の「Order Details Extended」、そして下の「編集」をクリックする。

それぞれパラメータを掛けたいところに、タイトルの右にあるドリルダウンボタンをクリックし、フィルタを掛ける。今回の案では、10254とフィルタする。

終了したときは、同じホーム ⇒ 閉じて次に読み込むとして、同じ表のA4セルに読み込む。

クエリのパラメータを入れ替える

鬼門が来た。前のステップで作成した本クエリのところをクリックして、編集を掛ける。編集を行いやすいように、表示 - レイアウト - 数式バー　にチェックを入れる。

とすると、数式バーに「[OrderID] = 10254」のような文字が書いてある。10254のところをOrderIDと書き換える。

同じように、掛けたいパラメータのところをそれぞれ対応するクエリ名に入れ替えて置く。

完成したら、閉じて読み込む。

以上で、完成。

おわりに

SQLクエリを変更可能なパラメータで行うには上記のやり方で可能だが、実際に操作してみてはやはり面倒くさいし、いろいろセキュリティ上の問題で、ネットでこれらの方法を展開していないのは理由が分かる気がする。自分でもやってみて「VBAにした方が楽だなぁ」と思ったりする。なにかいい方法があれば教えてください。

桂馬の学習ノード

SyntaxHighlighter