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.

Promedio ponderado en tablas con filas ocultas

Ya hemos tocado el tema de cómo calcular el promedio ponderado con Excel, si bien en la prehistoria de este blog, allá por el año 2006.

A diferencia del promedio normal, el promedio ponderado toma en cuenta el "peso" relativo de cada uno de los datos en relación al resto de los datos.

Por ejemplo, en este caso

El precio promedio, sin tomar en cuenta las cantidades vendidas por cada sucursal, es 15.50. Pero si tomamos en cuenta el "peso" de cada sucursal, vemos que en la mayor parte de las ventas el precio está por debajo del promedio, por lo cual el promedio ponderado noos da 12.84.
El promedio ponderado lo calculamos con la fórmula

=SUMAPRODUCTO(C3:C6,D3:D6)/SUMA(C3:C6)

Alternativamente podríamos agregar totales a la tabla y calcular el promedio ponderado dividiendo el total de venta (cantidad x precio) por el total de piezas vendidas

Ahora vamos a complicar las cosas y ver cómo podemos calcular el promedio ponderado en tablas filtradas (tablas en las que hemos aplicado Autofiltro u ocultado filas).

Veamos este ejemplo

El promedio sencillo del precio lo podemos calcular con SUBTOTALES, que ignora las celdas ocultas

Para calcular el promedio ponderado se nos presenta el problema el problema que, a diferencia de la función SUBTOTALES, SUMPRODUCTO toma en cuenta también las celdas ocultas en el rango.

Una forma de calcular el promedio ponderado en listas filtradas es usar columnas auxiliares (en este caso una única columna  auxiliar), usando la función SUBTOTALES con la opción 103 (CONTARA)

Al referirse a una única celda la función dará un valor 0 si la celda está en una fila oculta y 1 si la fila es visible. Luego podemos usar esta columna para calcular el promedio ponderado usando SUMPRODUCTO

=SUMAPRODUCTO(D4:D15*E4:E15*F4:F15)/SUMAPRODUCTO(D4:D15*F4:F15)

Si queremos evitar el uso de columnas auxiliares podemos usar esta fórmula que combina SUMAPRODUCTO con SUBTOTALES y DESREF

=SUMAPRODUCTO(SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1)),D4:D15,E4:E15)/SUBTOTALES(9,D4:D15)

La expresión

SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1))

da un vector de 1 (fila visible) o 0 (fila oculta). Para comprobarlo podemos seleccionar la expresión en la barra de las fórmulas y apretar F9 para ver el resultado

Esta fórmula de autoría incierta (posiblemente Bob Philips), aparece en varios foros de Excel.

La función SUBTOTALES con criterios.

Como ya sabemos, la función SUBTOTALES nos permite realizar cálculos con once operaciones distintas sin tomar en cuenta celdas ocultas. Esto es muy práctico cuando usamos Autofiltro

El resultado de la fórmula refleja sólo los valores de las filas visibles. Un lector me pregunta si se puede hacer el cálculo de tal manera que SUBTOTALES sólo tome en cuenta los valores positivos (o negativos, o distintos de cero, es decir, aplicando algún criterio al cálculo).

Una forma de hacerlo sería agregando una columna auxiliar y luego aplicar el filtro a la columna del criterio y a la columna auxiliar. Por ejemplo

También se puede hacer con fórmulas, que es lo que vamos a mostrar en esta nota. La ventaja de hacerlo con fórmulas es que podemos mostrar distintos resultados, por ejemplo el total de los montos positivos y el total de los negativos, en una misma vista de la hoja.

Supongamos que queremos ver el total de los montos positivos en la celda B1 y el total de los negativos en D1

La celda B1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14>0))


La celda D1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14<0))

Al filtrar por el criterio “a”, por ejemplo, veremos

subtcrit04

Analicemos la fórmula:

DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1) crea una matriz que contiene los valores del rango B4:B14

La expresión ($B$4:$B$14<0) crea una martiz de valores FALSO o VERDADERO

Al multiplicar ambas expresiones entre sí obtenemos un matriz donde los valores negativos han sido remplazados por ceros

Usamos estas expresiones como argumentos de la función SUBTOTALES con el operador 9 (suma).

La función SUMAPRODUCTO opera en forma matricial, por lo que nos permite realizar todas estas operaciones en una única celda.

Para calcular el subtotal de los valores negativos usamos ($B$4:$B$14<0); si quisiéramos hacer cálculos que no incluya ceros usaríamos ($B$4:$B$14<>0).