☰

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

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

решение3

Создайте макрос и назовите его «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, 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 вместе с:

=SUM(G:G)

веселиться

Связанный контент