実演！Excelを使った身近なデータ分析～2.実践①データ基礎俯瞰１

こんにちは、阿久津恵子です。
「ユーザ視点、データ起点」としてExcelを使った身近なデータ分析を、ここで紹介いたします。

具体的には、企業にある身近なデータの１つ顧客管理データを使って、ＣＲＭ(顧客関係管理)のためのデータ分析をします。

2017年JSDG東京研修会でご紹介した内容を５回にわたって説明いたします。この連載の概要はご紹介ページをご覧くださいませ。

２．実践①データ基礎俯瞰１

今回からExcelを使ってデータ分析を実践していきます。
データ分析で最初に行う大切なことは、そのデータ全体を理解するために、基礎俯瞰を実施することです。

今回用いるデータは英国を拠点とする非店舗のオンライン小売業者のすべての取引を含む多国籍データセットを利用します。（期間：2010年12月1日～2011年12月11日）

UCI machine learning repositoryで公開されていて、以下のURLからダウンロードする事ができます。https://archive.ics.uci.edu/ml/datasets/Online+Retail#

ダウンロードしたデータをみると項目は
・InvoiceNo　請求番号
・StockCode　商品番号
・Description　商品詳細
・Quantity　個数
・InvoiceDate　請求日
・UnitPrice　商品単価
・CustomerID　顧客番号
・Country　取引国
となっています。

この取引の状況を把握するために、最初に全期間の購買金額および購買人数の推移を確認してみたいと思います。

まず、データをExcelで開き以下を実施していきます。

I列に「Total Price(請求金額)」を掲載するため、1行目に項目名を記載し
・I列2行目に式「=F2*D2」を入力し、式を全行へコピー
→F列：商品単価、D列：個数であるため、両方のセルをかけることで請求金額が算出されます。

J列に「InvoiceMonth(請求年月)」を掲載するため、1行目に項目名を記載し
・J列2行目に式「=CONCATENATE(YEAR(E2),”/”,MONTH(E2))」を入力し、式を全行へコピー
を実施します。

※Excel関数の説明
CONCATENATE：文字列を結合する関数
YEAR：日付から年を取り出す関数
MONTH：日付から月を取り出す関数
→E列が請求日のため、この式を記載することで、請求年月を取り出すことができます。
このデータを用いて、請求年月毎の集計を行うことができるのです。

なお、行が多いと式のコピーが大変ですが、セルを選択したときにセルの右下の■（下図）にカーソルを当て、カーソルの形が「＋」になった状態でダブルクリックすると、データがあるところまで式が一挙にコピーされます。
コピー後は以下の様になります。

続いて、ピボットテーブルを作成するため、これらの全データを選択します。
選択するときはマウスを使ってスクロールする方法もありますが、データが多いと大変なので、以下の手順をおすすめします。
まずA1セルを選択した後に「Shift」＋「Ctrl」を押した状態で「↓」を押すとA列全体が選択されます。続いて、「Shift」＋「Ctrl」を押した状態で「→」を押すとデータがあるJ列まで選択されます。

全データを選択した状態で、「挿入」メニューから「ピボットテーブル」を選択すると「ピボットテーブルの作成」画面が出てきますので「OK」を押します。

新しいシートが作成されますので、以下のようにピボットテーブルの設定を行います。
・フィールド欄から「列」に「InvoiceMonth」をドラッグ＆ドロップ、「行」に「CustomerID」をドラッグ＆ドロップ、「値」に「TotalPrice」をドラッグ＆ドロップします。

すると、CustomerID毎に月別の購入金額が集計された表が作成されます。
表の最下部には月別の合計金額も計算されていますが、ピボットテーブルのままだと扱いづらいので、A4～N4376までを選択しコピーし、ピボットテーブルの横Q4セルを選択し貼り付けます。