在处理订单时，我们经常需要根据日期来生成新的单号。
单号随日期的改变而改变，如果某一天没有生成单号，则从001开始；如果已有单号，则找到最大单号并加1来生成新的单号。
先来看看动态效果图：
首先，我们在同一表中进行操作，易于说明公式的原理，跨表就会很简单
一、查找特定日期的所有单号
为了找到某个特定日期（如2025-1-3）的所有单号，我们可以使用以下公式：
=B3:B11=O2
这里，B3:B11是日期列的范围，O2是我们要查找的日期。这个公式会返回一个布尔值数组（TRUE或FALSE），表示哪些行符合我们的查找条件。
显示TRUE的，是2025-1-3的所有单。
二、转换为可用于查找的数组
由于LOOKUP函数不能直接处理布尔值数组，我们需要将其转换为0和#DIV/0!的数组。这可以通过以下公式实现：
=0/(B3:B11=O2)
这里，如果条件为真（即日期匹配），则分子为0，分母也为0（但Excel会将其处理为#DIV/0!）。如果条件为假，则分母为0，导致公式结果为#DIV/0!。
三、使用LOOKUP函数查找最大单号
现在，我们可以使用LOOKUP函数来查找特定日期的最大单号。
LOOKUP函数会忽略#DIV/0!错误值，并返回数组中最后一个比查找值小的元素（在这里是0）对应的单号。
注意这里的单号是一定要按升序排序。
公式如下：
=LOOKUP(1,0/(B3:B11=O2),A3:A11)
但请注意，这个公式需要在数组公式环境中使用（即按Ctrl Shift Enter键输入），或者在新版本的Excel中使用动态数组功能。
为了简化操作，并使其适用于整个列，回到进货单界面，我们可以将公式修改为：
=LOOKUP(1,0/(进货明细表!B:B=I3),进货明细表!A:A)
这里，I3是我们要查找的日期所在的单元格。
四、生成新的单号
最后，我们需要根据找到的最大单号来生成新的单号。
如果当天没有单号（即LOOKUP函数返回错误），则新单号应从001开始。
这可以通过以下公式实现：
=IFERROR(LOOKUP(1,0/(进货明细表!B:B=I3),进货明细表!A:A 1),TEXT(I3,"YYYYMMDD")&"001")
这里，IFERROR函数用于捕获LOOKUP函数可能返回的错误，并返回一个新的单号，格式为“日期 001”。
如果LOOKUP函数成功找到了最大单号，则将其加1并返回；如果没有找到（即返回错误），则使用TEXT函数将日期格式化为“YYYYMMDD”并加上“001”。
总结
通过以上步骤，我们可以根据日期生成新的单号。这种方法不仅简单易懂，而且非常灵活，适用于各种类似的情况。
评论 (0)