โปรแกรมเสริม Excel Solver ทำการเพิ่มประสิทธิภาพทางคณิตศาสตร์ โดยทั่วไปจะใช้เพื่อให้พอดีกับแบบจำลองที่ซับซ้อนกับข้อมูลหรือค้นหาวิธีแก้ไขปัญหาซ้ำๆ ตัวอย่างเช่น คุณอาจต้องการปรับเส้นโค้งผ่านจุดข้อมูลบางจุด โดยใช้สมการ ตัวแก้สามารถหาค่าคงที่ในสมการที่เหมาะสมกับข้อมูลมากที่สุด แอปพลิเคชั่นอื่นเป็นที่ที่ยากในการจัดเรียงแบบจำลองใหม่เพื่อให้ผลลัพธ์ที่ต้องการเป็นเรื่องของสมการ
ตัวแก้ไขใน Excel อยู่ที่ไหน
โปรแกรมเสริม Solver รวมอยู่ใน Excel แต่ไม่ได้โหลดเป็นส่วนหนึ่งของการติดตั้งเริ่มต้นเสมอไป หากต้องการตรวจสอบว่าโหลดเสร็จแล้วหรือไม่ ให้เลือกแท็บ DATA แล้วมองหาไอคอน Solver ในหัวข้อ Analysis.
หากคุณไม่พบ Solver ใต้แท็บ DATA คุณจะต้องโหลดส่วนเสริม:
-
เลือกแท็บ FILE จากนั้นเลือก Options.
Image -
ในกล่องโต้ตอบ Options ให้เลือก Add-Ins จากแท็บทางด้านซ้ายมือ
Image -
ที่ด้านล่างสุดของหน้าต่าง เลือก Excel Add-ins จากเมนูดรอปดาวน์ Manage แล้วเลือก Go…
Image -
ทำเครื่องหมายที่ช่องถัดจาก Solver Add-in และเลือก OK.
Image -
คำสั่ง Solver ควรปรากฏในแท็บ DATA คุณพร้อมที่จะใช้ Solver แล้ว
Image
การใช้ Solver ใน Excel
มาเริ่มกันด้วยตัวอย่างง่ายๆ เพื่อทำความเข้าใจว่า Solver ทำอะไร ลองนึกภาพว่าเราต้องการที่จะรู้ว่ารัศมีใดจะให้วงกลมที่มีพื้นที่ 50 ตารางหน่วย เรารู้สมการพื้นที่ของวงกลมแล้ว (A=pi r2) แน่นอน เราสามารถจัดเรียงสมการนี้ใหม่เพื่อให้ได้รัศมีที่ต้องการสำหรับพื้นที่ที่กำหนด แต่สำหรับตัวอย่าง ให้แสร้งทำเป็นว่าเราไม่รู้ว่าต้องทำอย่างไร
สร้างสเปรดชีตที่มีรัศมีใน B1 และคำนวณพื้นที่ใน B2 โดยใช้สมการ =pi()B1^2.
เราสามารถปรับค่าได้เองใน B1 จนถึง B2 แสดงค่าที่ใกล้ถึง 50 เพียงพอ ขึ้นอยู่กับว่าเราแม่นยำแค่ไหน จำเป็น นี่อาจเป็นแนวทางปฏิบัติ อย่างไรก็ตาม หากเราต้องการความแม่นยำมาก การปรับเปลี่ยนที่จำเป็นจะใช้เวลานานที่จริงแล้ว นี่คือสิ่งที่ Solver ทำโดยพื้นฐานแล้ว ทำการปรับเปลี่ยนค่าในบางเซลล์ และตรวจสอบค่าในเซลล์เป้าหมาย:
- เลือก DATA แท็บ และ Solver เพื่อโหลด Solver Parameters กล่องโต้ตอบ
-
ตั้งวัตถุประสงค์ เซลล์ที่จะเป็นพื้นที่ B2 นี่คือค่าที่จะตรวจสอบโดยปรับเซลล์อื่นๆ จนกว่าจะถึงค่าที่ถูกต้อง
Image -
เลือกปุ่มสำหรับ Value of: และตั้งค่าเป็น 50 นี่คือค่าที่ B2 ควรจะบรรลุ
Image -
ในกล่องชื่อ โดยการเปลี่ยนเซลล์ตัวแปร: เข้าไปในเซลล์ที่มีรัศมี B1.
Image -
ปล่อยตัวเลือกอื่นๆ ตามค่าเริ่มต้น แล้วเลือก Solve ดำเนินการปรับให้เหมาะสม ค่าของ B1 จะถูกปรับจนกระทั่ง B2 เป็น 50 และกล่องโต้ตอบ Solver Results จะปรากฏขึ้น
Image -
เลือก ตกลง เพื่อเก็บวิธีแก้ปัญหา
Image
ตัวอย่างง่ายๆ นี้แสดงให้เห็นว่าตัวแก้ปัญหาทำงานอย่างไร ในกรณีนี้ เราสามารถหาวิธีแก้ปัญหาด้วยวิธีอื่นได้ง่ายขึ้น ต่อไป เราจะมาดูตัวอย่างที่ Solver ให้วิธีแก้ปัญหาที่อาจหาได้ยากในวิธีอื่น
ติดตั้งโมเดลที่ซับซ้อนโดยใช้โปรแกรมเสริม Excel Solver
Excel มีฟังก์ชันในตัวเพื่อทำการถดถอยเชิงเส้น โดยปรับเส้นตรงผ่านชุดข้อมูลให้พอดี ฟังก์ชันที่ไม่เป็นเชิงเส้นทั่วไปจำนวนมากสามารถทำให้เป็นเชิงเส้นได้ หมายความว่าสามารถใช้การถดถอยเชิงเส้นเพื่อให้พอดีกับฟังก์ชันต่างๆ เช่น เลขชี้กำลังสำหรับฟังก์ชันที่ซับซ้อนมากขึ้น Solver สามารถใช้เพื่อดำเนินการ 'ลดกำลังสองน้อยที่สุด' ได้ ในตัวอย่างนี้ เราจะพิจารณาปรับสมการของรูปแบบ ax^b+cx^d ให้เหมาะสมกับข้อมูลที่แสดงด้านล่าง
มีขั้นตอนดังนี้
- จัดเรียงชุดข้อมูลด้วยค่า x ในคอลัมน์ A และค่า y ในคอลัมน์ B
- สร้างค่าสัมประสิทธิ์ 4 ค่า (a, b, c และ d) ที่ใดที่หนึ่งในสเปรดชีต ค่าเหล่านี้สามารถกำหนดเป็นค่าเริ่มต้นได้ตามต้องการ
-
สร้างคอลัมน์ค่า Y ที่พอดี โดยใช้สมการของรูปแบบ ax^b+cx^d ซึ่งอ้างอิงสัมประสิทธิ์ที่สร้างในขั้นตอนที่ 2 และค่า x ในคอลัมน์ A โปรดทราบว่าเพื่อคัดลอกสูตรลง คอลัมน์ การอ้างอิงถึงสัมประสิทธิ์ต้องเป็นแบบสัมบูรณ์ในขณะที่การอ้างอิงถึงค่า x ต้องเป็นแบบสัมพัทธ์
Image -
แม้ว่าจะไม่จำเป็น แต่คุณจะได้เห็นภาพว่าสมการนั้นพอดีแค่ไหนโดยพล็อตคอลัมน์ y ทั้งสองกับค่า x ในแผนภูมิกระจาย XY อันเดียว ควรใช้เครื่องหมายสำหรับจุดข้อมูลเดิม เนื่องจากค่าเหล่านี้เป็นค่าที่ไม่ต่อเนื่องพร้อมสัญญาณรบกวน และใช้เส้นสำหรับสมการที่พอดี
Image -
ต่อไป เราต้องการวิธีหาปริมาณความแตกต่างระหว่างข้อมูลและสมการที่พอดีของเรา วิธีมาตรฐานในการทำเช่นนี้คือการคำนวณผลรวมของผลต่างกำลังสอง ในคอลัมน์ที่สาม สำหรับแต่ละแถว ค่าข้อมูลดั้งเดิมสำหรับ Y จะถูกลบออกจากค่าสมการที่พอดี และผลลัพธ์จะถูกยกกำลังสอง ดังนั้น ใน D2 ค่าจะถูกกำหนดโดย =(C2-B2)^2 ผลรวมของค่ากำลังสองเหล่านี้จะถูกคำนวณ เนื่องจากค่าต่างๆ ถูกยกกำลังสอง จึงสามารถเป็นค่าบวกได้เท่านั้น
Image -
ตอนนี้คุณพร้อมที่จะทำการเพิ่มประสิทธิภาพโดยใช้ Solver แล้ว มีสี่ค่าสัมประสิทธิ์ที่ต้องปรับ (a, b, c และ d) คุณยังมีค่าวัตถุประสงค์เดียวที่จะย่อให้เล็กสุด ซึ่งเป็นผลรวมของผลต่างกำลังสอง เรียกใช้ตัวแก้ไขดังด้านบน และตั้งค่าพารามิเตอร์ของตัวแก้ไขเพื่ออ้างอิงค่าเหล่านี้ ดังที่แสดงด้านล่าง
Image -
ยกเลิกการเลือกตัวเลือกเพื่อ ทำให้ตัวแปรที่ไม่มีข้อจำกัดไม่เป็นค่าลบ ซึ่งจะบังคับให้สัมประสิทธิ์ทั้งหมดใช้ค่าบวก
Image -
เลือก Solve และตรวจทานผลลัพธ์ แผนภูมิจะอัปเดตโดยให้ข้อบ่งชี้ที่ดีถึงความเหมาะสม หากโปรแกรมแก้ปัญหาไม่เหมาะกับความพยายามครั้งแรก คุณสามารถลองเรียกใช้อีกครั้ง หากความพอดีดีขึ้น ให้ลองแก้ไขจากค่าปัจจุบันมิเช่นนั้น คุณอาจลองปรับปรุงความพอดีด้วยตนเองก่อนแก้ไข
Image - เมื่อได้ขนาดที่พอดีแล้ว คุณสามารถออกจากตัวแก้ปัญหาได้
การแก้แบบจำลองซ้ำๆ
บางครั้งมีสมการง่ายๆ ที่ให้ผลลัพธ์ในรูปของอินพุต อย่างไรก็ตาม เมื่อเราพยายามพลิกกลับปัญหา จะไม่สามารถหาวิธีแก้ไขง่ายๆ ได้ ตัวอย่างเช่น กำลังที่ยานพาหนะใช้โดยประมาณจาก P=av + bv^3 โดยที่ v คือความเร็ว a คือสัมประสิทธิ์ความต้านทานการหมุน และ b เป็นค่าสัมประสิทธิ์ของ การลากตามหลักอากาศพลศาสตร์ แม้ว่านี่จะเป็นสมการที่ค่อนข้างง่าย แต่ก็ไม่ง่ายที่จะจัดเรียงใหม่เพื่อให้สมการของความเร็วที่รถไปถึงสำหรับกำลังไฟฟ้าเข้าที่กำหนด อย่างไรก็ตาม เราสามารถใช้ Solver เพื่อค้นหาความเร็วนี้ซ้ำๆ ได้ ตัวอย่างเช่น ค้นหาความเร็วที่ได้รับจากกำลังไฟฟ้าเข้า 740 W
-
สร้างสเปรดชีตอย่างง่ายด้วยความเร็ว สัมประสิทธิ์ a และ b และกำลังที่คำนวณจากพวกมัน
Image -
เปิดตัว Solver และเข้าสู่อำนาจ B5 ตามวัตถุประสงค์ ตั้งค่าวัตถุประสงค์ของ 740 และเลือกความเร็ว B2 เป็นเซลล์ตัวแปรที่จะเปลี่ยน เลือก solve เพื่อเริ่มวิธีแก้ปัญหา
Image -
ตัวแก้จะปรับค่าความเร็วจนกระทั่งกำลังใกล้ถึง 740 มาก เพื่อให้ได้ความเร็วที่เราต้องการ
Image - การแก้ปัญหาแบบจำลองในลักษณะนี้มักจะเร็วกว่าและเกิดข้อผิดพลาดน้อยกว่าการกลับรูปแบบที่ซับซ้อน
การทำความเข้าใจตัวเลือกต่างๆ ที่มีอยู่ในโปรแกรมแก้ปัญหาอาจเป็นเรื่องยากทีเดียวหากคุณมีปัญหาในการหาวิธีแก้ปัญหาที่สมเหตุสมผล มักจะเป็นประโยชน์ในการใช้เงื่อนไขขอบเขตกับเซลล์ที่เปลี่ยนแปลงได้ สิ่งเหล่านี้เป็นการจำกัดค่าที่เกินกว่าที่ไม่ควรปรับ ตัวอย่างเช่น ในตัวอย่างก่อนหน้านี้ ความเร็วไม่ควรน้อยกว่าศูนย์ และยังสามารถกำหนดขอบเขตบนได้อีกด้วย นี่เป็นความเร็วที่คุณค่อนข้างมั่นใจว่ารถไม่สามารถวิ่งได้เร็วกว่านี้ หากคุณสามารถกำหนดขอบเขตสำหรับเซลล์ตัวแปรที่เปลี่ยนแปลงได้ ก็จะทำให้ตัวเลือกขั้นสูงอื่นๆ ทำงานได้ดีขึ้น เช่น การเริ่มต้นหลายขั้นตอน การดำเนินการนี้จะเรียกใช้โซลูชันต่างๆ จำนวนหนึ่ง โดยเริ่มต้นจากค่าเริ่มต้นที่แตกต่างกันสำหรับตัวแปร
การเลือกวิธีการแก้ปัญหาก็อาจเป็นเรื่องยากเช่นกัน Simplex LP เหมาะสำหรับโมเดลเชิงเส้นเท่านั้น หากปัญหาไม่เชิงเส้น จะล้มเหลวโดยมีข้อความว่าไม่ตรงตามเงื่อนไขนี้ อีกสองวิธีเหมาะสำหรับวิธีการที่ไม่ใช่เชิงเส้น GRG Nonlinear นั้นเร็วที่สุด แต่วิธีแก้ปัญหานั้นขึ้นอยู่กับเงื่อนไขการเริ่มต้นเริ่มต้นอย่างมากมีความยืดหยุ่นที่ไม่ต้องใช้ตัวแปรเพื่อตั้งค่าขอบเขต ตัวแก้ไขวิวัฒนาการมักจะเชื่อถือได้มากที่สุด แต่ต้องใช้ตัวแปรทั้งหมดที่มีขอบเขตบนและล่าง ซึ่งอาจเป็นเรื่องยากที่จะทำงานล่วงหน้า
โปรแกรมเสริม Excel Solver เป็นเครื่องมือที่ทรงพลังมากซึ่งสามารถนำไปใช้กับปัญหาในทางปฏิบัติมากมาย หากต้องการเข้าถึงพลังของ Excel อย่างเต็มที่ ให้ลองรวม Solver กับมาโคร Excel
