วันที่ในExcel เรื่องไม่ลับแต่คนส่วนใหญ่ไม่รู้

วันที่ในกรอบสีแดงมันผิดนะครับ ที่ถูกต้องเป็น 1-Mar-14 ทำไมไม่ใช่ 3Jan14? ดูตรงไหน? ผิดเพราะอะไร? จะแก้อย่างไร? จากรูปข้างบน เรื่องวันที่ของ Excel เป็นเรื่องที่ก่อให้เกิดความผิดพลาดในการวิเคราะห์ข้อมูลบ่อยมาก ๆ ทั้งนี้ไม่ใช่เพราะ Excel คิดผิด หรือทำงานผิด แต่เป็นเพราะเราที่เป็นผู้ใช้งานเองไม่มีความเข้าใจที่ดีพอ วันนี้ขออธิบายเรื่องวันที่ ด้วยตัวอย่างในรูปข้างบนนะครับ

วันที่ในกรอบสีแดงมันผิดนะครับ
ที่ถูกต้องเป็น 1-Mar-14 ทำไมไม่ใช่ 3Jan14? ดูตรงไหน? ผิดเพราะอะไร? จะแก้อย่างไร?
——-//——

🔸ที่ผิดเพราะ Excel ตีความการกรอกข้อมูล (หรือข้อมูลที่เรา import เข้ามาไม่ถูกต้อง)

รู้ได้อย่างไรว่าไม่ถูกต้อง..
🔸ตอบ: Excel ฟ้องว่า วันที่ ใน cell สีเหลือผิด

รู้ได้ไงว่า Excel บอกว่ามันผิด
🔸ตอบ: รู้เพราะ วันที่ใน Cell สีเหลืองมันชิดซ้าย นั่นแปลว่า Excel ไม่รู้จักวันที่นั้น ๆ มันเลยคิดว่าเป็น Text แล้วมันก็แสดงค่าให้ชิดซ้ายไว้

วันที่ในExcel อธิบายได้อย่างนี้ครับ…

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

วันที่ในกรอบสีแดงเป็นวันที่ ที่ไม่ถูกต้อง ทั้ง ๆ ที่เราเห็นว่ามันแสดงรูปแบบวันที่ ที่ถูกต้อง และหากเราลองปรับ Format number ให้เป็นตัวเลขดูก็จะเห็นว่ามันเป็นวันที่ที่แท้จริง คือ ไม่ได้เป็น Text มันหมายถึงวันที่ตามปฏิทินจริง ๆ

แต่นั่นแหละครับ เป็นการทำงานของ Excel ที่ไม่ถูกต้อง ในกรณีนี้วันที่ในกรอบสีแดงต้องเป็นวันที่ 01-Mar-14 ครับ

วันที่ในExcel ถือเป็นเรื่องสำคัญที่ต้องเข้าใจให้ถูกต้อง

มาดูว่า ทำไมเราจึงรู้ว่าวันที่ที่ถูกต้องไม่ใช่ 03-Jan-14 แต่ควรเป็น 01-Mar-14

ประการแรก หากเราดูวันที่ในพื้นที่สีเหลือง จะเห็นว่า ตัวเลขวันที่ชิดซ้าย นั่นหมายความว่า Excel คิดว่า คำว่า 01-13-2014 ไม่ใช่วันที่

🔸แล้วทำไมจึงเป็นเช่นนั้น ทำไมในพื้นที่สีส้มยังจัดการให้เป็นวันที่ได้ แต่ทำไมพื้นที่สีเหลืองจึงทำไม่ได้

ที่เป็นเช่นนี้เพราะ Excel เครื่องนี้ตีความว่า สำหรับมันการคีย์วันที่ต้องเป็น dd-mm-yy มันจึงไม่สามารถจัดการวันที่ในพื้นที่สีเหลืองได้ เพราะ ตัวเลขหลักที่ควรเป็นเดือน ตัวอย่าง ใน cell C6 มันเกิน 12 (มันเป็นเลข 13 ) ซึ่งตัวเลขของเดือนตามปฏิทินค่าเดือนที่สูงสุดคือ 12 เท่านั้น มันจึงบอกว่าสิ่งที่เห็นนี้แม้หน้าตาจะเหมือนว่าเป็นวันที่ แต่ไม่ใช่หรอกเพราะเลขเดือนมันเกิน 12 มันจึงไม่แปลงสิ่งที่พิมพ์เข้าไปให้เป็นวันที่ เลยปล่อยให้เป็น Text ไว้อย่างนั้น

🔸อ้าว…แล้วทำไม Excel คิดเป็น dd-mm-yy ทำไมไม่คิดว่ามันต้องเป็น mm-dd-yy

ตอบว่า Excel จะคิดตามรูปแบบวันที่ที่ตั้งเป็นค่า Default ซึ่งค่านี้ตั้งที่ Control Panel ของ windows ครับ หากอยากจะเปลี่ยนต้องเข้าไปเปลี่ยนที่ Control Panel ดูวิธีเปลี่ยนได้ที่ลิงค์นี้ครับ DefaultDateSetting

🔸มาถึงข้อสรุป ทำไมวันที่ในช่องสีส้มควรต้องเป็นวันที่ 01-Mar-14

• เพราะ 1) จากความรู้ข้างบน เรารู้ว่า Excel เครื่องนี้ตีความวันที่แบบ dd-mm-yy
• และ 2) เมื่อดูจาก C6 และ C9 จะเห็นว่ารูปแบบวันที่ที่กรอกเข้าไปเป็น mm-dd-yy
• นั่นคือ 3) วันที่ในพื้นที่สีส้มที่กรอกเข้าไปก็ต้องเป็น 03-01-2014 ซึ่งต้องการจะหมายถึง เดือน 3 / วันที่ 1 / ปี 14 แต่เพราะ Excel เครื่องนี้พยายามอ่านวันทึ่ให้เป็นแบบ dd-mm-yy จึงตีความผิด เป็น วันที่ 3 /เดือน 1 / ปี 14 นั่นเอง

🔸แล้วทำไมมันจึงแสดงเป็นรูปแบบวันที่ที่ถูกต้องล่ะ

เพราะ บังเอิญเลขวันที่ และเดือน ที่สลับกัน วัน-เดือนกัน มันสามารถเป็นวันที่ 1 Mar หรือ วันที่ 3 Jan ก็ได้ มันเลยคิดว่ามันถูกต้องเแล้ว แต่จริง ๆ แล้วมันผิด

วิธีแก้ไขวันที่ในExcel ให้ถูกต้อง

1) ปรับ Control Panel ให้ตรงกับรูปแบบการเขียนวันที่ที่ import ข้อมูลเข้ามา

แต่วิธีนี้จะไม่สะดวกเพราะในการทำงานจริงข้อมูลต่าง ๆ ที่เราได้รับมามันมีรูปแบบที่ไม่แน่นอน บางไฟล์ก็เป็น dd-mm-yy ส่วนอีกบางไฟล์ก็เป็น mm-dd-yy คร้้นเราเปลี่ยนที่ไฟล์ที่สอง ก็อาจจะทำให้เมื่อเปิดไฟล์ที่หนึ่งขึ้นมาวันที่ก็จะผิดอีก

แต่อย่างไรก็ตามผมแนะนำให้เข้า Control Panel เปลี่ยนวันที่ให้เป็น English United state แล้วกำหนดรูปแบบ Short date ให้เป็น “DD-MMM-YY” จะทำงานกับวันที่ง่ายขึ้นครับ

2) ใช้เมนู Text to Columns ปรับ Data Type ให้ถูกต้อง (เป็นวิธีที่ผมแนะนำครับ)

โดยทำตามนี้ครับ

• 🔸 ลากพื้นที่ C5:C9 (สังเกตว่าไม่ได้เลือกเพียงบรรทัดที่เป็นสีส้ม)
• 🔸 เข้า Menu > Data > Text to Columns
• 🔸จะเข้าหน้าต่าง Text To Columns Wizard ซึ่งจะมี 3 ขั้นตอน
  • Step 1. เลือก “Delimeted” (กำหนดบอก Wizard ว่าข้อมูลมีตัวแบ่ง จริง ๆ แล้วเราไม่ได้ต้องการแบ่งอะไร) กด Next จะไป Wizard Step 2
  • Step 2. ให้กำหนดว่าจะใช้ค่าอะไรแบ่ง ด้วยค่า Default จะเป็น Tab เราก็กด Next ต่อไป
  • Step 3. กำหนด Data Type ให้เลือก เป็น Date และกำหนดรูปแบบวันที่ของข้อมูล โดยกดเลือกจาก DropDown ทางขวาให้เป็น MDY แล้ว กด Finish
• เพียงเท่านี้เราก็จะได้รูปแบบวันที่ที่ถูกต้อง พร้อมนำไปวิเคราะห์ข้อมูลต่อได้แล้วครับ

ข้อพึงระวัง

ถ้าในคอลัมน์วันที่ มีอาการเพี้ยนวัน-เดือน แบบนี้ ให้สรุปไปได้เลยว่า วันที่ทั้งหมดในคอลัมน์นี้ผิดทั้งหมด แล้วให้ทำการจัดรูปแบบวันที่ใหม่ทั้งคอลัมน์ *อย่านั่งแก้เพียงบรรทัดที่เห็นว่าเป็น Text เพระในความเป็นจริงที่เห็นว่าเป็นวันที่ก็จะสลับวัน-เดือน กันอยู่ จึงต้องแก้ทั้งหมด ด้วยวิธีที่ถูกต้อง

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

พิชาติ
Learn Excel With Pichart
Smart Excel For Better LIFE
🔸 สนใจจัดอบรมในองค์กร หรือต้องการปรึกษาแบบ Private Consult สามารถติดต่อ อ. ชาติ โดยตรงครับโทร. 099-084-2562 / 082-663-9949