ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 EH云课堂直播课程免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
楼主: 山菊花

[Excel 函数与公式] [第20期]把数字连接起来

[复制链接]

TA的精华主题

TA的得分主题

发表于 2007-2-23 18:54 | 显示全部楼层

跟个帖,便于评分(我记得好像已经发过了)。

====

=IF(COUNTIF(A2:J2,0),0,"")&SUM(LOOKUP(SMALL(IF(A2:BR2<>"",COLUMN(A2:BR2),71),COLUMN(A2:G2)),COLUMN(A2:BR2),A2:BR2)*10^(7-COLUMN(A2:G2)))

QUOTE:

格式考虑不周,当前面连续两个以上0时不能返回正确结果。

——山菊花

[此贴子已经被山菊花于2007-2-23 21:52:44编辑过]

TA的精华主题

TA的得分主题

发表于 2007-2-23 20:43 | 显示全部楼层

答案已发送到山版主的邮箱中………

我的公式没有存底!!

====

=CONCATENATE(CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2,Z2,AA2,AB2,AC2,AD2),CONCATENATE(AE2,AF2,AG2,AH2,AI2,AJ2,AK2,AL2,AM2,AN2,AO2,AP2,AQ2,AR2,AS2,AT2,AU2,AV2,AW2,AX2,AY2,AZ2,BA2,BB2,BC2,BD2,BE2,BF2,BG2),CONCATENATE(BH2,BI2,BJ2,BK2,BL2,BM2,BN2,BO2,BP2,BQ2,BR2))

[此贴子已经被山菊花于2007-2-23 21:54:10编辑过]

TA的精华主题

TA的得分主题

发表于 2007-2-23 23:10 | 显示全部楼层

答案已发送给山版主

请评分........

====

=MAX(A2:J2)&MAX(K2:T2)&MAX(U2:AD2)&MAX(AE2:AN2)&MAX(AO2:AX2)&MAX(AY2:BH2)&MAX(BI2:BR2)

=SUM(A2:J2)&SUM(K2:T2)&SUM(U2:AD2)&SUM(AE2:AN2)&SUM(AO2:AX2)&SUM(AY2:BH2)&SUM(BI2:BR2)

[此贴子已经被山菊花于2007-2-24 0:19:43编辑过]

TA的精华主题

TA的得分主题

发表于 2007-2-25 11:50 | 显示全部楼层

根据版主吩咐占个位!起立、敬礼!!

还不到40字节吗?

====

=SUBTOTAL(4,A2:J2)&SUBTOTAL(4,K2:T2)&SUBTOTAL(4,U2:AD2)&SUBTOTAL(4,AE2:AN2)&SUBTOTAL(4,AO2:AX2)&SUBTOTAL(4,AY2:BH2)&SUBTOTAL(4,BI2:BR2)

[此贴子已经被山菊花于2007-2-25 22:27:49编辑过]

TA的精华主题

TA的得分主题

发表于 2007-2-27 22:37 | 显示全部楼层

都忘了我还给过答案,也忘了答案是什么,查了一下短信,贴上来。

呵呵,晚上刚好有空,看了一下竞赛题,给个用常量数组的答案:
既然要连成一个字符串,那结果就为文本格式
=TEXT(SUM(SUBTOTAL(9,OFFSET(A2,,{0;10;20;30;40;50;60},,10))*10^{6;5;4;3;2;1;0}),"0000000")

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-2-28 00:34 | 显示全部楼层

总结

本函数题简单,简单得就像做饺子。可是,做饺子也不简单,各人做的饺子自有各的做法、各的风味,不简单!不简单得就像本函数题。

1、扭扭捏捏,饺子终究做成了。

=A2&B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2&U2&V2&W2&X2&Y2&Z2&AA2&AB2&AC2&AD2&AE2&AF2&AG2&AH2&AI2&AJ2&AK2&AL2&AM2&AN2&AO2&AP2&AQ2&AR2&AS2&AT2&AU2&AV2&AW2&AX2&AY2&AZ2&BA2&BB2&BC2&BD2&BE2&BF2&BG2&BH2&BI2&BJ2&BK2&BL2&BM2&BN2&BO2&BP2&BQ2&BR2

有个朋友还分段加个TRIM()函数,因为没数字的单元格都是空单元格,所以变得多余了。

2、到厨房找个模具,做饺子像玩游戏,也有乐趣。

=SUM(A2:J2)&SUM(K2:T2)&SUM(U2:AD2)&SUM(AE2:AN2)&SUM(AO2:AX2)&SUM(AY2:BH2)&SUM(BI2:BR2)

=MAX(A2:J2)&MAX(K2:T2)&MAX(U2:AD2)&MAX(AE2:AN2)&MAX(AO2:AX2)&MAX(AY2:BH2)&MAX(BI2:BR2)

=LARGE(A2:J2,1)&LARGE(K2:T2,1)&LARGE(U2:AD2,1)&LARGE(AE2:AN2,1)&LARGE(AO2:AX2,1)&LARGE(AY2:BH2,1)&LARGE(BI2:BR2,1)

=CONCATENATE(LOOKUP(10,A2:J2),LOOKUP(10,K2:T2),LOOKUP(10,U2:AD2),LOOKUP(10,AE2:AN2),LOOKUP(10,AO2:AX2),LOOKUP(10,AY2:BH2),LOOKUP(10,BI2:BR2))

=SUBTOTAL(4,A2:J2)&SUBTOTAL(4,K2:T2)&SUBTOTAL(4,U2:AD2)&SUBTOTAL(4,AE2:AN2)&SUBTOTAL(4,AO2:AX2)&SUBTOTAL(4,AY2:BH2)&SUBTOTAL(4,BI2:BR2)

把70个单元格分成7段,每段有且只有一个数字,这是本题的特点。找到这个规律,用SUM()、MAX()、SUBTOTAL()等函数得到各段的数字,连接起来便是结果。

3、谁家姑娘手儿巧,做的饺子美,看看都是一种享受。

{=TEXT(SUM(SUBTOTAL(4,OFFSET(A2,,(ROW($1:$7)-1)*10,,10))*10^(7-ROW($1:$7))),"0000000")}

{=RIGHT(TEXT(SUM(SUMIF(OFFSET(A2,,(COLUMN($A:$G)-1)*10,1,10),">=0")*10^-COLUMN($A:$G)),"0.0000000"),7)}

这个公式的基础是把三维引用运用到公式,根据70个单元格平均分成7段的规律,用OFFSET()把一行数据分成7段进行引用,作SUBTOTAL()或SUMIF()的参数。

SUBTOTAL()的结果是返回各段中的数字,如:{9;6;8;1;3;6;2},把这些数字连接成字符串,经典做法是乘以10的不同次幂,再把结果相加:

10^(7-ROW($1:$7))即是{1000000;100000;10000;1000;100;10;1}

SUM({9;6;8;1;3;6;2}*{1000000;100000;10000;1000;100;10;1})

=9000000+600000+80000+1000+300+60+2

=9681362

{=TEXT(SUM((N(OFFSET(A2,,SMALL(IF(A2:BR2<>"",COLUMN(A2:BR2)),ROW($1:$7))-1,)))*(10^(7-ROW($1:$7)))),"0000000")}

{=TEXT(SUM(LOOKUP(SMALL(IF(A2:BR2<>"",COLUMN(A2:BR2)),ROW($1:$7)),COLUMN(A2:BR2),A2:BR2)*10^(7-ROW($1:$7))),"0000000")}

{=TEXT(SUM(N(INDIRECT("r"&ROW()&"c"&SMALL(IF(A2:BR2<>"",COLUMN(A2:BR2)),COLUMN($A$2:$G$2)),))*TRANSPOSE(10^(7-ROW($1:$7)))),"0000000")}

不管数字在哪个段,只要存在数字,先返回数字所在的列号,根据列号,用OFFSET()返回数值,是另一个思路,也值得学习。

=TEXT(MMULT(SUBTOTAL(9,OFFSET($A$2:$J$2,ROW($A$2:$A$14)-2,(COLUMN($A$2:$G$2)-1)*10)),10^(7-ROW($1:$7))),"0000000")

=TEXT(SUM(MMULT(N(OFFSET($A2,,TRANSPOSE(ROW($1:$10))+(ROW($1:$7)-1)*10-1,,)),ROW($1:$10)^0)*10^(7-ROW($1:$7))),"0000000")

MMULT()在这题中有笨重之嫌,但总是一个思路,研究它还是有益的。

{=TEXT(SUM(MOD(SMALL(IF(A2:BR2<>"",COLUMN(A2:BR2)),ROW($1:$7))-1,10)*10^(7-ROW($1:$7))),"0000000")}

{=TEXT(SUM((A2:BR2<>"")*MOD(COLUMN(A2:BR2)-1,10)*(10^(6-INT((COLUMN(A2:BR2)-1)/10)))),"0000000")}

单元格中的数字等于MOD(COLUMN(A:BR)-1,抓住这一规律,使公式显得独特而情趣盎然。

4、传说中的饺子就在你我眼前。

{=RIGHT("0000000"&SUM(A2:BR2*10^(6-INT((COLUMN(A2:BR2)-1)/10))),7)}

=TEXT(SUMPRODUCT(A2:BR2*10^INT((71-COLUMN(A2:BR2))/10)),REPT(0,7))

=MID(SUMPRODUCT(A2:BR2,10^(-INT(COLUMN(A2:BR2)/10+0.9)))&"000000000",3,7)

70个单元格平均分成7段,每段的10个数字(其中9个空单元格当0计算)乘以10的不同次幂,把70个结果求和。

5、老师啊,你要是再端点醋来,该多好啊。

结果要求以7位文本显示,多数人想到用TEXT(),包括我,当然,用它没错。有点意外,这里还有新鲜的花样给你带来好心情,RIGHT()、MID()本是十分平常的函数,平常得就像陈醋,但今天,它让你回味无穷。

[此贴子已经被作者于2007-2-28 8:36:16编辑过]

点评

55 Chars: =RIGHT(10^7+SUM(A2:BR2*10^INT((70-COLUMN(A:BR))/10)),7)  发表于 2014-4-2 20:06

TA的精华主题

TA的得分主题

发表于 2007-2-28 07:48 | 显示全部楼层

我也跟一个不用数组且较短的公式:

=TEXT(SUMPRODUCT((A2:BR2)*10^(CEILING((71-COLUMN(A:BR))/10,1)))/10,"0000000")

TA的精华主题

TA的得分主题

发表于 2007-3-2 10:12 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2007-3-2 15:05 | 显示全部楼层

哎呀,回家过年了,竞赛已经结束了,呵呵呵,这期就算了。

TA的精华主题

TA的得分主题

发表于 2007-3-2 17:06 | 显示全部楼层

占位晚了  不知道还能不能得分                                                             

=CONCATENATE(SUM(A2:J2),SUM(K2:T2),SUM(U2:AD2),SUM(AE2:AN2),SUM(AO2:AX2),SUM(AY2:BI2),SUM(BJ2:BR2))

[此贴子已经被山菊花于2007-3-28 17:58:00编辑过]
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-10-19 20:43 , Processed in 0.081320 second(s), 18 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表