Como sumar los ultimos n valores de un rango con celdas vacías

Aún no se ha secado la tinta de mi último post sobre la función INDICE y, por una de esas fantásticas casualidades, recibo esta consulta:

¿cómo puedo sumar los últimos tres valores de un rango que contiene celdas vacías?

La casualidad consiste en que la solución nos permitirá demostrar que INDICE da como resultado referencias a rangos y como ésto nos permite crear rangos dinámicos.

Supongamos estos datos

La idea es sumar los últimos tres valores de cada fila; en H2 debe ser 280, en H3 debe ser 130, etc. En el pasado he publicado un post sobre cálculos con los últimos n valores de un rango, pero esa técnica suponía que en el rango no había celdas vacías.

La solución es usar esta fórmula matricial (fórmulas que se ingresan apretando simultáneamente Ctrl-Mayúsuculas-Enter)

=SUMA(G2:INDICE(A2:G2,K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)))

Analizamos la fórmula de adentro hacia afuera:

La expresión COLUMNA(A2:G2)*(A2:G2<>"") produce un vector de valores númericos que pueden ser el número de columna o 0 si la celda del rango está vacía. Podemos mostrar el resultado de esta expresión en forma no matricial, es decir, aplicada a cada celda del rango

El vector de valores creado es el argumento de la expresión

K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)

que da como resultado el tercer valor en orden decreciente, es decir 3 (los valores del vector son 6,5,3,2,1 y 0).

Nuestra expresión se ha reducido ahora a INDICE(A2:G2,3) y aquí es donde podemos ver que INDICE da como resultado un rango, no un valor. El resultado de esta expresión es $C$2 y por lo tanto nuestra fórmula es ahora =SUMA(G2:$C$2), es decir =SUMA($C$2:$G$2), lo que nos da el resultado deseado.


De la misma manera podemos aplicar esta fórmula a las columnas en lugar de las filas, utilizando la función FILA() en lugar de la COLUMNA() y corrigiendo los rangos consecuentemente

En este video explico el funcionamiento de la fórmula usando la herramienta Auditoría de fórmula

La función INDICE, todo (o casi todo) lo que hay que saber

En la nota Traducción automática en hoja de Excel propuse dar un premio (el Planificador de Tareas JLD) a los primeros tres lectores que respondieron acertádamente a la pregunta qué tiene de particular la forma la forma en que se usa la función INDICE en el ejemplo de la nota.
Si bien hubo tres ganadores, ninguna respuesta fue totalmente exacta, lo que me impulsó a escribir esta nota.

La función INDICE es una de las más importantes y versátiles funciones del arsenal de Excel (y hay quien la corona como la más importante endilgándole superlatívamente el adjetivo "imponente").

La función INDICE permite encontrar el valor de un elemento dentro de una rango. Este rango puede ser un "vector" (un rango unidimensional de una fila como A2:M2 o de una columna como  A2:A10), o una matriz (un rango "rectangular", por ejemplo A2:M10). Para encontrar el elemento buscado debemos incluir uno o dos índices dependiendo se si trata de un vector o una matriz.

En este ejemplo

INDICE crea una referencia a la celda A2, cuyo valor es 40.  En este otro ejemplo

usamos la misma sintaxis a pesar de que el rango es "horizontal". En este caso INDICE crea una referencia a la celda B1.

Podemos concluir que:

• que cuando usamos vectores (rangos unidimensionales) INDICE interpreta el segundo argumento como fila o columna dependiendo de la orientación de vector

• cuando se refiere a un rango, la función INDICE crea una referencia a una celda (o celdas) en el rango.

Cuando usamos INDICE para referirnos a rangos bi-dimensionales, usamos los argumentos de fila y columna para referirnos al elemento buscado en la matriz

En este ejemplo INDICE crea una referencia a la celda B3.

Hasta aquí posiblemente no haya aportado nada dramático a los conocimientos de mis lectores. Ahora los invito a ver esta situación:

Cuando cuando falta el parámetro de fila, o es 0,  INDICE se refiere a toda la columna (en nuestro caso la columna B ya que el parámetro de columna es 2).  Pueden comprobarlo con

34+44+50+26+79+51+57 = 341

De la misma manera, si falta el parámetro columna, INDICE se refiere a toda la fila

47+50+18 = 115

Podemos aprovechar esta propiedad de INDICE para crear rangos dinámicos. La ventaja de usar INDICE en lugar de DESREF o INDIRECTO es que estas dos últimas son volátiles mientras que INDICE no lo es.
Un rango dinámico con INDICE se ve así

=A1:INDICE(A:A,CONTARA(A:A))

donde A1 es la primer celda del rango. Esta fórmula supone que no hay celdas vacías entre A1 y la última celda con valor de la columna A. Para usar esta fórmula como rango dinámica creamos un nombre que se refiera a la fórmula, por ejemplo el nombre "rango_dinamico"

Ahora podemos ver que a medida que agregamos valores en la columna a, el resultado en la celda C1 se va actualizando

Hojas Excel con traducción automática

Recientemente tuve que crear una hoja que incluyera la posibilidad de presentar el contenido en distintos idiomas.
La idea era usar una única hoja, de hecho un dashboard, pero que el usuario pudiera elegir en qué idioma presentar los textos de las celdas y demás objetos en la hoja/dashboard.

Este ejemplo muestra el funcionamiento

Como puede apreciarse, cuando el usuario elige un idioma apretando alguno de los botones de opción, los titulas de los cuadros de datos y del gráfico cambian.

La traducción se puede hacer fácilmente usando la función INDICE, pero la técnica varía si se trata de una celda o de un objeto. Esto es lo que mostraré en esta nota.

Para hacerlo empezamos por crear un glosario, una tabla con los textos de las palabras que queremos traducir en los distintos idiomas. En nuestro caso creamos un glosario con Castellano, Inglés e Italiano

En la hoja del dashboard agregamos los botones de opción para la elección de los idiomas. En las definiciones del botón lo asociamos a una celda. Es una buena práctica usar las columnas de la izquierda como columnas auxiliares, de manera que podamos ocultarlas. En nuestro caso vinculamos los botones a la celda A3

Otra buena práctica en Excel es definir un nombre que se refiera a la celda; en nuestro caso definimos el nombre "cel_Idioma" que se refiere a la celda A3

También es recomendable enmarcar los botones de opción en un cuadro de grupos.

Cuando el usuario elige el Castellano, el valor de "cel_Idioma" es 1; si elige English, será 2 y si elige Italiano será 3.

En la celda F3 de la hoja dashboard ponemos esta fórmula:

=INDICE(glosario!B6:D6,cel_Idioma)

Esta fórmula usa el valor de "cel_Idioma" para extraer el texto en el idioma correspondiente en la tabla Glosario.

Pregunta con premio (copia gratuita totalmente funcional del Planificador de Proyectos) a las primeras tres respuestas correctas:

¿Que tiene de particular la fórmula en la celda F3 del ejemplo?

 La misma técnica empleamos para todas las celda cuyo contenido queremos traducir.

Si nos fijamos en el título de la lista desplegable del gráfico, veremos que se trata de un cuadro de texto "montado" sobre una celda.

No podemos ligar un cuadro de texto, o cualquier otra forma gráfica, a una fórmula pero si podemos hacerlo a una referencia. El rodeo para lograr el efecto de traducción consiste en poner la fórmula con la función INDICE que extrae la traducción del Glosario en una celda de la columna A (de manera que luego podamos ocultarla).

Creamos un cuadro de texto (o cualquier otra forma) y la superponemos en la celda adecuada (K2 en nuestro ejemplo). Luego seleccionamos el cuadro de texto y en la barra de las fórmulas ponemos =A9 (o ponemos = y luego hacemos un clic en la celda A9)

La celda A9 contiene la fórmula

=INDICE(glosario!B14:D14,cel_Idioma)

Otra situación es si usamos tablas dinámmicas. A pesar que las tablas ocupan celdas, no podemos introducir fórmulas en los encabezamientos de las áreas (si podemos cambiar el texto).
Para superar este problema usamos nuevamente cuadros de texto ligados a celdas ocultas que contienen la fórmula con la función INDICE. Para mimetizar los cuadros de texto con el resto de la tabla le damos un relleno sólido con el mismo color que la tabla

En este video podemos ver el dashboard en funcionamiento