Aさん Excelで集めたデータに不備が多く、直すのにけっこう手間がかかっています。
それに、独特な表レイアウトなので集計するのが大変です。。
何か良いExcel機能はありますか？
森田 Excelで元データを不備がなく、使いやすくするのはなかなか大変ですね。
ちなみに、こうした作業を「データ整形」と言いますが、この作業に役立つ主要な機能をまとめましたので、順番に解説していきますね。
解説動画：【大前提】Excelを実務で活用するために理解必須な5つの作業プロセス
この記事の内容は下記の動画でも解説しています。
コメント欄の各プロセスの時間部分をクリックすると該当の解説へジャンプできますよ！
はじめに
本題に入る前に、この記事がおすすめな方を挙げてみます。
- Excelでのデータ整形に関する作業を効率化したい方
- Excelのデータ整形に役立つExcel機能を知りたい方
- Excelのデータ整形を実務で行う機会が多い方
前提条件
この記事を読む前に、以下の記事でExcelの作業プロセスの全体像を把握することをおすすめします。
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 Excelの困りごとに対し、ネット検索で調べ …
Excelで行うデータ整形とは
「データ整形」とは、集めた元データを集計できる状態に整えることです。
Excelの作業プロセスを5つのプロセスに分けたもののうち、2つ目のプロセスが該当します。
なぜ、このプロセスが必要かと言うと、いざ集計/分析を行う際に、元データに抜け漏れや誤りがあった場合、誤った集計/分析結果となり、ビジネス的な判断誤りを生むリスクがあるためです。
また、不備がなくとも、使いにくい元データの場合、集計/分析に必要以上の工数がかかってしまい、肝心の分析に手が回らずに時間切れとなるリスクもあります。
よって、集計/分析を行う前に集めた元データを不備がなく、かつ使いやすい1つの表にまとめることが重要です。
このデータ整形は大別すると以下の4つに分類できます。
- 集めた元データを綺麗にすること（データクレンジング）
- 集めた元データを一つの表にまとめること（データ結合(転記/追加)）
- 集めた元データを使いやすい表レイアウトにすること（レイアウト変更）
- 集めた元データを使いやすくするための列を追加すること（列の追加/計算）
では、それぞれを順番に解説していきましょう。
「データクレンジング」の運用上のポイントと主要なExcel機能とは
集めた元データに不備があった場合、その不備を解消する必要があります。
つまり、元データを「不備がなく綺麗な状態」にするということです。
こうした作業を「データクレンジング」と言います。
運用上のポイント
「データクレンジング」と一口に言っても、実は様々な作業の集合体です。
代表的な作業は以下の6つです。
- 不要な行/列データの削除
- 入力漏れ/誤入力の修正
- 表記ゆれの修正
- 重複データの削除
- データ型の変更
- 列データの粒度の変更（分割/マージ）
それぞれの作業の詳細は以下の通りです。
「不要な行/列データの削除」とは
ここで言う「不要な行/列データ」とは、集計/分析に使わない行/列データのことです。
特に、元データのデータ量が多い場合、不要なデータが入っていることでExcelが重くなり、大幅に処理効率が悪化してしまいます。
よって、データクレンジングの最初の作業として、不要な行/列データを削除することから始めましょう。
「入力漏れ/誤入力の修正」とは
どちらも文字通りの意味ですが、「入力漏れ」は本来入力すべきデータが入っていないこと、「誤入力」は誤ったデータにしてしまうことを指します。
どちらも入力者側のヒューマンエラーが原因であることが多いですが、入れるべきセルに正しいデータが入った状態に修正しましょう。
入力漏れは検知しやすいですが、自動化が難しい場合が多いです。
他のデータを見て何を入力すれば判断できれば良いですが、判断できない場合は入力者に聞くか、情報源となる紙やデータを見るしかありません。
ちなみに、元データの結合セルを解除し、空白セルになった部分への入力は自動化できます。
もう一方の誤入力は検知自体がしにくく、こちらも自動化は難しいです。
数式の列に文字が誤入力されている等であれば、修正は容易ですが、そうでない場合は地道に修正するしかありません。
いずれにしても、本来は入力時にチェックする、あるいは入力時に制御しておく方が無難です。
「表記ゆれの修正」とは
「表記ゆれ」とは、実質同じ意味のデータですが、別な表記になっていることを意味します。
たとえば、以下のようなものが表記ゆれです。
| 表記ゆれの種類 | 例 |
|---|---|
| 英数カナの半角/全角 | 「apple」と「ａｐｐｌｅ」等 |
| 英字の大文字/小文字 | 「APPLE」と「apple」等 |
| 記号の有無（スペースや改行コード等） | 「森田貢士」と「森田 貢士」等 |
| 同義語 | 「林檎」と「りんご」、「apple」等 |
この表記ゆれは、人間目線であればパッと見で同じデータだと推測できますが、PC（Excel）目線では、まったくの別データ扱いとなってしまうことがやっかいです。
よって、同じ意味のデータは表記を統一しておきましょう。
なお、正しいデータの一覧（マスタ）があれば、表記ゆれの検知および修正は容易です。
自動化しやすい作業の一つだと言えるでしょう。
「重複データの削除」とは
「重複データ」とは、文字通りデータが重複している状態を指します。
特に多いのは、レコード（行）が重複しているケースが多いです。
こちらも不要なデータとなるため、集計/分析の前に削除しましょう。
なお、完全に重複しているレコードは検知しやすいですが、部分的に重複しているレコードの検知は難しいです。
「データ型の変更」とは
「データ型」とは、データの種類だと思ってください。
通常は、データの値に応じたデータ型になっているものですが、元データによっては値とデータ型に矛盾が生じている場合（数値のデータなのに、データ型が「文字列」等）があります。
Excelの各種機能は、対象のデータ型を限定しているものがあり、それらの活用時にデータ型が不一致だとエラー等の原因となってしまいます。（文字列をSUMで集計する等）
だいたい、後続のデータ結合（転記/連結）や列の追加/計算を行う際にエラーで気づくことが多いです。
よって、値とデータ型が矛盾している場合、予めデータ型を正しいものへ変更しておきましょう。
「列データの粒度の変更（分割/マージ）」とは
「列データの粒度の変更」とは、元データの列データを集計/分析の目的に合ったデータのサイズに変更することです。
たとえば、元データが「部署」の1列しかなく、「本部」、「部」単位で集計したい場合は、「部署」の列を分割して「本部」と「部」の列に変更するといったイメージです。（その逆がマージ）
主要なExcel機能
Excelでのデータクレンジングで役立つ機能は作業別に以下の通りです。
- 不要な行/列データの削除：フィルター、パワークエリ、マクロ（VBA）
- 入力漏れ/誤入力の修正：フィルター、ジャンプ、パワークエリ、マクロ（VBA）
- 表記ゆれの修正：置換、関数（ASC/JIS、UPPER/LOWER、TRIM、CLEAN、SUBSTITUTE等）、パワークエリ、マクロ（VBA）
- 重複データの削除：条件付き書式、関数（COUNTIFS等）、パワークエリ、マクロ（VBA）
- データ型の変更：エラーチェック、関数（TYPE、VALUE、DATAVALUE、TIMEVALUE、TEXT等）、パワークエリ、マクロ（VBA）
- 列データの粒度の変更（分割/マージ）：置換、区切り位置、フラッシュフィル、関数（LEFT、RIGHT、MID、FIND、LEN等）、パワークエリ、マクロ（VBA）
おすすめは関数、もしくはパワークエリです。
特に、パワークエリは複数のデータクレンジング作業を記録させることで、大部分を自動化することが可能です。
その他、「データクレンジング」に関する個別テクニックについては、以下カテゴリーへ順次記事を更新していきます。
必要に応じてご参照ください。
データクレンジングに関するテクニックの解説コンテンツです。
「データ結合(転記/追加)」の運用上のポイントと主要なExcel機能とは
集めた元データが複数ある場合、最終的に1つの表に集約しないといけません。
こうした作業を「データ結合」と言います。
運用上のポイント
「データ結合」を分解すると、以下の3種類が代表的な作業です。
- データ転記
- データ追加（連結）
- リレーションシップ
それぞれの作業の詳細は以下の通りです。
「データ転記」とは
「データ転記」とは、元データのメインの表へ別表の列データを転記することです。
データ転記の基準は「主キー」です。
1行が一意（重複していないこと）であることを示す番号のこと。
列データが増えることで、集計/分析の切り口（軸）が増えますので、より多角的な集計/分析を行うことが可能になります。
「データ追加（連結）」とは
「データ追加」とは、元データのメインの表へ別表の行データ（レコード）を追加することです。
データ追加の基準は列の見出し名です。
実務では期間や部署等でExcelブックを分けて管理することもあるため、横断的に集計/分析したい場合に必要な処理です。
「リレーションシップ」とは
「リレーションシップ」とは、元データの複数の表を連携させることです。
リレーションシップの基準は「主キー」です。
主に、ワークシート上で表を1つに集約するのではなく、データモデル上で仮想的に一元集約する際に用います。
データモデルとは、Excelブック内の新しい格納先のこと。
主要なExcel機能
Excelでのデータ結合で役立つ機能は作業別に以下の通りです。
- データ転記：関数（VLOOKUP、INDEX、MATCH等）、パワークエリ、マクロ（VBA）
- データ追加（連結）：パワークエリ、マクロ（VBA）
- リレーションシップ：リレーションシップ、パワーピボット、マクロ（VBA）
ワークシート上でのデータ転記や追加ならパワークエリがおすすめです。
その他、「データ結合(転記/追加)」に関する個別テクニックについては、以下カテゴリーへ順次記事を更新していきます。
必要に応じてご参照ください。
データ結合(転記/追加)に関するテクニックの解説コンテンツです。
「レイアウト変更」の運用上のポイントと主要なExcel機能とは
集めた元データの表レイアウトが使いにくい場合、集計/分析がしやすいレイアウトへ変更しておく必要があります。
運用上のポイント
そもそも、集計/分析しやすい表レイアウトとは、テーブル形式の表のことです。
テーブル形式の表とは、以下の3要素を満たした表のことを指します。
- 見出しが1行
- 1行1データ
- 1列同一種類データ
ちなみに、元データの表レイアウトがテーブル形式以外のケースはいろいろありますが、一例としては以下のようなものが挙げられます。
これらを最終的にテーブル形式の表レイアウトに変更すると使いやすくなります。
主要なExcel機能
Excelでのレイアウト変更で役立つ機能は以下の通りです。
- 関数（VLOOKUP、INDEX、MATCH等）
- パワークエリ
- マクロ（VBA）
特に、パワークエリはレイアウト変更に便利な機能（列のピボット解除等）があるのでおすすめです。
その他、「レイアウト変更」に関する個別テクニックについては、以下カテゴリーへ順次記事を更新していきます。
必要に応じてご参照ください。
レイアウト変更に関するテクニックの解説コンテンツです。
「列の追加/計算」の運用上のポイントと主要なExcel機能とは
集計/分析の切り口を広げる、あるいは集計/分析の難易度を下げるために、元データへ新たな列を追加しておくと効果的です。
運用上のポイント
追加する列は、なるべく既存の列のデータを元に自動で計算させて工数を増やさないことがポイントです。
なお、主に追加する列で行う作業は以下の4種類です。
- 数値の計算
- 日付/時刻の計算
- 条件判定
- カテゴリ/ラベル付け
それぞれの詳細は以下の通りです。
「数値の計算」とは
「数値の計算」とは、既存の数値の列データを元に、新たな列で計算結果を示すことです。
代表的なものは四則演算や端数処理です。
元データ側で予めこうした計算をしておくことで、集計/分析の難易度が下がります。
「日付/時刻の計算」とは
「日付/時刻の計算」とは、既存の日付/時刻の列データを元に、新たな列で計算結果を示すことです。
日付から「年」や「月」の取得、複数の列で期間を計算する等が代表的です。
元データ側で予めこうした計算をしておくことで、集計/分析の難易度が下がります。
「条件判定」とは
「条件判定」とは、既存の列データを元に、新たな列で条件に応じた判定結果を示すことです。
目標の達成/未達やテストの合否、特定のキーワードを含むか否か等、条件判定しておくことで集計/分析の切り口（軸）が広がります。
「カテゴリ/ラベル付け」とは
「カテゴリ/ラベル付け」とは、既存の列データ（定性）を元に、新たな列で集計/分析用で分類するための目印となる文字列を付加することです。
特に、アンケートのフリーコメント欄をポジ/ネガ等で端的に分類したい場合等に必要な作業です。
なお、この作業は基本的に目検で行う必要があり、Excelでの自動化はなかなか困難です。
（部分的に条件判定と同じ処理ができるくらい）
自動化するにはExcelとは別にテキストマイニングツールを併用する必要があるでしょう。
主要なExcel機能
Excelでの列の追加/計算で役立つ機能は作業別に以下の通りです。
- 数値の計算：数式、関数（SUM、INT、ROUND等）、パワークエリ、マクロ（VBA）
- 日付の計算：関数（DATA、YEAR、MONTH、DAY、WORKDAY、NETWORKDAYS 等）、パワークエリ、マクロ（VBA）
- 条件判定、カテゴリ/ラベル付け：関数（IF、AND、OR等）、パワークエリ、マクロ（VBA）
おすすめは関数、もしくはパワークエリです。
なお、営業日カウントが必要な日付の計算が発生する際は、パワークエリでは行うのは難しいため、関数（WORKDAY、NETWORKDAYS等）を用いると良いですね。
その他、「列の追加/計算」に関する個別テクニックについては、以下カテゴリーへ順次記事を更新していきます。
必要に応じてご参照ください。
列の追加/計算に関するテクニックの解説コンテンツです。
さいごに
いかがでしたでしょうか？
データ整形は、地味で大幅な時間と労力がかかりますが、正しく楽に集計/分析を行うために必要不可欠なプロセスです。
この記事で役立ちそうな機能が見つかれば、ぜひその機能を詳しく調べ、実務で試してみてくださいね。
なお、データ整形に役立つ機能は、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 今回のデータ整形とその前のプロセスのデータ収集をセットで「前処理」と言いますが、データを扱う処理の約8割は前処理が占めるそうです。
つまり、いかにExcelの機能を用いて自動化し、この前処理にかかる工数を圧縮できるかが実務で成果を上げるための大きなポイントになると言えますね。
