“อยากเขียนสูตรปัดเวลาเข้างานเป็นนาที ต้องทำยังไงคะ?” คุณติ๊บ สาวงามอันดับหนึ่งของบริษัทให้เกียรติเดินมาถามผมถึงโต๊ะ

“อะ…อะ..ไรนะครับ” ผมประหม่าทุกครั้งที่คุยกับเธอ
“คืออย่างนี้ค่ะ ติ๊บอยากเขียนสูตรปัดเวลาเข้างาน โดยปัดเศษวินาทีทิ้งไป เช่น ถ้าเข้างาน 9:01:58 ก็ให้เป็น 9:01:00”
“อะ..อ่อ ชะ..ใช้ฟังก์ชัน FLOOR ได้เลยครับ”

=FLOOR(A4, “0:01″)”

“เอ๋! ในสูตรเขียนเป็น “0:01” ได้ด้วยหรือคะ?”
“ได้ครับ อันนี้เป็นทริกเล็กๆในการปัดเวลาเป็นนาทีครับ” ผมเริ่มคุมสติได้ ขณะที่พี่วิกที่นั่งเยื้องไปหันมาทำตาโต พลางทำมือชี้โบ้ชี้เบ๊

“เดี๋ยวติ๊บลองนิดนึงนะคะ” คุณติ๊บพูดพลางพิมพ์สูตรลงในคอมพิวเตอร์ที่ถือมาด้วย
“มองด้านข้างยิ่งสวยแฮะ” ผมคิดในใจ พลางสูดได้กลิ่นน้ำหอมที่ไม่รู้ยี่ห้ออะไร แต่ชวนฝันสุดๆ

“เอ๊ะ!” คุณติ๊บโพล่งขึ้น ผมทำเอาผมสะดุ้ง หรือเธอรู้ว่าผมแอบมอง
“มันได้แค่บางค่า แต่บางค่าไม่ได้ค่ะ” คุณติ๊บชี้ให้ดูสูตรในคอมพิวเตอร์

เพล๊ง!! ผมได้ยินเสียงแตกกราวของหน้าตัวเอง

“ดะ..เดี๋ยวผมขอลองนิดนึงนะครับ” ผมพยายามคุมสติ แล้วพิมพ์สูตรในคอมพ์ของคุณติ๊บ จังหวะเดียวกันนั้น ตั้มที่กำลังเดินเข้ามา พอเห็นคุณติ๊บถึงกับผงะ หันไปทางพี่วิก พลางส่งภาษามือชี้โบ๊ชี้เบ๊ใส่กัน

“เฮ้ย! ผิดได้ไงเนี่ย!” ผมคิดในใจ พลางเห็นพี่วิกและตั้มทำท่าสะใจสุดๆ
“เวรเอ๊ย! ตอนตั้มถามสูตรไม่เคยผิด ตอนคุณติ๊บถาม ผิดได้ไงฟะ” ผมคิดในใจพลางพิมพ์สูตรใหม่ แต่ก็ได้ผลลัพธ์เท่าเดิม

เดี๋ยวนะ!! ทำไมสูตรผิดเฉพาะค่าที่วินาทีเป็น 00 ล่ะ ทำไมค่าอื่นไม่เห็นผิดเลย?

ผมจึงทดลองพิมพ์ 9:10 ลงในเซลล์ A2 จากนั้นใช้สูตร FLOOR(A2, “0:01”) อีกครั้ง
คราวนี้ได้ผลตามต้องการ
ทดลองพิมพ์ค่าอื่นดูบ้าง ได้ผลตามต้องการหมดเลย

“หึๆๆ” ผมหัวเราะในลำคอ จังหวะนั้นเองแสงอาทิตย์ก็ฉายมาพอดี ผมใช้นิ้วกลางดันแว่นขึ้น ก้มหน้าเล็กน้อยแล้วพูดว่า
“ปริศนาทุกอย่าง ไขกระจ่างแล้ว”

ก่อนจะอธิบายว่าทำไมสูตรถึงผิด ขออธิบายเรื่องเวลาใน Excel นิดนึงครับ

ค่าที่แท้จริงของเวลาคือ ตัวเลข โดย
1 วัน = 1
1 ชั่วโมง = 1/24 = 0.04167
1 นาที = 1/24/60 = 0.00069
1 วินาที = 1/24/60/60 =0.00001157

ค่าที่แท้จริงของ 6 โมงเช้า (6:00) คือ 0.25 (6/24 )
ค่าที่แท้จริงของ บ่ายสามโมง (15:00) คือ 0.625 (15/24)

ค่าที่แท้จริงของ บ่ายสามสามสิบสี่สิบห้าวินาที (15:30:45) คือ
=15/24 + 30/24/60 + 45/24/60/60
=0.62500 + 0.02083 + 0.00052
=0.64635

การพิมพ์เวลาใน Excel ต้องคั่นระหว่างชั่วโมง:นาที:วินาที ด้วยเครื่องหมาย : (Colon) เท่านั้น
เช่น ต้องพิมพ์เป็น 15:30
ห้ามคั่นด้วยจุดหรือพิมพ์เป็น 15.30 (เพราะค่าที่แท้จริงจะกลายเป็น 15.30)

ถ้าลองพิมพ์ 15:30 ในเซลล์ใดๆ แล้วคลิกขวา/Format Cells จากนั้นเปลี่ยน number format เป็น General จะพบว่าค่าที่แท้จริงคือ 0.645833333

แล้วทำไมเซลล์นั้นถึงแสดงค่าเป็น 15:30 ล่ะ?

เพราะเมื่อคั่นตัวเลขด้วย : (colon) โปรแกรมจะรู้ว่านี่คือเวลา แล้วแปลงฟอร์แมตให้เป็นเวลา

จากตัวอย่างเดิม ถ้าลองพิมพ์ 15:30 ที่เซลล์ใดๆ แล้วคลิกขวา/Format Cells จะพบว่าโปรแกรมเซ็ต custom number format ให้เป็น h:mm ฟอร์แมตที่แสดงคือ 15:30 (แต่ค่าที่แท้จริงคือ 0.645833333)

ในทางตรงข้าม ถ้าพิมพ์ 0.75 ที่เซลล์ใดๆ แล้วปรับ custom number format เป็น Time จะได้ผลลัพธ์เป็น 6:00:00 PM

Custom Number Format คือการเปลี่ยนรูปแบบ แต่ไม่ได้เปลี่ยนค่าที่แท้จริง คล้ายกับเราใส่หน้ากากใน The Mask Singer แต่ข้างในยังเป็นเรา (มา The Mask Singer ได้ไงเนี่ย ^^)

กลับมาที่เคสของคุณติ๊บต่อ ทำไมสูตรถึงผิด?

มีความเป็นไปได้ 2 สาเหตุ

1. Export ข้อมูลจากโปรแกรมอื่น

คุณติ๊บอาจ export ข้อมูลนี้มาจากโปรแกรมอื่น แล้วเปิดด้วย Excel

ข้อมูลระดับวินาทีเป็นทศนิยมตำแหน่งที่ลึกมากๆ ถ้า export ข้อมูลเวลาจากโปรแกรมอื่นโดยไม่กำหนดความละเอียดของทศนิยมให้มากพอ ตัวเลขเวลาที่ได้ก็จะผิดในตำแหน่งท้ายๆ

พอใช้ FLOOR ในระดับ “0:01” ก็มีโอกาสที่จะผิดสูง

2. Manual + Bug

ในการทำงานจริงแต่ละคนมักมีกระบวนการ “แมนวล” บางอย่าง เช่น อาจพิมพ์แล้วแก้นี่นั่น อาจลากสูตรแล้วปรับนู่นนี่ อาจก็อปปี้ข้อมูลจากที่อื่นแล้วแก้นี่โน่น ซึ่งถ้าเป็นข้อมูลปกติก็ไม่มีปัญหาอะไร

แต่ถ้าเป็นข้อมูลเวลาระดับวินาที กระบวนการแมนวลเหล่านี้อาจทำให้เกิดบั๊กเล็กๆ (bug) ในระดับทศนิยมลึกๆ

แถมเคสนี้ใช้ระดับนัยสำคัญของ FLOOR เป็น “0:01” จึงมีโอกาสที่ผลลัพธ์จะผิด

ดังนั้น สูตรไม่ได้ผิด แต่ผิดที่ข้อมูล

“ได้มั๊ยคะพี่?” เสียงคุณติ๊บคล้ายกระชากวิญญาณอาเคจิออกจากร่าง ดึงผมกลับสู่โลกแห่งความจริง
“ลองเปลี่ยนเป็นสูตรนี้ครับ”

=TIME(HOUR(A4),MINUTE(A4),0)

“ว้าว ใช่เลยค่ะ แบบนี้แหละที่ต้องการ” คุณติ๊บปรบมือเบาๆ ยิ้มกว้างจนลักยิ้มบุ๋มลงไป

เนื่องจากขัอมูลเวลาผิด จึงไม่สามารถใช้ FLOOR ปัดตัวเลขได้โดยตรง
ทางแก้คือต้องสร้างเวลาขึ้นมาใหม่ โดยอิงจากข้อมูลเดิม
แล้วจะสร้างเวลาได้อย่างไรล่ะ?

ด้วยฟังก์ชัน TIME นั่นเอง!

ฟังก์ชัน TIME คืออะไร?
ฟังก์ชัน TIME มีความหมายตามชื่อเลย คือฟังก์ชันที่ใช้สร้างเวลา โดยค่าที่แท้จริงยังคงเป็นตัวเลขตามหลักการของเวลา

โครงสร้างฟังก์ชันง่ายมาก คือ

TIME( hour, minute, second )

hour คือ ชั่วโมง เคสนี้ต้องการเป็นเลขชั่วโมง ก็ใช้ฟังก์ชัน HOUR อิงจากข้อมูลเดิมได้เลย
เช่น HOUR(A4) = 9

minute คือ นาที เคสนี้ต้องการเป็นเลขนาที ก็ใช้ฟังก์ชัน MINUTE อิงจากข้อมูลเดิมได้เลย เช่น
MINUTE(A4) = 0
MINUTE(A5) = 2
MINUTE(A6) = 10

second คือ วินาที เคสนี้ต้องการปัดวินาทีทิ้ง หรือไม่สนใจข้อมูลระดับวินาที ก็ใส่เป็น 0 ได้เลย

=TIME(HOUR(A4),MINUTE(A4),0)
=9:00 (ค่าที่แท้จริงคือ 9/24 = 0.375)

ได้ผลลัพธ์ตามต้องการ โดยไม่สนใจว่าข้อมูลต้นทางมีทศนิยมลึกๆผิดหรือไม่

“แล้วถ้าต้องการข้อมูลเป็นเลขนาทีล่ะคะ?”
“ใช้ฟังก์ชัน MINUTE ตรงๆได้เลยครับ เช่น MINUTE(A4)”

“แล้วถ้าต้องการได้นาทีในรูปแบบของเวลา เช่น 00:13 ล่ะคะ?”
“ใช้ฟังก์ชัน MINUTE ได้เช่นกัน แล้วหารด้วย 24 และ 60 ครับ”

“หรือใช้ฟังก์ชัน TIME ก็ได้เช่นกัน แต่กำหนด hour เป็น 0 ครับ”

=TIME(0,MINUTE(A4),0)

ถ้าจะให้ดี ปรับ custom number format เป็น hh:mm:ss ด้วยนะครับ
(h = hour, m = minute, s = second)

“ว้าว ได้ความรู้เยอะมากๆ ขอบคุณมากเลยนะคะ” คุณติ๊บส่งยิ้มหวาน เก็บคอมพ์แล้วเดินจากไป เดินไปสักพัก หันมาโค้งและยิ้มให้อีกครั้ง

เธอจากไป แต่กลิ่นน้ำหอมจาง ๆ ยังคงอยู่..

“ทำงาน ทำงานได้แล้ว มัวแต่จีบสาวอยู่นั่นแหละ” พี่วิกตะโกนข้ามโต๊ะ
สอนเพื่อนร่วมงาน ก็เป็นงานอย่างนึงนะพี่ ^^

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่โดยคลิก Like เฟซบุ๊กแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^
.
อ้อ! ตอนนี้ผมมีสอนคอร์สออนไลน์กับทาง SkillLane แล้ว ดูรายละเอียดได้จาก ลิงก์นี้ครับ