Calcular días por períodos

Supongamos que queremos calcular el interés a cobrar por una deuda (por ejemplo, un pago atrasado). Durante el lapso transcurrido hay períodos con distintas tasas de interés.
El problema consiste en calcular cuantos días del lapso de la deuda caen en cada período de interés.
Consideremos esta ejemplo (el cuaderno es interactivo y puede descargarse)





El lapso de la deuda corre del 15/02/2014 al 27/06/2014, 133 días. Durante este lapso hay tres períodos de interes, tal como aparece en el rango B6:E8.

Nuestra tarea es asignar los 133 días a los distintos períodos de interés, tal como aparece en el rango F6:F8. La celda F6 contiene la fórmula

=SUMAPRODUCTO((((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))>=$C$3)*(((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3))

En esta fórmula usamos la técnica que ya mostré en la nota "Calcular días por años entre dos fechas". El funcionamiento es el siguiente:

• la expresión (C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1)) al estar dentro de la función SUMAPRODUCTO crea un vector de fechas , en nuestro caso {01/01/2014,02/01/2014,03/01/2014...,31/03/2014}

• al comparar los miembros de este vector con la expresión >=$C$3 creamos un vector con los valores VERDADERO o FALSO (según se cumpla la condición o no)

• la segunda expresión ((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3) funciona de la misma manera para la fecha del fin del lapso

• SUMAPRODUCTO multipllica ambos vectores resultando 1 cuando se multiplican dos valores VERDADERO y 0 para los restantes casos. La suma interna del vector da como resultado el número de días comprendido dentro del período de interés.

Comentarios en tablas dinámicas.

Con un pie en el avión rumbo a Italia (viaje de trabajo y placer, lamentablemente no en las proporciones deseadas) recibo un llamado de un amigo consultándome sobre comentarios en tablas dinámicas.

- ¿Cómo hago para poner poner un comentario en una tabla dinámica?
- ¿Dónde en la tabla dinámica?
- ¿Cómo dónde en la tabla? ¡En una celda de la tabla! ¿Dónde va a ser      sino?
- Bien, sucede que las tablas dinámicas no tienen celdas, eeeehh, tienen   pero no exactamente, es decir, eeehh, bueno...
- ¿Te podés explicar por favor?!!

Me explico. Por lo general usamos el menú contextual de para poner un comentario en una celda

Pero si intentamos hacerlo en una celda que es parte de una tabla dinámica, la opción "Insertar comentario" no aparece en el menú contextual

Esto no es casual ni tampoco un error. El contenido de las celdas que ocupa una tabla dinámica cambian (obviamente en forma dinámica) con los cambios en los datos de la base de datos que la alimenta o cuando cambiamos el enfoque de la tabla. Pero los comentarios queda ligados a la celda y no dependen del valor que le asigne a esa celda la tabla dinámica.

A pesar de esto podemos introducir un comentario en la celda usando el menú de la cinta Revisar-Comentarios-Nuevo Comentario

Supongamos que hemos puesto un comentario en la celda C8 del ejemplo explicando por qué las ventas de Brasil han crecido en un 50%

Sin embargo cuando apuntamos a la celda no podremos ver el comentario; en su lugar aparecerá la información contextual relacionada al valor de la tabla que ocupa la celda

Para que aparezca el comentario tenemos que modificar la propiedad "Mostrar información contextual..." en el menú de "Opciones de tabla dinámica-Mostrar"

Lamentablemente aquí no terminan nuestros penares. 

Ahora quitemos a Brasil del informe usando el filtro del campo

El resultado será que ahora Canadá ocupa la celda C8, pero el comentario sobre Brasil sigue apareciendo en la celda

Conclusión: no usar comentarios en celdas ocupadas por valores de una tabla dinámica; y si queremos usarlos, convertir la tabla dinámica en una tabla estática (copiar-pegar valores).

Lista desplegable con contenido condicional - versión con Vba

Supongamos este escenario:

tenemos una lista que asocia nombres con valores

donde los nombres se repiten.

Queremos crear una lista desplegable que muestre los valores asociados al nombre que eliljamos en la celda G2 de este ejemplo

Vamos a mostrar como está construido este modelo.

Comenzamos por mostrar la columna A que están oculta. Esta columna contiene un rango dinámico donde ponemos los valores asociados al nombre que aparece en la celda G2.

El rango D1:E14 que contiene los nombres y sus valores está definido como Tabla.
La columna A contiene los valores asociados al nombre introducido en la celda G2.
Para poner estos valores usamos un evento de tipo Change

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$G$2" Then
        Range("tblNombreValor[#All]").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("A1"), Unique:=False
    End If
 
End Sub

El código usa la funcionalidad Filtro Avanzado (Datos - Ordenar y Filtrar - Avanzadas) que nos permite filtrar una tabla y copiar los resultados a otro rango.

Definimos un nombre que se refiere a este rango dinámicamente con esta fórmula

=Hoja1!$A$2:INDICE(Hoja1!$A:$A,CONTARA(Hoja1!$A:$A))

Ahora definimos una validación de datos en la celda H2 con la opción Lista y el nombre definido ListValores en la casilla Origen

Con esto concluimos la construcción del modelo. El archivo del ejemplo se puede descargar aquí.

En cuanto al formato condicional para señalar las filas del nombre introducido en G2, usamos la opción Formula con esta definición