☰

2 つの Excel スプレッドシートの違いを強調表示する

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting
2 つの Excel スプレッドシートの違いを強調表示する

スプレッドシート A のデータには複数の列がありますが、比較する必要があるのは次の列だけです。

Column A - order #
Column C - count
Column D - date

スプレッドシート B にも多くの列がありますが、比較する必要があります。

Column A - order #
Column B - count
Column C - date

行全体のデータが 3 列のみで同一である場合、両方のスプレッドシートの行の列 A を強調表示する必要があります。この例では、行 6 と 14 には異なる日付があり、行 7 と 15 には異なる数量があるため、強調表示されません。

例を見るにはクリック

答え1

この方法では、最初のスプレッドシート内のいくつかのヘルパー列、または必要に応じて別のシート内のヘルパー列を使用します。

MATCH() は、配列内の指定された値の行番号を返します。

INDEX()は、別の指定された配列でこの値を検索します。

したがって、ヘルパー列の行 1 (列 X とします) では、=MATCH(A1,SheetName!A:A,0) A1 の注文番号を含む他のシートの行が返されます。

次に、次の 2 つの列に を入力します=INDEX(SheetName!C:C,$X1) =INDEX(SheetName!D:D,$X1)。これにより、その行の他のシートからカウントと日付が返されます。

次に、最後の列で=And(Y1=C1,Z1=D1)、すべてが同じであれば TRUE を返します。

次に、必要に応じて条件付き書式を使用してハイライトを作成します。

これらはすべて 1 つの数式に凝縮して、必要に応じて条件付き書式設定の条件として使用できます。また、vlookup はインデックス マッチと同様に機能します。何が起こっているのか、なぜ機能するのかを理解しやすいように、すべてを分けて示しましたが、練習として凝縮してみることをお勧めします。

これが役に立つことを願っています。

注意: 注文番号がルックアップ リスト内で重複している場合は、テーブル内の最初の出現のみが検索されるため、これは機能しません。

答え2

条件付き書式設定の「数式を使用して書式設定するセルを決定する」を使用して、これを実行できます。簡単に言うと、Sheet1 にある最初のテーブルと Sheet2 にあるもう 1 つのテーブルの両方で、A1 から始まる次の数式を使用する必要があります。

=AND(MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)>0; INDEX(Sheet1!$A:$D;ROW();3)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2); INDEX(Sheet1!$A:$D;ROW();4)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);3))

この式では次のことを前提としています。

  • 両方のテーブルは、Sheet1 と Sheet2 という 2 つの別々のシートにあります。
  • Sheet1 には最初のテーブルが含まれており、その A、C、D 列が Sheet2 の A、B、C 列の 2 番目のテーブルと比較されます。
  • シート1の条件付き書式設定領域で、$A:$Aまたは$A2:$AXX（XXはすべての値を含むのに十分な大きさの数字）を選択します。これは、上記の式が正しい場合にのみ色を設定するために重要です。

期待通りに動作しない場合:

  • 2番目のテーブルに同じ注文番号が複数ある場合は、最初の一致のみが検索され、比較されます。
  • Sheet2テーブルに適用すると正しい色付けが返されないため、参照値の一部を変更する必要があります。

使用される機能:

  • AND()- すべてのパラメータが真の場合のみ真を返します
  • MATCH()- 選択した範囲（この場合は列）内の値を検索します
  • INDEX()- 行番号と列番号を明示的に指定して、行列/範囲からセル参照を返します。
  • ROW()- パラメータを指定しない場合は現在の行番号を返します

次に、いくつかのコンポーネントを見てみましょう。

  • INDEX(Sheet1!$A:$A;ROW();1)これは、条件付き書式設定の実行時に現在の比較セル参照を返します。
  • MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)ここでは、現在のセルと一致する 2 番目のシート上のセルを検索します。一致する場合、返される値は 0 より大きくなり、そうでない場合は警告/エラーが表示されます。
  • INDEX(Sheet1!$A:$D;ROW();3)- ここでは、最初のテーブル (Sheet1) の 3 番目の列 (エイリアス C 列) の値を検索します。
  • INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2)- これは、Sheet2 テーブルから 2 番目の列を取得します。注文 ID が配置されている行はすでに存在しており、その式はこの関数の中央にあり、行を決定します。
  • 3 番目のAND()パラメータは前の 2 つのポイントと同じですが、列番号が変更されるだけです。

注記：

  • 正しい行を見つけるために、私は列のみを使用します：Sheet1!$A:$AそしてSheet2!$A:$A
  • 別の列を取得する必要がある場合は、最後の列が少なくとも参照する最後の列であるマトリックス/テーブルに列を拡張する必要があります。シート1の場合は4番目のエイリアスD列です。Sheet1!$A:$D

上記を理解した上で、Sheet2 テーブルでも機能するように数式を変更できる必要があります。

答え3

これは、次のように条件付き書式を使用すると簡単に実行できます。

設定

以下の例の数式では、データはセル Sheet1!A1:C3 と Sheet2!A1:C3 にあります。

条件付き書式設定式

セル範囲を強調表示しますシート2数式による条件付き書式を追加し、次の式を入力します: =AND(Sheet1!$A1=$A1,Sheet1!$B1=$B1,Sheet1!$C1=$C1) ...そして、結果を強調表示する色を選択します。

行ではなく列をロックすることが重要です (つまり、文字の前にドル記号を付けますが、数字の前には付けません)。

条件付き書式設定の数式で使用される参照については、セル参照は対応するデータ列の最上位セルである必要があります。したがって、注文番号の両方の列が A1 で始まる場合は、上記のように A1 のままにします。ただし、最初のタブでは考慮する最初の注文番号が行 4 にあり、2 番目のタブでは行 7 にある場合は、数式を AND(Sheet1!$A4=A7...) などに変更します。

何か明確にする必要があることがあればお知らせください。

関連情報