กรณีศึกษา : การคำนวณหาอายุ จากข้อมูลที่ Export ออกมาจากโปรแกรม Power Vision

เช้าวันนี้ ผมได้รับเมล์ฉบับหนึ่งจากคุณประสิทธิ์ชัย (ขออภัยไม่เอ่ยนามสกุล) สอบถามมาเกี่ยวกับเรื่องปัญหาในการคำนวณหาอายุงาน มีรายละเอียดดังนี้

เรียน อ.สำเริง ผมได้ติดตามผลงานของอาจารย์ มาเสมอ ตั้งแต่หนังสือ EXCEL FOR HR และ เข้ามาดูใน website ผมเลื่อมใสในการ share ความรู้สู่สาธารณชนของอาจารย์ ครั้งนี้ผมขอความกรุณาจากอาจารย์ ได้ช่วยชี้แนะ แนวทางในการแก้ปัญหาของโปรแกรม excel ให้ด้วยครับ คือว่าผมใช้โปรแกรมของ power vision แต่ต้องการ export file ออกมาเป็น excel เพื่อที่จะหา 1. อายุงานของพนักงาน (Sort หาค่าเฉลี่ยของอายุงาน) 2. อายุตัวของพนักงาน (เพื่อหาคนที่ใกล้จะเกษียณอายุงาน) แต่เนื่องจาก file ที่ export ออกมา มี format ค่อนข้างจะแปลกๆ ครับ ผมได้ลองใช้ function ต่างๆ ตามที่อาจารย์ได้แนะนำ ก็ยังไม่สามารถทำได้ครับ จึงขอความกรุณาอาจารย์ ช่วยชี้แนะด้วยครับ ขอบพระคุณเป็นอย่างสูง นายประสิทธิ์ชัย xxxxxxxxxx 086-xxx-xxxx หมายเหตุ : การตั้งค่าวันที่ของเครื่อง เป็นภาษาไทยครับ

ภาพที่ 1 แสดงข้อมูลตัวอย่างที่ export ออกมาจากโปรแกรมของ Power Vision

จากข้อมูลในภาพที่ 1 จะเห็นว่า รูปแบบอายุงานและอายุตัว ของพนักงานแต่ละคน จะอยู่ในรูปแบบเดียวกับเวลา (time format) ซึ่งถ้าให้ใครมาดูข้อมูลนี้ โดยไม่มีหัวคอลัมน์กำกับ ร้อยทั้งร้อยก็คงเดาไม่ออกว่านี่คือ อายุ เช่น 7:02 หมายถึง อายุงาน 7 ปี 2 เดือน หรือ 29:04:00 ก็หมายถึง อายุ 29 ปี 4 เดือน นั่นเอง

ปัญหาหนึ่งที่ผมเคยพบเห็นบ่อยๆ ในหลายๆ โปรแกรมที่เป็นสัญชาติไทยก็คือ การใช้ user interface ที่ไม่เป็นสากล ต่างคนต่างกำหนดรูปแบบการใช้งานเป็นของตัวเอง ไม่มีมาตรฐานเหมือนกับซอฟต์แวร์ต่างประเทศ ตัวอย่างง่ายๆ ที่เราคุ้นเคยกันมาแต่ไหนแต่ไรก็คือ ปุ่ม F1 กดเมื่อต้องการความช่วยเหลือ (help), ปุ่ม F10 หรือปุ่ม Alt กดเมื่อต้องการใช้งานคำสั่งบนเมนูบาร์, Ctrl + C คือการคัดลอก (copy), Ctrl + V คือการวาง (paste), Ctrl + X คือการตัด (cut), Ctrl + P คือการเปิดหน้าต่างเพื่อสั่งพิมพ์ ฯลฯ เป็นต้น ซึ่งซอฟต์แวร์ทั่วไป จะใช้มาตรฐานนี้เหมือนกัน

แต่โปรแกรมไทยหลายตัว เช่น โปรแกรมระบบบัญชี โปรแกรมระบบเงินเดือน และอื่นๆ จะใช้วิธีการที่แตกต่างไป เช่น บางเจ้า กดปุ่ม F1 เพื่อเรียกเมนู, บางเจ้ากดปุ่ม F10 เพื่อจบการทำงาน, บางเจ้า กดปุ่ม F12 เพื่อขอความช่วยเหลือ, ฯลฯ เหล่านี้เป็นต้น และที่ร้ายไปกว่านั้นก็คือ หลายๆ คำสั่งไม่มีการกำหนด keyboard shortcuts ต้องใช้เมาท์เพียงอย่างเดียว ถ้าเมาท์เสีย ก็คือหยุดงาน

จากข้อมูลตัวอย่างนี้ก็เช่นกัน รูปแบบของอายุงานที่ส่งออกมาจากโปรแกรมสำเร็จรูปดังกล่าว มีรูปแบบที่ไปซ้ำซ้อนกับ format time ที่เป็นมาตรฐาน ทำให้การนำข้อมูลนั้นไปใช้งานต่อในทันที ทำไม่ได้ ต้องได้รับการปรับปรุงให้อยู่ในรูปแบบที่ถูกต้องเสียก่อน อีกทั้งยังอาจจะสื่อความหมายที่ผิดพลาดด้วย ซึ่งเมื่อใครมาดูก็จะเข้าใจโดยปริยายว่าเป็นข้อมูล เวลา ผมเชื่อว่าไม่มีใครบอกว่าเป็น อายุ หรอก ยกเว้นคนที่เขียนโปรแกรมเอง แต่ถ้าคนเขียนโปรแกรมนั้น ไม่รู้ว่านี่เป็นข้อมูลที่ออกมาจากโปรแกรมของเขา เขาก็อาจจะคิดว่าเป็นเวลาเช่นเดียวกับคนอื่นๆ

มาทำความเข้าใจเรื่องรูปแบบ date, time ใน Excel กันก่อน

โดยปกติแล้ว ใน Excel จะใช้รูปแบบวันที่เป็นปี ค.ศ. ส่วนจะใช้รูปแบบเป็น dd/mm/yyyy หรือ mm/dd/yyyy ขึ้นอยู่กับการตั้งค่าของแต่ละเครื่อง ให้ตรวจสอบโดยการพิมพ์ =TODAY() ณ เซลล์ใดๆ ก็จะได้รูปแบบวันที่ปกติของเครื่องนั้นๆ

ดังนั้น การที่เราป้อนข้อมูลเข้าไป เช่น 15/10/2550 Excel จะตีความหมายว่า 15 พฤศจิกายน ค.ศ.2550 ซึ่งถ้าเราเอาไว้ดูเฉยๆ เป็นที่เข้าใจเอง ก็คงไม่เป็นไร แต่ถ้ามีการนำไปคำนวณใดๆ ต่อ อาจจะเกิดความผิดพลาดขึ้นได้ เช่น ปี พ.ศ.2550 กับ ค.ศ.2550 เดือนกุมภาพันธ์ มีจำนวนวันเท่ากันหรือไม่, หรือ 15 พฤศจิกายน พ.ศ.2550 กับ 15 พฤศจิกายน ค.ศ.2550 ตรงกับวันในสัปดาห์เหมือนกันหรือไม่ ฯลฯ ซึ่งสิ่งเหล่านี้จะมีผลต่อการเขียนสูตรคำนวณอย่างแน่นอน

นอกจากนี้ระบบวันที่ใน Excel ที่ใช้กับ PC จะอยู่ในรูปแบบของเลขลำดับ โดยให้วันที่ 1 มกราคม ค.ศ.1900 เป็นวันที่ลำดับที่หนึ่ง (หากต้องการดูว่าวันที่ปัจจุบันเป็นวันที่ลำดับที่เท่าไหร่ ก็ให้พิมพ์ =TODAY() ณ เซลล์ใดๆ ก็ได้ เสร็จแล้วไปเปลี่ยนรูปแบบเซลล์ (format cell...) ให้เป็นแบบทั่วไป (general) ก็จะทราบเลขที่ลำดับของวันที่ปัจจุบัน) ส่วนรูปแบบของเวลาก็จะเป็นทศนิยมของรูปแบบวันที่อีกทีหนึ่ง โดย 1 ชม. จะเท่ากับ 1/24 วันนั่นเอง เช่น 39408 คือลำดับที่ของวันที่ 22 พฤศจิกายน ค.ศ.2007 และ 39408.25 ก็คือ วันที่ 22 พฤศจิกายน ค.ศ.2007 เวลา 6:00 นาฬิกา นั่นเอง (1/4 วัน หรือ 0.25 คือ 6 ชม.)

ที่ต้องกล่าวถึงเรื่องนี้ก็เพราะว่า บางครั้งที่เราเห็นรูปแบบวันที่ หรือรูปแบบเวลาใน Excel นั้น ค่าจริงๆ จะไม่ใช่สิ่งที่เรามองเห็น เพราะนั่นได้รับการจัดรูปแบบมาแล้ว ถ้าอยากรู้ค่าจริงๆ ก็ต้องเปลี่ยนรูปแบบให้เป็นแบบทั่วไปก่อน (general)

คำนวณหาอายุงานด้วยฟังก์ชัน DATEDIF

รูปแบบโครงสร้างของฟังก์ชัน DATEDIF คือ

=DATEDIF(วันที่เริ่มต้น, วันที่สิ้นสุด, รูปแบบการแสดงผล)
การหาอายุงาน หรืออายุตัว เรามักจะหามาถึงวันปัจจุบัน เช่นอยากรู้ว่า ณ วันนี้มีอายุงานหรืออายุตัว กี่ปี กี่เดือน กี่วัน ดังนั้น วันที่สิ้นสุดจึงมักใช้วันปัจจุบันเป็นตัวเปรียบเทียบ โดยทั่วไปจึงใช้ฟังก์ชัน TODAY() แทนวันที่สิ้นสุด ดังนั้น เราจะพิมพ์สูตรที่ H4 ดังนี้

=DATEDIF(D4, TODAY(), "Y")
แต่เนื่องจากวันที่เข้างาน ในคอลัมน์ D อยู่ในรูปแบบปี พ.ศ. ดังนั้น เราจะต้องแปลงให้อยู่ในรูปแบบปี ค.ศ.เสียก่อน โดยใช้ฟังก์ชัน DATE สูตรจึงเปลี่ยนเป็นดังนี้

=DATEDIF(DATE(YEAR(D4)-543,MONTH(D4),DAY(D4)), TODAY(), "Y")
จากสูตรนี้ จะส่งค่าออกมาเป็น "ปี" เท่านั้น หากเราต้องการให้แสดงผลลัพธ์เป็น xx ปี xx เดือน xx วัน ก็ต้องเขียนสูตรยาวสักหน่อย แต่ก็ยังใช้สูตรเดียวกันนี่แหละ เปลี่ยนแต่รูปแบบการแสดงผล แล้วนำสูตรมาเชื่อมต่อกัน ดังนี้

=DATEDIF(DATE(YEAR(D4)-543,MONTH(D4),DAY(D4)),TODAY(),"Y") &" ปี "& DATEDIF(DATE(YEAR(D4)-543,MONTH(D4),DAY(D4)),TODAY(),"YM") &" เดือน "& DATEDIF(DATE(YEAR(D4)-543,MONTH(D4),DAY(D4)),TODAY(),"MD")+1 &" วัน"

หมายเหตุ : สูตรนี้ต้องเขียนต่อกันในบรรทัดเดียวกันทั้งหมด แล้วก็คัดลอกสูตรลงมาทุกบรรทัด


ภาพที่ 2 แสดงผลลัพธ์ที่ได้จากสูตรคำนวณ

สำหรับการหาอายุตัว จริงๆ แล้วก็ใช้สูตรเดียวกันได้ แต่ก็มีปัญหาอีกว่า ในข้อมูลตัวอย่าง ไม่มีวันเกิดมาให้ มีแต่วันเข้างาน ซึ่งเราคำนวณหาอายุงานไปแล้ว

แต่ก็มีข้อมูลในคอลัมน์ F ที่เดาว่า น่าจะเป็นอายุตัว (ถึงแม้หน้าตาของข้อมูลจะเหมือนเวลาอะไรสักอย่างก็ตาม) ผมก็จะเอาตัวเลขในคอลัมน์ F นี่แหละ มาแปลงเป็นอายุ ให้รูปแบบเหมือนกับอายุงาน

ลองคลิกดูข้อมูลในคอลัมน์ F สังเกตุที่แถบสูตรด้านบน จะเห็นว่าข้อมูลอยู่ในรูปแบบวันที่ และมีเวลาพ่วงท้ายมาด้วย เช่น 1/1/1900 18:01:00 ซึ่งถ้าเราลองเปลี่ยนรูปแบบเซลล์ให้เป็น general ก็จะกลายเป็น 1.75069444444444 ตรงนี้แหละคือสิ่งที่ผมพูดถึงข้างต้น เกี่ยวกับระบบวันที่ของ Excel (1 วันมี 24 ชม. 1.75 วันก็คือ 24+18 = 42 ชม.) เพราะฉะนั้น ที่เราเห็นว่า 42:01:00 จึงเท่ากับ 1.75069444444444 (ที่ไม่ลงตัวเพราะมีเศษเกินมา 1 นาที)

ดังนั้นถ้าจะแปลงร่าง 1.75069444444444 ให้กลายเป็น 42 ปี 1 เดือน ในเซลล์ I4 จึงต้องใช้สูตรดังนี้

=INT(F4*24)&" ปี "&MID(F4*24*60,4,2)&" เดือน"
เสร็จแล้วก็คัดลอกสูตรลงมาทุกบรรทัด ก็จะได้ผลลัพธ์ดังในภาพที่ 2 นั่นเอง

ทั้งหมดนี้ เป็นตัวอย่างการแก้ปัญหาที่เกิดจากรูปแบบข้อมูล ที่ไม่เป็นไปตามมาตรฐาน สำหรับท่านที่สนใจไฟล์ตัวอย่างประกอบเรื่องนี้ ให้คลิกดาวน์โหลดได้ที่
//www.e-hrit.com/samroeng/download/age_calculation.xls

Create Date : 22 พฤศจิกายน 2550 Last Update : 1 กุมภาพันธ์ 2552 10:44:47 น. Counter : 6396 Pageviews.		17 comments
Share Tweet

	สปอร์ตไลท์ High pressure VS สปอร์ตไลท์ LED สมาชิกหมายเลข 6679443 (28 ต.ค. 2564 11:05:44 น.)		AI เพื่อร้านอาหาร : "นาทีนี้" ต้อง -->EatLab<-- peaceplay (16 ต.ค. 2564 13:49:03 น.)
	SpaceX, BlueOrigin :ธุรกิจอวกาศจะเป็นเรื่องที่จับต้องได้ง่ายขึ้น (วิดีโอจาก SPACETH.CO) peaceplay (6 ต.ค. 2564 13:52:03 น.)		รีวิวเกม Fall Guys ชุลมุนวุ่นวายอะไรอย่างนี้  ออโอ (19 ก.ย. 2564 15:25:19 น.)