excel 刪減技巧 @ rdfvjur的部落格

標題:

excel 刪減技巧

發問:

舉例我只看小時... 顯示更多舉例我只看小時分鐘想要留下每十分鐘的數據列中間的數據都不要小時:分鐘:秒:毫秒 (18:22:13:156) (18:23:13:187) (18:24:13:218) (18:25:13:250) (18:26:13:296) (18:27:13:328) (18:28:13:359) (18:29:13:406) (18:30:13:437) (18:31:13:484) (18:32:13:515) (18:33:13:546) (18:34:13:578) (18:35:13:609) (18:36:13:640) (18:37:13:703) (18:38:13:734) (18:39:13:765) (18:40:13:796) (18:41:13:843) (18:42:13:875) (18:43:13:937) (18:44:14:000) (18:45:14:031) (18:46:14:062) (18:47:14:093) (18:48:14:140) (18:49:14:171) (18:50:14:203) (18:51:14:234) (18:52:14:281) 所以就會變成 (18:22:13:156) (18:32:13:515) (18:42:13:875) (18:52:14:281) 因為數據繁多請問各位前輩有更方便的刪減法嗎?

最佳解答:

originalsub-stringhmidsampledFinal(18:22:13:156)18:22182218:22(18:22:13:156)(18:22:13:156)(18:23:13:187)18:23183218:32(18:32:13:515)(18:32:13:515)(18:24:13:218)18:24184218:42(18:42:13:875)(18:42:13:875)(18:25:13:250)18:25185218:52(18:52:14:281)(18:52:14:281)(18:26:13:296)18:2619219:02(18:52:14:281)(18:27:13:328)18:27191219:12(18:52:14:281)(18:28:13:359)18:28192219:22(18:52:14:281)(18:29:13:406)18:29193219:32(18:52:14:281)(18:30:13:437)18:30194219:42(18:52:14:281)(18:31:13:484)18:31195219:52(18:52:14:281)(18:32:13:515)18:3220220:02(18:52:14:281)(18:33:13:546)18:33201220:12(18:52:14:281)(18:34:13:578)18:34202220:22(18:52:14:281)(18:35:13:609)18:35(18:36:13:640)18:36(18:37:13:703)18:37(18:38:13:734)18:38(18:39:13:765)18:39(18:40:13:796)18:40(18:41:13:843)18:41(18:42:13:875)18:42(18:43:13:937)18:43(18:44:14:000)18:44(18:45:14:031)18:45(18:46:14:062)18:46(18:47:14:093)18:47(18:48:14:140)18:48(18:49:14:171)18:49(18:50:14:203)18:50(18:51:14:234)18:51(18:52:14:281)18:52 B2: =MID(A2,2,5) 往下複製C2: = value(mid(A2,2,2)D2: = value(mid(A2,5,2)C3: = IF(D3=50,D2-50,D2+10) 往下複製E2: = TEXT(C2,"00")&":"&TEXT(D2,"00") 往下複製F2: = LOOKUP(E2,B2:B32, A2:A32) 往下複製G2: = F2G3: = IF(F3<>F2,F3,"") 往下複製 2012-12-16 00:00:43 補充： F2: = LOOKUP(E2,B2:B$32, A2:A$32) 往下複製結束列改用絕對位置, 在往下複製時才不會參考到不存在的資料格.

其他解答: