楼主 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单元格输入公式
将多单元格数据合并到一个单元格中,为后面迭代计算准备 2、设置迭代次数为4以上,我设置为4 3、F1单元格设置计数器,输入公式
F1单元格的作用在于通过获取单元格区域的列数逐次返回迭代次数 4、H3单元格输入公式
H3&" "&IF(ISERROR(FIND(MID(F3,F$1*2-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公式
【循环引用】练习-合并不重复文本(答案).rar |
8楼 biaotiger1 |
再来一个计数器+indirect的做法 【循环引用】练习-合并不重复文本.rar |
9楼 LoveJinLee |
在这里学习了循环引用,昨天在网上看到一个提问,现在自己做了下,虽然还是有瑕疵,不过能这样循环计算,方便很多,谢谢各位版主提供算法。 现将附件传上,看看各位版主有没有好建议。 因为版本不全,所以A1开关没法像各位版主做的那样好看。 求连星数.rar |
10楼 gouweicao78 |
你这个,不需要循环引用,I3输入以下公式按CTRL+SHIFT+ENTER结束即可:
|
11楼 LoveJinLee |
哈,这个更好,所以还是要交流呀 哈哈 谢谢总版主 |
12楼 LoveJinLee |
我这里显示value错误 |
13楼 LoveJinLee |
确实很佩服总版的思维。。 这都被你想到 其实,我都不知道FREQUENCY函数是干什么的。 谢谢总版,学习了 |
14楼 gouweicao78 |
【函数解读系列】之解读FREQUENCY函数 http://www.exceltip.net/thread-3383-1-1.html |
15楼 LoveJinLee |
谢谢总版主,最近一直在潜心学函数。 不过我忘记我之前那个附件的要求的,总版给的公式很有效,不过是返回MAX 而那个人最终的要求是只要遇到一个空格,就从下一个重新计算连星数。 所以总版的比较适合求最大连星数,不过特感谢总版。 很多函数的解析写的很详细,虽然有些还没吃透。 慢慢来咯。 特此感谢。 |