A fórmula do Excel não funciona com referências de colunas

Question

Oi,

Eu tenho 7 resultados de partidas de futebol:

             B   D           

             Team A       
2001        0   - 12      
2002        1   - 5        
2003        1   - 11        
2004        4   - 1         
2005        1   - 6  
2006        0   - 5
2007        1   - 2

e esta fórmula que me dá a melhor pontuação de futebol deles

=INDEX(B2:B8,SUMPRODUCT((B2:B8-D2:D8*1.01=MAX(B2:B8-D2:D8*1.01))*ROW(B2:B8))-ROW(B1))&" - "&INDEX(D2:D8,SUMPRODUCT((B2:B8-D2:D8*1.01=MAX(B2:B8-D2:D8*1.01))*ROW(B2:B8))-ROW(B1))

Isso funciona perfeitamente até agora, já que o valor resultante é 1-2. No entanto, como vou adicionar notas abaixo de B8, preciso modificar essa fórmula para sempre referenciar a última célula com dados. Então eu tentei isso:

=INDEX(B:B,SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B))-ROW(B1))&" - "&INDEX(D:D,SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B))-ROW(B1))

mas não funcionou. Como posso consertar isso?

Obrigado.

    

Answer 1

Tomando emprestado liberalmente da resposta que eu coloquei nos comentários ... Abra o gerenciador de nomes (ctrl + F3) e defina dois nomes. Definir range1 como:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(9.9E+307,Sheet1!$B:$B,1))

e range2 como

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH(9.9E+307,Sheet1!$D:$D,1))

Depois, você pode usar sua fórmula original, com os intervalos substituídos pelos intervalos nomeados que você acabou de criar:

=INDEX(range1,SUMPRODUCT((range1-range2*1.01=MAX(range1-range2*1.01))*ROW(range1))-ROW(B1))&" - "&INDEX(range2,SUMPRODUCT((range1-range2*1.01=MAX(range1-range2*1.01))*ROW(range1))-ROW(B1))

Fórmula de bônus:

=INDEX(range1,MATCH(MAX(range1-range2),range1-range2,0))&" - "&INDEX(range2,MATCH(MAX(range1-range2),range1-range2,0))

Observe que essa é uma fórmula de matriz e deve ser confirmada com ctrl + Shift + Enter.

    

Answer 2

Parece que funciona

=INDEX(B:B, SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B)))&"-"&INDEX(D:D, SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B)))

Se precisar excluir a primeira célula esta resposta diz que a melhor maneira é especificar os números de células manualmente (por exemplo, B2:B1048576 )

    

Answer 3

Você sabia que sua fórmula falharia se houvesse mais de uma linha em seus dados que compartilhasse o valor máximo?

A ideia de implementar um dos set-ups para determinar a última linha usada em seus dados, como dada no link fornecido por Kyle, é sensata.

No entanto, talvez você também queira considerar essa alternativa, uma construção mais curta, que funcionará mesmo se algumas das células que estão sendo referenciadas estiverem em branco, embora com a ressalva de que nenhum dos resultados da peça:

B2:B100-D2:D100*1.01

são negativos.

=LOOKUP(1,0/FREQUENCY(0,1/(1+((B2:B100-D2:D100*1.01)))),B2:B100&" - "&D2:D100)

Obviamente, você pode alterar a linha superior sendo referenciada aqui, mas tenha certeza de não torná-la arbitrariamente grande. Essas construções (bem como SUMPRODUCT , AGGREGATE e qualquer configuração que requeira CSE ) calculam sobre todas as células transmitidas a elas, seja tecnicamente além das últimas células usadas nessas faixas ou não.

Atenciosamente