martes, 3 de julio de 2012

Contar Registros Únicos

"Tengo más de 1.000 registros (números) en una columna. Muchos de ellos están repetidos y lo que me gustaría es poder contar, con fórmulas, cuántos son únicos".

Esta es la pregunta de mi querido hermano Santi a quién, evidentemente, le dedico este post (que generoso soy...). Hay diversas soluciones. Una de ellas sería haciendo uso de los Filtros Avanzados, como ya describí en mi post "Copiar Registros Únicos". También podemos generar una Tabla Dinámica y aplicar la función CONTAR para ver el número de registros únicos. Pero buscamos una solución con fórmula (no con herramientas). Para ello vamos a utilizar las siguientes funciones: Y, CONTAR, CONTAR.SI y Fórmulas Matriciales. Partimos del siguiente ejemplo:

Lo que vamos a hacer en el rango C3:C20 es comprobar si cada uno de los valores que hay en el rango B3:B20 es único o no. Para ello vamos a utilizar una fórmula matricial que, como ya sabéis, se caracteriza porque al finalizar pulsamos Ctrl + Shift + Enter. Nos situamos en la celda C3 y escribimos:
=Y(B3<>$B$2:B2) y acabamos pulsando Ctrl+Shift+Enter, lo que convierte esta fórmula en:
{=Y(B3<>$B$2:B2)}   Copiamos C3 hasta C20.

En esta fórmula hay varias cuestiones importantes:

1. Al poner dólares (referencias absolutas) en el primer término del rango B2:B2, quedando como $B$2:B2 conseguimos que cuando copiemos esta fórmula hacia abajo el rango se vaya ampliando, ya que el origen se mantiene fijo ($B$2) mientras que el segundo término se va ampliando a B3, B4, etcétera.

2. Con la fórmula matricial conseguimos comparar una celda contra todas las que le "quedan por encima". Por ejemplo, en la celda C8 la fórmula que aparecerá será:
{=Y(B8<>$B$2:B7)}
Esta fórmula está comprobando si la celda B8 es distinta de B2, B3, B4, B5, B6 y B7. En el caso que esto sea cierto excel devolverá el resultado de VERDADERO (FALSO en el caso contrario) como se observa a continuación:

Una vez hemos conseguido diferenciar los registros únicos la solución es muy sencilla. Preparamos la siguiente salida de datos:

Escribimos las siguientes fórmulas:
En la celda F3, para contar los registros totales  =CONTAR(B3:B20)
En la celda F4, para contar los registros únicos  =CONTAR.SI(C3:C20;VERDADERO)

Como se puede ver, existen 18 registros en total pero sólo 9 son únicos, a saber: 10, 20, 30, 40, 50, 60, 70, 80 y 90.

He propuesto esta solución porque me parece razonablemente sencilla de comprender y desarrollar. Pero se podría solucionar con una única fórmula como propone JLD en su blog.  La fórmula sería: 
{=SUMA(1/CONTAR.SI(B3:B20;B3:B20))}

Por aquello de no apropiarme de lo que no es mío, puedes encontrar la explicación a esta fórmula en:
http://jldexcelsp.blogspot.com.es/2007/08/contar-valores-nicos-en-un-rango-de.html

5 comentarios:

  1. Disculpa, pero según interpreto el enunciado, el objetivo es detectar si un valor es único o si por el contrario esta repetido, pero en la solución por ejemplo, el 10 figura como único, cuando en realidad hay cuatro registros con 10.

    Creo que una posible solución seria introducir en C3 la formula =SI(CONTAR.SI($B$3:$B$20;B3)>1;"Registro único";"Registro duplicado) y luego copiarla al resto.

    ResponderEliminar
    Respuestas
    1. Muchas gracias por tu aportación. Es cierto que el enunciado puede llevar a confusión. En realidad lo que estamos obteniendo es el número de registros distintos. Gracias de nuevo.

      Eliminar
  2. Hola Kiko!, te expongo lo siguiente, en una sola hoja de Exel tengo 28 cuadriculas formadas por 40 columnas y 40 renglones cada una. En cada espacio de la cuadricula se coloca 1 de 15 valores posibles, ocupando de 3 al 5% de su capacidad.

    La pregunta es ¿es posible aplicar algún método en Exel para contabilizar cuantas veces se repite cada uno de los 15 valores posibles en las 28 cuadriculas?



    Gracias

    ResponderEliminar
    Respuestas
    1. Con la función CONTAR.SI
      suponiendo que tenga la cuadrícula 1 en el rango A1:AN40 (40 filas por 40 columnas).Llamamos a este rango tabla1. Suponiendo que hemos generado números entre el 1 y 15, yo pondría un listado de estos números (del 1 al 15) por ejemplo en AP1:AP15 y en AQ1 haría la fórmula:
      =CONTAR.SI(tabla1;AP1) y copiaría esta fórmula hasta AQ15. De esta manera me dirá cuántas veces aparece en la cuadrícula (tabla1) cada uno de esos valores. Si tenemos varias cuadrículas podemos resolverlo una a una y sumar los CONTAR.SI (o reunirlos todos en una única fórmula).

      Eliminar
    2. Tienes toda la razón!!!, me enfoque erroneamente al tratar de resolver el problema visualizando el total de cuadriculas mensuales.
      Eres un Genio, felicidades!

      Muchas Gracias por tu apoyo.

      Eliminar