Emulando una función CONTAR.UNICOS() mediante fórmulas matriciales

En Calc (al igual que en Excel) hay muchas funciones para contar elementos de un rango: CONTAR(), CONTARA(), CONTAR.SI(), etc, pero ¿no habéis encontrado a faltar un CONTAR.UNICOS() que os permita obtener el número de elementos sin repeticiones en un rango dado?

Esto lo podremos emular fácilmente mediante el uso de fórmulas matriciales.  Podéis ir directamente al final del artículo para ver la fórmula que hay que usar, pero como este artículo tiene finalidad didáctica, os cuento con un poco de detalle como se llega a la formulación adecuada.

Fórmulas matriciales... (Hoygan! y ezo ke es?) 

Las fórmulas matriciales son cálculos introducidos de un modo especial (con Ctrl+Mayúsc+Intro) que permiten aplicar una sóla fórmula a todo un bloque de celdas.  Podéis identificar que en una hoja se está usando una fórmula matricial cuando en la barra de fórmulas veáis que la fórmula aparece entre llaves: {=la_fórmula_que_sea}. Algunas funciones como la función FRECUENCIA() exigen ser introducidas como fórmula matricial.  Si aplicamos una función matricial a todo un rango de celdas, no se puede modificar la fórmula individualmente en ninguna de las celdas, por lo que, en cierto modo, tiene una función de protección frente a errores.  

Sintetizando un poco las ventajas de uso de las fórmulas matriciales:

• Permiten hacer operaciones en bloque con grandes rangos de celdas.
• Ahorran memoria (ocupan más tamaño mil celdas con una fórmula cada una, que una sóla fórmula matricial que opera con las mil celdas)
• Y permiten resolver de forma elegante problemas que exigirían cálculos intermedios o más complicados sin su ayuda (por ejemplo, un SUMAR.SI() o CONTAR.SI() donde necesitemos más de un criterio).

Este último caso es el que vamos a utilizar para mostrar una aplicación práctica.  

Si deseáis profundizar en el tema, os recomiendo las excelentes guías del maestro Antonio Roldán Martínez, tanto para Calc como para Excel.

Planteando el problema

Hace algún tiempo me vi en la necesidad de conocer cuantos días había dedicado a un proyecto.  Pero los registros de la hoja donde controlaba los proyectos, correspondían a tareas con una hora de inicio y una hora final, por lo cual aparecían varias filas correspondientes al mismo día.  Ahí es donde comencé a buscar infructuosamente si existía una función CONTAR.UNICOS().

Con fines didácticos plantearemos un problema similar más simplificado, y luego veremos como apliqué esta técnica para resolver mi necesidad.  La hoja de cálculo con los ejemplos que se muestran la podéis descargar del adjunto al final del artículo (tanto en ODF como en XLS).  Podéis ver las capturas de pantalla a tamaño real pulsando sobre las miniaturas.

Disponemos de una hoja de cálculo con un rango de datos con diferentes nombres repetidos:

Podemos ver como pese a que hay dieciséis celdas en este rango, tan sólo hay cinco nombres en él:

Usando CONTAR.SI() podemos ver cuantas repeticiones hay para cada uno de los nombres:

Creando nuestra fórmula matricial

En el ejemplo, ¿qué ocurriría si asignamos un "peso" a cada elemento consistente en la unidad dividido por el número de repeticiones?

Es decir, a cada elemento "José", le damos un peso de 1/6; a "Juan", 1/2; a "María", 1/3 y así sucesivamente. De esta manera si multiplicamos el número de repeticiones de cada uno por su "peso", obtendremos la unidad.  Como todas las celdas de un elemento pesarán 1, la suma final nos mostrará el número de elementos con que estamos trabajando.

Y esto es lo que hemos aplicado en esta primera aproximación a la solución:

{=SUMA(1/CONTAR.SI(datos;datos))}

Recordad, en la fórmula no hay que escribir las llaves {...}.  Éstas aparecerán al introducir la fórmula con Ctrl+Mayúsc+Intro.

Dentro de la fórmula, CONTAR.SI(datos,datos) está contando para cada celda las repeticiones del mismo elemento que hay en todo el rango.  Ese es el denominador que por el cual se divide la unidad.  

Esta expresión en una fórmula no matricial provocaría un error de #VALOR! pues CONTAR.SI() espera que su segundo argumento sea un simple criterio, no un rango de criterios.  

Pero al introducirla como matricial, lo que hace CONTAR.SI() es devolver una matriz del mismo tamaño que el rango de datos con el número de repeticiones correspondiente a cada elemento.  

Éste es el valor por el cual vamos a dividir la unidad, obteniendo una matriz con el "peso" de cada elemento tal como habíamos visto antes.  

Finalmente esta matriz es la que pasamos a la función SUMA(), con lo cual obtenemos el resultado deseado.

¡Socorro! Las celdas vacías me provocan un siniestro #DIV/0! (error de división por cero)

Hemos de prever que en nuestro rango de datos, no necesariamente todas las celdas tendrán contenido y de darse este caso, se provoca el típico error de división por cero.

No hay problema, podemos mejorar nuestra fórmula contemplando esta posibilidad:

{=SUMA(SI(ESBLANCO(datos);0;1/CONTAR.SI(datos;datos)))}

Una vez más, os recuerdo la necesidad de introducir la fórmula con Ctrl+Mayúsc+Intro para que sea correctamente tratada como una fórmula matricial.

Una aplicación práctica

Ahora, que ya hemos visto como usar una fórmula matricial para emular el comportamiento de una hipotética función CONTAR.UNICOS(), podemos aplicarla en el control de seguimiento de proyectos que exponía en el planteamiento.

Para su estudio, podéis descargar las hojas de cálculo en formato ODF y XLS del pie de este artículo.

Espero que hayáis encontrado útil esta técnica y la apliquéis a vuestros proyectos.

 

Adjunto	Tamaño
CONTAR_UNICOS.ods	21.28 KB
CONTAR_UNICOS.xls	30.5 KB