Excel集計作業前にはデータ整理を ネットから取得したデータをExcelで使うために整理します

ネットから取得したデータをExcelで加工することがよくあります。その際に使える便利な機能と関数を紹介します。

スポンサーリンク

データはリスト形式で整理

私がネットからデータを集めるのは、上場企業の財務データやサッカー選手のプレイデータなどが中心で、最初からExcelやCSVの形で用意されていないものがほとんどです。

 
HPやPDFで開示されているデータはそのまま「コピー&ペースト」してExcel上で加工していきますが、その際はリスト形式で整理する世にしています。

 
Excelは大量のデータを処理するのに力を発揮しますが、その処理のは、縦方向に並んだデータを前提として設計されています。
よく使う関数の一つにVLOOKUP関数がありますが、この関数の仕組みも、データを縦方向に検索して一致があればデータを抽出するようにできています(VLOOKUP関数についてはここでは詳しく触れません)。

 
スクリーンショット 2014 12 18 8 26 08

 
データも、このような縦方向の処理に対応するように整理しておくと、加工や集計もしやすくなります。
この縦方向に整理したデータものが「リスト形式」です。
もう少し詳しく説明すると、各列に見出しがあり、その下にデータが並べる形式のことです。

 
改めて説明すると難しく聞こえますが、実際には皆さんがExcel上で最も頻繁に目にする形式なので、馴染みがあるはずです。

 
スクリーンショット 2014 12 18 8 40 42

 
「リスト形式」にしておくとVLOOKUP関数などの関数以外に、特定の条件を指定してデータを抽出する「オートフィルタ」、データの並べ替えと集計を同時にやってしまう「ピボットテーブル」など
が使えますので非常に便利です。

 
今回は、サンプルとして海外のサッカー選手のサラリーデータを加工してみます。
データはTSM PLUGさんのページからFC Barcelonaのデータを元に加工します。

 

スクリーンショット 2014 12 18 8 50 50

(TSM PLUG:「Barcelona Players Salaries 2014-15」
http://www.tsmplug.com/football/barcelona-players-salary-list-2014/)
 

このページのデータ部分をそのまま「コピー&ペースト」したのが次の画像です。リスト形式でデータが整理されています。

 

スクリーンショット 2014 12 18 8 56 25

 
このHPのデータは行、列が混同されることなく、1つのデータが1つのセルに収まるようにペーストできるので、追加のデータ整理が不要で楽です。
 
PDFからのデータ移行の場合は、データの区分が明確でない箇所があることも多く、データをExcel上で整理し直すケースがあります。

 

 

データの形式を整える

このままの形でデータを加工することも可能ですが、他のデータとも合わせて表にするような場合には、見た目にも配慮して形式を整えておく必要があります。

 
特に装飾を加える必要はありません。いつも使っているExcelのデータと同じ形にするだけでOKです。
ここでは、範囲指定のショートカットと「形式を選択して貼り付け」を使います。

 
手順は以下の通りです

 
1.データ全体を範囲指定してコピー
2.「形式を選択して貼り付け」で「値貼り付け」を選択

 
まず、コピーする範囲を指定します。指定するのはHPから抽出したデータ全体です。
この程度のデータ量ならマウスのドラッグ(左クリックしたままでマウスを移動させる)で十分ですがデータが多くなった場合を考えてショートカットで指定する方法を覚えておきましょう。

 
1.データの左端上部のセルをアクティブにします(マウスをセルに合わせて左クリック。緑の枠で表示されます)

スクリーンショット 2014 12 18 9 20 53

 

2.「shift+control+矢印キー(→)」で右方向のデータを指定
 この「shift+control+矢印キー(→)」はデータが切れるセルまで、範囲指定ができます。

スクリーンショット 2014 12 18 9 28 54

 
3.「control+C」でコピー

 
4.ペーストする先頭のセルをアクティブに

 
5.マウスを右クリックして「形式を選択して貼り付け」を選びますが、マウスではなくキーボードの「S」でショートカットします。

 
6.「形式を選択して貼り付け」画面が開くので「値」を選択しますが、ここでもマウスで選択するのではなく、キーボードの「V」でショートカット。
 
マウスでクリックすると、「値」の選択のあと、マウスを移動させて「OK」を選択をしなければいけません。
 

スクリーンショット 2014 12 18 9 38 19

 
キーボードのショートカットなら
「形式を選択して貼り付け」を左手薬指で「S」を選択した後、
→「値」を左手人差し指で「V」をタッチして選択
→右手小指で「Enter」をタッチして完了
と一瞬の流れで作業を完了させられます。

 

スクリーンショット 2014 12 18 9 38 10
 

その結果、Excelで設定した形式で表示されるようになりました。

 

スクリーンショット 2014 12 18 9 50 33

 

 

数値データにするため通貨の記号を削除する

これでデータ加工できる状態になったと思って、集計を始めたくなりますが、もうひと手間必要になります。

 
WEEKLY WAGEの列を見て下さい。

 

スクリーンショット 2014 12 18 10 00 48

 
ご覧の通りセルの中に数字だけではなくポンドの記号まで入力されています。
データを集計するには、文字であるポンドの記号と数字を同じセルに併存させることはできません。

 
ポンド記号を削除してセルを数字のみにします。
ここでは2つの方法を確認しておきましょう。

「置換」を使う

置換という機能を使う方法です。
文字通り置き換える機能ですが、ポンド記号£を空欄に置き換えれば解決です。

 
1.置き換えたい文字があるセルを指定

 

スクリーンショット 2014 12 18 10 14 10

 
2.「Contorol」+「H」で「検索と置換」画面を開き「置換」タブを選択ます

 
スクリーンショット 2014 12 18 10 07 44

 
3.「検索する文字列」の欄にポンド記号「£」を入力
 ここは、自分で入力するのではなく、置換したい文字(ここでは「£」)をコピー&ペーストするのが効率的です。
 

スクリーンショット 2014 12 18 10 12 16

 
4.「置換後の文字列」は空欄

スクリーンショット 2014 12 18 10 15 12

 
5.「全て置換」をクリック
 
スクリーンショット 2014 12 18 10 22 01

 
スクリーンショット 2014 12 18 10 24 19

 
「£」がとれて数字だけのデータになりました。

 

関数を使う方法

次に関数を使う方法です。
 
1.「WEEKLY WAGE」の隣に列を挿入して、関数を入れる列を作ります。
 
スクリーンショット 2014 12 18 10 27 17

 
2.RIGHT関数を使います。
RIGHT関数は、セルを指定した上で、文字数を指定します。
すると、セルにある文字を”右”から、指定した文字数分だけ表示してくれる関数です。
 
「KANA-BOON」と入力されたN2セルを指定して、「右から4文字分表示せよ」と指示したのが次のRIGHT関数です。
 

スクリーンショット 2014 12 18 10 36 10

 
右から4文字分「BOON」が表示されました。
 
スクリーンショット 2014 12 18 10 42 54

 
この関数を使って「£」を削除します。
WEEKLY WAGEの先頭のセルを指定してRIGHT関数を入力します。
 

スクリーンショット 2014 12 18 10 46 16

 
3.文字数を指定します。
 何文字にするかが問題ですがここでも関数を使います。
 LEN関数です。
 LEN関数は指定したセルにある文字の文字数を表示してくれる関数です。

 
LEN関数に、先ほどの「KANA-BOON」と入力したセルを指定してみます。
 

スクリーンショット 2014 12 18 10 50 41
 
正しく文字数が表示されました。
 
スクリーンショット 2014 12 18 10 52 27

 
このLEN関数を使って文字数を指定します。
「£」を除けば、数値だけを表示できますから、セルの文字数から「£」の1文字分を引いた数を指定すればいいわけです。
つまり、「LEN(I2)-1」のように指定します。

 

スクリーンショット 2014 12 18 10 57 13

 
「£」を除いた数字が表示されました。
 

スクリーンショット 2014 12 18 11 01 16

 
あとはこのセルをコピーします。

 

スクリーンショット 2014 12 18 11 03 27

 
これで完成ですが、このままでは数値として加工できないので、さらに、コピーして「形式を選択して貼り付け」で「値」で貼り付け。
さらに、「数値」に変換してようやく完成します。

スクリーンショット 2014 12 18 11 05 59

 

 

まとめ

Excelは集計も重要ですが、その前のデータ整理が非常に重要です。
効率的に集計を行うために、正しくデータ整理できるようにしておきましょう。
<おまけ>
寒すぎて足下がつらいです。
みなさん、オフィスの足下の防寒はどうされているのでしょうか。
コストパフォーマンスのよいグッズがあれば教えていただきたいです。

タイトルとURLをコピーしました