发布时间:2017-04-26 作者:admin 点击量:
网友问到:在 Excel 中有一个学员受训的记实表,若何按照下图左的日期区间,转换至下图中各月的清单报表?
【公式设计与解析】
先拔取贮存格A1:C23,按 Ctrl+Shift+F3 键,勾选「顶端列」,界说名称:学员、起头、竣事。
贮存格E2:
{=IFERROR(OFFSET($A$1,SMALL(IF((MONTH(起头)<=COLUMN(A:A))*(MONTH
(竣事)>=COLUMN(A:A)),ROW(学员),""),ROW(1:1))-1,0,1,1),"")}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 主动输入「{}」。
(1) (MONTH(起头)<=COLUMN(A:A))*(MONTH(竣事)>=COLUMN(A:A))
前提一:(MONTH(起头)<=COLUMN(A:A))
判定起头日期的月份是不是小于或即是1月,COLUMN(A:A)向右複製时,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→ ... 。
修件二:(MONTH(竣事)>=COLUMN(A:A))
判定竣事日期的月份是不是年夜或即是1月。
两个前提之间的运算子『*』,相当于履行逻辑 AND 运算,而且会将 TRUE/FALSE 阵列转换为 1/0 阵列。
(2) IF(第(1)式,ROW(学员),"")
若是第(1)的前提成立,则传回成员的列号阵列,不然传回空缺(空字串)
(3) SMALL(第(2)式,ROW(1:1))
在 SMALL 函数中操纵第(2)式,找出传回的列号中最小值的第 1, 2, 3, ... 个。ROW(1:1)向下複製时,会发生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ ... 。
(4) OFFSET($A$1,第(3)式-1,0,1,1)
按照第(3)传回的列号代入 OFFSET 函数获得贮存格内容。(注重公式中的『-1』)
(5) IFERROR(第(4)式,"")
当公式传回毛病讯息时,操纵 IFERROR 函数使其显示空缺(空字串)。
创作者先容 vincent 北京拓展公司联系我们
contact us地址:北京市西红门理想商业街3号楼6层
电话:4000-288-501
点击图标在线留言,我们会及时回复