Excel: usando ADDRESS para criar uma fórmula de matriz

Question

Estou tentando substituir as referências codificadas E13: E15 por referências relativas:

=+SUM(LN(INDIRECT("E13:E15")))

Eu tentei variações abaixo, na célula e16, mas nada parece funcionar:

=+SUM(LN(INDIRECT(ADDRESS(ROW()-3,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))))

Estou recebendo #VALUE! erros. Eu suponho que é porque a referência da Matriz não está certa, mas eu não tenho certeza do que deveria ser.

Sim, tenho usado o ctrl-shift-enter.

    

Answer 1

As funções ROW e COLUMN retornam "matrizes" mesmo quando são valores únicos, por exemplo, {3} ao invés de apenas 3, e algumas outras funções não podem lidar com isso - você pode testar isso envolvendo cada função ROW e COLUMN em uma função SUM, então a fórmula deve funcionar (embora sua sugestão INDEX seja provavelmente mais simples)

Você pode simplificar usando INDIRECT com notação R1C1 para referências relativas, por exemplo,

=SUMPRODUCT(LN(INDIRECT("R[-3]C:R[-1]C",0)))

O uso do SUMPRODUCT, em vez de SUM, apenas evita a "entrada da matriz".

    

Answer 2

Eu encontrei uma solução; Enrole o conteúdo da função LN dentro do ÍNDICE.

=SUM(LN(INDEX(INDIRECT(ADDRESS(ROW()-3,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())),0,0)))

Funciona muito bem com o controle-shift-enter.

Alguém pode explicar porque o INDEX é necessário? É necessário porque é uma referência de matriz?

    

Answer 3

A referência da matriz está correta, dadas as suas coordenadas.

Você está tentando encontrar a soma de um logaritmo natural de um intervalo de células. Você não deveria estar tentando obter um logaritmo natural da soma do intervalo de células?

Mude SUM e LN na sua fórmula.