Tengo problemas para encontrar una solución que me permita copiar la misma fórmula pero aumentar las filas de la matriz de la tabla en incrementos de 15. En mi primer intento simplemente intenté crear manualmente tres fórmulas apropiadas seguidas de la mano completa con la esperanza de que así fuera. Capté el patrón, pero no tuve suerte.
Estoy buscando una salida como esta:
=MAX(VLOOKUP($A2,Sheet2!$A2:$B17,2,0),VLOOKUP($A2,Sheet2!$E2:$F17,2,0),VLOOKUP($A2,Sheet2!$I2:$J17,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A17:$B32,2,0),VLOOKUP($A2,Sheet2!$E17:$F32,2,0),VLOOKUP($A2,Sheet2!$I17:$J32,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A32:$B47,2,0),VLOOKUP($A2,Sheet2!$E32:$F47,2,0),VLOOKUP($A2,Sheet2!$I32:$J47,2,0))
Soy bastante aficionado, así que cualquier consejo será bienvenido. Gracias de antemano.
Respuesta1
OFFSETsería la función por la que la mayoría de la gente gravitaría para esta opción. sin embargo, es una función volátil. Las funciones volátiles no son el fin del mundo. Simplemente significa que vuelven a calcular cada vez que cambia una celda de la hoja, incluso si esa celda no tiene relación con la fórmula volátil. La fórmula regular solo se recalcula si algo que les afecta cambia. Como resultado, puedes inducir muchos cálculos excesivos mediante el uso de funciones volátiles. Especialmente si se trata de algo copiado y reutilizado en muchas celdas.
OFFSETSe pueden lograr los mismos resultados utilizando INDEXuna fórmula regular (no volátil). INDEXA menudo se supone que devuelve el valor en una fila (rango 1D) y columna (rango 2D) determinada. Lo INDEXque realmente regresa en la dirección de la celda que luego extrae el valor de esa dirección. Usando este retorno de dirección, puede definir el punto inicial de un rango usando una INDEXfunción y luego usando otra INDEXfunción para definir el final del rango. Separe las dos INDEXfunciones con : ¡y ahora tendrá un rango!
Ahora veamos las matemáticas para determinar el patrón de filas. La Fórmula 1 comienza en la fila 2, la Fórmula 2 comienza en la fila 17, la Fórmula 3 comienza en la fila 32... y así sucesivamente. entonces el patrón aquí es esencialmente (Fórmula #-1)*15+2. Así que ahora sólo necesitamos desarrollar un contador que aumente en uno a medida que se copia la fórmula. Podrías llenar una columna con 1, 2, 3, etc. o simplemente podrías usar ROW(A1). La primera vez que se usa en cualquier lugar, devolverá 1. A medida que se copia, devolverá 2, luego 3, etc., pequeño contador perfecto. (Nota: Column(A1)podría usarse para contar horizontalmente).
Como se indicó anteriormente, INDEXtoma la forma de:
INDEX(SELECT RANGE, ROW in SELECT RANGE, COLUMN in SELECT RANGE)
Un par de notas:
El número de fila y columna es relativo al rango seleccionado y no es el mismo que el de la hoja de trabajo a menos que el rango seleccionado comience en A1 para un rango 2D, o en la fila 1 o columna A para un rango 1D.
El número de columna no es necesario en un rango seleccionado 1D, solo el número de fila. Si el rango seleccionado es horizontal, el número de fila es en realidad el número de columna.
Si se ingresa 0 para el número de fila o columna, entonces INDEX lo considera como si devolviera la fila o columna ingresada del rango seleccionado.
Volvamos a la construcción de la fórmula. Comencemos encontrando el punto inicial del rango. Entonces, en este caso queremos decirle al índice que busque A2, A17, A32, etc.
=INDEX($A:$A,(ROW(A1)-1)*15+2)
Y para encontrar el punto final del rango B17, B32, B47 la fórmula sería algo así:
=INDEX($B:$B,ROW(A1)*15+2)
Ahora, combinando los dos para definir su rango, la fórmula se vería así:
=INDEX($A:$A,(ROW(A1)-1)*15+2):INDEX($B:$B,ROW(A1)*15+2)
Ahora bien, esa fórmula por sí sola no parece gran cosa ya que no puedes poner más que el valor de una celda en una sola celda. Sin embargo, funcionará para sus fórmulas de búsqueda. Entonces, sustituyendo la ecuación de rango en tu fórmula original tendrás:
=MAX(VLOOKUP($A2,INDEX(SHEET2!A:A,(ROW(A1)-1)*15+2):INDEX(SHEET2!B:B,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!E:E,(ROW(A1)-1)*15+2):INDEX(SHEET2!F:F,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!I:I,(ROW(A1)-1)*15+2):INDEX(SHEET2!J:J,ROW(A1)*15+2),2,0))