lunes, 29 de noviembre de 2010
Fórmulas dinámicas utilizando la función CELDA (CELL)
La interactividad puede lograrse de diversas formas pero una de las más sencillas para el usuario final es ir seleccionando distintas celdas y utilizar su contenido como parámetro para que produzca distintos resultados. Este proceso es muy fácil ya que el usuario solo debe seleccionar distintas celdas para ver como se actualiza el contenido. Para lograrlo utilizaremos la función llamada CELDA, que nos permite obtener información de una celda de nuestra planilla de cálculo.
La función CELDA (CELL para la versión en Inglés), tiene dos parámetros. El primero indica el tipo de información que se quiere obtener y el segundo es la celda sobre la cual buscaremos información.
Si queremos saber si la celda B8 está protegida podemos usar la siguiente fórmula =CELDA("proteger",B8), si el valor devuelto es 1 entonces está protegida y si el valor es 0 entonces no está protegida. Hacer clic en http://office.microsoft.com/es-es/starter-help/celda-funcion-celda-HP010342226.aspx?CTT=1 para ver los distintos parámetros de la función CELDA.
Si omitimos el segundo parámetro, la función CELDA devolverá información de la celda activa. Por ejemplo la fórmula =CELDA("DIRECCION") devuelve la dirección de la celda activa.
Veamos el ejemplo
Tenemos un listado de ventas de productos por país y queremos que al seleccionar una celda de la tabla nos informe el total del producto y del territorio incluido en esa línea.
Para el caso del territorio podemos usar la fórmula =SUMAR.SI(A2:A14,INDIRECTO(CELDA("direccion")),C2:C14). Utilizamos la función INDIRECTO para que el valor devuelto por la función CELDA sea transformado a una referencia que luego es utilizada por SUMAR.SI como la condición para realizar la suma.
Para realizar el total del producto simplemente cambiamos el rango que se usa como primer parámetro de la función SUMAR.SI quedando de la siguiente forma =SUMAR.SI(B2:B14,INDIRECTO(CELDA("direccion")),C2:C14).
Esta fórmula solo funciona si estamos en la columna correcta, para solucionarlo haremos algunos cambios en las formulas.
En primer lugar vamos a definir nombres para que la formula no sea tan larga y difícil de entender. Por un lado definimos el nombre UltCelda al que le asignamos la fórmula =CELDA("dirección") y luego nombre UltCol a la fórmula =CELDA("columna") y UltFila a la fórmula =CELDA("fila").
Para saber si la celda activa se encuentra dentro del rango de la tabla usamos la función SI y chequeamos que los valores de UltFila y UltCol estén dentro de los límites de la tabla de la siguiente forma =SI(O(UltCol>2,UltFila>14),"Fuera de rango",…).
Para obtener el nombre del producto sin importar en que columna de la tabla me encuentro, utilizamos =DESREF(INDIRECTo(UltCelda),0,2-UltCol), lo que nos permite ajustar la celda con el cálculo 2-UltCol. Si estoy en la columna A entonces 2-Ultcol será igual a 1 y eso nos desplazará una columna devolviendo la referencia a la columna B.
La fórmula final para los países es =SI(O(UltCol>2,UltFila>14),0,SUMAR.SI(A2:A14,DESREF(INDIRECTO(UltCelda),0,1-UltCol),C2:C14)) y para los productos =SI(O(UltCol>2,UltFila>14),0,SUMAR.SI(B2:B14,DESREF(INDIRECTO(UltCelda),0,2-UltCol),C2:C14)).
El último paso consiste en hacer que Excel recalcule la hoja cuando cambiamos de celda. Seleccionar una nueva celda no es suficiente para que Excel recalcule toda la hoja por lo tanto tenemos que agregar una pequeña macro para hacerlo.
La macro debe agregarse en la hoja que contiene las fórmulas antes explicadas.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
Esta macro se ejecuta cada vez que se cambia la celda activa y lo que hace es forzar un recalculo de la hoja.
Para acceder al archivo de ejemplo hacer clic aquí.
lunes, 4 de octubre de 2010
Cálculo de antiguiedad de deuda con INDICE y COINCIDIR
Tenemos un listado de facturas con sus fechas de vencimiento y los importes.
Cálculamos los días desde el vencimiento utilizando una fecha de referencia ubicada en la celda J7, también se puede utilizar la fecha actual para que se actualice automáticamente. Para realizar el cálculo pero tomando días laborales debemos utilizar la fórmula DIAS.LAB.
Una forma de resolverlo es con SI anidados utilizando la siguiente fórmula =SI(D2<30,"0-30",SI(D2<60,"30-60",SI(D2<90,"60-90","+90"))). El problema que presenta esta solución es que se puede volver muy grande si tenemos varios rangos y es dificil de ingresar y mantener. Adicionalmente si queremos cambiar algún rango o agregar nuevos tenemos que modificar todas las fórmulas.
Definimos dos rangos con nombres que son RangoValores y RangoNombre, estos definene los distintos rangos sobre los cuales calcularemos la antigüedad de deuda. En el RangoValores tenemos que escribir el límite superior de cada rango y en RangoNombre ponemos la descripción que identifica a cada rango.
La fórmula que utilizamos es INDICE(RangoNombres,COINCIDIR(D2,RangosValores,1)). La función COINCIDIR toma el valor búscado, en nuestro ejemplo "D2" y lo busca en RangoValores devolviendo la posición dentro de RangoValores. El último parámetro de la fórmula COINCIDIR nos permite definir si buscamos un valor exacto en ese caso definimos el parámetro con el valor 0 o bien buscamos el valor mas grande en nuestro RangoValores que sea menor al valor búscado.
Si necesitamos modificar los rangos o agregar nuevos simplemente tenemos que modificar los dos nombres que definimos anteriormente y nuestra fórmula funcionará sin tener que ajustarla.
Para acceder el archivo de ejemplo hacer clic aquí.
sábado, 21 de agosto de 2010
Cálculos con fechas en Excel usando DIA.LAB y DIAS.LAB (WORKDAY, NETWORKDAYS)
Entre los cálculos de fechas más comunes encontramos el cálculo de una fecha a x días del día actual. Otro cálculo común es el número de días que hay entre dos fechas.
Veamos unos ejemplos
Para obtener la fecha que se encuentra a 30 días a partir de hoy utilizamos =AHORA() + 30 o para saber que fecha corresponde a 7 días antes de hoy utilizamos =AHORA() - 7.
Si tenemos dos fechas y se encuentran en las celdas A1 y A2 entonces podemos ingresar la fórmula =A1-A2 que nos calculará la cantidad de días.
Estos cálculos los podemos realizar de esta forma ya que Excel guarda internamente las fechas como valores numéricos que representan el número de días transcurridos desde el 01/01/1900. La parte decimal se utiliza para determinar la cantidad de segundos, minutos y horas desde el comienzo del día. Pueden probarlo usando la función AHORA y luego cambiando el formato de la celda a numérico.
En muchos casos no necesitamos hacer estos cálculos con días corridos sino con días laborales y para ello Excel tiene dos funciones DIAS.LAB y DIA.LAB
Primero analizaremos la función DIA.LAB que nos permite calcular una fecha que se encuentra a x días laborales de una fecha inicial. Esta función toma tres parámetros, el primero es la fecha inicial sobre la cual haremos el cálculo. El segundo es la cantidad de días positivo para una fecha futura o negativo para una pasada. El tercer parámetro es un array con fechas que son feriados los mismos no se consideran días laborales.
La segunda función DIAS.LAB nos permite contar la cantidad de días laborales que hay entre dos fechas. Esta función toma tres parámetros, el primero es la fecha inicial, el segundo es la fecha final y el último es el array con fechas que son feriados y no serán considerados como días laborales.
Veamos unos ejemplos
En la siguiente imagen mostramos los cálculos de fechas utilizando los cálculos con días corridos y con días laborales.
Para calcular los días laborares entre dos fechas usamos =DIAS.LAB(B1;B2;Feriados) y para calcular la fecha que se encuentra a 60 días laborales de la fecha de inicio usamos =DIA.LAB(B1;60;Feriados)
Feriados es una matriz o array con valores constantes que contiene los días que no consideramos como laborales. En nuestro ejemplo el día 9 de Julio es considerado feriado. Para más ayuda sobre arrays haga clic aquí.
domingo, 23 de mayo de 2010
Rangos Dinámicos
Si tenemos un archivo con muchas fórmulas esto genera un trabajo tedioso y que puede generar resultados incorrectos. Para facilitar este proceso es posible definir nombres a los rangos utilizados que nos permite ajustar el rango definido para ese nombre y luego todas las fórmulas utilizarán el nuevo rango. Para rangos de datos que cambian constantemente este proceso también puede volverse tedioso.
En la nota de hoy veremos una alternativa que nos permite definir el rango dinámicamente sin tener que actualizar las fórmulas o los nombres de los rangos.
Para poder definir el nombre del rango dinámicamente debemos detectar cual es la última fila utilizada en el rango y para ello utilizamos la siguiente fórmula array : =MAX(FILA(1:65378)*(A1:A65378<>"")) (recuerden presional Ctrl + Shift + Enter)
Luego definimos el nombre UltimaCelda para la celda que contiene esta fórmula.
Esta fórmula genera en primer lugar un arrary con números de filas (FILA(1:65378)) del tamaño que nosotros deseamos. Esto definirá el límite de crecimiento del rango. Luego se genera un array con valores lógicos, que serán VERDADEROS si la celda no esta vacía. Al multiplicarlos solo me quedan los valores de las filas con datos, el resto es siempre cero. Finalmente la función MAX devuelve el valor máximo del array.
Con esta información definimos un nuevo nombre en Excel llamado Rango. (Para mas información ver nota sobre nombres) y en el campo Aplica a en lugar de escribir un rango fijo (del tipo A1:C10) escribimos la siguiente fórmula: =DESREF(Hoja1!$A$1;0;0;UltimaCelda)
La función DESREF permite definir un rango a partir de una celda de referencia, la función toma 5 parámetros. El primero es la celda de referencia, los siguientes dos indican la cantidad de filas y columnas de distancia que va a tener nuestro rango desde el valor de referencia. Los últimos dos valores indican la cantidad de filas y columnas que tendrá nuestro rango.
Para descargar el ejemplo haga clic aquí.
Otra aplicación de esta fórmula es para la definición del rango de impresión. Si ya hemos definido una, entonces encontraremos en la lista de nombre uno llamado : Área_de_Impresión si ingresamos la fórmula arriba explicada el rango se actualizará automáticamente.
miércoles, 21 de abril de 2010
Descubriendo las funciones de Excel 3 BUSCARH (HLOOKUP)
La función BUSCARH (HLOOKUP para la versión en inglés) nos permite realizar búsquedas en un rango y una vez encontrado devolverá una celda que se encuentra en la misma columna pero a una cantidad determinada de líneas hacia abajo.
Dicha función toma cuatro parámetros. El primero es el valor buscado, el segundo es el rango donde se va a realizar la búsqueda, el tercero es el número de la fila dentro del rango que se utilizará para devolver el valor y el último parámetro indica si queremos que el valor buscado coincida exactamente con algún valor en el rango.
Si no hay coincidencias se devuelva el error #N/A. Si deseamos evitar este error podemos utilizar la función SI.ERROR como se explicó en una nota anterior.
Como pueden ver los parámetros y su funcionamiento son muy similares a la función BUSCARV y por lo tanto surge la pregunta: ¿Por qué tenemos dos funciones tan similares?
En muchos casos en Excel tenemos que trabajar con datos de planillas creadas por otras personas y por lo tanto no podemos cambiar los formatos sino que tenemos que adaptarnos a ellos. De acuerdo como estén distribuidos la función BUSCARH puede ser mas conveniente que BUSCARV.
Veamos algunos ejemplos.
Disponemos de una lista de ventas donde se muestran en las columnas las regiones y en las filas los meses.
Si deseamos buscar las ventas del mes de Mayo para la región Sur podemos utilizar la siguiente fórmula =BUSCARH("Sur";B1:E13;6;FALSO) y para buscar las ventas del mes de Agosto para la región Oeste podemos utilizar =BUSCARH(“Oeste”;B1:E13;9;FALSO).
Podran ver que el número de la fila sumando uno es igual al nro del mes. Esto es porque la primer fila contiene los encabezados. De esta forma podemos fácilmente obtener los distintos meses utilizando los valores.
domingo, 24 de enero de 2010
Acceder los elementos de una tabla mediante fórmulas
Las principales ventajas es que las referencias son más fáciles de usar que los nombres de las celdas ya que pueden tener el nombre que el usuario desee. Funciona de forma similar a la definición de un nombre para un rango.
Se puede cambiar el tamaño de la tabla o mover la tabla y esto ajustara todas las referencias a la tabla sin tener que cambiar las formulas una por una.
Si alguna de las columnas de la tabla tiene una fórmula esta se copiará automáticamente al resto de las líneas.
Las referencias a una tabla se componen de la siguiente forma : =NombreTabla[NombreColumna]
También se pueden acceder distintas partes de la tabla utilizando la siguiente notación
- =NombreTabla[#Todas] Para acceder a la tabla completa.
- =NombreTabla[#Datos] El rango de todos los datos, sin encabezados ni totales.
- =NombreTabla[#Encabezados] Solamente incluye la primer línea de encabezados.
- =NombreTabla[#Totales] Solamente incluye la última línea.
Veamos algunos ejemplos.
Utilizaremos una tabla de datos como la siguiente
Si todavía los datos no fueron convertidos a una tabla se debe seleccionar la opción tabla de la ficha Insertar. Luego se debe definir el nombre tblVentas. El nombre se puede cambiar seleccionado la ficha Herramientas de tabla (solo aparece cuando la celda activa es parte de la tabla) y completar el campo nombre de tabla.
Utilizando fórmulas similares a las explicadas en la nota de sumas condicionales con mas de un criterio, utilizaremos referencias a las tablas para obtener los resultados.
Si deseamos obtener la cantidad de productos para un determinado país podemos usar la fórmula =SUMAR.SI(TblVentas[Pais];"Argentina";TblVentas[Cantidad]).
Para obtener la cantidad de un producto determinado y en un solo país podemos usar la fórmula =SUMAR.SI.CONJUNTO(TblVentas[Cantidad];TblVentas[Pais];"Argentina";TblVentas[Producto];"Manzana").
También podemos combinar otras fórmulas de Excel y por ejemplo obtener un promedio de unidades =PROMEDIO(TblVentas[Cantidad]) o el precio promedio de todas las ventas =TblVentas[[#Totals];[Importe]]/TblVentas[[#Totals];[Cantidad]].
Para acceder al archivo del ejemplo pueden hacer clic aquí.
sábado, 2 de enero de 2010
Ranking con posiciones duplicadas usando la función JERARQUIA
Primero explicaré como funciona la función JERARQUIA. Esta función toma tres parámetros, el primero indica la celda que contiene el valor a evaluar. Luego se indica el rango de celdas donde están todos los valores que forman parte del ranking. Finalmente se indica el orden 0 para ascendente, es decir el mayor valor tiene ranking 1, y 1 para descendente.
Veamos algunos ejemplos
Tenemos una lista de precipitaciones promedios como la siguiente :
En la columna C aplicamos la siguiente fórmula =JERARQUIA(B2;$B$2:$B$13;0) y obtenemos el ranking de mayor a menor.
En el puesto 9 tenemos dos meses, Agosto y Septiembre que tienen el mismo valor. De ahí saltamos al puesto 11 y no tenemos un mes en el puesto 10. Para solucionar este problema podemos utilizar una variante en la fórmula antes descripta: =JERARQUIA(B2;$B$2:$B$13;0)+CONTAR.SI($B$2:B2;B2)-1
La segunda parte de la formula cuenta cuantas veces aparece el valor de la fila que estamos calculando el ranking desde el principio de la tabla hasta la fila actual. Por lo tanto si el valor esta repetido el que figure mas abajo tendrá un ranking mayor. Es muy importante poner la referencia "$B$2:B2" fijando el comienzo del rango para que siempre empiece desde el principio de la tabla pero si copiamos la fórmula hacia abajo se incrementa el rango.
lunes, 21 de diciembre de 2009
Formulas con rangos dinámicos usando la función INDIRECTO
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í.
sábado, 28 de noviembre de 2009
Definición de nombres en Excel
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.
lunes, 9 de noviembre de 2009
Búsquedas con múltiples parámetros
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í.
sábado, 31 de octubre de 2009
Accediendo a la información de una tabla dinámica con la función IMPORTARDATOSDINAMICOS
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.
viernes, 23 de octubre de 2009
Buscar valores en una tabla con dos parámetros
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í.
lunes, 19 de octubre de 2009
Realizar sumas condicionales con la formula SUMAPRODUCTO (SUMPRODUCT)
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.
jueves, 15 de octubre de 2009
Buscar el valor más frecuente en una lista con la función MODA
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.
domingo, 11 de octubre de 2009
Evaluar formulas en Excel
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.
sábado, 3 de octubre de 2009
Utilizando matrices o arrays en Excel
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.
viernes, 25 de septiembre de 2009
Conteo de elementos con varios criterios CONTAR.SI.CONJUNTO
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
sábado, 19 de septiembre de 2009
Creación de listas de validación en forma dinámica
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:
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
domingo, 13 de septiembre de 2009
Funciones de búsqueda, INDICE y COINCIDIR (INDEX y MATCH)
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.
jueves, 10 de septiembre de 2009
Realizar sumas condicionales con mas de un criterio SUMAR.SI.CONJUNTO
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.