比較 Excel 中的清單內容

比較 Excel 中的清單內容

所以這是我的情況:我正在創建一個工作表,其中有 9 個分組,每組 3 個列表,其中所選項目全部包含在透過資料驗證建立的列表中。我需要做的是為這些3 組中的每一組添加一個分數,其中,如果從資料驗證清單中做出的3 個選擇與先前3 組中所做的選擇是唯一的(請注意,3 組永遠不會是相同的選擇,它們將永遠是彼此獨一無二的)。

基本上,假設第 1 組中有藍色、綠色和紫色 - 給出值 1,因為選擇是唯一的。在第 2 組中,有橙色、黃色和粉紅色 - 也將其值設為 1,因為選擇是唯一的。然而,依此類推,假設在第 9 組中您有洋紅色、紅色和紫色 - 該組將得分 0,因為它不是完全獨特的(在第 1 組中選擇了紫色)。請注意,選擇是在不同時間做出的,並按這些單獨的時間間隔進行評分- 因此,第2 組僅與第1 組進行比較,第3 組與第1 組和第2 組進行比較,依此類推。

我嘗試了類似以下的公式:

=IF(NOT(B1:B3=A1:A3),1,0)

但是,單元格顯示的分數#VALUE!不是 1 或 0。如果它們不等於單元格A1、A2 或A3 的內容,則檢查單元格B3 的內容,如果它們不等於A1:A3 的內容,則該列表的值為 1如果我確實必須單獨執行此操作,我有什麼想法如何表達該公式嗎?會不會是這樣的:

=IF(NOT(A1:A3=B1,NOT(A1:A3=B2,NOT(A1:A3=B3),1,0)))

在Excel中是否可以做到這一點?

我正在使用最新版本的 Excel (Office 365) 和 Windows 10。

答案1

一步一步(以支持任何級別的未來讀者)

使用函數COUNTIF()來了解某個範圍是否包含某個值。 IOW,要檢查 in 中的值是否B1出現在 range 中A1:A3,您可以編寫

=COUNTIF(A1:A3,B1)

您可以透過與 0 比較來轉換為布林結果(含義B1不在 中A1:A3

=COUNTIF(A1:A3,B1)=0

但您還需要檢查B2B3是否出現在A1:A3.您可以使用該AND()功能:

=AND(COUNTIF(A1:A3,B1)=0,COUNTIF(A1:A3,B2)=0,COUNTIF(A1:A3,B3)=0)

B1如果,B2B3中沒有出現,則傳回 True A1:A3。為了使我們能夠跨行複製該函數並調整要檢查的範圍,我們將對第一個單元格的引用更改為固定列引用A1:A3-> $A1:A3。當我們將公式複製到右側時,範圍將增長到$A1:B3$A1:C3依此類推,最後到$A1:I3第九列

上面的公式傳回一個布林結果,TrueFalse。您指出您需要 1 或 0,這可以透過IF()在前面的公式周圍添加一個函數來輕鬆完成。

我們現在可以將公式寫入儲存B4

=IF(AND(COUNTIF($A1:A3,B1)=0,COUNTIF($A1:A3,B2)=0,COUNTIF($A1:A3,B3)=0),1,0)

然後複製到單元格C4:I4

由於根據您的定義,每組 3 個都是唯一的,因此 中不需要公式A4,您只需將其設為 1 即可。

更新

筆記!範圍內的空白儲存格如C1:C31 所示。您可以透過向函數新增第四項來做到這一點AND()COUNTBLANK((B1:B3)=0。的條目B4將是

=IF(AND(COUNTIF($A1:A3,B1)=0,COUNTIF($A1:A3,B2)=0,COUNTIF($A1:A3,B3)=0,COUNTBLANK(B1:B3)=0),1,0)

在複製到單元格之前C4:I4

相關內容