[准提方帖]請為各分店會員卡編號列出連續碼

准提部林

請為各分店會員卡編號列出連續碼
　
Ｅ-Ｈｏｍｅ公司在〔廣州．北京．杭州．上海〕都設有分店，
每周各店會將所發出的會員卡〔起始號碼〕及〔結束號碼〕傳回公司，
會員編號以一個〔英文碼〕及４～８位數字組成，且號碼不會有重覆。
　
希望在 Ｅ３ 寫一公式，可右拉下拉列出各分店的會員卡連續號碼，
當公司人員在Ａ３：Ｃ１５逐一輸入時，可逐步顯示結果，且不出現錯誤值。
　


～～純公式．一式完成～～ 　
請各位高手不吝指教，提供智慧結晶供後進觀摩學習，
公式長短及版本不拘（２００７以上版本恕無法驗證），
鮮花有限，若有遺漏請包涵！
　
參考附件：
TT20170212-01.rar (2.36 KB, 下载次数: 41)

2017-2-12 20:23 上传

点击文件名下载附件

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
＜結語＞
＿＿本題看似複雜，但其〔不重覆〕編碼是可突破的，
　　而且只要平常思路即可解決，以下是個人的〔冷盤〕及〔家常菜〕：
１：=IF(E2="","",IF(OR(ROW()=3,$C$3:$C$15=E2),INDEX($B:$B,SMALL(IF($A$3:$A$15=E$2,ROW($3:$15),4^8),SUM(COUNTIF($C:$C,E$2:E2))+1)),LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9))&"")
２：=IF(E2="","",IF(OR(E2=E$2,$C$3:$C$15=E2),INDEX($B:$B,SMALL(IF($A$3:$A$15=E$2,ROW($3:$15),4^8),SUM(COUNTIF($C:$C,E$2:E2))+1)),LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9))&"")
３：=LOOKUP("z",IF({1,0},"",IF(OR(E2=E$2,$C$3:$C$15=E2),VLOOKUP(E$2,OFFSET($A$1,IF(E2=E$2,,MATCH(E2,$C:$C,)),,99,3),2,),LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9))))
４：=LOOKUP("z",IF({1,0},"",IF(OR(E2=E$2,COUNTIF($C$3:$C$15,E2)),VLOOKUP(E$2,OFFSET($A$1,IF(E2=E$2,,MATCH(E2,$C:$C,)),,99,3),2,),LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9))))
　
＿＿接著〔最精華〕的〔海鮮極品〕，是我最想不到的：
１３樓：=IF(MAX(MMULT(COUNTIF(E$2:E2,$A$3:$C15),{1;1;-1}))=2,LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9),LOOKUP(,0/FREQUENCY(1,($A$1:$A15=E$2)-COUNTIF(E$2:E2,$C$1:$C15)),$B:$B)&"")
１７樓：=LOOKUP("咗",IF({1,0},LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9),INDEX($B:$B,MATCH(2,FREQUENCY({1,2},MMULT(COUNTIF(E$2:E2,$A$1:$C15),{2;-1;-1})),))&""))
１７樓：=IFNA(INDEX($B:$B,MATCH(2,FREQUENCY({1,2},MMULT(COUNTIF(E$2:E2,$A$1:$C15),{2;-1;-1})),)),LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9))&""
１９樓：=LOOKUP("咗",CHOOSE(FREQUENCY({1,2},MMULT(COUNTIF(E$2:E2,$A$1:$C15),{2;-1;-1})),LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9),$B$1:$B16&""))
＊＊＊１９樓公式是〔神來之手〕，望之莫及～～
　
＿＿還有．piny版主的進階式：
=IF(-1^SUM(COUNTIF(E$2:E2,$B$3:$C15))+1,LOOKUP(,0/FREQUENCY(1,($A$1:$A15=E$2)-COUNTIF(E$2:E2,$C$1:$C15)),$B:$B)&"",LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9))

～～謝謝各位．獲益良多～～
　
　

准提部林

這論壇何時可開放以自己擁有的花送人, 而不僅限預設那少得可憐的幾朵?
真是~~
有心送花空折枝，無奈得解沒花簇

xwcxks48

1. =IF(ROW(A1)>SUM(IF(E$2=$A$3:$A$15,REPLACE($C$3:$C$15,1,1,"")-REPLACE($B$3:$B$15,1,1,"")+1)),"",CHAR(LEFT(SMALL(IF(IF(E$2=$A$3:$A$15,CODE($B$3:$B$15)&REPLACE($B$3:$B$15,1,1,""))+COLUMN($A$1:$AZ$1)-1<=1*IF(E$2=$A$3:$A$15,CODE($C$3:$C$15)&REPLACE($C$3:$C$15,1,1,"")),IF(E$2=$A$3:$A$15,CODE($B$3:$B$15)&REPLACE($B$3:$B$15,1,1,""))+COLUMN($A$1:$AZ$1)-1),ROW(A1)+13-SUM(1*($A$3:$A$15=E$2))),2))&REPLACE(SMALL(IF(IF(E$2=$A$3:$A$15,CODE($B$3:$B$15)&REPLACE($B$3:$B$15,1,1,""))+COLUMN($A$1:$AZ$1)-1<=1*IF(E$2=$A$3:$A$15,CODE($C$3:$C$15)&REPLACE($C$3:$C$15,1,1,"")),IF(E$2=$A$3:$A$15,CODE($B$3:$B$15)&REPLACE($B$3:$B$15,1,1,""))+COLUMN($A$1:$AZ$1)-1),ROW(A1)+13-SUM(1*($A$3:$A$15=E$2))),1,2,""))

复制代码

拼凑出来的公式，顺序有点不一样
如果07及以上版本应该可以更简单些。。

准提部林

xwcxks48 发表于 2017-2-12 21:41
拼凑出来的公式，顺序有点不一样
如果07及以上版本应该可以更简单些。。

好在可以在2003中順利驗證,
感謝不吝提供貴解!!

丢丢表格

AV=SMALL(IF(COLUMN(Sheet1!$A:$X)<(Sheet1!$A$3:$A$9=Sheet1!E$2)*(MMULT(-MID(Sheet1!$B$3:$C$9,2,9),{1;-1})+2),ROW(Sheet1!$3:$9)*100+COLUMN(Sheet1!$A:$X)-1),ROW(Sheet1!A1))

=IFERROR(LEFT(INDEX($B:$B,av%))&MID((1&MID(INDEX($B:$B,av%),2,9))+RIGHT(av,2),2,9),"")

sqrall900

=IF(COUNTIF($A$3:$A$15,E$2)*(ROW(A1)<=SUM((MMULT(--(0&MID($B$3:$C$15,2,100)),{-1;1})+1)*($A$3:$A$15=E$2))),LOOKUP(ROW(A1),MMULT(N(ROW($3:$15)>COLUMN($C:$O)),(MMULT(--(0&MID($B$3:$C$15,2,100)),{-1;1})+1)*($A$3:$A$15=E$2))+1,LOOKUP(ROW($3:$15),IF($A$3:$A$15=E$2,ROW($3:$15)),LEFT($B$3:$B$15)&TEXT(MID($B$3:$B$15,2,10)+ROW(A1)-LOOKUP(ROW(A1),MMULT(N(ROW($3:$15)>COLUMN($C:$O)),(MMULT(--(0&MID($B$3:$C$15,2,100)),{-1;1})+1)*($A$3:$A$15=E$2))+1),REPT(0,LEN($B$3:$B$15)-1)))),"")

sqrall900

感觉很麻烦，耗时30分钟

sqrall900

缩短一点=IF(COUNTIF($A$3:$A$15,E$2)*(ROW(A1)<=SUM((MMULT(--(0&MID($B$3:$C$15,2,100)),{-1;1})+1)*($A$3:$A$15=E$2))),LOOKUP(ROW(A1),MMULT(N(ROW($3:$15)>COLUMN($C:$O)),(MMULT(--(0&MID($B$3:$C$15,2,100)),{-1;1})+1)*($A$3:$A$15=E$2))+1,LEFT($B$3:$B$15)&TEXT(MID($B$3:$B$15,2,10)+ROW(A1)-LOOKUP(ROW(A1),MMULT(N(ROW($3:$15)>COLUMN($C:$O)),(MMULT(--(0&MID($B$3:$C$15,2,100)),{-1;1})+1)*($A$3:$A$15=E$2))+1),REPT(0,LEN($B$3:$B$15)-1))),"")

sqrall900

再缩短

1. =IF(COUNTIF($A$3:$A$15,E$2)*(ROW(A1)<=SUM((MMULT(--(0&MID($B$3:$C$15,2,100)),{-1;1})+1)*($A$3:$A$15=E$2))),LOOKUP(ROW(A1),MMULT(N(ROW($3:$15)>COLUMN($C:$O)),(MMULT(--(0&MID($B$3:$C$15,2,100)),{-1;1})+1)*($A$3:$A$15=E$2))+1,REPLACE((1&MID($B$3:$B$15,2,10))+ROW(A1)-COUNTIF(E$2:E2,"<>"&LEFT($B$3:$B$15,3)&"*"),1,1,LEFT($B$3:$B$15))),"")

复制代码

准提部林

丢丢表格 发表于 2017-2-12 22:13
AV=SMALL(IF(COLUMN(Sheet1!$A:$X)

哈! 無法驗證, 有勞其他大大,
感謝參與!!

鮮花剛撒種子, 明天開再送!!