เราจะสามารถแปลงวันที่ที่กรอกมาเป็น พ.ศ. (วิธีที่ผิด) ให้กลายเป็น ค.ศ. (แบบที่ถูกต้อง) ได้อย่างไร มาดูกันครับ
บางครั้งเวลาเรากรอกข้อมูลใน Excel โดยตั้งใจกรอกเป็นวันที่ 31 มกราคม ปี พ.ศ. 2557 เราก็เลยกรอกลงไปว่า 31/01/2557 แต่สิงที่ Excel เข้าใจ คือ มันจะมองว่าเป็น ค.ศ. 2557 (หรือ พศ. 3100 )ต่างหาก!! ไม่ใช่ พ.ศ. 2557 อย่างที่เราอยากได้ (อันนี้ผิดในแง่ข้อมูลเลยนะครับ ไม่ใช่เรื่องของ Format)
ซึ่งบางคนคิดว่าไม่เห็นเป็นไร เราเข้าใจว่าเป็น พศ. 2557 เองก็ได้ อันนี้เป็นความคิดที่ไม่ถูกต้องครับ เพราะทุกอย่างจะผิดเพี้ยนไปหมด ทั้งวันที่นี้คือวันจันทร์ อังคาร พุธ หรืออะไรก็จะผิดหมด แถมบางปีมี 29 กพ. โผล่มาอีกทั้งๆที่ปีนั้นถ้าอยู่ถูกปฏิทินจะมีแค่ 28 กพ. ดังนั้นการแก้วันที่ให้ถูกปฏิทินจึงเป็นเรื่องสำคัญมาก
บางทีเราอาจมี Input ทำนองนี้อยู่มากมาย (เช่น Import มาจาก Database อื่นแล้วผิด Format มาเลย) จะไล่แก้ทีละอันก็ใช่เรื่อง… วันนี้ผมเลยมีวิธีแปลงข้อมูลพวกนี้ให้ถูกต้อง โดยแก้ปัญหา พศ. กับ คศ. แบบง่ายๆ มาให้ครับ
จริงๆทำได้หลายวิธีมาก ผมขอแนะนำซัก 2 แบบละกัน
แปลงวันที่วิธีแรก ใช้ฟังก์ชั่น EDATE มาช่วย
สมมติ ในช่อง A1 เป็น 31/01/2557 (excel เข้าใจว่าเป็น คศ. 2557 ) แล้วเราจะแปลงให้เป็น 31/01/2014 โดยใช้สูตรมาช่วย มาดูกันครับว่าทำยังไง
วิธีคือ \=EDATE(ช่องวันที่,-543*12)
เดี๋ยวผมจะอธิบายว่าทำไมเขียนแบบนี้ครับ
ก่อนอื่นต้องรู้จักฟังก์ชั่น EDATE ซะก่อนครับ… คร่าวๆคือมันสามารถเพิ่ม/ลด วันเข้าไปในวันที่ที่เรากำหนด โดยมีหน่วยเป็นเดือน โดยไม่สนว่าแต่ละเดือนจะมีกี่วัน เช่นจะมี 28, 29, 30 หรือ 31 วันก็ไม่สน
วิธีการเขียนสูตร EDATE
= EDATE(start_date,months)
ตัวอย่างการทำงานของฟังก์ชั่น EDATE
ให้ A1 = 31/01/2013
- ถ้าใส่เลข months เป็นบวก มันจะเพิ่มเวลาไปในอนาคต เท่ากับจำนวน months
- EDATE(A1,1) => 28/2/2013 (ไม่มีวันที่ 31/2/2013 จึงปรับเป็นวันสิ้นเดือนให้)
- EDATE(A1,2) => 31/3/2013
- EDATE(A1,3) => 30/4/2013 (ไม่มีวันที่ 31/4/2013 จึงปรับเป็นวันสิ้นเดือนให้)
- EDATE(A1,4) => 31/5/2013
- ถ้าใส่เลข months ติดลบ มันจะย้อนอดีตเท่ากับจำนวน months
- EDATE(A1,-1) => 31/12/2012
- EDATE(A1,-2) => 30/11/2012 (ไม่มี 31/11/2012)
ใช้ EDATE แปลงจาก พ.ศ. เป็น ค.ศ.
ที่นี้ถ้าเลขต้นฉบับ สมมติช่อง C1 เป็น 31/01/2557 แล้วเราจะทำให้เป็น 31/01/2014 แปลว่ามันต้องย้อนอดีตไป 543 ปี => นั่นคือ 543*12 เดือน
ดังนั้นเราจะเขียนสูตรว่า
\= EDATE(C1,-543*12) นั่นเองครับ (ติดลบ เพราะเลขต้องน้อยลง)
เท่านี้ก็จะได้เป็นวันที่ 31/01/2014 แล้ว (ถ้าเห็นเป็นเลขหลักหมื่นให้ลองเปลี่ยน Format เป็น Date ดูครับ)
ข้อสังเกต!! วิธีนี้กรณีของวันที่ 29 กพ. เช่น เดิมใส่เป็น 29/02/2556 ถ้าใช้สูตรนี้แปลงจะได้เป็น 28/02/2013 ซึ่งต่างจากวิธีล่างที่่จะได้วันที่ 01/03/2013 แทน ซึ่งผมมองว่าวิธีบนถูกต้องมากกว่าครับ เพราะเป็นเดือนเดียวกันด้วย แต่ก็แล้วแต่สถานการณ์นะครับ
แปลงวันที่วิธีที่ 2 ใช้ฟังก์ชั่น DATE, YEAR, MONTH, DAY
วิธีนี้เป็นวิธีที่เห็นได้ทั่วไป ซึ่งผมมองว่าค่อนข้างยุ่ง เพราะต้องเขียนเยอะ คือ แตกปี เดือน วันออกมา แล้วลบที่ปีไปด้วยเลข 543
ใน Excel คุณสามารถจัดรูปแบบตัวเลขในเซลล์สำหรับสิ่งต่างๆ เช่น สกุลเงิน เปอร์เซ็นต์ จุดทศนิยม วันที่ หมายเลขโทรศัพท์ หรือหมายเลขประกันสังคม
- เลือกเซลล์หรือช่วงของเซลล์
- บนแท็บ หน้าแรก ให้เลือก ตัวเลข จากดรอปดาวน์ หรือคุณสามารถเลือกหนึ่งในตัวเลือกเหล่านี้:
- กด CTRL + 1 แล้วเลือก ตัวเลข
- คลิกขวาเซลล์หรือช่วงของเซลล์ เลือก จัดรูปแบบเซลล์... แล้วเลือก ตัวเลข
- เลือกลูกศรขนาดเล็ก ตัวเปิดใช้กล่องโต้ตอบ แล้วเลือก ตัวเลข
- เลือกรูปแบบที่คุณต้องการ
รูปแบบตัวเลข
เมื่อต้องการดูการจัดรูปแบบตัวเลขที่มีอยู่ ให้คลิกตัวเปิดใช้กล่องโต้ตอบที่อยู่ถัดจาก ตัวเลข บนแท็บ หน้าแรก ในกลุ่ม ตัวเลข
รูปแบบ
คำอธิบาย
ทั่วไป
รูปแบบตัวเลขเริ่มต้นที่Excelนําไปใช้เมื่อคุณพิมพ์ตัวเลข ส่วนใหญ่แล้ว ตัวเลขที่ถูกจัดรูปแบบด้วยรูปแบบ ทั่วไป จะแสดงเหมือนกับที่คุณพิมพ์ อย่างไรก็ตาม ถ้าเซลล์ไม่กว้างพอที่จะแสดงตัวเลขทั้งหมด รูปแบบ ทั่วไป จะปัดเศษตัวเลขด้วยทศนิยม รูปแบบตัวเลข ทั่วไป ยังใช้เครื่องหมายทางวิทยาศาสตร์ (เอ็กซ์โพเนนเชียล) สําหรับตัวเลขจํานวนมาก (12 หลักขึ้นไป) ด้วย
ตัวเลข
ใช้สําหรับการแสดงตัวเลขทั่วไป คุณสามารถระบุจํานวนตําแหน่งทศนิยมที่คุณต้องการใช้ ไม่ว่าคุณต้องการใช้ตัวคั่นหลักพัน และวิธีที่คุณต้องการแสดงจํานวนลบ
สกุลเงิน
ใช้สําหรับค่าทางการเงินทั่วไป และแสดงสัญลักษณ์สกุลเงินเริ่มต้นที่มีตัวเลข คุณสามารถระบุจํานวนตําแหน่งทศนิยมที่คุณต้องการใช้ ไม่ว่าคุณต้องการใช้ตัวคั่นหลักพัน และวิธีที่คุณต้องการแสดงจํานวนลบ
บัญชี
ใช้กับค่าทางการเงินด้วย แต่จะจัดแนวสัญลักษณ์สกุลเงินและจุดทศนิยมของตัวเลขในคอลัมน์หนึ่งคอลัมน์
วันที่
แสดงเลขลําดับของวันที่และเวลาเป็นค่าวันที่ ตามชนิดและตําแหน่งที่ตั้ง (ตําแหน่งที่ตั้ง) ที่คุณระบุ รูปแบบวันที่ที่ขึ้นต้นด้วยเครื่องหมายดอกจัน (*) จะตอบสนองต่อการเปลี่ยนแปลงในการตั้งค่าวันที่และเวลาภูมิภาคที่ระบุไว้ในแผงควบคุม รูปแบบที่ไม่มีเครื่องหมายดอกจันจะไม่ได้รับผลกระทบจากการตั้งค่าแผงควบคุม
เวลา
แสดงเลขลําดับของวันที่และเวลาเป็นค่าเวลา ตามชนิดและตําแหน่งที่ตั้ง (ตําแหน่งที่ตั้ง) ที่คุณระบุ รูปแบบเวลาที่ขึ้นต้นด้วยเครื่องหมายดอกจัน (*) จะตอบสนองต่อการเปลี่ยนแปลงในการตั้งค่าวันที่และเวลาภูมิภาคที่ระบุในแผงควบคุม รูปแบบที่ไม่มีเครื่องหมายดอกจันจะไม่ได้รับผลกระทบจากการตั้งค่าแผงควบคุม
เปอร์เซ็นต์
คูณค่าในเซลล์ด้วย 100 และแสดงผลลัพธ์ที่มีสัญลักษณ์เปอร์เซ็นต์ (%) คุณสามารถระบุจํานวนตําแหน่งทศนิยมที่คุณต้องการใช้ได้
เศษส่วน
แสดงตัวเลขเป็นเศษส่วนตามชนิดของเศษส่วนที่คุณระบุ
เชิงวิทยาศาสตร์
แสดงตัวเลขในเครื่องหมายเอ็กซ์โพเนนเชียล โดยแทนที่ตัวเลขส่วนหนึ่งด้วย E+n โดย E (ซึ่งหมายถึงเลขชี้กําลัง) จะคูณตัวเลขก่อนหน้าด้วย 10 ให้เป็นเลขยกกําลังที่ n ตัวอย่างเช่น รูปแบบ เชิงวิทยาศาสตร์ ที่มีทศนิยม 2 ตําแหน่งจะแสดง12345678901เป็น 1.23E+10 ซึ่งเท่ากับ 1.23 คูณ 10 ถึงเลขยกกําลังที่ 10 คุณสามารถระบุจํานวนตําแหน่งทศนิยมที่คุณต้องการใช้ได้
ข้อความ
จะถือว่าเนื้อหาในเซลล์เป็นข้อความ และแสดงเนื้อหาดังกล่าวเหมือนกับที่คุณพิมพ์รวมถึงเมื่อคุณพิมพ์ตัวเลขด้วย
พิเศษ
แสดงตัวเลขเป็นรหัสไปรษณีย์ หมายเลขโทรศัพท์ หรือหมายเลขประกันสังคม
กำหนดเอง
อนุญาตให้คุณปรับเปลี่ยนสําเนาของรหัสรูปแบบตัวเลขที่มีอยู่ ใช้รูปแบบนี้เพื่อสร้างรูปแบบตัวเลขแบบกําหนดเองที่จะถูกเพิ่มลงในรายการโค้ดรูปแบบตัวเลข คุณสามารถเพิ่มรูปแบบตัวเลขแบบกําหนดเองได้ระหว่าง 200 และ 250 รูปแบบ ทั้งนี้ขึ้นอยู่กับเวอร์ชันภาษาของExcelที่ติดตั้งบนคอมพิวเตอร์ของคุณ สําหรับข้อมูลเพิ่มเติมเกี่ยวกับรูปแบบแบบกําหนดเอง ให้ดูที่
คุณสามารถนํารูปแบบต่างๆ ไปใช้กับตัวเลขเพื่อเปลี่ยนลักษณะที่ปรากฏ รูปแบบจะเปลี่ยนวิธีการแสดงตัวเลขเท่านั้น และไม่มีผลต่อค่า ตัวอย่างเช่น ถ้าคุณต้องการให้ตัวเลขแสดงเป็นสกุลเงิน คุณต้องคลิกเซลล์ที่มีค่าตัวเลข> สกุลเงิน
การนํารูปแบบตัวเลขไปใช้จะเปลี่ยนแปลงเฉพาะวิธีการแสดงตัวเลขเท่านั้น และไม่มีผลต่อค่าของเซลล์ที่ใช้ในการคํานวณ คุณสามารถดูค่าจริงได้ใน แถบสูตร
ต่อไปนี้คือรายการของรูปแบบตัวเลขที่พร้อมใช้งานและวิธีที่คุณสามารถใช้รูปแบบตัวเลขเหล่านั้นใน Excel สำหรับเว็บ
รูปแบบตัวเลข
คำอธิบาย
ทั่วไป
รูปแบบตัวเลขเริ่มต้น ถ้าเซลล์ไม่กว้างพอที่จะแสดงตัวเลขทั้งหมด รูปแบบนี้จะปัดเศษตัวเลข ตัวอย่างเช่น 25.76 จะแสดงเป็น 26
นอกจากนี้ ถ้าตัวเลขเป็นตัวเลขอย่างน้อย 12 หลัก รูปแบบ ทั่วไป จะแสดงค่าด้วยเครื่องหมายทางวิทยาศาสตร์ (เอ็กซ์โพเนนเชียล)
หมายเลข
ทํางานได้เหมือนกับรูปแบบ ทั่วไป แต่จะแตกต่างกันมากแต่จะแสดงตัวเลขด้วยตัวคั่นตําแหน่งทศนิยมและจํานวนลบ ต่อไปนี้เป็นตัวอย่างวิธีที่ทั้งสองรูปแบบแสดงตัวเลข:
สกุลเงิน
แสดงสัญลักษณ์ทางการเงินที่มีตัวเลข คุณสามารถระบุจํานวนตําแหน่งทศนิยมได้ด้วย การเพิ่มตําแหน่งทศนิยม หรือ ลดตําแหน่งทศนิยม
บัญชี
ใช้สําหรับค่าทางการเงินด้วย แต่จะจัดแนวสัญลักษณ์สกุลเงินและจุดทศนิยมของตัวเลขในคอลัมน์
วันที่แบบสั้น
แสดงวันที่ในรูปแบบนี้:
วันที่แบบยาว
แสดงเดือน วัน และปีในรูปแบบนี้:
เวลา
แสดงตัวเลขลําดับของวันที่และเวลาเป็นค่าเวลา
เปอร์เซ็นต์
คูณค่าในเซลล์ด้วย 100 และแสดงผลลัพธ์ที่มีสัญลักษณ์เปอร์เซ็นต์ (%)
ใช้ เพิ่มทศนิยม หรือ ลดตําแหน่งทศนิยม เพื่อระบุจํานวนตําแหน่งทศนิยมที่คุณต้องการ
เศษส่วน
แสดงตัวเลขเป็นเศษส่วน ตัวอย่างเช่น 0.5 จะแสดงเป็น 1/2
เชิงวิทยาศาสตร์
แสดงตัวเลขในเครื่องหมายเอ็กซ์โพเนนเชียล โดยแทนที่ตัวเลขส่วนหนึ่งด้วย E+n โดย E (เลขชี้กําลัง) จะคูณตัวเลขก่อนหน้าด้วย 10 ให้เป็น เลขยกกําลังn ตัวอย่างเช่น รูปแบบ เชิงวิทยาศาสตร์ ที่มีทศนิยม 2 ตําแหน่งจะแสดง12345678901เป็น 1.23E+10 ซึ่งเท่ากับ 1.23 คูณ 10 ถึงเลขยกกําลังที่ 10 เมื่อต้องการระบุจํานวนตําแหน่งทศนิยมที่คุณต้องการใช้ ให้นํา เพิ่มตําแหน่งทศนิยม ไปใช้ หรือ ลดทศนิยม
ข้อความ
จะถือว่าค่าในเซลล์เป็นข้อความ และแสดงค่านั้นเหมือนกับที่คุณพิมพ์ แม้ว่าคุณจะพิมพ์ตัวเลขก็ตาม เรียนรู้เพิ่มเติมเกี่ยวกับ การจัดรูปแบบตัวเลขเป็นข้อความ