BlogGang.com : : ครูเอก : การคำนวณหาอายุงานแบบอัตโนมัติ

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

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

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

คำสั่ง

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

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

จากคำสั่งในโจทย์ ที่กำหนดให้หาอายุงาน และให้ผลลัพธ์ดังกล่าว อัพเดตอัตโนมัติ ณ วันใดๆ ที่เปิดไฟล์ขึ้นมา ทั้งนี้ด้วยเหตุผลที่ว่า ถ้าเราหาอายุงานออกมา โดยการคิดแบบ 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 น.

12 comments

Counter : 18164 Pageviews.

ขอเรียนด้วยน่ะฮับ

โดย: ทาเฉพาะหัวสิว

วันที่: 3 มิถุนายน 2551 เวลา:22:33:31 น.

ช่วยทำตารางคำนวน ฟุตบอลยูโร 2008 ให้หน่อยครับ

โดย: Krissana IP: 202.91.19.204 วันที่: 14 มิถุนายน 2551 เวลา:21:50:37 น.

สวัสดีครับ คุณ Krissana
ขอบคุณที่แวะมาครับ แต่ต้องขอโทษด้วย เรื่องฟุตบอลยูโร 2008 ผมไม่มักครับ อิอิ

โดย: ครูเอก

วันที่: 15 มิถุนายน 2551 เวลา:9:38:35 น.

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

โดย: Opey

วันที่: 15 มิถุนายน 2551 เวลา:12:27:27 น.

ขอบคุณครับ ใช้แล้วดีจิริง

โดย: sit IP: 58.147.58.69 วันที่: 19 พฤศจิกายน 2553 เวลา:10:06:21 น.

ขอบคุณค่ะใช้ได้ดีจริง

โดย: รัตนา IP: 27.130.113.72 วันที่: 23 พฤศจิกายน 2554 เวลา:15:05:57 น.

หากตามโจทก์ เปลี่ยนจาก Today เป็นวันอื่นได้ไหมครับ เช่นวันที่ 1 มกราคม 2554 เป็นต้น ต้องใช้สูตรอย่างไรครับ ขอบคุณครับ potiphan@hotmail.com

โดย: ake IP: 203.114.109.141 วันที่: 23 ธันวาคม 2554 เวลา:11:57:54 น.

หากตามโจทก์ เปลี่ยนจาก Today เป็นวันสิ้นปีเช่นเป็นวันที่ 31 มกราคม 2554 เป็นต้น ต้องใช้สูตรอย่างไรค่ะ ขอบคุณค่ะ pichayaya.m@hotmail.com

โดย: พิชยา IP: 118.174.17.93 วันที่: 7 พฤศจิกายน 2555 เวลา:9:41:48 น.

อยากได้สูตรออกมาเป็น ปีและเดือนเท่านั้นละคะ แต่เศษวันปัดขึ้นคะ

โดย: แคทตี้ IP: 125.25.156.205 วันที่: 14 ธันวาคม 2556 เวลา:11:42:36 น.

ขอบคุณมากๆค่ะ ลองมาหลายวิธีแล้ว วิธีนี้ใช้งานได้ดีมากเลยค่ะ

โดย: Mee Pooh IP: 110.77.229.67 วันที่: 21 สิงหาคม 2557 เวลา:14:13:29 น.

ขอบคุณนะคะ ใช้ได้จริงคะ

โดย: ส้ม IP: 112.142.7.23 วันที่: 13 มกราคม 2558 เวลา:11:30:51 น.

รบกวนถามหน่อยนะครับ
=DATEDIF(A1,TODAY(),"Y")&" ปี "&DATEDIF(A1,TODAY(),"YM")&" เดือน "&DATEDIF(A1,TODAY(),"MD")+1&" วัน"

จากสูตรข้างบนนี้ จะเปลี่ยนเป็นปี พ.ศ.ต้องทำยังไงครับ
ช่วยตอบหน่อยนะครับ ขอบคุณมากครับ

โดย: thutha IP: 110.77.132.60 วันที่: 21 เมษายน 2558 เวลา:11:14:23 น.