วิธีใช้ 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, หรือหาค่าระยะเวลาระหว่างต้นและปลายได้ด้วย
   • Push หมายถึง ผลักไปข้างหน้า ใช้คำนวณหากำหนดการสิ้นสุดในอนาคต โดยใช้ข้อมูลจากกำหนดการเริ่มต้นและระยะเวลาที่ใช้จนเสร็จ
   • Pull หมายถึง ดึงมาข้างหลัง ใช้คำนวณหากำหนดการเริ่มต้นในปัจจุบัน โดยใช้ข้อมูลจากกำหนดการสิ้นสุดและระยะเวลาที่ใช้จนเสร็จ

ตารางกำหนดการแบบง่าย

• Range Name ที่เกี่ยวข้อง
  • Date =G2:R2
  • Start =C3:C20
  • Stop =D3:D20
  • Cycle =E3:E20
  • 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 ช่วยในการวางแผนกำหนดการผลิต และมีตัวอย่างให้ download ได้ด้วย

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))}

วิธีใช้ Function VBA สั้นๆแทนสูตร Array

1. Download SchedulingV2.xla นำมาลงใน folder ใดก็ได้
2. สั่ง Excel Options > Add-Ins > Go > Browse หา SchedulingV2.xla จะพบว่า เกิดกาช่อง Expert Scheduling with VBA Function
3. เรียกดูหรือใช้สูตรที่เกิดจากการใช้ Function ได้โดยกดปุ่ม Fx หรือดูจาก Help ในกลุ่มสูตรชื่อ 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 ช่วยทำให้ผู้ใช้สามารถคลิกเลือกเดือนและปีเปลี่ยนแปลงได้ตามต้องการ
• ควรใช้คำสั่ง Format > Conditional Formatting แบบ Formula is ปรับเปลี่ยนสีเซลล์ให้กับวันที่ในปฏิทินซึ่งมีกิจกรรมการทำงาน

 ชมวิดีโอได้จาก

https://www.excelexperttraining.com/book/index.php/excel-articles-and-videos/excel-expert-management/scheduling-vdo