วิธีใช้การค้นหาเป้าหมายใน Microsoft Excel สำหรับสถานการณ์สมมติ What-If

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

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

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

สูตรหาเป้าหมายของ Excel คืออะไร?

Goal Seek ใน Excel แบ่งออกเป็นสามองค์ประกอบหลัก:

ตั้งค่าเซลล์ : เซลล์ที่คุณต้องการใช้เป็นเป้าหมายของคุณ
มูลค่า : คุณค่าที่คุณต้องการเป็นเป้าหมายของคุณ
โดยการเปลี่ยนเซลล์ : เซลล์ที่คุณต้องการเปลี่ยนเพื่อให้ได้มูลค่าเป้าหมาย

ด้วยการตั้งค่าทั้งสามนี้ Excel จะพยายามปรับปรุงค่าในเซลล์ที่คุณตั้งค่าใน โดยการเปลี่ยนเซลล์ จนถึงเซลล์ที่คุณตั้งค่าใน ตั้งค่าเซลล์ ถึงมูลค่าที่คุณกำหนดใน มูลค่า .

หากฟังดูสับสน ตัวอย่างด้านล่างจะทำให้คุณมีความคิดที่ดีว่า Goal Seek ทำงานอย่างไร

ตัวอย่างการแสวงหาเป้าหมาย 1

ตัวอย่างเช่น สมมติว่าคุณมีสองเซลล์ (A & B) และเซลล์ที่สามที่คำนวณค่าเฉลี่ยของทั้งสองเซลล์

ตอนนี้ สมมติว่าคุณมีค่าคงที่สำหรับ A และคุณต้องการเพิ่มระดับค่าเฉลี่ยโดยการเปลี่ยนค่าสำหรับ B โดยการใช้ Goal Seek คุณสามารถคำนวณว่าค่า B จะสร้างค่าเฉลี่ยที่คุณต้องการได้

ที่เกี่ยวข้อง: เคล็ดลับในการประหยัดเวลาใน Microsoft Excel

ซื้อทีวีที่ไม่ใช่สมาร์ทได้ไหม

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

ใน Excel คลิกเซลล์ C1 .
ในแถบสูตร ให้ป้อนรหัสต่อไปนี้: |_+_| สูตรนี้จะมี Excel คำนวณค่าเฉลี่ยของค่าในเซลล์ A1 และ B1 และส่งออกว่าในเซลล์ C1 . คุณสามารถตรวจสอบให้แน่ใจว่าสูตรของคุณทำงานโดยการป้อนตัวเลขใน A1 และ B1 C1 ควรแสดงค่าเฉลี่ย
สำหรับตัวอย่างนี้ ให้เปลี่ยนค่าของ A1 เซลล์ถึง 16 .
เลือกเซลล์ C1 จากนั้นจากริบบิ้นไปที่ ข้อมูล แท็บ
ภายใต้ แท็บข้อมูล , เลือก การวิเคราะห์แบบ What-If แล้วก็ ค้นหาเป้าหมาย . จะเป็นการเปิดหน้าต่าง Goal Seek
ในหน้าต่างการแสวงหาเป้าหมาย ให้ตั้งค่า ตั้งค่าเซลล์ ถึง C1 . นี่จะเป็นเซลล์เป้าหมายของคุณ การไฮไลต์ C1 ก่อนเปิดหน้าต่าง Goal Seek จะเป็นการตั้งค่าโดยอัตโนมัติใน ตั้งค่าเซลล์ .
ใน มูลค่า ให้ใส่มูลค่าเป้าหมายที่คุณต้องการ สำหรับตัวอย่างนี้ เราต้องการให้ค่าเฉลี่ยเป็น 26 ดังนั้นมูลค่าเป้าหมายจะเป็น 26 เช่นกัน
ในที่สุดใน โดยการเปลี่ยนเซลล์ เลือกเซลล์ที่คุณต้องการเปลี่ยนแปลงเพื่อให้บรรลุเป้าหมาย ในตัวอย่างนี้ นี่จะเป็นเซลล์ B2 .
คลิก ตกลง เพื่อให้ Goal Seek ใช้เวทย์มนตร์

เมื่อคุณคลิกตกลง กล่องโต้ตอบจะปรากฏขึ้นเพื่อแจ้งให้คุณทราบว่า Goal Seek พบวิธีแก้ปัญหาแล้ว

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

ที่เกี่ยวข้อง: สูตร Crazy Excel ที่ทำสิ่งที่น่าอัศจรรย์

ตัวอย่างการแสวงหาเป้าหมาย 2

Goal Seek จะมีประโยชน์เมื่อคุณใช้มันในสถานการณ์จริง อย่างไรก็ตาม ในการใช้ Goal Seek ให้เต็มศักยภาพ คุณต้องมีสูตรที่เหมาะสมในการเริ่มต้น

สำหรับตัวอย่างนี้ ลองพิจารณากรณีธนาคารขนาดเล็ก สมมติว่าคุณมีบัญชีธนาคารที่ให้ดอกเบี้ย 4% ต่อปีสำหรับเงินที่คุณมีในบัญชีของคุณ

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

สำหรับตัวอย่างนี้ สมมติว่าเราต้องการรับ 0 ทุกเดือนจากการจ่ายดอกเบี้ย วิธีคำนวณดังนี้

แถบงานไม่ทำงานใน windows 10

ในเซลล์ A1 พิมพ์ สมดุล .
ในเซลล์ A2 พิมพ์ อัตรารายปี .
ในเซลล์ A4 พิมพ์ กำไรรายเดือน .
ในเซลล์ B2 พิมพ์ 4% .
เลือกเซลล์ B4 และในแถบสูตร ให้ป้อนสูตรด้านล่าง: |_+_| กำไรรายเดือนจะเท่ากับยอดเงินในบัญชีคูณด้วยอัตรารายปีแล้วหารด้วยจำนวนเดือนในหนึ่งปี 12 เดือน
ไปที่ ข้อมูล แท็บ คลิกที่ การวิเคราะห์แบบ What-If แล้วเลือก ค้นหาเป้าหมาย .
ใน ค้นหาเป้าหมาย หน้าต่าง พิมพ์ B4 ใน ตั้งค่าเซลล์ .
พิมพ์ 350 ใน มูลค่า เซลล์ (นี่คือกำไรรายเดือนที่คุณต้องการรับ)
พิมพ์ B1 ใน โดยการเปลี่ยนเซลล์ . (สิ่งนี้จะเปลี่ยนยอดคงเหลือเป็นค่าที่จะให้ผลตอบแทน 0 ต่อเดือน)
คลิก ตกลง . NS สถานะการแสวงหาเป้าหมาย บทสนทนาจะปรากฏขึ้น

ในกล่องโต้ตอบสถานะการค้นหาเป้าหมาย คุณจะเห็นว่าการค้นหาเป้าหมายพบวิธีแก้ปัญหาในเซลล์ B4 ในเซลล์ B1 คุณจะเห็นวิธีแก้ปัญหา ซึ่งควรเป็น 105,000

ข้อกำหนดในการแสวงหาเป้าหมาย

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

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

ข้อ จำกัด ในการแสวงหาเป้าหมาย

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

ในเซลล์ A1 , พิมพ์ 3 .
เลือกเซลล์ C1 จากนั้นในแถบสูตรให้ป้อนสูตรด้านล่าง: |_+_| สูตรนี้จะหารด้วย A1-5 และถ้า A1-5 เป็น 0 ผลลัพธ์จะเป็นค่าที่ไม่ได้กำหนด
ไปที่ ข้อมูล แทป คลิก การวิเคราะห์แบบ What-If แล้วเลือก ค้นหาเป้าหมาย .
ใน ตั้งค่าเซลล์ , พิมพ์ C1 .
ใน มูลค่า , พิมพ์ 1 .
ในที่สุด ใน โดยการเปลี่ยนเซลล์ พิมพ์ A1 . (สิ่งนี้จะเปลี่ยนค่า A1 เป็น 1 ใน C1)
คลิก ตกลง .

กล่องโต้ตอบสถานะการค้นหาเป้าหมายจะบอกคุณว่าอาจไม่พบวิธีแก้ไข และคุณสามารถตรวจสอบอีกครั้งว่าค่า A1 นั้นไม่สมเหตุสมผล

ทีนี้ วิธีแก้ปัญหาสำหรับ Goal Seek ก็คือการมี 6 ใน A1 สิ่งนี้จะให้ 1/1 ซึ่งเท่ากับ 1 แต่ ณ จุดหนึ่งระหว่างกระบวนการทดลองและปรับปรุง Excel พยายาม 5 ใน A1 ซึ่งให้ 1/0 ซึ่งไม่ได้กำหนดไว้ ซึ่งทำให้กระบวนการนี้ล้มเหลว

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

ตัวอย่างเช่น หากคุณเปลี่ยน A1 เป็นตัวเลขใดๆ ที่มากกว่า 5 แล้วทำซ้ำขั้นตอนเดียวกันกับ Goal Seek for 1 คุณควรได้ผลลัพธ์ที่ถูกต้อง

วิธีรับ wifi โดยไม่ต้องใช้อินเทอร์เน็ต

ทำให้การคำนวณมีประสิทธิภาพมากขึ้นด้วยการแสวงหาเป้าหมาย

การวิเคราะห์แบบ What-If และการแสวงหาเป้าหมายสามารถเร่งการคำนวณของคุณด้วยสูตรของคุณด้วยการวัดที่ยอดเยี่ยม การเรียนรู้สูตรที่ถูกต้องเป็นอีกก้าวสำคัญในการเป็นผู้เชี่ยวชาญของ Excel

แบ่งปัน แบ่งปัน ทวีต อีเมล 15 สูตร Excel ที่จะช่วยคุณแก้ปัญหาในชีวิตจริง

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

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

ผลผลิต
สเปรดชีต
Microsoft Excel
เคล็ดลับ Microsoft Office
การวิเคราะห์ข้อมูล

เกี่ยวกับผู้เขียน อาเมียร์ เอ็ม. อินเทลลิเจนซ์(39 บทความเผยแพร่)

อาเมียร์เป็นนักศึกษาเภสัชศาสตร์ที่มีความหลงใหลในเทคโนโลยีและการเล่นเกม เขาชอบเล่นดนตรี ขับรถ และเขียนคำ

เพิ่มเติมจาก Amir M. Bohlooli

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

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

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