これが私たちの公式です

Question

同じ列間で異なる値を持つセルを条件付きで書式設定する必要があります。もし特定の列の値は同じです。

シナリオ

私のスプレッドシートは連絡先データベースで、各行は連絡先レコードであり、各列には各連絡先レコードのデータの種類 (姓、番地 1、番地 2、市、州など) が含まれています。

スプレッドシートは、社内のさまざまなオフィスに保存されている連絡先情報を統合したものです。すべての連絡先情報を同じソースに統合することで、不一致があれば合意し、今後すべての連絡先を同じ場所に保管できるようになります。

一部の連絡先には 1 つのレコードしかありません。たとえば、その連絡先のデータが 1 つのオフィスにしかなく、他のオフィスのデータと一致しない場合があります。一方、他の連絡先には複数のレコードがあります (たとえば、Fred Johnson には 1 行しかないのに、Jane Smith には 4 行あるなど)。クラウド データベースにアップロードする前に、すべての連絡先にまったく同じ連絡先情報が必要です。そうでないと、重複が作成されます。

同じ人物の記録間でデータを一致させるために長い時間を費やしてきましたが、今度は残りの矛盾がどこに存在するかを見つける必要があります。

LastName フィールドと FirstName フィールドを連結して、このフィールドに同じ値を持つ行を互いに比較できるようにしました。目的は、LastFirst 列 (存在する場合) に同じ値を持つ他のレコードと比較したときに、同じ列内の異なるデータを持つセルを強調表示することです。

これどうやってするの？

これまでのところ、Find Special 機能を使用すると、指定された行または列と比較したときにセルを強調表示できることはわかっていますが、特定の列に対して同じ値を持つ行に対して比較を行う必要があります。

この機能を実行するために、Excel 2010 および 2013 にアクセスできます。

ありがとう！

Answer 1

もっとすっきりした解決策があるかもしれませんが、「FullName」列を作成するために姓と名を連結する必要はありません。

出力のプレビュー

(右側の白いセルは、条件付き書式を実行するために数式が生成する出力を示しています):

これが私たちの公式です

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
   -COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

データセット内の行数と、姓と名の列が A と B にない場合はそれらの位置に合わせて調整することを忘れないでください。

また、名前以外の最初のデータはセル C2 から始まると想定しています。つまり、これは不一致をチェックするデータの左上隅です。必要に応じて、この数式の「C」を編集してください。

条件付き書式の適用

上記の数式をコピーし (必要な変更を加えた後)、不一致をチェックする左上のセルをクリックします (ここでは「C2」と呼びます)。次に、Conditional Formattingリボンのボタンを選択し、 を選択しますNew Rule。

を選択しUse a formula to determine which cells to format、数式をテキストボックスに貼り付けます (スペースがないことを確認してください。上からコピーした場合は、スペースをいくつか削除する必要があります)。これで、Format不一致をどのように強調表示するかを設定するためにクリックできます。私は (派手な!) 赤で塗りつぶすことにしました。OKスプレッドシートに戻るまでクリックします。

セル C2 に不一致がない場合、何も起こらなかったように見えるかもしれませんが、それはルールをデータセット全体に適用する必要があるためです。C2 を選択したまま、リボン メニューManage Rulesから をクリックしますConditional Formatting。

これで、不一致をチェックしたい範囲全体を選択できるようになりました。ボックス内でApplies toクリックしてドラッグし、比較したいものをすべて選択します (行がたくさんある場合は、セル参照を入力すると高速になります=$C$2:$Z$999)

「OK」をクリックすれば完了です。

使い方

この数式は、COUNTIFS()その人物の行数をカウントするために使用します。

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)

次に、その人の名前と、チェックしている列の値を持つ行の数を計算します。すべての行が同一であれば、この数は最初の行と正確に一致するはずですCOUNTIFS()。

COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

後者を前者から減算し、すべての行が一致する場合、数式は 0 を出力し、条件付き書式は実行されません。ただし、何かが異なる場合は、出力は 1 以上になり、条件付き書式がトリガーされます。

注記

空白を処理するために、最終COUNTIFS()基準をステートメントで囲む必要がありました。空白はあまり好まれません (空白を 0 としてカウントするか、"" としてカウントするかが不明なようです。奇妙です)。IF()COUNTIFS

---

ファイルのダウンロード

このサンプル文書はダウンロード可能。