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

Question

Estou tentando ajudar uma amiga com um% de%% de% herdado no trabalho, mas isso 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 foram construídos pelo AND juntos com outros arrays carregados com BOOLEAN (no entanto, isso deve funcionar no Excel ...) , então o INDEX irá selecionar "o número de linha" de whatever-mess-that-was que resultou na presença de "1", e 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 da MATCH.

Eu tentei brincar com uma planilha em branco e não consegui nem deduzir o que INDEX(MATCH()) faz - ou, ainda, o que =(SingleCell=RangeOfCells) faz - o valor de retorno parece ser depende de qual célula a fórmula é digitada (sua linha de deslocamento a partir dos dados que está inspecionando) e a adição de =RangeOfCells antes dos números de linha nessas fórmulas não faz diferença.

Quais são os fundamentos 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, finalmente, o que o $ está tentando procurar?

Que diabos é em 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ê:

Se houver uma linha única da Planilha2 em que TODAS as três condições sejam VERDADEIRAS:

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

Em seguida, retorne o valor da célula na Planilha2, Coluna B, qualquer que seja a linha-de-Planilha2-que-composto-condição-foi-verdadeira-para.

    

Answer 1

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, digitei 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.

VocêestácorretoaomultiplicarvaloreslógicoséoequivalentedaoperaçãoAND(),excetoqueelefornece1e0comoresultadosemvezdeTrueeFalse.Vocêpodeverificarissodigitando=True*Trueou=True*Falseemumacélula,porexemplo.

Vocêperguntouoquepoderiaestarnamatrizbastanteestranha(sim,éumamatriz).Então,vamosveroqueoExcelachaqueéoseuvalor.

Cliquedentrodeumadasfórmulase,emseguida,cliquenoelementodafórmulacujovalorvocêdesejaver-nessecaso,é"lookup_array". Isso destaca o lookup_array como mostrado na próxima figura.

AgoraaperteF9.IssofazcomqueoExcelmostreovalordoelementorealçado.

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. Portanto, a função MATCH () encontra a posição do 1 nessas matrizes, 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á correto: a MATCH () encontra a primeira linha (Tipo de correspondência = 0) em H, I e J que possui valores correspondentes às células em D, E e amp; ; 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.

    

Answer 2

Isso é 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 eletrônica (e admitidamente sem passar pelos detalhes exaustivos do seu post) que há um intervalo que tem essa função e você 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 daremos uma olhada.