网友想要按照下图中的 Excel 资料清单,补足持续分钟数罅漏的部门(下图左有底线位置即出缺漏),下图右红色文字即为补上的分钟数,该若何处置如许的题目?
【公式设计与解析】
1. 发生持续的分钟数
贮存格G3:=G2+1/24/60
由于在 Excel 中一天以「1」来暗示,所以每分钟暗示为「1/24/60」。
複製贮存格G3,贴至贮存格G3:G101。(假定要发生100分钟的资料)
2. 资料查表
贮存格H2:{=IFERROR(OFFSET($C$2,MATCH(ROUND(G2,5),
ROUND($B$2:$B$101,5),0)-1,0),"")}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel主动发生「{}」。
由于时候转换出来的数值太紧密,所以在利用 MATCH 函数时没法准确比对,是以要调剂一下,只取到小数点第5位。
(1) ROUND(G2,5)
将贮存格G2的数值取小数点5位。
(2) ROUND($B$2:$B$101,5)
在阵列公式中,将贮存格B2:B101的数值全数取小数点5位。
(3) MATCH(ROUND(G2,5),ROUND($B$2:$B$101,5),0)-1,0)
在贮存北京拓展公司格B2:B101中比对和贮存格G2不异者,传回其列号。
(4) OFFSET($C$2,第(3)式,0)
操纵 MATCH 函数传回的列号代入 OFFSET 函数,查询对应的贮存格内容。
(5) IFFERROR(OFFSET($C$2,第(3)式,0),"")
操纵 IFFERROR 函数,将传回毛病讯息的贮存格转换为空字串。(什么时候会呈现毛病呢?在原始资料清单中罅漏的秒数,将会查询不到任何资料,即会传回毛病讯息。)
同理:
贮存格I2:{=IFERROR(OFFSET($D$2,MATCH(ROUND(G2,5),
ROUND($B$2:$B$101,5),0)-1,0),"")}