Excel活用法(1) Excelデータの前処理 - Excelでデータを把握、整理

データの視覚化や分析のために Tableau、Power BI、HEARTCOUNT など多くのツールが生まれましたが、MS Excelを利用する方が非常に多いです。最も馴染みのあるツールでもあり、容易に様々な視覚化ができるので、依然として活用度が高いためです。

そこで今回は3回に渡ってExcelを使ってデータを前処理し、分析、報告にも活用する手順を作成する予定です。(回数は作成途中で変更する可能性があります:D)

最初の「データを整理して把握する」というテーマで今回のブログを続けます。

データの全体的な構造を把握し、どのような変数で構成されているのか、どのような行で構成されているのかを把握して初めて、効果的な分析テーマと方法とを選定することができます。この順序が変ってしまうと、分析テーマは決めたものの、その分析に必要な変数がないため、再度分析テーマを決めなければならないという不幸に見舞われるケースもあります。

Excelでは、集計関数を活用したりピボットテーブルや図表を利用することでデータを把握し、分析を行うことが多いです。

そのステップに進むためには、まずデータを整理する手順を経なければなりません。よく整理されたデータである場合、Excelはデータを正しく読み取り、エラー無しで正確な値を表示することができます。

それでは、まずExcelデータを整理する手順を見てみましょう。

Excelでデータを整理する

1.シートの構成を整理する

Excelでデータを受領する場合、すぐに使いやすい形であればよいのですが、そうではない場合が多いです。

例えば、下の画像のように各列のヘッダーが列ごとに1つに決まっていなかったり、最初の行は列ヘッダーとなるはずにもかかわらず、上側のスペースが不必要に空白となっている場合があります。

このような場合、セルの結合を解除して、列を明確に識別する列ヘッダーのみを残し、不要な空白行を削除します。

下の動画で簡単に理解されると思います!

2.セルの書式設定

今回のブログで使用するデータセットは各列について、数値は数値として、日付は日付として正しく指定されています。

Excelをお使いの方であれば、セルの左上に緑色の三角形が表示される場合があるのを経験されたことがあると思いますが、それが表示される場合は数値であるにもかかわらず文字列として指定されているような可能性があるので、書式を確認してみることをおすすめします。下の動画のように上部の四角形のリストで確認することができます。

書式を変更しなければならない場合、以下の3つの方法を使うことが可能です。

①対象の列を選択 - 上の動画で赤枠で表示されている四角形のメニューをクリックして希望の書式を選択します。

②対象の列を選択 - マウスを右クリック - [セルの書式設定] メニューから希望の書式を選択します。

③対象の列を選択 - 上部の [データ] メニューから [区切り位置] 機能を使用します。

3.フィルターをかけて整理する

次にフィルターをかけて、より見やすくテーブルを整理してみましょう。

フィルターは上部の [データ] メニューにあります。[フィルター] をクリックすると、すべての列ヘッダーの横に下向きの三角形が表示されます。

この三角形をクリックすると、並べ替え基準を指定するかどうかを選択することができます。フィルターで表示したい基準を決めることもでき、その列を構成している値のリストも一目で確認することができます。

ここまで正しく利用するためにExcelデータを整理する手順について説明しました。

それでは、データを把握してみましょう。

Excelでデータを把握する

1.クリックとドラッグで個数、合計、平均を把握する

簡単な方法ですが、把握することができる内容は限られます。

まず、行数を把握してデータのサイズを把握します。最も基準となりそうな(空白ではないと思われる)列の任意のセルを選択して Ctrlキー (コントロールキー) + 方向キー ⬇️ を押します。その列の最後の行(空白ではないセル)に移動します。このショートカットキーで空白のセルを見つけることができるので、覚えておいてください。

列全体を選択すると、下部にその列のデータ全体の平均、個数、合計が表示されます。テキスト列の場合は、個数のみ確認することができます。

2.ピボットテーブルを使用する

Excelでデータを活用する際に欠かせない機能が「ピボットテーブル」です。

下の画像のように上部の [挿入] メニューの左側にある [ピボットテーブル] をクリックして作成することができます。ピボットテーブルを作成する場合、行、列、値(計算)を選択する必要がありますが、その指定が難しい場合は、Excelが推奨する [おすすめピボットテーブル] メニューを利用するのもよいかもしれません。

下の画像は、[おすすめピボットテーブル] を選択した結果です。

通常の [ピボットテーブル] メニューをクリックすると、下の画像のようなウィンドウが表示されます。基本的に [テーブル/範囲] には値が入力されている範囲全体が選択され、必要に応じて修正も可能です。今回は分かりやすく、新しいワークシートにピボットテーブルが表示されるように配置する場所を選択しました。

右側に表示される [ピボットテーブルのフィールド] を使用して、列、行、値を指定することができます。

フィールド名ボックスにある変数名を目的の領域のボックスへドラッグする方法を用います。気になる内容に変更して確認することができます。

下のボックスへドラッグされたそれぞれの変数名の横には i が表示されます。i をクリックすることで使用する関数とデータの表示形式を指定することができます。

今回のテーマは全体としてデータをざっくり見る手順です。その先の分析のためのピボットテーブルの使い方については次回で説明します。

ここまでで、Excelでデータを整理して、データを全体的に把握する方法をみてきました。

HEARTCOUNTでより簡単にデータを整理して把握する方法

Excelでデータを把握するのも簡単ですが、これからより簡単で迅速にデータを把握する方法もあわせて紹介します!

データの視覚的な分析が無料でできる HEARTCOUNT を活用した方法です。

まず、上記の「データを整理する」のステップ1まで完了したExcelファイルを準備します。

そのファイルをドラッグ・アンド・ドロップすると、データセットを表形式で表示することができ、変数別にカード形式で整理されます。

*[概要] の情報をもとに、以下のカードを見てみましょう。

このデータセットでは、空白の列が1つあり、数値型とカテゴリ型のいずれかを選択する必要のある列が2つありそうです。

*[概要] の情報では、データセットでユーザが追加で指定する必要がある変数を提示してくれたり、意味のない変数(空であったり、値が同一で分析には不要な列)を自動的に把握して削除してくれたりします。

最初にカードを確認します。

それぞれの変数を構成している値を一目で見ることができ、データのおおよその分布も視覚的に把握することができます。

また、データの形式に応じて自動的に日付値型データと数値型データ、カテゴリ値型データに区分されます。この区分は変数の左側に小さなアイコンでそれぞれ、カレンダー、#、Abcで表示されます。

ここで特記すべき点は、数値型変数についてもHEARTCOUNTで区間が指定された派生変数が作成され、その派生変数がカテゴリ値型データとして表示されることです。この機能により、より多彩な分析が可能になります。

[概要] の情報で現れた i がその変数名の横に表示された、2つの変数(数量、割引率)を確認することができます。この2つの変数の i は、数値型かカテゴリ値型かのいずれかに指定すべきことを意味しています。2つの変数ともランクのような区分のための数字ではないので、数値型変数とします。

さらに、上部の▲を選択することでそのサマリの数値を確認することができます。

使いやすいデータ活用方法やツールがすでに決まっている人もいるでしょうし、まだ適切な方法やツールを見つけられない人もいるでしょう。

本日は、国内で最も多くの企業で使用されているExcelのデータ活用の最初のステップについて紹介しました。

同時に、同様の作業を別のツール(HEARTCOUNT)で行う方法についてもあわせて紹介しました。

それぞれ長所と短所があるので、ご自身に合った方法を選択してください。

分析を始める前の重要なステップである「データの把握」に役立つことを願って、今回のブログを終ります。