Excel で矛盾を見つける – 5 つの簡単なテクニックの説明

Microsoft Excel で大規模なデータセットを操作する場合、データの不一致が発生することは避けられません。これらの不一致を特定して修正することは、正確なレポートと分析を保証するために重要です。

良いニュース？Excelには、これらの問題の発見と解決に役立つさまざまなツールとテクニックが付属しています。この記事では、最も効果的な方法のいくつかについて学びます。これには、フィルタリング、条件付き書式設定、高度な数式の使用が含まれます。

これらのテクニックを日常のワークフローに組み込むことで、データの管理と分析がより効率的に行えるようになります。最終的には、より良い意思決定とプロジェクトの成果の向上につながります。

目次

• Excel の不一致を理解する
• Excel で不一致を見つける 5 つの方法
• #1 – Excel での手動レビューによる不一致の発見
• #2 – Excel のフィルターを使用して矛盾したデータを検出および修正する
• 3# – 条件付き書式設定による不一致の強調表示
• #4 – 高度な Excel 関数の不一致の検出
  • IF 関数と IS 関数
  • VLOOKUP、HLOOKUP、XLOOKUP
  • マッチ
• #5 – Excel アドインを使用した不一致検出
• 不一致を防ぐデータ検証ルールを作成するにはどうすればよいでしょうか?
• 私たちの最後の言葉

Excel の不一致を理解する

行と列の不一致は、人による入力エラー、重複エントリ、またはデータ形式の不規則性によって発生する可能性があります。

次のデータを見てください。ウォルター ホワイトとウェイン シナの性別のスペルが間違っていることに気づくでしょう(男性ではなくマエル)。実際、このシナリオではエラーに気づいて修正するのは簡単です。しかし、数千の行と列を含むスプレッドシートの場合はそうではありません。

これらの不一致を見つけて管理するには、次のExcel機能を使用できます。

• フィルター:特定の列にフィルターを適用して一意の値を表示し、矛盾したデータをすばやく見つけます。
• 条件付き書式設定:書式設定ルールを使用して、特定の条件を満たすセル、または列間で不一致なデータを強調表示します。
• 高度な Excel 関数: MATCH やVLOOKUPなどの関数を利用して、データ セットを比較し、それらの間の不一致を特定します。

Excel でのこれらの機能の使用方法を理解することで、データ セット内の不一致を効率的に特定して修正できます。

Excel で不一致を見つける 5 つの方法

Excel で不一致に対処する主な方法は 5 つあります。

1. マニュアルレビュー
2. フィルタリング
3. 条件付き書式
4. 高度な Excel 関数
5. Excel アドイン

それぞれの方法を確認して、自分に合った最適な方法を見つけてください。

#1 – Excel での手動レビューによる不一致の発見

Excelを使用する場合、手動レビューはデータの不一致を特定するのに役立つ方法です。このプロセスでは、データセットを注意深く調べて、矛盾やエラーを見つける必要があります。

手動レビューを実行するために実行できる手順をいくつか示します。

• まず、使用しているデータセットを明確に理解することから始めます。行、列、書式設定についてよく理解してください。
• データをスクロールし、各セルに表示される情報に細心の注意を払ってください。塗りつぶし色によるセルの強調表示、テキスト書式設定、条件付き書式設定などのツールを使用して、データを読みやすくすることができます。
• ある行と別の行、またはある列と別の列を比較します。Excel シートに不正確さを引き起こす可能性のある情報の不一致や重複エントリなどを探します。
• 矛盾を特定したら、リストを作成するかコメントを使用して文書化します。これらは、後で問題を修正するための重要な参照として機能します。

Excel スプレッドシート内のデータの不一致を手動で確認する場合は、忍耐力が鍵となります。このプロセスには時間がかかります。ただし、これは潜在的なエラーを特定するための良い出発点です。

#2 – Excel のフィルターを使用して矛盾したデータを検出および修正する

矛盾したデータを検出する最も簡単な方法の 1 つは、Excel のFilterを使用することです。

フィルターを使用して、次のデータ セットの[性別]列の誤ったデータを分離します。

1. [ホーム]タブで[並べ替えとフィルター]を選択します。

2. [フィルター] を選択します。カーソルがデータの範囲内にあることを確認します。

3.一貫性のないデータを含む列のドロップダウン リストの矢印をクリックします。

4. ここで、正しい一致データをすべて選択解除します。私たちの場合、それはMaleです。次に、「OK」をクリックして変更を保存します。

5. Excel ワークシートには、間違った値が含まれる行のみが表示されるようになります。これらを修正するには、[ホーム]タブの[検索と選択]をクリックします。

6.ドロップダウン リストが表示されたら、[置換]をクリックします。

7. [検索する文字列]に間違った値を入力し、[置換後の文字列] に正しい値を入力します。次に、「すべて置換」をクリックします。

8. 変更を確認するダイアログボックスが表示されます。[OK]をクリックして続行します。

3# – 条件付き書式設定による不一致の強調表示

列と行の違いを識別するもう 1 つの方法は、条件付き書式を使用することです。

ハイライトセル、上部/下部、データバー、カラースケール、アイアンセットなどのルールにアクセスできます。さらに、新しいルールを作成したり、以前のルールを管理またはクリアしたりできます。

サンプル表を使用して、条件付き書式設定の威力を示します。

1. テーブルまたは希望の行と列を選択します。ここでは列 A (会社) と列 B (国) を選択しています。次に、「ホーム」タブで「条件付き書式」を選択します。

2. ルールのタイプを選択し、ルールを選択します。ハイライトセルルールと重複値を選択しています…

3. ここで、重複値または一意の値を強調表示するかどうか、およびセルの書式設定のための塗りつぶしの色とフォントを選択する必要があります。完了したら、「OK」をクリックします。

4. すべての重複値が、明るい赤の塗りつぶしと暗い赤のテキストで強調表示されます。

#4 – 高度な Excel 関数の不一致の検出

IF 関数と IS 関数

セルを比較して違いを特定するには、IF 関数と IS 関数を利用できます。

たとえば、数式を使用して=IF(A2=B2,"MATCH","NOT MATCHED")セル A2 と B2 を比較しました。そして、数式をドラッグして他のセルに適用しました。したがって、同一のセルはMATCHを返し、それ以外の場合はNOT MATCHED を返します。

IF と ISNUMBER や ISTEXT などの IS 関数を組み合わせて、特定のデータ型をチェックできます。たとえば、B2 が数値の場合、数式は列 c のNumber=IF(ISNUMBER(B2), "Number", "NaN")を返し、それ以外の場合はNaNを返します。

VLOOKUP、HLOOKUP、XLOOKUP

VLOOKUP (垂直) 関数と HLOOKUP (水平) 関数は、2 つのテーブルの異なる列または行の値を比較する場合に便利です。これを行うには、次の式を使用します:=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) または=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])。

lookup_value を他のテーブルで検索する値に置き換え、 table_array を2 番目のテーブルのセルの範囲に置き換え、col_index_numまたはrow_index_num を値を返す列または行のインデックス番号に置き換え、[range_lookup] を次のように置き換えます。完全一致の場合はFALSE 、近似一致の場合はTRUE 。

次のチュートリアルでは、列 E で VLOOKUP 関数を使用して列 A と列 D の従業員名を比較し、一致するものが見つかった場合に列 B から給与を引き出します。

XLOOKUP は、Excel 2021 および Excel 365 で利用できる VLOOKUP および HLOOKUP の更新バージョンです。XLOOKUP の式は次のとおりです=XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found])。

lookup_value を他のテーブルで検索する値に、lookup_array を2 番目のテーブルで検索するセルの範囲に、return_array を値を返す配列に、[if_nout_found] をテキスト値に置き換えます（一致するものがない場合）。値が見つかります。

同じテーブルを使用した XLOOKUP の簡単なサンプルを次に示します。

マッチ

MATCH 関数を使用して、2 つのリストの矛盾を比較することもできます。

MATCH 関数 は、=MATCH(lookup_value, lookup_array, [match_type])範囲内で指定された項目を検索し、その範囲内の項目の相対位置を返します。

ここでは、MATCH を使用して配列A2:A8内のスイカの位置を見つけています。lookup_valueと正確に等しい最初の値を見つけるために、match_type を0として使用しています。

#5 – Excel アドインを使用した不一致検出

Excel には、不一致をより効率的に検出および分析できるようにするさまざまなアドインおよび組み込みツールが用意されています。このセクションでは、それらの便利なアドインのいくつかについて説明します。

まず、Spreadsheet Inquireアドインの使用を検討してください。これは、2 つのブックを比較し、セルごとの相違点を強調表示するのに役立ちます。Spreadsheet Inquire アドインを有効にするには:

1. 「ファイル」タブをクリックします。

2. [オプション]を選択し、[アドイン]カテゴリをクリックします。

3. [管理]ボックスで[COM アドイン]を選択し、[移動]をクリックします。

4. [スプレッドシートの照会]ボックスをオンにして、[OK]をクリックします。

5. 有効にしたら、「照会」タブに移動して「ファイルの比較」コマンドを使用します。

不一致検出に役立つもう 1 つのツールは、分析ツールです。このアドインは、矛盾を分析する際に役立つ高度な統計関数とデータ分析ツールを提供します。

分析ツールを有効にするには、Spreadsheet Inquire アドインを有効にするのと同じ手順に従います。ただし、[管理]ボックスで[Excel アドイン]を選択し、[分析ツール]ボックスをオンにします。

要約すると、Excel には、データの不一致を検出して処理するのに役立つさまざまなアドインが用意されています。これらのツールと計算式に慣れることで、データの精度を向上させることができます。

不一致を防ぐデータ検証ルールを作成するにはどうすればよいでしょうか?

不一致の防止に役立つデータ検証ルールを Excel で作成するには、次の手順に従います。

1. 制限したいセルを選択します。これは、単一のセル、セル範囲、または列全体にすることができます。

2.ツールバーの「データ」タブに移動し、 「データ検証」ボタン (2 つの水平ボックス、緑のチェックマーク、および赤の×印の円でマークされています) をクリックします。

3. [データ検証] ウィンドウで、[設定]タブが表示されていることを確認します。ここでは、ニーズに基づいて検証基準を定義できます。

Excel ではさまざまな種類の検証基準を使用できます。そのうちのいくつかは次のとおりです。

• 整数 - 指定された範囲内の整数のみを許可します。
• 10 進数 – 指定された範囲内の 10 進数のみを許可します。
• リスト – 入力を許容値の事前定義されたリストに制限します。
• 日付 – 特定の日付範囲内の日付が必要です。
• 時間 – 特定の時間範囲内の時間のみを受け入れます。
• テキストの長さ – 入力されたテキストの長さに制限を適用します。
• カスタム – Excel の数式を使用してカスタム検証ルールを作成できます。

適切な検証基準を選択した後、必要に応じてパラメータを指定します。たとえば、[整数] を選択した場合は、[範囲]、[等しい]、[未満] などのデータ範囲バリデーターを使用して最小値と最大値を設定する必要があります。

検証基準に加えて、ユーザーが検証ルールを理解して遵守できるように、カスタム エラー メッセージや入力ヒントを設定することもできます。これを行うには、[入力メッセージ]タブと[エラー アラート]タブに切り替えて、必要なメッセージを入力します。

データ検証ルールを実装すると、データの不整合の可能性が大幅に減少し、Excel ワークシートの全体的な精度が向上します。

私たちの最後の言葉

この記事では、Excel データの不一致を見つけて対処するためのさまざまな方法を学習しました。これらの主要な戦略を要約しましょう。

• 列のフィルタードロップダウンを使用して、一意の値と一貫性のないデータを特定します。
• 選択したセルに条件付き書式を適用し、多数のルール テンプレートを使用して不一致を強調表示します。
• MATCH、VLOOKUP、XLOOKUPなどの高度な Excel 関数を適用して、2 つのリストまたはテーブルのデータをクロスチェックします。
• Excel アドインを使用して不一致を検出します。
• データのエラーを防ぐためのデータ検証ルールの設定

これらのテクニックを Excel スキルセットに組み込むことで、データの正確性と信頼性が高まります。

スプレッドシートの不一致をより効率的に検出できるよう、これらの方法を定期的に練習することを忘れないでください。

にサインアップしてスキルを磨き続け、Excel の習熟度をさらに高めるために新しい機能を探索し続けてください。