การแสวงหาเป้าหมาย VBA | วิธีใช้ Goal Seek เพื่อค้นหามูลค่าใน Excel VBA

ค้นหาเป้าหมายใน Excel VBA

Goal Seek เป็นเครื่องมือที่มีอยู่ใน excel VBA ซึ่งช่วยให้เราค้นหาจำนวนที่ต้องการเพื่อให้บรรลุเป้าหมายที่ตั้งไว้

ตัวอย่างเช่นคุณเป็นนักเรียนและคุณได้ตั้งเป้าหมายคะแนนเฉลี่ย 90% จากหกวิชาที่มีอยู่ ณ ตอนนี้คุณสอบเสร็จแล้ว 5 วิชาและเหลือเพียงวิชาเดียวคะแนนที่คาดว่าจะได้รับจากห้าวิชาที่สำเร็จคือ 89, 88, 91, 87, 89 และ 90 ตอนนี้คุณต้องการทราบว่าคุณต้องทำคะแนนเท่าไหร่ การตรวจขั้นสุดท้ายเพื่อให้บรรลุเป้าหมายเปอร์เซ็นต์เฉลี่ยโดยรวมที่ 90%

ซึ่งสามารถทำได้โดยใช้ GOAL SEEK ในแผ่นงาน excel และในการเข้ารหัส VBA มาดูวิธีการทำงานกับ VBA

VBA Goal Seek Syntax

ใน VBA Goal Seek เราจำเป็นต้องระบุค่าที่เรากำลังเปลี่ยนแปลงและมาถึงผลลัพธ์ที่กำหนดเป้าหมายสุดท้ายดังนั้นให้ระบุการอ้างอิงเซลล์โดยใช้วัตถุ VBA RANGE ในภายหลังเราสามารถเข้าถึงตัวเลือก GOAL SEEK ได้

ด้านล่างนี้คือไวยากรณ์ของการแสวงหาเป้าหมายใน VBA

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

ตัวอย่างของ Excel VBA Goal Seek

ต่อไปนี้เป็นตัวอย่างของการแสวงหาเป้าหมายใน Excel VBA

คุณสามารถดาวน์โหลดเทมเพลต Excel VBA Goal Seek ได้ที่นี่ - เทมเพลต Excel ของ VBA Goal Seek

VBA Goal Seek - ตัวอย่าง # 1

มาดูตัวอย่างคะแนนเฉลี่ยการสอบเท่านั้น ด้านล่างนี้คือคะแนนที่คาดการณ์ไว้ของ 5 วิชาจากการสอบที่เสร็จสมบูรณ์

อันดับแรกเราต้องมาถึงคะแนนเฉลี่ยจาก 5 วิชาที่สำเร็จ ใช้ฟังก์ชัน AVERAGE ในเซลล์ B8

ในตัวอย่างนี้เป้าหมายของเราคือ 90, การเปลี่ยนแปลงของเซลล์จะB7 ดังนั้น Goal Seek จะช่วยให้เราค้นหาคะแนนที่กำหนดเป้าหมายจากหัวข้อสุดท้ายเพื่อให้ได้ค่าเฉลี่ยโดยรวมที่ 90

เริ่มกระบวนการย่อยในโมดูลคลาส VBA

รหัส:

 เป้าหมายย่อย _Seek_Example1 () End Sub

ตอนนี้เราต้องการผลลัพธ์ในเซลล์ B8 ดังนั้นให้ระบุการอ้างอิงช่วงนี้โดยใช้วัตถุ RANGE

รหัส:

 ย่อย Goal_Seek_Example1 () ช่วง ("B8") End Sub

ตอนนี้ให้ใส่จุดและป้อนตัวเลือก "ค้นหาเป้าหมาย"

อาร์กิวเมนต์แรกคือ "เป้าหมาย" สำหรับสิ่งนี้เราต้องเข้าสู่เป้าหมายสุดท้ายของเราเพื่อมาถึง RANGE B8 ในตัวอย่างนี้เราพยายามบรรลุเป้าหมายที่ 90

รหัส:

 ย่อย Goal_Seek_Example1 () ช่วง ("B8") เป้าหมาย GoalSeek: = 90 End Sub

อาร์กิวเมนต์ถัดไปคือ“ การเปลี่ยนเซลล์” สำหรับสิ่งนี้เราจำเป็นต้องจัดหาเซลล์ที่เราต้องการค่าใหม่เพื่อให้บรรลุเป้าหมาย

รหัส:

 ย่อย Goal_Seek_Example1 () ช่วง ("B8") เป้าหมาย GoalSeek: = 90, ChangingCell: = ช่วง ("B7") End Sub

ในตัวอย่างนี้เซลล์ที่เปลี่ยนแปลงของเราคือเซลล์ย่อย 6 เช่นเซลล์ B7

โอเคมารันโค้ดเพื่อดูสิ่งที่ต้องทำในหัวข้อสุดท้ายเพื่อให้ได้เปอร์เซ็นต์เฉลี่ยโดยรวมที่ 90

ดังนั้นในวิชาสุดท้าย 95 จะต้องได้คะแนนเฉลี่ยรวม 90

VBA Goal Seek - ตัวอย่าง # 2

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

ด้านล่างนี้คือคะแนนที่คาดว่าจะได้รับ 5 วิชาหลังการสอบ

เนื่องจากเรากำลังค้นหาเป้าหมายสำหรับนักเรียนมากกว่าหนึ่งคนเราจึงต้องใช้ลูปด้านล่างนี้คือรหัสสำหรับคุณ

รหัส:

 Sub Goal_Seek_Example2 () Dim k As Long Dim ResultCell As Range Dim การเปลี่ยนเซลล์เป็นช่วง Dim TargetScore เป็นจำนวนเต็ม TargetScore = 90 สำหรับ k = 2 ถึง 5 ตั้งค่า ResultCell = เซลล์ (8, k) ตั้งค่า ChangingCell = เซลล์ (7, k) ResultCell.GoalSeek TargetScore, ChangingCell ถัดไป k End Sub

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

ดังนั้นเราจึงได้ผลลัพธ์ตอนนี้เป็น

Student A needs to score just 83 to secure the overall 90 percentage and Student D needs to score 93.

But look at Student B & C they need to score 104 each in the final examination which is not possible at all.

Like this using GOAL SEEK analysis we can find the required number to achieve the targeted number mid through the project or process.

Things to Remember

Goal Seek is available with both worksheet tool as well as VBA tool.
Result cell should always contain a formula.
We need to enter goal value and changing cell reference to the goal seek tool.