单元格内文本数字查询并提取

cabcyvr

数据源: 999 个4位数（4001-4999），D列和F列

数据源内容说明：

• DF列全部为文本格式，只有逗号或是中划线（如只有一个4位数，如D8内的4500，可能会出现数字格式）
• 4位数范围（最小4001，最大4999）
• "4001-4005" 表示范围, 包含5个4位数，其中4002，4003，4004没有显示
• "4001 ,4003"表示两个独立4位数
• D列和F列分别为针对于C列MOD号码的有效性文本描述，范围组合"4???-4???‘在同一单元格内的位置不定，数量不定
• D列和F列的文本4位数数字输入规则为从左向右升序排列，中间用逗号或是中划线分隔,只要符合此书写规格即为输入正常，不需要去理会为何不将类似“4001,4002,4003,4004"输入成 "4001-4004"
  

要求: 将4001-4999范围内的999和4位数数字分别在D列和F列的同一行内查询, 如果999个4位数数字中的任何一个同时被D列和F列的文本有效性（EFF）描述所包含

• 在H列显示同时被两列包含的4位数数量
• 在I列的同一单元格内显示所有同时被D列和F列有效性包含的实际4位数，中间用"/"分隔. 如果没有则显示”/“
  

注：

• H7为最大的可能重复数量（999），I列内的最大的可能字符长度为4994
• Excel版本为O365（可以使用TEXTJOIN等新函数）
  

cutecpu

I2 陣列公式:
=TEXTJOIN("/",,
IF(
(
ISNUMBER(FIND(TEXT(ROW($1:$999),"4000"),D2))+
ISNUMBER(FIND(TEXT(LOOKUP(ROW($1:$999),FIND(TEXT(ROW($1:$999),"4000"),D2)^0*ROW($1:$999)),"000")&"-",D2))
)*
(
ISNUMBER(FIND(TEXT(ROW($1:$999),"4000"),F2))+
ISNUMBER(FIND(TEXT(LOOKUP(ROW($1:$999),FIND(TEXT(ROW($1:$999),"4000"),F2)^0*RO
W($1:$999)),"000")&"-",F2))
),
TEXT(ROW($1:$999),"4000"))
)

公式說明:
藍色: D 列部份 4000-4999 bit 數組(0: 不存在, 1: 存在)
橙色: F 列部份 4000-4999 bit 數組(0: 不存在, 1: 存在)

H2 公式: =LEN(SUBSTITUTE(I2,"/",))/4

祝順心，南無阿彌陀佛

cabcyvr

以下是我对你的textjoin条件部分的结构理解
TEXTJOIN ("/",,如符合(条件1 OR 条件2) AND ( 条件3 OR 条件4),Row(4001:4009),空)

条件1，3好理解没问题，条件2，4类似。为了简化理解最重要的部分，以条件2对D2的范围查询为例，最关键的部分
FIND(TEXT(ROW($1:$999),"4000"),D2)^0*ROW($1:$999) 看了好一会才明白你的思路，主要是把 0*ROW($1:$999)当作一组数作为FIND(TEXT(ROW($1:$999),"4000"),D2)次方数在看，后来猛然醒悟，实际的顺序应该是
(FIND(TEXT(ROW($1:$999),"4000"),D2)^0)*ROW($1:$999)      没有理解错吧？
我自己写的话会加括号，虽然长度增加但是好一眼看明白，否则时间长了自己都会看花眼（在说我自己）

模糊之中记得以前用过此思路（帮人解答时），这次是自己实际工作中要用到了反而彻底忘记了，准备回去面壁！阿彌陀佛 ！

今天用此方法对两列的15998行数据，（15998x2）个单元格做了校对，查出32行逻辑错误的记录(不能同时包含）

cutecpu

cabcyvr 发表于 2019-10-5 06:08
以下是我对你的textjoin条件部分的结构理解
TEXTJOIN ("/",,如符合(条件1 OR 条件2) AND ( 条件3 OR 条件4 ...

兄弟，您好棒喔，您理解的完全正確喔，給您一個大大的讚喔{:9_305:}
還有您說: 「適時地替运算式加上括号，有助于阅读者对语意上的理解，
避免因为运算子优先顺序的關係，而誤解語意」，我完全同意您说的喔！
對了，再請兄弟您將帖子設為[已解決]一下喔！

南無阿彌陀佛！

cabcyvr

已经按照大侠的要求更改为”已解决“