มีงานหลายอย่าง ที่เกี่ยวข้องกับการหาช่วงระยะเวลา หรืออายุ (ทั้งอายุงาน, อายุคน, อายุสินค้า ฯลฯ) อย่างเช่นในไฟล์ แบบทดสอบทักษะการใช้สูตรและฟังก์ชัน Excel ข้อที่ 2 ซึ่งมีอยู่ในซีดีรวมไฟล์ตัวอย่าง Excel ชุดที่ 1 (หรือเข้าไปดาวน์โหลดจากเว็บ http://www.e-hrit.com ก็ได้ครับ) ในโจทย์ข้อที่ 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 จะรับค่าเป็นปี ค.ศ. เท่านั้น ดังนั้น วันที่เริ่มต้น (ไม่ว่าจะเป็นวันเกิด หรือวันเข้างาน) ต้องเก็บเป็นปี ค.ศ. เท่านั้น
โครงสร้างของฟังก์ชัน 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" )
หลังจากนี้ เราจะต้องใช้การเชื่อมข้อความ เพื่อให้ได้ผลลัพธ์ ดังที่โจทย์กำหนด
=จำนวนปี&" ปี "&เดือน&" เดือน "&วัน&" วัน"
โดยสรุปก็คือ ที่เซลล์ F19 (อ้างอิงจากรูปข้างบน) พิมพ์สูตรดังนี้
=DATEDIF(E19,TODAY()+1,"Y")&" ปี "&DATEDIF(E19,TODAY()+1,"YM")&" เดือน "&DATEDIF(E19,TODAY()+1,"MD")&" วัน"
แล้วคัดลอกสูตรลงมายัง F20 และ F21 ตามลำดับ
0 ความคิดเห็น:
แสดงความคิดเห็น