☰

如何根据另外两列中的标准计算一列中的唯一值？

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)（这是单元格 (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总和中的一条记录。如果有三个这样的记录，则每条记录都将算作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。您还需要 4 列 E、F、G、H。

在 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)

玩得开心

相关内容