Excel. Buscar MÁXIMO y devolver celdas contiguas
He visto que manejas muy bien lo de los máximos, tengo una pregunta al respecto.
He buscado resolver mi problema y hasta el momento no logro dar con la solución, tengo la siguiente tabla:
La hoja Resumen-MAX es la hoja principal, los datos están distribuidos en el resto de las hojas. Todo se basa en encontrar en las demás hojas el dato KW as KwTot (Columna I). Para encontrarlo, en la columna equipo las tres primeras letras define en que hoja se buscará, el resto es el valor a buscar, pero en este caso, todas las veces que aparezca se analizará solo dentro del horario definidos en la celda $G$2 a K2$J$2), tomemos de ejemplo el equipo CUS4040 de la fila 8, bien, en este caso irá a la hoja CUS, busará en la columna "kw as kw tot" el mayor pero solo aquellos que cumplan con el horario de 10 a 16 horas como está definido en la parte superior HORA INICIAL y HORA FINAL, sin tomar en cuenta la fecha (solo horario), la fórmula que te comparto lo hace y regresa el valor 2300, ¿pero ahora como decirle que me traiga los otros datos que requiero una ves localizado ese valor máximo?
Te anexo el archivo de pruebas (con datos reales), pesa algo como 2 MB para tratar de hacerlo lo más real, te comento que leí que las fórmulas matriciales que es la que uso, consumen mucha memoria, ojala tengas una solución alterna.
Ejemplo-Real
Nota: El filtrado que dejé en la hoja CUS es solo de prueba, los datos no van filtrados.
No puedo descargar archivos
Envíame un correo y me adjuntas tu archivo con los ejemplos.
También te envío el archivo de máximos con las fórmulas de máximos que viste en mi respuesta.
Mi correo [email protected]
En el asunto del correo escribe tu nombre de usuario “Fernando Vega”
Te anexo la macro para poner todos los datos en base al máximo
Sub Maximos()
'Por.Dante Amor
Application.ScreenUpdating = False
Set h1 = Sheets("Resumen-MAX")
Set ht = Sheets("TEMP")
'
hora1 = Format(h1.Range("H2"), "hh:mm:ss")
hora2 = Format(h1.Range("K2"), "hh:mm:ss")
u = h1.Range("C" & Rows.Count).End(xlUp).Row
h1.Range("D4:N" & u).ClearContents
For i = 4 To u
hoja = Left(h1.Cells(i, "C"), 3)
equi = h1.Cells(i, "C")
Set h2 = Sheets(hoja)
ht.Cells.Clear
If h2.AutoFilterMode Then h2.AutoFilterMode = False
u = h2.Range("C" & Rows.Count).End(xlUp).Row
h2.Range("C3:AE" & u).AutoFilter Field:=1, Criteria1:=equi
h2.Range("C3:AE" & u).AutoFilter Field:=3, _
Criteria1:=">=" & hora1, Operator:=xlAnd, Criteria2:="<=" & hora2
u = h2.Range("C" & Rows.Count).End(xlUp).Row
h2.Range("C3:AE" & u).Copy ht.Range("A1")
'
'Busca valor máximo
ut = h2.Range("C" & Rows.Count).End(xlUp).Row
wmax = WorksheetFunction.Max(ht.Range("O2:O" & ut))
Set r = ht.Columns("O")
Set b = r.Find(wmax, LookAt:=xlWhole)
If Not b Is Nothing Then
fila = b.Row
cont = 0
celda = b.Address
Do
cont = cont + 1
Set b = r.FindNext(b)
Loop While Not b Is Nothing And b.Address <> celda
End If
h1.Cells(i, "D") = ht.Cells(fila, "B")
h1.Cells(i, "E") = ht.Cells(fila, "C")
h1.Cells(i, "F") = cont
h1.Cells(i, "G") = ht.Cells(fila, "L")
h1.Cells(i, "H") = ht.Cells(fila, "M")
h1.Cells(i, "I") = ht.Cells(fila, "N")
h1.Cells(i, "J") = wmax
h1.Cells(i, "K") = ht.Cells(fila, "P")
h1.Cells(i, "L") = ht.Cells(fila, "Q")
h1.Cells(i, "M") = ht.Cells(fila, "R")
h1.Cells(i, "N") = ht.Cells(fila, "S")
If h2.AutoFilterMode Then h2.AutoFilterMode = False
Next
Application.ScreenUpdating = True
MsgBox "Fin"
End Sub.
'S aludos. Dante Amor. Recuerda valorar la respuesta. G racias
.
Avísame cualquier duda
.
1 respuesta más de otro experto
No entiendo muy bien cuál es el objetivo que buscas. ¿A qué otros datos te refieres? Puedes volver a colgar el fichero, pero con los otros datos que quieres traer, ¿y dónde quedarían esos datos?
Te anexo una imagen que hace referencia a esto
Te anexo el link con el archivo modificado, le he agregado unas notas y datos que debería de traer: Link a archivo de muestra con anotaciones
De antemanos muchas gracias.
olvidé la imagen, es esta...
De momento solo te puedo dar la solución para el campo #veces:
=contar.si.conjunto(indirecto(izquierda(C8;3)&"!C:C");C8;indirecto(izquierda(C8;3)&"!E:E");">="&$H$2;indirecto(izquierda(C8;3)&"!E:E");"<="&$K$2;indirecto(izquierda(C8;3)&"!Q:Q");J8)
Sigo mirando el resto.