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

Calcular días por años entre dos fechas

Supongamos una lista de fechas de inicio y finalización de tareas (o contratos o cualquier otra actividad) que se extienden a más de una año, como en este ejemplo:

Podemos ver, por ejemplo, que la Tarea 1 empieza en el 2013 y termina en el 2015. ¿Cómo hacemos para calcular cuántos días caen en cada año?

Para resolver este problema vamos a usar una técnica no tan conocida: crear dentro de la fórmula un vector implícito (o "virtual"). El concepto quedará más claro cuando expliquemos la fórmula.

La columna "Total de días" muestra el total entre ambas fechas (incluida el día del comienzo) usando la fórmula =SIFECHA(C4,D4,"d")+1

Para poder calcular cuantos días caen dentro de cada año (2013,2014,2015), vamos a agregar tres columnas en nuestra tablam una para cada año, y en la celda F4 ponemos esta fórmula (que luego copiamos al resto de la tabla):

=SUMAPRODUCTO(--(AÑO($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))))=F$3))

Vamos a "disecar" la fórmula de adentro hacia afuera:

• La expresión "($D4-$C4+1)" calcula el total de días transcurridos entre el principio y el fin de la tarea. Es equivalente al cálculo en la celda E4.
• La expresión INDIRECTO("1:"&($D4-$C4+1)) crea una referencia al rango "1:750". Al usar esta expresión como argumento de la función FILA en forma matricial (al estar dentro de la función SUMAPRODUCTO), obtenemos un vector con los valores 1,2,3,...,750
• Al sumar a $C4 este vector en ($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))), obtenemos un vector de fechas día por día que comienza el 06/07/2013 y termina con el 25/07/2015
• La función AÑO extrae de cada fecha el año correspondiente, por lo que obtenemos un vector de años con 750 puntos.
• Este vector los comparamos con el año que aparece en la celda F$3, por lo que obtenemos un vector de valores VERDADERO o FALSO (si se cumple o no la condición).
• La doble negación a la izquierda de la función AÑO convierta los valores VERDADERO en 1 y los FALSO en 0. Igualmente podríamos haber multiplicado la expresión por 1.
• Finalmente SUMAPRODUCTO suma todos los valores del vector que cumplen con la condición. Recordemos, como puse más arriba, que SUMAPRODUCTO se comporta en forma matricial.

Podemos visualizar el proceso del cálculo usando la herramienta "Evaluar fórmula" en "Fórmulas-Auditoría de fórmulas"

En este video comento el proceso