เราแสดงเวลาแบบ Realtime ใน excel ได้อย่างไร (How to show real time in excel)

การใช้ excel ในบทความวันนี้ ผมขอเสนอการแสดงเวลาแบบ real time ใน worksheet ต่างๆ ซึ่งจะอาศัยการเขียนด้วย vba โดยอาศัยสูตร excel ต่างๆ ซึ่งคิดว่าจะเป็นประโยชน์กับท่านผู้อ่านสำหรับนำไปประยุกต์การใช้ excel ในด้านต่างๆ เช่น การตัดสต๊อกสินค้าที่เวลาใดๆในแต่ละวัน การเขียนคำสั่งให้ excel ทำงานที่เวลาต่างๆเป็นต้น ซึ่งผมจะนำเสนอในบทความต่อๆไปครับ วันนี้เรามาดูตัวอย่างง่ายๆของการใช้ excel แสดงเวลาแบบ real time ในเซลล์ใดๆ โดยจะดักจับเหตุการณ์ Ontime สำหรับการสั่งให้ฟังก์ชันของ excel ปรับปรุงเวลาอยู่ตลอดเวลา โดยฟังก์ชัน Ontime จะมีรูปแบบของสูตร excel ดังนี้ครับ

.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

เมื่อ

EarliestTime คือค่าเวลาที่เราต้องการให้กระบวนการใดๆของเราทำงาน

Procedure คือกระบวนการใดๆที่เราต้องการให้ทำงานเมื่อถึงเวลาที่กำหนด

LatestTime คือค่าเวลาสุดท้ายที่เราต้องการให้กระบวนการใดๆทำงาน

Schedule คือ การกำหนดสำหรับการเรียกใช้เหตุการณ์ Ontime มีค่าเป็น true หากต้องการเรียกใช้เหตุการณ์ Ontime มีค่าเป็น false หากต้องการยกเลิก เหตุการณ์ Ontime

เอาหล่ะครับ หากผมต้องการให้แสดงเวลาแบบ real time ที่มีรูปแบบเป็น h:mm:ss ในเซลล์ B1 ของ worksheet ชื่อ Sheet1 เราสามารถเขียนกระบวนการทำงานนี้โดยใช้วางฟังก์ชันไว้ที่โมดูลใดๆได้ดังนี้ครับ

Sub mytime()

Worksheets(“Sheet1”).Cells(1,2).Value = Format(Now,”h:mm:ss”)

End Sub

เมื่อ Now คือสูตร excel ที่ใช้แสดงเวลาในปัจจุบัน

จากนั้นหากเราต้องการให้แสดงเวลาตั้งแต่ตอนเปิดไฟล์ excel เราต้องเรียกกระบวนการ mytime ตั้งแต่เกิดเหตุการณ์ เปิดไฟล์ excel ซึ่งจะถูกกำหนดในฟังก์ชัน Workbook_Open โดยเราจะเขียนโค้ดโปรแกรมในฟังก์ชัน Workbook_Open ซึ่งอยู่ในส่วนของ ThisWorkbook ได้ดังนี้

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("0:0:1"), "mytime"
End Sub

เมื่อ สูตร excel :TimeValue เป็นสูตรสำหรับการแปลงข้อความเป็นค่าเวลาในหน่วย ชั่วโมง :นาที: วินาที

การที่จะให้กระบวนการ mytime ทำงานทุกๆ 1 วินาที จะต้องปรับแก้กระบวนการ mytime ให้เรียกตัวเองอยู่เสมอ จึงต้องปรับแก้ไขดังนี้

Sub mytime()

Worksheets(“Sheet1”).Cells(1,2).Value = Format(Now,”h:mm:ss”)

Application.OnTime Now + TimeValue("0:0:1"), "mytime"

End Sub

สุดท้ายครับเราต้องยกเลิกการดักจับเหตุการณ์ Ontime ก่อนที่จะปิดไฟล์ excel ครับ ซึ่งเราจะเขียนโปรแกรมยกเลิกการดักจับเหตุการณ์ Ontime ในเหตุการณ์ก่อนปิดไฟล์ excel ซึ่งจะใช้ฟังก์ชัน Workbook_BeforeClose ซึ่งอยู่ในส่วนของ ThisWorkbook ได้ดังนี้

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Now + TimeValue("0:0:1"), "mytime", False
End Sub

เรามาดูผลการทำงานของกระบวนการแสดงผลเวลาแบบ real time ในการใช้ excel ของเรากันเลยครับ

ภาพที่ 1 ฟังก์ชัน excel ในส่วนของ ThisWorkbook

ภาพที่ 2 กระบวนการ mytime ในส่วนของ Module1

ภาพที่ 3 การแสดงเวลาแบบ Real Time ใน excel

หากท่านผู้อ่านดำเนินการมาถึงขั้นตอนนี้ เมื่อท่านทดลองเปิดไฟล์ excel ดังกล่าวขึ้นมาท่านจะพบเวลาในเซลล์ B1 แสดงแบบ Real Time ครับ ก็ดูเก๋ไปอีกแบบนะครับ หากเรานำไปประยุกต์ใช้กับงานอื่นๆก็คงจะไม่ยากมากนัก ก็เพราะตอนนี้เราสามารถกังจับและควบคุมเวลาใน excel ของเราได้แล้วนั่นเองครับ แล้วมาพบกันในบทความเกี่ยวกับการใช้ excel หรือ vba excel ได้ใหม่นะครับ สวัสดีครับ

การใช้ excel แก้ปัญหาการลบช่องว่างในข้อความไม่ได้

การใช้ excel ฉบับนี้อาจจะย้อนไปกล่าวถึงปัญหาการใช้สูตร excel : Trim เพื่อลบช่องว่างภายในข้อความก่อนนำข้อความดังกล่าวไปค้นหาในสูตร excel :vlookup ซึ่งในบทความฉบับนั้นได้กล่าวถึงการแก้ปัญหาโดยการใช้สูตร excel : left เนื่องจากเราทราบจำนวนอักขระที่ต้องการไปใช้งานที่แน่นอนนั่นเองครับ ปัญหาที่เราไมสามารถลบช่องว่างหรือหาตำแหน่งช่องว่างในข้อความไม่ได้นั้น โดยส่วนใหญ่จะพบในข้อความที่เราได้มากจาก Aplication อื่นๆ เช่น website หรือโปรแกรมทางธุรกิจอื่นๆ เช่น Express เป็นต้น การใช้ excel การแก้ปัญหาดังกล่าว จะเริ่มจากตรวจสอบอักขระที่เป็นช่องว่างดังกล่าวก่อนโดยใช้สูตร excel : code เพื่อตรวจสอบรหัส Ascii ของช่องว่างดังกล่าว โดยทั่วไปหากเป็นช่องว่างที่เกิดจากการเคาะ space bar หลายๆครั้ง สูตร excel : code จะคืนค่ารหัส Ascii = 32 มาให้ ซึ่ง เราสามารถใช้สูตร excel : trim ในการจัดการลบช่องว่างหรือใช้สูตร excel :find ในการค้นหาตำแหน่งของช่องว่างดังกล่าวเพื่อใช้ในการแบ่งข้อความนั้น แต่ปัญหาที่พบคือช่องว่างในข้อความที่มาจากโปรแกรมอื่น เช่น website หรือ การส่งออกมาจากโปรแกรมประมวลผลอื่นๆเช่น express ช่องว่างดังกล่าวหากใช้สูตร excel : code ตรวจสอบดูจะพบมีรหัส ascii = 160 ซึ่งเป็นวิธีการพิมพ์ &nbsp โดยเราจะเรียกการพิมพ์ลักษณะนี้ว่า Soft Space โดย Soft Space จะไม่สามารถใช้สูตร excel : trim ลบออกจากข้อความได้ ในการใช้ excel แก้ปัญหาดังกล่าวก่อนนำข้อความดังกล่าวไปประมวลผลต่อในการใช้ excel เราสามารถใช้สูตร excel ได้ดังนี้

1. ใช้สูตร excel : SUBSTITUTE เพื่อแทนที่ Soft Space ด้วย ช่องว่าง

ตัวอย่างการใช้ excel

สมมุติเรามีข้อความที่ได้จากโปรแกรม express ในเซลล์ B2 โดยเป็นชื่อและนามสกุลของลูกค้า

ใช้สูตร excel : SUBSTITUTE ในเซลล์ C2 เพื่อแทนที่ Soft Space ด้วยช่องว่าง โดยเขียนสูตร excel ได้ดังนี้ C2 =SUBSTITUTE($B2,CHAR(160)," ") โดยสูตร excel : char จะคืนค่า Soft Space กลับมา

ใช้สูตร excel : left และ right ในเซลล์ D2 และ E2 เพื่อแยกชื่อและนามสกุลของลูกค้า

สูตร excel แยกชื่อ

D2 = LEFT($C2,FIND(" ",$C2)-1) สูตร excel : find ใช้หาตำแหน่งช่องว่างในข้อความ

สูตร excel แยกนามสกุล

E2 = RIGHT($C2,LEN($C2)-FIND(" ",$C2)-1)

ภาพที่ 1 ตัวอย่างการใช้สูตร excel

หวังว่าท่านผู้อ่านจะนำไปใช้แก้ปัญหาในการใช้ excel ของท่านผู้อ่านได้นะครับ อย่าลืมว่าการนำข้อมูลหรือข้อความมาจากโปรแกรมอื่นจะต้องตรวจสอบความถูกต้องของข้อมูลหรือรูปแบบของข้อมูลให้ดีก่อนนะครับ แล้วพบกันใหม่กับบทความเกี่ยวกับการใช้ excel ครับ สวัสดีครับ

สูตร 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 ต่อๆไปนะครับ สวัสดีครับ