[准提方帖]取得日期最大單號，將其序號加一產生新單號

准提部林

取得日期最大單號，將其序號加一產生新單號
　
一、〔DateSerialNumberList〕工作表：
１．Ａ列為〔日期單號〕存放區，其內容為文本格式〔YYYYMMDD-???]。
２．單號的後三碼序號介于 001~900 之間，不會超出這個區間。
３．單號由小而大遞增排序，中間不會有空格，且最小單號不一定是從 001 開始，
　　同時也會有〔中間跳號〕情形。
４．單號會不斷增加，所以不能使用〔固定位址〕，也禁用〔定義名稱〕。
５．本〔工作表名稱〕有點長，且不可更改；
　　更有可能是〔跨檔引用〕，所以其引用的參照文字會很長，
　　在此情況下，請儘量減少引用次數，以免公式過長。

　
二、〔輸入區〕工作表：
１．Ａ列為〔日期〕輸入區。
２．請在Ｂ列輸入〔公式〕，需求如下：
　　(1)取得該日期的〔最大單號〕，並將其後三碼序號加１，產生新單號。
　　 　例如：最大單號為 20170605-004， 序號加１為　20170605-005
　　(2)若該日期並無單號存在，則以 001 為其序號，例如：20170601-001

　
三、公式要求：
１．必須適用 OFFICE 2003，一個公式完成，可直接下拖套用，越短越好。
２．不可使用〔輔助公式．定義名稱．ＶＢＡ〕，若能考慮效能更佳。
　
　
參考檔案：
TT20170728-01.rar (3.38 KB, 下载次数: 18)

2017-7-28 13:46 上传

点击文件名下载附件

鮮花有限，對提供幫助的朋友，每次僅能以一朵聊表謝意～～
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

zopey

TT20170728-01.rar (8.18 KB, 下载次数: 6)

2017-7-28 23:47 上传

点击文件名下载附件

准提部林

zopey 发表于 2017-7-28 23:47

還以為沒有人要回應這帖子, 謝謝你的首發回應!!!
公式不錯, 用了最直接易懂的方法,
DateSerialNumberList!A$2:A$34 只引用兩次, 但限制了參照範圍,
因資料會不斷累增或因刪除而減少,
且因特定原因, 不能使用動態定義名稱來取得參照資料範圍,
所以最好能用 DateSerialNumberList!A:A 來參照.
另外, 有可能是[外部檔案]參照, DateSerialNumberList! 就會變得很長, 若能只引用一次最好.

七夕、

LOOKUP的二分查找的效率相对较高点

1. =IF(COUNTIF(DateSerialNumberList!A:A,TEXT(A2,"emmdd!*")),TEXT(SUBSTITUTE(LOOKUP(,-FIND(TEXT(A2,"emmdd"),DateSerialNumberList!A:A),DateSerialNumberList!A:A),"-",)+1,"0-000"),TEXT(A2,"emmdd-!0!0!1"))

复制代码

准提部林

七夕、 发表于 2017-7-29 11:02
LOOKUP的二分查找的效率相对较高点

FIND(TEXT(A2,"yyyymmdd"),DateSerialNumberList!A:A)  2003版用不了,

DateSerialNumberList!A:A 引用了３次，跨檔引用，公式就長了點，
期待更簡約的公式，謝謝！
　
　

准提部林

這帖的公式適用于〔會計傳票．進貨單．銷貨單〕的〔新增單號〕編製，
通常會另外存放在另一專用檔案中，有其實務上的應用，
若使用ＶＢＡ或輔助公式是可以達到目的，但覺繁複，
希望以一個較短的公式完成！
　
　

七夕、

准提部林 发表于 2017-7-29 11:39
FIND(TEXT(A2,"yyyymmdd"),DateSerialNumberList!A:A)  2003版用不了,

DateSerialNumberList!A:A 引用 ...

FIND(TEXT(A2,"yyyymmdd"),DateSerialNumberList!A:A)
这都是03版支持的函数呀,咋用不了呢?
试试这个:

1. =IF(TEXT(A2,"emmdd")<LEFT(DateSerialNumberList!A$2,8),TEXT(A2,"emmdd-!0!0!1"),TEXT(MAX(SUBSTITUTE(LOOKUP(TEXT(A2,"emmdd-!9!9!0"),DateSerialNumberList!A:A),"-",)+1,TEXT(A2,"emmdd")*1000+1),"0-000"))

复制代码

努力简化ing...

准提部林

七夕、 发表于 2017-7-29 11:51
FIND(TEXT(A2,"yyyymmdd"),DateSerialNumberList!A:A)
这都是03版支持的函数呀,咋用不了呢?
试试这个:
...

貴式稍修：
=TEXT(A2,"emmdd-")&TEXT(IF(COUNTIF(DateSerialNumberList!A:A,TEXT(A2,"emmdd!*")),RIGHT(LOOKUP(TEXT(A2,"emmddz"),DateSerialNumberList!A:A),3))+1,"000")
　
是否還可再簡～～期待中～～
　
　

七夕、

准提部林 发表于 2017-7-29 12:24
貴式稍修：
=TEXT(A2,"emmdd-")&TEXT(IF(COUNTIF(DateSerialNumberList!A:A,TEXT(A2,"emmdd!*")),RIGHT( ...

在您的公式的基础上简单改下,只减了3字符

1. =TEXT(A2,"emmdd-")&TEXT(LOOKUP(999,IF({0,1},RIGHT(LOOKUP(,-FIND(TEXT(A2,"emmdd"),DateSerialNumberList!A:A),DateSerialNumberList!A:A),3))+1),"000")

复制代码

象山海鲜

1. =TEXT(A2,"emmdd-")&RIGHT(1001-LOOKUP(1,IF({1,0},,-SUBSTITUTE(LOOKUP(TEXT(A2,"emmddc"),DateSerialNumberList!A:A),TEXT(A2,"emmdd-"),))),3)

复制代码

速度快一点

********************************************************

1. =TEXT(A2,"emmdd-")&RIGHT(1001-LOOKUP(1,IF({1,0},,LOOKUP(,-SUBSTITUTE(DateSerialNumberList!A:A,TEXT(A2,"emmdd-"),)))),3)

复制代码

字符短一点