THE LATEST INFORMATION
| 资讯中心 |
若是你要将记实完全的告假记实表,转换成摘要资讯,而不想利用关键阐发东西,则利用阵列公式应当是不错的选择。重点申明相干做法:
在记实表中若要对齐日期,而不要呈现例如:2008/1/4、2008/12/4、2008/10/10等字数纷歧样多的对齐题目,则在日期贮存格设定格局为自订:yyyy/mm/dd。
在礼拜几的栏位,其公式为B3贮存格为「=A3」,再设定其格局为「礼拜X」。
告假假别和时数则自行输入。
全部记实表应依日期挨次,由小到年夜记实。
在F1贮存格中若输入学年度,则管帐算由该年度的8/1到次年的7/31之间的各类假别时数。
在F3贮存格中的公式为:
{=SUM(IF(LEFT($C$3:$C$60,2)=E3,IF($A$3:$A$60>=DATE($F$1+1911,8,1),IF($A$3:$A$60<=DATE($F$1+1912,7,31),VALUE(MID($C$3:$C$60,3,1)),),),0))}
再複製到F4:F6。
在G3贮存格中要将时数换算成天数,则将8小时换算为一天,
在G3贮存格中的公式为:
=INT(F3/8) & "天" & MOD(F3,8) & "小时"
再複製到G4:G6。
接着要成立一个Table,要能主动列出该学年度各类假此外日期和时数。
在I3贮存格中的公式为:
{=IF(ISERROR(SMALL(IF(LEFT($C$3:$C$60,2)=I$2,IF($A$3:$A$60>=DATE($J$1+1911,8,1),IF($A$3:$A$60<=DATE($J$1+1912,7,31),$A$3:$A$60,""))),ROW(1:1))),"",SMALL(IF(LEFT($C$3:$C$60,2)=I$2,IF($A$3:$A$60>=DATE($J$1+1911,8,1),IF($A$3:$A$60<=DATE($J$1+1912,7,31),$A$3:$A$60,""))),ROW(1:1)))}
此中ISERROR用于判定有公式的贮存格,但却没有日期值的时辰,避免显示#Num!。
因为要挑出某个区间的日期,所以借用DATE函数,先将学年转换成西洋年(+1911),再设定日期。
利用阵列时,则透过IF(IF(IF…))体例到达将三个前提履行AND的功能。
利用SMALL函数和ROW(1:1)(複製后会酿成ROW(2:2), ROW(3:3)…),将遴选出来的日期阵列,第一个贮存格显示最小值(日期),下一个贮存格显示第2最小值(日期),余类推。
将贮存格往下複製。
接着操纵所显示的日期,操纵查表法将时数显示出来。
在J3贮存格中的公式为:
=IF(ISERROR(MID(VLOOKUP(I3,$A$3:$C$60,3),3,1)),"",MID(VLOOKUP(I3,$A$3:$C$60,3),3,1)&"小时")
将贮存格往下複製。
将I3複製到K3, M3, O3。
将J3複製到L3, N3, P3。
操纵阵列公式,可以主动发生摘要表,而且可以查询各学年,还可以列出所有告假的日期和时候。
创作者先容 vincent 北京拓展训练基地