☰

Como posso contar valores únicos em uma coluna com base em critérios de duas outras colunas?

tag-icon • tag-icon microsoft-excel-2010
Como posso contar valores únicos em uma coluna com base em critérios de duas outras colunas?

Estou tentando contar o número de entradas exclusivas na coluna A, onde a coluna C diz NÃO e a coluna D diz SIM. Contudo, não posso conceber uma fórmula para o fazer com base em dois critérios diferentes. Como eu poderia fazer algo assim?

Por exemplo, quero saber quantos países únicos assistiram a um evento sob demanda, mas não ao vivo, que seria 4 no exemplo a seguir:

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

A partir das fórmulas sugeridas na resposta abaixo consegui fazer com que o Excel aceitasse a fórmula a seguir, mas isso não retorna um valor. O objetivo disso é inserir 1 na coluna F se E for igual a 1. Qualquer ajuda será apreciada:

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

Responder1

Você consideraria adicionar uma fórmula que simplesmente combine os valores de ambas as colunas e outra que conte valores novos/exclusivos?

Fórmula para E=$C:$C&$D:$D

Fórmula para F =IF(COUNTIF(E$1:E2,E2)=1,1,0)(este é um exemplo de célula 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

Responder2

Você pode usar a seguinte fórmula de matriz para obter a contagem de registros exclusivos que atendem a todos os critérios. Cole o seguinte na barra de fórmulas e pressione 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")))

É longo e um pouco repetitivo, mas deve funcionar para você. Esta fórmula usa o 1/COUNTIF(...)truque que aparecemuitos vezesneste site no passado para contar registros únicos. Tive que introduzir a IFcondição repetitiva para evitar #DIV/0!erros.

O que a fórmula faz:

SUMPRODUCTcom um argumento de array apenas adiciona os elementos do array. Os termos de multiplicação vão para 0 se uma das condições não for atendida e 1 se ambas forem atendidas. A divisão por COUNTIFSé uma forma de dimensionar esse valor para obter a contagem única. Por exemplo, se houvesse dois registros GBsob demanda, mas não ao vivo, cada um desses registros seria reduzido para contar como 1/2um registro na soma total. Se houvesse três desses registros, cada um contaria como 1/3. Dessa forma, quando a soma somar esses registros, a soma total para GBOn Demand, mas não para Live, será 1, por exemplo, 1/2+ 1/2= 1. É assim que a singularidade é capturada.

Responder3

crie um Makro e nomeie-o como "CountMyUniqueEntries", abra o Editor VBA e copie o seguinte trecho de código no Módulo1:

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

talvez você tenha que ativar primeiro o menu "Ferramentas do desenvolvedor" nas "Opções"

Responder4

esta tende a ser uma fórmula bastante complexa. Vou dividi-lo em pedaços, para ficar mais claro e óbvio como isso funciona.
você já tem 4 colunas A, B, C, D. Você precisará de mais quatro colunas E, F, G, H.

Na coluna E, copie esta fórmula para cada célula (copie usando o sinal + na parte inferior direita da primeira célula):

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

Na coluna F, copie esta fórmula para a primeira célula:

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

selecione todas as células da coluna F pressione "F2" e depois pressione "Ctrl"+"Shift"+"Enter"

na coluna G copie esta fórmula para cada célula:

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

use uma célula extra, talvez na coluna H e some os 1 de G junto com:

=SUM(G:G)

divirta-se

informação relacionada