Datos que poseo: Fecha de transacción (A:A), Nombre del cliente (B:B), Número de orden de venta (C:C), Nombre del producto (D:D), Unidades (E:E), Ingresos (F:F)
Un nuevo pedido sería cualquier cosa que el cliente no haya pedido en los últimos 6 meses o nunca.
Un nuevo pedido sería si el cliente hubiera comprado ese producto específico en los últimos 6 meses.
No puedo entender cómo poner esta lógica en una fórmula de Excel.
Respuesta1
Quizás estoy entendiendo mal algo, pero esto parece ser bastante simple. Según tengo entendido, una fila representa un nuevo pedido si hay al menos una fila encima de la actual que tiene el mismo Nombre de cliente (Columna B) que la fila actual, el mismo Nombre de producto (Columna D) y una Fecha de transacción ( Columna A) dentro de los últimos seis meses a partir de la Fecha de Transacción actual. Columnas Cy se pueden ignorar E. FSupongo que las filas están ordenadas por Fecha de transacción (aunque supongo que no necesito hacer esa suposición).
El criterio de la fecha de transacción es el “más difícil” (uso ese término de manera vaga). Una fecha pasada es dentro de los últimos seis meses de A2si es
> EDATE(A2,-6)
Entonces, para contar las filas hasta la actual que satisfacen los tres criterios, usamos
=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)
La A$2:A2notación es interesante. Representa un rango que comienza en la fila 2 y termina en la fila actual; es decir, todo hasta (e incluyendo) la fila actual. Este recuento siempre será al menos 1, porque cuenta la fila actual. Si es mayor que 1, hubo al menos una fila anterior que también coincidió. Entonces la respuesta es entrar.
=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")
hacia H2(o donde quieras) y arrastra/rellena hacia abajo.
Si las filas pueden estar desordenadas, debemos buscar en toda la tabla y probar que la fecha sea menor que la fecha actual:
=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")
donde estoy usando 99para representar la última fila de los datos. Cambié la prueba de <=1a =0
porque la < A2prueba elimina la fila actual. Si sus datos pueden incluir varias filas con el mismo Nombre de cliente y Nombre de producto, y exactamente la misma Fecha de transacción, especifique cómo deben manejarse.
Respuesta2
Oh, este te va a encantar.
Lo dividiré en pasos porque la fórmula final será bastante compleja.
Configuración
Creé una hoja de trabajo en el formato que especificaste. Agregué tres columnas para determinar lo que debemos hacer, que describiré individualmente a continuación. Tenga en cuenta que congelé la fila superior y me desplacé un poco hacia abajo en esta primera captura de pantalla.
Paso 1: Establecer rango para buscar
Primero necesitamos saber cuándo fue hace 6 meses (y en qué lugar de la hoja de cálculo se encuentra esa fecha). En cada registro, usaremos EDATEpara averiguarlo.
En la celda G2:
VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)
Esto es fantástico: nos da el último registro de ventas que tenemos hace más de 6 meses. Tenga en cuenta que si tiene ventas poco frecuentes (es decir, grandes brechas entre los registros de ventas), la forma en que he escrito esto significa que podría "captar" falsos positivos, si el último pedido del artículo en cuestión fue, digamos, 6 meses. y hace una semana Y no hubo ningún pedido entre hace 6 meses y una semana y hace exactamente 6 semanas. Decidí que esto era "lo suficientemente cercano" para ti.
El único otro defecto es que hay algunos errores de apariencia desagradable cerca de la parte superior de la hoja, porque para el primer registro (es decir, fila 2) no hay ningún registro de hace 6 meses. Así que terminemos con un IFERROR:
=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)
En otras palabras, dime cuándo fue el pedido más reciente hace más de 6 meses, a menos que no haya ninguno, en cuyo caso solo dame el primer pedido del que tengamos registro.
Esta fórmula se copia. Eso está aquí:
Paso 2: convierta los datos iniciales del rango en una ubicación de celda
En la celda H2:
=MATCH(G2,A:A,0)
Éste es fácil. ¿En qué ubicación ordinal de la columna Aencontramos la fecha de hace 6 meses que establecimos en el Paso 1?
Esta fórmula se copia. Aquí estamos:
Paso 3: hazlo
Avancemos y pongamos esto en, ¿qué tal?, I21para que podamos ver cómo funciona.
=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)
Esto es, básicamente, sólo una simple INDEX MATCHbúsqueda. Pero lo que hemos hecho es reemplazar las referencias de celda de las matrices de esas funciones con INDIRECTreferencias al valor que acabamos de sembrar en la columna H.
En mis datos de muestra, la fila 21es un registro de ventas con una fecha de 10/1/2018. La columna Gencuentra el registro de ventas más reciente de más de 6 meses antes de esa fecha, que en mi fecha de muestra resulta ser 4/1/2018. La columna Hfija la (primera) ubicación de esa fecha en la columna Aen la octava fila. Por lo tanto, INDIRECT("A"&H21&dice que se inicie la matriz para la búsqueda en la fila Aidentificada por el valor en H. ¡Lindo! Y la otra mitad de INDIRECT, ":D"&ROW()-1)dice que finalice la matriz en la fila Dque está una fila por encima del registro en el que nos encontramos actualmente.
En otras palabras, podrías escribir
=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)
Para encontrar la primera incidencia de su código de producto en DTODO el libro de ventas, pero no queremos hacer eso. Entonces reemplazamos las referencias de celda con rangos dinámicos que utilizamos para generar en los dos pasos anteriores. ¿Conmigo?
Entonces eso se ve así:
Una vez que copiamos esa fórmula (envuelta en otra IFERRORpara las apariencias) hacia arriba 2y hacia abajo, tienes:
Hay un falso positivo en la primera fila. Yo también voy a vivir con eso.
Paso 4: combinar
Entonces la fórmula en Ireferencias H, qué referencias G. La expansión inversa genera una fórmula final que dice:
=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")
Y parece:
Entonces, si lo desea, puede simplemente copiar esa fórmula en la columna "reordenar" en la fila 2y copiarla hacia abajo.
NÓTESE BIEN.
- Utilizará Copiar formato en su campo de salida, porque las fórmulas tal como están escritas tratarán las fechas del texto
Acomo números de serie de fechas y también devolverán un número de serie. - Tenga en cuenta la debilidad en el Paso 1: puede ver en la captura de pantalla del
helper1campo en el Paso 3 que en mis datos de muestra, la fecha de hace 6 meses para el 1/10 era 1/4 y la fecha de hace 6 meses para el 10 /5 eratambién4/1, porque no hubo ningún pedido entre el 4/1 y el 4/5. Potencialmente, esto podría provocar falsos positivos. - Nuevamente, como se analizó en el Paso 3, también habrá un falso positivo en el primer registro de ventas que utilice este método.
Respuesta3
Mi enfoque es un poco diferente para resolver el problema, ya que elegí el hilo de OP.
Un nuevo pedido sería cualquier cosa que el cliente no haya pedido en los últimos 6 meses o nunca.
Un nuevo pedido sería si el cliente hubiera comprado ese producto específico en los últimos 6 meses.
- Una fórmula de matriz (CSE) en celda
H41, termine conCtrl+Mayús+Entrar.
{=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")}
Situación 1:
Nueva fecha de tracción: 03/26/19.
Nombre del cliente: Bob.
Nombre del producto: Cake.
Orden Sataus: Order before 12 months.
Situación 2:
Nueva fecha de tracción: 03/26/19.
Nombre del cliente: Bob.
Nombre del producto: Milk.
Orden Sataus: New Order.
NÓTESE BIEN
Porque la diferencia entre la Fecha de transacción anterior ( 10/01/18) y la Fecha de transacción nueva ( 03/26/19) es inferior a 6 meses.
Situación 3:
Nueva fecha de tracción: 03/26/19.
Nombre del cliente: Bob.
Nombre del producto: Wheat.
Orden Sataus: Order before 6 months.
Situación 4:
Nueva fecha de tracción: 03/26/19.
Nombre del cliente: Bob.
Nombre del producto: Fruit.
Orden Sataus: Cust's. New Pro.Order.
Nota:
Si ingresa el nombre del nuevo cliente y el producto y la fecha antiguos o nuevos, obtendrá Cust's . New Pro. Orderel estado.
Ahora déjame explicarte cómo funciona la Fórmula.
La fórmula se puede dividir en dos partes.
Part 1
{=IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match")}
Básicamente, se trata de una búsqueda de 2 criterios que encuentra Old Transaction Date, Customer & the Producty la fórmula lo considera como Start Datesi DATEDIFla fórmula estuviera dentro de A41:A47...
Part 2
El original DATEDIFes,
{=LOOKUP(DATEDIF(A41:A47,I41,"m"),{0,6,12},{"New order","Order before 6 month","Order before 12 months"})}
Donde A41:A47se reemplazó con la Part 1Fórmula como Start Datey End Dateestá en la celda I41.
Y ambas partes están muy bien envueltas IFERROR.
NÓTESE BIEN
- Puede ajustar las referencias de celda según sea necesario.
- Los mensajes con la Fórmula también se pueden modificar según su elección.
Respuesta4
Puedes utilizar la siguiente fórmula:
=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )
Utiliza la técnica de la vieja escuela de pruebas simples de "rango=" para producir matrices que le dicen cada cosa (¿Está el nombre del cliente en alguna celda del rango de la columna A? ¿Está el nombre del producto en el rango de la columna D? ¿Están las fechas en el rango?). columna ¿Un rango dentro de los 183 días posteriores al pedido de hoy?) y luego los multiplica para obtener una matriz final.
Los valores de la matriz sin formato son valores VERDADEROS/FALDOS, pero multiplicarlos obliga a Excel a cambiarlos a valores 1/0 que se multiplican muy bien. El resultado final es una matriz con 1 cuando se cumplen las tres condiciones anteriores y 0 cuando no es así. Excel no convierte los elementos de la matriz nuevamente a valores TREU/FALSE, por lo que toda la matriz es numérica.
SUMluego los resume todos en un solo valor. Si el resultado es distinto de 0, entonces existe al menos un mismo pedido en los últimos 183 días. Si es 0, entonces no existe ninguno. Simplemente IFverifica cuál es el resultado y le dice "Nuevo" o "Reordenar".
Realmente el único punto de interés es la mezcla de absolutismo versus relatividad en el rango de direccionamiento. Todos los puntos de inicio del rango son completamente absolutos, por lo que la esquina superior izquierda de cada rango está anclada, inmóvil. Los puntos finales del rango solo hacen que la columna de la referencia sea absoluta para que el rango crezca a medida que agrega filas, pero nunca incluye la fila actual... por lo que comienza en la parte superior y termina una fila por encima de la fila actual.
Si no está claro desde el principio, coloco la información que aparece en la celda de la columna G de la fila del pedido que acaba de ingresar. Sin embargo, podría estar en otro lugar, incluso ni siquiera en la misma fila ni en la misma hoja, y podría usarse para realizar una prueba de formato condicional para cambiar los colores de las celdas de las filas para mostrar "Nuevo/Reordenar" de esa manera.