Formulas con rangos dinámicos usando la función INDIRECTO

Muchas veces necesitamos realizar una misma operación pero sobre rangos de celdas diferentes y no deseamos estar cambiando la fórmula para ajustar las celdas sobre las cuales se realiza la operación.

Para lograr esto podemos utilizar la función INDIRECTO (INDIRECT para la versión en Inglés) que toma una valor de texto, como el ingresado por un usuario en una celda y lo devuelve como una referencia a una celda, tal cual como se utiliza cuando es ingresado en una formula.

La función INDIRECTO toma dos parámetros, el primero es el nombre de la celda o nombre de rango al cual se desea hacer referencia. El segundo indica el formato de la referencia, si es VERDADERO (valor por defecto) la referencia será del tipo "A1" si es FALSO será "R1C1".

Si en la celda B2 ingresamos =INDIRECTO("C3") o =INDIRECTO("R3C3",Falso) obtendremos el valor de la celda como vemos en la siguiente pantalla.


Una alternativa es definir nombres de rangos y luego cambiar las celdas a las que se refiere ese rango, esta alternativa brinda la ventaja de hacer este proceso directamente en la planilla de Excel sin tener que entrar a definir nuevos nombres.


Veamos un ejemplo.


En cada hoja tenemos un listado de ventas que representan las ventas de todo el mes. Todas las hojas tienen el mismo formato pero pueden variar en cantidad de líneas.


En cada hoja definimos nombres para los rangos para facilitar las formulas y que nos permita trabajar con distinta cantidad de datos por mes. La rango en columna B tiene el nombre Clientes.

Es importante definir los nombres de los rangos locales a la hoja, de esta forma podemos utilizar el mismo nombre de rango en varias hojas. Realizamos este proceso para todas las hojas con datos.


De manera similar definimos el nombre Productos para el rango en la columna C y Cantidades para el rango en la columna D.

En la hoja resumen tomamos información de las hojas presentando un estado de situación. El usuario puede seleccionar el mes a evaluar con solo seleccionar de la lista desplegable ubicada en la celda B2


Por ejemplo si deseamos calcular el total de productos vendidos usamos la fórmula: =SUMA(INDIRECTO(B2&"!Cantidades")), al cambiar el valor de B2 la función INDIRECTO hará referencia a distintos rangos.

Para acceder al archivo del ejemplo pueden hacer clic aquí.

Definición de nombres en Excel

Cuando hacemos referencia a una celda usualmente utilizamos la nomenclatura A1, A2, A3, etc donde la primera letra corresponde a la columna y el número corresponde a la fila. Adicionalmente Excel posee la función para otorgarle nombres definidos por el usuario a una celda o a un rango de celdas.

Esta herramienta se encuentra en la ficha Formulas, en la opción definir nombres dentro de la sección Nombres definidos.


Una vez seleccionada esa opción aparecerá un cuadro de dialogo para definir el nombre, donde ingresamos el nombre del rango y la celda o celdas a las que hace referencia.



A continuación veremos algunos ejemplos sencillos sobre como definir fórmulas utilizando nombres definidos por el usuario y cuales son sus ventajas.

Si tenemos una lista de precios y deseamos devolver el precio tomando como parámetro el código podemos definir el rango A1:B5 con el nombre Datos y utilizar la siguiente formula para la búsqueda =BUSCARV("Uva",Datos,2,Falso).


Si tenemos dicha fórmula en varios lugares en nuestra planilla y tenemos que cambiar el rango Datos o agrandarlo solo debemos hacerlo en un solo lugar y esto afectara a todas las formulas facilitando la administración y mantenimiento de dicha hoja de cálculos.

Otra aplicación es cuando tenemos un valor fijo que es aplicado en varias formulas en la planilla y para facilitar le lectura de la formula definimos un nombre para ese rango, si tenemos un nombre Descuento que tiene el valor 0.15 entonces podemos definir una formula =A1 * Descuento para obtener el resultado.


Cuando deseamos cambiar el valor del descuento solo modificamos el valor asignado al nombre y esto actualiza todas las fórmulas sin tener que hacerlo una por una.

Búsquedas con múltiples parámetros

Nuevamente abordamos el tema de las búsquedas de datos en listas en Excel y si hablamos de buscar datos casi siempre utilizamos la función BUSCARV (o su hermana menos conocida BUSCARH) pero esta función tiene algunas limitaciones. En otras notas anteriores he explicado alternativas utilizando la formulas INDICE Y COINCIDIR

En esta nota explicaré como realizar una búsqueda utilizando más de un parámetro o celda para realizar la búsqueda.

La forma más sencilla de lograr esto es realizar una columna auxiliar donde concatenamos los datos de las dos columnas que forman nuestro criterio de búsqueda. De esta forma realizamos la búsqueda normal con un solo parámetro pero que contiene dos criterios o valores en su contenido

Si no deseamos o no podemos crear esta columna podemos recurrir nuevamente a INDICE Y COINCIDIR en una función matricial o array.

Para lograr el resultado, definimos cada condición o criterio de búsqueda que nos devolverá una matriz con VERDADERO O FALSO, ya que estamos utilizando una función matricial. Este array tendrá tantos elementos como filas tenga la lista de datos y dependiendo si esa fila cumple el criterio tendrá el valor VERDADERO y si no tendrá FALSO.

Luego para juntar todos los criterios multiplicamos las matrices y obtendremos una matriz con un valor verdadero si y solo si todas las matrices tenían un verdadero en esa posición. Aquí es donde realizamos la operación para asegurarnos que se cumplen todos los criterios de búsqueda y aquellas líneas que no cumplen con todos los criterios queda excluidas.

Una vez ubicado la posición devuelta por coincidir utilizamos la función INDICE para devolver el dato ubicado en esa posición.


Veamos un ejemplo


Tenemos una lista de precios como la que se muestra a continuación


Si deseamos ubicar el precio para Naranja y Minorista podemos utilizar la siguiente formula :
{=INDICE(C2:C11,COINCIDIR(1,(A12:A11="Naranja")*(B12:B11="Minorista"),0))}

Para ver como se resuelven las distintas condiciones pueden utilizar la herramienta Evaluar Fórmula de Excel que muestra paso a paso como se procesa todas estas fórmulas.

Recuerden que es una fórmula matricial por lo tanto se debe presionar Ctrl + Shift + Enter.

Para acceder al archivo del ejemplo pueden hacer clic aquí.

Accediendo a la información de una tabla dinámica con la función IMPORTARDATOSDINAMICOS

Las tablas dinámicas son de gran utilidad en Excel ya que permiten agrupar la información y poder verla de forma resumida. Además ofrece mucha flexibilidad para poder decidir como mostrar la información.

En algunos casos cuando tenemos mucha información, por más que utilicemos una tabla dinámica sigue quedando demasiado grande y en esos casos podemos utilizar IMPORTARDATOSDINAMICOS (o GETPIVOTDATA para la versión en Inglés) para traer algunos datos específicos de una tabla dinámica y presentarlos en una pequeña tabla o de la forma que decidamos.

La función toma 2 parámetros obligatorios que son, el primero el nombre del campo que contiene los datos en la tabla dinámica y el segundo una referencia a una celda de la tabla dinámica. Esto es necesario para saber que tabla dinámica se va a utilizar.

Luego se pueden agregar hasta 126 pares de parámetros donde se indica el nombre de un campo y un valor para filtrar los datos.
El valor tiene que estar visible en la tabla dinámica, si intentamos acceder un dato oculto o ingresamos por error un nombre de un campo incorrecto o valor inexistente la función devuelve el error #REF!. La función siempre devolverá el valor que sea la intersección de una fila y una columna de la tabla dinámica.



Veamos algunos ejemplos

Si tenemos una tabla dinámica como la que mostramos a continuación, podemos utilizar las siguientes fórmulas para buscar información.


Para obtener el total de artículos vendidos usamos la fórmula: =IMPORTARDATOSDINAMICOS("Cantidad";A$3)

Para obtener el importe total vendido a Pedro durante el mes de Enero usamos la fórmula: =IMPORTARDATOSDINAMICOS("Importe";$A$3;"Mes";"Enero";"Cliente";"Pedro")

Para obtener la cantidad de manzanas vendidas usamos la fórmula: =IMPORTARDATOSDINAMICOS("Cantidad";$A$3;"Producto";"Manzana")

La mejor forma de practicar con esta fórmula es presionar = y luego seleccionar un dato en la tabla dinámica y Excel creará la formula necesaria para devolver el elemento seleccionado

También podemos buscar datos en otras celdas y no ingresar los valores fijos en las celdas, por ejemplo si la celda B25 contiene el nombre del producto sobre el que deseo información puedo usar la siguiente fórmula: =IMPORTARDATOSDINAMICOS("Cantidad";$A$3;"Producto";B25). Al cambiar el valor de la celda H1 se modificará el resultado.

Ventajas

Es posible las formulas en cualquier parte de la hoja y moverlas o insertar filas ya que siempre solo hay una referencia a una cela de la tabla dinámica.
Al tratarse de una formula normal de Excel puedo formatearla como se desea y sin las restricciones de formato que tienen las tablas dinámica.

Desventajas

El dato tiene que estar visible en la tabla dinámica para poder ser utilizado con esta fórmula. Esto puede no ser tan importante si nuestra tabla está siendo creada con una rango de Excel como base pero si el origen de los datos es un cubo OLAP genera algunas limitaciones.

No se pueden cambiar los filtros de página de la tabla dinámica con una formula.

Para acceder el archivo de ejemplo haga clic aqui.

Buscar valores en una tabla con dos parámetros

Usualmente en Excel tenemos que buscar un dato particular que puede estar en una lista y para ello Excel tiene varias funciones de búsqueda. En el artículo de hoy veremos un ejemplo que combina dos funciones de búsqueda BUSCARV y COINCIDIR para poder ubicar un valor determinado en una tabla de datos.

Para buscar un valor que se encuentra en una celda determinada de la tabla vamos a necesitar realizar dos búsquedas. Primero debemos localizar la fila donde se encuentra el dato que deseamos buscar y luego la columna. Con estos dos valores identificamos la celda que contiene el dato buscado.

Para ubicar la fila usamos la función BUSCARV que busca un valor en la primera columna de un rango y devuelve el valor ubicado en la misma fila pero a una determinada cantidad de columnas hacia la derecha. Esta función toma 4 valores como parámetros, el primero es el valor buscado, el segundo el rango de celdas donde buscará dicho valor y que contiene también la columna que desea ser devuelta. Luego se envía el número de columna que se desea y finalmente el parámetro FALSE para indicar una coincidencia exacta.

En el tercer parámetro de BUSCARV en lugar de proporcionar directamente el número de columna utilizamos la función COINCIDIR y con ella buscamos el titulo de la columna que deseamos y como resultado obtendremos el número de columna dentro del rango.



Veamos un ejemplo


Si tenemos una tabla como la que se muestra a continuación donde se tienen Productos en las filas y los nombres de los vendedores en las columnas.


Podemos utilizar la siguiente función para ubicar cuantas Manzanas vendió Juan : =BUSCARV("Manzana";A2:E10;COINCIDIR("Juan";A2:E2;0);FALSE)

La parte de la fórmula =COINCIDIR("Juan";A2;E2;0) devolverá el número de columna que en este caso es 4, luego este valor es utilizado por BUSCARV para devolver la celda deseada.

Para hacer esta fórmula más flexible pondremos el nombre del producto y del vendedor en dos celdas separadas y luego haremos referencia a ellas en la formula.

Si la celda H5 tiene el producto y H6 el vendedor nuestra formula sería =BUSCARV(H5;A2:E10;COINCIDIR(H6;A2:E2;0);FALSE)

Cambiando los valores obtenemos distintos resultados sin tener que modificar la formula.

Para acceder al archivo del ejemplo haga clic aquí.

Realizar sumas condicionales con la formula SUMAPRODUCTO (SUMPRODUCT)

En una nota anterior he comentado como realizar sumas con más de una condición utilizando la formula SUMAR.SI.CONJUNTO, pero esta fórmula solo está disponible en Excel 2007 en este artículo explicaré una alternativa que sirve para Excel 2003 o anteriores.

La fórmula SUMAPRODUCTO realiza la multiplicación entre los arrays enviados como parámetros y luego suma cada uno de los resultados de esas multiplicaciones.

Se pueden incluir desde 2 hasta 255 matrices en esta formula y las matrices pueden ser provistas mediante rango de celdas o rangos con nombre o arrays constantes. La fórmula devolverá el error #VALOR! si los arrays tienen distintas longitudes.


Veamos algunos ejemplos


Si tenemos una lista de productos con su precio unitario y la cantidad vendida podemos en una sola celda calcular el valor total sin necesidad de utilizar una fórmula en cada línea para calcular el valor total de cada línea.


Utilizando la siguiente formula podemos calcular el total =SUMAPRODUCTO(B2:B6;C2:C6)

Otra aplicación de esta fórmula es para realizar sumas condicionales, la idea es que en el primer parámetro ingresemos rango que queremos sumar y luego las condiciones que queremos cumplir para sumar los números.


Utilizaremos la formula =SUMPRODUCTO(C2:C14;--(B2:B14="Manzana");--(A2:A14="Argentina"))

Las condiciones devolverán arrays con valores "VERDADERO" o "FALSO" que luego convertiremos a 1s y 0s. Si el valor es verdadero usaremos 1 y si es Falso usaremos 0. Al utilizar este proceso multiplicaremos cada elemento del primer parámetro por 1s o 0s dependiendo de la condición.

El -- es utilizado para convertir los valores "VERDADERO" o "FALSO" en 1s o 0s. Solo aquellos elementos que sean multiplicados por 1 llegaran a tener un valor para el paso final de la suma.

Si no incluimos el rango a sumar y solamente las condiciones estaremos realizando una fórmula similar a CONTAR.SI.CONJUNTO

Para bajar el archivo de ejemplo pueden hacer clic aqui.

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

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.

Descubriendo las funciones de Excel 2, K.ESIMO.MAYOR y K.ESIMO.MENOR (LARGE y SMALL)

La función K.ESIMO.MAYOR devuelve el k-esimo valor más grande de una lista. Por su parte la función K.ESIMO.MENOR devuelve el k-esimo valor más pequeño de una lista. Para ambas funciones se toman dos parámetros como argumentos, el primero es el rango o lista de valores a evaluar y el segundo es el nro. de elemento más grande o más pequeño que se desea.

Si el nro. de elemento ingresado es mayor que la cantidad de elementos en el rango seleccionado, la fórmula devuelve el error#¡NUM!.

Existen otras dos funciones similares que son MAX y MIN, estas funciones pueden devolver el más grande y más pequeño de una lista respectivamente. Estas funciones son equivalentes a K.ESIMO.MAYOR y K.ESIMO.MENOR con el segundo parámetro en 1.


Veamos algunos Ejemplos

Si tenemos una tabla de resultados, que no esta ordenada y por lo tanto el primer equipo no es el que tiene mayor cantidad de puntos y el último no es el que tiene menor cantidad de puntos :

Con las formulas K.ESIMO.MAYOR y K.ESIMO.MENOR podemos encontrar los primeros tres y los últimos tres equipos.

Para el primer equipo =K.ESIMO.MAYOR(A4:A10,1), para el segundo =K.ESIMO.MAYOR(A4:A10,2) y así sucesivamente.

Para el último equipo =K.ESIMO.MENOR(A4:A10,1), para el anteúltimo =K.ESIMO.MENOR(A4:A10,1) y así sucesivamente.

Luego con la funciones INDICE y COINCIDIR (o INDEX y MATCH para la versión en inglés) se puede buscar el equipo correspondiente al puntaje.

Esta es una de las tantas aplicaciones que pueden tener estas formulas en artículos futuros las utilizaremos para realizar otras tareas más complejas.

Si tienen preguntas no duden en poner comentarios.

Descubriendo las funciones de Excel, SI.ERROR (IFERROR)

Excel posee muchas funciones (la versión 2007 tiene 359 funciones) y algunas de ellas muy utilizadas, pero otras son más desconocidas y en una serie de artículos intentaré explicarles brindando algunos ejemplos prácticos.

En la primer nota de esta serie utilizaremos la función SI.ERROR o IFERROR si poseen la versión en Inglés.

Esta función evaluará el resultado de una formula y si produce un error nos permitirá tomar una acción determinada. Esta función evalúa los siguientes tipos de errores : #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!
Se toman dos parámetros, el primero es la función que se desea evaluar y el segundo es la acción que se desea hacer si la formula evaluada generó un error.


Veamos algunos ejemplos

Si se intenta dividir por cero Excel mostrará el error #¡DIV/0! para evitar este mensaje o realizar otra acción se puede utilizar :

=SI.ERROR(E6/F6;”Error”) o bien dejar el resultado de la celda en blanco =SI.ERROR(E6/F6;””) como se ve en el ejemplo y tenemos como ventaja la posibilidad de realizar una suma.

Otro ejemplo para esta función, es para evitar el valor #N/A que puede ser devuelto por la función BUSCARV (VLOOKUP para la versión en inglés) :

=BUSCARV(“Total”; F1:G20; 2; Falso). Si el valor “Total” no se encuentra en el rango buscado Excel devolverá el error #N/A, para no mostrar dicho error podemos utilizar la siguiente función : =SI.ERROR(BUSCARV(“Total”; F1:G20; 2; Falso),””)

Antes de la introducción esta fórmula era común utilizar la función ESNOD para evaluar el resultado del BUSCARV, pero la desventaja aquí es que la función BUSCARV se realizaba dos veces si el valor era encontrado.

Por ejemplo =SI(ESNOD(BUSCARV(“Total”; F1:G20; 2; Falso));””; BUSCARV(“Total”; F1:G20; 2; Falso))
Con lo cual esta nueva formula resuelve mas rápido las búsquedas y también es mas fácil ingresarla ya que no se debe registrar una formula tan larga.

Como verán la función puede aplicarse en diversas situaciones permitiendo solucionar muchos inconvenientes.

En próximos artículos revisaremos otras aplicaciones de esta fórmula como así también otras formulas incluidas en Excel pero no tan conocidas.