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

Discute sobre la aplicación de hojas de cálculo
Responder
pepegotera
Mensajes: 2
Registrado: Vie Jun 15, 2018 7:18 pm

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

Mensaje 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()?
Adjuntos
MAX-MIN.ods
(91.04 KiB) Descargado 138 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
Avatar de Usuario
PepeOooSevilla
Mensajes: 1480
Registrado: Sab Abr 04, 2009 6:10 pm
Ubicación: Sevilla (España)

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

Mensaje 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

    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

    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) Descargado 134 veces
LibreOffice 6.4.6. Windows 10. Java 8 rev. 261 (64 bits)
Por favor, utiliza el Foro para tus consultas, no los mensajes privados
Si usas OpenOffice/LibreOffice trabaja y guarda en ODT, ODS, ODP, ... Y haz copias de seguridad.
Avatar de Usuario
fornelasa
Mensajes: 3268
Registrado: Jue Feb 17, 2011 8:30 pm
Ubicación: Estado de México, México.

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

Mensaje 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?
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!
pepegotera
Mensajes: 2
Registrado: Vie Jun 15, 2018 7:18 pm

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

Mensaje 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
OpenOffice 4.1.5 en Windows 10
Avatar de Usuario
fornelasa
Mensajes: 3268
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

Mensaje 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.
Adjuntos
MAX-MIN (1).ods
Buscar máximo o mínimo
(91.1 KiB) Descargado 140 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: 3268
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

Mensaje 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.
Adjuntos
MAX-MIN_v2 (1).ods
Resumen varios criterios
(103.32 KiB) Descargado 124 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!
Responder