Atualmente, tenho uma planilha em movimento que lista os contratos. Preciso saber se um contrato é uma renovação, novo para o produto ou novo para a empresa. Atualmente estou lutando com a lógica por trás disso e agradeceria qualquer ajuda!
Como o software de relatórios não pergunta sobre os vários tipos de contrato, fico com menos informações do que gostaria.
Assim como as datas De e Até, cada cliente possui um identificador exclusivo, cada produto possui um nome específico e cada contrato possui um número exclusivo, bem como um "status" atual. para os contratos com diversas versões diferentes, também adicionei uma coluna "versão mais recente"
Amostra:
CompanyNumber Product Contract Number Version Status Latest version? Valid From Valid To
12345 A 9876A-BA 1 Active Yes 1/1/14 31/12/15
O que preciso fazer é gerar colunas adicionais "Sim/Não" para New Customer, New to product, And Renewal.
Isso deve permitir que eu crie relatórios mensais com muito mais facilidade para essas três colunas, simplesmente filtrando-as em Tabelas Dinâmicas.
Já gerei diversas colunas extras por outros motivos como segue
Start date in this month? - End Date In This Month - Expires in next three months? - More than one version? - Existing Client > 365 days - Latest version?
A lógica que tenho no momento para renovações é primeiro verificar se outra entrada com o mesmo número de empresa tem data de término no último ano e depois verificar se a versão mais recente do contrato começa neste mês e é o versão mais recente, assim
If
unique company number = no, find if any duplicate result ended in the last year
AND
Contract Starts this month AND this entry is the latest version
= True, "YES"
False = "NO"
Estou sem saber como fazer isso! Qualquer ajuda é muito apreciada.
Muito obrigado
EDITAR.
Depois de alguns dias tentando, cheguei até aqui. Acho que funciona, mas não estou 100%
Comecei a tentar isso como uma função if, com funções aninhadas e, countif (número exclusivo da empresa) >1, countif(product)>1, atual e de versão mais recente como esta: =IF(AND(COUNTIF(B:B,B10 )>1,CONT.SE(C:C,C10)>1,(S10="Sim"),AA10=1),1,0)
As respostas chegam como 1 ou 0, então posso usar os resultados em uma tabela dinâmica com facilidade.
Se alguém pensar em uma resposta melhor, me avise :) -
SEGUNDA EDIÇÃO
Aquela não estava funcionando, então fiz mais pesquisas e encontrei essa fórmula, que não está funcionando muito bem.
Decidi fazer uma coluna "auxiliar" para que possamos contar a instância de cada produto por número de empresa, mas contando TODAS as instâncias do número da empresa, não apenas aquelas que fazem referência cruzada ao produto.
=SUM(IF(C:C=C28,IF(B:B=B28,1,0),0))
Onde C:Cestá a coluna na qual estou armazenando os números dos produtos e Bé a referência exclusiva da empresa.
Responder1
Muitas vezes usarei concatenar para combinar elementos de dados e, em seguida, usarei uma tabela dinâmica para contar o número de vezes que um elemento de dados combinado ocorre, ou você pode usar contar se. Por exemplo, se você está tentando descobrir se uma Empresa (Número da Empresa) é nova em um Produto, você pode criar uma coluna auxiliar com a fórmula =Concatenate(Company Number, Product)que, no seu exemplo, forneceria o valor 12345A. Você poderia então usar countifuma tabela dinâmica para ver 1) se a empresa é nova nessa linha de produtos 2) a data de início (válido a partir de) etc.