[EXCEL] 集計前にデータを整える（データクレンジング）　公務員に必要なエクセルの知識

【まとめ】
・集計の前にデータの汚れを落とす（データクレンジング）
・主な汚れ
①表記ブレ（例：ケ・ヶ　川・河）
⓶空白（半角・全角）
③セル内改行
・クレンジング方法
「置換」（Alt⇒E⇒E）を行う。
　＊セル内改行は Ctrl＋J

【説明】
データが正しくないと正しい集計が出来ません。
数値の誤りは勿論ですが、ありがちなのが文字データ（文字列）の「表記ブレ（表記のゆれ）」です。
例えば、下の「あ」と「い」は一見同じに見えます。

しかし、データとしては異なっています。
＝「あ」のセル＝「い」のセル　が「FALSE（偽）」なら「違う」ということです。
同じなら「TRUE（真）」になります。

何が違うでしょうか？
1つ目は「ヶ」と「ケ」。よく見れば分かります。
２つ目、３つ目は下のようにしてみると違いが分かります。

見た目は同じでも、データとしては違います。
このままでは、データの照合が正しく行えません。
従って、名寄せ集計（条件付き集計）で使うSUMIFS関数やCOUNTIFS関数や抽出（取り出し）に使うXLOOKUP関数で正しい答えが得られません。
＊SUMIF/COUNTIF/VLOOKUP関数は使わない。
参考記事：使ってはいけない関数３選　①VLOOKUP、②SUMIF、③COUNTIF　初心者向けエクセル情報を鵜呑みにするなかれ

集計を正しく行うためには、集計を行う前にデータを整えておきます。

上の例にように、空欄や改行などの「余計なゴミ」を取り除く「データクレンジング（データ洗浄）」を行います。

１　相違データの見つけ方

データクレンジング（データ洗浄）の前に、「同じはずなのに違ている」データを見つけます。
２つのデータが並んでいれば以下が可能です。
[EXCEL] 2つのセルが同じか確認する方法
ただし、通常は１列にデータが縦に並んでいるかと思います。
なので、まずは「フィルター」を掛けてみます。

上と同じデータが縦に並んでいるとして・・・

「データ」を選択して　Alt ⇒ D ⇒ F ⇒ F でフィルターを掛け、▼をクリックしてみると・・・

同じに見える語句が複数出ています。
同じ語句ならフィルターを掛けると１つしか出ないはずです。
２つ（以上）出るということは、その２つは「違うものである」ということです。
「茅ヶ崎」と「茅ケ崎」は分かりやすいでしょう。
「東京都」と「東 京 都」も、離れていますが、一目で違いが分かります。
一方、「東京都品川区」は２つとも同じに見えます。
こういう時は、そのデータだけ選んで表示し、列幅を広げてみるとわかるかもしれません。

上は改行のため変わっていません。
下は「折り返し表示」なので列幅が変わると、折り返し位置も変わります。
これで、上と下の違いがはっきりします。

「おかしなデータ」が全くなければ「データクレンジング」は不要ですが、念のため、以下の３つぐらいをやっておくと、集計後の「合わないな。おかしいな」という状態が減るでしょう。

２　データクレンジング基本３選

＊以下は、数式を使わない場合の方法。数式を使って他のセルに修正後データを表示する方法もあるが今回は省略（列の挿入などが必要になるため）。

２-①「ヶ」と「ケ」の統一（置換）

小さな「ケ」と大きな「ケ」のブレは、どちらかに統一します（どちらが正しいか、は取り合えず置いておきます）。
仮に、大きな「ケ」を小さな「ヶ」に統一する場合は・・・

① データ列を選ぶ（列番号をクリック）
② Alt ⇒ E ⇒ E（又は Ctrl＋H ）で「検索と置換」ウィンドウを開く
③ 上の「検索する文字列」に大きな「ケ」を入れる
下の「置換後の文字列」に小さな「ヶ」を入れる

④ 「全て置換」を押下（または　Alt＋A）
⑤ 該当データがあれば以下の表示が出る。

⑥ 該当データがなければ以下のような表示が出る。

これで、大きな「ケ」が「ヶ」に変換されました。

「ヶ」と「ケ」の他にも、「川」と「河」など間違えやすいデータは同じように対応します。
＊データの表記ブレ（表記のゆれ）を防ぐには、入力時に「プルダウンリスト」を設定しておくと効果的です。
参考記事：プルダウンリスト(シンプル基本版）～とりあえずこれだけでOK ～「名前の定義」もINDIRECT関数も不要で項目追加可能

２-②　空白（スペース）の削除

空白（スペース）の除去も②‐1と同じです。
① データ列を選ぶ（列番号をクリック）
② Alt ⇒ E ⇒ E（又は Ctrl＋H ）で「検索と置換」ウィンドウを開く
③ 上の「検索する文字列」に半角スペースを入れる
下の「置換後の文字列」には何も入れない

④ 「全て置換」を押下（または　Alt＋A）
⑤ 同じことを全角スペースでも行う

（置換前）

（置換後）

「東 京 都」が「東京都」となりました。
一見、「東京都」と、その下の「東　京　都」は違うデータに見えます。
しかし、フィルターを掛けてみると・・・

「東京都」は一つしかないので、この２つは同じものであるとわかります。
下の「東　京　都」は「均等割り（インデント」が掛かっているため、セル幅いっぱいに表示されているのです。
これは、左上の数式バーが「東京都」となっていることからも分かります。

２-③　改行の削除

改行の除去も②‐1と同じです。

① データ列を選ぶ（列番号をクリック）
② Alt ⇒ E ⇒ E（又は Ctrl＋H ）で「検索と置換」ウィンドウを開く
③ 上の「検索する文字列」を選択 ⇒ Ctrl＋J
　下の「置換後の文字列」には何も入れない

④ 「全て置換」を押下（または Alt＋A）

（置換前）

（置換後）

下から２番目の「東京都品川区」が改行されていないことが分かります。
一番下の「東京都品川区」は「折り返して表示」となっているため、列の幅によって折り返し場所が変わりますが、今回の操作で、改行が入ってたデータも同様となっています。

★注意★
そもそも、文字データ（文字列）の入力の際に改行をいれてはいけません。
（改行は　Alt＋Enterで入る）。
参考記事：［Excel］セル内で改行してはいけない（セル内での改行のマイナス面）

＊セル内の改行が許されるのは、集計表（見せる表）で使う場合のみです（別記事予定）。

以上が主な３つのデータクレンジングの方法です。
これだけやるのなら１分もあればできます。
是非、データ集計の前には取り入れてください。

＊これ以外にもちょっとイレギュラーなものもありますので、別途記事にします。

#お役所のエクセル・データ修正