Questão Fórmula do Excel: O que, em linguagem simples, está nessa matriz em um INDEX (MATCH ())?


Estou tentando ajudar um amigo com um Excel INDEX(MATCH()) ela herdou no trabalho, mas me deixou perplexo. As pessoas podem me ajudar a entender melhor o que está acontecendo? Aqui está o formato:

=INDEX(
    '[Spreadsheet2.xlsx]TabOfInterest'!$B$B
    ,
    MATCH(
        1
        ,
        (
            (D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
            *
            (F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
            *
            (K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)
        )
        ,
        0
    )
    ,
    1
)

Eu posso ver que se o número "1" (parecendo "VERDADEIRO", parece, aqui) aparece em algum lugar em algum tipo de array de TRUEs / 1s & FALSEs / 0s que foi construído por AND-junto com outros BOOLEAN-filled matrizes (no entanto, é suposto que isso funcione no Excel ...), então INDEX vai pegar "o número da linha" de seja qual for a bagunça que fosse que resultou na presença do "1" e o INDEX retornará o valor da célula da planilha 2 na interseção dessa linha e da coluna B.

Mas não tenho ideia do que está acontecendo nesse segundo parâmetro do MATCH.

Eu tentei brincar com uma planilha em branco e não conseguia nem deduzir o que =(SingleCell=RangeOfCells)  faz - ou, aliás, o que =RangeOfCells faz - o valor de retorno parece ser dependente de qual célula a fórmula é digitada (seu deslocamento de linha a partir dos dados que está inspecionando), e adicionando $ antes que os números das linhas nessas fórmulas não façam diferença.

O que é fundamental para se referir a intervalos e fazer comparações de "igualdade" entre células e intervalos? Preciso saber para entender melhor o que está acontecendo nesta fórmula e, em última análise, o que INDEX(MATCH()) está tentando procurar?

O que na terra é dentro este array?

(D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
*
(F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
*
(K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)

Além disso, se eu tivesse que adivinhar, suponho que diria que a função geral faz isso, mas está me incomodando não saber por quê:

E se há um fila unica da Planilha2 em que TODAS as três condições são VERDADEIRAS:

  • célula D34 de qualquer planilha que este INDEX-MATCH está dentro aparece na coluna "M" do Spreadsheet2 E
  • célula F34 de qualquer planilha que este INDEX-MATCH está dentro aparece na coluna "P" do Spreadsheet2
  • A célula K34 de qualquer planilha na qual esta INDEX-MATCH está dentro aparece na coluna "Y" da Spreadsheet2

Então retorna o valor da célula na Planilha2, Coluna B, qualquer que seja a linha-de-Planilha2-que-composto-condição-era-verdadeira-para.


2


origem


@HackSlash - soa como D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M cheques para ver qual valores na coluna M correspondem ao valor de D34, não se todos eles fazem (e, em seguida, retorna uma matriz de trues & falses indicando quais deles). Veja a resposta de Bandersnatch. - k..


Respostas:


Vamos simplificar isso um pouco e ver se conseguimos descobrir o que está acontecendo. Na imagem abaixo eu configurei D1, E1 e F1 para imitar D34, F34 e K34 em sua fórmula e preenchi-os com valores. Para sorrisos, adicionei mais duas linhas de valores abaixo das três primeiras células. Mais sobre isso depois.

Em seguida, nas colunas H, I e J, adicionei matrizes equivalentes às suas colunas M, P e Y. Também as reduzi um pouco, principalmente porque o truque de depuração abaixo não funcionará se elas forem colunas inteiras.

No G7, inseri apenas a parte MATCH () da sua fórmula acima. Conforme mostrado pelas chaves entre elas, é uma fórmula de matriz. É digitado digitando CTRL-Shift Enter e o Excel adiciona as chaves. Uma vez feito isso, você pode preencher mais duas linhas. É claro que a função MATCH () está chegando com algumas respostas.

enter image description here

Você está correto que multiplicar valores lógicos é o equivalente da operação AND (), exceto que ele fornece 1 e 0 como resultados em vez de True e False. Você pode verificar isso digitando =True*True ou =True*False em uma célula, por exemplo.

Você perguntou o que poderia estar na matriz bastante estranha (sim, é uma matriz). Então, vamos ver o que o Excel acha que é o seu valor.

Clique dentro de uma das fórmulas e, em seguida, clique no elemento da fórmula cujo valor você deseja ver - nesse caso, é "lookup_array". Isso destaca o lookup_array como mostrado na próxima figura.

enter image description here

Agora aperte F9. Isso faz com que o Excel mostre valor do elemento destacado.

enter image description here

E o Excel nos diz que o valor da parte destacada da fórmula é a matriz {1; 0; 0; 0; 0}. Se você fizer isso para G8 e G9, verá que os valores são {0; 0; 0; 0; 1} e {0; 0; 0; 1; 0} respectivamente. Assim, a função MATCH () encontra a posição do 1 nesses arrays, ou seja, 1, 5 e 4.

Você pode pensar assim: A primeira parte do lookup_array verifica onde D1 é igual a H1: H5 - essa parte retorna {1; 1; 0; 0; 0}. Então, checando E1 em I1: I5 dá {1; 0; 1; 0; 0}. Verificando F1 em J1: J5 fornece {1; 0; 1; 0; 0}. AND todos os três destes dão {1; 0; 0; 0; 0}, que é o resultado que o Excel mostra para o array inteiro (reconhecidamente estranho). Similarmente, a fórmula em G8 encontra {0; 0; 0; 0; 1}, {0; 1; 0; 0; 1} e {0; 0; 0; 0; 1) então ANDs para obter {0; 0; 0; 0; 1}.

Seu palpite sobre o que está acontecendo está certo: a MATCH () encontra o primeiro row (Tipo de correspondência = 0) em H, I e J que possui valores correspondentes às células em D, E e F e, em seguida, INDEX () retorna essa posição na coluna B.

Você pode mexer com os valores acima e verificar isso por si mesmo. Lembre-se de usar CTRL-Shift Enter para inserir a fórmula de matriz - você estará fazendo isso muito, já que o Excel tem um bug / recurso onde clicar na fórmula e, em seguida, apertar return ou tab, dá o valor de #VALUE! erro. CTRL-Z irá desfazer isso.

Espero que isso ajude e boa sorte.


4



E1=$I$1:$I$5: "verificação de E1 em I1: I5 dá {1; 0; 1; 0; 0}"- OBRIGADO! Foi tão difícil dizer, por causa dessa peculiaridade" #VALUE "quando você acabou de inserir a fórmula normalmente (pressionando "enter" ou "tab"). Eu também vejo, desde, na documentação, que quando o Excel "faz uma operação para" dois arrays em vez de dois escalares, ele apenas aplica a mesma operação a cada par de escalões de mesma posição no array (Eu estava voltando para procurar álgebra sobre como a multiplicação vetorial funciona antes que eu terminasse e procurei por tal documentação ...)  Ótimo colapso - graças a um milhão! - k..


Isso está escrito como uma função de matriz? Ou seja, chaves em ambos os lados e entrou usando ctrl / enter? Em caso afirmativo, a seção intermediária está comparando o valor na matriz correspondente à linha em que está. Dada a multiplicação, eu diria que você está certo de que está procurando por algo em que todos os 3 valores sejam verdadeiros.

Eu suspeito, sem ver sua planilha (e admitidamente sem passar pelos detalhes exaustivos de seu post) que há um intervalo que tem essa função, e você por acaso nos mostra um deles. Eu suspeito que a linha antes e a linha após (e muitas outras também) tenham a mesma fórmula, com os números das linhas se ajustando de acordo. Em caso afirmativo, duplico a expectativa de que essa seja uma função de matriz, gravada uma vez e aplicada a uma série de células, aplicada com ctrl / enter para fazê-la interagir em todas as células no tempo de execução.

E se esse for o caso, editar a fórmula e usar enter para salvá-la (mesmo que você não tenha feito edições ou alterações reais) alterará o comportamento e, portanto, os resultados.

Se nada disso fizer sentido, sinta-se à vontade para me enviar a planilha e eu darei uma olhada.


2



Bob Smiley, meu amigo não disse, mas certamente soa como a razão pela qual eu estava tendo tanto trabalho para conseguir qualquer coisa razoável para renderizar quando eu construí uma planilha de amostra para inspecionar as nuances de fórmulas como =E1=$I$1:$I$5 tem a ver com não saber pressionar Ctrl-Shift-Enter. Eu não tenho acesso à planilha do meu amigo, na verdade. Mas acho que entendo tudo, entre a resposta de Bandersnatch mostrando o conteúdo da matriz retornada por essa função e alguma documentação adicional sobre como funcionam as operações entre arrays que encontrei desde que fiz a pergunta. - k..