☰

¿Cómo puedo contar valores únicos en una columna según los criterios de otras dos columnas?

tag-icon • tag-icon microsoft-excel-2010
¿Cómo puedo contar valores únicos en una columna según los criterios de otras dos columnas?

Estoy tratando de contar el número de entradas únicas en la columna A, donde la columna C dice NO y la columna D dice SÍ. Sin embargo, no puedo idear una fórmula para hacerlo basándose en dos criterios diferentes. ¿Cómo podría hacer algo como esto?

Por ejemplo, quiero saber la cantidad de países únicos que han visto un evento a pedido pero no en vivo, que sería 4 en el siguiente ejemplo:

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 de las fórmulas sugeridas en la respuesta a continuación, logré que Excel aceptara la siguiente fórmula, pero esto no devuelve un valor. El objetivo de esto es ingresar un 1 en la columna F si E es igual a 1. Se agradecería cualquier ayuda:

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

Respuesta1

¿Consideraría agregar una fórmula que simplemente combine valores de ambas columnas y otra que cuente valores nuevos/únicos?

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

Fórmula para F =IF(COUNTIF(E$1:E2,E2)=1,1,0)(este es un ejemplo para la celda (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

Respuesta2

Puede utilizar la siguiente fórmula matricial para obtener el recuento de registros únicos que cumplen con todos los criterios. Pegue lo siguiente en la barra de fórmulas y presione 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")))

Es largo y un poco repetitivo, pero debería funcionar para ti. Esta fórmula utiliza el 1/COUNTIF(...)truco que se muestra.muchos vecesen este sitio en el pasado para contar registros únicos. Tuve que introducir la IFcondición repetitiva para evitar #DIV/0!errores.

Qué hace la fórmula:

SUMPRODUCTcon un argumento de matriz simplemente agrega los elementos de la matriz. Los términos de multiplicación llegan a 0 si no se cumple alguna de las condiciones y a 1 si se cumplen ambas. La división por COUNTIFSes una forma de escalar este valor para obtener el recuento único. Por ejemplo, si hubiera dos registros GBcon On Demand pero no en vivo, cada uno de esos registros se reduciría para contar como 1/2un registro en la suma total. Si hubiera tres registros de este tipo, cada uno contaría como 1/3. De esa manera, cuando la suma suma estos registros, la suma total para GBOn Demand pero no en Vivo será 1, por ejemplo, 1/2+ 1/2= 1. Así se capta la singularidad.

Respuesta3

cree un Makro y asígnele el nombre "CountMyUniqueEntries", abra el editor VBA y copie el siguiente fragmento de código en el 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

tal vez tengas que activar primero el menú "Herramientas de desarrollador" en las "Opciones"

Respuesta4

Esta tiende a ser una fórmula bastante compleja. Lo dividiré en pedazos, para que quede más claro y obvio cómo funciona.
ya tienes 4 columnas A, B, C, D. Necesitarás cuatro columnas más E, F, G, H.

En la columna E, copie esta fórmula en cada celda (copie usando el signo + en la parte inferior derecha de la primera celda):

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

En la columna F copie esta fórmula a la primera celda:

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

seleccione cada celda de la columna F, presione "F2" y luego presione "Ctrl"+"Shift"+"Enter"

en la columna G copie esta fórmula en cada celda:

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

use una celda adicional, tal vez en la columna H y sume los 1 de G junto con:

=SUM(G:G)

divertirse

información relacionada