Как подсчитать уникальные значения в одном столбце на основе критериев в двух других столбцах?

Question

Я пытаюсь подсчитать количество уникальных записей в столбце А, где в столбце С указано НЕТ, а в столбце 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, чтобы принять следующую формулу, но это не возвращает значение. Цель этого состоит в том, чтобы ввести 1 в столбце F, если E равно 1. Любая помощь будет оценена:

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

Answer 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

Answer 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 с On Demand, но не Live, каждая из этих записей была бы уменьшена до 1/2 записи в общей сумме. Если бы было три таких записи, каждая считалась бы 1/3 . Таким образом, когда сумма добавляет эти записи, общая сумма для GB On Demand, но не Live будет равна 1, например, 1/2+1/2 = 1 . Вот как уникальность фиксируется.

Answer 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

может быть, вам нужно сначала активировать меню «Developer-Tools» в "Options"

Answer 4

Можете ли вы создать еще один столбец с формулой вида

IF(<Condition>,Actual Value,Placeholder Value)

А затем взять число различных значений в этом столбце и вычесть из него 1?

Таким образом, если ваше условие истинно, фактическое значение достигает этого столбца, в противном случае - значение заполнителя. Затем вы можете взять подсчет различных значений

Answer 5

это, как правило, довольно сложная формула. Я разделю его на части, чтобы было понятнее и понятнее, как это работает.
у вас уже есть 4 столбца A, B, C, D. Вам понадобится еще четыре столбца 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 и суммируйте 1 с G вместе с:

=SUM(G:G)

повеселись