第2回　データの分析（データクレンジング）
Excelを使ったデータチェックと
データクレンジング

データ分析の前準備
（データチェックとクレンジング・加工等）

今回はExcelを使って、データ分析の前準備としてデータチェックとデータクレンジングについて説明します。
第1回の問題解決方法でも説明したように、PPDACサイクルにそって分析を進めていきます。

使用するデータは、サンプルデータとして作成された家電卸企業の売上データ(22,757件)です。

Excelを使ったデータチェックとデータクレンジングを動画で紹介しています。
項目ごとにご覧いただく方法と、まとめてご覧いただく方法がございます。
【項目ごとにご覧いただく場合】
【まとめてご覧になる場合】 ※動画で紹介している操作などの詳細については、以下をお読みください。

① Problem：問題点の洗い出しと、課題・指標の設定
データ分析を行う前に、問題、課題、指標を設定します。 この企業の場合は次の通りです。

• 問題：売上が平準化し伸びない
• 課題：売上向上
• 指標：売上高

② Plan：計画の策定
次に売上高が増えるような施策を考え、分析手法や収集すべきデータを決めます。
今回は既存データの分析ですので、データから売上増につながる項目と分析手法を考えます。

売上を上げる施策を考えた場合、次のようなことが考えられます。

• 売上高の低い担当者の売上向上
• 取引高の低い得意先の取引高向上
• 売れない商品の拡販
• 売上高の低い月の拡販

これらの施策に対する仮説と分析方法は、次のようになります。

項目	施策	仮説	分析方法
担当者	売上高の低い担当者の売上向上	担当者毎の売上高に大きな違いがある	単純集計と クロス集計
商品名(CD)	売れない商品の拡販	売れる商品と、売れない商品がある
得意先名	取引高の低い得意先の取引高向上	取引先毎に売上高に大きな違いがある
売上年月	売上高の低い月の拡販	売上高の月変動が大きい

③ Data：データ収集・データチェックとデータクレンジング
今回は、既存データを使用するためデータ収集は必要はありませんが、分析を迅速かつ正確に行うためにデータのチェックとクレンジングを行います。

それでは、Excelのピボットテーブルを使ってデータの重複や間違いなどがないかを調べ、整理してみることにします。

Excelのピボットテーブルを使ってみよう
ピボットテーブルの操作方法

ここでは、Excelのピボットテーブルの使用方法について説明します。

クリックして拡大
• 分析するデータファイルをExcelで開きます。
• 表中のセルをクリック。
  図では、表頭の「伝票番号」(A1)をクリック
• 「挿入」タブをクリック
• 「テーブル」グループの「ピボットテーブル」をクリック
• 「ピボットテーブルの作成」ダイアログボックスの＜OK＞ボタンをクリック

新しいシートに「ピボットテーブル」が表示されます。

「ピボットテーブルのフィールドリスト」から「ボックス」と呼ばれる4つの領域に「項目」をドラッグすることで、データのチェックや分析が行えます。

ボックス名	意味
レポート フィルター	項目内の種類別集計を行う際、その項目に相当するフィールドリストの項目をドラッグ
行ラベル	表側にあたり、集計したいフィールドリストの項目をドラッグ ※データの入っていない箇所があった場合、空白セルとして最後に表示
列ラベル	表頭にあたり、集計したいフィールドリストの項目をドラッグ
値	集計したい値のフィールドリストの項目をドラッグ 項目の内容ごとの合計値を表示 ※数値項目以外の項目をドラッグすると、その内容の個数を表示

Excelのピボットテーブルを使って
データチェックとデータクレンジング。

Excelのピボットテーブルを利用してデータのチェックとクレンジングを行います。

① 伝票番号の枚数と重複の確認

伝票番号ごとの枚数を調べ、重複がないかを調べます。

• 「行ラベル」ボックスに「伝票番号」をドラッグ
• 「値」ボックスに「商品名」をドラッグ
  (「商品名」以外でも文字列の項目であれば可)

「行ラベル」に伝票番号、「データの個数／商品名」に伝票番号ごとの枚数が表示され、すべて１であれば、重複がないことになります。

伝票の枚数が少なければ、目視でのチェックも可能ですが、今回は2万件を超える件数ですので目視ではとても不可能です。

Excelの「COUNTIF関数」を使って重複がないかをチェックしてみます。
B列の4行目から合計値の前の行(22,760行目)までを範囲指定し、1の数を数えます。

=COUNTIF(B4:B22760,1)

結果は、22,757となり、合計の値以外の数値が入っている行数は 　22,760－3＝22,757　 ですので、すべての行の値となっており、伝票の重複は無いことがわかりました。
また、「行ラベル」の最後に空白セルがないことから、「伝票番号」の抜けがないことも確認できました。

② 売上年月の確認

売上年月を確認するには、次のような操作を行います。

• 「売上年月」を「行ラベル」ボックスにドラッグ

「行ラベル」の値が2000年1月から2000年12月までのデータであることわかります。
想定外の年月が表示されていなければ特に問題はありません。
総計の上のセルに空白セルはないので、「売上年月」の抜けが無いことも確認できました。

③ 商品CD・商品名・販売単価の確認

③-1 商品CD・商品名の対応の確認

「商品コード(CD)」と「商品名」が、それぞれ1対1で対応しているか確認します。

• 「商品CD」を「行ラベル」ボックスにドラッグ
• 「商品名」を「行ラベル」ボックスの「商品CD」の下にドラッグ

「商品コード(CD)」と「商品名」が1対1ならば、ブランクのセルや1つの「商品コード(CD)」に対し複数の「商品名」が存在することはありません。
空白セルはないので、「商品コード(CD)」の抜けたデータはないことが確認できました。

③-2 商品名に抜けがないか確認

「COUNTBLANK関数」を使って空白セルを数え、「商品名」の抜けがないかを確認します。

=COUNTBLANK(A4:A121)　　※範囲：セルA4～A121(総計の上のセル)まで

結果は「0」が返ってきました。「商品コード(CD)」の分も含まれていますが、「商品コード(CD)」も抜けがないので、「商品名」には抜けがないことになります。

③-3 商品CDの件数の確認

「商品コード(CD)」は数値項目なので「COUNT関数」を使って件数を確認してみます。

=COUNT(A4:A121)　　※範囲：セルA4～A121(総計の上のセル)まで

結果は、「59」が返ってきました。

③-4 商品名の件数の確認

「COUNTA関数」を使って「商品名」の件数を確認してみます。

=COUNTA(A4:A121)　　※範囲：セルA4～A121(総計の上のセル)まで

「118」と結果が返ってきましたので、「A4」から「A121」までのセルにデータが入っているセルの件数から「商品コード(CD)」の件数を引きます。

59(118－59＝59)

「商品コード(CD)」と「商品名」の件数は等しく、「商品名」の抜けもないことから、「商品コード(CD)」と「商品名」は1対1で対応していることが確認できました。

③-5 商品CDと商品名、単価の対応の確認

一つの商品に対して「単価」が一つであった場合、「商品コード(CD)」と「商品名」、「単価」がすべて1対1で対応することになります。
「商品コード(CD)」と「商品名」に関しては、単価の対応付けも確認する必要があります。
ただし、「単価」に関しては、違う「商品」で同一価格という場合もあるので、件数だけで判別することができません。この場合、ブランクのセルがあるのか、ないのかと、適正な数値であるかを確認することによって判定することになります。

ピボットテーブルを使って、次のような操作を行います。

• 「販売単価」を「行ラベル」ボックスにドラッグ

行ラベルの列に「単価」が表示されます。
「単価」の最小値は「230」、最大値は「189,700」となり、特に異常値は見られません。
また、ブランクのセルも見られないので、「単価」は正常です。ただし、件数を調べたところ「51」だったので、「商品」の件数が「59」であることを考えると、同じ単価の商品があることが分かります。

④ 得意先CD・得意先名の確認

得意先コード(CD)」と「得意先名」が、1対1で対応しているかをピボットテーブル使って確認します。

空白セルはなく、Excel関数による計算も「34」となり、1対1で対応しています。

⑤ 担当者の確認

「担当者」の抜けと間違いがないかを、ピボットテーブルを使ってチェックします。

• 「行ラベル」ボックスに「担当者」をドラッグ

結果は、ブランクのセルも無く、正しい名前もあるので、問題はありません。

⑥ 数量の確認

「数量」についても、ピボットテーブルを使って間違いがないかをチェックします。

• 「行ラベル」ボックスに「数量」をドラック

1回に注文できる数量は「1」～「4」となっていたのですが、「10」～「40」の間違ったデータがありました。

⑦ エラーデータの抽出と修正

Excelのフィルターを使って、元データから間違った伝票を抽出して修正を行います。

• 「数量」の項目（列）にフィルターを設定
• 間違ったデータ「10」～「40」を表示

単位を間違えて「0」を付けていたことが原因だったので、「0」を取ります。