กรณีศึกษา : การคำนวณหาอายุ จากข้อมูลที่ 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 17 comments Last Update : 1 กุมภาพันธ์ 2552 10:44:47 น. Counter : 7762 Pageviews. Share Tweet

แวะมาทักทาย ขอบคุณที่ไปแวะเยี่ยมและให้กำลังใจค่า จะเอากำลังใจไปทุ่มเทให้กับนักศึกษาอย่างเต็มที่แน่นอนค่ะ   โดย: rubber ducky 23 พฤศจิกายน 2550 17:08:11 น.

โดย: NaNaNong IP: 203.146.12.206 23 พฤศจิกายน 2550 17:30:12 น.

こんばんわ！！ こいびと　が　ないから、今日　は　さびしい　です。 Burapha 大学へ　いきます。でも...　Loyｋｒaｔhong を しません。 อุตส่าห์แวะไปหาขอบคุณมากค่า ตอนนี้เห็นพวกข้อมูลแล้วเวียนหัว เพราะเรียนวิจัยอยู่ค่ะ ภาษาญี่ปุ่นก็ไม่เก่งเท่าไรหรอกค่ะพอถูๆไถๆ เพราะเรียนเอกนิเทศค่ะ อิอิ ども　ありがとう　ございます。   โดย: simple_xxx 25 พฤศจิกายน 2550 1:10:04 น.

หวาดดี msn มันให้ ดาวโหลด อะไรก็ไม่รู้ นานมาก 2 ชั่วโมงยังไม่เสร็จเลย เปิด ก็ไม่ได้ ทำไงดี   โดย: may (may-momo ) 25 พฤศจิกายน 2550 11:35:04 น.

ども　ありがとう　ございます นี่ขนาดไม่เก่งนะเนี่ย อ่านออกเขียนได้ ก็สุดยอดแล้วครับ เสียดายที่แป้นพิมพ์ผมยังไม่ได้ลงภาษาญี่ปุ่น จะได้รื้อฟื้นภาษาซักหน่อย จะพิมพ์เป็นโรมันจิ ก็ไม่ค่อยคุ้นเคย ขอบคุณที่แวะมาเยี่ยมครับ   โดย: ครูเอก 25 พฤศจิกายน 2550 12:22:48 น.

สวัสดีครับคุณครูเอก พึ่งเจอว่าเขียนบทความเกี่ยวกับ Excel ด้วย ดีครับจะได้มีพลพรรคเอาไว้คอยปรึกษาปัญหา excel เยอะๆ แล้วจะแวะมาอ่านอีกนะครับ thannoy ว่าแต่ว่าขอถามซักคำถามนะครับ รู้ไหมว่า ถ้าเปรียบ excel เป็นคนจะเป็นเพศอะไรครับ หญิง หรือ ชาย?   โดย: แดนน้อย IP: 202.91.19.206 27 พฤศจิกายน 2550 21:49:07 น.

ขอบคุณมากค่ะได้นำไปใช้จริง Amazing ค่ะ เพราะก่อนหน้านี้ใช้คณิตศาสตร์พื้นฐานในการคิด ค่าออกมาใกล้เคียง แต่ยังไม่ถูก 100% พอได้มาเจอ Blog นี้เข้า ก็ต้องขอบอกค่ะว่า แจ๋วจริง ขอบคุณในความใจดี มีน้ำใจค่ะ   โดย: search....Search...... IP: 58.137.1.154 7 มกราคม 2551 16:41:18 น.

อยากทรายสูตรการคำนวณวันที่เป็นจำนวนวันค่ะรบกวนอ.เอกด้วยนะค่ะขอบคุณค่ะ   โดย: ขิม IP: 124.157.228.133 5 มีนาคม 2551 16:40:47 น.

การคำนวณวันที่เป็นจำนวนวัน ง่ายมากครับ เพียงแค่เอาวันที่สองค่ามาลบกันธรรมดา เช่น เข้างานวันที่ 1/2/2008 อยากรู้ว่าจนถึงวันนี้ (6/3/2008) มีอายุงานกี่วันแล้ว ก็เอา 6/3/2008-1/2/2008 และบวกเพิ่มอีก 1 ครับ ถ้าหากผลลัพธ์ออกมาเป็นรูปแบบวันที่ ก็จัดรูปแบบเซลให้เป็น General ก็จะได้จำนวนวัน สาเหตุที่ต้องบวกเพิ่มอีก 1 เพราะเอ็กเซลบวกลบวันที่เหมือนกับคณิตศาสตร์ ซึ่งไม่นับวันตั้งต้น เราจึงต้องบวก 1 เพื่อให้นับวันตั้งต้นด้วย อธิบายง่ายๆ คือ ถ้าทำงานวันที่ 1 เสร็จวันที่ 5 ถามว่าทำงานกี่วัน เราก็คงตอบว่า 5 วัน แต่เอ็กเซลเอา 5-1 ได้ผลลัพธ์เป็น 4 เราจึงต้องบวกกลับเข้าไปอีก 1 ครับ   โดย: ครูเอก 6 มีนาคม 2551 2:14:40 น.

ขอบคุณนะคะอ.เอก รบกวนอีกนะคะคือว่าถ้าวันที่เราใส่เป็นจุดเช่น 06.12.16กับ 18.12.46 สามารถนำมาลบกันได้ไหมคะ   โดย: ขิม IP: 124.157.228.208 7 มีนาคม 2551 15:48:37 น.

ตอบคุณขิม ต้องทำให้เป็นรูปแบบวันที่ก่อน แล้วจึงค่อยนำไปคำนวณครับ เช่น ที่ A1 มีข้อมูล 06.12.16 ที่ B1 พิมพ์สูตร =DATE("20"&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)) เพื่อแปลงค่าใน A1 ให้เป็นรูปแบบวันที่   โดย: ครูเอก 10 มีนาคม 2551 14:20:15 น.

ต้องทำให้เป็นรูปแบบวันที่ก่อน แล้วจึงค่อยนำไปคำนวณครับ เช่น ที่ A1 มีข้อมูล 06.12.16 ที่ B1 พิมพ์สูตร =DATE("20"&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)) เพื่อแปลงค่าใน A1 ให้เป็นรูปแบบวันที่ "20"หมายถึงอะไรคะ LEFT(A1,2) MID(A1,4,2) RIGHT(A1,2)   โดย: ขิม IP: 124.157.228.208 11 มีนาคม 2551 9:45:12 น.

ตอบคุณขิม (อีกครั้งหนึ่ง) ฟังก์ชันที่แนะนำทั้งหมดนี้ เป็นฟังก์ชันพื้นฐานของพื้นฐานเลยครับ ผมเชื่อว่า 99% ของผู้ที่เรียน Excel ต้องได้เรียนแน่นอน ดังนั้น ผมขออนุญาตเล่นตัว โดยไม่ขออธิบายเรื่องนี้ครับ แต่ถ้าคุณอยากรู้ว่าฟังก์ชันแต่ละตัวนั้น ส่งผลลัพธ์อย่างไรออกมา ให้ทดลองดังนี้ ที่ A1 พิมพ์ 06.12.16 ที่ A2 พิมพ์ =LEFT(A1,2) ที่ A3 พิมพ์ ="20"&LEFT(A1,2) ที่ A4 พิมพ์ =MID(A1,4,2) ที่ A5 พิมพ์ =RIGHT(A1,2) แล้วสังเกตผลลัพธ์ที่ได้ จากนั้นให้ลองเปลี่ยนข้อมูลใน A1 ให้เป็นข้อความอื่นดูบ้าง เช่นพิมพ์คำว่า Computer ลงไปแทนข้อความใน A1 แล้วสังเกตผลลัพธ์ที่ได้ เรียนเอ็กเซลมันต้องปฏิบัติไปด้วยครับ อ่านอย่างเดียวไม่ได้   โดย: ครูเอก 11 มีนาคม 2551 19:32:25 น.

ขอบคุณคะ   โดย: ขิม IP: 124.157.228.208 12 มีนาคม 2551 12:47:54 น.

ขอบคุณครับ _/\\_   โดย: ตูดเย็น IP: 61.90.192.174 22 เมษายน 2551 15:57:42 น.

มีคู่มือการใช้โปรแกรม Power vision หรือเปล่าครับ พอดีทำงานเกี่ยวกับโปรแกรมนี้แต่ยังไม่ชำนาญเลย ก็เลยอยากจะหาคู่มือลองอ่านดู ไม่ทราบว่าจะหาได้จากที่ไหนครับ (sittichai_seemee@hotmail.com)   โดย: สิทธิ์ IP: 192.168.2.132, 119.42.74.138 15 มีนาคม 2555 15:22:23 น.

ก็ใช้ โปรแกรมนี้เหมือนกัน โปรแกรมเค้ามี function export อายุงานด้วย แต่ถ้า export แล้วเจอปัญหารูปแบบนี้ เคยโทรไปสอบถามทาง support แจ้งว่าให้ทำการ export เป็นรูปแบบของ text ก่อน แล้วค่อนแปบงเป็น excel เราลองดูแล้ว o มากอะ ถาม supportมาหลายคนแระ แต่ไม่ได้คำตอบ บังเอิญล่าสุดชคดีไปเจอsupport ตัวแม่ เออ รูปแบบ format ของเค้าจะเป็น ปี :เดือน:วัน ตาม system date อะ   โดย: ทางผ่าน IP: 1.47.238.9 22 มิถุนายน 2555 0:20:24 น.