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

Funciones de búsqueda, INDICE y COINCIDIR (INDEX y MATCH)

Excel posee varias funciones para buscar información en un rango de celdas o matriz (array). La mas común y utilizada de ellas es BUSCARV. Estas funciones permiten buscar un valor determinado en un rango de celdas y devolver un valor ubicado en la misma fila pero en una columna ubicada a la derecha del valor encontrado.
En algunas ocasiones tenemos que devolver un valor que se encuentra a la izquierda del valor buscado. Una solución a este problema es modificar el formato de la lista o rango de celdas pero a veces esto no es posible. En ese caso podemos usar una combinación de formulas INDICE y COINCIDIR (INDEX y MATCH para la versión en inglés) para lograr el resultado.


Veamos una descripción de lo que realizan estas dos formulas


COINCIDIR devuelva la posición (con respecto al primer elemento) del elemento buscado dentro de un rango de celdas. La función toma tres parámetros, el primero es el valor buscado, el segundo es el rango donde se desea buscar el elemento. Por último podemos indicar si queremos una equivalencia exacta el valor próximo mayor o menor. Si el valor buscado no se encuentra, la función devuelve el error #N/A.


INDICE devuelve el elemento que se encuentra en una posición determinada dentro de un rango de celdas o matriz. La función toma tres parámetros, el primero es el rango de celdas a buscar o matriz, el segundo el número de fila donde se encuentra el elemento. El tercero indica el número de la columna, este parámetro puede ser omitido y en ese caso se toma la primer columna.
Las posiciones son relativas al comienzo del rango donde se realiza la búsqueda. Por ejemplo si buscamos en el rango B15:B25 y deseamos el elemento que se encuentra en B20 debemos ingresar el número 5 como parámetro.


Veamos algunos ejemplos


Utilizando el mismo ejemplo de una tabla de posiciones que presenté en el artículo de las funciones K.ESIMO.MAYOR y K.ESIMO.MENOR podemos utilizar las funciones INDICE y COINCIDIR para saber cual es el nombre del equipo que tiene más puntos.

Para traer el nombre del equipo que se encuentra primero utilizamos esta fórmula =INDICE($A$4:$A$10;COINCIDIR(F4;$B$4:$B$10;0)).

Si desea obtener una copia del ejemplo haga clic aqui.

Realizar sumas condicionales con mas de un criterio SUMAR.SI.CONJUNTO

La función SUMAR.SI permite realizar una suma en un rango determinado si se cumple cierto criterio definido en uno de sus parámetros siendo una función es muy utilizada. Uno de los principales problemas de esta función es que solo se puede poner un criterio.

Para resolver este problema antes de la introducción de esta nueva función se podía utilizar en conjunto las funciones SUMAR y SI e introducirlas como un matriz (Array). En un artículo futuro explicaré los detalles de las funciones de matrices.


Excel 2007 introduce una nueva función denominada SUMAR.SI.CONJUNTO que permite realizar una suma utilizando múltiples criterios, hasta 127 criterios pueden ser incluidos.

Esta función toma como primer parámetro el rango a sumar y luego se tienen que indicar el criterio para sumar. Este se define de a pares, es decir para cada criterio tenemos dos parámetros, el primero es el rango dónde queremos aplicar la condición y el segundo es las condición que queremos definir.


Veamos algunos ejemplos

Si tenemos una tabla donde se tienen las ventas por producto y país y queremos obtener totales para un país o un producto o una combinación podemos utilizar las siguientes formulas :

Para obtener el total de ventas para un país, usamos la función normal SUMAR.SI, =SUMAR.SI(A2:A14;"Argentina";C2:C14)

Para obtener el total de ventas para Manzanas, usamos la función =SUMAR.SI(B2:B14;"Manzana";C2:C14)

Para obtener el total de ventas de Manzanas en Argentina, podemos usar =SUMAR.SI.CONJUNTO(C2:C14;A2:A14;"Argentina";B2:B14;"Manzana")

Si no tienen Excel 2007 pueden lograr el mismo resultado utilizando una función ingresada como matriz, {=SUMA(SI((A2:A14="Argentina")*(B2:B14="Manzana");C2:C14))}. Para ingresar la función matricial se debe presionar Ctrl + Shift + Enter
Para obtener mas información de las funciones matriciales o arrays pueden leer el siguiente nota.

Cambiando los criterios se puede analizar cualquier lista de Excel y obtener rápidamente un resultado que de otra forma llevaría varios pasos.