這是我們的公式

Question

我需要有條件地格式化同一列之間具有不同值的單元格，如果它們對於特定列的值是相同的。

設想

我的電子表格是聯絡人資料庫，其中每一行都是聯絡人記錄，每列包含每個聯絡人記錄的一種資料類型（姓氏、街道 1、街道 2、城市、州等）。

此電子表格是我們公司不同辦公室保存的聯絡資訊的合併 - 我們已將所有聯絡資訊合併到同一來源中，以便我們可以就任何差異達成一致，並將所有聯絡人資訊保留在同一位置。

有些聯絡人只有一筆記錄，例如當我們只有一個辦事處擁有該聯絡人的資料時，因此它不會與我們從任何其他辦事處獲得的資料不一致；其他聯絡人有幾個記錄（即 Fred Johnson 可能只有一行，而 Jane Smith 可能只有四行）。每個聯絡人都需要具有完全相同的聯絡資訊，然後我們才能將其上傳到雲端資料庫，否則會建立重複項。

我們花了很長時間嘗試在同一個人的記錄之間達成一致數據，現在我們需要找出剩餘差異存在的位置。

我連接了 LastName 和 FirstName 字段，以便可以相互比較該字段中具有相同值的行 - 目標是在與具有相同值的其他記錄進行比較時，突出顯示同一列中具有不同資料的單元格。如果有）。

我怎樣才能做到這一點？

到目前為止，我知道“查找特殊”函數可以在與指定的行或列進行比較時突出顯示單元格，但我需要對特定列具有相同值的行進行比較。

我可以存取 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）

點選“確定”即可！

怎麼運作的

此公式用於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()標準包含在一個IF()聲明中來處理空白 -COUNTIFS不太喜歡空白（似乎不確定是否將它們算作 0 或“”。奇怪）。

---

文件下載

該範例文件也是可供下載。