สูตร excel : การดำเนินการระหว่างตัวเลขและTime data

สูตร excel วันนี้อยากจะนำเสนอข้อผิดพลาดที่ผมก็ยังไม่แน่ใจนักว่าท่านผู้ใช้ สูตร excel จะเผลอเหมือนผมหรือไม่นะครับ เอาเป็นว่าอยากยกให้เป็นข้อควรระวังก็แล้วกันนะครับ สมมุติท่านผู้อ่านได้รับข้อมูลเป็นจำนวนเวลาเข้ามาก็แล้วกันนะครับ เช่น จำนวนเวลาที่ทำโอที เวลาที่ใช้ทดสอบผลิตภัณฑ์ หรือใช้ดำเนินการกับข้อมูลดิบที่จะนำไปคำนวณ kpi ค่า Avaliabilty Rate หรือ oee เป็นต้นครับ ซึ่งหากได้รับข้อมูลใน excel จะอยู่ในรูปแบบดังนี้ครับ

h.m ซึ่งผมว่ามันเข้าใจในภาษามนุษย์เราใช่มั้ยครับเช่น 4.53 ก็หมายถึง 4 ชั่วโมง 53 นาที เอาหล่ะครับหากเราต้องการแปลงตัวเลขนี้ให้เป็นข้อมูลประเภท Time จะทำอย่างไร เรามาดูกันขั้นตอนกันเลยครับ

สูตร excel

1. ใช้สูตร excel : Floor เพื่อปัดเป็นจำนวนเต็มจะได้เป็น =FLOOR(4.53,1) ผลที่ได้คือจำนวนชั่วโมงเท่ากับ 4
2. นำผลที่ได้จากการใช้สูตร excel : Floor ลบออกจากตัวเลขตั้งต้นและคูณด้วย 100 จะได้เศษนาทีออกมาครับ

ภาพที่ 1 ผลการใช้สูตร excel

เขียน excel vba

1. ใช้ฟังก์ชัน Int เพื่อแปลงตัวเลขเป็นจำนวนเต็ม เขียนได้ดังนี้ h = Int(4.53) ผลที่ได้คือ 4
2. นำผลที่ได้จากการใช้ฟังก์ชัน Int ลบออกจากตัวเลขตั้งต้นและคูณด้วย 100 จะได้เศษนาทีออกมาครับ

จะเห็นว่าหากเราต้องการแปลงตัวเลขเหล่านี้เป็นจำนวนนาทีก็ไม่ยากใช่มั้ยครับสามารถคำนวณได้ดังนี้

time_minute = h*60 + m

จากขั้นตอนที่กล่าวมาหากเราปรับเป็นฟังก์ชันไว้ใช้งานก็สามารถทำได้ ดูที่นี่ครับ สมมุติผมตั้งชื่อฟังก์ชันว่า NumToMinute ซึ่งมีรูปแบบของฟังก์ชันดังนี้

Function NumToMinute(ByVal num As Double) As Integer
Dim h As Integer
h = Int(num)
NumToMinute = h * 60 + (num - h) * 100
End Function

ผลการเรียกใช้ฟังก์ชัน NumToMinute แสดงได้ดังภาพที่ 2

ภาพที่ 2 การเรียกใช้สูตร excel : NumToMinute

มาถึงตรงนี้ท่านผู้อ่านคงเห็นช่องทางในการต่อยอดนะครับ เราสามารถดำเนินการกับจำนวนเวลานี้ได้โดยง่าย โดยทั่วไปเราก็นิยมดำเนินการกับเวลาสองลักษณะครับคือ ผลรวมของเวลา หรือผลต่างของเวลา เช่นในการคำนวณค่า oee จำนวนเวลาที่เครื่องจักรเกิด BreakDown (ใช้ผลรวม) หรือระยะเวลาการซ่อมเครื่องจักรสองเครื่องต่างกันเท่าไหร่(ผลต่าง) เรามาดูกันครับว่าเราจะสร้างสูตร Excel ได้อย่างไร

ผลรวมของเวลา

Function AddTime(tm1 As Integer, tm2 As Integer) As Integer
AddTime = tm1 + tm2
End Function

ผลต่างของเวลา

Function MinusTime(tm1 As Integer, tm2 As Integer) As Integer
If tm1 >= tm2 Then
MinusTime = tm1 - tm2
Else
MinusTime = tm2 - tm1
End If
End Function

ผลการใช้สูตร Excel ทั้งสองแสดงได้ดังภาพที่ 3

ภาพที่ 3 การใช้สูตร Excel ร่วม

ในบางกรณีเราต้องการแยกองค์ประกอบของจำนวนนาทีให้อยู่ในรูปแบบของจำนวนชั่วโมงและจำนวนนาที (h.m) เราสามารถเขียนฟังก์ชันได้ดังนี้

Function MinuteToNum(ByVal tm As Integer) As Double
Dim h As Integer
Dim m As Integer
m = tm Mod 60
h = Int(tm / 60)
MinuteToNum = h + m / 100#
End Function

ดังนั้นหลังจากดำเนินการกับเวลาเสร็จแล้วเราสามารถแปลงจำนวนเวลาดังกล่าวกลับมาอยู่ในรูปแบบที่มนุษย์เข้าใจได้โดยง่ายโดยใช้สูตร Excel : MinuteToNum

หวังว่าท่านผู้อ่านคงได้ประโยชน์จากบทความนี้และนำไปปรับใช้กับปัญหาที่เจอได้นะครับ เพราะว่าข้อมูลที่เราได้จากผู้ใช้มีหลากหลาย เราจึงต้องประยุกต์สูตร Excel ที่มีอยู่ใน Excel และพัฒนาสูตร Excel ให้เกิดประโยชน์สูงสุดครับ เราคงได้เห็นประโยชน์ของฟังก์ชันที่ผมแนะนำไปในตัวอย่าง kpi ต่อๆไปนะครับ สวัสดีครับ