Analizar rango (fila a fila) y obtener unos calculos en unas celdas,,,,

Voy a poner a continuación un resumen explicativo de todo el caso, con
problemática y su solución. Evidentemente existirán distintas
alternativas pero la expuesta aquí solventa realmente y al 100% las
necesidades.


Tengo 2 tablas dadas:

TABLA 1:

B C D E
01 [ ][ ][ ][ ]
02 [ ][ fechaI ][ fechaE ][ fechaE+1]
03 [ 01 ][ ][ ][ ]
04 [ 02 ][ 12-jul ][ 14-jul ][ 15-jul ]
05 [ 03 ][ ][ ][ ]
06 [ 04 ][ ][ ][ ]
07 [ 05 ][ ][ ][ ]
08 [ 06 ][ ][ ][ ]
09 [ 07 ][ 18-jul ][ 19-jul ][ 20-jul ]
10 [ 08 ][ ][ ][ ]
11 [ 09 ][ ][ ][ ]
12 [ 10 ][ ][ ][ ]
13 [ 11 ][ ][ ][ ]
14 [ 12 ][ 22-jul ][ 24-jul ][ 25-jul ]
15 [ 13 ][ ][ ][ ]
16 [ 14 ][ ][ ][ ]
17 [ 15 ][ ][ ][ ]
18 [ 16 ][ ][ ][ ]
19 [ 17 ][ 27-jul ][ 28-jul ][ 29-jul ]
20 [ 18 ][ ][ ][ ]
21 [ 19 ][ 30-jul ][ 30-jul ][ 31-jul ]
22 [ 20 ][ ][ ][ ]

Es de donde se extraen los datos. En si es una tabla dinámica dónde
las fechas vienen dadas de otra parte (en el mismo XLS), pero que
siempre pueden cambiar.
Lo que esta claro es que:
• Las fechas de ‘fechaI', ‘fechaE' y ‘fechaE+1' nunca se repiten
excepto:
• Las fechas de "fechaE" se pueden repetir solo en el caso excepcional
cómo se puede ver en la fila 21, donde en ambas es 30-jul, pero en
ningún caso se podría repetir esta fecha en cualquier otra fila. Asi
pues en todas las filas se podria repetir la fecha en la columna
FechaE con la FechaI, pero en ninguna otra parte.
• La fecha de "fechaE+1" = "fechaE" + 1 (como su propio nombre
indica). Esta tabla nunca estaría vacía (si el resto de las celdas de
la fila contienen una fecha).

TABLA 2:

L M N
01 [ ][ ][ 0 ]
02 [ ][ ][ 0 ]
03 [ ][ fecha ][ 0 ]
04 [ 01 ][ 01-jul ][ 0 ]
05 [ 02 ][ 02-jul ][ 0 ]
06 [ 03 ][ 03-jul ][ 0 ]
07 [ 04 ][ 04-jul ][ 0 ]
08 [ 05 ][ 05-jul ][ 0 ]
09 [ 06 ][ 06-jul ][ 0 ]
10 [ 07 ][ 07-jul ][ 0 ]
11 [ 08 ][ 08-jul ][ 0 ]
12 [ 09 ][ 09-jul ][ 0 ]
13 [ 10 ][ 10-jul ][ 0 ]
14 [ 11 ][ 11-jul ][ 0 ]
15 [ 12 ][ 12-jul ][ 1 ]
16 [ 13 ][ 13-jul ][ 1 ]
17 [ 14 ][ 14-jul ][ 1 ]
18 [ 15 ][ 15-jul ][ 2 ]
19 [ 16 ][ 16-jul ][ 0 ]
20 [ 17 ][ 17-jul ][ 0 ]
21 [ 18 ][ 18-jul ][ 1 ]
22 [ 19 ][ 19-jul ][ 1 ]
23 [ 20 ][ 20-jul ][ 2 ]
24 [ 21 ][ 21-jul ][ 0 ]
25 [ 22 ][ 22-jul ][ 1 ]
26 [ 23 ][ 23-jul ][ 1 ]
27 [ 24 ][ 24-jul ][ 1 ]
28 [ 25 ][ 25-jul ][ 2 ]
29 [ 26 ][ 26-jul ][ 0 ]
30 [ 27 ][ 27-jul ][ 1 ]
31 [ 28 ][ 28-jul ][ 1 ]
32 [ 29 ][ 29-jul ][ 2 ]
33 [ 30 ][ 30-jul ][ 1 ]
34 [ 31 ][ 31-jul ][ 2 ]

En esta tenemos la columna M donde están una serie de fechas (en este
caso todo el mes de julio). En la columna N tenemos todo vacío, que de
hecho será seleccionado entre 0, 1 y 2.

PROBLEMÁTICA:

Necesitamos que cada fila de la TABLA2 analice todas las filas de la
TABLA 1 para obtener un resultado entre (0,1 o 2). Exactamente
insertamos una formula en cada celda N de cada fila de esta TABLA2.
Esta fórmula analiza el RANGO (C3:C22) fila a fila con la siguientes
preguntas:

if M4à then N4=1 else
if M4>C3 & M4=<D3 then N4=1 else
if M4ã then N4=2


Evidentemente esta formula de arriba seria valida para la celda N4.
Para la N5 simplemente realizaríamos los cambios pertinentes donde
indica N4 x N5, y así consecutivamente con N6, N7…..N34.

Así pues, en la celda N4 insertaríamos la siguiente formula:

=1*(NOT(ISERROR(MATCH(M4;$C$3:$C$22;0))))+MAX(($C$3:$C$22<M4)*(M4<=$D$3:$D$22))+2*(NOT(ISERROR(MATCH(M4;$E$3:$E$22;0))))

Nota:
• la formula se ha de insertar completamente (las 2 filas, pues es una
en si) en la celda.
• Esta formula es la correcta cuando nuestro Excel esta en ingles. En
caso de estar en Español, la formula seria como sigue:

=1*(no(eserror(coincidir(m4,$c$3:$c$22,0))))+max(($c$3:$c$22<m4)*(m4<=$d$3:$d$22))+2*(no(eserror(coincidir(m4,$e$3:$e$22,0))))

• Las mayúsculas o minúsculas realmente no importan.
• Otra cosa a tener presente es el caso de las [,], o sea [….coincidir
(m4,$c$3:$c$22,0)…]. En algunos sistemas esta tendrá que ser [;] en
vez (tal y cómo lo podemos ver en la fórmula en inglés de arriba.
• Por último, cuando copiamos esta formula en la celda correspondiente
N4, la forma de activarla no es realmente pulsando sobre [‘ENTER']
(como los profanos creeríamos) sino que este tipo de fórmulas (más
detalles leed en este mismo forum un poco antes, es decir.. días antes
de la mano de Hector) se activan pulsando [‘CONTROL' + ‘SHIFT' +
‘ENTER']
• Una vez tenemos la formula activa en N4, simplemente traspasamos la
misma a las siguiente celdas de la columna con el método clásico de
Excel con el ratón y listos.

Como ultimo detalle indicar que en la TABLA2 se indica en la columna N
el resultado de aplicar la formula correctamente en la misma
(partiendo de los datos que contiene la TABLA1).

Espero haya quedado todo resumido y contemplado y sirva esto de buena
y rápida guia para los que buscabais esta solución.


Una vez más, agradecemos esto a Hector por su amable ayuda y
dedicación.

Victor