Usando índice para extrair um valor de resultado de uma fórmula de matriz excel

Nem todas as fórmulas de matriz retornam matrizes com várias colunas e / ou várias linhas para a planilha. Mas quando o fazem, pode acontecer que você está interessado em ver apenas um valor na matriz. Você pode usar a função ÍNDICE de Excel para ajudar com isso.

Por exemplo, PROJ.LIN é uma das funções de planilha que irá trabalhar corretamente somente se você matriz introduzir a fórmula que contém a função. Mas suponha que você quer acesso a apenas um valor de célula nos resultados PROJ.LIN, talvez para acomodar um layout de folha de cálculo em um relatório de rotina. Nesse caso, você não necessariamente quer o conjunto completo de resultados PROJ.LIN, e você pode usar a função índice do Excel para arrancar e exibir apenas o que você está interessado em mostrar.

Por exemplo, aqui está como você pode array de entrar PROJ.LIN para uma regressão múltipla:

= PROJ.LIN (A2: A51, B2: D51,, TRUE)

Se matriz a entrar nesse fórmula em uma fileira 5 por quatro intervalo de coluna, a intersecção da terceira linha desse intervalo e a primeira coluna contém o valor de R-quadrado de regressão. Então, se você selecionar apenas uma única célula e insira a seguinte fórmula, você vai ter apenas o valor de R-quadrado:

= Índice (PROJ.LIN (A2: A51, B2: D51,, TRUE), 3,1)

Aqui, você está fornecendo INDEX com a matriz de valores devolvidos pela função PROJ.LIN. Esse é o primeiro argumento para INDEX. Os segundo e terceiro argumentos para indexar são os números 3 e 1, que instruem INDEX para encontrar o valor na terceira linha e primeira coluna da matriz e retorná-lo para a folha de cálculo.

Você pode inserir a fórmula INDEX completo como apenas dada normalmente, com uma série de resultados PROJ.LIN como seu primeiro argumento, sem o Ctrl e Shift e Enter combinação - ou seja, sem array de entrar nela. (Experimente ambos os sentidos, tanto gama inserindo-o e inserindo-o normalmente.)

E, no entanto, se você tentar entrar na seguinte fórmula de matriz de uma única célula, que produz o erro # valor! se você tentar inseri-lo normalmente:

Video: Excel - Matriz dinâmica com função ÍNDICE - com Fernando Fernandes

= IF (H44639: H44644>-0, G44639: G44644,0)

Quando a fórmula chama uma função que Excel espera tomar uma matriz como um argumento, a fórmula pode ser inserido normalmente. Esse é o caso com esta fórmula:

= Índice (PROJ.LIN (A2: A51, B2: D51,, TRUE), 3,1)

Os resultados PROJ.LIN são aninhados dentro da função INDEX, onde atuam como seu primeiro argumento. Excel espera INDEX para tomar uma matriz de valores como seu primeiro argumento - a análise de um conjunto é o que INDEX nasci para fazer. Assim, a fórmula como dado não precisa ser digitado-array.

Video: [Excel] Função para retornar último valor da coluna

Em contraste, esta única fórmula de matriz celular deve ser inscrita-matriz:

= MÉDIA (SE (A2: A25 ="zig", B2: B25,""))

Neste caso, o Excel não espera que a função IF terá uma matriz de valores como um argumento, mas aqui estamos apresentando não um, mas dois conjuntos de valores para IF: A23 intervalo: A25 e B2: B25. (Você pode até mesmo assumir a posição de que há uma série de 24 casos de “” implícita pelos dois primeiros argumentos.) Porque a fórmula não atender a expectativa inicial do Excel dos argumentos para IF, você tem que chamar a atenção do Excel com a situação, e você fazê-lo por matriz de entrar na fórmula.