งานขนส่งใน Excel ค้นหาวิธีการขนส่งที่ดีที่สุดจากผู้ขายไปยังผู้ซื้อ

Excel เป็นโปรแกรมที่ใช้งานได้ดีมาก สามารถใช้เพื่อแก้ปัญหาชั้นยอดที่ต้องเผชิญในธุรกิจ ที่พบมากที่สุดคือการคมนาคมขนส่ง ลองนึกภาพว่าเราต้องเข้าใจวิธีการขนส่งจากผู้ผลิตไปยังผู้ซื้อขั้นสุดท้ายที่เหมาะสมที่สุดในแง่ของเวลา เงิน และทรัพยากรอื่นๆ ปัญหานี้ค่อนข้างเป็นที่นิยมไม่ว่าธุรกิจจะอยู่ในอุตสาหกรรมใด ดังนั้น เรามาดูวิธีใช้งาน Excel อย่างละเอียดยิ่งขึ้น

คำอธิบายของงานขนส่ง

ดังนั้นเราจึงมีคู่สัญญาสองรายที่มีปฏิสัมพันธ์ซึ่งกันและกันอย่างต่อเนื่อง ในกรณีของเรา นี่คือผู้ซื้อและผู้ขาย เราจำเป็นต้องหาวิธีการขนส่งสินค้าในลักษณะที่มีต้นทุนต่ำ ในการทำเช่นนี้ คุณต้องนำเสนอข้อมูลทั้งหมดในรูปแบบแผนผังหรือเมทริกซ์ ใน Excel เราใช้ตัวเลือกหลัง โดยทั่วไป งานขนส่งมีสองประเภท:

1. ปิด. ในกรณีนี้ อุปสงค์และอุปทานอยู่ในภาวะสมดุล
2. เปิด. ไม่มีความเท่าเทียมกันระหว่างอุปสงค์และอุปทานที่นี่ เพื่อให้ได้วิธีแก้ปัญหานี้ ก่อนอื่นคุณต้องทำให้เป็นประเภทแรก ปรับสมดุลอุปสงค์และอุปทาน ในการทำเช่นนี้ คุณต้องแนะนำตัวบ่งชี้เพิ่มเติม – การปรากฏตัวของผู้ซื้อหรือผู้ขายแบบมีเงื่อนไข นอกจากนี้ คุณต้องทำการเปลี่ยนแปลงบางอย่างในตารางต้นทุน

วิธีเปิดใช้งานฟีเจอร์ค้นหาโซลูชันใน Excel

เพื่อแก้ปัญหาการขนส่งใน Excel มีฟังก์ชันพิเศษที่เรียกว่า “Search for a solution” ไม่ได้เปิดใช้งานโดยค่าเริ่มต้น ดังนั้นคุณต้องทำตามขั้นตอนต่อไปนี้:

1. เปิดเมนู "ไฟล์" ซึ่งอยู่ที่มุมซ้ายบนของหน้าต่างโปรแกรม
2. หลังจากนั้นให้คลิกที่ปุ่มที่มีพารามิเตอร์
3. ต่อไปเราจะพบส่วนย่อย "การตั้งค่า" และไปที่เมนูการจัดการส่วนเสริม เหล่านี้เป็นโปรแกรมขนาดเล็กที่ทำงานภายในสภาพแวดล้อม Microsoft Excel เราเห็นว่าในตอนแรกเราคลิกที่เมนู "เพิ่มเติม" จากนั้นในส่วนล่างขวาเราตั้งค่ารายการ "Excel Add-ins" และคลิกที่ปุ่ม "ไป" การดำเนินการที่จำเป็นทั้งหมดจะถูกเน้นด้วยสี่เหลี่ยมสีแดงและลูกศร
4. จากนั้นเปิด Add-in "Search for a solution" หลังจากนั้นเรายืนยันการกระทำของเราโดยกดปุ่ม OK จากคำอธิบายของฉาก เราจะเห็นว่ามันถูกออกแบบเพื่อวิเคราะห์ข้อมูลที่ซับซ้อน เช่น วิทยาศาสตร์และการเงิน
5. หลังจากนั้นไปที่แท็บ "ข้อมูล" ซึ่งเราจะเห็นปุ่มใหม่ซึ่งเรียกว่าเหมือนกับ Add-in สามารถพบได้ในกลุ่มเครื่องมือการวิเคราะห์

ยังคงเป็นเพียงการคลิกที่ปุ่มนี้และเราดำเนินการแก้ไขปัญหาการขนส่ง แต่ก่อนหน้านั้น เราควรพูดถึงเครื่องมือ Solver ใน Excel ให้มากขึ้นอีกนิด นี่เป็นโปรแกรมเสริมพิเศษของ Excel ที่ทำให้สามารถค้นหาวิธีแก้ไขปัญหาได้เร็วที่สุด คุณลักษณะเฉพาะคือการพิจารณาข้อจำกัดที่ผู้ใช้กำหนดในขั้นตอนการเตรียมการ พูดง่ายๆ ก็คือ นี่คือรูทีนย่อยที่ทำให้สามารถกำหนดวิธีที่ดีที่สุดเพื่อให้บรรลุงานบางอย่างได้ งานดังกล่าวอาจรวมถึงสิ่งต่อไปนี้:

1. การลงทุน การโหลดคลังสินค้า หรือกิจกรรมอื่นที่คล้ายคลึงกัน รวมถึงการส่งมอบสินค้า
2. วิธีที่ดีที่สุด ซึ่งรวมถึงวัตถุประสงค์ต่างๆ เช่น การบรรลุผลกำไรสูงสุดด้วยต้นทุนขั้นต่ำ วิธีบรรลุคุณภาพที่ดีที่สุดด้วยทรัพยากรที่มีอยู่ และอื่นๆ

นอกจากงานขนส่งแล้ว ส่วนเสริมนี้ยังใช้เพื่อวัตถุประสงค์ต่อไปนี้:

1. การพัฒนาแผนการผลิต นั่นคือต้องผลิตผลิตภัณฑ์กี่หน่วยเพื่อให้ได้รายได้สูงสุด
2. ค้นหาการกระจายแรงงานสำหรับงานประเภทต่าง ๆ เพื่อให้ต้นทุนรวมในการผลิตผลิตภัณฑ์หรือบริการน้อยที่สุด
3. กำหนดเวลาขั้นต่ำที่จะใช้ในการทำงานทั้งหมดให้เสร็จ

อย่างที่คุณเห็น งานแตกต่างกันมาก กฎสากลสำหรับการใช้ Add-in นี้คือก่อนที่จะแก้ปัญหา จำเป็นต้องสร้างแบบจำลองที่สอดคล้องกับลักษณะสำคัญของปัญหาที่เกิดขึ้น โมเดลคือชุดของฟังก์ชันที่ใช้ตัวแปรเป็นอาร์กิวเมนต์ นั่นคือค่านิยมที่สามารถเปลี่ยนแปลงได้

สิ่งสำคัญคือต้องสังเกตว่าการปรับให้เหมาะสมของชุดค่านั้นดำเนินการบนตัวบ่งชี้เดียวซึ่งเรียกว่าฟังก์ชันวัตถุประสงค์เท่านั้น

โปรแกรมเสริม Solver ระบุค่าต่าง ๆ ของตัวแปรที่ส่งผ่านไปยังฟังก์ชันวัตถุประสงค์ในลักษณะที่เป็นค่าสูงสุด ต่ำสุด หรือเท่ากับค่าหนึ่ง (นี่คือข้อจำกัดอย่างแม่นยำ) มีฟังก์ชันอื่นที่ค่อนข้างคล้ายคลึงกันในหลักการทำงาน และมักสับสนกับ "Search for a solution" เรียกว่า “การเลือกตัวเลือก” แต่ถ้าคุณเจาะลึกลงไป ความแตกต่างระหว่างพวกเขานั้นยิ่งใหญ่มาก:

1. ฟังก์ชัน Goal Seek ใช้ไม่ได้กับตัวแปรมากกว่าหนึ่งตัว
2. ไม่มีความสามารถในการกำหนดขีดจำกัดของตัวแปร
3. สามารถกำหนดได้เฉพาะความเท่าเทียมกันของฟังก์ชันวัตถุประสงค์กับค่าที่แน่นอน แต่ไม่สามารถหาค่าสูงสุดและต่ำสุดได้ จึงไม่เหมาะกับงานของเรา
4. สามารถคำนวณได้อย่างมีประสิทธิภาพเฉพาะในกรณีที่แบบจำลองเชิงเส้นแบบ หากตัวแบบไม่เป็นเชิงเส้น จะพบค่าที่ใกล้เคียงค่าเดิมมากที่สุด

โครงสร้างงานการขนส่งมีความซับซ้อนมากขึ้น ดังนั้นส่วนเสริม "การเลือกพารามิเตอร์" จึงไม่เพียงพอสำหรับสิ่งนี้ มาดูวิธีการใช้ฟังก์ชัน “Search for a Solution” ในทางปฏิบัติโดยใช้ตัวอย่างของปัญหาการขนส่ง

ตัวอย่างการแก้ปัญหาการขนส่งใน Excel

เพื่อแสดงให้เห็นอย่างชัดเจนถึงวิธีการแก้ปัญหาการขนส่งในทางปฏิบัติใน Excel มาดูตัวอย่างกัน

งานเงื่อนไข

สมมติว่าเรามีผู้ขาย 6 รายและผู้ซื้อ 7 ราย อุปสงค์และอุปทานระหว่างกันมีการกระจายตามลำดับดังนี้ 36, 51, 32, 44, 35 และ 38 หน่วยเป็นผู้ขาย และ 33, 48, 30, 36, 33, 24 และ 32 หน่วยเป็นผู้ซื้อ หากคุณสรุปค่าทั้งหมดเหล่านี้ คุณจะพบว่าอุปสงค์และอุปทานอยู่ในสมดุล ดังนั้นปัญหานี้จึงเป็นแบบปิดซึ่งแก้ไขได้ง่ายมาก

นอกจากนี้เรายังมีข้อมูลเกี่ยวกับจำนวนเงินที่คุณต้องใช้ในการเดินทางจากจุด A ไปยังจุด B (ในตัวอย่างจะเน้นเป็นเซลล์สีเหลือง)

วิธีแก้ปัญหา – อัลกอริทึมทีละขั้นตอน

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

1. ขั้นแรก เราสร้างตารางที่ประกอบด้วย 6 แถว 7 คอลัมน์
2. หลังจากนั้นเราไปที่เซลล์ใด ๆ ที่ไม่มีค่าใด ๆ และในเวลาเดียวกันก็อยู่นอกตารางที่สร้างขึ้นใหม่และแทรกฟังก์ชัน ในการดำเนินการนี้ ให้คลิกที่ปุ่ม fx ซึ่งอยู่ทางด้านซ้ายของบรรทัดป้อนฟังก์ชัน
3. เรามีหน้าต่างที่เราต้องเลือกหมวดหมู่ "คณิตศาสตร์" เราสนใจฟังก์ชั่นอะไร? ที่ไฮไลต์ในภาพหน้าจอนี้ การทำงาน SUMPRODUCT คูณช่วงหรืออาร์เรย์ระหว่างกันและรวมเข้าด้วยกัน เพียงแค่สิ่งที่เราต้องการ หลังจากนั้นให้กดปุ่ม OK
4. ถัดไป หน้าต่างจะปรากฏขึ้นบนหน้าจอซึ่งคุณต้องระบุพารามิเตอร์ของฟังก์ชัน มีดังต่อไปนี้:
   1. อาร์เรย์ 1 นี่เป็นอาร์กิวเมนต์แรกที่เราเขียนช่วงที่ไฮไลต์ด้วยสีเหลือง คุณสามารถตั้งค่าพารามิเตอร์ของฟังก์ชันได้โดยใช้แป้นพิมพ์หรือโดยการเลือกพื้นที่ที่เหมาะสมด้วยปุ่มซ้ายของเมาส์
   2. Array 2 นี่คืออาร์กิวเมนต์ที่สอง ซึ่งเป็นตารางที่สร้างขึ้นใหม่ การดำเนินการจะดำเนินการในลักษณะเดียวกัน

ยืนยันการกระทำของคุณโดยกดปุ่ม OK

1. หลังจากนั้น เราทำการคลิกเมาส์ซ้ายบนเซลล์ที่ทำหน้าที่เป็นด้านบนซ้ายในตารางที่สร้างขึ้นใหม่ ตอนนี้ให้คลิกปุ่มแทรกฟังก์ชันอีกครั้ง
2. เราเลือกหมวดหมู่เดียวกันกับในกรณีก่อนหน้า แต่คราวนี้เราสนใจฟังก์ชั่น SUM.
3. มาถึงขั้นตอนการกรอกข้อโต้แย้ง เป็นอาร์กิวเมนต์แรก เราเขียนแถวบนสุดของตารางที่เราสร้างไว้ตอนต้น เช่นเดียวกับเมื่อก่อน สามารถทำได้โดยการเลือกเซลล์เหล่านี้บนแผ่นงานหรือทำด้วยตนเอง เรายืนยันการกระทำของเราโดยกดปุ่มตกลง
4. เราจะเห็นผลลัพธ์ในเซลล์ที่มีฟังก์ชัน ในกรณีนี้ จะเป็นศูนย์ ถัดไป เลื่อนเคอร์เซอร์ไปที่มุมล่างขวา หลังจากนั้นเครื่องหมายเติมข้อความอัตโนมัติจะปรากฏขึ้น ดูเหมือนตุ๊กตาสีดำเล็กน้อย หากปรากฏขึ้น ให้กดปุ่มซ้ายของเมาส์ค้างไว้แล้วเลื่อนเคอร์เซอร์ไปที่เซลล์สุดท้ายในตารางของเรา
5. สิ่งนี้ทำให้เรามีโอกาสถ่ายโอนสูตรไปยังเซลล์อื่นทั้งหมด และรับผลลัพธ์ที่ถูกต้องโดยไม่ต้องทำการคำนวณเพิ่มเติม
6. ขั้นตอนต่อไปคือการเลือกเซลล์ด้านซ้ายบนและวางฟังก์ชัน SUM เข้าไปในเธอ หลังจากนั้น เราป้อนอาร์กิวเมนต์และใช้เครื่องหมายเติมข้อความอัตโนมัติเพื่อเติมเซลล์ที่เหลือทั้งหมด
7. หลังจากนั้นเราจะดำเนินการแก้ไขปัญหาโดยตรง ในการทำเช่นนี้ เราจะใช้ส่วนเสริมที่เรารวมไว้ก่อนหน้านี้ ไปที่แท็บ "ข้อมูล" แล้วเราจะพบเครื่องมือ "ค้นหาวิธีแก้ปัญหา" เราคลิกที่ปุ่มนี้
8. ตอนนี้มีหน้าต่างปรากฏขึ้นต่อหน้าต่อตาเรา ซึ่งคุณสามารถกำหนดค่าพารามิเตอร์ของโปรแกรมเสริมของเราได้ ลองมาดูที่ตัวเลือกเหล่านี้กัน:
   1. ปรับฟังก์ชันวัตถุประสงค์ให้เหมาะสม ที่นี่เราต้องเลือกเซลล์ที่มีฟังก์ชัน SUMPRODUCT. เราเห็นว่าตัวเลือกนี้ทำให้สามารถเลือกฟังก์ชันที่จะค้นหาโซลูชันได้
   2. ก่อน. ที่นี่เราตั้งค่าตัวเลือก "ขั้นต่ำ"
   3. โดยการเปลี่ยนเซลล์ของตัวแปร ที่นี่เราระบุช่วงที่สอดคล้องกับตารางที่เราสร้างขึ้นเมื่อเริ่มต้น (ยกเว้นแถวและคอลัมน์สรุป)
   4. อยู่ภายใต้ข้อจำกัด ที่นี่เราต้องเพิ่มข้อจำกัดโดยคลิกปุ่มเพิ่ม
   5. เราจำได้ว่าเราต้องสร้างข้อจำกัดประเภทใด – ผลรวมของมูลค่าความต้องการของผู้ซื้อและข้อเสนอของผู้ขายจะต้องเหมือนกัน
9. งานของข้อ จำกัด ดำเนินการดังนี้:
   1. เชื่อมโยงไปยังเซลล์ ที่นี่เราป้อนช่วงของตารางสำหรับการคำนวณ
   2. ข้อกำหนด นี่คือการดำเนินการทางคณิตศาสตร์ที่ตรวจสอบช่วงที่ระบุในช่องป้อนข้อมูลแรก
   3. ค่าของเงื่อนไขหรือข้อจำกัด ที่นี่เราป้อนคอลัมน์ที่เหมาะสมในตารางแหล่งที่มา
   4. หลังจากทำตามขั้นตอนทั้งหมดแล้ว ให้คลิกปุ่ม ตกลง เพื่อยืนยันการดำเนินการของเรา

เราดำเนินการเหมือนกันทุกประการสำหรับแถวบนสุด โดยตั้งค่าเงื่อนไขต่อไปนี้: ต้องเท่ากัน

ขั้นตอนต่อไปคือการตั้งเงื่อนไข เราจำเป็นต้องกำหนดเกณฑ์ต่อไปนี้สำหรับผลรวมของเซลล์ในตาราง – มากกว่าหรือเท่ากับศูนย์ ซึ่งเป็นจำนวนเต็ม เป็นผลให้เรามีรายการเงื่อนไขดังกล่าวซึ่งปัญหาได้รับการแก้ไข ที่นี่คุณต้องตรวจสอบให้แน่ใจว่าได้เลือกช่องทำเครื่องหมายถัดจากตัวเลือก "สร้างตัวแปรโดยไม่มีขีด จำกัด ไม่เป็นค่าลบ" นอกจากนี้ ในสถานการณ์ของเรา จำเป็นต้องเลือกวิธีการในการแก้ปัญหา – “การค้นหาวิธีแก้ไขปัญหาที่ไม่เชิงเส้นของวิธี OPG” ตอนนี้เราสามารถพูดได้อย่างปลอดภัยว่าการตั้งค่าเสร็จสิ้น ดังนั้นจึงเหลือเพียงการคำนวณเท่านั้น ในการดำเนินการนี้ ให้คลิกที่ปุ่ม "ค้นหาวิธีแก้ปัญหา"

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

หากปรากฎว่างานของเรากลายเป็นงานประเภทเปิด แสดงว่าสิ่งนี้ไม่ดี เพราะคุณต้องแก้ไขตารางต้นทางเพื่อให้งานกลายเป็นงานปิด อย่างไรก็ตาม เมื่อทำเสร็จแล้ว อัลกอริทึมที่เหลือจะเหมือนเดิม

สรุป

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