การคำนวณค่าใน excel

การคำนวณค่าต่างๆใน excel

ให้นิสิตหาค่าต่างๆ ในการคำนวณด้วยโปรแกรม excel เพื่อนำมาใช้ในการหาค่าต่างๆในงานประวัติศาสตร์

[Beaukhew]

วิธีใช้ Excel วางแผนกำหนดการงานวางแผนเป็นงานซึ่งเกี่ยวข้องกับกำหนดการ ต้องใช้ข้อมูลที่มีอยู่หาทางมองย้อนกลับไปในอดีต วิเคราะห์ตัวเลขรายเดือนในปีปัจจุบัน หรือพยากรณ์ยอดขายในอนาคต ไม่ว่าจะเป็นการวางแผนแบบหยาบๆง่ายๆ ที่เกี่ยวข้องกับการใช้ชีวิตประจำวัน ตั้งแต่การคำนวณหาวันครบกำหนดทดลองงาน วันครบกำหนดไถ่ถอน วันที่กำหนดนัดหมาย หรือใช้คำนวณละเอียดถึงตัวเลขของเวลาและระยะเวลาเป็นชั่วโมง นาที หรือวินาที ไม่ว่าจะมีเงื่อนไขเกี่ยวข้องกับวันหยุดหรือระยะเวลาหยุดงานแทรกหรือไม่ หรือจะใช้คำนวณถึงขั้นสลับซับซ้อน ใช้คำนวณหาอัตราผลผลิต หรือใช้วางแผนแบบ Just-in-Time ก็ตาม ไม่ว่าปัญหาจะเป็นเช่นไร เราสามารถนำ Excel มาประยุกต์ใช้วางแผนได้ดีกว่าและยืดหยุ่นกว่าโปรแกรมสำเร็จรูปที่หาซื้อมาเสียอีกลองพิจารณาปัญหาต่อไปนี้ดูกันหน่อย เชื่อว่าหลายๆปัญหาต้องเป็นปัญหาที่คุณกำลังพบอยู่ 1.       ต้องการคำนวณหายอดเงินที่เก็บได้จากลูกค้า ลูกค้าบางคนมีกำหนดการจ่ายชำระตั้งแต่วันที่ 2 ของเดือน บางคนจ่ายชำระมานานแล้วกำลังจะสิ้นสุดภายในวันที่ 9 นี้ บางคนจ่ายชำระแบบวันเว้นวัน จะคำนวณหายอดรายรับแต่ละวันได้อย่างไร 2.       บริษัทกำหนดแผนการผ่อนชำระ ให้ลูกค้าสามารถเลือกผ่อนแผนใดก็ได้ แถมยังมีทางเลือกพิเศษให้ลูกค้ากำหนดอัตราเงินผ่อนได้เองเสียอีก เราจะสร้างสูตรเพื่อกระจายยอดเงินผ่อนชำระได้อย่างไร แล้วที่ว่าเปิดโอกาสให้ลูกค้ากำหนดอัตราเงินผ่อนได้เองนั้น จะใช้ Excel สร้างตารางเลือกการผ่อนชำระได้ด้วยหรือ 3.       ในวันหนึ่งๆ มีกำหนดการทำงานตั้งแต่ 8:00 - 17:00 น. ส่วนนอกเวลาที่กำหนดนั้น ให้ถือว่าเป็นช่วงพิเศษ ต้องจ่ายค่าแรงอีกอัตราหนึ่ง เราจะสร้างตารางคำนวณค่าแรงได้อย่างไร 4.       บริษัทวางแผนจัดตารางการทำงานของพนักงานเป็นกะ และกำลังจะนำ Key Performance Index มาใช้วัดว่า ผลงานซึ่งทำได้ในแต่ละกะนั้น ตรงตามมาตรฐานที่กำหนดไว้หรือไม่ จะคำนวณหามาตรฐานของผลผลิตซึ่งควรผลิตได้อย่างไร 5.       ต้องการสร้าง Gantt Chart เพื่อแสดงช่วงเวลาที่ใช้ในการทำงานต่อเนื่องกันแต่ละ Job แต่ไม่อยากใช้ Chart ของ Excel เพราะรู้มาว่ายุ่งยากมาก จะใช้ตารางธรรมดาๆแต่ให้มี Gantt Chart แสดงขึ้นมาได้อย่างไร 6.       บริษัทมีกำหนดเวลาหยุดพัก ในช่วงวันหนึ่งๆเป็นช่วงๆ กำลังวางแผนให้พนักงานทำงานให้เสร็จไม่เกิน 17:00 น. โดยให้ทำงานได้ไม่เกินคนละ 3 ชั่วโมง จะต้องกำหนดให้พนักงานเริ่มงานเวลาใด ทั้งนี้ระยะเวลา 3 ชั่วโมงซึ่งให้ใช้ในการทำงานนั้น ไม่ได้รวมช่วงเวลาหยุดพักระหว่างวันไว้ด้วย 7.       บริษัทมีกำหนดการหยุดงานประจำปีและอาจมีวันหยุดพิเศษแทรกเมื่อใดก็ได้ จะทราบได้อย่างไรว่า งานซึ่งกำหนดให้เริ่มต้นเมื่อกลางเดือนที่ผ่านมา และใช้เวลาทำงาน 100 วัน ควรจะมีกำหนดเสร็จในวันใด 8.       ฝ่ายบุคคลจะคำนวณค่าแรงให้พนักงานแต่ละคนให้ง่ายที่สุดได้อย่างไร โดยคำนึงถึงวันหยุด วันลากิจ วันลาป่วย และวันหยุดตามปกติ ของพนักงานแต่ละคนซึ่งแตกต่างกันไป 9.       ไม่ว่าจะมีปัญหาแบบใดดังกล่าวข้างต้น เมื่อคำนวณได้ผลลัพธ์ตามต้องการได้แล้ว ยังต้องการสร้างเป็นตารางปฏิทินแสดงให้เห็นช่วงเวลาซึ่งใช้ในกิจกรรมนั้นๆด้วย ก่อนการใช้ Excel วางแผนกำหนดการใดๆ เราควรให้คำจำกัดความกับคำที่เกี่ยวข้องการเริ่มต้นและสิ้นสุดแผนงานให้ชัดเจนเสียก่อนว่า หากกำหนดให้เริ่มต้นงาน วันที่ 1 แล้วสิ้นสุดงานวันที่ 5 นั้น ถ้านำค่าวันเดือนปีและเวลามาบันทึกลงไปในเซลล์แล้ว ที่ว่าเริ่มต้นงานวันที่ 1 นั้น ถือว่าเริ่มต้นเวลาใด หรือจะให้ถือกันแค่วันที่ 1 เฉยๆโดยไม่ต้องใส่กับเวลา ส่วนคำว่าสิ้นสุดงานวันที่ 5 นั้นเล่า ถือว่าวันที่ 5 ยังทำงานอยู่ไหม หรือในวันที่ 5 ยังทำงานอยู่และจะทำงานไปจนถึงเวลาใดของวันที่ 5คำเกี่ยวข้องกับกำหนดการซึ่งควรตีความให้ชัดเจนก่อน ได้แก่ เริ่มต้น สิ้นสุด จาก ถึง จบ หยุด หรือคำภาษาอังกฤษ เช่น Start Stop Begin End From To เป็นต้น แต่ละบริษัทอาจตีความไม่เหมือนกันข้อควรคำนึงในการใช้ Excel วางแผนกำหนดการ1.       ผู้วางแผนต้องมีพื้นฐานเรื่องการใช้วันที่และเวลาของ Excel สามารถใช้ค่า Date Serial Number(SN) ในการคำนวณ และรู้จักเลือกใช้รูปแบบการแสดงผล 2.       ผู้ใช้แฟ้มงาน ซึ่งอาจไม่ใช่คนเดียวกับผู้ที่ใช้ Excel สร้างสูตรสร้างตารางกำหนดการ มีพื้นฐาน Excel มากน้อยเพียงไร อย่างน้อยต้องเข้าใจความแตกต่างระหว่างค่าที่แท้จริงและค่าที่แสดงตามรูปแบบ เพราะตัวเลขของวันที่ 14 ซึ่งมองเห็นในเซลล์ อาจไม่ใช้เลข 14 แต่เป็นตัวเลข SN มีค่าเป็นหลักหมื่นซึ่งผ่านการปรับรูปแบบ dd จึงเห็นเฉพาะเลข 14 เท่านั้น 3.       เลือกวิธีบันทึกค่าเริ่มต้นให้เหมาะกับลักษณะการทำงาน เช่น จำเป็นต้องแยกบันทึก 14/2/2004 ออกเป็นเซลล์ 3 เซลล์ เพื่อแยกส่วนของวัน เดือน และปีออกจากกันเป็น 14 และ 2 และ 2004 หรือสะดวกที่จะบันทึกพร้อมกันลงไปในเซลล์เดียว 4.       เมื่อคำนวณเสร็จแล้วต้องการให้แยก 14/2/2004 ออกแต่ละส่วน เช่นเดียวกับข้อควรคำนึ่งข้างต้นหรือไม่ 5.       ต้องการคำนวณแบบสูตรเดียวให้เกิดผลลัพธ์ที่ต้องการเลย ซึ่งจะใช้สูตรยากกว่า หรือจะใช้ตารางช่วยคำนวณทีละขั้น 6.       สูตรซึ่งใช้ในการคำนวณนั้น สูตรเดิมสูตรเดียวต้องสามารถดัดแปลงให้ใช้กับงานหลายเงื่อนไข มิใช้ว่าต้องใช้สูตรซ้อนสูตรให้ยาวเข้าไปจึงจะคำนวณได้ หรือต้องสร้างสูตรใหม่เฉพาะแต่ละเงื่อนไข 7.       การวางแผนกำหนดการที่ดีต้องสามารถคำนวณแบบ Push, Pull, หรือหาค่าระยะเวลาระหว่างต้นและปลายได้ด้วย o        Push หมายถึง ผลักไปข้างหน้า ใช้คำนวณหากำหนดการสิ้นสุดในอนาคต โดยใช้ข้อมูลจากกำหนดการเริ่มต้นและระยะเวลาที่ใช้จนเสร็จ

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

 ตารางกำหนดการแบบง่าย ·         Range Name ที่เกี่ยวข้อง o        Date =G2:R2 o        Start =C3:C20 o        Stop =D3:D20 o        Cycle =E3:E20 o        Amount =F3:F20 ·         กรณีกำหนดให้ยอด Amount เกิดขึ้นตั้งแต่ Start
ใช้สูตร =IF(Date>=Start,Amount,0) ·         กรณีกำหนดให้ยอด Amount เกิดขึ้นไม่เกิน Stop
ใช้สูตร =IF(Date<=Stop,Amount,0) ·         กรณีกำหนดให้ยอด Amount เกิดขึ้นในช่วงตั้งแต่ Start จนถึง Stop
ใช้สูตร =IF(AND(Date>=Start,Date<=Stop),Amount,0) ·         กรณีกำหนดให้ยอด Amount เกิดขึ้นนอกช่วงตั้งแต่ Start จนถึง Stop
ใช้สูตร =IF(OR(Date<Start,Date>Stop),Amount,0) ·         กรณีกำหนดให้ยอด Amount เกิดขั้นในช่วงตั้งแต่ Start จนถึง Stop
และให้เกิดขึ้นทุกระยะเวลาของ Cycle
ใช้สูตร
=IF(AND(Date>=Start,Date<=Stop,
OR(MOD(Date-Start+1,Cycle)=1,Cycle=1)),Amount,0) ·         เคล็ดของการคำนวณให้ยอด Amount เกิดขึ้นทุกระยะเวลาของ Cycle แล้วยังสามารถขยับตามวันที่เริ่ม Start นั้น อยู่ที่สูตร Date-Start+1 ซึ่งแทรกในสูตร MOD(Date-Start+1,Cycle)=1 โดยเป็นสูตรซึ่งทำหน้าที่เปลี่ยนวันที่ Date เดิมให้เป็น Date ใหม่ ทำให้ Date เดิม ไม่ว่าเป็นวันที่ใด แต่หากตรงกับวันที่เริ่ม Start จะถูกคำนวณเปลี่ยนเป็นวันที่ 1 เสมอ แล้วทำให้วันที่ถัดไปกลายเป็นวันที่ 2, 3, 4 เพิ่มขึ้นที่ละ 1 เรื่อยไป ·         Gantt Chart สามารถสร้างลงในตารางแทนที่จะเสียเวลาสร้างด้วยกราฟ โดยสั่ง Format > Conditional Formatting > Formula is =$F$19 เป็นเงื่อนไขให้เปลี่ยนสีเซลล์ต่อเมื่อสูตรคำนวณได้ค่าเท่ากับ Amount  ตารางกำหนดการแบบกระจายสัดส่วนตัวอย่างนี้ลูกค้าสามารถเลือกวิธีผ่อนชำระได้ตามใจ หรือจะกำหนดอัตราการผ่อนชำระเองเลยก็ยังได้ โดยใช้สูตร=IF(G$11>=$E5, $F5*INDEX( PayTBL, $D5, G$11-$E5+1),0)PayTBL คือตารางเก็บอัตราการผ่อนชำระ G12:O14เคล็ดของสูตรนี้อยู่ที่การนำสูตร Index เข้ามาร่วมใช้ในการดึงตัวเลขอัตราการผ่อนชำระขึ้นมาคูณกับยอดราคาสินค้า และใช้สูตร G$11-$E5+1 ช่วยกำหนดเลขที่ Column ตารางคำนวณหาระยะเวลาที่ใช้ กรณีไม่หยุดพักตัวอย่างนี้ใช้สูตร =MAX( 0, MIN( F$7, $D10 ) - MAX( F$6, $C10 ) ) เพียงสูตรเดียว ช่วยคำนวณหาเวลาที่ใช้ในแต่ละกะ เช่น Job 1 เริ่มต้นงานตั้งแต่ 7:00 - 12:00 น. เมื่อแยกคำนวณหาเวลาที่ใช้ในกะแรกและกะที่สองด้วยสูตรดังกล่าว จะได้ระยะเวลาที่ใช้ในแต่ละกะเท่ากับ 2 ชั่วโมง และ 3 ชั่วโมง ตามลำดับ สูตรคำนวณหาระยะเวลานี้ให้จำสั้นๆว่า
= Min ปลาย - Max ต้น
= Min(ปลายกะ,ปลาย Job) - Max(ต้นกะ,ต้น Job) หากต้องใช้สูตร If จะต้องใช้ If ซ้อน If กันหลายชั้น เพื่อตรวจสอบให้ครบทุกเงื่อนไขของการทำงานซึ่งเป็นไปได้1.       ช่วงเวลาทำงานอยู่ในกะ 2.       ช่วงเวลาทำงานพอดีเท่ากับกะ 3.       ช่วงเวลาทำงานอยู่คร่อมก่อนเริ่มกะ 4.       ช่วงเวลาทำงานอยู่คร่อมหลังสิ้นสุดกะ 5.       ช่วงเวลาทำงานอยู่คร่อมทั้งก่อนเริ่มกะและหลังสิ้นสุดกะ 6.       ช่วงเวลาทำงานอยู่ในช่วงเกิดก่อนเริ่มกะ 7.       ช่วงเวลาทำงานอยู่ในช่วงเกิดหลังสิ้นสุดกะ จะเห็นว่าสูตร = Min ปลาย - Max ต้น ลัดและสั้นกว่าการใช้สูตร If หลายเท่าตัวขอแนะนำเว็บ http://www.production-scheduling.com เว็บนี้ใช้ Excel ช่วยในการวางแผนกำ Push - Pull Scheduling แบบมีตารางหยุดพักหากช่วงเวลาซึ่งวางแผนการทำงาน มีช่วงเวลาหยุดพักแทรก เช่น พักในช่วงเวลาระหว่างวัน หรือมีกำหนดวันหยุดประจำปี วันหยุดพิเศษ จะทำให้ต้องใช้สูตรซึ่งยากกว่าเดิม แม้จะหนีไปใช้สูตร Array ตัวสูตรก็ยังยาวมากและยากต่อการทำความเข้าใจ เช่น สูตรคำนวณหาระยะเวลาหยุดพัก
{=SUM((Start<To)*(Stop>From)
*(IF((Stop>=From)*(Stop<=To),Stop,To)
-IF((Start>=From)*(Start<=To),Start,From)))}สูตรคำนวณหาเวลาเสร็จงาน ซึ่งใช้หลัก Push
{=PushStart+PushWrkHours
+SUM((PushStart<To)*((PushStart+PushWrkHours
+SUM((PushStart<To)*((PushStart+PushWrkHours)>From)
*(To-IF((PushStart>=From)*(PushStart<=To),PushStart,From)))
)>From)*(To-IF((PushStart>=From)*(PushStart<=To),PushStart,From)))}สูตรคำนวณหาเวลาเริ่มงาน ซึ่งใช้หลัก Pull
{=PullStop-PullWrkHours
-SUM(((PullStop-PullWrkHours-SUM(((PullStop-PullWrkHours)<To)
*(PullStop>From)*(IF((PullStop>=From)*(PullStop<=To),PullStop,To)-From))
)<To) *(PullStop>From)*(IF((PullStop>=From)*(PullStop<=To),PullStop,To)-From))} หนดการผลิต และมีตัวอย่างให้ download ได้ด้วยวิธีใช้ Function VBA สั้นๆแืทนสูตร Array1.       Download Scheduling.xla จาก http://www.tpa.or.th/xlsiam/general/rscheduling.html นำมาลงใน folder ใดก็ได้ 2.       สั่ง Tools > Add-Ins > Browse หา Scheduling.xla จะพบว่า เกิดกาช่อง Expert Scheduling with VBA Function 3.       เรียกดูหรือใช้สูตรที่เกิดจากการใช้ Function ได้โดยกดปุ่ม Fx หรือจาก Insert > Function > User Defined
ถ้าจำสูตรได้ สามารถพิมพ์สูตรได้เลยตามปกติ รูปแบบสูตร กรณีมีตารางเวลาหยุดพัก ·         Start : กำหนดเริ่มงาน ·         Stop : กำหนดสิ้นสุดงาน ·         From : ตารางกำหนดเริ่มช่วงหยุดงาน ·         To : ตารางกำหนดสิ้นสุดช่วงหยุดงาน ·         WrkHours : ระยะเวลาที่ใช้ ชั่วโมง นาที ·         =BreakInterval(Start,Stop,From,To)
รวมระยะเวลาหยุดงาน ชั่วโมง นาที ·         =WorkStop(Start,WrkHours,From,To)
หากำหนดสิ้นสุดงาน ·         =WorkStart(Stop,WrkHours,From,To)
หากำหนดเริ่มงาน ·         กรณีใช้กับวันที่ หรือวันที่และเวลา ให้เปลี่ยนรูปแบบในเซลล์ โดยไม่ต้องแก้ไขสูตรแต่อย่างใด รูปแบบสูตร กรณีมีตารางวันหยุดและวันหยุดพิเศษ ·         From : วัน เดือน ปี ที่เริ่มงาน รวมวันแรกด้วย ·         To : วัน เดือน ปี ที่งานสิ้นสุด รวมวันสุดท้ายด้วย ·         WeekdayNum : ตารางวันหยุดประจำสัปดาห์ ใส่เลขที่วันในสัปดาห์ซึ่งหยุด
1=Sun 2=Mon … 7=Sat ·         Special Holiday ตารางวันเดือนปีของวันหยุดพิเศษ
ไม่ซ้ำกับวันหยุดประจำสัปดาห์ ·         Option 1 - 6 : ดูตัวอย่างแต่ละรูปแบบสูตร ·         WrkDays : จำนวนวันทำงาน ·         =DaysCount(From,To,WeekdayNum,SpecialHoliday,1)
นับจำนวนวันทำงาน ไม่รวมวันหยุด ·         =DaysCount(From,To,WeekdayNum,SpecialHoliday,2)
นับจำนวนวันหยุดในสัปดาห์ ·         =DaysCount(From,To,WeekdayNum,SpecialHoliday,3)
นับจำนวนวันหยุดพิเศษ ·         {=DaysCount(From,To,WeekdayNum,SpecialHoliday,4)*1}
Array สรุปวันทำงาน ·         {=DaysCount(From,To,WeekdayNum,SpecialHoliday,5)*1}
Array สรุปวันหยุดในสัปดาห์ ·         {=DaysCount(From,To,WeekdayNum,SpecialHoliday,6)*1}
Array สรุปวันหยุดพิเศษ ·         =ToDate(From,WrkDays,WeekdayNum,SpecialHoliday)
หากำหนดวันเดือนปีที่สิ้นสุดงาน ·         =FromDate(To,WrkDays,WeekdayNum,SpecialHoliday)
หากำหนดวันเดือนปีที่เริ่มงาน รูปแบบสูตร กรณีใช้เฉพาะวันหยุดพิเศษ โดยไม่กำหนดวันหยุดประจำสัปดาห์·         =DaysCount(From,To,,SpecialHoliday,1)
นับจำนวนวันทำงาน ไม่รวมวันหยุด ·         =DaysCount(From,To,,SpecialHoliday,3)
นับจำนวนวันหยุดพิเศษ ·         =ToDate(From,WrkDays,,SpecialHoliday)
หากำหนดวันเดือนปีที่สิ้นสุดงาน ·         =FromDate(To,WrkDays,,SpecialHoliday)
หากำหนดวันเดือนปีที่เริ่มงาน รูปแบบสูตร กรณีคำนวณหาวันทำงานก่อนและหลัง
โดยคำนึงถึงวันหยุดพิเศษ ·         =FromDate(From,1,,SpecialHoliday)
หากำหนดวันเดือนปีที่เริ่มงาน ก่อนหน้าวันหยุด ·         =FromDate(To,1,,SpecialHoliday)
หากำหนดวันเดือนปีที่สิ้นสุดงาน ก่อนหน้าวันหยุด ·         =ToDate(From,1,,SpecialHoliday)
หากำหนดวันเดือนปีที่เริ่มงาน หลังวันหยุด ·         =ToDate(To,1,,SpecialHoliday)
หากำหนดวันเดือนปีที่สิ้นสุดงาน หลังวันหยุด วิธีสร้างปฏิทิน·         เซลล์ C4:I4 มีค่าเป็นตัวเลข 1-7 ตามลำดับ จากนั้นกำหนดรูปแบบ วว เพื่อแสดงชื่อย่อของวันในสัปดาห์ ·         เซลล์ C5 สร้างสูตร =IF(B5<>0,B5+1,IF(WEEKDAY(DATE($I$2,$D$2,1))=C4,DATE($I$2,$D$2,1),0)) ลงใน Row ของสัปดาห์แรก แล้ว Copy ไปเซลล์ด้านขวาจนครบ 7 วัน จากนั้นให้สร้างสูตรใน Row ถัดไปเช่น เซลล์ C6 = C5+7 ·         เซลล์ D2 และ I2 ควรใช้คำสั่ง Data > validation > Allow : List ช่วยทำให้ผู้ใช้สามารถคลิกเลือกเดือนและปีเปลี่ยนแปลงได้ตามต้องการ ·         ควรใช้คำสั่ง