表1

Question

我有一個包含 2 個工作表的 Excel 電子表格。第一個只是標題行和一列項目名稱。第二個是項目組列表，在最左邊的列中包含標題行和標題，後續的每個行都是另一個工作表中的一個或另一個項目：

Sheet1:            Sheet2:
+-------+--+--+    +-------+-------+-------+-------+-------+
| Item  |  |  |    | Group | Item1 | Item2 | Item3 | ...
+-------+--+--+    +-------+-------+-------+-------+-------+
| Shirt |  |  |    | A     | Shirt | Hat   | Tie   |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Hat   |  |  |    | B     | Socks | Shirt | SHOES |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Socks |  |  |    | C     | Hat   | Socks |       |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Tie   |  |  |    | D     | Tie   | Tie   | Socks |
+-------+--+--+    +-------+-------+-------+-------+-------+
| ...   |  |  |
+-------+--+--+

我想有條件地格式化“Sheet2”中的所有單元格，以便與“Sheet1”第一列中的值不匹配的任何值都標記為紅色背景；那些這樣做的標記為綠色背景。因此，本範例中從 B2 開始的所有儲存格除了值「SHOES」之外都是綠色的。下面的值沒有輸入任何內容，因此根本不會被格式化。

我嘗試過的綠色格式規則是：

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)

對於紅色，大致相同：

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)

這兩個規則都「應用於」某種任意範圍（我希望它應用於整個工作表，減去最上面和最左邊的行/列）：

=$C$3:$E$10,$C$36:$Q$50,$E$11,$C$11,$C$2,$E$2:$Q$2,$C$12:$E$35,$F$3:$Q$35

這個半有效，但結果是不可預測的。有些值會以我的預期突出顯示，但僅針對幾行，而其他值則不突出顯示。也許我的範圍不知何故不正常，但我不再像以前那樣頻繁地使用 Excel。有人可以幫忙嗎？

謝謝！

Answer 1

正如 Doktoro Reichard 所說，你想使用條件格式去做這個。在這種特定情況下，您需要三個規則：

1. 若儲存格為空白，請勿變更背景
2. 如果儲存格有匹配項，則將背景設為綠色
3. 如果儲存格沒有符合項，則將背景設為紅色

_{抱歉，我的 Excel 是日文的。今天是多語日。}

為此，我們需要 3 個公式，這些公式將為每個條件傳回TRUEor 。FALSE我假設您的數據如下所示：

表1

表2

規則1

以下公式將傳回儲存格是否為空白。我已選擇

=ISBLANK(B2)

請注意，我選擇了B2:D5具有相對引用的單元格。這將應用相同的公式來更改所選範圍中每個單元格的單元格引用。當此條件成立時，將背景顏色設為白色（或任何您喜歡的顏色）。

規則#2

以下公式將傳回工作表 1 的清單中是否存在完美匹配：

=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))

規則#3

以下公式將傳回工作表 1 的清單中是否存在完美匹配：

=ISERROR(MATCH(B2,Sheet1!$A:$A,0))

命令

最上面的規則將首先執行。因此，由於所有空白單元格都將不匹配，因此您需要先放置空白規則。 #2 和 #3 的順序並不重要（它們永遠不會重疊）。

Answer 2

看來您需要的是儲存格條件格式。

這裡有一個關聯描述這是什麼。

我的圖片來自葡萄牙語版本的Excel 2003，但Office 2010 上也應該有該功能。事情在一張紙上。

首先，表格：

您想要做的是將第二個表中的元素與第一個表中的所有元素進行比較。所以，你需要寫一個像這樣的函數：

=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))

所做EXACT()的就是比較兩個文字字串。如果裡面的任何邏輯條件是的話，所做OR()的就是成為。TrueTrue

話雖這麼說，然後您從第二個表中選擇所有單元格，然後按條件格式，如圖所示。

在了解 Office 2010 及其新的功能區介面後，您應該在「格式」窗格中找到這一點。如果我沒記錯的話，它是一個圖示。

點擊該圖示將出現一個與此類似的視窗：

在那裡，您首先需要選擇您想要的公式，然後貼上我之前提到的公式。若要將所有驗證條件的儲存格設為綠色，只需變更格式即可。要進行紅色格式化，只需使用NOT(OR(...));這將返回您設定的條件的倒數。

為了確保它不會格式化沒有任何內容的儲存格，請設定第三個條件，其中公式為 ISBLANK(E3)（E3 位於左上角）。

我寫完初稿後檢查過的事情是優先順序。至少在我的版本中，條件 1 在條件 2 之前得到驗證，依此類推。因此，您應該以不干擾自身的方式對條件進行排序。所以：

1st condition - =ISBLANK(E3)
2nd condition - =OR(...)
3rd condition - =NOT(OR(...))

因此，你應該有一個像這樣的視窗：

嘗試根據您的情況進行調整。如果我沒記錯的話，它與我所展示的並沒有什麼不同。結果應該是這樣的：