การคำนวณหาอายุงานแบบอัตโนมัติ

เฉลยแบบทดสอบทักษะการใช้สูตรและฟังก์ชัน Excel ข้อที่ 2

โจทย์ข้อที่ 2



คำสั่ง

1. จากตารางที่โจทย์กำหนด จงคำนวณหาอายุงานของแต่ละคน ณ วันปัจจุบัน โดยแสดงผลลัพธ์เป็นข้อความ "xx ปี xx เดือน xx วัน"
   
2. เปิดไฟล์นี้ขึ้นมาดูเมื่อใด ผลลัพธ์ที่ได้จะต้องอัพเดตอัตโนมัติถึง ณ วันนั้นๆ

---

อธิบายแนวความคิด

จากคำสั่งในโจทย์ ที่กำหนดให้หาอายุงาน และให้ผลลัพธ์ดังกล่าว อัพเดตอัตโนมัติ ณ วันใดๆ ที่เปิดไฟล์ขึ้นมา ทั้งนี้ด้วยเหตุผลที่ว่า ถ้าเราหาอายุงานออกมา โดยการคิดแบบ Manual แล้วพิมพ์ผลลัพธ์ใส่ลงไปตรงๆ เช่น 2 ปี 5 เดือน 15 วัน จะเปิดไฟล์ข้อมูลนี้ขึ้นมาวันไหนๆ ก็ยังเป็น 2 ปี 5 เดือน 15 วัน เหมือนเดิมไม่เปลี่ยนแปลง ทำให้เราได้ข้อมูลที่ไม่ถูกต้อง

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

สำหรับฟังก์ชันที่ใช้แสดงวันที่ปัจจุบัน โดยดึงค่าวันที่มาจากเครื่องคอม คือฟังก์ชัน TODAY เราสามารถแสดงวันที่ปัจจุบัน ณ เซลใดๆ โดยการพิมพ์ =TODAY()

สำหรับการหาผลต่างระหว่างวันที่สองค่า มีอยู่ด้วยกันหลายวิธี แต่ในที่นี้ผมขอแนะนำฟังก์ชันชื่อว่า DATEDIF ซึ่งมีรูปแบบการใช้งาน ดังนี้

=DATEDIF( วันที่เริ่มต้น , วันที่สิ้นสุด , รูปแบบผลลัพธ์ )

โดยที่รูปแบบผลลัพธ์มีอยู่ด้วยกัน 6 แบบคือ
"Y" หมายถึง หาผลต่างเป็นจำนวนเต็มปี
"YM" หมายถึง หาผลต่างเป็นจำนวนเดือน แบบปีชนปี
"YD" หมายถึง หาผลต่างเป็นจำนวนวัน แบบปีชนปี
"M" หมายถึง หาผลต่างเป็นจำนวนเต็มเดือน
"MD" หมายถึง หาผลต่างเป็นจำนวนวัน แบบเดือนชนเดือน
"D" หมายถึง หาผลต่างเป็นจำนวนเต็มวัน

เงื่อนไขอีกอย่างหนึ่งก็คือ วันที่เริ่มต้น และวันที่สิ้นสุด จะต้องเป็นวันที่รูปแบบเดียวกัน เช่น d/m/y หรือจะ m/d/y หรือจะ y/m/d ซึ่งจะเป็นอย่างไรก็ได้ แต่ขอให้นำเข้าเป็นรูปแบบเดียวกัน

และอีกอย่างหนึ่งคือ เป็นปี ค.ศ. หรือจะเป็นปี พ.ศ. แต่โดยปกติแล้ว Excel จะรับค่าเป็นปี ค.ศ. เท่านั้น ซึ่งในที่นี้ผมจะใช้เป็นปี ค.ศ. และจากที่กล่าวมาข้างต้น ว่าให้คำนวณอายุงานถึง ณ วันปัจจุบัน ดังนั้น ผมจะใช้ฟังก์ชัน TODAY() แทนค่าวันปัจจุบัน

ดังนั้น โครงสร้างฟังก์ชัน DATEDIF สำหรับการหาอายุงานในข้อนี้ คือ

=DATEDIF( วันที่เข้างาน , TODAY() , รูปแบบผลลัพธ์ )


การหาอายุงานออกมาเป็น กี่ปี เศษกี่เดือน เศษกี่วัน

หาจำนวนปี =DATEDIF( วันที่เข้างาน , TODAY() , "Y" )
หาเศษเดือน =DATEDIF( วันที่เข้างาน , TODAY() , "YM" )
หาเศษวัน =DATEDIF( วันที่เข้างาน , TODAY() , "MD" )

จากทั้งสามสูตรด้านบน จะได้ผลลัพธ์ออกมาเป็นตัวเลข จำนวนปี เศษเดือน และเศษวัน ตามลำดับ

แต่เนื่องจากการบวก ลบ วันที่ใน Excel ใช้หลักการเดียวกันกับการบวก ลบ ตัวเลขจำนวนเต็มทางคณิตศาสตร์ ดังนั้น ผลลัพธ์ที่ได้จึงอาจจะดูแปลกๆ เช่น ถ้าเราทำงานตั้งแต่วันที่ 2 ถึงวันที่ 6 ถามว่าเราทำงานกี่วัน คำตอบคือ 5 วัน แต่ในทางคณิตศาสตร์ เราเอา 6 ตั้ง แล้วลบด้วย 2 จะได้ผลลัพธ์เป็น 4 ซึ่งการบวก ลบ วันที่ใน Excel ก็ใช้หลักการนี้ ดังนั้น ผลลัพธ์ที่ได้เราจึงบวกกลับเข้าไปอีก 1 เพื่อให้นับวันตั้งต้นด้วยเสมอ

สูตรที่นำไปใช้งานจริง จึงเป็นดังนี้

หาจำนวนปี =DATEDIF( วันที่เข้างาน , TODAY()+1 , "Y" )
หาเศษเดือน =DATEDIF( วันที่เข้างาน , TODAY()+1 , "YM" )
หาเศษวัน =DATEDIF( วันที่เข้างาน , TODAY()+1 , "MD" )

หมายเหตุ เพื่อให้เข้าใจเกี่ยวกับเรื่องของ วันและเวลา ใน Excel มากยิ่งขึ้น ท่านสามารถเข้าไปอ่านเพิ่มเติมเกี่ยวกับเรื่องนี้ ได้ที่ Blog ของคุณคนควน

หลังจากนี้ เราจะต้องใช้การเชื่อมข้อความ เพื่อให้ได้ผลลัพธ์ดังที่โจทย์กำหนด

=จำนวนปี&" ปี "&เศษเดือน&" เดือน "&เศษวัน&" วัน"


โดยสรุปก็คือ ที่เซล F19 (อ้างอิงจากรูป) พิมพ์สูตรดังนี้

=DATEDIF(E19,TODAY()+1,"Y")&" ปี "&DATEDIF(E19,TODAY()+1,"YM")&" เดือน "&DATEDIF(E19,TODAY()+1,"MD")&" วัน"

แล้วคัดลอกสูตรลงมายัง F20 และ F21 ตามลำดับ

Create Date : 03 มิถุนายน 2551 Last Update : 1 กุมภาพันธ์ 2552 13:54:45 น. Counter : 19379 Pageviews.		12 comments
Share Tweet