วิธีการใช้เป้าหมายของ Excel และตัวแก้ปัญหาเพื่อแก้ปัญหาสำหรับตัวแปรที่ไม่รู้จัก

Excel มีความสามารถมากเมื่อคุณมีข้อมูลทั้งหมดที่จำเป็นสำหรับการคำนวณของคุณ

แต่จะดีไหมถ้าทำได้ แก้หาตัวแปรที่ไม่รู้จัก ?

ด้วย Goal Seek และโปรแกรมเสริม Solver ก็สามารถทำได้ และเราจะแสดงให้คุณเห็นว่า อ่านคำแนะนำฉบับเต็มเกี่ยวกับวิธีการแก้ปัญหาสำหรับเซลล์เดียวด้วย Goal Seek หรือสมการที่ซับซ้อนยิ่งขึ้นด้วย Solver

วิธีใช้การค้นหาเป้าหมายใน Excel

การค้นหาเป้าหมายมีอยู่แล้วใน Excel มันอยู่ภายใต้ ข้อมูล แท็บใน การวิเคราะห์แบบ What-If เมนู:

สำหรับตัวอย่างนี้ เราจะใช้ชุดตัวเลขง่ายๆ เรามียอดขายสามในสี่และเป้าหมายรายปี เราสามารถใช้ Goal Seek เพื่อค้นหาว่าตัวเลขใดที่ต้องมีในไตรมาสที่ 4 เพื่อสร้างเป้าหมาย

อย่างที่คุณเห็น ยอดรวมการขายปัจจุบันคือ 114,706 หน่วย ถ้าเราต้องการขาย 250,000 ภายในสิ้นปี เราต้องขายเท่าไหร่ใน Q4? Goal Seek ของ Excel จะบอกเรา

นี่คือวิธีใช้ Goal Seek ทีละขั้นตอน:

คลิก ข้อมูล > การวิเคราะห์แบบ What-If > การแสวงหาเป้าหมาย . คุณจะเห็นหน้าต่างนี้:
ใส่ส่วน 'เท่ากับ' ของสมการของคุณใน ตั้งเซลล์ สนาม. นี่คือตัวเลขที่ Excel จะพยายามปรับให้เหมาะสม ในกรณีของเรา เป็นยอดรวมของยอดขายในเซลล์ B5
พิมพ์มูลค่าเป้าหมายของคุณลงใน มูลค่า สนาม. เรากำลังมองหายอดขายรวม 250,000 หน่วย ดังนั้นเราจะใส่ '250,000' ลงในฟิลด์นี้
บอก Excel ว่าต้องแก้ตัวแปรใดใน โดยการเปลี่ยนเซลล์ สนาม. เราต้องการดูว่ายอดขายของเราในไตรมาสที่ 4 เป็นอย่างไร ดังนั้นเราจะบอกให้ Excel แก้ปัญหาสำหรับเซลล์ D2 จะมีลักษณะเช่นนี้เมื่อพร้อมที่จะไป:
ตี ตกลง เพื่อแก้ปัญหาให้กับเป้าหมายของคุณ เมื่อดูดีก็กด ตกลง . Excel จะแจ้งให้คุณทราบเมื่อ Goal Seek พบวิธีแก้ปัญหา
คลิก ตกลง อีกครั้งแล้วคุณจะเห็นค่าที่แก้สมการของคุณในเซลล์ที่คุณเลือก โดยการเปลี่ยนเซลล์ .

ในกรณีของเรา การแก้ปัญหาคือ 135,294 หน่วย แน่นอน เราเพิ่งค้นพบว่าโดยการลบยอดรวมจากเป้าหมายประจำปี แต่ Goal Seek สามารถใช้กับเซลล์ที่ มีข้อมูลอยู่แล้ว . และนั่นก็มีประโยชน์มากกว่า

โปรดทราบว่า Excel จะเขียนทับข้อมูลก่อนหน้าของเรา เป็นความคิดที่ดีที่จะ เรียกใช้ Goal Seek บนสำเนาข้อมูลของคุณ . ยังเป็นความคิดที่ดีที่จะจดบันทึกข้อมูลที่คัดลอกของคุณซึ่งสร้างขึ้นโดยใช้การค้นหาเป้าหมาย คุณคงไม่อยากสับสนกับข้อมูลปัจจุบันที่ถูกต้อง

วิธีสำรองข้อมูลบุ๊กมาร์กใน chrome

ดังนั้น Goal Seek จึงเป็น คุณสมบัติ Excel ที่มีประโยชน์ แต่ก็ไม่ได้น่าประทับใจเท่าไหร่ มาดูเครื่องมือที่น่าสนใจกว่ากัน: Solver add-in

Solver ของ Excel ทำอะไรได้บ้าง

ในระยะสั้น Solver ก็เหมือน a Goal Seek เวอร์ชันหลายตัวแปร . ใช้ตัวแปรเป้าหมายหนึ่งตัวและปรับตัวแปรอื่นๆ จำนวนหนึ่งจนกว่าจะได้คำตอบที่คุณต้องการ

สามารถแก้หาค่าสูงสุดของตัวเลข ค่าต่ำสุดของตัวเลข หรือจำนวนที่แน่นอนได้

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

เป็นวิธีที่ยอดเยี่ยมในการแก้ปัญหาตัวแปรที่ไม่รู้จักหลายตัวใน Excel แต่การค้นหาและใช้งานมันไม่ตรงไปตรงมา

มาดูการโหลด Add-in ของ Solver จากนั้นข้ามไปที่วิธีใช้ Solver ใน Excel 2016

วิธีการโหลด Solver Add-In

Excel ไม่มี Solver ตามค่าเริ่มต้น มันเป็น Add-in ดังนั้น คุณต้องโหลดมันก่อน เช่นเดียวกับฟีเจอร์ Excel ที่ทรงพลังอื่นๆ โชคดีที่มีอยู่แล้วในคอมพิวเตอร์ของคุณ

มุ่งหน้าสู่ ไฟล์ > ตัวเลือก > Add-Ins . จากนั้นคลิกที่ ไป ถัดจาก จัดการ: โปรแกรมเสริมของ Excel .

หากรายการแบบเลื่อนลงนี้มีข้อความอื่นที่ไม่ใช่ 'Excel Add-In' คุณจะต้องเปลี่ยน:

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

ตอนนี้คุณจะเห็น Solver ปุ่มใน การวิเคราะห์ กลุ่มของ ข้อมูล แท็บ:

หากคุณเคยใช้ Data Analysis Toolpak คุณจะเห็นปุ่มการวิเคราะห์ข้อมูล ถ้าไม่เช่นนั้น Solver จะปรากฏขึ้นมาเอง

เมื่อคุณโหลด Add-in แล้ว มาดูวิธีใช้งานกัน

วิธีใช้ Solver ใน Excel

มีสามส่วนในการดำเนินการของ Solver: วัตถุประสงค์ เซลล์ตัวแปร และข้อจำกัด เราจะเดินผ่านแต่ละขั้นตอน

คลิก ข้อมูล > Solver . คุณจะเห็นหน้าต่าง Solver Parameters ด้านล่าง (หากคุณไม่เห็นปุ่มตัวแก้ไข ให้ดูส่วนก่อนหน้าเกี่ยวกับวิธีการโหลด Add-in ของ Solver)
กำหนดวัตถุประสงค์ของเซลล์และบอก Excel เป้าหมายของคุณ วัตถุประสงค์อยู่ที่ด้านบนสุดของหน้าต่าง Solver และมีสองส่วน ได้แก่ เซลล์วัตถุประสงค์และตัวเลือกการขยายใหญ่สุด ย่อเล็กสุด หรือค่าเฉพาะ หากคุณเลือก แม็กซ์ , Excel จะปรับตัวแปรของคุณเพื่อให้ได้จำนวนมากที่สุดในเซลล์เป้าหมายของคุณ นาที เป็นสิ่งที่ตรงกันข้าม: Solver จะย่อจำนวนวัตถุประสงค์ให้น้อยที่สุด มูลค่าของ ให้คุณระบุหมายเลขเฉพาะสำหรับ Solver เพื่อค้นหา
เลือกเซลล์ตัวแปรที่ Excel สามารถเปลี่ยนแปลงได้ เซลล์ตัวแปรถูกตั้งค่าด้วย โดยการเปลี่ยนเซลล์ตัวแปร สนาม. คลิกลูกศรที่อยู่ถัดจากฟิลด์ จากนั้นคลิกและลากเพื่อเลือกเซลล์ที่ Solver ควรทำงานด้วย โปรดทราบว่าสิ่งเหล่านี้คือ ทุกเซลล์ ที่สามารถเปลี่ยนแปลงได้ ถ้าคุณไม่ต้องการให้เซลล์เปลี่ยน อย่าเลือกเซลล์นั้น
กำหนดข้อจำกัดของตัวแปรหลายตัวหรือตัวแปรเดี่ยว ในที่สุด เราก็มาถึงข้อจำกัด นี่คือจุดที่ Solver มีประสิทธิภาพมาก แทนที่จะเปลี่ยนเซลล์ตัวแปรเป็นตัวเลขที่ต้องการ คุณสามารถระบุข้อจำกัดที่ต้องปฏิบัติตามได้ สำหรับรายละเอียด โปรดดูส่วนวิธีกำหนดข้อจำกัดด้านล่าง
เมื่อข้อมูลทั้งหมดนี้พร้อมแล้ว ให้กด แก้ปัญหา เพื่อรับคำตอบของคุณ Excel จะอัปเดตข้อมูลของคุณเพื่อรวมตัวแปรใหม่ (นี่คือเหตุผลที่เราขอแนะนำให้คุณสร้างสำเนาข้อมูลของคุณก่อน)

คุณยังสามารถสร้างรายงาน ซึ่งเราจะดูคร่าวๆ ในตัวอย่าง Solver ด้านล่าง

วิธีกำหนดข้อจำกัดใน Solver

คุณอาจบอก Excel ว่าตัวแปรหนึ่งต้องมากกว่า 200 เมื่อลองใช้ค่าตัวแปรอื่น Excel จะไม่ต่ำกว่า 201 กับตัวแปรนั้น

หากต้องการเพิ่มข้อจำกัด ให้คลิกที่ เพิ่ม ปุ่มถัดจากรายการข้อจำกัด คุณจะได้หน้าต่างใหม่ เลือกเซลล์ (หรือเซลล์) ที่จะจำกัดใน การอ้างอิงเซลล์ ฟิลด์ จากนั้นเลือกตัวดำเนินการ

นี่คือโอเปอเรเตอร์ที่ใช้ได้:

<= (น้อยกว่าหรือเท่ากับ)
= (เท่ากับ)
=> (มากกว่าหรือเท่ากับ)
int (ต้องเป็นจำนวนเต็ม)
เป็น (ต้องเป็น 1 หรือ 0)
ทั้งหมดแตกต่าง

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

สุดท้าย เพิ่มค่าสำหรับข้อจำกัด

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

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

ตัวอย่าง Solver

เพื่อดูว่าทั้งหมดนี้ทำงานอย่างไร เราจะใช้ Solver add-in เพื่อทำการคำนวณอย่างรวดเร็ว นี่คือข้อมูลที่เราเริ่มต้นด้วย:

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

นี่คือข้อจำกัดที่เราจะใช้:

ไม่มีงาน สามารถตกต่ำกว่าสี่ชั่วโมง
งานที่ 2 ต้องเป็น มากกว่าแปดชั่วโมง .
งานที่ 5 ต้องเป็น น้อยกว่าสิบเอ็ดชั่วโมง .
จำนวนชั่วโมงทำงานทั้งหมดต้องเป็น เท่ากับ 40 .

การเขียนข้อจำกัดของคุณในลักษณะนี้ก่อนใช้ Solver อาจเป็นประโยชน์

นี่คือวิธีที่เราจะตั้งค่าใน Solver:

ก่อนอื่นให้สังเกตว่า ฉันได้สร้างสำเนาของตารางแล้ว ดังนั้นเราจึงไม่เขียนทับต้นฉบับ ซึ่งประกอบด้วยชั่วโมงทำงานปัจจุบันของเรา

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

มาตีกัน แก้ปัญหา และดูว่าเกิดอะไรขึ้น

Solver พบวิธีแก้ปัญหา! ดังที่คุณเห็นทางด้านซ้ายของหน้าต่างด้านบน รายได้ของเราเพิ่มขึ้น 0 และเป็นไปตามข้อจำกัดทั้งหมดแล้ว

วิธีเลี่ยงการลงชื่อเข้าใช้ Google บน Android

เพื่อรักษาค่าใหม่ ให้แน่ใจว่า Keep Solver Solution ถูกตรวจสอบแล้วกด ตกลง .

อย่างไรก็ตาม หากคุณต้องการข้อมูลเพิ่มเติม คุณสามารถเลือกรายงานจากด้านขวาของหน้าต่างได้ เลือกรายงานทั้งหมดที่คุณต้องการ บอก Excel ว่าคุณต้องการโครงร่างหรือไม่ (ฉันแนะนำ) แล้วกด ตกลง .

รายงานจะถูกสร้างขึ้นบนแผ่นงานใหม่ในเวิร์กบุ๊กของคุณและให้ข้อมูลเกี่ยวกับกระบวนการที่ Solver add-in ดำเนินการเพื่อให้ได้คำตอบของคุณ

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

ตัวเลือกขั้นสูงของ Solver

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

ที่ชัดเจนที่สุดคือวิธีการแก้ปัญหา:

คุณสามารถเลือกระหว่าง GRG Nonlinear, Simplex LP และ Evolutionary Excel ให้คำอธิบายง่ายๆ เกี่ยวกับเวลาที่คุณควรใช้แต่ละอัน คำอธิบายที่ดีกว่านั้นต้องการความรู้เกี่ยวกับสถิติและการถดถอย

หากต้องการปรับการตั้งค่าเพิ่มเติม เพียงกด ตัวเลือก ปุ่ม. คุณสามารถบอก Excel เกี่ยวกับความเหมาะสมของจำนวนเต็ม ตั้งค่าข้อจำกัดด้านเวลาในการคำนวณ (มีประโยชน์สำหรับชุดข้อมูลขนาดใหญ่) และปรับวิธีการแก้ปัญหา GRG และ Evolutionary เกี่ยวกับการคำนวณ

อีกครั้ง หากคุณไม่รู้ว่าสิ่งนี้หมายความว่าอย่างไร ก็ไม่ต้องกังวลไป หากคุณต้องการทราบข้อมูลเพิ่มเติมเกี่ยวกับวิธีการแก้ปัญหาที่จะใช้ Engineer Excel มี a บทความดีๆ ที่จัดให้ . หากคุณต้องการความแม่นยำสูงสุด Evolutionary น่าจะเป็นวิธีที่ดี เพียงแค่ทราบว่าจะใช้เวลานาน

การแสวงหาเป้าหมายและตัวแก้ปัญหา: นำ Excel ไปสู่อีกระดับ

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

การค้นหาเป้าหมายสามารถช่วยคุณประหยัดเวลาโดยทำให้การคำนวณเร็วขึ้น และ Solver จะเพิ่มพลังมหาศาลให้กับ ความสามารถในการคำนวณของ Excel .

มันเป็นเรื่องของการทำความคุ้นเคยกับพวกเขา ยิ่งคุณใช้มากเท่าไหร่ก็ยิ่งมีประโยชน์มากขึ้นเท่านั้น

คุณใช้ Goal Seek หรือ Solver ในสเปรดชีตของคุณหรือไม่? คุณมีเคล็ดลับอะไรอีกบ้างเพื่อให้ได้คำตอบที่ดีที่สุดจากพวกเขา แบ่งปันความคิดของคุณในความคิดเห็นด้านล่าง!

แบ่งปัน แบ่งปัน ทวีต อีเมล 5 เคล็ดลับในการเพิ่มพลังให้เครื่อง VirtualBox Linux ของคุณ

เบื่อกับประสิทธิภาพที่ย่ำแย่ของเวอร์ชวลแมชชีนหรือไม่? นี่คือสิ่งที่คุณควรทำเพื่อเพิ่มประสิทธิภาพ VirtualBox ของคุณ

อ่านต่อไป หัวข้อที่เกี่ยวข้อง

ผลผลิต
สเปรดชีต
Microsoft Excel
เคล็ดลับ Microsoft Office

เกี่ยวกับผู้เขียน แล้วออลไบรท์(506 บทความที่ตีพิมพ์)

Dann เป็นที่ปรึกษาด้านกลยุทธ์เนื้อหาและการตลาดที่ช่วยให้บริษัทต่างๆ สร้างความต้องการและโอกาสในการขาย เขายังบล็อกเกี่ยวกับกลยุทธ์และการตลาดเนื้อหาที่ dannalbright.com

เพิ่มเติมจาก Dann Albright

สมัครรับจดหมายข่าวของเรา

เข้าร่วมจดหมายข่าวของเราสำหรับเคล็ดลับทางเทคนิค บทวิจารณ์ eBook ฟรี และดีลพิเศษ!

คลิกที่นี่เพื่อสมัครสมาชิก