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

Discute sobre la aplicación de hojas de cálculo

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

Notapor pepegotera » Vie Jun 15, 2018 7:40 pm

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()?
Adjuntos
MAX-MIN.ods
(91.04 KiB) 46 veces
Última edición por pepegotera el Lun Jun 18, 2018 9:09 pm, editado 1 vez en total
OpenOffice 4.1.5 en Windows 10
pepegotera
 
Mensajes: 2
Registrado: Vie Jun 15, 2018 7:18 pm

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

Notapor PepeOooSevilla » Lun Jun 18, 2018 6:00 pm

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.
Adjuntos
MAX-MIN_v2.ods
LibreOffice Calc
(61.79 KiB) 43 veces
LibreOffice 6.1.5 en Windows 7 / 10
Por favor, utiliza el Foro para tus consultas, no los mensajes privados
Avatar de Usuario
PepeOooSevilla
 
Mensajes: 1066
Registrado: Sab Abr 04, 2009 6:10 pm
Ubicación: Sevilla (España)

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

Notapor fornelasa » Lun Jun 18, 2018 8:22 pm

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?
lo 6.2.0 | aoo 4.1.6 | win 7/10
¡Un aplauso para todos los que luchan por proteger y promover la Web abierta!
Avatar de Usuario
fornelasa
 
Mensajes: 3216
Registrado: Jue Feb 17, 2011 8:30 pm
Ubicación: Estado de México, México.

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

Notapor pepegotera » Lun Jun 18, 2018 9:07 pm

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
OpenOffice 4.1.5 en Windows 10
pepegotera
 
Mensajes: 2
Registrado: Vie Jun 15, 2018 7:18 pm

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

Notapor fornelasa » Lun Jun 18, 2018 10:11 pm

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.
Adjuntos
MAX-MIN (1).ods
Buscar máximo o mínimo
(91.1 KiB) 46 veces
lo 6.2.0 | aoo 4.1.6 | win 7/10
¡Un aplauso para todos los que luchan por proteger y promover la Web abierta!
Avatar de Usuario
fornelasa
 
Mensajes: 3216
Registrado: Jue Feb 17, 2011 8:30 pm
Ubicación: Estado de México, México.

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

Notapor fornelasa » Mar Jun 19, 2018 6:27 pm

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.
Adjuntos
MAX-MIN_v2 (1).ods
Resumen varios criterios
(103.32 KiB) 37 veces
lo 6.2.0 | aoo 4.1.6 | win 7/10
¡Un aplauso para todos los que luchan por proteger y promover la Web abierta!
Avatar de Usuario
fornelasa
 
Mensajes: 3216
Registrado: Jue Feb 17, 2011 8:30 pm
Ubicación: Estado de México, México.


Volver a Calc

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 10 invitados