将A列区间时间按年度填入BCD列

sheen328 · 发表于 2024-7-25 16:06

将A列区间时间按年度填入BCD列

part98 · 发表于 2024-7-25 16:38

=IF(AND(MID($A2,1,6)<=LEFT(B$1,4)&12,MID($A2,8,6)>=LEFT(B$1,4)&"01"),MAX(MID($A2,1,6),LEFT(B$1,4)&"01")&"-"&MIN(MID($A2,8,6),LEFT(B$1,4)&12),"")
右拉，下拉

hugeinfo · 发表于 2024-7-25 16:49

=LET(a,DATE(LEFT(TAKE(TEXTSPLIT($A2,"-",),,1),4),RIGHT(TAKE(TEXTSPLIT($A2,"-",),,1),2)+SEQUENCE(SUM(LEFT(TEXTSPLIT($A2,,"-"),4)*{-1;1})*12+SUM(RIGHT(TEXTSPLIT($A2,,"-"),2)*{-1;1})+1,,0,1),1),IFERROR(TEXTJOIN("-",,TEXT(TAKE(FILTER(a,YEAR(a)=--SUBSTITUTE(B$1,"年度","")),{1,-1}),"yyyymm")),""))

quqiyuan · 发表于 2024-7-25 17:15

=IF(LEFT($A2,4)=LEFT(B$1,4),IF(MID($A2,8,4)>LEFT(B$1,4),LEFT($A2,7)&LEFT($A2,4)&12,LEFT($A2,7)&RIGHT($A2,6)),IF(LEFT($A2,4)<LEFT(B$1,4),IF(MID($A2,8,4)>LEFT(B$1,4),LEFT(B$1,4)&"01-"&LEFT(B$1,4)&12,IF(MID($A2,8,4)<LEFT(B$1,4),"",LEFT(B$1,4)&"01-"&RIGHT($A2,6))),""))

sucx007 · 发表于 2024-7-25 17:28

=BYCOL(SEQUENCE(12,,202001)+COLUMN(A:C)*100,LAMBDA(x,LET(a,VDB(,,--RIGHT(A2,6),--LEFT(A2,6),x),b,FILTER(x,ISNUMBER(a)),IFERROR(MIN(b)&-MAX(b),""))))

1786024734 · 发表于 2024-7-26 13:21

本帖最后由 1786024734 于 2024-7-26 13:27 编辑

=IF(PRODUCT(LEFT(B$1,4)-MID($A2,{1,8},4))>0,"",MAX(LEFT($A2,6),LEFT(B$1,4)&"01")&MAX(-RIGHT($A2,6),-(LEFT(B$1,4)&12)))
=IF(PRODUCT(LEFT(B$1,4)-MID($A2,{1,8},4))>0,"",CONCAT(SMALL(IF({1;0},--MID($A2,{1,8},6),LEFT(B$1,4)/1%+{1,12}),{2,3})&{"-",""}))
=IFERROR(CONCAT(AGGREGATE({15,14},6,VLOOKUP(ROW(INDIRECT(SUBSTITUTE($A2,"-",":"))),LEFT(B$1,4)/1%+ROW($1:$12),1,),1)&{"-",""}),"")

复制代码

sheen328 · 发表于 2024-7-27 15:34

1786024734 发表于 2024-7-26 13:21

好牛，我看了几小时才看懂思路

1786024734 · 发表于 2024-7-27 21:37

sheen328 发表于 2024-7-27 15:34
好牛，我看了几小时才看懂思路

思路是灵魂，能看懂就Get到了

		自动登录	找回密码
密码			免费注册

[已解决] 将A列区间时间按年度填入BCD列

评分

评分