☰

Usando correspondência de índice condicionalmente para indicar se

tag-icon • tag-icon microsoft-excel worksheet-function
Usando correspondência de índice condicionalmente para indicar se

Sinto-me bastante confortável com o INDEX MATCH, mas me deparei com um problema que gostaria de usar onde não tenho certeza se funciona. Tenho duas planilhas: a primeira planilha menor possui um pequeno subconjunto de nomes que estão na outra planilha e possui um conjunto de datas associadas a cada nome. A outra folha maior possui um conjunto diferente de datas associadas a cada nome. O que eu gostaria de poder fazer é INDEXar as datas na planilha maior e COMBINAR os nomes apenas SE essa data for maior que a data na planilha menor e menor que o final do mês associado a essa data. Em vez de retornar o valor MATCH, gostaria de retornar, digamos, 1 se o critério for atendido. Isso é algo que pode ser feito facilmente com INDEX MATCH ou há outro conjunto de funções que eu deveria procurar? Como você faria isso?

Responder1

Acredito que sua pergunta gira em torno de escrever uma fórmula INDEX/MATCH em torno destes critérios:

  • INDEX as datas na folha maior

  • CORRESPONDER nomes apenas SE esta data for maior que a data na folha menor

    • menos que o final do mês associado a essa data

  • Em seguida, retorne 1 se os critérios forem atendidos.

Podemos fazer isso aninhando INDEX/MATCH dentro de uma condição IF:

=IF(

    INDEX([LargerSheet!*Range with Dates*],

       MATCH(

         IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
         [SmallerSheet!*Range with Dates]),

       [SmallerSheet!*Range with Dates*],0)

    )>0,1,"")

A função MATCH retornará valores para cada vez que uma correspondência for encontrada e, por definição, esse valor será maior que 0. Portanto, queremos retornar 1 para cada ocorrência disso.

Espero que isto ajude!

Responder2

Eu usaria o suplemento Power Query para isso. Uma consulta pode começar a partir de uma tabela do Excel e, em seguida, construir a transformação de seus dados em etapas. Você pode revisar o resultado em cada etapa.

Para sua necessidade, eu usaria o comando Mesclar para unir as duas planilhas por nome e expandir a coluna de data de sua planilha maior.

http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx

Isso resultará em todas as correspondências possíveis. Então, eu usaria o comando Filtro para chegar apenas às datas do intervalo. Pode ser necessário editar a função Filtro para isso. Observe que existe uma função Power Query para derivar o final do mês:

http://office.microsoft.com/en-au/excel-help/date-endofmonth-HA104110485.aspx?CTT=5&origin=HA104122363

Finalmente eu entregaria a consulta para uma nova tabela do Excel.

informação relacionada