《練習データ付き》テキストファイルのインポート☆
MOSExcel一般の出題範囲で、一番わけがわからなくて、どう操作したらいいかわかんなくなるのが「テキストファイルからデータをインポートする」っていう操作です。
いったい何のために操作するのか、世の中でどう役立ってるのかもよくわからない機能なんですが、実際の仕事ではかなり大活躍してます！！
実際に仕事で使っているのははこの機能直接じゃなくて、テキストファイルを開くという操作なんですけど、でもこの「テキストファイルからデータをインポートする」操作は、「テキストファイルを開く」操作よりもちょっと優れてるような気がします。
練習データは下からダウンロードし、圧縮されているので、好きな場所に展開して操作してくださいね！（この問題は展開しないと必要なファイルを選択できません）
テキストファイルとは
ふつう私たちはExcelで使うデータはExcelファイルです。このExcelファイルって個人個人のパソコンに入ってるExcelで作るなら簡単に作れるものなんですけど、そうじゃないインターネット上のデータをダウンロードするものとかの場合って、作るのがめっちゃ大変なんです。もしExcelのデータで配布しようと思うと、それなりにお金をかけて仕組みを作らなきゃならなくなるので、今まで無料でダウンロードできてたものもできなくなっちゃうかもしれません。
インターネット上でデータを作るのがとっても簡単なのは「テキストファイル」っていうファイルなんです。
テキストファイルは、文字や塗りつぶしの色、図形やグラフがありません。ただ文字や数字が入っているだけです。
このデータをExcelで読み込めば次のように読み込めるのですね。
例えば、気象庁からある地点のある期間の気象情報をダウンロードできます。これもExcelファイルではなくテキストファイルの一つであるCSVファイルです。
https://www.data.jma.go.jp/risk/obsdl/index.php
そのほかにも、証券会社から株価の情報をダウンロードできたり、Amazonで販売していればその販売状況もダウンロードができます。これらもテキストファイルであることが多いです。
もっとも仕事で使うのはインターネット上からダウンロードするのではなく、社内で使っている販売システムとか営業システムなどから、入力しているデータを一気にダウンロードするという使い方です。
レジのデータなどもレジ本体にパソコンをつないで販売データをダウンロードできるものもありますね。
これができないと、売上データなどをもう一度Excelに手入力しなければならなくなります。
Excelを開いて、ファイルを開くででもテキストファイルを開くことができます。ほとんどのケースではこれで開いているという場合が多いようです。
テキストファイルからデータをインポートする
「テキストファイルからデータをインポートする」操作は、Excelを開いてファイルを開くでテキストファイル操作と違い、今、開いているブックの中にテキストファイルの中身を埋め込みます。
「テキストファイルからデータをインポートする」前にこのような表を用意しておきます。
セルE2にはB列のうちビジネスが何個あるか数える計算式「=COUNTIF(B:B,D2)」が入っています。
セルE2にはB列のうち趣味が何個あるか数える計算式「=COUNTIF(B:B,D3)」が入っています。
この状態で、セルA1からの範囲にテキストファイルを取り込んだとたんに、ビジネスと趣味が何個ずつあるのか計算されます。
このような販売情報のテキストファイルは販売するたびにどんどんデータが増えていきます。元のテキストファイルのデータが増えても取り込んでくれるのです。
最新の情報にするためには取り込んだ範囲を右クリックして更新します。
「テキストファイルからデータをインポートする」は次の3つがポイントじゃないかなって思います。
- 今開いているブックにデータを取り込む
- あらかじめ計算式などの仕組みを設定しておけばインポートしたとたんにその計算結果が出る
- テキストデータにデータが追加されても更新すれば最新情報に
だから「ファイルを開く」で開くよりもメリットがあるんですね。
実際に操作してみよう
では実際にダウンロードしたファイルの中の「01データの取得」を開いて、「操作しよう」シートで操作します。
操作は、「データ」タブから行います。「テキストファイルをデータとしてシートに入れる」と考えると「挿入」タブか「データ」タブかなぁと想像がつきます。
データタブの中の「テキストまたCSVから」、またはデータタブの中の「データの取得」の中の「ファイルから」の中の「テキストまたCSVから」をクリックして始めます。この2つはまったく同じものなんですね。
ここで、ファイル「登録日時コース」を指定し「インポート」ボタンをクリックします。
２、３秒だけ待ち時間があったあと、こんな画面が表示されます。実際には画面いっぱいくらいの大きな画面なので、左上と右下だけ表示してます。
ここで、読み込みボタンをクリックすると新しいシートにテキストファイルが入ります。今回のように計算式を設定しててインポートされると同時に集計が計算されるという風にしたいときは、指定した場所に入れないといけないので、読み込みボタンの▼をクリックし、読み込み先をクリックします。
そうするとこんな画面が出ます。はじめは新規ワークシートが選ばれているので、既存のワークシートのチェックを入れると、始まりのセルを指定することができます。これでOKボタンをクリックします。
実際にテキストファイルの中身が「操作しよう」シートのセルA1からに入って、計算式の入っているE2とE3のセルの値が計算されます。テキストファイルの中身はテーブルとして入るのでそのまま並べ替えやフィルターをすることができます。
画面右の方には「クエリと接続」の作業ウィンドウが表示されます。ここには、元のデータが今操作しているExcelのブック以外の場所にあるということが示されています。特に操作しなければ、テキストファイルの名称がここに設定されます。
うまくできましたか？
更新がいらない場合
入ってしまったらもうそれでいいという場合、このブックとテキストファイルがつながっている必要はありません。むしろつながっていると、テキストファイルを消したりフォルダの場所を移動したりすると、どれを見ているのか見失うので、つながりを外したほうがいいと思います。
その場合は、画面右の方には「クエリと接続」の作業ウィンドウの中の接続名（今回は「登録日時コース」）を右クリックし削除します。
こんなメッセージが出ますが削除します。これだけでテキストファイルとこのブックの関係は一切なくなります。
また、関係がなくなったとはいえ、シート上にある元テキストファイルはテーブルの形になっているので、無駄にカラフルかもしれません。フィルターボタンもいらない、ただのセル範囲にしたいというときは、元テキストファイルの範囲を選択すると、リボンに「テーブルデザイン」タブが表示されます。この中のテーブルスタイルの中の一番左上の「なし」をクリックします。
そうするとカラフルがなくなります。
次に、テーブルデザインタブの「範囲に変換」をクリックします。
ここではいをします。
フィルターボタンが消え、ただのセル範囲になりました。
テキストファイルをただここに入れたいなというときはこの操作をするといいでしょう。
ここで「もう一度」シートにテキストファイル「登録日時コース」を入れて、テキストファイルとExcelブックの関係をなくして、普通のセル範囲に戻すまでの操作の復習をしてみましょう。
インポートの3つのパターン
MOSExcel一般の出題範囲では「テキストファイルからデータをインポートする」と書いてあるのですが、実際にはこの操作は、インポートするテキストファイルの中身によって、取り込み方法が少し変わってくるのです。
この少しずつパターンの違う取り込み方法についての質問はMOSExcel一般の中で一番多い質問です。
次の表を見てみましょう。こんなExcelの表は普通は作りません。
1行目に項目名がないと、A列はくだもののみかんを表しているのか、もしかしたら人名のみかんという人を指しているのか、幼稚園のみかん組を指しているのかわからないのです。B列も販売数なのか、単価なのか、重量なのか、全くわからないです。
でもテキストファイルの中にはこんなデータがあるときがあります。はじめに1行目を用意しておいてね、このデータはその下の2行目から入れてねってことだと思います。
こんな表は作るかもしれませんね。
セルA1には項目名がないので、おそらく魚の名前ですが、B列はその感想なのだということがわかります。魚屋さんが市場で魚を見て、その状況をメモしたのかもしれませんね。ただ、この場合、セルA1に何も入っていないので1行目に項目名が入っているのか、Excelは判断できないのです。
このように普通に項目名がきちんと入っていないテキストファイルもあるのです。この場合、取り込み方法を切り替える必要があるのです。
普通に項目名が入っているテキストファイル、全く項目名が入っていないテキストファイル、一部だけ項目名が入っているテキストファイル、この3パターンがあるということを覚えておきましょう。
普通に項目名が入っているテキストファイルの操作
これは、上記の方法で取り込めばいいのでとても簡単です。
次の画面で一覧表が表示されたときに、項目名が太字で設定されていれば、そのまま取り込めます。
全く項目名が入っていないテキストファイルの操作
実際に「項目行なし」シートで操作してみましょう。
「テキストまたCSVから」をクリックし、テキストファイル「くだもの」を開いてみます。
そうすると1行目の項目名がなく「Column1」のような項目名になっています。その下の行は「みかん」というように明らかにデータなのですね。
この場合は、上記の方法と読み出し方は同じです。そのまま「項目行なし」シートのセルA1から取り込むと、項目名が「Column1」のような名前で取り込まれます。
シートに取り込んでから項目名を書き換えればいいパターンだと思います。
でも注意しなきゃいけないのは、更新すると項目名が「Column1」に戻るので、テキストファイルの関係は削除して使うことになると思います。
一部だけ項目名が入っているテキストファイルの操作
実際に「項目一部だけ」シートで操作してみましょう。
「テキストまたCSVから」をクリックし、テキストファイル「魚感想」を開いてみます。
そうすると1行目の項目名がなく「Column1」のような項目名になっています。しかし、その下の行は項目行なのですね。
Excelが1行目を項目名じゃないと誤って判断しちゃうんです。それをしっかりこの画面で確認します。
一部だけ項目名が入っているテキストファイルは、1行目を項目名とする場合と、しない場合があるのでそこで判断が必要になります。
ここで、1行目を項目名としているのであれば、上記の普通の方法で取り込めます。
しかし、今回のように、1行目を項目名にしてほしいのに、項目名にしてくれない場合は、右下の「データの変換」ボタンをクリックします。
こんな画面が表示されます。ホームタブの「1行目をヘッダーとして使用」ボタンをクリックします。
そうすると「感想」が項目行になってくれます。
あとはシート上にこのデータを入れるのですが、ファイルタブの「閉じて次に読み込む」にします。
そうすると、シートにインポートできる画面になります。
項目名がないセルA1は「Column1」になりますが、1行目は項目名となってくれます。
「テキストまたCSVから」をクリックし、テキストファイルを開いてその画面で項目名になっているべきなのか、なっていないべきなのかを見て、項目行が「Column1」となっていた場合に、その下の行が明らかに項目行であれば「データ変換」が必要になります。
この判断がMOSのデータのインポートの問題では必要になります。
Webデータのインポート
MOSの出題範囲で「テキストファイルからデータをインポートする」の下に書いてあるのが「オンラインソースからデータをインポートする」で、これはWebページにある表をExcelに取り込む操作です。
テキストファイルではなくWebページのデータでは、常に誰かが最新のデータにしています。
Webページ「https://www.maho-c.com/excel-practice/」では、Excelの問題を配布しているのでぜひ練習をしてほしいんですけど、その練習問題が一覧表になっていて、時々新問題が追加されます。
このような一覧表をWebから入手出来たら、そのあと何かの集計ができるのかもしれません。
実際に「講座一覧」シートで操作してみましょう。
データタブの中の「Webから」、またはデータタブの中の「データの取得」の中の「その他のデータソースから」の中の「Webから」をクリックして始めます。
この画面が出てくるので、表のあるWebページのURLを指定しOKボタンをクリックします。この後に、もう一度URLの一部が表示される画面が出てくる時がありますが、それもOKなどで先に進めてください。
そうするとこんな画面（一部省略しています）が出てきて、左の「HTMLのテーブル」の中の「テーブル1（数字は1かどうか定かではない）」をクリックすると、表が右の画面に表示されます。
今回の場合は、ちゃんと項目名が全部入っていますが、Excelは項目名はないと判断したようで項目名が「Column1」になっていますね。「データの変換」で「1行目をヘッダーとして使用」ボタンで処理する必要がありますね。
最終的に次のように取り込まれます。
まとめ
MOSExcel一般の出題範囲の一番上に書いてあるので、そのままこの順番で作っている対策テキストだと、一番初めにMOSExcel一般で難しい操作で始まるので、くじけてしまうか、無理やり納得しちゃうかになりがちなんですが、実はこれだけ魅力的な機能だし、Excelのデータがほかのものとつながって何かをし始めたら、もっともっと面白いことができそうな気がしませんか？
やっぱりMOSは合格が目的になるんですが、本当に合格だけを目的にしてしまって、こんな魅力的な機能に気づかないなんてもったいないです。
MOSの勉強法として、ひとつの機能を学んだら、その周辺機能も深堀りして覚えていってほしいのです。「テキストファイルからデータをインポートする」についてはここまで深堀りすれば、実際に仕事でも役立つんじゃないかなって思います。
じつは、「テキストファイルからデータをインポートする」機能は、Excelの超高度な機能であるPowerQueyという機能を使っています。これはMOSExcel上級でも扱わない範囲です。
このPowerQueryを究めていくと、項目名がないものに項目名を設定したり、5行目から取り込んだり、項目ごとの合計を集計した結果の表として取り込んだりすることができます。
それだけ難しいものが最初に書いてあるので、MOSが難しく感じてしまうかもしれませんが、この機能がMOS一般の中で最高に難しい操作なので、安心して勉強を進めましょう。
今回はとにかくものすごくわかりやすいように「テキストファイルからデータをインポートする」の魅力とその活用例を紹介しました。積極的に何に使えるか、どんどん考えて活用していきましょう。
では、最後に「練習１」から「練習２」シートで練習問題で操作ができるようになったかチェックしましょう。
練習問題
練習1
テキストファイル「スコア」を「練習1」シートのセルA3から取り込んでください。「Team」の行がシートの3行目になるように取り込みます。
手順
「テキストまたCSVから」をクリックし、テキストファイル「スコア」を開きます。
プレビューを見ると1行目の項目名がなく「Column1」のような項目名になっています。
その下がTeamの項目名なので、右下の「データの変換」ボタンをクリックします。
ホームタブの「1行目をヘッダーとして使用」ボタンをクリックします。
ファイルタブの「閉じて次に読み込む」にします。
既存のワークシートにして、セルA3を指定しOKボタンをクリックします。
練習2
テキストファイル「請求書」を「練習2」シートのセルA1から取り込んでください。取り込んだ後はセルに色が付かない状態のテーブルではない状態にし、「御中」が1行目に入るようにセルの削除を行います。
手順
「テキストまたCSVから」をクリックし、テキストファイル「請求書」を開きます。
プレビューを見ると1行目の項目名がなく「Column1」のような項目名になっていますが全部の列がそうなので、「読み込み」ボタンの▼をクリックし、「読み込み先」をクリックします。
既存のワークシートにして、セルA1を指定しOKボタンをクリックします。
取り込まれた表をクリックし、画面右の方の「クエリと接続」の作業ウィンドウの中の「請求書」を右クリックし削除します。
取り込まれた表をクリックし、「テーブルデザイン」タブのテーブルスタイルの中の一番左上の「なし」をクリックします。
テーブルデザインタブの「範囲に変換」をクリックします。
1行目を削除します。