Estou tendo problemas para encontrar uma solução que me permita copiar a mesma fórmula, mas aumentar as linhas da matriz da tabela em incrementos de 15. Na minha primeira tentativa, simplesmente tentei criar manualmente três fórmulas apropriadas seguidas pela mão inteira, na esperança de que isso acontecesse. peguei o padrão, mas não tive sorte.
Estou procurando uma saída 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))
Sou bastante amador, então qualquer conselho é bem-vindo. Agradeço antecipadamente.
Responder1
OFFSETseria a função que a maioria das pessoas escolheria para esta opção. no entanto, é uma função volátil. Funções voláteis não são o fim do mundo. Significa apenas que eles recalculam sempre que uma célula da planilha muda, mesmo que essa célula não tenha relação com a fórmula volátil. As fórmulas regulares só são recalculadas se algo que as afeta mudar. Como resultado, você pode induzir muitos cálculos excessivos usando funções voláteis. Principalmente se for algo copiado e reutilizado em muitas células.
Os mesmos resultados OFFSETpodem ser alcançados usando INDEXuma fórmula regular (não volátil). INDEXmuitas vezes é assumido que retorna o valor em uma determinada linha (intervalo 1D) e coluna (intervalo 2D). O que INDEXrealmente está retornando no endereço da célula, que então extrai o valor desse endereço. Usando esse retorno de endereço, você pode definir o ponto inicial de um intervalo usando uma INDEXfunção e depois usando outra INDEXfunção para definir o final do intervalo. Separe as duas INDEXfunções com : e agora você tem um intervalo!
Agora vamos dar uma olhada na matemática para determinar o padrão de linha. A Fórmula 1 começa na linha 2, a Fórmula 2 começa na linha 17, a Fórmula 3 começa na linha 32.... e assim por diante. então o padrão aqui é essencialmente (Fórmula #-1)*15+2. Então agora só precisamos desenvolver um contador que aumente em um à medida que a fórmula é copiada. Você pode preencher uma coluna com 1, 2, 3, etc. ou simplesmente usar ROW(A1). Na primeira vez que for usado em qualquer lugar, ele retornará 1. À medida que for copiado, retornará 2, depois 3, etc. (Nota: Column(A1)pode ser usado para contagem horizontal).
Como afirmado anteriormente, INDEXassume a forma de:
INDEX(SELECT RANGE, ROW in SELECT RANGE, COLUMN in SELECT RANGE)
Algumas notas:
Os números da linha e da coluna são relativos ao intervalo selecionado e não são iguais aos da planilha, a menos que o intervalo selecionado comece em A1 para o intervalo 2D ou na linha 1 ou coluna A para um intervalo 1D.
O número da coluna não é obrigatório em um intervalo selecionado 1D, apenas o número da linha. Se o intervalo selecionado for horizontal, o número da linha será na verdade o número da coluna.
Se 0 for inserido para o número da linha ou coluna, INDEX o considerará como retornando a linha ou coluna inserida do intervalo selecionado.
Vamos voltar à construção da fórmula. Vamos começar encontrando o ponto inicial do intervalo. Então, neste caso, queremos dizer ao índice para encontrar A2, A17, A32, etc.
=INDEX($A:$A,(ROW(A1)-1)*15+2)
E para encontrar o ponto final do intervalo B17, B32, B47 a fórmula seria algo como:
=INDEX($B:$B,ROW(A1)*15+2)
Agora, combinando os dois para definir seu intervalo, a fórmula ficaria assim:
=INDEX($A:$A,(ROW(A1)-1)*15+2):INDEX($B:$B,ROW(A1)*15+2)
Agora, essa fórmula por si só não parecerá muito, pois você não pode colocar mais do que o valor de uma célula em uma única célula. No entanto, funcionará para suas fórmulas de pesquisa. Portanto, substituindo a equação de intervalo em sua fórmula original, você terá:
=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))