Buscar el valor más frecuente en una lista con la función MODA

Cuando necesitamos encontrar el valor que se repite con más frecuencia en una lista o rango de celdas podemos utilizar la función MODA (o MODE para la versión en inglés).

La función solo toma un parámetro que es el rango donde se encuentran los valores a evaluar. Como resultado se devuelve el valor que mas aparece en la lista.

La principal limitación de esta función es que solo funciona con valores numéricos y muchas veces en Excel necesitamos buscar el valor más frecuente sobre un campo de texto. Por ejemplo supongamos que disponemos de una lista de artículos vendidos a clientes y queremos saber quien fue el cliente que ha realizado mas pedidos, dado que el nombre de cliente no es un número no podríamos usar dicha función.

Para evitar este problema podemos usar una combinación de formulas que nos permitirá utilizar la función MODA sobre un rango de valores que no sean numéricos.


Veamos el ejemplo


Utilizaremos la siguiente fórmula para buscar el valor más frecuente

{=INDICE(B2:B10;MODA(COINCIDIR(B2:B10;B2:B10;0)))}

La idea es que utilicemos algún valor numérico que identifique unívocamente al elemento. En este caso utilizamos la posición donde aparece por primera vez el elemento dado que es un valor numérico. Luego con la función índice devolvemos el elemento ubicado en esa posición.

Para ubicar la posición que ocupa cada uno de los elementos en la lista utilizamos la función COINCIDIR. Recuerden que en el caso de estar repetido un valor siempre se devolverá la primera ubicación.


Con el array de posiciones devuelto por COINCIDIR utilizamos la función MODA que devuelve aquel número que más se repite.


Finalmente con la función INDICE devolvemos el nombre que se encuentra en esa posición.

Combinando una serie de formulas que vimos en artículos anteriores podemos ampliar o resolver algunos problemas que no son posibles con la utilización de una sola formula

Para ver el archivo del ejemplo pueden hacer clic aqui.

Evaluar formulas en Excel

Cuando tenemos formulas complejas y no funcionan como necesitamos es difícil encontrar el problema. Para ayudarnos con esta tarea Excel posee una herramienta Evaluar Fórmulas que nos permite seguir paso a paso cada uno de los cálculos que Excel realiza hasta llegar al valor final.

Esta herramienta es de especial importancia cuando tenemos funciones con matrices o arrays ya que en éstas Excel realiza muchos cálculos que están incluidos en una sola fórmula.

En Excel 2007 la herramienta se encuentra en la ficha fórmulas dentro del grupo Auditoría de fórmulas


En Excel 2003 la herramienta se encuentra en el menú Herramientas, auditoría de fórmulas, Evaluar fórmulas.

Una vez seleccionada la herramienta podemos evaluar la fórmula paso a paso haciendo clic en el botón Evaluar.


Veamos un ejemplo


Utilizaremos la fórmula publicada en el articulo "Utilizando Matrices o Arrays en Excel" para sumar los 3 valores más grandes de una lista :

{=SUMA(K.ESIMO.MAYOR(A1:A10;FILA(INDIRECTO("1:3")))}

En el primer paso Excel resuelve la formula INDIRECTO("1:3") lo que nos devuelve la referencia para la siguiente fórmula


En el segundo paso Excel resuelve FILA("$1:$3") al ser una fórmula array esta se evalúa tres veces y su resultado es un array {1,2,3}


En el tercer paso Excel resuelve la fórmula K.ESIMO.MAYOR(A1:A10;{1,2,3}), nuevamente al tratarse de una formula array Excel la evalúa tres veces devolviendo el mayor, el segundo mayor y el tercer mayor en un nuevo array


En el último paso Excel resuelve la fórmula SUMA, sumando todos los valores devueltos en el array del paso anterior.

Utilizando matrices o arrays en Excel

Un array puede definirse como una colección de elementos. En Excel podríamos decir que un rango de celdas es un array pero también podemos crear nuestros propios arrays o funciones de arrays. Esto último podría definirse como una colección de elementos a los que les aplicamos una formula.

Las funciones de array las podemos utilizar para realizar cálculos complejos que de otra forma requieren ingresar varias formulas en varias celdas. De esta forma podemos reducir el ingreso de formulas como asi también los posibles errores.

Para ingresar una formula array debemos presiones Ctrl + Shift + Enter y no solo la tecla enter. Excel agregará { } en la formula indicando que es una formula array. Si no se presiona Ctrl + Shift + Enter Excel la considerara una formula normal y por lo tanto no funcionará como deseamos.

Si bien las formulas array son muy utiles no son muy utilizadas y una razón es que son mas difcilis de entender ya que una sola celda se realizan varios cálculos y cuando no funciona correctamente es mas difícil seguir los resultados.

Array constantes

En Excel podemos definir un array que esta compuestos de valores constantes y el mismo puede ingresarse en una celda o se puede asignarse a un nombre de la misma forma que lo hacemos para rangos de excel.

Por ejemplo podemos definir un nombre llamado Dias e ingresar : ={"Lunes";"Martes";"Miercoles";"Jueves";"Viernes"}.
Para los arrays constantes es necesario ingresar { }.

Para ingresar el array constante debemos ir a la ficha Formulas y presionar en el botón Definir Nombres



Para obtener un valor particular del array de constantes podemos utilizar la función INDICE de la siguiente forma : =INDICE(Dias, 1) para devolver el primer elemento. El segundo parámetro puede cambiarse para devolver el valor deseado.


Veamos algunos ejemplos de funciones con arrays

Sumar los diez numeros mas grandes de un rango
{=SUMA(K.ESIMO.MAYOR(A1:A100,{1;2;3;4;5;6;7;8;9;10))}

Si tenemos que sumar gran cantidad de numeros podemos usar esta alternativa :

{=SUMA(K.ESIMO.MAYOR(A1:A100,FILA(INDIRECTO("1:10")))}

Para mas información sobre como Excel resuelve esta fórmula puedes hacer clic aqui.

Sumar con condiciones
{=SUMA(SI(A1:A10>0;A1:A10))}

Si bien este resultado puede obtenerse utilizando la función SUMAR.SI con el array podemos definir condiciones mas complejas e incluso varias condiciones.

El tema de los arrays es muy extenso por lo tanto en as próximas semana agregaré otra nota con ejemplos.

Conteo de elementos con varios criterios CONTAR.SI.CONJUNTO

En el artículo de hoy explicaré algunos ejemplos sobre como utilizar la función CONTAR.SI.CONJUNTO (CONUNTIFS para la versión en inglés) que funciona de forma similar a SUMAR.SI.CONJUNTO (explicada en un articulo anterior).

La función CONTAR.SI.CONJUNTO permite contar la cantidad de registros que cumplen con un criterio definido por el usuario. Esta función permite definir mas de una condición a diferencia de la función CONTAR.SI.

Se deben proporcionar parámetros de a pares y los primeros dos se utilizan de la siguiente forma. El primero es el rango donde se desea aplicar el criterio y el segundo es la condición que se desea cumplir. Esta función acepta un máximo 127 criterios diferentes con lo cual se pueden definir criterios muy específicos.

Si bien se pueden definir varios parámetros de criterio estos deben tener la misma cantidad de celdas para poder ser utilizados en una única función.


Veamos algunos ejemplos


Disponemos de una lista de productos que fueron comprados por un grupo de clientes durante los meses de enero a marzo.


Si deseamos saber la cantidad de ventas que se efectuaron al cliente Gustavo utilizamos la formula =CONTAR.SI(B2:B100;"=Gustavo")

Si necesitamos solo contar las ventas que se efectuaron entre el 01/01/2009 y el 15/01/2009 para el cliente Gustavo utilizamos la formula : =CONTAR.SI.CONJUNTO(B2:B100;"=Gustavo";A2:A100;">=01/01/2009";A2:A100;"<=15/01/2009")

Para versiones anteriores a Excel 2007 podemos utilizar una formula matricial que nos permite obtener el mismo resultado : {=CONTAR(SI(($B$2:$B$100="Gustavo")*($A$2:$A$100>=FECHA(2009;1;1))*($A$2:$A$100<=FECHA(2009;1;15));1))}

Recuerden que las formulas matriciales se deben ingresar presionando Ctrl + Shift + Enter. Para mas información sobre matrices o array puede leer esta nota.

Para acceder el archivo de ejemplo pueden hacer clic aqui

Creación de listas de validación en forma dinámica

Excel posee la funcionalidad para validar un valor ingresado en una celda con respecto a un rango predeterminado de valores. Esto se puede realizar utilizando la función de validación de datos que puede encontrarse en la ficha Datos y luego seleccionar Validación de datos.

Para muchos escenarios donde se necesita ingresar un solo dato o varios pero que no tienen relación entre si esta validación funciona muy bien y asegura que solo se puedan ingresar opciones pre definidas.

Si disponemos valores que son dependientes de una selección anterior (por ejemplo si primero se selecciona una región y luego un país) deberíamos armar la segunda lista de validación en base a lo seleccionado en la primera para evitar combinaciones inválidas.


Veamos un ejemplo

Para construir una validación en forma dinámica basada en una selección o valor ingresado por el usuario debemos seguir los siguientes pasos:

El primer paso es definir un nombre para las regiones. En nuestro ejemplo es el rango A2:A6.

Luego definimos nombres para los países un nombre por región y el nombre del rango debe ser igual al nombre de la región. Por ejemplo para la primera región definimos un nombre de rango llamado America que se refiere al rango C2:C6. Repetimos este proceso para cada región.


Una vez definidos los nombres ingresamos los valores para la validación. En la celda donde se desea introducir la región utilizaremos la siguiente regla de validación =Regiones


En la celda de los países debemos utilizar la siguiente regla =INDIRECTO(C11)


INDIRECTO devuelve el valor de la celda que se le especifica como parámetro. Por lo tanto si ingresamos =INDIRECTO("America") devolverá como una matriz los valores que forman parte de ese rango.

La validación de Excel toma como valores validos un rango o una matriz de valores, al cambiar el valor al cual apunta la función indirecto se actualiza la validación lo que permite su construcción de manera dinámica.

Para acceder al archivo de ejemplo pueden hacer clic aqui