การวิเคราะห์ Linear programming โดยโปรแกรม Microsoft Excel

การจะทำการวิเคราะห์ Linear Programming ได้นั้นมีหลายวิธี แต่เนื่องจากในปัจจุบัน คอมพิวเตอร์เข้ามาช่วยได้มากในการคำนวณ โดยเฉพาะอย่างยิ่งโปรแกรม Microsoft Excel นั้นเป็นที่แพร่หลายเป็นอย่างมากสำหรับผู้ใช้ คอมพิวเตอร์ ยิ่งเฉพาะในเมืองไทย  ดังนั้นเรามาเรียนรู้โดยการใช้ Microsoft Excel ในการวิเคราะห์ โดย MS Excel มีชุดสำหรับการวิเคราะห์ Maximize / Minimize ที่เป็น Linear ที่เรียกว่า Solver ให้ใช้ ซึ่งท่านสามารถเข้าไปดูขั้นตอน Solver Add-ins ได้ 

ในการนี้ ผู้เขียนจะอนุมานว่าท่านผู้อ่านหัวข้อนี้ มีความเข้าใจและใช้งาน Microsoft Excel พอสมควร และโดยเฉพาะอย่างยิ่ง Function ทางคณิตศาสตร์ของ EXcel ที่ผู้เขียนจะกล่าวถึงต่อไปในหัวข้อนี้  โดยตัวอย่างก็จะอ้างถึงตัวอย่างในหัวข้อ การวิเคราะห์โดยใช้กราฟ ซึ่งมีสมการดังต่อไปนี้

                      Maximize              800L + 500S                           (รายได้สูงสุด ต่อสัปดาห์)

                      Constraint              2L   +  S  <= 1000                (วัตถุดิบ พลาสติก)

                                                    3L  +  4S  <= 2400                (เวลาในการผลิตใน 1 สัปดาห์)

                                                      L   +  S   <= 700                  (ผลิตรวม)

                                                      L   -  S    <= 350                  ( ความหลากหลายของ ผลิตภัณฑ์)

                                                      L  ,  S  >=  0                        ( Nonnegativity)

1. เริ่มต้นด้วยการเขียนข้อมูลจากสมการต่างๆ ลงใน Worksheet ของ Excel ดังนี้

จะเห็นว่าตัวเลขที่อยู่ในตาราง ก็คือสัมประสิทธิ์ ต่างๆจากสมการข้างบน  ในส่วน B4 , C4 รวมทั้ง  D6 ถึง D10 นั้นให้ว่างเอาไว้

2. ที่ D6 นั้น ให้พิมพ์ ฟังก์ชันคณิตศาสตร์ ดังนี้  

                   =SUMPRODUCT($B$4:$C$4,B6:C6)

3. ให้ทำการ Copy สูตรหรือฟังก์ชันคณิตศาสตร์ใน D6 ไปที่ D7,D8,D9 และ D10

หรือใช้ วิธีดังต่อไปนี้

              - Click ที่ D6 

               - Click ที่ Paste Function  ( fx )

                        - หน้าต่างด้านซ้ายมือ เลือก All ด้านขวามือเลือก SUMPRODUCT  แล้วกด OK

                         - Array1 และ Array2 ให้พิมพ์ข้อความดังในรูป  หลังจากนั้น กด OK 

                            -  เลื่อน Cursor ของ Mouse ไปที่ตำแหน่ง ขวาล่างสุดของ Cell D6  สังเกตว่า Cursor จะต้องกลายเป็น   +

                             -  กดปุ่ม ซ้ายของ Mouse ค้างไว้แล้วลากลงมาจนถึง D10 แล้วปล่อยมือ ซึ่งจะปรากฏผลดังในรูปต่อไปนี้

ที่กล่าวมานี้เป็นแค่การเตรียมข้อมูลใน Worksheet ก่อนใช้ Solver เท่านั้น

4. เข้าสู่ Tools >>> Solver  ดังรูป

         จะปรากฏ Solver window ดังรูปต่อไปนี้

5. ในช่อง Set Target Cell ให้ ใช้เม้าส์ Click ตรงลูกศรสีแดงขวามือของช่อง จะปรากฏแทบใส่ข้อมูล ให้ Click เม้าส์ ที่ Cell  D6 หรือพิมพ์ดังที่ปรากฏในรูป

6. Equal To ให้ Click ที่ Max (เพราะโจทย์ข้อนี้ให้หา Maximum)

7. By Changing Cells: ความหมายก็คือ ให้ระบุ ตำแหน่งที่จะให้ Solver นำค่า Optimal point มาใส่ไว้ หลังจากทำการวิเคราะห์เสร็จแล้ว จากข้อมูลที่เราทำไว้ และเว้นไว้ ให้กดเม้าส์ที่ลูกศรสีแดงของช่องนี้ เมื่อปรากฏแถบให้ใส่ข้อมูล ก็เลื่อนเม้าส์ไปที่ Cell  B4 กดปุ่มซ้ายของเม้าส์ค้างไว้ แล้วลากมาที่ Cell C4 แล้วปล่อยมือที่กดเม้าส์  หรือสามารถพิมพ์โดยตรงดังที่ปรากฏในรูปเลยก็ได้

8. Subject to the Constraints: เป็นการระบุตัวเลขจากสมการ Constraints ต่างๆ จากรูปข้างบน ให้กด Add  จะปรากฏ Window ใหม่ดังรูปต่อไปนี้

               จากรูป ช่องตรงกลางเราสามารถเปลี่ยนจาก <= ไปเป็น =  หรือ  =>  ก็ได้แล้วแต่โจทย์   ในข้อนี้เราใช่ <=

               Cell Reference : หมายถึง Cell ที่เก็บผลลัพธ์ของสมการ Constraints ทั้งหมดไว้ ในที่นี่ก็คือ Cell  D7, D8 , D9 และ D10 ให้กดเม้าส์ที่ลูกศรสีแดงขวามือของช่อง   เมื่อปรากกช่องใส่ข้อมูล ให้ Click เม้าส์ที่ D7 กดปุ่มซ้ายของเม้าส์ค้างไว้ แล้วลากมาจนถึง Cell D10

                Constraints : ก็คือข้อจำกัด จากข้อมูล ก็คือตัวเลขใน Cell F7 จนถึง F10 นั่นเอง ให้กดเม้าส์ที่ลูกศรสีแดงขวามือของช่อง   เมื่อปรากกช่องใส่ข้อมูล ให้ Click เม้าส์ที่ F7 กดปุ่มซ้ายของเม้าส์ค้างไว้ แล้วลากมาจนถึง Cell F10

 หลังจากใส่ข้อมูลครบแล้ว ก็ให้กด OK ก็จะปรากฏ Window ดังรูปต่อไปนี้

9. จากรูปข้างบน ให้กดปุ่ม Option ก็จะปรากฏหน้าต่างใหม่ดังรูปต่อไปนี้

               ให้ Click ที่ Assume Linear Model  และ Assume Non-Negative 

               จากนั้นให้ Click OK  หน้าต่างดังรูปที่อยู่ถัดไปข้างบนก็จะปรากฏอีกครั้งหนึ่ง

10. ให้ Click ที่ Solve 

11. จะปรากฏหน้าต่างใหม่อีกครั้งดังรูปต่อไปนี้

         ในช่องขวามือสุด ให้เลือกชนิด Report เป็น Answer และ Sensitivity แล้วกดปุ่ม OK

12. หลังจากนั้น Excel จะสร้าง Report sheet เพิ่มขึ้นมาให้อีก 2 sheet พร้อมทั้งใส่ตัวเลขที่เหลือใน Sheet แรกที่เราสร้างขึ้นมาเพื่อใส่ข้อมูล (ถูกเปลี่ยนไปเป็น Sheet ตำแหน่งที่ 3 แล้ว)

สิ่งที่ปรากฏใน Sheet ที่สร้างขึ้นใหม่ชื่อว่า  Answer Report 1

สิ่งที่ปรากฏใน Sheet ที่สร้างขึ้นใหม่ชื่อว่า  Sensitivity Report 1

สิ่งที่ปรากฏใน Sheet ที่เราสร้างขึ้นมาครั้งแรก