网页资讯视频图片知道文库贴吧地图采购
进入贴吧全吧搜索
excel吧 关注：281,452贴子：1,550,934
  • 32回复贴，共1页

[函数题第二发]生成住户的房号

预设183字符




2025-08-05 04:41:37
广告
不感兴趣
开通SVIP免广告
=offset(a$2,int(sum(countif(a$1:a1,"a$2:a$6*")/(b$2:b$6*c$2:c$6),0)&"座-"roundup(((now(1)-int(sum((countif(a$1:a1,"a$2:a$6*")/(b$2:b$6*c$2:c$6))*c$2：c$6)))/(countif(a$1:a1,"a$2:a$6*")/(b$2:b$6*c$2:c$6))*c$2：c$6))&0&mod(同上)等我想个短的


546字符，普通公式，太长了。。。
E2 =INDEX(A:A,SUM(1*(ROW(A1)>MMULT(1*(COLUMN($A:$E)<=ROW($1:$5)),B$2:B$6*C$2:C$6)))+2)&"座-"&(INT((ROW(A1)-IFERROR(LOOKUP(ROW(A1)-1,MMULT(1*(COLUMN($A:$E)<=ROW($1:$5)),B$2:B$6*C$2:C$6)),0)-1)/(INDEX(C:C,SUM(1*(ROW(A1)>MMULT(1*(COLUMN($A:$E)<=ROW($1:$5)),B$2:B$6*C$2:C$6)))+2)))+1)&"0" & (MOD(ROW(A1)-IFERROR(LOOKUP(ROW(A1)-1,MMULT(1*(COLUMN($A:$E)<=ROW($1:$5)),B$2:B$6*C$2:C$6)),0),-INDEX(C:C,SUM(1*(ROW(A1)>MMULT(1*(COLUMN($A:$E)<=ROW($1:$5)),B$2:B$6*C$2:C$6)))+2))+INDEX(C:C,SUM(1*(ROW(A1)>MMULT(1*(COLUMN($A:$E)<=ROW($1:$5)),B$2:B$6*C$2:C$6)))+2))
下拉


完全不懂


2013 2016excel的快速填充就能很快实现，也不用那么麻烦


抓狂又是函数。。。。有话好好说能用VBA吗？答案肯定是不行的，我来暖贴


我觉得应该短不了多少


2025-08-05 04:35:37
广告
不感兴趣
开通SVIP免广告
这反人类，还是vba吧


=IFERROR(OFFSET(A$2,SUM(INT(COUNTIF(F$1:F3,A$2:A$6&"*")/(B$2:B$6*C$2:C$6))),)&"座-"&((INT(1+(ROW(A3)-1-SUM(INT(COUNTIF(F$1:F3,A$2:A$6&"*")/(B$2:B$6*C$2:C$6))*B$2:B$6*C$2:C$6))/OFFSET(A$2,SUM(INT(COUNTIF(F$1:F3,A$2:A$6&"*")/(B$2:B$6*C$2:C$6))),2))))&0&(MOD(ROW(A3)-1,OFFSET(A$2,SUM(INT(COUNTIF(F$1:F3,A$2:A$6&"*")/(B$2:B$6*C$2:C$6))),2))+1),"")


=SMALL(ROW(INDIRECT("1:"&OFFSET(B$2,SUM(INT(COUNTIF(F$1:F7,A$2:A$6&"*")/(B$2:B$6*C$2:C$6))),)))*100+COLUMN(INDIRECT("c1:c"&OFFSET(C$2,SUM(INT(COUNTIF(F$1:F7,A$2:A$6&"*")/(B$2:B$6*C$2:C$6))),),)),ROW(INDIRECT("a"&(ROW(A7)-SUM(INT(COUNTIF(F$1:F7,A$2:A$6&"*")/(B$2:B$6*C$2:C$6))*B$2:B$6*C$2:C$6)))))


扫二维码下载贴吧客户端

下载贴吧APP
看高清直播、视频！
  • 32回复贴，共1页
分享到: