実践で使うExcelとAccessの話

20年以上Excelを使ってきた筆者が綴ります

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

f:id:yururimaaruku:20161023074731p:plain

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

 

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

<スポンサーサイト>

 

その前に・・

Excelの機能

Excelは様々な事ができます。仕事で使う方法として大まかに、入力、計算(集計・分析)、アウトプット(成果物の作成、印刷)の3つがあります。どの部分をやるかによって必要なスキルは変わります。

 

Excelの機能は多岐に渡るので、もし入力だけ、とか、計算だけとか業務が限られている、または、そこだけでよいというのであれば、そこだけ勉強してもいいと思います。今後、入力やアウトプットの話も順番に書いていきます。必要なところだけ参考にしていただければと思います。

 

分析の話から焦点を当てていきます

このブログではまず計算(集計・分析)の話にスポットを当てていきます。Excelの機能の中でも一番の醍醐味で、面白い所だからです。Excelは表計算ソフトですから。業務でもよく使いますし、出来ると非常に重宝がられます。応用もききます。仕事でも日常生活でも強い味方になってくれます。

 

データを整える

f:id:yururimaaruku:20161023074931p:plain

分析しやすいデータとは

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

 

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

 

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

 

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

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

 

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

 

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

 

データベースの鉄則とは

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

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

 

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

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

f:id:yururimaaruku:20161022235543p:plain

 

以下の点が鉄則です。

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

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

 

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

 

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

 

こんなデータがあったとします。

タイトル行(行1、行2)のデザインが見やすいので、やむを得ず残す時の話です。

f:id:yururimaaruku:20161023075124p:plain

下の図のように3行目の黒い部分を挿入します。

そして、各フィールドに分かりやすくて重複しない名前を付けます。

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

f:id:yururimaaruku:20161023075451p:plain

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

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

 

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

f:id:yururimaaruku:20161023075843p:plain

タイトル(フィールド名)は重複不可

・タイトルに同じ名前を付けない。同じだとピボット集計で扱えません。

・空白も入れない。

・自分が判別しやすい名前を付ける。

・後で変更すると面倒なので、よく考えて分かりやすい名前を付けておく。

 

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の上の空白のセルをクリック)してセルの書式設定→配置タブ→セル結合のチェックを外します。(今日は省略します)

 

もっと色々なケースがありますが、思いつくところをピックアップしました。できればまた後日記事にします。

 

また、長くなってしまいました。ところどころ端折っています。整え方にも色々あって、ほんの数例しか出せませんでしたが、それぞれピックアップして「困った」に答えていってもいいなと思いました。

 

必要なところがあればまた後日補足します。今日はここで終わります。

 

何かのご参考になれば幸いです。

 

ではまたお会いしましょう。