PowerPoint プレゼンテーション

Transcription

1 総務省 ICTスキル総合習得教材 概要版 eラーニング用 [ コース3] データ分析 3-2: データのクレンジングと可視化 [ コース1] データ収集 [ コース2] データ蓄積 [ コース3] データ分析 [ コース4] データ利活用

2 実習紹介本講座の学習内容 (3-2: データのクレンジングと可視化 ) 講座概要 Microsoft Excel のステータスバーを利用した異常値の確認方法を紹介します Excel の IF 関数を使った欠損値 異常値の除去方法や PHONETIC 関数 TRIM 関数を使った表記揺れの統一方法を説明します データの可視化 ( 見える化 ) の概念を紹介し 可視化の方法による印象の相異を示します 講座構成 [1] Excelにおける異常値の確認 [2] Excel 関数におけるデータクレンジング [3] データの可視化 ( 見える化 ) 学習のゴール Excel のステータスバーを活用した異常値 欠損値の確認ができる Excel の IF 関数を利用して 欠損値 異常値を除去する方法を理解する Excel の PHONETIC 関数 TRIM 関数を使った表記揺れの統一方法を把握する 可視化の概念を理解し 可視化の方法による印象の相異を把握する

3 [1] Excelにおける異常値の確認 Excelにおける異常値の確認とデータクレンジング Microsoft Excel を利用した異常値の確認および Excel 関数を利用したデータクレンジングを説明します データクレンジング ( データの前処理 ) とは 収集したデータを利用しやすい形に整えたり 分析の障害となる異常値 重複データ等を取り除き 分析しやすい状態にすることです データ分析の専門家であっても データクレンジングにかかる時間が本格的な分析作業以上の時間となることがたびたびあります このため データクレンジングを効率的に行うことは重要です このパートでは 既に構造化データ (Excel 形式 ) になっているデータのクレンジングを行うケースを想定しています 半構造化データ 非構造化データを構造化データへ変換するデータクレンジングもありますが 入門者向けの内容を越えることに加え Microsoft Excel 内では実行できないため 割愛します このパートでは 広く普及している Excel を用いて プログラミング等の知識なく行えるクレンジングを紹介します Excel においても プログラミングが可能な VBA(Visual Basic for Applications) を利用できますが 本パートでは VBA の紹介はしません Excel におけるプログラミング不要のデータクレンジングは 比較的簡単に実施できるため 分析初心者や分析の補助者でも可能です Excel におけるプログラム不要のデータクレンジングの短所として 作業プロセスとなるプログラムコードや作業記録となるログが自動で残らないことが挙げられます 作業手順を Excel の別シート等に記録するとともに 元のデータを削除せず保存しておきましょう データクレンジングの記録を残しておくことで 再度同じクレンジングを行う場合 クレンジングの方法を変える場合に参照することができます データ分析においては 他の人が行っても同じ分析結果を導出できる 再現可能性 が重要です 自分自身が理解するのみならず 他の人に説明できるように 他の人でも同じデータクレンジングが行えるように記録することが重要です 講座 4-3で紹介するような統計解析向けのプログラミング言語のRでは プログラムコードやログを残すことができます

4 通し番号の挿入 [1] Excel における異常値の確認 Excel 形式のデータの整理 クレンジングする際には 通し番号を作っておくのが便利です 右表のような 文房具店の売上を示す構造化データを Excel でクレンジングするケースを考えます Excelでは水平線側を 行 垂直線側を 列 と呼びます リレーショナルデータベースと呼ばれる構造化データの格納に適したデータベースにおいても水平線を 行 垂直線を 列 と言います 漢数字の 十 の書き順で 行 列 と覚えて下さい この表では 水平線の行には個別の売上情報が記入され 垂直線の列にはその単価や数量の属性情報が記入されています 水平線側に個別の対象 垂直線側にそれぞれの属性情報が記入されるのは リレーショナルデータベースとしても一般的です 元のデータに通し番号がない場合は 一番左に通し番号の列を作っておきましょう データセットの左端に空白の列を作り 1 行目に 1 2 行目に 2 を入力して 入力した二つのセルを選択した状態で 2 の右下の黒い四角をダブルクリックすると 最終行まで通し番号がつきます 1 のセルだけを選択した状態で 右下の黒い四角をダブルクリックすると 最終行まで 1 が並びます 通し番号は 行の ID としても利用でき データの全レコード ( 行 ) 数を確認する場合にも ソート ( 並び替え ) を元に戻す場合においても 便利です 行 列 通し番号 日付 商品 単価 数量 1 7 月 1 日 ボールペン黒 月 1 日 ボールペンRED 月 1 日 ホ ールヘ ンred 月 1 日 ノート 月 1 日 はさみ *** *** *** *** 7 7 月 2 日 鉛筆 月 2 日 ハサミ 月 2 日 ノート 100 N/A 10 7 月 2 日 エンピツ Excel における通し番号のつけ方 ダブルクリック

5 ステータスバーによる異常値のチェック Excel で異常値の確認をする際は ステータスバーによる確認が便利です Excel 画面下側の表示倍率を表す枠の部分を ステータスバー といい ステータスバーを右クリックすることで その表示内容を選択できます Excel 画面のステータスバーを右クリックし 表示項目の 平均 データの個数 数値の個数 最大値 最小値 合計 にチェックを入れます Excel の列頭のアルファベットを左クリックすることで 列全体を選択してからステータスバーを確認します [1] Excel における異常値の確認 異常値のチェックに利用するのは 主に データの個数 数値の個数 最大値 最小値 ですが 平均 合計 の表示もデータの確認に便利です 例示の表のように 1 行目に 単価 数量 などの変数名が入っている場合は データの個数 よりも 数値の個数 が 1 小さくなりますが それ以上の差があれば 数値が入るべき列に文字入力があることに気がつけます 利用できないデータを欠損値や欠測値と言います 欠損値は N/A と記入されたり 代わりの値として が記入される場合があります ステータスバーの最大値や最小値が現実的な値になっているかを確認することで 簡潔な異常値のチェックができます ステータスバーの表示内容を選択 左クリック 列全体を選択して異常値を確認 D 列 [ 単価 ] 選択時のステータスバー 左クリック データの個数 よりも 数値の個数 が 2 小さく 変数名以外に文字入力が 1 つあることに気がつく 最小値 最大値は現実的な値で問題はなし E 列 [ 数量 ] 選択時のステータスバー 右クリック データの個数 よりも 数値の個数 が 3 小さく 変数名以外に文字入力が 2 つあることに気がつく 最小値 最大値は非現実的な値で 異常値がある

6 IF 関数による欠損値 異常値の置き換え [2] Excel 関数によるデータクレンジング Excel の IF 関数を使うと欠損値 異常値を他の文字に置き換えて数値を抽出できます Excel の IF 関数は =IF( 条件式, 条件を満たす場合の出力, 条件を満たさない場合の出力 ) とコンマで区分して入力することで 条件式で場合分けした出力ができます 数値データを抽出し 数値でなければピリオドを出力する場合 数値データの抽出 セルに = IF(D2<10000,D2,. ) と入力すれば 対象セルの D2 が考えられる上限の より小さい数値であれば D2 の値をそのまま出力し 文字を含めそれ以外なら半角のピリオド. を出力でき 数値データを抽出できます Excel の条件式において 記号やスペースを含む全ての文字 はあらゆる数値より大きい値 ( 無限大 ) として扱われます このため 文字入力の可能性がある列においては 考えられる下限の 0 より大きいかの条件とする = IF(D2>0,D2,. ) ではなく 考えられる上限値 より小さいかを条件とする = IF(D2<10000,D2,. ) としてください 数値データかどうかを識別する ISNUMBER という関数もあります この関数を IF 関数の条件式に入れて =IF(ISNUMBER(D2),D2,. ) とすることで 数値データを抜き出すこともできます -1 や 9999 といった異常値も除いて 0 以上 100 以下のみ数値を出力する場合 0 以上 100 以下の数値の抽出 セルに =IF(AND(E2>=0,E2<=100),E2,. ) と入力すれば 対象セルの E2 が 0 以上 100 以下なら E2 の値をそのまま出力し そうでなければ半角のピリオド. を出力することができ 数値データを抽出できます 条件式の中に入っている AND は 両方満たす場合の かつ を表す Excel 関数で コンマで区切ることで複数の条件を与えることができます どちらかを満たす場合の または を表す OR という関数もあります Excel の条件式では より大きい (>) より小さい (<) の記号の後ろにイコール (=) を入れることで 以上 (>=) 以下 (<=) となります

7 [2] Excel 関数によるデータクレンジング Excel 関数による表記揺れの統一 PHONETIC 関数では読み仮名を抽出し TRIM 関数では文字前後の空白を削除します 文字データに表記揺れや空白があると データをとりまとめられないため 表記を統一する必要があります PHONETIC( フォネティック ) 関数 : 文字列の読み仮名をカタカナで出力する セルに =PHONETIC(C2) と入力すれば 対象セルの C2 のフリガナをカタカナで出力します カタカナでの表記揺れの統一 PHONETIC 関数は読み仮名をつける関数ですが はさみ と ハサミ の表記揺れの統一にも利用できます TRIM( トリム ) 関数 : 文字の始めと終わりの空白を削除して出力する 空白除去による表記揺れの統一 セルに =TRIM(C2) と入力すれば 対象セルの文字の前後の空白を除去します 文字の前後に空白が入り 表記揺れになってしまう場合の表記統一に利用できます TRIM 関数は単語内で複数の空白が続く場合は 一つの空白にまとめます ( 例 ) ノート ノート Excel の 検索と置換 から置換機能を使うことでも 表記揺れの統一作業は可能ですが Excel 関数を使う場合に比べても 作業手順や置換した内容が分かりにくく 作業内容の確認や作業の再現がしにくくなります Excel の置換機能を使う場合は 置換した文字のリスト 指定範囲を記録するようにしましょう 異常値の除去 表記揺れの統一といったデータクレンジングが終われば 可視化をはじめとする分析が行えます

8 データの可視化 ( 見える化 ) [3] データの可視化 ( 見える化 ) データの可視化 ( 見える化 ) は図表を使って データを分かりやすく表すことを指しています 大きい / 小さい 暑い / 寒い といった定性情報を定量データで表すことが可視化の第一歩です 平均値などを表で表すことも可視化の一つと言えますが 棒グラフの長さといった視覚情報の方が 人間にとって理解しやすく 印象に残ります 可視化において 図表の作成者が強調したい点を図表に入れ込むことができます 営業資料などの作成者の主観的な強調が許容されるケースもあれば 学術資料などの客観的 画一的な表示が望ましいケースもあります 下記の 3 種の図表が持っている情報量は同じですが 情報の分かりやすさ 印象が異なります 表による可視化客観的 画一的な図での可視化尺度を変更し 強調表示をした図 曜日 平均気温 ( ) 平均気温 ( ) 平均気温 ( ) 月曜日 31 火曜日 32 月曜日火曜日 月曜日火曜日 水曜日 34 木曜日 37 水曜日木曜日 水曜日木曜日 金曜日 35 金曜日 35 金曜日 35 土曜日 33 土曜日 33 土曜日 33 日曜日 31 日曜日 31 日曜日 31 データの可視化は分析の第一歩であるとともに 分析結果を分かりやすく表示するためにも重要です