Excel - Máximo entre dos fechas que se incrementan

 
Vista:
sin imagen de perfil
Val: 8
Ha aumentado su posición en 2 puestos en Excel (en relación al último mes)
Gráfica de Excel

Máximo entre dos fechas que se incrementan

Publicado por adolfo (18 intervenciones) el 15/04/2023 19:23:43
Por acá en busca de un poco de ayuda: Tengo esta fórmula matricial:
=MAX(SI(ENTERO('1D'!A1439:A1445)>A208;SI(ENTERO('1D'!A1439:A1445)<=A209;'1D'!C1439:C1445))); funciona según lo requerido; que es buscar en la hoja 1D el valor mas alto de un rango, de acuerdo a dos fechas especificadas en otra hoja.
En la hoja 1D están los registros diarios; fecha y valor. En la otra hoja -que llamaremos 1W-, están las fechas por periodos de 7 días, se trata de que la fórmula busque en la hoja 1 el valor más alto en el periodo de siete días, de acuerdo a la fecha inicial de la semana especificada en 1W y la fecha final de esa hoja. El problema se da porque necesito copiar la fórmula en las celdas que siguen hacia abajo, pero que se incremente la cantidad de días.

Ejemplo: si en la celda C2 copio la fórmula
=MAX(SI(ENTERO('1D'!A1439:A1445)>A208;SI(ENTERO('1D'!A1439:A1445)<=A209;'1D'!C1439:C1445))), al copiarla a C3 debería quedar así:
=MAX(SI(ENTERO('1D'!A1446:A1452)>A210;SI(ENTERO('1D'!A1446:A1452)<=A211;'1D'!C1446:C1452))).
Nótese que en la 2da fórmula ha subido de 1439 a 1446 y de 1445 a 1452, de A208 a A210 y de A209 a A211. Este cambio lo estoy haciendo manualmente cada vez que copio la fórmula, y me gustaría automatizarlo.
Me recomendaron esta formula matricial, pero funciona en algunos casos y en otros no:
​=MIN(SI(('1D'!$A$1:$A$2000>A1)*('1D'!$A$1:$A$2000<=A2);'1D'!$D$1:$D$2000)). Tambien hay que considerar que la formula funcione cuando en el rango en que va a buscar el valor, hayan celdas con valor nulo o 0, para que los obvie
Gracias de antemano por sus comentarios
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
sin imagen de perfil
Val: 8
Ha aumentado su posición en 2 puestos en Excel (en relación al último mes)
Gráfica de Excel

Máximo entre dos fechas que se incrementan

Publicado por adolfo (18 intervenciones) el 15/04/2023 21:47:21
Gracias por la respuesta. No, no me sirve trabajar con tablas dinamicas
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Máximo entre dos fechas que se incrementan

Publicado por Antoni Masana (2498 intervenciones) el 16/04/2023 19:52:59
Después de estar devanando los sesos he llegado a esta solución.

Primero el problema esta mal planteado porque explicas lo que haces, mal porque no funciona, en lugar de que es lo que quieres conseguir.
Tu formula es esta que me ha costado entender, no que hace sino que quieres hacer:

1
2
3
=MAX(SI(ENTERO('1D'!A1:A7) >  A1;
     SI(ENTERO('1D'!A1:A7) <= A2;
               '1D'!C1:C7)))

Esta es la que realizado:

1
2
3
=MAX(SI(ENTERO(INDIRECTO(CONCATENAR("'1D'!A";COINCIDIR(A2;'1D'!A:A;1)-6;":A";COINCIDIR(A2;'1D'!A:A;1)-1))) >  A1;
     SI(ENTERO(INDIRECTO(CONCATENAR("'1D'!D";COINCIDIR(A2;'1D'!A:A;1)-6;":D";COINCIDIR(A2;'1D'!A:A;1)-1))) <= A2;
               INDIRECTO(CONCATENAR("'1D'!D";COINCIDIR(A2;'1D'!A:A;1)-6;":D";COINCIDIR(A2;'1D'!A:A;1)-6)))))

Y aquí sobra los condicionales y la formula que queda es esta:

1
=MAX(INDIRECTO(CONCATENAR("'1D'!C";COINCIDIR(A2;'1D'!A:A;1)-6;":C";COINCIDIR(A2;'1D'!A:A;1))))

¿Que hace esto?

Evaluamos la función COINCIDIR() a evaluar:

1
COINCIDIR(A2;'1D'!A:A;1)

Busca la fecha 05/05/2019 en la tabla de la hoja 1D y da que esta en la posición 7

Ahora cambio la fórmula por su valor:

1
=MAX(INDIRECTO(CONCATENAR("'1D'!C";7-6;":C";7)))

Y hago la resta:

1
=MAX(INDIRECTO(CONCATENAR("'1D'!C";1;":C";7)))

Esta función CONCATENAR convierte en una cadena string:

1
CONCATENAR("'1D'!C";1;":C";7)

Resultado

1
=MAX(INDIRECTO('1D'!C1:C7))

La Función INDIRECTO convier un texto en una referencia:

1
=MAX('1D'!C1:C7)

Y ya tienes una función que la réplicas en toda la columna y se ajusta a tu necesidad. La columna A de la hoja 1D debe tener todas las fechas desde el 28/04/2019 hasta el 16/04/2023, si falta un solo día la fórmula deja de funcionar.
Otra forma de conseguir lo mismo es con la fórmula en la columna F de la hoja 1D.
Y por supuesto usando las Tablas Dinámicas que pueden dar resumen por semana como explica Pepe en el post anterior.


Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar