Fórmulas desbordadas: Cómo anexar rangos discontinuos.

Unos días atrás un buen amigo me preguntaba por la mejor manera de recuperar datos de una plantilla tipo agenda, donde registraba información por días de la semana y por horas...

Podría haber intentado adaptar de alguna manera la explicación publicada en esta entrada, pero lo pensé dos veces después de leer este fantástio post de Mourad Louha (también MVP Excel).
Mourad, en su publicación anexa la información contenida en dos tablas, independientemente del número de registros de cada una de ellas... fantástico ejercicio que reproduzco antes de meterme en faena con mi problema.
Si tenemos dos tablas con la misma estructura de campos (País y Unidades) y queremos anexarlas y tenerlas en una única fuente emplearemos la siguiente fórmula:
=LET(
Cols;SECUENCIA(1;COLUMNAS(Tabla1));
Filas1;FILAS(Tabla1);
Filas2;FILAS(Tabla2);
TotalFilas;SECUENCIA(Filas1+Filas2);
SI(TotalFilas<=Filas1;INDICE(Tabla1;TotalFilas;Cols);INDICE(Tabla2;TotalFilas-Filas1;Cols)))

La genialidad de la fórmula es su comprotamiento matricial, ya que según se aumenta el número total de las filas (suma de filas de ambas tablas), el condicional controla de dónde recuperar los datos con unas funciones INDICE...
En este ejemplo, además, Mourad emplea la función nueva LET para facilitar la lectura... Fantástico!!.
Gracias Mourad.

Esta idea me llevo a pensar que en mi ejercicio, con rangos constantes (de igual tamaño: columnas y filas) pero mayor número de rangos podría implementar alguna mejora a su función.. y es que con el ejemplo que desarrollaremos, bajo el supuesto de igual tamaño de las fuentes, no hay limitación ni dificultad añadida en la composición de la fórmula.

Comenzamos asignando nombres definidos a nuestros rangos...
día1 =Hoja2!$C$2:$C$8
día2 =Hoja2!$E$2:$E$8
día3 =Hoja2!$G$2:$G$8
día4 =Hoja2!$I$2:$I$8
día5 =Hoja2!$K$2:$K$8
Esto siempre facilita el trabajo y la lectura de nuestras fórmulas ;-)
E insertamos nuestra fórmula en N2:
=LET(
rangos;(día1;día2;día3;día4;día5);
Areas;AREAS(rangos);
filas;FILAS(día1);
TotalFilas;SECUENCIA(Areas*filas);
NumArea;MULTIPLO.SUPERIOR.MAT(SECUENCIA(Areas*filas);filas)/filas;
Fila;TotalFilas-(NumArea-1)*filas;
INDICE(rangos;Fila;1;NumArea))
Lo cual genera un recorrido por cada rango, esto es, para cada día de la semana...
Importante el uso que haré de la función INDICE con su segunda sintáxis de referencia:
INDICE(ref, núm_fila, [núm_columna], [núm_área])
donde no solo recuperamos una posición de fila o columna, si no que además, lo hacemos de un rango origen indicado en el primer argumento!.
Analicemos la fórmula...
Lo primero que hacemos es listar el conjunto de rangos a consolidar. Así dentro del contexto de la función LET (leer algo más aquí) definimos la variable:
rangos como (día1;día2;día3;día4;día5) fundamental escribirlos entre paréntesis!!
Si tuvieramos más rangos los incluiríamos en este listado...

La segunda variable cuenta el número de rangos de la lista empleando la función AREAS:
Areas como AREAS(rangos) que en mi ejemplo devolverá 5

La tercera variable cuenta el número de filas de los rangos empleados... recuerda que la condición de partida es que todos los rangos tienen el mismo número de filas (y columnas)
filas como FILA(día1)

La siguiente variable generará una lista 'secuencial' de 1 hasta el número total de filas de todos los rangos (5 rangos x 7 filas cada rango = 35 filas para el ejemplo)
TotalFilas como SECUENCIA(Areas*filas)

Una nueva variable calcula una lista de 35 elementos con valores repetidos seguidos: siete 1, siete 2, siete 3, etc
NumArea como MULTIPLO.SUPERIOR.MAT(SECUENCIA(Areas*filas);filas)/filas
permitirá para cada fila asignar en qué rango (día1;día2;día3;día4;día5) nos encontramos, y del cual podemos recuperar información.

Otra variable importante es la que nos genera cinco secuencias seguidas con valores 1,2,3,4,5,6,7; es decir, del 1 hasta el número de filas por rango:
Fila como TotalFilas-(NumArea-1)*filas
Un poco de imaginación matemática para lograr el patrón de crecimiento...

Y por fin, con todas las variables declaradas, procedemos al cálculo... esto es, a la extracción de los datos de cada rango en un único listado:
INDICE(rangos;Fila;1;NumArea)
más simple imposible... del conjunto de 'rangos' recuperamos la 'Fila' de la columna 1 y del área 'NumArea'.
Es decir, recorremos de cada área todas sus filas antes de pasar al siguiente área...

Si los rangos definidos tuvieran varias columnas bastaría cambiar en este cálculo el 1 por una constante matricial que liste ese número de columnas, por ejemplo, {1\2}.

Una vez conseguido nuestro listado podríamos obtener con UNICOS una matriz de elementos únicos para realizar conteos u otros cálculos necesarios...
=UNICOS(LET(...))