Página 1 de 1

[RESUELTO] Buscar máximo o mínimo con varias condiciones

NotaPublicado: Vie Jun 15, 2018 7:40 pm
por pepegotera
Buenas tardes. Tengo una hoja de Calc con 4 columnas (fecha, hora (en formato HHMMSS), y 2 columnas de datos) y quiero buscar el máximo y el mínimo para cada fecha entre 2 horas determinadas (inicio y final en las celdas G1:H2).
En el archivo adjunto lo he conseguido usando fórmulas matriciales pero tengo que hacer esta operación con muchos datos y no puedo simplemente arrastrar la celda para actualizar las fórmulas (o no sé hacerlo).
En el foro he visto algún ejemplo para buscar el mínimo usando la función SUMA.PRODUCTO(), que me facilitaría mucho la copia de fórmulas y su actualización, aunque al incluir el filtro de las 2 horas no consigo el resultado correcto.
¿Es posible buscar el máximo el máximo y el mínimo para cada fecha entre 2 horas determinadas usando la función SUMA.PRODUCTO()?

Re: Buscar máximo o mínimo con varias condiciones

NotaPublicado: Lun Jun 18, 2018 6:00 pm
por PepeOooSevilla
Hola.

En el archivo adjunto va una posible solución sin usar fórmulas matriciales pero teniendo que crear un columna auxiliar para encontrar el comienzo del rango de celdas a comparar para hallar el máximo y el mínimo. He tenido que eliminar miles de filas para que no sea tan "pesado" el archivo.

Intento explicarme:

    1º. Las fechas y horas son consecutivas luego en la columna auxiliar (columna C) escribo la fórmula:
    Código: Seleccionar todo   Expandir vistaContraer vista
    Celda C2 =TEXTO(A2;"AAAAMMDD")&TEXTO(B2;"000000")

    Con lo cual me garantizo un campo índice único sin repeticiones.
    2º. Utilizo la función DESREF(Referencia; Filas; Columnas; Altura; Anchura) para definir el rango de celdas donde buscar el máximo y el mínimo:
    Código: Seleccionar todo   Expandir vistaContraer vista
    Celda J6 =MAX(DESREF($A$2; COINCIDIR(TEXTO(H6;"AAAAMMDD")&TEXTO($I$1;"000000");$C$2:$C$1222;0)-1; 3; SUMA.PRODUCTO($A$2:$A$1222=H6;$B$2:$B$1222>=$I$1;$B$2:$B$1222<=$I$2)))

    Referencia: $A$2. Es la referencia desde la que la función busca una nueva referencia (ver la Ayuda de CALC).

    Filas: COINCIDIR(TEXTO(H6;"AAAAMMDD")&TEXTO($I$1;"000000");$C$2:$C$1222;0)-1. Devuelve la posición en que se encuentra la fila que coincide con la fecha y la hora especificadas; se le resta 1 porque esta función no empieza a contar desde 0 sino desde 1. Es el número de filas en que se corrigió la referencia hacia arriba (valor negativo) o hacia abajo. Se usa 0 para permanecer en la misma fila.

    Columnas: 3 (empezando en la columna A, 3 columnas a la derecha, es decir, la columna D para el máximo; 4 para la columna E que es el mínimo). Es el número de columnas por el cual se corrigió la referencia hacia la izquierda (valor negativo) o la derecha. Utilice 0 para quedarse en la misma columna.

    Altura: SUMA.PRODUCTO($A$2:$A$1222=H6;$B$2:$B$1222>=$I$1;$B$2:$B$1222<=$I$2). O lo que es lo mismo, ¿cuántas filas cumplen con los valores proporcionados? También se puede usar la función CONTAR.SI.CONJUNTO($A$2:$A$1222;H6;$B$2:$B$1222;">="&$I$1;$B$2:$B$1222;"<="&$I$2). (Opcional) es la altura vertical de un área que comienza en la nueva posición de referencia.

    Anchura: <VACÍO>, ya que la anchura es 1. (Opcional) es la anchura horizontal de un área que comienza en la posición de referencia nueva.

Ahora puedes arrastrar la fórmula hacia abajo ...

Espero que no sea peor el remedio que la enfermedad :D .

Saludos cordiales.

Re: Buscar máximo o mínimo con varias condiciones

NotaPublicado: Lun Jun 18, 2018 8:22 pm
por fornelasa
Hola, solo como nota:
pepegotera dijo:
En el archivo adjunto lo he conseguido usando fórmulas matriciales pero tengo que hacer esta operación con muchos datos y no puedo simplemente arrastrar la celda para actualizar las fórmulas (o no sé hacerlo).

¿Lo intentamos con copiar pegar (control-c control-v) en vez de arrastrar?

[RESUELTO] Buscar máximo o mínimo con varias condiciones

NotaPublicado: Lun Jun 18, 2018 9:07 pm
por pepegotera
Hola a los dos y gracias por las respuestas.
Fornelasa, intenté tanto copiar y pegar como arrastrar. Al copiar y pegar después no podía modificar una celda de las copiadas. Al arrastrar después tenía que modificar la fecha en cada celda, lo que lo hacía inviable. Por alguna razón copiar y pegar funciona ahora, así que hay algo que no hacía bien.
Un saludo

Re: [RESUELTO] Buscar máximo o mínimo con varias condiciones

NotaPublicado: Lun Jun 18, 2018 10:11 pm
por fornelasa
Ok bien, solo para no dejar, aquí una opción más para evitar las formulas matriciales (aunque tal vez no nos guste la propuesta).
Saludos, Federico.

Re: [RESUELTO] Buscar máximo o mínimo con varias condiciones

NotaPublicado: Mar Jun 19, 2018 6:27 pm
por fornelasa
Aquí otro ejemplo usando la formula y archivo de PepeOooSevilla, para LibreOffice y sin la columna auxiliar.
Obvio al manejar muchos datos el calculo se ralenta mucho.

Saludos, Federico.