之前有過一篇 ,
第一反應就是利用(CHOOSE,INT,HOUR)函數來進行加工。
在 Excel 中有一個關於時間的清單(參考下圖左),如何根據以下規則調整(參考下圖右)?
當「分」為:
若為00~14者要捨去
若為15~29要變為30
若為30~44者要變為30
若為45~59者要進位至小時
【公式設計與解析】
單元格C2:=CHOOSE(INT(MINUTE(A2)/15)+1,TIME(HOUR(A2),0,0),
TIME(HOUR(A2),30,0),TIME(HOUR(A2),30,0),TIME(HOUR(A2),60,0))
複製單元格C2,貼至單元格C2:C17。
(1) INT(MINUTE(A2)/15)+1
利用 MINUTE 函數取出單元格A2中的「分」數,再利用 INT 函數求取單元格除以 15 的商數,其結果可能為 0, 1, 2, 3。因為要使用在 CHOOSE 函數中,所以將公式再加 1,使結果可能為 1, 2, 3, 4。
(2) 根據第(1)式的傳回值對應執行以下的公式:
傳回1→TIME(HOUR(A2),0,0)
傳回2→TIME(HOUR(A2),30,0)
傳回3→TIME(HOUR(A2),30,0)
傳回4→TIME(HOUR(A2),60,0)
【拓展閱讀】
如果使用 FLOOR函數和 CEILING 函數,該如何處理?
【公式設計與解析】
單元格E2:
=CHOOSE(INT(MINUTE(A2)/15)+1,FLOOR(A2,1/24/2),CEILING(A2,1/24/2),FLOOR(A2,1/24/2),CEILING(A2,1/24/2))
複製單元格E2,貼至單元格E2:E17。
FLOOR(A2,1/24/2):單元格A2的時間無條件捨去至最接近的1/24/2的倍數,即30分鐘的倍數。
CEILING(A2,1/24/2):單元格A2的時間進位至1/24/2的倍數,即30分鐘的倍數。
【FLOOR 函數說明】
FLOOR 函數:將 number 無條件舍位至趨近於零,到最接近的基數倍數。
語法: FLOOR(number, significance)
Number:這是要舍位的數值。
Significance:這是要舍位的倍數。
【CEILING 函數說明】
CEILING 函數:傳回進位後的數字,背離於零,到最接近之指定基數的倍數。
語法: CEILING(number, significance)
Number:這是要舍位的數值。
Significance:這是要舍位的倍數。
閱讀更多 老徐漫談 的文章