A função de deslocamento do Excel retorna #VALUE! Erro quando combinado com a função de linha

Question

Por que a seguinte função do Excel retorna um erro? O erro ocorre apenas quando a referência que é retornada é maior que 1 célula. Na fórmula, a referência retornada tem 1 linha de altura e 2 linhas de altura.

    =OFFSET($A$2,ROW(G6),0,1,2) 

    

Answer 1

Existem duas maneiras diferentes pelas quais a sua fórmula pode retornar o erro #VALUE! aqui, dependendo de como você está tentando empregá-la:

Primeiramente, se você tem uma fórmula que retorna uma matriz de valores, então faz uma diferença onde você coloca a fórmula (se não "matriz inserida").

Coloque essa fórmula em A10 (não matriz inserida) e você verá o valor de A8, coloque a fórmula em B10 e verá o valor de B8, coloque a fórmula em algum lugar diferente das colunas A e B e você terá #VALUE! Esse é o comportamento padrão no Excel para esse tipo de função.

Em segundo lugar, se você tentar "array enter" a fórmula em um intervalo horizontal de 2 células para ver ambos os valores (como sugere chuff), você também obtém o erro #VALUE! que é causado pela função ROW .

Isso acontece porque ROW () retorna um "array" (mesmo quando é uma única linha como aqui) que algumas funções não processam corretamente - você pode ver isso se você realçar ROW (G6) e pressione a tecla F9 - você verá {6} em vez de 6. A solução é usar apenas 6 na fórmula ou, presumindo que você deseja a flexibilidade fornecida por ROW , você pode incluir outra função, como SUM , por exemplo

=OFFSET($A$2,SUM(ROW(G6)),0,1,2)

várias outras funções farão o mesmo trabalho que SUM aqui, por exemplo MAX , MIN ..... ou pode substituir ROW por ROWS , por exemplo

=OFFSET($A$2,ROWS(G$1:G6),0,1,2)