Articles

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 1

วิธีคำนวณหาระยะเวลาที่ใช้ทำงานในแต่ละกะ

แทนที่จะสร้างสูตรลัดแต่ซ้อนกันยาวเหยียดในเซลล์เดียว

มาชมทางออกที่ดีกว่า โดยออกแบบตารางช่วยคำนวณทีละขั้น

สูตรหาระยะเวลา = Minปลาย - Maxต้น

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWQjdsQUJyV3FWN00

https://youtu.be/gfbCNbxNZ8o

https://www.facebook.com/ExcelExpertTraining/videos/1961492814120527/

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 2a

จะวางแผนการรับเงินผ่อนชำระแบบรายเดือนได้อย่างไร

ลูกค้าบางรายผ่อนทุกเดือน บางรายผ่อนแบบเดือนเว้นเดือน หรือทุกกี่เดือนก็ได้

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWSnF6Z2RqZko3Yjg

https://youtu.be/m_xtUfgGAsA

https://www.facebook.com/ExcelExpertTraining/videos/1962100414059767/

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 2b

วิธีวางแผนการรับเงินผ่อนชำระแบบเดือนเว้นเดือนหรือทุกกี่เดือนก็ได้

เผยเคล็ดการใช้สูตร Mod หาตำแหน่งเดือนจากตัวเลขที่ไม่มีในตาราง

 

กรณีกำหนดให้ยอด 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 เรื่อยไป

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWSnF6Z2RqZko3Yjg

อ่านคำอธิบายจาก

https://www.excelexperttraining.com/indexextra.html?url=https://www.excelexperttraining.com/extra/xlmgt/scheduling.html

https://youtu.be/bex_Tnh5kcQ

https://www.facebook.com/ExcelExpertTraining/videos/1962309690705506/

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 3

เบื้องหลังของการทำ Financial Feasibility Study หรืองานวางแผนการเงิน

การวางแผน Cash In-Flow ซึ่งสามารถกำหนดรูปแบบรายรับได้หลายแบบ

ไม่จำเป็นต้องรับเท่ากันทุกงวด จะกำหนด Cash In-Flow ตามกำหนดใดก็ได้

โดยใช้สูตร Index ควบคุมกำหนดการ

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWX1B6YkQzaHlJMTQ

https://youtu.be/5pYck8M9uS0

https://www.facebook.com/ExcelExpertTraining/videos/1962668217336320/

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 4

วิธีวางแผนแบบ Just-in-Time เพื่อเตรียมส่งของให้ลูกค้าตรงตามกำหนดเวลา

โดยใช้สูตร Offset ควบคุมกำหนดการให้คิดย้อนหลังจากอนาคตมาปัจจุบัน

เมื่อทำได้จะช่วยประหยัดเงินเป็นแสนเป็นล้าน ไม่ต้องซื้อโปรแกรมสำเร็จรูปมาใช้

หลักการสำคัญ

ใช้สูตร Min IF Array เพื่อบอกตำแหน่งรายการ 

ซึ่งเป็นเคล็ดลับสำคัญทำให้เปลี่ยนโครงสร้างการสั่งซื้อหรือผลิตได้ตามใจ

จากนั้นใช้สูตร Indirect+Address เพื่อหาจำนวนที่ต้องการไปออกใบสั่งซื้อ

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWNEJ5bUk1Q00zLTg

https://youtu.be/SWw743JtSbg

https://www.facebook.com/ExcelExpertTraining/videos/1963934413876367/

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 5

วิธีสร้างปฏิทินพันปี โดยใช้สูตรวันที่ของ Excel และใช้สีแสดงวันหยุด

แจกตัวอย่างแฟ้ม Excel แสดงปฏิทินปีค.ศ. 2018 

ซึ่งคุณสามารถกำหนดวันหยุดประจำปีได้เอง 

สามารถเลือกแสดงปฏิทินช่วงปีใดก็ได้ ระหว่างปีค.ศ.1900 - 9999

หรือปรับช่วงปีโดยใส่เลขที่ปีที่ต้องการลงไปในช่อง This Year

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWRlFiVkp1NjhwNjg

https://www.facebook.com/ExcelExpertTraining/posts/1964326623837146

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 6

วิธีคำนวณหาระยะเวลาทำงาน ในช่วงกะหนึ่งๆ

แสดงที่มาของสูตรสั้นๆ Min ปลาย - Max ต้น

แทนการใช้สูตร IF ซ้อน IF ซึ่งต้องซ้อนกันถึง 7 ชั้น

อย่าใช้แค่สมองคิดสูตร ต้องใช้สายตาช่วยคิดด้วย

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWejFZNDB0bC1xUDQ

https://youtu.be/AwaWuCKARPI

https://www.facebook.com/ExcelExpertTraining/videos/1965273033742505/

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 7

วิธีสร้างแถบสีบอกช่วงระยะเวลาทำงานด้วย Conditional Formatting 

ซึ่งเปลี่ยนเป็นสีต่างๆได้เองแล้วแต่ว่าทำงานติดต่อกันกี่วัน 

โดยประยุกต์ใช้สูตร Min ปลาย - Max ต้น เพื่อคำนวณหาระยะเวลาที่ทำงานติดต่อกัน

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWMVhUemdhTzRyWWM

ตัวอย่างใหม่ใช้ Array น้อยลง

https://drive.google.com/open?id=0Bxpfgh-rqhIWdXdfdmxPbllkdlU

https://youtu.be/ROZ83b-CjWE

https://www.facebook.com/ExcelExpertTraining/videos/1965720310364444/

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 8

วิธีสร้างตารางคำนวณค่าแรงของลูกจ้าง แยกค่าแรงปกติและค่าล่วงเวลา

และใช้เป็นฐานข้อมูลไปในตัว พร้อมสร้างแถบสีแบบ Gantt Chart

โดยประยุกต์ใช้สูตร Min ปลาย - Max ต้น

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWYjk3ZnZnSzJaQW8

https://youtu.be/2foHnwzzOGs

https://www.facebook.com/ExcelExpertTraining/videos/1965930957010046/

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 9

วิธีออกแบบ Queueing Module เพื่อจัดลำดับคิวการทำงาน 

คำนวณหาเวลาเริ่มงานแต่ละขั้นจนเสร็จสิ้น และแสดงเป็น Gantt Chart

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWVEVLUXFGbUNHVWM

https://youtu.be/SsvOpxRDogs

https://www.facebook.com/ExcelExpertTraining/videos/1966576923612116/

=====

วิธีประยุกต์ใช้ Excel ในงานวางแผนกำหนดการ (Scheduling) ตอนที่ 10

วิธีออกแบบ MRP Module เพื่อวางแผนความต้องการวัสดุเพื่อใช้ในการผลิต

(Material Requiements Planning) ซึ่งปกติต้องซื้อโปรแกรมแพงๆมาใช้

เมื่อ copy module ไปวางต่อๆกันแล้ว ตัวแปรจะรับส่งไปคำนวณต่อให้เอง

Download ตัวอย่างได้จาก

https://drive.google.com/open?id=0Bxpfgh-rqhIWMkQ1dnhyZi01anc

https://youtu.be/U6ome90JC2A

https://www.facebook.com/ExcelExpertTraining/videos/1967038780232597/

=====

อ่านคำอธิบายได้จาก

http://www.excelexperttraining.com/home/free-excel-e-learning/excel-articles-list/excel-for-management-and-executive-articles/371-scheduling

 

งานวางแผนเป็นงานซึ่งเกี่ยวข้องกับกำหนดการ ต้องใช้ข้อมูลที่มีอยู่หาทางมองย้อนกลับไปในอดีต วิเคราะห์ตัวเลขรายเดือนในปีปัจจุบัน หรือพยากรณ์ยอดขายในอนาคต ไม่ว่าจะเป็นการวางแผนแบบหยาบๆง่ายๆ ที่เกี่ยวข้องกับการใช้ชีวิตประจำวัน ตั้งแต่การคำนวณหาวันครบกำหนดทดลองงาน วันครบกำหนดไถ่ถอน วันที่กำหนดนัดหมาย หรือใช้คำนวณละเอียดถึงตัวเลขของเวลาและระยะเวลาเป็นชั่วโมง นาที หรือวินาที ไม่ว่าจะมีเงื่อนไขเกี่ยวข้องกับวันหยุดหรือระยะเวลาหยุดงานแทรกหรือไม่ หรือจะใช้คำนวณถึงขั้นสลับซับซ้อน ใช้คำนวณหาอัตราผลผลิต หรือใช้วางแผนแบบ 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 จาก https://drive.google.com/open?id=0Bxpfgh-rqhIWV1VrSFhkMGxHaEE นำมาลงใน 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 ปรับเปลี่ยนสีเซลล์ให้กับวันที่ในปฏิทินซึ่งมีกิจกรรมการทำงาน

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

http://www.excelexperttraining.com/home/free-excel-e-learning/excel-articles-list/excel-for-management-and-executive-articles/372-scheduling-vdo

Managing Data with Excel VBA ตอนที่ 1

วิธีนำรายการใหม่ไปบันทึกเพิ่มต่อท้ายรายการสุดท้ายในตารางฐานข้อมูล

โดยใช้รหัส VBA แค่ 3 บรรทัด
ThisWorkbook.Activate
MyVar = [Source]
[Target] = MyVar

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWYXA3VW9GZEtvV0k

https://youtu.be/5uCHHbFwLR0

https://www.facebook.com/ExcelExpertTraining/videos/1957696174500191/

=====

Managing Data with Excel VBA ตอนที่ 2

วิธีใช้สูตร Dynamic Range นำรายการใหม่ทั้งหมด กี่รายการก็ได้
ไปบันทึกเพิ่มต่อท้ายรายการสุดท้ายในตารางฐานข้อมูล

โดยใช้รหัส VBA แค่ 3 บรรทัด
ThisWorkbook.Activate
MyVar = [Source]
[Target] = MyVar

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWd3U3d1VIc0R6bDg

https://youtu.be/BTLsUNtvpgg

https://www.facebook.com/ExcelExpertTraining/videos/1958774077725734/

=====

Managing Data with Excel VBA ตอนที่ 3

วิธีใช้สูตร Dynamic Range แบบ Dynamic Sheet
นำรายการข้อมูลจากแต่ละชีท กี่รายการก็ได้ มารวมไว้เป็นชีทเดียว
โดยบันทึกเพิ่มต่อท้ายรายการสุดท้ายในตารางฐานข้อมูล

คราวนี้มาเพิ่มรหัส MsgBox "Done" เพื่อแสดงผลว่าทำงานเสร็จแล้ว

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWN1duSDZ5Wkp3U3c

https://youtu.be/Xh82Vp3_-Oo

https://www.facebook.com/ExcelExpertTraining/videos/1959330391003436/

=====

Managing Data with Excel VBA ตอนที่ 4

วิธีทำให้ Excel สื่อสารบอกให้คุณตัดสินใจก่อนว่า จะทำงานต่อไปหรือไม่
โดยใช้ MsgBox ร่วมกับ IF ใน VBA

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWU2JETjJQci1TUkU

https://youtu.be/qi1HMhyHxrk

https://www.facebook.com/ExcelExpertTraining/videos/1959917664278042/

=====

Managing Data with Excel VBA ตอนที่ 5

วิธีทำให้ VBA ช่วยตัดสินใจว่าข้อมูลที่บันทึกเป็นรายการใหม่หรือรายการเก่า
ถ้าเป็นรายการใหม่ ให้บันทึกเพิ่มต่อท้ายรายการสุดท้ายในตารางฐานข้อมูล
ถ้าเป็นรายการเก่า ให้บันทึกทับรายการเดิมที่มีอยู่แล้วในตารางฐานข้อมูล
โดยใช้ชื่อ Target ชื่อเดิมชื่อเดียวแต่ย้ายตำแหน่งตามเงื่อนไขได้เอง

หมายเหตุ วิดีโอนี้อัดตอนฝนตกครับ
ขอแก้ตอนที่ 6 เป็นตอนที่ 5 ด้วยครับ ฟังเสียงฝนเพลินไปหน่อย

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWVWU2Rnhnb2YzWjQ

https://youtu.be/XYPZvxVK7Tk

https://www.facebook.com/ExcelExpertTraining/videos/1959951697607972/

=====

วิดีโอชุด Managing Data with Excel VBA จบแล้วครับ

ที่ผ่านมาผมได้ทำวิดิโอเพื่อเป้าหมายการจัดการฐานข้อมูลเป็นหลัก จึงขอสรุปลิงก์วิดีโอชุดที่เกี่ยวข้องกัน ดังนี้

Macro Recorder 10 ตอน
http://www.excelexperttraining.com/home/free-excel-e-learning/excel-articles-list/excel-for-developer-and-designer-articles/368-macro-recorder-video

Excel VBA 10 ตอน

http://www.excelexperttraining.com/home/free-excel-e-learning/excel-articles-list/excel-for-developer-and-designer-articles/367-excel-vba-video

Managing Data with Excel VBA 5 ตอน

http://www.excelexperttraining.com/home/free-excel-e-learning/excel-articles-list/excel-for-developer-and-designer-articles/370-vdo-managing-data-with-excel-vba

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

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

แนะนำให้หาตำรามาอ่านจะดีกว่าการดูวิดีโออย่างเดียวครับ
Excel Power Programming with VBA

jwalkvba

 

ส่วนการใช้ Macro/VBA ในงานด้านอื่นๆ โปรดติดตามต่อๆๆๆไปครับ

 

Go to top