Excelデータベースの鉄則。分析はデータの下ごしらえで決まります

Excelでのデータ分析にあたり一番大事なのが「データの下ごしらえ」です。「下ごしらえ」とは「集計できる形にデータを整えること」です。実践の場では、教科書のようにデータは美しく整っていません。ほとんどの場合、はっきり言って「無茶苦茶」。または変なデータが混ざっています。これを整えられるかどうかが決め手で、かつ重要なスキルです

結局は経験しながら、ひとつひとつ身に着けていくしかないのですが、注目すべきことについて、私なりにピックアップしてみます。

Excelの機能

Excelを仕事の使い方は大まかに

  1. 入力
  2. 計算(集計・分析)
  3. アウトプット(成果物の作成、印刷)

の3つがあります。業務がどの部分かによって必要なスキルは変わります。

Excelの機能は多岐に渡るので、もし「入力だけ」「計算だけ」など業務が限られているのであれば、そこだけ勉強してもいいと思います。

今回はデータを整える下準備について書きますが、必要なところだけ参考にしていただければと思います。

データを整える

f:id:yururimaaruku:20161023074931p:plain

分析しやすいデータとは

ずばりデータベースです。データベースとしての体裁が整っていると、並べ替え、抽出、集計が容易になります。強力な集計機能であるピボット集計オートフィルター機能も使えます。

「データを整えておくこと」の重要性は、慣れないうちは分からないかもしれませんが、経験を積むと分かります。データを作った人がExcel集計が分かっているかどうかは、ここが意識されているかどうかで分かります。

自分でデータを作る時は、出来るだけデータベースとして成立するデータづくりを心がけましょう。また、変なデータを受け取った時もデータベースとして整えるスキルを身に着けましょう。

データベースの鉄則とは

 データベースとは、複数の主体で共有、利用したり、用途に応じて加工や再利用がしやすいように、一定の形式で作成、管理されたデータの集合のこと。

データベースとは|database|DB − 意味 / 定義 / 解説 / 説明 : IT用語辞典より

簡単に言うと、このような縦横に揃った「データの集まり」のことです。

f:id:yururimaaruku:20161022235543p:plain

横1行をデータ(レコード)、縦をフィールドと呼びます

以下の点が鉄則です。

1行目がタイトル、2行目以降がデータ

タイトル名(フィールド名)は1行で入力する。

タイトルが複数行になっていれば1行に書き換えます。私がよくやるのは黒1行挿入です。書式は何でもよいのですが、挿入行であることが分かるように背景色を黒、文字色を白にしています。データを渡す時には、これが分析用のタイトル(フィールド名)であることを相手に伝えます。

参考までに私のやり方です

例えばこんなデータがあったとします。タイトル行が2行に渡っていますね。本来であれば1行にしたいところですが、表としてはこのままの方が見やすいですね。例えば上司に提出する場合やお客さんに渡す場合などは特にそうです。やむを得ず残すとしましょう。

f:id:yururimaaruku:20161023075124p:plain

その場合、集計用に使えるようにタイトル用の行を挿入します。下の図3行目の黒い部分がそれです。この時、各フィールドには分かりやすくて重複しない名前を付けます。

これ以降の作業は、3行目から最下行までを一つのデータベースとして扱います。

f:id:yururimaaruku:20161023075451p:plain

提出する相手のスキルによって変えますが、不要であればここを非表示にして提出します。非表示にしていることが分かるようにグループ化で非表示しています。

(行番号で行ごと選択→データ→グループ化)

行番号の左横にプラスマークが入るので「何か隠れているな」と分かる人は分かります。

f:id:yururimaaruku:20161023075843p:plain

この黒い行にタイトルをつける時はタイトル(フィールド名)は重複不可と書きましたが、その他にも注意点はあります。

MEMO
フィールド名の注意点

  • タイトルに同じ名前を付けない。同じだとピボット集計で扱えません。
  • 空白も入れない。
  • 自分が判別しやすい名前を付ける。
  • 後で変更すると面倒なので、よく考えて分かりやすい名前を付けておく。

1件1行

1件が複数行に渡っていることがよくあります。表としては見やすいかもしれませんが、集計には適していません。分析するなら1件1行に変換する必要があります。

▼よくありますが、こういうものはダメです。サンプルとして簡単に作りました。集計には月が横並びなのも良くないですね。でもこういう表は多いです。ここでは複数行に渡っているところだけ注目してください。

f:id:yururimaaruku:20161023080454p:plain

1件1行にする方法
例えば、1件あたりの行数が一定なら以下の方法が使えます。

セル参照で1行にデータをまとめます。(例:セルM3に入っているのは =H5 )

3行目に1レコードが出来上がりますのでそれを下にコピーします。

A列に抽出用のフィールドを作ります。1,2,3,4と入力して下までコピーしています。オートフィルターで2以外のデータを抽出して、行ごと削除します。(抽出する方法は他にも色々あります。オートフィルターの使い方は後日書きます。)

f:id:yururimaaruku:20161025221203p:plain

空白行は不可

空白行が入ると分断されてしまい、そこでデータベースが切れていると見なされます。一体として扱うにはすべてのフィールドが空白の行を無くす必要があります。

▼空白行があることで、データベースが赤い部分までだとExcelが勘違いする。

f:id:yururimaaruku:20161023082136p:plain

空白行を削除する方法
すべて空白の行を抽出して削除するには、抽出用のセルを作ってフィルターでひろう方法があります。

全データを&でつないだフィールドを一つ作ります。例:J列

列Jで空白のデータを抽出して、不要であれば行ごと削除します。

f:id:yururimaaruku:20161023081923p:plain

空白行を残したまま除外する方法
J列に最下行まで関数を入れた時点で、一つのデータベースとしてまとまりますから、ここは、=IF(LENB(A4&B4&C4&D4&E4&F4&G4&H4)=0,”空白”,””)という数式を使って、何も入っていない行に目印をつけておくのもいいですね。ピボットなどで集計する時はこの「空白」を除いて計算します。

全体の件数を変えずに扱えるので、後々この方がが実務ではよいと思います。始めから終わりまで合計件数を一致させて処理していくことは、非常に有効です。

f:id:yururimaaruku:20161023082503p:plain

表記を揃える

半角・全角、ひらがな・カタカナの表記を揃えます。ちなみに、データを変換する時は元データは置いておいて、右隣または、右端に変換後のフィールドを作ります。例えば「担当者」「担当者_変換」または「担当者★」などとします。そしてフィールド名のセル色を変えて、元データではないことを明確にしておきます(上の図のJ列のような感じです)。集計をとるときは変換後のデータを使用します。

※関数を入力する時は、関数ウィザードは使わずに手入力しましょう。理由は後日書きます。よく関数はすぐに覚えます。

半角・全角を変換する関数

全角→半角 =asc(セル)

半角→全角 =jis(セル)

ひらがな・カタカナを変換する関数

=phonetic(セル)フリガナ設定を使って変換する方法があります(省略)。

余分な空白を取る関数

見えないところに空白があると別データだと見なされて結果がまとまらないことがあります。必要な時は関数で空白を取り除きます。

空白を削除する =trim( セル )

実践ではこんな程度ではありません。「困った」をまとめてまた記事を書いてもいいと思います。「困った」があれば、よろしければお知らせください。できる限りお答えしたいと思います。

セル結合しない

セルを全選択(行番号1の上の空白のセルをクリック)してセルの書式設定→配置タブ→セル結合のチェックを外します

複数の範囲内で中央に文字列を配置したい時は、「選択範囲内で中央」を使えば結合せずに中央寄せすることができるのでおすすめです。

選択範囲内で中央
複数セルを選択→右クリック→セルの書式設定→横位置

テーブル機能はひとまず置いておきます

テーブル機能を使えば、データがひとまとまりに認識されて非常に扱いやすくなります。しかし、Excelに慣れていない方はまずはテーブル機能を使わずにマスターするのが良いと思っています。これは私の考えです。色々意見はあると思いますが、経験上、あまり慣れていない方がいきなりテーブルを扱うと混乱する事が多いです。

まずは、「テーブルを使わずにExcelがどのように動くのか」をよく経験した上でテーブル機能を扱えばそのすごさがより実感できるし、習得がスムーズにいくと考えます。ですので、当面の間はテーブル機能を使う話はしません。

テーブル機能は非常に強力な便利機能ですので、Excelに慣れたら、ぜひマスターしてください。