2025年07月01日更新

インソースデジタルアカデミー

データ整形はすべての土台！Excel関数で「データの汚れ」を解消するデータクレンジング

現代ビジネスにおいて、データ活用は企業の意思決定を左右する重要な要素となっています。しかし、多くの企業が直面するのが「データの汚れ」という課題です。

どれほど優れた分析ツールや専門知識があっても、元となるデータが適切に整備されていなければ、正しい分析結果は得られません。

データ分析の成否を分ける「データクレンジング」の重要性

データ分析の肝は、まさにデータクレンジングにあると言っても過言ではありません。データが最初からきれいに整っている組織は、ほとんど存在しないでしょう。社内のさまざまなシステムから出力されたデータ、手入力されたデータ、外部から取り込んだデータなど、多岐にわたるデータには、表記のゆれ、誤入力、欠損値、重複などの「汚れ」が必ずと言っていいほど含まれています。

「汚れたデータ」が引き起こすビジネス上の問題

データクレンジングが適切に行われないと、以下のような問題が発生し、ビジネスに大きな影響を及ぼします。

正しく集計ができない: 例えば、「税抜で売上集計される物販商品」と「税込で売上集計されるシステムサービス」を横断して分析する場合、いずれかに合わせる処理をしないと正確に比較することができません
事実を誤って把握する:一見正しく集計できていたとしても、元データに誤りや偏りがあると、現状を正しく認識できなくなります
意思決定を間違える: 不正確なデータに基づく意思決定は、事業戦略の誤りや機会損失につながりかねません

例えば、社員向けの社内研修参加者データを分析する際、外部の受講者データが混入していたり、全角と半角で氏名が登録されていたりすると、正確な参加者数や属性を把握できません。このような「データの整形」は、データ分析を行う上で絶対に必要な技術なのです。データクレンジングは、まさにデータ活用の土台であり、DX（デジタルトランスフォーメーション）推進の第一歩とも言えるでしょう。

Excelでできるデータクレンジングの基本と実践

データクレンジングと聞くと、専門的なツールや高度なプログラミングスキルが必要だと考える方もいるかもしれません。しかし、実際はそうではありません。日頃から使い慣れているExcelの基本的な関数や機能だけでも、多くのデータクレンジング作業に対応できます。ここでは、特に頻繁に直面する「データクレンジングあるある」とその解決策を、具体的なExcelの操作例を交えながらご紹介します。

文字の置き換え: SUBSTITUTE関数で表記を統一する

データに混在する表記のゆれを修正する際によく使うのが、SUBSTITUTE関数です。特定の文字列を別の文字列に置き換えたい場合に非常に役立ちます。

使用例: 「株式会社」の表記を統一する

ある顧客リストで、「株式会社」「(株)」「K.K.」といった表記が混在しているとします。これをすべて「株式会社」に統一する場合を考えます。

このデータをSUBSTITUTE関数を使って整形します。

数式: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(株)","株式会社"),"K.K.","株式会社"),"　","")

この数式では、まず(株)を株式会社に、次にK.K.を株式会社に置き換えています。さらに、全角スペースが混入している場合も想定し、"　"（全角スペース）を""（何もなし）に置き換える処理も加えています。このように、SUBSTITUTE関数を複数組み合わせることで、複雑な置き換えも可能です。

置き換え時の注意点として、「想定していない置き換え」が発生していないかを確認する必要があります。例えば上記の場合、万が一「株式会社K.K. 」などという社名があると、「株式会社株式会社」と変換されてしまいます。この場合、事前に該当するものだけ除外するなど、さらにデータクレンジングの工程が一つ追加されることとなります。

表記ゆれの修正と数値化: VALUE関数で文字列を数値に変換する

データの中には、見た目は数値でも実際は文字列として認識されているケースがあります。例えば、数値の先頭にスペースが入っていたり、全角数字で入力されていたりする場合です。このようなデータは、そのままでは集計や計算ができません。VALUE関数は、「文字列形式」の値を「数値形式」に変換する際に役立ちます。

使用例: 売上データで全角数字を半角に変換し数値化する

売上データに、全角数字で入力されたもの（１０００など）と半角数字（500など）で入力されたものが混在しているとします。

このデータをVALUE関数とASC関数を組み合わせて整形します。ASC関数は、全角英数字を半角英数字に変換する関数です。

数式: =VALUE(ASC(A2))

この数式を入力すると、全角数字が半角数字に変換され、さらに数値として認識されるようになります。

これにより、これらの数値を正確に集計したり、計算に使用したりできるようになります。

逆に、IDや商品コードのような計算に使用しない数値については、TEXT関数などを使って文字列として扱います。これにより、「0100」などの番号の冒頭の「０」が抜け、「100」になってしまうといったデータの破損を防ぐことができます。分析・集計を行う前に意図通りの「型」にデータがはまっているか、入念に確認しましょう。

不要情報の削除: フィルター機能とSUMIF関数を活用する

データの中から特定の条件に合致する不要な情報を削除したり、集計から除外したりすることも、データクレンジングの重要なステップです。

フィルター機能で視覚的に不要な行を非表示にする

Excelのフィルター機能を使えば、特定の条件を満たす行だけを表示し、不要な行を一時的に非表示にできます。これにより、必要なデータだけを抽出して作業を進めることが可能です。

使用例: 特定のプロジェクトのデータを除外して集計する

ある売上データから、社内研修の売上を除外して集計したい場合を考えます。

売上データの見出し行を選択し、「データ」タブから「フィルター」を適用します。
「プロジェクト名」列のフィルターボタンをクリックし、「社内研修A」と「社内研修B」のチェックを外します。
これにより、社内研修のデータが一時的に非表示になり、それ以外のデータのみが表示されます。

この状態で売上を合計すれば、社内研修を除外した売上合計を簡単に算出できます。

フィルター機能における注意点は、「正しい範囲にフィルターがかかっているか」を確認することです。一度フィルターをかけた後に追加された行や列がフィルターの範囲から出ていると、並べ替えや抽出をした際にデータに不具合が起きます。特に他者とやり取りをしたものや、古くから使っているデータを扱う際には注意し、場合によってはフィルターをかけなおしましょう。

SUMIF関数で条件に合致するデータだけを集計する

フィルター機能は視覚的な操作ですが、関数を使って条件に基づいて集計したい場合はSUMIF関数が便利です。

数式: =SUMIF(A2:A6,"<>社内研修A",B2:B6)

この数式は、A列のセルが「社内研修A」ではない場合に、B列の対応するセルの値を合計します。特定の文字列を含まない、あるいは特定の条件を満たさないデータだけを集計したい場合に非常に有効です。

またExcelのバージョンが2016以降であれば、複数の条件を設定できる「SUMIFS関数」も利用できます。様々な条件を設定し値の出し分けを行えることは非常に便利ですが、他の人や読み解くのが困難になったり、条件設定のミスが起こりやすくなるなどのリスクもあるので、慎重に扱いましょう。

最も難しい「日本の住所の整形」

データクレンジングの中でも、特に複雑で手間がかかると言われるのが「日本の住所の整形」です。都道府県、市区町村、番地、建物名、部屋番号など、住所を構成する要素が多様であり、かつ表記のゆれも非常に多いため、一筋縄ではいきません。例えば、「東京都」「東京」といった都道府県名のゆれ、漢数字と算用数字の混在、ビル名やマンション名の省略形など、考慮すべき点が多数存在します。

これらをExcelの関数だけで完全に整形するのは非常に困難であり、高度な処理が必要になる場合もあります。しかし、基本的なルールを定めて部分的に修正するだけでも、データの精度は格段に向上します。例えば、LEFT関数やFIND関数、MID関数などを組み合わせて、特定の文字で区切られた住所の要素を抽出したり、不要な情報を削除したりといったアプローチがあります。

まとめ: データクレンジングはすべての土台

ここまで見てきたように、データ分析や統計学といった専門的な知識がなくても、Excelの基本的な関数や機能を使いこなすだけで、多くのデータクレンジング作業に対応できます。データクレンジングは、決して地味な作業ではありません。むしろ、データの信頼性を高め、正確な分析結果を導き出すための、非常に重要な土台作りのプロセスです。

貴社の部下の方々が、このようなデータクレンジングのスキルを習得することで、日々の業務におけるデータ活用能力が飛躍的に向上し、より精度の高い意思決定に貢献できるようになるでしょう。データが持つ真の価値を引き出すために、まずは「きれいなデータ」を用意することから始めてみませんか。

（半日研修）Excelでできるデータクレンジング入門研修

インソースでは、本コラムでご紹介したようなExcelを用いたデータクレンジングの基礎から実践までを習得できる「Excelでできるデータクレンジング入門研修」をご提供しています。

研修のポイント

データクレンジングの重要性と基本原則を理解します。
Excelの関数（SUBSTITUTE、VALUEなど）や機能（フィルターなど）を駆使した実践的なデータクレンジング手法を習得します。
データ分析の前処理としてのクレンジングのコツを学びます。
ワークや演習を通じて、実践的なスキルを身につけます。

＞公開講座の詳細はこちら

セットでおすすめの研修・サービス

データサイエンティスト入門研修～Excelでのデータ分析を体験する（２日間）

データサイエンティストとしての業務フローを学び、ビジネス課題を解決するスキルを実践的に身につける研修です。

具体的には「残業時間が多いと離職率が高いのではないか」という仮説をテーマに、用意されたデータをExcelのみで分析し、結果から読み取れることを踏まえて施策を考えます。実務に即した一連のプロセスを体験した後には総合演習に取り組み、データ活用による問題解決力と分析スキルの習得によって、即戦力としての活躍を目指します。

＞講師派遣の詳細はこちら