skip to main |
skip to sidebar
Una de las ventajas de Excel es que permite crear libros de trabajo que sean interactivos y que puedan producir diferentes resultados dependiendo de ciertos valores ingresados por el usuario. Esta interactividad permite explorar la información de diversas maneras haciendo mucho más útil nuestro libro de trabajo.
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í.
En una nota anterior expliqué como utilizar las funciones INDICE y COINCIDIR (INDEX y MATCH) para buscar valores, en la nota de hoy explicaré como utilizar ambas funciones para construir un cálculo de antigüedad de deuda.
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í.
Luego de los valores numéricos las fechas deben ser el siguiente tipo de dato más común en Excel y por ello hoy explicaré como realizar algunos cálculos con fechas. Entre los ejemplos utilizaremos dos funciones muy útiles llamadas DIA.LAB y DIAS.LAB que nos permitirán hacer cálculos con fechas pero tomando solo días laborales.
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í.
En la nota anterior expliqué como crear un gráfico de cascada, ahora realizaremos algunos cambios que mejoraran ese gráfico. Si bien estos cambios también pueden lograrse mediante macros, en este artículo mostraré alternativas sin usar macros ya que en ciertos casos es necesario generar un archivo sin incluir macros.
Como en el ejemplo anterior utilizamos la siguiente tabla para armar nuestro grafico y le asignamos el nombre TablaChart.
Veamos los cambios
El primer cambio nos permitirá asignarle distintos colores a las variaciones, en el caso que sean positivas o negativas. Para lograr esto separamos los valores positivos y negativos en dos columnas que serán incluidas como dos series distintas de datos y por lo tanto podremos asignarle distintos colores.
La columna que tiene los valores positivos contiene la siguiente fórmula =SI(B2>0;B2;0). En el caso de ser un valor negativo entonces mostramos un cero y por lo tanto esto no queda visible en el gráfico. A esta columna la llamaremos Series2Mas.
Por otra parte la columna que tiene los valores negativos contiene la siguiente fórmula =SI(B2<0;ABS(B2);0). Similar al caso anterior, si la variación es positiva se mostrará cero y sino el valor absoluto. A esta columna la llamaremos Series2Menos.
El otro cambio es la utilización de las tablas de Excel 2007 para que podamos ingresar nuevas filas según la necesidad y el gráfico se actualice automáticamente. Para lograr que funcione correctamente la tabla y se actualicen las fórmulas debemos utilizar una misma fórmula para cada fila y que ésta haga referencia a la misma fila.
En el ejemplo anterior las fórmulas de la columna Series1 eran diferentes para la primera y última fila ya que siempre llevan el valor cero. Lo primero que debemos lograr es una fórmula que devuelva 0 solo para la primera y última fila de la tabla. Para saber si estoy en la primera fila utilizamos FILA(TablaChart) y si es igual a FILA(), que devuelve la fila actual, entonces estamos en la primer fila. Para calcular la última fila FILA(TablaChart)+FILAS(TablaChart)-1, de la misma forma chequeamos si esta es igual a FILA(). La fórmula completa sería =SI(O(FILA(TablaChart)=FILA();FILA(TablaChart)+FILAS(TablaChart)-1 = FILA());0;B2).
Finalmente la fórmula utilizada para acumular los valores, en el ejemplo anterior llamada Ultimo tope, tampoco funciona correctamente al insertar nuevas filas en una tabla y por ello lo reemplazamos por =SUMA($B$2:B2), la referencia absoluta fija la primer fila mientras que la segunda se incrementa a medida que se incrementan las filas en la tabla.
Si integramos la fórmula para acumular con la explicada en el paso anterior obtenemos la siguiente fórmula =SI(O(FILA(TablaChart)=FILA();FILA(TablaChart)+FILAS(TablaChart)-1 = FILA());0; SI(B2<0;SUMA($B$2:B2);SUMA(B$2:B2)-B2))
Para acceder al archivo del ejemplo haga clic aquí.
Excel nos permite trabajar con cantidades muy grandes de datos y muchas veces esta información va creciendo a medida que transcurre el tiempo. Al modificarse los rangos en donde se encuentran los datos se deben modificar las fórmulas que usan dichos datos para que incluyan la totalidad del rango.
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.
Retomando los artículos sobre funciones de búsquedas, explicaremos en el artículo de hoy una función que no es muy utilizada pero es muy fácil ya que es similar a otra función que es una de las de mayor uso en Excel. Por supuesto no referimos a BUSCARV.
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.
Si bien podemos considerar que todas las hojas de Excel son una tabla, en Excel 2007 se introdujo una nueva herramienta llamada "Tablas". Esta herramienta permite darle un nombre a un rango determinado de celdas y poder acceder a los elementos de la tabla mediante una referencias, y no el nombre de la celda donde está el dato, que pueden usarse en formulas.
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í.
que figuran en la página.
