查看销售数据。如何确定订单是新订单还是针对特定客户和产品的重新订购？

Question 1

也许我误解了什么，但这似乎相当简单。我对这个问题的理解是，如果当前行上方至少有一行具有 B与当前行相同的客户名称（列）、相同的产品名称（列）以及在当前交易日期的过去六个月内的 D交易日期（列），则该行代表重新排序。列，并且可以忽略。我假设行按交易日期排序（尽管我想我不需要做这个假设）。ACEF

交易日期标准是最“难”的（我使用这个词比较宽泛）。过去日期是指过去六个月内， A2如果

> EDATE(A2,-6)

因此，为了计算当前满足三个条件的行，我们使用

=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)

这个A$2:A2符号很有趣。它表示从第 2 行开始到当前行结束的范围；即，直到（包括）当前行为止的所有内容。此计数将始终至少为 1，因为当前行也算数。如果它大于 1，则至少有一行也匹配。所以答案是输入

=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")

放入H2（或任何您想要的地方）并向下拖动/填充。

如果行可能无序，我们需要搜索整个表并测试日期是否小于当前日期：

=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")

我使用99来表示数据的最后一行。我将测试从改为<=1因为 =0 测试< A2会消除当前行。如果您的数据可能包含多个具有相同客户名称和产品名称以及完全相同交易日期的行，请指定应如何处理它们。

Answer

也许我误解了什么，但这似乎相当简单。我对这个问题的理解是，如果当前行上方至少有一行具有 B与当前行相同的客户名称（列）、相同的产品名称（列）以及在当前交易日期的过去六个月内的 D交易日期（列），则该行代表重新排序。列，并且可以忽略。我假设行按交易日期排序（尽管我想我不需要做这个假设）。ACEF

交易日期标准是最“难”的（我使用这个词比较宽泛）。过去日期是指过去六个月内， A2如果

> EDATE(A2,-6)

因此，为了计算当前满足三个条件的行，我们使用

=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)

这个A$2:A2符号很有趣。它表示从第 2 行开始到当前行结束的范围；即，直到（包括）当前行为止的所有内容。此计数将始终至少为 1，因为当前行也算数。如果它大于 1，则至少有一行也匹配。所以答案是输入

=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")

放入H2（或任何您想要的地方）并向下拖动/填充。

如果行可能无序，我们需要搜索整个表并测试日期是否小于当前日期：

=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")

我使用99来表示数据的最后一行。我将测试从改为<=1因为 =0 测试< A2会消除当前行。如果您的数据可能包含多个具有相同客户名称和产品名称以及完全相同交易日期的行，请指定应如何处理它们。

Question 2

噢，你一定会喜欢这个。

我将把它分解成几个步骤，因为最终的公式会相当复杂。

设置

我按照您指定的格式创建了一个工作表。我添加了三列来说明我们需要做什么，我将在下面逐一描述。请注意，在第一个屏幕截图中，我冻结了顶行并向下滚动了一点。

步骤 1：确定搜索范围

首先，我们需要知道 6 个月前是什么时候（以及该日期在电子表格中的位置）。我们将在每条记录中使用它EDATE来找出答案。

在单元格中G2：

VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)

这很棒 - 这为我们提供了 6 个月前的最新销售记录。请注意，如果您的销售不频繁（即销售记录之间的间隔很大），我写的方式确实意味着您可能会“捕获”误报，如果相关商品的最后一次订单是在 6 个月零一周前，并且 6 个月零一周前和 6 周前之间没有任何订单。我认为这对你来说“足够接近”。

唯一的另一个缺陷是工作表顶部附近有一些令人不快的错误，因为对于第一条记录（即行2），根本没有 6 个月前的记录。所以让我们用来包装IFERROR：

=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)

换句话说，告诉我 6 个月前最近的订单是什么时候，除非没有，在这种情况下只需给我我们记录的第一个订单。

此公式复制下来。如下：

第 2 步：将范围起始数据转换为单元格位置

在单元格中H2：

=MATCH(G2,A:A,0)

这个很简单。A我们在列中的哪个序数位置可以找到我们在步骤 1 中确定的 6 个月前的日期？

此公式复制下来。如下所示：

步骤 3：行动起来

我们先跳过这一步，把它放进去，怎么样，，I21这样我们就能看到它是如何工作的。

=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)

从根本上讲，这只是一次简单的INDEX MATCH搜索。但我们所做的是将这些函数数组的单元格引用替换为INDIRECT我们刚刚播种到列中的值的引用H。

在我的示例数据中，行21是日期为的销售记录10/1/2018。列G查找该日期之前 6 个月以上的最近销售记录，在我的示例日期中恰好是4/1/2018。列H将该日期的（第一个）位置固定A在第 8 行的列中。因此表示从中的值标识的INDIRECT("A"&H21&行开始搜索数组。太棒了！而的另一半表示在中比我们当前所在的记录高一行的行结束数组。AHINDIRECT":D"&ROW()-1)D

换句话说，你可以写

=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)

在整个销售分类账中找到您的产品代码的第一次出现D- 但我们不想这样做。所以我们用之前两个步骤生成的动态范围替换单元格引用。跟我一起吗？

看起来是这样的：

一旦我们复制该公式（IFERROR为了美观，将其包裹在另一个公式中）到行2并再复制回来，您就会得到：

第一行有误报。我也会接受这一点。

步骤 4：合并

I因此，参考文献中的公式H，参考文献G。反向扩展得到最终公式，如下所示：

=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")

看起来像：

因此，如果您愿意，您可以将该公式复制到行中的“重新排序”列中2并向下复制。

注意：

您将在输出字段上使用格式刷，因为所写的公式会将文本日期视为A日期序列号，并返回序列号。
请注意步骤 1 中的弱点 - 您可以在helper1步骤 3 中的字段屏幕截图中看到，在我的示例数据中，10/1 的 6 个月前的日期是 4/1，而 10/5 的 6 个月前的日期是还4/1，因为 4/1 到 4/5 之间根本没有订单。这可能会产生误报。
再次，如步骤 3 中讨论的那样，使用此方法的第一个销售记录也会出现误报。

Answer

噢，你一定会喜欢这个。

我将把它分解成几个步骤，因为最终的公式会相当复杂。

设置

我按照您指定的格式创建了一个工作表。我添加了三列来说明我们需要做什么，我将在下面逐一描述。请注意，在第一个屏幕截图中，我冻结了顶行并向下滚动了一点。

步骤 1：确定搜索范围

首先，我们需要知道 6 个月前是什么时候（以及该日期在电子表格中的位置）。我们将在每条记录中使用它EDATE来找出答案。

在单元格中G2：

VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)

这很棒 - 这为我们提供了 6 个月前的最新销售记录。请注意，如果您的销售不频繁（即销售记录之间的间隔很大），我写的方式确实意味着您可能会“捕获”误报，如果相关商品的最后一次订单是在 6 个月零一周前，并且 6 个月零一周前和 6 周前之间没有任何订单。我认为这对你来说“足够接近”。

唯一的另一个缺陷是工作表顶部附近有一些令人不快的错误，因为对于第一条记录（即行2），根本没有 6 个月前的记录。所以让我们用来包装IFERROR：

=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)

换句话说，告诉我 6 个月前最近的订单是什么时候，除非没有，在这种情况下只需给我我们记录的第一个订单。

此公式复制下来。如下：

第 2 步：将范围起始数据转换为单元格位置

在单元格中H2：

=MATCH(G2,A:A,0)

这个很简单。A我们在列中的哪个序数位置可以找到我们在步骤 1 中确定的 6 个月前的日期？

此公式复制下来。如下所示：

步骤 3：行动起来

我们先跳过这一步，把它放进去，怎么样，，I21这样我们就能看到它是如何工作的。

=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)

从根本上讲，这只是一次简单的INDEX MATCH搜索。但我们所做的是将这些函数数组的单元格引用替换为INDIRECT我们刚刚播种到列中的值的引用H。

在我的示例数据中，行21是日期为的销售记录10/1/2018。列G查找该日期之前 6 个月以上的最近销售记录，在我的示例日期中恰好是4/1/2018。列H将该日期的（第一个）位置固定A在第 8 行的列中。因此表示从中的值标识的INDIRECT("A"&H21&行开始搜索数组。太棒了！而的另一半表示在中比我们当前所在的记录高一行的行结束数组。AHINDIRECT":D"&ROW()-1)D

换句话说，你可以写

=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)

在整个销售分类账中找到您的产品代码的第一次出现D- 但我们不想这样做。所以我们用之前两个步骤生成的动态范围替换单元格引用。跟我一起吗？

看起来是这样的：

一旦我们复制该公式（IFERROR为了美观，将其包裹在另一个公式中）到行2并再复制回来，您就会得到：

第一行有误报。我也会接受这一点。

步骤 4：合并

I因此，参考文献中的公式H，参考文献G。反向扩展得到最终公式，如下所示：

=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")

看起来像：

因此，如果您愿意，您可以将该公式复制到行中的“重新排序”列中2并向下复制。

注意：

您将在输出字段上使用格式刷，因为所写的公式会将文本日期视为A日期序列号，并返回序列号。
请注意步骤 1 中的弱点 - 您可以在helper1步骤 3 中的字段屏幕截图中看到，在我的示例数据中，10/1 的 6 个月前的日期是 4/1，而 10/5 的 6 个月前的日期是还4/1，因为 4/1 到 4/5 之间根本没有订单。这可能会产生误报。
再次，如步骤 3 中讨论的那样，使用此方法的第一个销售记录也会出现误报。

Question 3

我解决这个问题的方法有点不同，因为我选择了 OP 的帖子，，

新订单是指客户在过去 6 个月内或从未订购过的任何商品。
如果客户在过去 6 个月内购买过该特定产品，则会重新订购。

单元格中的数组（CSE）公式H41，以Ctrl+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")}

情况 1：

新牵引日期：03/26/19。

顾客姓名：Bob。

产品名称：Cake。

订购 Sataus：Order before 12 months。

情况 2：

新牵引日期：03/26/19。

顾客姓名：Bob。

产品名称：Milk。

订购 Sataus：New Order。

注意：

因为旧交易日期（10/01/18）与新交易日期（03/26/19）之间的差异小于 6 个月。

情况 3：

新牵引日期：03/26/19。

顾客姓名：Bob。

产品名称：Wheat。

订购 Sataus：Order before 6 months。

情况 4：

新牵引日期：03/26/19。

顾客姓名：Bob。

产品名称：Fruit。

订购 Sataus：Cust's. New Pro.Order。

笔记：

如果您输入新客户的姓名以及旧产品或新产品和日期，您将获得Cust's . New Pro. Order状态。

现在让我解释一下该公式的工作原理。

公式可分为两部分。

Part 1

 {=IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match")}

基本上，它是 2 个条件查找，用于查找Old Transaction Date，Customer & the Product并且公式将其视为Start Date公式DATEDIF在...之内A41:A47。

Part 2

原文DATEDIF是，

{=LOOKUP(DATEDIF(A41:A47,I41,"m"),{0,6,12},{"New order","Order before 6 month","Order before 12 months"})}

其中A41:A47已被替换为Part 1公式Start Date并且End Date位于单元格中I41。

并且两个部分都用很好地包裹着IFERROR。

注意：

您可以根据需要调整单元格引用。
带有公式的消息也可以根据您的选择进行更改。

Answer

我解决这个问题的方法有点不同，因为我选择了 OP 的帖子，，

新订单是指客户在过去 6 个月内或从未订购过的任何商品。
如果客户在过去 6 个月内购买过该特定产品，则会重新订购。

单元格中的数组（CSE）公式H41，以Ctrl+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")}

情况 1：

新牵引日期：03/26/19。

顾客姓名：Bob。

产品名称：Cake。

订购 Sataus：Order before 12 months。

情况 2：

新牵引日期：03/26/19。

顾客姓名：Bob。

产品名称：Milk。

订购 Sataus：New Order。

注意：

因为旧交易日期（10/01/18）与新交易日期（03/26/19）之间的差异小于 6 个月。

情况 3：

新牵引日期：03/26/19。

顾客姓名：Bob。

产品名称：Wheat。

订购 Sataus：Order before 6 months。

情况 4：

新牵引日期：03/26/19。

顾客姓名：Bob。

产品名称：Fruit。

订购 Sataus：Cust's. New Pro.Order。

笔记：

如果您输入新客户的姓名以及旧产品或新产品和日期，您将获得Cust's . New Pro. Order状态。

现在让我解释一下该公式的工作原理。

公式可分为两部分。

Part 1

 {=IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match")}

基本上，它是 2 个条件查找，用于查找Old Transaction Date，Customer & the Product并且公式将其视为Start Date公式DATEDIF在...之内A41:A47。

Part 2

原文DATEDIF是，

{=LOOKUP(DATEDIF(A41:A47,I41,"m"),{0,6,12},{"New order","Order before 6 month","Order before 12 months"})}

其中A41:A47已被替换为Part 1公式Start Date并且End Date位于单元格中I41。

并且两个部分都用很好地包裹着IFERROR。

注意：

您可以根据需要调整单元格引用。
带有公式的消息也可以根据您的选择进行更改。

Question 4

您可以使用以下公式：

=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )

它使用简单的“范围=”测试的老式技术来生成告诉您每件事的数组（客户的姓名是否在 A 列范围内的任何单元格中？产品名称是否在 D 列范围内？A 列中的日期是否在今天订单的 183 天内？）然后将它们相乘以获得最终数组。

原始数组值是 TRUE/FALSE 值，但将它们相乘会强制 Excel 将它们更改为 1/0 值，这样相乘效果会很好。最终结果是数组中满足上述所有三个条件时为 1，不满足上述所有三个条件时为 0。Excel 不会将数组的元素转换回 TREU/FALSE 值，因此整个数组都是数字。

SUM然后将它们全部加起来形成一个值。如果结果不是 0，则表示在过去 183 天内至少存在一个相同的订单。如果结果为 0，则表示不存在相同的订单。它IF只是检查结果是哪一个，并告诉您“新订单”或“重新订购”。

真正唯一有趣的点是范围寻址中绝对性和相对性的混合。所有范围的起点都是完全绝对的，因此每个范围的左上角都是固定的，不会移动。范围的终点只使引用的列绝对，因此范围会随着行的增加而增长，但永远不会包括当前行……因此从顶部开始并在当前行上方一行结束。

如果一开始就不清楚，我假设信息出现在刚输入的订单行的 G 列单元格中。但是，它可能在其他地方，甚至不在同一行，也不在同一张表中，并且可以用于进行条件格式测试，以更改行单元格颜色以以这种方式显示“新建/重新订购”。

Answer

您可以使用以下公式：

=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )

它使用简单的“范围=”测试的老式技术来生成告诉您每件事的数组（客户的姓名是否在 A 列范围内的任何单元格中？产品名称是否在 D 列范围内？A 列中的日期是否在今天订单的 183 天内？）然后将它们相乘以获得最终数组。

原始数组值是 TRUE/FALSE 值，但将它们相乘会强制 Excel 将它们更改为 1/0 值，这样相乘效果会很好。最终结果是数组中满足上述所有三个条件时为 1，不满足上述所有三个条件时为 0。Excel 不会将数组的元素转换回 TREU/FALSE 值，因此整个数组都是数字。

SUM然后将它们全部加起来形成一个值。如果结果不是 0，则表示在过去 183 天内至少存在一个相同的订单。如果结果为 0，则表示不存在相同的订单。它IF只是检查结果是哪一个，并告诉您“新订单”或“重新订购”。

真正唯一有趣的点是范围寻址中绝对性和相对性的混合。所有范围的起点都是完全绝对的，因此每个范围的左上角都是固定的，不会移动。范围的终点只使引用的列绝对，因此范围会随着行的增加而增长，但永远不会包括当前行……因此从顶部开始并在当前行上方一行结束。

如果一开始就不清楚，我假设信息出现在刚输入的订单行的 G 列单元格中。但是，它可能在其他地方，甚至不在同一行，也不在同一张表中，并且可以用于进行条件格式测试，以更改行单元格颜色以以这种方式显示“新建/重新订购”。

查看销售数据。如何确定订单是新订单还是针对特定客户和产品的重新订购？

答案1

答案2

设置

步骤 1：确定搜索范围

第 2 步：将范围起始数据转换为单元格位置

步骤 3：行动起来

步骤 4：合并

注意：

答案3

答案4

相关内容