|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
如果A5:A8, B5:B8也需要公式获取的话可以用以下:
A5输入数组公式:
=IFERROR(YEAR(INDEX(F:F,1/MOD(SMALL(IF((MATCH($D$14:$D$26&YEAR($F$14:$F$26)&MONTH($F$14:$F$26),$D$14:$D$26&YEAR($F$14:$F$26)&MONTH($F$14:$F$26),)=ROW($14:$26)-13)*($D$14:$D$26=$B$2),YEAR($F$14:$F$26)*10^5+MONTH($F$14:$F$26)*10^2+1/ROW($F$14:$F$26),9^9),ROW(A1)),1))),"")
B5输入数组公式:
=IFERROR(MONTH(INDEX(F:F,1/MOD(SMALL(IF((MATCH($D$14:$D$26&YEAR($F$14:$F$26)&MONTH($F$14:$F$26),$D$14:$D$26&YEAR($F$14:$F$26)&MONTH($F$14:$F$26),)=ROW($14:$26)-13)*($D$14:$D$26=$B$2),YEAR($F$14:$F$26)*10^5+MONTH($F$14:$F$26)*10^2+1/ROW($F$14:$F$26),9^9),ROW(A1)),1))),"")
C5,D5参照2楼老师
|
|