Autor: Oscar Cronquist Artículo actualizado en septiembre 10, 2019

Este artículo muestra una fórmula que cuenta valores distintos únicos filtrados de una tabla definida por Excel.

Debra Dalgleish describió en un artículo cómo crear un Línea entre fechas en lista filtrada. Ella modificó una fórmula de Laurent Longre encontrada aquí: Excel E-letter de los expertos del sitio web de John Walkenbach.

Recuerdo un post que hice sobre extracción de valores distintos únicos de una tabla filtrada (udf y fórmula matricial) eso también se inspiró en la fórmula de Laurent Longre.

La Tabla de Excel definida que se muestra en la imagen de arriba se llama Table2. Hay nombres en la columna A. La tabla se filtra con "Finlandia" y "México" en la columna C, la siguiente fórmula de matriz cuenta valores únicos únicos basados ​​en un filtro aplicado a una tabla definida por Excel.

Lo que aprenderás en este artículo.

  • Cree una fórmula que cuente valores distintos únicos en una tabla filtrada utilizando una versión modificada de la fórmula de Laurent Longre.
  • Identifique valores filtrados usando la función SUBTOTAL y OFFSET.
  • Cómo hacer referencia a una columna en una tabla definida por Excel, en una fórmula.
  • Convertir valores booleanos en equivalentes numéricos
  • Convierta valores de texto en números únicos basados ​​en el orden como si hubieran sido ordenados de la A a la Z.
  • Contar números distintos únicos

Fórmula de matriz en A26:

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,  OFFSET(Table2[First Name],  MATCH(ROW(Table2[First Name]),  ROW(Table2[First Name]))-1,  0,  1)), COUNTIF(Table2[First Name], "<"&Table2[First Name]), ""), COUNTIF(Table2[First Name], "<"&Table2[First Name]))>0))

Cómo crear una fórmula matricial

  1. Copie la fórmula de matriz anterior
  2. Haga doble clic en la celda A26 para ver el mensaje.
  3. Pegue la fórmula de matriz en la celda.
  4. Mantenga presionado Ctrl + Shift simultáneamente.
  5. Presione Enter una vez.
  6. Suelte todas las teclas.

Explicando la fórmula matricial en la celda A26.

Paso 1 - Crea una matriz de rangos

Cuando usa una matriz de números en OFFSETEn el segundo argumento se devuelve una matriz de rangos, esto permite evaluar cada valor utilizando Función subtotal.

La Función MATCH crea una matriz de números en secuencia desde 1 hasta tantos como haya valores en la columna 'Nombre' en la tabla definida por Excel Table2.

MATCH (ROW (Table2 [Nombre]), ROW (Table2 [Nombre]))

se convierte en

JUEGO ({2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; ; 20; 21; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16})

y devoluciones

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}

OFFSET (Table2 [Nombre], MATCH (ROW (Table2 [Nombre]), ROW (Table2 [Nombre])) - 1, 0, 1)

se convierte en

OFFSET (Table2 [Nombre], {0; 1; 2; 3; 4; 5; 6; 7; NUMX: Recolección principal; 8, 9)

y devuelve (si presiona F9 en la barra de fórmulas)

{"Fraser"; "Kaya"; "Horace"; "Kelton"; "Linnie"; "Jui"; "Mckinley"; "Emmitir"; "Siena"; "Cyril"; "Tiara"; "Charley"; "Kaya"; "Fraser"; "Fraser"; "Lynna"; "Jui"; "Lynna"; "Damien"; "Spencer"}

o devuelve una matriz de errores #VALOR si usa la función "Evaluar fórmula" que se encuentra en la pestaña "Fórmula" en la cinta.

Paso 2 - Encuentra valores visibles

La Función subtotal es capaz de identificar valores filtrados si utiliza la función DESPLAZAMIENTO demostrada en el paso anterior.

SUBTOTAL (3, OFFSET (Table2 [First Name], MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1))

se convierte en

SUBTOTAL (3, {"Fraser"; "Kaya"; "Horace"; "Kelton"; "Linnie"; "Jui"; "Mckinley"; "Emmit"; "Siena"; "Cyril"; "Tiara"; " Charley ";" Kaya ";" Fraser ";" Fraser ";" Lynna ";" Jui ";" Lynna ";" Damien ";" Spencer "})

y devuelve la siguiente matriz:

{0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 1; 1; 0; 1; 0; 0; 0}

0 (cero) es un valor oculto y 1 es un valor visible en la tabla definida por Excel.

Paso 3 - Calcular rango por orden alfabético

La Función COUNTIF le permite asignar un número único a todos los valores según el orden si se ordenaron de la A a la Z o viceversa. Simplemente agregue un carácter menor o mayor que al segundo argumento en la función COUNTIF.

Este paso es necesario para contar valores distintos únicos, los duplicados tendrán asignado el mismo número, lo cual es útil en este caso.

COUNTIF (Table2 [Nombre], "<" & Table2 [Nombre])

devoluciones

{18; 2; 14; 8; 14; 4; 4; 10; 0; 19; 1; 17; 3; 16; 8; 13; 10; 7; 10; 4}

Paso 4 - Filtra el rango alfabético del valor visible

La Función IF reemplaza los valores filtrados con los números calculados en el paso anterior.

IF (SUBTOTAL (3, OFFSET (Table2 [First Name], MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1)), COUNTIF (Table2 [First Name] , "<" & Table2 [Nombre]), "")

se convierte en

IF ({0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 1; 1; 0; 1; 0; ; 0; 0; 18; 2; 14; 8; 14; 4; 4; 10; 0; 19; 1; 17; 3; 16; 8}, "")

y devoluciones

{""; ""; ""; 8; ""; 4; 4; 10; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""}

Paso 5 - Calcular frecuencia

La Función de frecuencia cuenta cada número en la matriz y devuelve el total en la misma posición relativa que el primer número que ocurre.

FRECUENCIA (IF (SUBTOTAL (3, OFFSET (Table2 [First Name]), MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1)), COUNTIF (Table2 [First Nombre], "<" & Table2 [Nombre], ""), COUNTIF (Table2 [Nombre], "<" & Table2 [Nombre]))

se convierte en

FRECUENCIA ({""; ""; "; 8;" "; 4; 4; 10;" ";" "" "" "" "" ";" ""; ""; ""; 10; ""}, {18; 2; 14; 8; 14; 4; 4; 10; 0; 19; 1; 17;; )

y devoluciones

{0, 0, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, XXUMX, 0, 0, 0, 0, XXUMX, XNUMX, XNUMX, XNUMX, XNUMX

Paso 6: valores de suma mayores que cero

La Función SUMA agrega todos los valores si son mayores que 0 (cero), primero debemos verificar si el total es mayor que cero, se devuelven VERDADERO o FALSO. Luego, necesitamos convertir los valores booleanos a su equivalente numérico para que la función SUMA pueda agregar números y devolver un total.

SUMA (- (FRECUENCIA (SI (SUBTOTAL (3, OFFSET (Table2 [Nombre)], MATCH (ROW (Table2 [Nombre)], ROW (Table2 [Nombre])) - 1, 0, 1)), COUNTIF (Table2 [Nombre], "<" & Table2 [Nombre]), ""), COUNTIF (Table2 [Nombre], "<" & Table2 [Nombre]))> 0))

se convierte en

SUMA (- ({0, 0, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, XNXX, XNXX, 0, 0 ))

se convierte en

SUMA (- ({FALSO; FALSO; FALSO; VERDADERO; FALSO; VERDADERO; FALSO; VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}))

se convierte en

SUM({0;0;0;1;0;1;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0})

y devuelve 3 en la celda A26. Kaya, Fraiser y Jui son nombres distintos únicos y son tres, lo cual es correcto.