☰

突出顯示兩個 Excel 電子表格之間的差異

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting
突出顯示兩個 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 中的訂單號碼的行。

然後在接下來的兩列中，您需要=INDEX(SheetName!C:C,$X1) =INDEX(SheetName!D:D,$X1).這將傳回該行中另一張工作表的計數和日期。

然後，在您想要的最後一列中=And(Y1=C1,Z1=D1)。如果一切都相同，這將傳回 TRUE。

然後，如果您願意，可以使用條件格式來突出顯示。

如果您願意，這一切都可以壓縮為公式，並用作條件格式化條件，此外，vlookup 在這裡也可以像索引匹配一樣工作。我把它們全部分開，因為我認為這樣更容易理解正在發生的事情以及為什麼它會起作用，但我建議你嘗試將其濃縮，作為練習。

我希望這有幫助。

請注意，如果訂單號碼在查找清單中重複，則此操作將不起作用，因為它只會查找表中的第一個符合項目。

答案2

您可以使用「使用公式決定要設定格式的儲存格」的條件格式來完成此操作。而簡短的版本，您需要在位於 Sheet1 上的第一個表格和位於 Sheet2 上的另一個表格（均從 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
  • Sheet1 包含您的第一個表，其中 A、C 和 D 列與 Sheet2 A、B 和 C 列上的第二個表進行比較。
  • 在第一個工作表上作為條件格式設定區域，您選擇$A:$A 或$A2:$AXX （其中XX 是足夠高的數字以包含所有值） - 如果上述等式為真，則這只與顏色有關

當它不能按“預期”工作時：

  • 如果第二個表中有多個相同的訂單號，則只尋找並比較第一個符合的訂單號
  • 透過應用於 Sheet2 表，它不會傳回正確的顏色，需要更改一些引用值

使用的功能：

  • AND()- 僅當所有參數都為 true 時才傳回 true
  • MATCH()- 尋找選定範圍內的值（在我們的例子中為列）
  • INDEX()- 透過明確說出行號和列號，從矩陣/範圍返回儲存格引用
  • ROW()- 不帶任何參數返回目前行號

現在讓我們來看看一些元件：

  • INDEX(Sheet1!$A:$A;ROW();1)當條件格式運行時，這將傳回目前比較的儲存格引用
  • MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)在這裡，我們在第二個工作表上尋找與當前單元格匹配的單元格，如果匹配，則返回值大於 0，否則給出警告/錯誤。
  • INDEX(Sheet1!$A:$D;ROW();3)- 我們在此處尋找第一個表 (Sheet1) 的第三列（別名 C 列）值。
  • INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2)- 從 Sheet2 表中取得第二列。我們已經有了放置訂單 ID 的行，該方程式位於該函數的中間，用於確定行。
  • 第三個AND()參數與前兩點相同，只是更改列號。

筆記：

  • 為了找到正確的行，我只使用列：Sheet1!$A:$A和Sheet2!$A:$A
  • 當我需要取得另一列時，我需要將該列擴展為矩陣/表，其中最後一列至少是我要引用的最後一列，如果是sheet1，則為第四列，別名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 行，但在第二個選項卡上則位於第7 行，請將公式修改為AND(Sheet1!$A4=A7 ...) 等。

讓我知道是否有什麼需要澄清的！

相關內容