Dados que possuo: Data da transação (A:A), Nome do cliente (B:B), Número do pedido de venda (C:C), Nome do produto (D:D), Unidades (E:E), Receita (F:F)
Novo pedido seria qualquer coisa que o cliente não tenha pedido nos últimos 6 meses ou nunca.
Um novo pedido ocorreria se o cliente tivesse comprado aquele produto específico nos últimos 6 meses.
Não consigo entender como colocar essa lógica em uma fórmula do Excel.
Responder1
Talvez eu esteja entendendo mal alguma coisa, mas isso parece bastante simples. Meu entendimento da questão é que uma linha representa um novo pedido se houver pelo menos uma linha acima da atual que tenha o mesmo Nome do Cliente (Coluna B) que a linha atual, o mesmo Nome do Produto (Coluna D) e uma Data de Transação ( Coluna A) nos últimos seis meses da Data da Transação atual. Colunas Ce Epodem Fser ignoradas. Presumo que as linhas sejam classificadas por data de transação (embora eu ache que não preciso fazer essa suposição).
O critério Data da Transação é o “mais difícil” (uso esse termo vagamente). Uma data passada está dentro dos últimos seis meses A2se for
> EDATE(A2,-6)
Portanto, para contar as linhas até a atual que satisfazem os três critérios, usamos
=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)
A A$2:A2notação é interessante. Representa um intervalo que começa na Linha 2 e termina na linha atual; ou seja, tudo até (e incluindo) a linha atual. Essa contagem sempre será pelo menos 1, porque a linha atual conta. Se for maior que 1, houve pelo menos uma linha anterior que também correspondeu. Então a resposta é entrar
=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")
em H2(ou onde você quiser) e arraste/preencha.
Se as linhas estiverem fora de ordem, precisamos pesquisar a tabela inteira e testar se a data é menor que a data atual:
=IF(COUNTIFS(A$2:A$99, ">" & EDATE(A2,-6), A$2:A$99, "<" & A2,
B$2:B$99, B2, D$2:D$99, D2)=0, "New Order", "Reorder")
onde estou usando 99para representar a última linha dos dados. Mudei o teste de <=1para =0
porque o < A2teste elimina a linha atual. Se seus dados puderem incluir várias linhas com o mesmo Nome de Cliente e Nome de Produto e exatamente a mesma Data de Transação, especifique como eles devem ser tratados.
Responder2
Ah, você vai adorar esse.
Vou dividir em etapas porque a fórmula final será bastante complexa.
Configurar
Criei uma planilha no formato que você especificou. Adicionei três colunas para definir o que precisamos fazer, que descreverei individualmente a seguir. Observe que congelei a linha superior e rolei um pouco para baixo nesta primeira captura de tela.
Etapa 1: estabeleça o intervalo de pesquisa
Primeiro, precisamos saber quando foi há 6 meses (e onde essa data está localizada na planilha). Em cada registro, usaremos EDATEpara descobrir.
Na célula G2:
VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)
Isso é ótimo - nos dá o último recorde de vendas que temos há mais de 6 meses. Observe que se você tiver vendas pouco frequentes (ou seja, grandes lacunas entre os registros de vendas), a maneira como escrevi isso significa que você pode 'capturar' falsos positivos, se o último pedido do item em questão tiver sido de, digamos, 6 meses e há uma semana E não houve nenhum pedido entre 6 meses e uma semana atrás e exatamente 6 semanas atrás. Decidi que isso era 'próximo o suficiente' para você.
A única outra falha é que existem alguns erros de aparência desagradável perto do topo da planilha, porque para o primeiro registro (ou seja, row 2) não há nenhum registro de 6 meses atrás. Então, vamos encerrar com IFERROR:
=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)
Em outras palavras, diga-me quando foi o pedido mais recente há mais de 6 meses, a menos que não haja nenhum; nesse caso, basta me fornecer o primeiro pedido do qual temos registro.
Esta fórmula é copiada. Está aqui:
Etapa 2: Transforme os dados iniciais do intervalo em um local de célula
Na célula H2:
=MATCH(G2,A:A,0)
Este é fácil. Em que posição ordinal na coluna Aencontramos a data de 6 meses atrás que estabelecemos na Etapa 1?
Esta fórmula é copiada. Aqui estamos:
Etapa 3: faça
Vamos avançar e colocar isso em, que tal, I21para que possamos ver como funciona.
=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)
Esta é, basicamente, apenas uma INDEX MATCHpesquisa simples. Mas o que fizemos foi substituir as referências de células dos arrays dessas funções por INDIRECTreferências ao valor que acabamos de semear em column H.
Nos meus dados de amostra, linha 21é um registro de vendas com data de 10/1/2018. A coluna Gencontra o registro de vendas mais recente há mais de 6 meses antes dessa data, que na minha amostra é 4/1/2018. A coluna Hfixa a (primeira) localização dessa data na coluna Ada 8ª linha. Assim INDIRECT("A"&H21&diz para iniciar o array para a busca na linha Aidentificada pelo valor in H. Legal! E a outra metade do INDIRECT, ":D"&ROW()-1)diz para terminar o array na linha Dque está uma linha acima do registro em que estamos atualmente.
Em outras palavras, você poderia escrever
=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)
Para encontrar a primeira incidência do código do seu produto em DTODO o livro de vendas - mas não queremos fazer isso. Portanto, substituímos as referências de células por intervalos dinâmicos que usamos nas duas etapas anteriores para gerar. Comigo?
Então fica assim:
Depois de copiarmos essa fórmula (embrulhada em outra IFERRORpara aparências) de cima para baixo 2e de volta, você terá:
Há um falso positivo na primeira linha. Eu vou viver com isso também.
Etapa 4: combinar
Portanto, a fórmula em Ireference H, quais referências G. A expansão reversa resulta em uma fórmula final que diz:
=IFERROR(INDEX(INDIRECT("A"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),MATCH(D2,INDIRECT("D"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),0),1),"NEW ORDER")
E se parece com:
Então, se quiser, você pode simplesmente copiar essa fórmula na coluna 'reordenar' na linha 2e copiá-la.
Nota.
- Você usará o Format Painter em seu campo de saída, porque as fórmulas escritas tratarão as datas do texto
Acomo números de série de datas e também retornarão um número de série. - Observe a fraqueza na Etapa 1 - você pode ver na captura de tela do
helper1campo na Etapa 3 que em meus dados de amostra, a data de 6 meses atrás para 1/10 era 1/4 e a data de 6 meses atrás para 10 /5 eratambém01/04, porque não houve nenhum pedido entre 01/04 e 05/04. Isso poderia potencialmente gerar falsos positivos. - Novamente, conforme discutido na Etapa 3, também haverá um falso positivo no primeiro registro de vendas usando esse método.
Responder3
Minha abordagem é um pouco diferente para resolver o problema, já que escolhi o tópico do OP.
Novo pedido seria qualquer coisa que o cliente não tenha pedido nos últimos 6 meses ou nunca.
Um novo pedido ocorreria se o cliente tivesse comprado aquele produto específico nos últimos 6 meses.
- Uma fórmula Array (CSE) em Cell
H41, termine comCtrl+Shift+Enter.
{=IFERROR(LOOKUP(DATEDIF(IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match"),I41,"m"),{0,6,12},{"New Order","Order before 6 month","Order before 12 months"}),"Cust's. New Pro. Order")}
Situação 1:
Nova data de tração: 03/26/19.
Nome do cliente: Bob.
Nome do produto: Cake.
Encomende Sataus: Order before 12 months.
Situação 2:
Nova data de tração: 03/26/19.
Nome do cliente: Bob.
Nome do produto: Milk.
Encomende Sataus: New Order.
Observação
Porque a diferença entre a data de transação antiga ( 10/01/18) e a data de nova transação ( 03/26/19) é inferior a 6 meses.
Situação 3:
Nova data de tração: 03/26/19.
Nome do cliente: Bob.
Nome do produto: Wheat.
Encomende Sataus: Order before 6 months.
Situação 4:
Nova data de tração: 03/26/19.
Nome do cliente: Bob.
Nome do produto: Fruit.
Encomende Sataus: Cust's. New Pro.Order.
Observação:
Se você inserir o nome do novo cliente e o produto e data antigos ou novos, você obterá Cust's . New Pro. Orderum status.
Agora deixe-me explicar como a Fórmula funciona.
A fórmula pode ser dividida em duas partes.
Part 1
{=IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match")}
Basicamente é a pesquisa de 2 critérios que encontra Old Transaction Date, Customer & the Producte a Fórmula considera que Start Datea DATEDIFfórmula está dentro de A41:A47..
Part 2
O original DATEDIFé,
{=LOOKUP(DATEDIF(A41:A47,I41,"m"),{0,6,12},{"New order","Order before 6 month","Order before 12 months"})}
Onde A41:A47foi substituído pela Part 1Fórmula como Start Datee End Dateestá na célula I41.
E ambas as partes estão bem embrulhadas com IFERROR.
Observação
- Você pode ajustar as referências de células conforme necessário.
- Mensagens com a Fórmula também podem ser alteradas conforme sua preferência.
Responder4
Você pode usar a seguinte fórmula:
=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )
Ele usa a técnica tradicional de testes simples de "intervalo =" para produzir matrizes que informam cada coisa (o nome do cliente está em alguma célula do intervalo da coluna A? O nome do produto está no intervalo da coluna D? As datas estão no intervalo da coluna A? coluna A intervalo dentro de 183 dias do pedido de hoje?) e então os multiplica para obter uma matriz final.
Os valores brutos da matriz são valores TRUE/FALSE, mas multiplicá-los força o Excel a alterá-los para valores 1/0 que se multiplicam bem. O resultado final é uma matriz com 1 onde todas as três condições acima são atendidas e 0 onde isso não acontece. O Excel não converte os elementos da matriz de volta para valores TREU/FALSE, portanto, toda a matriz é numérica.
SUMem seguida, resume todos eles em um único valor. Se o resultado for diferente de 0, então existe pelo menos um mesmo pedido nos últimos 183 dias. Se 0, então nenhum deles existe. O IFsimplesmente verifica qual é o resultado e informa "Novo" ou "Reordenar".
Na verdade, o único ponto de interesse é a mistura de absoluto versus relativo no endereçamento de intervalo. Todos os pontos iniciais do intervalo são totalmente absolutos, de modo que o canto superior esquerdo de cada intervalo fica ancorado e imóvel. Os pontos finais do intervalo apenas tornam a coluna da referência absoluta, de modo que o intervalo aumenta à medida que você adiciona linhas, mas nunca inclui a linha atual... então começa no topo e termina uma linha acima da linha atual.
Se não estiver claro desde o início, coloco as informações que aparecem na célula da coluna G da linha do pedido que acabou de ser inserido. No entanto, ele pode estar em outro lugar, inclusive nem na mesma linha, nem na mesma planilha, e pode ser usado para fazer um teste de formatação condicional para alterar as cores das células da linha para mostrar "Novo/Reordenar" dessa maneira.