Actualización simultánea de tablas dinámicas en Excel.

Después de realizar cambios en la base de datos de una tabla dinámica tenemos que apretar el botón de actualización para que los cambios se reflejen en la tabla



Si tenemos más de una tabla dinámica en el cuaderno, tenemos que actualizar cada una por separado. Además de la molestia de tener que hacer varios clics existe también el riesgo de olvidar de actualizar alguna de las tablas.
Aparentemente Excel no tiene ningún método para actualizar todas las tablas simultáneamente, pero podemos usar el botón Actualizar Todo, que por defecto aparece en la barra de herramientas de Datos Externos, para esta tarea. Para agregar el botón a la barra de herramientas Tabla Dinámica hacemos lo siguiente:

1 - pulsamos la flecha de Opciones de la barra de Tablas Dinámicas para abrir la posibilidad Agregar o quitar botones



2 - Elegimos Tabla Dinámica y señalamos el botón Actualizar todo



Al apretar el botón se actualizarán todas las tablas dinámicas del cuaderno. Hay que tomar en cuenta que si hay rangos externos en el cuaderno, éstos se actualizarán.

Technorati Tags: MS Excel

Cuadro de texto flotante en Excel - segunda nota

En la nota anterior sobre cómo crear cuadros de texto flotantes en Excel mostramos una técnica para simular el efecto de un objeto flotante.
La técnica consiste en crear una cuadro de texto y ligarlo a una celda, o mejor aún usar la cámara fotográfica de Excel para crear una imagen dinámica de un rango.
El problema con la técnica que describimos en esa nota es que el objeto se desplaza sólo verticalmente. Es decir que si creamos el objeto con la cámara y lo ubicamos en el área de la columna D, cuando nos movamos hacia la derecha hasta la columna AB, por ejemplo, no veremos el objeto ya que éste no se desplaza horizontalmente.

Para lograr que el objeto se desplace también horizontalmente creamos este evento en el módulo de Vba de la hoja correspondiente

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    With Shapes("Picture 2")
        .Left = Target.Offset(0, 1).Left
        .Top = Target.Offset(-1, 0).Top
    End With
    On Error GoTo 0
End Sub


Usamos la propiedad OFFSET para colocar el objeto a la izquierda y arriba de la celda activa. Así por ejemplo, si tenemos una tabla de cotizaciones en el rango A1:B4, después de crear el objeto con la cámara y poner el código en el módulo de la hoja, al seleccionar la celda C6 el cuadro se desplaza de esta manera



Si seleccionamos la celda N3, el cuadro se moverá para sobreponerse al rango O2:P5.



Usamos "On Error Resume Next" para evitar que el código produzca un error y se detenga en caso de seleccionar alguna celda en la fila 1.

Technorati Tags: MS Excel

Cálculo de interés con Excel - versión mejorada

En la nota anterior sobre el tema de cálculo de intereses mostramos cómo construir una fórmula para esta tarea. El modelo que mostramos en la nota tiene un serio inconveniente: sólo sirve si las fechas del comienzo y fin del período caen en el mismo año. Por ejemplo, si la fecha de vencimiento del pago cae el 15/02/2008 y nuestro deudor pagó efectivamente la cuenta el 22/08/2009, la tabla de la nota anterior nos dará un resultado erróneo



Dado que varios lectores me han pedido que les envíe el archivo, y supongo que querrán usarlo para calcular intereses de morosos, mostraremos en esta nota cómo construir un real modelo de cálculo intereses.

Empezamos por diseñar nuestro modelo. Queremos que:

1 - Ingresado el monto adeudado, la fecha de vencimiento y la fecha de pago real, el modelo calcule los intereses por mora.

2- El modelo debe mostrar el detalle del cálculo por mes (estamos suponiendo que las tasas de interés cambian solo por meses).

3- El detalle del cálculo mostrar sólo los meses relevantes al cálculo. Es decir, sólo los meses del período del cálculo.

4 - Las tasas de interés mensuales deben ser definidas en forma dinámica, es decir, no serán ingresadas por el usuario sino definidas automáticamente por el modelo de acuerdo al período relevante (mes/año).

Nuestro primer paso será crear el "esqueleto" de nuestro modelo



Partiendo de la base que el número máximo de meses que puede haber en un cálculo sea 60 (5 años), el rango de la tabla del detalle será A10:A69 (sin incluir los encabezamientos)

Nuestro segundo paso será crear las fórmulas necesarias en las celdas B4, B5 y en la tabla del detalle del cálculo.

La fórmula de B4 es sencilla: =SUMA(intereses), donde "intereses" es el nombre del rango D10:D69.

La fórmula de B5 es obviamente =B3+B4.

Ahora nos ocuparemos de la tabla de cálculo de intereses. En A10 ponemos

=FECHA(AÑO(B1),MES(B1),1)

Esta fórmula determina cuál es el primer día del mes de la fecha de vencimiento. Usamos formato personalizado para que se vea en pantalla como Mes-Año. A partir de esta celda calculamos los 59 meses subsiguientes, con la fórmula

=EDATE(A10,1)

Para usar la función EDATE (o FECHA:MES, según las definiciones del sistema) tiene que estar instalado el complemento Analysis ToolPak. FECHA.MES calcula el número de serie que representa la fecha que indica el número de meses anteriores o posteriores a la fecha especificada (argumento fecha_inicial). Copiamos esta fórmula en todo el rango A11:A69.

Para calcular la cantidad de días en cada mes de acuerdo a las fechas de inicio y fin del período de cálculo de intereses no podemos usar la fórmula que desarrollamos en la nota anterior. En su lugar usaremos esta otra fórmula desarrollada a partir de una sugerida por un compañero de trabajo de uno de mis lectores (y como no sé su nombre no puedo darle el crédito)

=SI((MIN($B$2,EOMONTH(A10,0))-MAX(EOMONTH(A10,-1)+1,$B$1))+1>0,MIN($B$2,EOMONTH(A10,0))-MAX(EOMONTH(A10,-1)+1,$B$1)+1,0)

Esta fórmula usa las funciones MIN y MAX para determinar si las fechas de vencimiento ($B$1) y la de pago ($B$2) caen dentro del mes de la fila. En caso afirmativo calcula cuantos días caen dentro del mes considerado, En caso negativo el resultado es 0.
Para evitar el uso de columnas auxiliares usamos la función FIN.MES (EOMONTH) para determinar el primer y el último día del mes de la fila en la tabla en forma dinámica.

Ahora tenemos que ocuparnos de la columna C en la tabla del detalle. Primero creamos una hoja adiciones en la cual ponemos una lista de las tasa de interés por mes y año



Esta tabla nos sirve como argumento en la fórmula que ponemos en las celdas de la columna C de la tabla

=BUSCARV(A10,tabla_de_interes,2,0)

La fórmula en la columna D de la tabla es obvia: =$B$3*(C10/365*B10)

Antes de seguir adelante probamos nuestras fórmulas:



Vemos con satisfacción que las fórmulas funcionan correctamente. El primer mes en la tabla del detalle (A10) es diciembre de 2007, la cantidad de días de cada mes ha sido calculada correctamente y la tasa de interés corresponde a los valores de la tabla de tasas.

Sólo nos queda por resolver cómo ocultar las filas no relevantes al cálculo. Esto lo hacemos con facilidad usando Formato Condicional. De hecho, queremos ocultar una fila en la tabla si en la cantidad de días en la fila es 0. Seleccionamos todo el rango de la tabla (A10:D69) y aplicamos formato condicional con esta fórmula



con estas definiciones para fuente



y estas para los bordes



Ahora nuestro modelo cumple todos los requisitos.




Technorati Tags: MS Excel