別ファイルのデータを読み込む
まずは簡単なことをやってみましょう。下図のようなCSVファイルを読み込んでみます。
Excelを起動したら、[データ]タブを開き[データの取得と変換]グループにある[テキストまたはCSVから]ボタンをクリックします。
同じボタンは、その左にある[データの取得]を開き、[ファイルから]をポイントすると表示されます。どちらでも同じです。
ちなみに、このへんで「あれ？私の画面とリボンが違う…」という方がいるはずです。詳しくは「Office 2016は3種類ある」をご覧ください。私の画面は、ProPlusの月次チャネルです。ボタンの表示や位置が違ったら、あれこれクリックして試してください。壊れませんから。
さて[テキストまたはCSVから]ボタンをクリックすると、[データの取り込み]ダイアログボックスが開きます。CSVを保存したフォルダを開きます。
開くファイル(ここではSamsple1.csv)を選択すると、右下の[開く]ボタンが[インポート]に変わります。
[インポート]ボタンをクリックすると、選択したファイルのプレビュー画面が表示されます。
プレビュー画面の右下には、[読み込み]が選択されているドロップダウンリストボックスと、[編集]ボタンが表示されます。この後[編集]ボタンをクリックするのですが、ちなみにドロップダウンリストボックスを開くと次のような項目が表示されます。
[読み込み先]をクリックすると、次のようなダイアログボックスが表示されます。このダイアログボックスに関しては、あとで解説します。
さて、プレビュー画面の[編集]ボタンをクリックすると、「取得と変換」機能のメイン画面である[Power Queryエディタ]が開きます。
この画面からも分かるように、Power QueryエディタはExcelとは別画面のウィンドウです。Power Queryエディタを開いている間はExcelを操作できません。
ここからは、Power Queryエディタの操作です。
読み込んだCSVを編集する
ここでは、次のように操作してみましょう。まず[日付]列のタイトルをクリックして[日付]列全体を選択します。その状態で[並べ替え]グループの[昇順で並べ替え]ボタンをクリックします。このへんはExcelと一緒ですね。
実行すると[日付]列が昇順で並べ替わります。
さらにもう一手間。[名前]列のタイトルを右方向にドラッグして、[記号]と[数値]の間にドロップします。
今日はこのへんでかんべんしてやりましょう。このデータをExcelのワークシートに読み込みます。リボンの[閉じて読み込む]ボタン下部をクリックし、表示されるメニューから[閉じて次に読み込む]をクリックします。
実行すると、さきほどご紹介した[データのインポート]ダイアログボックスが表示されますので、[テーブル]オプションボタンがオンになっているのを確認して、[既存のワークシート]を選択します。[OK]ボタンをクリックすると、先のデータがアクティブシートのセルA1に読み込まれます。
読み込まれたデータはテーブル形式になります。余談ですが、だからこれからのExcelユーザーは、テーブルに慣れていないと困るわけです。ちなみに、VBAでテーブルを操作する方法は「VBAでテーブルの操作」をご覧ください。かなり詳しく解説しています。
さて、ワークシートに読み込まれたデータですが、[日付]列が昇順に並んでいるのが分かります。さらに、B列に[記号]が配置されC列に[名前]がレイアウトされています。元のCSVはこうでした。
ここで、元データであるCSVファイルをテキストエディタで直接編集してみます。現在ワークシート上の2行目に表示されている桜井の数値を変更します。
Excelの画面に戻り、リボンの[更新]ボタンをクリックするか、割り当てられているショートカットキーのAlt + [F5]を押します。
実行すると、ワークシート上のデータも変更されます。
元のCSVファイルに新しいデータを追加してみます。最後の行に「2018/1/2,たなか,12345」と入力してCSVファイルを上書き保存します。
Excel画面に戻り[更新]ボタンをクリックすると、[日付]の"昇順で並べ替え"が反映された位置に、新しいデータが代入されます。
「取得と変換」のイメージとメリット
今回はシンプルなケースでお見せしましたが、元データはCSVでなくてもかまいません。Excelのブック(xlsx)でもAccessのデータでも基幹システム(サーバー)のデータでも共有フォルダにあるデータでもWeb上にあるデータでも、もう何でも取得できます。
そして、元データが変更されたら、新しいデータをExcelが処理しやすい形式に変換した上で提供してくれます。
もちろん、元データは複数のファイルを扱えます。基幹システムやサーバーからデータを取得して、それをExcelに提供することも可能です。
今まではどうでしょう。たとえば基幹システムのデータを扱う場合。まず基幹システムからCSVファイルを出力します。これは手動です。ここをVBAでやりたいというリクエストをよく聞きますが、VBAは何でもできる魔法の道具じゃありません。表計算ソフト「Excel」のマクロ言語です。そんなの無理に決まってます。ちなみに昨今は、この部分をRPAでやる考え方が普及しています。喜ばしいことです。何でもかんでもExcelだけでやろうとするから苦労するんです。さて、出力されたCSVファイルをExcelに取り込みます。これはVBAで可能です。しかし読み込んだCSVファイルには膨大なデータが含まれています。私が毎月コンサルタントでお邪魔している大手企業では、読み込んだCSVデータが100列を超えるそうです。Excelで開くだけでも数分かかるとか。その100列の中で実際に使うのは数列だけです。それら不要なデータを削除したり、決められたフォーマットに加工しなければなりません。VBAでやるか、あるいは高度な数式を作ります。当然自分では作れません。そこでネットを検索します。「これでできるよ」という、自分では意味も分からないコードや数式を、意味も分からないままコピペします。何とか動いているようだから「ま、いっか」と。こうして、誰にも引き継げないマクロやブックが完成します。
「取得と変換」を活用すると、Excelだけでやろうと無理をしていた苦労から解放されます。Excelの、本来の仕事である計算に専念できます。