ネットから取得したデータをExcelで加工することがよくあります。その際に使える便利な機能と関数を紹介します。
データはリスト形式で整理
私がネットからデータを集めるのは、上場企業の財務データやサッカー選手のプレイデータなどが中心で、最初からExcelやCSVの形で用意されていないものがほとんどです。
HPやPDFで開示されているデータはそのまま「コピー＆ペースト」してExcel上で加工していきますが、その際はリスト形式で整理する世にしています。
Excelは大量のデータを処理するのに力を発揮しますが、その処理のは、縦方向に並んだデータを前提として設計されています。
よく使う関数の一つにVLOOKUP関数がありますが、この関数の仕組みも、データを縦方向に検索して一致があればデータを抽出するようにできています（VLOOKUP関数についてはここでは詳しく触れません）。
データも、このような縦方向の処理に対応するように整理しておくと、加工や集計もしやすくなります。
この縦方向に整理したデータものが「リスト形式」です。
もう少し詳しく説明すると、各列に見出しがあり、その下にデータが並べる形式のことです。
改めて説明すると難しく聞こえますが、実際には皆さんがExcel上で最も頻繁に目にする形式なので、馴染みがあるはずです。
「リスト形式」にしておくとVLOOKUP関数などの関数以外に、特定の条件を指定してデータを抽出する「オートフィルタ」、データの並べ替えと集計を同時にやってしまう「ピボットテーブル」など
が使えますので非常に便利です。
今回は、サンプルとして海外のサッカー選手のサラリーデータを加工してみます。
データはTSM PLUGさんのページからFC Barcelonaのデータを元に加工します。
(TSM PLUG:「Barcelona Players Salaries 2014-15」
http://www.tsmplug.com/football/barcelona-players-salary-list-2014/)
このページのデータ部分をそのまま「コピー＆ペースト」したのが次の画像です。リスト形式でデータが整理されています。
このHPのデータは行、列が混同されることなく、1つのデータが1つのセルに収まるようにペーストできるので、追加のデータ整理が不要で楽です。
PDFからのデータ移行の場合は、データの区分が明確でない箇所があることも多く、データをExcel上で整理し直すケースがあります。
データの形式を整える
このままの形でデータを加工することも可能ですが、他のデータとも合わせて表にするような場合には、見た目にも配慮して形式を整えておく必要があります。
特に装飾を加える必要はありません。いつも使っているExcelのデータと同じ形にするだけでOKです。
ここでは、範囲指定のショートカットと「形式を選択して貼り付け」を使います。
手順は以下の通りです
１．データ全体を範囲指定してコピー
２．「形式を選択して貼り付け」で「値貼り付け」を選択
まず、コピーする範囲を指定します。指定するのはHPから抽出したデータ全体です。
この程度のデータ量ならマウスのドラッグ（左クリックしたままでマウスを移動させる）で十分ですがデータが多くなった場合を考えてショートカットで指定する方法を覚えておきましょう。
１．データの左端上部のセルをアクティブにします（マウスをセルに合わせて左クリック。緑の枠で表示されます）
２．「shift+control+矢印キー（→）」で右方向のデータを指定
この「shift+control+矢印キー（→）」はデータが切れるセルまで、範囲指定ができます。
３．「control＋C」でコピー
４．ペーストする先頭のセルをアクティブに
５．マウスを右クリックして「形式を選択して貼り付け」を選びますが、マウスではなくキーボードの「S」でショートカットします。
６．「形式を選択して貼り付け」画面が開くので「値」を選択しますが、ここでもマウスで選択するのではなく、キーボードの「V」でショートカット。
マウスでクリックすると、「値」の選択のあと、マウスを移動させて「OK」を選択をしなければいけません。
キーボードのショートカットなら
「形式を選択して貼り付け」を左手薬指で「S」を選択した後、
→「値」を左手人差し指で「V」をタッチして選択
→右手小指で「Enter」をタッチして完了
と一瞬の流れで作業を完了させられます。
その結果、Excelで設定した形式で表示されるようになりました。
数値データにするため通貨の記号を削除する
これでデータ加工できる状態になったと思って、集計を始めたくなりますが、もうひと手間必要になります。
WEEKLY WAGEの列を見て下さい。
ご覧の通りセルの中に数字だけではなくポンドの記号まで入力されています。
データを集計するには、文字であるポンドの記号と数字を同じセルに併存させることはできません。
ポンド記号を削除してセルを数字のみにします。
ここでは2つの方法を確認しておきましょう。
「置換」を使う
置換という機能を使う方法です。
文字通り置き換える機能ですが、ポンド記号£を空欄に置き換えれば解決です。
１．置き換えたい文字があるセルを指定
２．「Contorol」＋「H」で「検索と置換」画面を開き「置換」タブを選択ます
３．「検索する文字列」の欄にポンド記号「£」を入力
ここは、自分で入力するのではなく、置換したい文字（ここでは「£」）をコピー＆ペーストするのが効率的です。
４．「置換後の文字列」は空欄
５．「全て置換」をクリック
「£」がとれて数字だけのデータになりました。
関数を使う方法
次に関数を使う方法です。
１．「WEEKLY WAGE」の隣に列を挿入して、関数を入れる列を作ります。
２．RIGHT関数を使います。
RIGHT関数は、セルを指定した上で、文字数を指定します。
すると、セルにある文字を”右”から、指定した文字数分だけ表示してくれる関数です。
「KANA-BOON」と入力されたN2セルを指定して、「右から４文字分表示せよ」と指示したのが次のRIGHT関数です。
右から4文字分「BOON」が表示されました。
この関数を使って「£」を削除します。
WEEKLY WAGEの先頭のセルを指定してRIGHT関数を入力します。
３．文字数を指定します。
何文字にするかが問題ですがここでも関数を使います。
LEN関数です。
LEN関数は指定したセルにある文字の文字数を表示してくれる関数です。
LEN関数に、先ほどの「KANA-BOON」と入力したセルを指定してみます。
正しく文字数が表示されました。
このLEN関数を使って文字数を指定します。
「£」を除けば、数値だけを表示できますから、セルの文字数から「£」の1文字分を引いた数を指定すればいいわけです。
つまり、「LEN(I2)-1」のように指定します。
「£」を除いた数字が表示されました。
あとはこのセルをコピーします。
これで完成ですが、このままでは数値として加工できないので、さらに、コピーして「形式を選択して貼り付け」で「値」で貼り付け。
さらに、「数値」に変換してようやく完成します。
まとめ
Excelは集計も重要ですが、その前のデータ整理が非常に重要です。
効率的に集計を行うために、正しくデータ整理できるようにしておきましょう。
＜おまけ＞
寒すぎて足下がつらいです。
みなさん、オフィスの足下の防寒はどうされているのでしょうか。
コストパフォーマンスのよいグッズがあれば教えていただきたいです。