Help! Função para criar lista condicional dependente de outra lista


#1


Pessoal, a explicação do que preciso está na imagem. Já vi vários tutoriais ensinando mas todos utilizam listas distintas para cada tipo, e eu procuro por uma forma de fazer isto numa única lista, pois a planilha real é gigantesca.
Obrigado!


#2

@Will, não é uma tarefa muito simples sem poder nomear os intervalos, especialmente porque a validação de dados não permite fórmulas matriciais. Sendo assim, é preciso fazer uso de mais auxiliares para criar a funcionalidade.

Primeiro trouxe a descrição de cada tipo para minha tabela auxiliar para ter a lista fonte para a validação de dados:
image

Como pôde ver, criei uma fórmula complicadinha para isso, uma fórmula matricial.
Para buscar a correspondência do tipo na tabela fiz uso da função ÍNDICE que, com base em uma determinada posição, retorna o valor da célula em um intervalo. Algo assim:

=ÍNDICE($A$3:$B$14;CORRESP($K2;$A$3:$A$14;0);2)

O problema, no entanto, é que assim teríamos apenas a primeira correspondência encontrada, mas queremos todas as correspondências. Neste caso, podemos, então, utilizar a função LIN, que retorna o número da linha de uma determinada célula. Mas em matriz, retornará o número das linhas de diversas células. Mas para filtrar apenas as células que correspondem ao tipo, faremos uso da função condicional SE.

{=ÍNDICE($A$3:$B$14;SE($A$3:$A$14=$K2;LIN($A$3:$A$14)-2);2)}

Note as chaves no início e no fim da fórmula. Isto indica que trata-se de uma fórmula matricial. Entretanto, você não deve digitá-las diretamente na fórmula, o Excel as colocará automaticamente apos você solicitar que ele a interprete como matriz. Para isso, ao terminar de digitar a sua fórmula, antes de pressionar a tecla Enter, pressione as teclas Ctrl + Shift + Enter para finalizar a digitação pedindo ao Excel que interprete sua fórmula como matriz.

Isso já é quase o que buscamos, mas essa fórmula retorna uma matriz, e precisamos que ela retorne apenas um resultado, a primeira correspondência encontrada mas que, ao arrastar, me traga a segunda, depois a terceira, e assim por diante se for o caso. Para isso, podemos lançar mão da função MENOR que, juntamento com a função COL, já que estaremos listando em uma linha, vamos pedir primeiro a primeira menor, depois a segunda menor e depois a terceira.

{=ÍNDICE($A$3:$B$14;MENOR(SE($A$3:$A$14=$K2;LIN($A$3:$A$14)-2);COL(A$1));2)}

Agora sim conseguimos o resultado pretendido, conforma a imagem acima, mas só resolvemos a primeira parte do problema. Ainda precisamos, dinamicamente, selecionar esta lista que geramos na validação de dados.

Para gerar a lista de forma dinâmica podemos concatenar as colunas, que são fixas, com uma posição (linha) variável. E para encontrar esta linha (posição), podemos utilizar a função CORRESP.

="L"&CORRESP($F$2;$K$1:$K$5;0)&":N"&CORRESP($F$2;$K$1:$K$5;0)

Como resultado, se o tipo selecionado for Bebida, por exemplo, a fórmula retornará L2:N2, que corresponde exatamente ao intervalo que corresponde à lista de descrições do tipo Bebida na nossa tabela auxiliar.
Só que isso é um texto, o que desejamos é um intervalo que, entregue à validação de dados de lista, gere a lista selecionável. Para isso, podemos, finalmente, utilizar a função INDIRETO, que recebe um endereço de célula em formato de texto e retorna o valor da célula (ou no nosso caso, do intervalo).

=INDIRETO("L"&CORRESP($F$2;$K$1:$K$5;0)&":N"&CORRESP($F$2;$K$1:$K$5;0))

Mas este ainda não é o fim. Agora temos duas listas suspenas, sendo a segunda, dinâmica de acordo com a primeira, mas ainda precisamos encontrar o valor que corresponde ao tipo e a descrição selecionadas e, para isso, podemos utilizar uma das novas funções do Excel 2019, MÁXIMOSES ou MÍNIMOSES. No nosso caso qualquer uma das duas vai servir já que a correspondência é única. Assim como as antigas funções SOMASES e CONT.SES, estas novas funções nos permite encontrar o valor máximo/mínimo que atendam a quantos critérios desejarmos. Sendo assim, bastou escolher uma e voilà, encontramos nosso resultado.

=MÁXIMOSES($C$3:$C$14;$A$3:$A$14;$F$2;$B$3:$B$14;$G$2)

image

Assim creio que chegamos ao resultado pretendido.