【循环引用】练习-合并不重复文本

楼主 gouweicao78	【答题期限】2009-12-20日0:00截稿（以回帖时间为准），欢迎大家踊跃参与。【题目】如图，要求通过设置循环引用公式，实现以下功能 1、B:E列的项目合并文本，用逗号相隔，在同一行的H列单元格中返回结果； 2、B:E列中重复的文本、空单元格要剔除。【要求】 1、不使用VBA编程 2、在答题中上传压缩后的附件，工作簿命名为“【循环引用】练习-合并不重复文本-ID” 【评分】 1、实现功能（如图所示）可以获得3个技能分 2、写清楚制作过程可再获得1~2技能分 3、精彩答案可再获得加分，总分控制在10个技能分以内。附件：【循环引用】练习-合并不重复文本.rar
2楼 biaotiger1	【操作步骤】 1、F3单元格输入公式 =B3&C3&D3&E3 ，并下拉至F12单元格将多单元格数据合并到一个单元格中，为后面迭代计算准备 2、设置迭代次数为4以上，我设置为4 3、F1单元格设置计数器，输入公式 =A1((--F1<COUNTA(B2:E2))+F1) F1单元格的作用在于通过获取单元格区域的列数逐次返回迭代次数 4、H3单元格输入公式 =IF(A$1,SUBSTITUTE(TRIM(H3&" "&IF(ISERROR(FIND(MID(F3,F$12-1,2),H3)),MID(F3,F$12-1,2),""))," ",","),"") 并下拉至H12 H3&" "&IF(ISERROR(FIND(MID(F3,F$12-1,2),H3)),MID(F3,F$1*2-1,2),"")这部分公式初步实现了剔除重复数据，重复数据用空格“ ”代替，不同数据间用“，”分隔 TRIM函数将字符串中间多余的空格剔除，使字符串中间仅保留一个空格 SUBSTITUTE将空格替换为“-” 5、选中F1:F12单元格区域，设置字体颜色为白色。隐藏辅助单元格【循环引用】练习-合并不重复文本-biaotiger1.rar
3楼 syz105729913	参与一个【循环引用】练习-合并不重复文本-syz105729913.rar
4楼 rongjun	【循环引用】练习-合并不重复文本-rongjun.rar
5楼 knifefox	首先设置一个计数器 G2单元格 =IF(A1,G2+1,0) 然后根据计数器挨个引用项目1-项目4 判断结果数据是否含有引用数据，如果不包含则在结果数据上加上引用数据加了个判断如果结果数据为空则添加引用数据时不加"," 最后加G$2<5关闭循环【循环引用】练习-合并不重复文本-knifefox.rar
6楼 biaotiger1	不借助辅助列的解法我已经想到了，不过还要借助辅助单元格（比如计数器）来实现等下看高手解法
7楼 gouweicao78	简单点评： biaotiger1兄的答案借助了辅助列且解法较为复杂，答案局限于每个项目只能2个字。 rongjun、knifefox兄的答案也有所欠缺：比如项目为：粗刨、粗刨1、铣床或者粗刨1、粗刨、铣床——不能正确返回答案（即项目存在包含、被包含时有问题） syz兄的答案正确。我的答案： B1作为计数器，公式：=IF(A1,MOD(B1,4)+1,0)——产生一个不超过4的计数器 H3公式 =IF(A$1,H3&IF(ISERR(FIND(INDEX(B3:E3,B$1)&",",H3&",")),IF(H3="",,",")&INDEX(B3:E3,B$1),),"") 思路：利用B1的计数器，配合INDEX函数遍历B:E的4个项目，注意要与逗号合并&","，然后判断是否在H3&","中已有该项目——与逗号合并这一点rongjun、knifefox兄没有考虑，所以答案有所欠缺。当FIND在H3找不到项目时，如果H3为空（即第一个）则不加逗号，否则用H3加逗号在加该项目。【循环引用】练习-合并不重复文本（答案）.rar
8楼 biaotiger1	再来一个计数器+indirect的做法【循环引用】练习-合并不重复文本.rar
9楼 LoveJinLee	在这里学习了循环引用，昨天在网上看到一个提问，现在自己做了下，虽然还是有瑕疵，不过能这样循环计算，方便很多，谢谢各位版主提供算法。现将附件传上，看看各位版主有没有好建议。因为版本不全，所以A1开关没法像各位版主做的那样好看。求连星数.rar
10楼 gouweicao78	你这个，不需要循环引用，I3输入以下公式按CTRL+SHIFT+ENTER结束即可： =MAX(FREQUENCY(IF(B3:H3,COLUMN(B:H)),IF(B3:H3="",COLUMN(B:H))))
11楼 LoveJinLee	哈，这个更好，所以还是要交流呀哈哈谢谢总版主
12楼 LoveJinLee	我这里显示value错误
13楼 LoveJinLee	确实很佩服总版的思维。。这都被你想到其实，我都不知道FREQUENCY函数是干什么的。谢谢总版，学习了
14楼 gouweicao78	【函数解读系列】之解读FREQUENCY函数 http://www.exceltip.net/thread-3383-1-1.html
15楼 LoveJinLee	谢谢总版主，最近一直在潜心学函数。不过我忘记我之前那个附件的要求的，总版给的公式很有效，不过是返回MAX 而那个人最终的要求是只要遇到一个空格，就从下一个重新计算连星数。所以总版的比较适合求最大连星数，不过特感谢总版。很多函数的解析写的很详细，虽然有些还没吃透。慢慢来咯。特此感谢。