[开_164]转换列号(已总结)

cnmlgb9998

这是套用了 PQ区的 “渡渡"老师的代码。


非我原创。

fs_lxf

1. =LET(f,LAMBDA(f,x,IF(x,f(f,INT((x-1)/26))&CHAR(MOD(x-1,26)+65),"")),f(f,B2))

复制代码

1. =LET(f,LAMBDA(x,IF(x,f(INT((x-1)/26))&CHAR(MOD(x-1,26)+65),"")),f(B2))

复制代码

补充内容 (2024-10-5 21:33):
=LET(f,LAMBDA(f,x,IF(x,f(f,INT((x-1)/26))&CHAR(MOD(x-1,26)+65),)),f(f,B2))

补充内容 (2024-10-5 21:44):

1. =LET(f,LAMBDA(f,x,IF(x,f(f,INT((x-1)/26))&CHAR(MOD(x-1,26)+65),)),f(f,B2))

复制代码

秋秋wang

=BYROW(B2:B7,LAMBDA(x,TEXTBEFORE(ADDRESS(1,x,2),"$")))

inrever

1. =LET(s,ROW($1:$99),a,TOCOL(SCAN(B2,s,LAMBDA(x,y,IF(x<=26,\,INT(x/26)))),3),b,DROP(REDUCE(0,VSTACK(B2,a)-VSTACK(a,0)*26,LAMBDA(m,n,VSTACK(m,IF(n,IF(TAKE(m,-1)=26,IF(n-1,n-1,26),n),26)))),1),CONCAT(CHOOSEROWS(CHAR(IFERROR(IF(AVERAGE(b)=26,DROP(b,1),b),B2)+64),100-s)))

复制代码

edwin11891

不用新函数循环起来实在费力，还是跑老式绿皮火车：

1. =LET(t,SCAN(B2,SEQUENCE(10),LAMBDA(x,y,LET(Smod,IF(FLOOR(x/26,1)=(x/26),26,MOD(x,26)),(x-Smod)/26))),s,TOCOL(IF(t<0,1/0,t),3),Ra,VSTACK(B2,s),a,IF(TAKE(Ra,-1)=0,DROP(Ra,-1),Ra),b,IF(FLOOR(a/26,1)=(a/26),26,MOD(a,26)),c,ROWS(b),d,CONCAT(CHAR(64+INDEX(b,c-SEQUENCE(c)+1))),d)

复制代码

方法2稍短：

1. =LET(fx,LAMBDA(x,IF(FLOOR(x/26,1)=(x/26),26,MOD(x,26))),t,REDUCE(B2,SEQUENCE(10),LAMBDA(x,y,VSTACK(B2,(x-fx(x))/26))),s,TOCOL(IF(t>0,t,1/0),3),n,CHAR(64+fx(s)),Res,CONCAT(INDEX(n,ROWS(n)-SEQUENCE(ROWS(n))+1)),Res)

复制代码

风中的我脸

1. f = LAMBDA(a, LET(s, a - 1, IF(s < 0, "", f(s / 26) & CHAR(MOD(s, 26) + 65))));

复制代码

ww87725244

1. =LET(f,LAMBDA(f,x,y,IF(x<1,y,f(f,(x-1)/26,CHAR(65+MOD(x-1,26))&y))),f(f,B2,))

复制代码

theking_wsz

..........

我走我流

1. =DROP(REDUCE(IF({1,0},B2,""),ROW($1:$9),LAMBDA(x,y,IF(@x,IF(MOD(@x,26),HSTACK(INT(@x/26),BASE(9+MOD(@x,26),36)&TAKE(x,,-1)),HSTACK(@x/26-1,"Z"&TAKE(x,,-1))),x))),,1)

复制代码

shixv

365版本 =LET(k,INT(LOG(25*B2+1,26)),n,k-SEQUENCE(k),CONCAT(CHAR(INT(MOD((B2-(26^k-1)/25)/26^n,26)+65))))

非LET低版本 =CONCAT(CHAR(INT(MOD((B2-(26^INT(LOG(25*B2+1,26))-1)/25)/26^(INT(LOG(25*B2+1,26))-ROW(OFFSET(A$1,,,INT(LOG(25*B2+1,26))))),26)+65)))


2003版本 =IF(1<=LOG(25*B2+1,26),CHAR(INT(MOD((B2-(26^INT(LOG(25*B2+1,26))-1)/25)/26^(INT(LOG(25*B2+1,26))-1),26)+65)),"")&IF(2<=LOG(25*B2+1,26),CHAR(INT(MOD((B2-(26^INT(LOG(25*B2+1,26))-2)/25)/26^(INT(LOG(25*B2+1,26))-2),26)+65)),"")&IF(3<=LOG(25*B2+1,26),CHAR(INT(MOD((B2-(26^INT(LOG(25*B2+1,26))-3)/25)/26^(INT(LOG(25*B2+1,26))-3),26)+65)),"")&IF(4<=LOG(25*B2+1,26),CHAR(INT(MOD((B2-(26^INT(LOG(25*B2+1,26))-4)/25)/26^(INT(LOG(25*B2+1,26))-4),26)+65)),"")&IF(5<=LOG(25*B2+1,26),CHAR(INT(MOD((B2-(26^INT(LOG(25*B2+1,26))-5)/25)/26^(INT(LOG(25*B2+1,26))-5),26)+65)),"")&IF(6<=LOG(25*B2+1,26),CHAR(INT(MOD((B2-(26^INT(LOG(25*B2+1,26))-6)/25)/26^(INT(LOG(25*B2+1,26))-6),26)+65)),"")&IF(7<=LOG(25*B2+1,26),CHAR(INT(MOD((B2-(26^INT(LOG(25*B2+1,26))-7)/25)/26^(INT(LOG(25*B2+1,26))-7),26)+65)),"")&IF(8<=LOG(25*B2+1,26),CHAR(INT(MOD((B2-(26^INT(LOG(25*B2+1,26))-8)/25)/26^(INT(LOG(25*B2+1,26))-8),26)+65)),"")