SE SE ENTÃO INDEXAR ESTE ou INDEX MATCH&MATCH?

Question

Aqui está uma abordagem para fazer listas dinâmicas de coisas no Excel.

Suponha que você tenha uma matriz de números de linha onde estão seus critérios TRUE(ou seja, Código do Comprador = JDH e Restante > 0). Então você poderia alimentar essa matriz para uma SMALL()função que listaria os números das linhas à medida que fosse preenchida. E, finalmente, você poderia usar o SMALL()como row_numsem uma INDEX()função, e isso listaria os valores apropriados de qualquer coluna especificada com o column_numparâmetro.

Então, vamos começar obtendo uma matriz dos números das linhas. Para simplificar, presumirei que sua tabela de dados tenha apenas 25 linhas e que o Dashboard esteja na mesma planilha que os dados. Além disso, estou usando o Código do Comprador ELN, que está na célula B30.

Primeiro, calcularemos uma matriz de TRUE/FALSEvalores correspondentes aos seus critérios:

($C$1:$C$25=$B$30)*($G$1:$G$25>0)

A primeira expressão fornece uma matriz de TRUE/FALSEvalores onde Código do Comprador (coluna C) = ELN, e a segunda fornece uma matriz de TRUE/FALSEvalores onde Restante (coluna G) é> 0. Em operações aritméticas envolvendo valores lógicos, TRUEe FALSEsão convertidos em 1 e 0 , então essa multiplicação fornece uma matriz de 1 e 0 com 1 nas posições onde seus critérios são ambos TRUE.

Agora precisamos converter esse array em uma lista dos números das linhas onde estão seus critérios TRUE. Se dividirmos uma matriz de números sequenciais por esta matriz de 1 e 0, obteremos uma matriz de números de linha onde quer que haja um 1, intercalados com #DIV/0!erros onde quer que haja um 0.

ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0))

A seguir, em vez de SMALL(), usaremos AGGREGATE(), que faz a mesma coisa que SMALL()faria (função=15), exceto que tem uma opção para ignorar erros (opção=6). Também AGGREGATE()pode lidar com arrays sem precisar inseri-los com CTRLShiftEnter.

Portanto, na fórmula abaixo, AGGREGATE()ignora os #DIV/0!erros e fornece uma matriz contendo apenas os números das linhas que correspondem aos seus critérios:

AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33)

Eu tenho essa fórmula na linha 34, então "-33" faz a função começar em 1 e listar os números das linhas à medida que são preenchidas. Altere este parâmetro para se adequar à sua situação.

Agora podemos usar isso em um INDEX() e agrupar tudo em um IFERROR()para inserir espaços em branco quando INDEX()acabar row_numsà medida que for preenchido. Esta fórmula, preenchida a partir de B34:

=IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH(B$32,$A$1:$G$1,0)),"")

fornece os resultados mostrados abaixo.

Para o ELN do comprador, apenas as linhas 5 e 11 têm 0 restante e a fórmula lista todas as linhas, exceto essas.

Observe que me referi aos cabeçalhos do painel que fornecem MATCH()o column_num'sarquivo B$32. lookup_valueQuando a fórmula é preenchida, isso funciona para o número do pedido e a descrição, mas os outros títulos no painel não são iguais aos títulos da sua tabela de dados.

Assim, para as demais colunas, substituí a referência da célula B$32pelo texto entre aspas: "LINE_NBR" para a coluna Linha PO, "QUANTIDADE" para a coluna Qtde encomendada e "REMAINING" para a coluna Qtde recebida. Esta última coluna envolve um cálculo, então a fórmula é na verdade:

=E34-IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH("REMAINING",$A$1:$G$1,0)),"")

A última coluna, "Necessário", é calculada como:

=E34-F34e preenchido.

Espero que isso ajude e boa sorte.

Answer 1