☰

如何根據其他兩列中的條件計算一列中的唯一值？

tag-icon • tag-icon microsoft-excel-2010
如何根據其他兩列中的條件計算一列中的唯一值？

我正在嘗試計算 A 列中唯一條目的數量，其中 C 列表示“否”，D 列表示“是”。但是，我無法根據兩個不同的標準設計一個公式來做到這一點。我怎麼能做這樣的事呢？

例如，我想知道點播但未直播觀看過活動的唯一國家/地區的數量，在以下範例中為 4：

country  |  Preview  |  Live  |  On Demand
GB       |  NO       |  NO    |  YES
GB       |  NO       |  YES   |  YES
ES       |  NO       |  YES   |  YES
DE       |  NO       |  NO    |  YES
FR       |  NO       |  NO    |  YES
US       |  NO       |  NO    |  YES

根據下面答案中建議的公式，我已設法讓 Excel 接受以下公式，但這不會傳回值。這樣做的目的是如果 E 等於 1，則在 F 列中輸入 1。

=IF(E=1, IF(FREQUENCY(MATCH(A2:A214, A2:A214, 0), MATCH(A2:A214, A2:A214, 0)) > 0, 1, 0))

答案1

您是否會考慮新增一個簡單地組合兩列值的公式，以及另一個計算新/唯一值的公式？

E 的公式=$C:$C&$D:$D

F 的公式=IF(COUNTIF(E$1:E2,E2)=1,1,0)（這是 fopr 單元格的範例（F2）

country  |  Preview  |  Live  |  On Demand | Combined | New/unique
GB       |  NO       |  NO    |  YES       | NOYES    | 1
GB       |  NO       |  YES   |  YES       | YESYES   | 1
ES       |  NO       |  YES   |  YES       | YESYES   | 0
DE       |  NO       |  NO    |  YES       | NOYES    | 0
FR       |  NO       |  NO    |  YES       | NOYES    | 0
US       |  NO       |  NO    |  YES       | NOYES    | 0

答案2

您可以使用下列陣列公式來取得滿足所有條件的唯一記錄的計數。將以下內容貼到編輯欄中，然後按Ctrl+ Shift+ Enter。

=SUMPRODUCT((C2:C8="NO")*(D2:D8="YES")/IF(COUNTIFS(A2:A8,A2:A8,C2:C8,"NO",D2:D8,"YES")=0,1,COUNTIFS(A2:A8,A2:A8&"",C2:C8,"NO",D2:D8,"YES")))

它很長並且有點重複，但它應該對您有用。這個公式使用了1/COUNTIF(...)所示的技巧許多 次過去在這個網站上計算唯一記錄。我必須引入重複IF條件以避免#DIV/0!錯誤。

公式的作用：

SUMPRODUCT使用一個陣列參數只是將陣列的元素相加。如果任一條件不滿足，則乘法項變為 0；如果同時滿足，則乘法項變為 1。除以 是COUNTIFS縮放該值以獲得唯一計數的一種方法。例如，如果有兩筆記錄用於GB隨選但不是即時記錄，則每筆記錄都會按比例縮小以計1/2為總和中的一筆記錄。如果有 3 筆這樣的記錄，則每筆記錄都算作1/3。這樣，當總和添加這些記錄時，點播而非即時的總和GB將為 1，例如1/2+ 1/2= 1。這就是捕捉獨特性的方式。

答案3

建立一個 Makro 並將其命名為“CountMyUniqueEntries”，開啟 VBA 編輯器並將以下程式碼片段複製到 Module1 中：

Sub CountMyUniqueEntries()
    Dim cells As Range: Set cells = Excel.Selection
    'the selection does only contain data, no column-headers
    Dim i
    Dim uniqueCountries As New Collection
    For i = 1 To cells.Rows.Count
        Dim Live As Boolean: Live = (UCase(cells(i, 3)) = "YES")
        Dim OnDemand As Boolean: OnDemand = (UCase(cells(i, 4)) = "YES")
        Dim country As String: country = UCase(cells(i, 1))
        If Not Live And OnDemand Then
            If Not Contains(uniqueCountries, country) Then
                uniqueCountries.Add country, country
            End If
        End If
    Next
    MsgBox "The number of unique countries is: " & uniqueCountries.Count 
End Sub
Private Function Contains(col As Collection, ByVal entry As String) As Boolean
    On Error Resume Next
    If IsEmpty(col(entry)) Then: 'DoNothing
    Contains = (Err.Number = 0)
    On Error GoTo 0
End Function

也許您必須先啟動“選項”中的“開發人員工具”選單

答案4

這往往是一個相當複雜的公式。我將把它分成幾部分，這樣它的工作原理就會更加清晰明了。
您已經有 4 列 A、B、C、D。

在 E 列中，將此公式複製到每個單元格（使用第一個單元格右下角的 + 號進行複製）：

=IF(C2="NO", IF(D2="YES", 1, 0), 0)

在 F 列中，將此公式複製到第一個儲存格：

=IF(FREQUENCY(MATCH(A2:A8, A2:A8, 0), MATCH(A2:A8, A2:A8, 0)) > 0, 1, 0)

選取F列的每個儲存格按“F2”，然後按下“Ctrl”+“Shift”+“Enter”

在 G 列中將此公式複製到每個儲存格：

=IF(E2=1, IF(F2=1, 1, 0), 0)

使用額外的儲存格，可能在 H 列中，並將 G 的 1 與以下內容相加：

=SUM(G:G)

玩得開心

相關內容