【完全版】データクレンジングに活用できるExcel/スプシの関数を考えてまとめてみた
こんにちは！クロスコムの本田です。
今回は、データクレンジングに活用できるExcel/スプシの関数を考えてまとめました。
「クレンジングなくしてデータ利活用なし！」ということで、早速お伝えしていきます。
１．どんなときにクレンジングが必要？
・顧客データの入力形式を関数で自動統一したい方
・複数のスプレッドシートを１つに統合する際に突合する方
・データクレンジングの関数を一覧で把握したい方
・クレンジングに活用できる関数を知りたい方
事の発端は、とある企業様の顧客データをクレンジングするときにデータの入力形式、全然揃ってねぇじゃん！から始まりました。検索エンジンで「データクレンジング Excel関数」と調べても、一部の関数だけしか説明されていないサイトばかり。かと言って、Excel関数全486つを１つ１つ見るのは面倒。ということで、クレンジング用に活用できる関数に限定して、一覧でパッと把握できるリストをつくりました。
またみなさんには本投稿を手引書のように使ってほしいので、目次の各見出しに記載した関数名から、知りたい関数へ直接ジャンプできるよう書いています。データクレンジングに四苦八苦する方の一助になれば幸いです。
複数のExcel/スプシを１つに統合するとき
複数のファイルもしくは別sheetに散在しているスプシを統合するときに、ラベルや表記、単位などデータクレンジングって必要になるんですよね。それぞれのデータを関数でラクに突合したくても、ただの表記ゆれで同一だと判断できないと重複を引き起こしますから。
冒頭文で説明した会社名・住所で完全一致できないと、関数やフィルター機能で重複判断ができないので、結構メンドーです。数十件程度ならまだ初見で分かりますが、何千件何万件のレコード数となると、人力ですべて対応するのはバカにならない時間を要します。
入力データの形式を統一したいとき
クレンジング対象になり得るデータ候補は、主に以下のような形式が該当するでしょう。
■表記ゆれ（同一の値にもかかわらず、表記が混在している状態）
・英数字/カタカナの半角全角の不統一 ー 例）クロスコムとｸﾛｽｺﾑ
・法人格の有無または略称 ー 例）（株）と(株)と株式会社
・スペースの半角/全角または有無 ー 例）〇番地マンションAと〇番地 マンションA
・電話番号の国際形式 ー 例）(+81)3-xxxx-xxxxと03-xxxx-xxxx
・ハイフンの有無または半角全角 ー 例）090-xxxx-xxxxと090xxxxxxxx
■欠損値（データが記録されず存在していない状態）
・値が空白 － 入力忘れや、数式/関数漏れ）
・値がN/A － （Not Available_該当なし）
■外れ値（データ全体の分布から極端に離れた値）
・原因が不明の極端に離れた値
・原因が把握できている極端に離れた値（＝異常値）
表記ゆれに関してこの状態では、同一リストなのにフィルタをかけると完全一致しない、vlookup関数等で参照がヒットしない他、データの名寄せ（複数のデータを1つのデータに統合する）を行う場合、同一なのに別物として存在することでリストの重複が発生してしまうんですよね。
※欠損値や外れ値に関しては、データの整合性が問われる現象ですので、今回の記事では取り扱いません。
重複がある状態のデータを活用すると、例えば営業のときに同じ会社に２回連続でメールを配信したり電話してしまいます。顧客からすれば迷惑な話です。企業への不満と不信感も高まるので、避けたいところです。
ということで、これから顧客リストを統合される方が、重複の削除作業をできるだけラクに実施できるよう、活用できる関数をリスト化しています。突合した後のクレンジング内容次第で、活用する関数も異なると思うので、なるべく厳選せずに活用できそうな関数をひと通り纏めています。
２．データを整形する関数
CLEAN関数：改行を削除する
TRIM関数：空白を削除する
ASC関数：全角文字を半角文字にする
JIS関数：半角文字を全角文字にする
CLEAN関数：改行を削除する
式 ：=CLEAN(A2)
説明：セルA2に含まれる改行を削除します。
CLEAN関数は、セル内の表示されない制御文字（例えば、タブ、改行など）を削除するために使用されます。特に、他のアプリケーションからインポートしたデータに含まれる印刷できない文字を除去する際に役立ちます。
TRIM関数：空白を削除する
式 ：=TRIM(文字列)
説明：対象セルの文字列から余分なスペースを削除します。
補足：スプレッドシートでは"半角スペースのみ"削除できます。文字間に最低1つはスペースを残すため、半角スペースが連続する(A17)場合は、B17のように1つ半角スペースを残します。一方Excelでは、全角半角両方のスペースが削除できます。
TRIM関数は、テキスト文字列から不要なスペースを削除します。これには、文字列の先頭と末尾のスペース、および文字列内の連続するスペース（これらは単一のスペースに縮小されます）が含まれます。
ASC関数：全角文字を半角文字にする
式 ：=ASC(A3)
説明：セルA3に含まれる全角文字を半角に変換します。
ASC関数は、全角のカタカナや特殊文字を半角に変換します。主に、システムが全角文字をサポートしていない場合に使用されます。
JIS関数：半角文字を全角文字にする
式 ：=JIS(A2)
説明：セルA2に含まれる半角文字を全角に変換します。
補足：JIS関数はスプレッドシートで使えません。
JIS関数は、半角のカタカナや特殊文字を全角に変換します。主に、文書の整形や表示の一貫性を保つために使用されます。
３．データを変換・置換する関数
DATEVALUE関数：日付データをシリアル値に変換する
IF関数：特定の条件を満たす文字列を指定値に置換する
UPPER関数：すべての文字を大文字に変換する
LOWER関数：すべての文字を小文字に変換する
PROPER関数：各単語の最初の文字を大文字、残りを小文字に変換する
SUBSTITUTE関数：文字を別の文字へ置換する
REPLACE関数：指定した文字の位置で別の文字へ置換する
DATEVALUE関数：日付データをシリアル値に変換する
式 ：=DATEVALUE(A4)
説明：テキスト形式のセルA4"2024/1/1"を日付のシリアル値に変換します。
DATEVALUE関数は、日付を表すテキストを日付のシリアル値に変換します。これにより、日付として認識されないテキスト形式の日付を、計算や分析に使用できる形式に変換できます。
※シリアル値とは、「1900年1月1日」を「1」として、経過した日数値を指します。
IF関数：特定の条件を満たす文字列を指定値に置換する
式 ：=IF(A5>10, "大きい", "小さい")
説明：セルA5の値が10より大きい場合は"大きい"、そうでない場合は"小さい"と表示します。
IF関数は、指定された条件が真か偽かに基づいて、異なる値を返します。条件式、真の場合の値、偽の場合の値を指定します。
UPPER関数：すべての文字を大文字に変換する
式 ：=UPPER(A6)
説明：セルA6をすべて大文字に変換します。
UPPER関数は、テキストのすべての文字を大文字に変換します。文書の整形やデータの標準化に使用されます。
LOWER関数：すべての文字を小文字に変換する
式 ：=LOWER(A7)
説明：セルA7をすべて小文字に変換します。
LOWER関数は、テキストのすべての文字を小文字に変換します。これも文書の整形やデータの標準化に役立ちます。
PROPER関数：各単語の最初の文字を大文字、残りを小文字に変換する
式 ：=PROPER(A8)
説明：文字列"john doe"の各単語の最初の文字を大文字に変換します。
PROPER関数は、テキストの各単語の最初の文字を大文字に、残りの文字を小文字に変換します。特に名前やタイトルの整形に適しています。
SUBSTITUTE関数：文字を別の文字へ置換する
式 ：=SUBSTITUTE(A9, "（株）", "株式会社")
説明：セルA9の"（株）"を、"株式会社"に置換します。
SUBSTITUTE関数は、テキスト内の特定の文字列を別の文字列に置換します。指定した文字列が複数回出現する場合、すべて置換されます。
REPLACE関数：指定した文字の位置で別の文字へ置換する
式 ：=REPLACE(A10, 4, 3, "ABC")
説明：セルA10の4文字目から3文字を"ABC"に置換します。
REPLACE関数は、テキスト内の指定された位置から始まる特定の長さの文字列を、別の文字列で置換します。
４．データを抽出・統合する関数
CONCATENATE関数：複数の文字列を１つに統合する
RIGHT関数：右から指定した数の文字列を抽出する
LEFT関数：左から指定した数の文字列を抽出する
MID関数：指定した位置から指定の文字数を抽出する
VLOOKUP関数：列方向に並ぶ範囲から値を抽出する
HLOOKUP関数：行方向に並ぶ範囲から値を抽出する
XLOOKUP関数：列方向に並ぶ範囲から取り出す範囲を指定して値を抽出する
INDEX関数：表から行・列の番号を指定し値を抽出する
PHONETIC関数：文字のフリナガを抽出する
DATEDIF関数：２つの日付期間を抽出する
CONCATENATE関数：複数の文字列を１つに統合する
式 ：=CONCATENATE(A2, " ", B2)
説明：セルA2とB2の値を全角スペースで区切って結合します。
CONCATENATE関数は、複数のテキスト文字列を一つに結合します。異なるセルにあるデータを一つのセルにまとめる際に使用されます。
例えば、名前と姓が別々の列にある場合、これらを一つの列に結合して整理することができます。
RIGHT関数：右から指定した数の文字列を抽出する
式 ：=RIGHT(A11, 4)
説明：セルA11の右端から4文字を抽出します。
RIGHT関数は、テキスト文字列の右端から指定された数の文字を抽出します。特に、文字列の末尾にある特定の情報を取り出す際に役立ちます。
続くLEFT関数・MID関数も同様で、例えば、郵便番号や電話番号の特定の部分を取り出すことで、データの一貫性を保つことができます。
LEFT関数：左から指定した数の文字列を抽出する
式 ：=LEFT(A12, 3)
説明：セルA12の左端から3文字を抽出します。
LEFT関数は、テキスト文字列の左端から指定された数の文字を抽出します。特に、文字列の先頭にある特定の情報を取り出す際に使用されます。
MID関数：指定した位置から指定の文字数を抽出する
式 ：=MID(A13, 5, 4)
説明：セルA13の5文字目から4文字を抽出します。
MID関数は、テキスト文字列の指定された位置から始まる特定の数の文字を抽出します。文字列の中間にある特定の情報を取り出す際に使用されます。
VLOOKUP関数：列方向に並ぶ範囲から値を抽出する
式 ：=VLOOKUP(C5, A:B, 2, FALSE)
説明：範囲A:Bの最初の列で"山田"を検索し、同じ行の2列目の値を返します。
数式：=VLOOKUP(検索値, 参照範囲, 列番号, 一致条件)
VLOOKUP関数は、表または範囲の最初の列で指定された値を検索し、同じ行の別の列から値を返します。続くHLOOKUP関数・XLOOKUP関数同様、大きなデータセットから特定の情報を検索し、関連する他のデータと結びつけるのに役立ちます
HLOOKUP関数：行方向に並ぶ範囲から値を抽出する
式 ：=HLOOKUP(B6 $A$2:$E$3, 2, FALSE)
説明：範囲A2:E3の最初の行でB6を検索し、同じ列の2行目の値を返します。
数式：=HLOOKUP(検索値, 参照範囲, 行番号, 一致条件)
HLOOKUP関数は、表または範囲の最初の行で指定された値を検索し、同じ列の別の行から値を返します。横方向のデータ構造での検索に使用されます。
XLOOKUP関数：列方向に並ぶ範囲から取り出す範囲を指定して値を抽出する
式 ：=XLOOKUP("F2", A1:A10, B1:B10)
説明：範囲A1:A10の行でB6を検索し、B1:B10の該当する行の値を返します。
数式：=XLOOKUP(検索値, 参照範囲, 戻し範囲)
XLOOKUP関数は、指定された範囲内で値を検索し、別の範囲から対応する値を返します。VLOOKUPやHLOOKUPよりも柔軟で強力な機能を提供します。
※戻し範囲は、返したい範囲の列を設定します。
INDEX関数：表から行・列の番号を指定し値を抽出する
式 ：=INDEX(A1:C10, 3, 2)
説明：範囲A1:C10の3行目と2列目の交点にある値を返します。
数式：=INDEX(検索範囲, 行番号, 列番号)
INDEX関数は、特定の行と列の交点にある値を返します。範囲内の特定の位置のデータを取得する際に使用されます。
PHONETIC関数：文字のフリナガを抽出する
式 ：=PHONETIC(A3)
説明：セルA3に含まれる日本語のフリガナを抽出します。
補足：PHONETIC関数はスプレッドシートでは使えません。
PHONETIC関数は、日本語のセル内のフリガナ（ふりがな）を抽出します。主に、日本語の名前や単語の読みを取得する際に使用されます。
DATEDIF関数：２つの日付期間を抽出する
式 ：=DATEDIF(A7, B7, "D")
説明：セルA7からセルB7までの日数を計算します。
数式：=DATEDIF(検索日付①, 検索日付②, "Y or M, D")
※年単位は"Y"、月単位は"M"、日単位は"D"
DATEDIF関数は、二つの日付の間の期間を計算します。日数、月数、年数など、異なる単位でデータの期間や年齢を求めることができます。
５．Excel,スプレッドシートのデフォルト機能も十分にクレンジング活用できる
今の現場で使えそうな関数はありそうでしょうか？調べてみると、EXCELの関数は合計で486つもあるそうです。すべてを駆使するのは相当難しいですし、状況に応じて
私自身もこれらの中から、データの実態に即して関数を取捨選択しています。私の場合、複数のスプレッドシートのリストを突合する作業なので、フィルターで完全一致しない対象リストの属性を整形したり、しています。
また関数以外でも、Excel/スプレッドシートには検索置換や重複削除のデフォルト機能があります。さらにはワイルドカード「*」という特定の文字列をこの記号で代替できますので、関数と組み合わせてExcel/スプレッドシートの機能を活用してみてください。
現場で顧客データの突合作業で四苦八苦されている方、私は同志です。ChatGPTに任せようとデータ読み込ませてトライしましたが、期待通りの出力結果が返ってこないので、断念しました。
みなさんのクレンジング作業が無事に終えられることを祈っています。
最後までお付き合いいただきありがとうございました！