☰

Fórmulas de matriz e lógica, verificando vários critérios

tag-icon • tag-icon microsoft-excel worksheet-function array
Fórmulas de matriz e lógica, verificando vários critérios

No momento, estou apenas solucionando problemas em uma planilha de relatório que escrevi. A lógica que escrevi não está funcionando, então pensei em ver se poderia pedir ajuda aqui.

Essencialmente, tenho uma lista de contratos, uma lista de produtos, uma lista de datas de término e início e várias outras informações. Preciso saber se um produto de uma renovação utiliza esta informação.

Dados de exemplo:

Product   Company Number    Start of Contract        End of contract     Contract ID     Include in this month's report
Fax            1234             10.09.2013              10.09.2014           1                      No
Fax            1234             10.09.2014              10.09.2015           2                      No     
Box            5678             11.01.2014              30.04.2015           3                      No
Box            5678             01.05.2015              11.01.2016           4                     Yes
Fax            5678             01.05.2015              01.05.2016           5                     Yes
Cup            9876             03.05.2015              03.05.2016           6                     Yes

O que eu quero fazer é descobrir, usando esses dados, se o arquivo é novo para o produto, novo para a empresa ou se é a renovação de um arquivo existente.

A lógica por trás da inclusão do contrato no relatório deste mês é simples - basta verificar se Start dateé o mês solicitado em outra parte do relatório.

No exemplo dado acima,

  • Os IDs de contrato 1,3 e 6 são novos para os negócios
  • ID's 2 e 4 são renovações
  • O ID 5 é novo no produto, com negócios existentes com a empresa

Elaborei a maneira básica de calcular para este mês se há um novo arquivo de produto, porque o número da empresa não será exclusivo, mas o produto será. Eu gostaria de melhorar isso, porque se for feito como está, retornará como "Falso" para a primeira ocorrência de onde o produto foi posteriormente renovado.

O que preciso fazer é encontrar uma maneira de verificar se existe um contrato anterior conosco que era com o produto listado, contratos com clientes completamente novos e contratos onde EXISTE um contrato anterior conosco que NÃO ERA com o mesmo produto . Em essência, como abaixo, mas calculado automaticamente a partir da tabela acima.

Resultados:

Contract ID     Renewal?        New to Product?         New to Business?
    1              No                  No                     Yes
    2             Yes                  No                     No
    3              No                  No                     Yes
    4             Yes                  No                     No
    5              No                 Yes                     No
    6              No                  No                     Yes

Até agora, tenho algumas colunas auxiliares, como "terminou nos últimos 12 meses" e "número de contratos". mas continuo me virando no que diz respeito à lógica dos três resultados finais!

Muito obrigado!

Responder1

Acho que é assim que você deseja que suas colunas fiquem:

insira a descrição da imagem aqui

Aqui estão as fórmulas usadas:

  • G2 é=IF(AND(B2=B1,A2=A1),"Yes","No")
  • H2 é=IF(AND(B1=B2,A2<>A1),"Yes","No")
  • I2 é=IF(B2<>B1,"Yes","No")

informação relacionada