La función CELDA de Excel

En un post anterior sobre funciones Excel raramente usadas tendría que haber mencionado a la función CELDA(). Por ejemplo, a lo largo y a lo ancho de los 670 posts de esta blog, hay solamente dos menciones a esta función.

Tal como lo insinúa su nombre, esta función da como resultado información sobre una celda y su entorno. La función CELDA() acepta dos argumentos, el tipo de información, que es obligatorio y la celda de referencia, que es opcional.

El uso más obvio de esta función es en fórmulas donde el resultado depende de cierta característica del contenido de la celda o del entorno donde se halla.

Esta función tiene dos características importantes:

1. es volátil, es decir que es recalculada con cualquier cambio en la hoja. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones. 
2. Si se omite la referencia a una celda la información especificada en el argumento tipo_de_info el resultado muestra la información de la última celda cambiada

Veamos algunos usos posibles.

Determinar si la celda contiene una fecha.

Excel no tiene una función nativa para determinar si el valor de una celda es una fecha (existe la función ESNUMERO pero no la función ESFECHA). Podemos usar la opción "Formato" para evaluar si el contenido es una fecha en lugar de usar la función IsDate de Vba (lo que implica crear una UDF).

Sucede que si la celda contiene una fecha la función CELDA con la opción Formato da como resultado "Dx", donde "x" es un número que identifica el tipo de formato

En este ejemplo, la fórmula =IZQUIERDA(CELDA("formato",B5),1)="D" nos permite determinar que la celda B5 contiene una fecha

SUBTOTALES con columnas ocultas 

Podemos hacerlo usando la opción "ancho" que da el ancho de la columna de referencia como número entero (SUBTOTALES realiza cálculos sin tomar en cuenta filas ocultas, pero no funciona con columnas ocultas). 

Si la columna esta oculta su ancho es 0, dato que podemor usar en conjunto con SUMAPRODUCTO tal como muestra Ismael Romero en esta nota de su blog Excelforo

El rango C1:H1 contiene la fórmula =CELDA("ancho",C4)<>0; la celda I4 contiene la fórmula =SUMAPRODUCTO(C1:H1*C4:H4).

Al multiplicar las matriz C1:H1 por la matriz C4:H4, SUMAPRODUCTO convierte los valores VERDADERO en 1 y los FALSO en 0.

Hay que tomar en cuenta que ocultar o mostrar filas no dispara el mecanismo de cálculo de Excel y por lo tanto hay que apretar F9 para actualizar el resultado de la fórmula.

Extaer el nombre de la hoja: 

Lo hacemos usando la opción "nombrearchivo" que da la  ruta de acceso completa de la celda referencia, en forma de texto.  

Supongamos que tenemos un cuaderno con una hoja por mes; si el nombre de la hoja es "agosto", esta fórmula dará como resultado "agosto"

=EXTRAE(CELDA("nombrearchivo",A1),ENCONTRAR("]",CELDA("nombrearchivo",A1))+1,256)

En esta fórmula es obligatorio usar la referencia a una celda de la hoja ya que de lo contrario elresultado será el nombre de la hoja donde se produjo el último cambio.

Ordenar valores del filtro de informe en tablas dinámicas

Las tablas dinámicas presentan cuatro áreas a las cuales podemos arrastrar los campos de la base de datos que la alimenta: etiquetas de filas, etiquetas de columna, valores y filtro de informe

Por defecto, los valores de las áreas de etiquetas, columnas y filtro del informe están ordenadas alfabéticamente, de A a Z, y el orden de los valores depende del orden del área de las filas.
Para cambiar el orden por defecto de las eetiquetas de fila y de columna podemos usar el menú de edición de estas áreas

El filtro de informe no cuenta con un menú de edición y por lo tanto no tenemos una forma directa de cambiar el orden de los valores (que por defecto es de A a Z). Pero podemos hacerlo con un pequeño truco.
En nuestro ejemplo tenemos un único campo, País, en el área de filtro de informe

y como puede apreciarse está ordenado de A a Z. Para cambiar este orden reubicamos el campo del filtro de informe en el área de filas

Ahora podemos, por ejemplo, elegir "Ordenar de Z a A", apretar Aceptar y luego volver a ubicar el campo en el área de filtro de informe

con lo cual hemos invertido el orden por defecto del campo.

Ahora supongamos que queremos que los valores del filtro de informe aparezcan ordenados de mayor a menor según el total de ventas de cada país. Para lograrlo empezamos por quitar temporariamente el campo Ciudad y ubicar el campo País en el área de las filas. Abrimos el menú de edicion del campo y en "Más opciones de ordenación" elegimos "Descendente..." y "Por Ventas"

Pero esto no es suficiente. Ahora tenemos que apretar el botón "Más opciones" (en el ángulo inferior izquierdo del formulario, en la imagen está parcialmente oculto) y quitar la marca de la opción "Ordenar automáticamente cada vez que se actualice el informe)

Apretamos Aceptar para que el cambio cobre efecto

Cálculos de interés compuesto con Excel

En este post voy a tocar el tema del cálculo con Excel del interés compuesto. Supongo que la mayoría de mis lectores, en particular aquellos que ocupan su tiempo de trabajo con temas financieros, están familiarizados con el tema. Para quien no lo esté digamos, en pocas palabras, que el interés compuesto consiste en tomar en cuenta el interés acumulado en los períodos anteriores al calcular el interés de un período, o citando a Wikipedia

El interés compuesto representa la acumulación de intereses que se han generado en un período determinado por un capital inicial (CI) o principal a una tasa de interés (r) durante (n) periodos de imposición, de modo que los intereses que se obtienen al final de cada período de inversión no se retiran sino que se reinvierten o añaden al capital inicial, es decir, se capitalizan.

Este ejemplo muestra la aplicación de la definición

Por supuesto con Excel podemos ser un poco más sofisticados y hacer el cálculo en una única celda usando la expresión =C2*(1+C3)^C4 o con =C2*POTENCIA(1+C3,C4)

Pero podemos ser aún más sofisticados y usar la función VF (valor futuro) dejando el argumento Pago de la función en blanco

Hasta ahora hemos asumido una tasa de interés constante para todos los períodos. Supongamos que cada año tenemos una tasa de interés distinta. En este caso no podemos usar la función VF. Pero Excel viene provisto con 55 funciones financieras y una de ellas es VF.PLAN que nos permite hacer el cálculo fácilmente, como podemos ver en este ejemplo:

La función VF.PLAN tiene dos argumentos: el capital inicial y un rango que contiene las tasas de interés a tomar en cuenta que Excel usa como matriz para realizar el cálculo

Podemos darle dinamismo a nuestro modelo usando Tablas (como ya saben, soy fanático de las tablas). En nuestro ejemplo creamos la tabla "tblInteres" y la usamos en nuestra fórmula

De esta manera cuando agregamos o quitamos períodos la fórmula se ajusta automáticamente

Ahora surge la pregunta, si tenemos distintas tasas en distintos períodos ¿cómo calculamos la tasa promedio? Esta tasa es conocida como TCAC (Tasa de Crecimiento Anual Compuesto) también conocida como CAGR (en inglés Compound Annual Growth Rate).
Dados:

• V(t_0) : valor inicial, 
• V(t_n) : valor final, 
• t_n - t_0 : número de años

la expresión para calcular la TCAC (CAGR) es

En nuestro ejemplo calculamos la TCAC con la expresión   =(C3/C2)^(1/5)-1

Podemos también usar la función TASA, dejando el argumento Pago en blanco y poniendo el capital inicial como número negativo (o el valor futuro como negativo y el inicial como positivo)

Es una norma de buena práctica evitar el uso de constantes en nuestras fórmulas; por lo tanto en lugar de la constante 5 para el número de períodos usamos la fórmula CONTARA(tblInteres[Período]) quedando
=TASA(CONTARA(tblInteres[Período]),,-C2,C3)