seychellesartprojects.org

Tìm kiếm mục tiêu trong Excel VBA

Goal Seek là công cụ có sẵn trong excel VBA giúp chúng ta tìm ra con số cần đạt được để đạt được mục tiêu đã đặt ra.

Ví dụ: bạn là sinh viên và bạn đã nhắm mục tiêu điểm trung bình là 90% từ sáu môn học có sẵn. Tính đến thời điểm hiện tại, bạn đã hoàn thành 5 bài thi và bạn chỉ còn lại một môn học, điểm dự đoán của bạn từ năm môn đã hoàn thành là 89, 88, 91, 87, 89 và 90. Bây giờ bạn muốn biết mình cần đạt bao nhiêu điểm trong kiểm tra cuối kỳ đạt chỉ tiêu tỷ lệ trung bình chung là 90%.

Điều này có thể được thực hiện bằng cách sử dụng GOAL SEEK trong bảng tính excel cũng như trong mã hóa VBA. Hãy xem nó hoạt động như thế nào với VBA.

Cú pháp tìm kiếm mục tiêu VBA

Trong VBA Goal Seek, chúng tôi cần chỉ định giá trị chúng tôi đang thay đổi và đi đến kết quả được nhắm mục tiêu cuối cùng, vì vậy hãy cung cấp tham chiếu ô bằng cách sử dụng đối tượng VBA RANGE, sau này chúng tôi có thể truy cập tùy chọn GOAL SEEK.

Dưới đây là cú pháp tìm kiếm mục tiêu trong VBA.

• Range (): Trong trường hợp này, chúng ta cần cung cấp tham chiếu ô mà chúng ta cần để đạt được giá trị được nhắm mục tiêu.
• Mục tiêu: Trong lập luận này, chúng ta cần nhập mục tiêu mà chúng ta đang cố gắng đạt được là gì.
• Thay đổi ô: Trong đối số này, chúng ta cần cung cấp bằng cách thay đổi giá trị ô nào chúng ta cần để đạt được mục tiêu.

Ví dụ về Tìm kiếm mục tiêu VBA trong Excel

Sau đây là các ví dụ về tìm kiếm mục tiêu trong Excel VBA.

Bạn có thể tải xuống Mẫu Excel Tìm kiếm Mục tiêu VBA này tại đây - Mẫu Excel Tìm kiếm Mục tiêu VBA

Tìm kiếm mục tiêu VBA - Ví dụ số 1

Hãy chỉ lấy ví dụ về điểm trung bình của kỳ thi. Dưới đây là dự đoán điểm của 5 môn trong kỳ thi đã hoàn thành.

Đầu tiên, chúng ta cần xem điểm trung bình của 5 môn học đã hoàn thành là bao nhiêu. Áp dụng hàm AVERAGE trong ô B8.

Trong ví dụ này, Mục tiêu của chúng ta là 90, Thay đổi Ô sẽ là B7 . Vì vậy, Goal Seek sẽ giúp chúng ta tìm điểm mục tiêu từ môn học cuối cùng để đạt được điểm trung bình chung là 90.

Bắt đầu quy trình con trong mô-đun lớp VBA.

Mã:

 Sub Goal_Seek_Example1 () End Sub 

Bây giờ chúng ta cần kết quả trong ô B8, vì vậy hãy cung cấp tham chiếu phạm vi này bằng cách sử dụng đối tượng RANGE.

Mã:

 Mục tiêu phụ_Seek_Example1 () Phạm vi ("B8") Kết thúc phụ 

Bây giờ hãy đặt một dấu chấm và nhập tùy chọn “Tìm kiếm mục tiêu”.

Đối số đầu tiên là "Mục tiêu" cho điều này, chúng ta cần nhập mục tiêu cuối cùng của mình để đến RANGE B8. Trong ví dụ này, chúng tôi đang cố gắng đạt được mục tiêu là 90.

Mã:

 Phạm vi Goal_Seek_Example1 () phụ ("B8"). Mục tiêu GoalSeek: = 90 End Sub 

Đối số tiếp theo là "Thay đổi ô" cho điều này, chúng tôi cần cung cấp ô mà chúng tôi cần giá trị mới để đạt được Mục tiêu.

Mã:

 Mục tiêu phụ_Seek_Example1 () Phạm vi ("B8"). Mục tiêu mục tiêu phụ: = 90, ChangeCell: = Phạm vi ("B7") Kết thúc phụ 

Trong ví dụ này, ô thay đổi của chúng tôi là ô Phụ 6 tức là ô B7.

Được rồi, hãy chạy mã để xem những gì cần phải làm trong chủ đề cuối cùng để đạt được tỷ lệ phần trăm trung bình tổng thể là 90.

Vì vậy, trong môn học cuối cùng, 95 phải được cho điểm để có được điểm trung bình chung là 90.

Tìm kiếm mục tiêu VBA - Ví dụ # 2

Chúng tôi đã học cách áp dụng GOAL SEEK để tìm ra con số cần thiết để đạt được mục tiêu. Bây giờ chúng ta sẽ xem một số ví dụ nâng cao về việc tìm điểm kiểm tra cuối kỳ của nhiều học sinh.

Dưới đây là dự đoán điểm của 5 môn sau kỳ thi.

Vì chúng tôi đang tìm mục tiêu cho nhiều học sinh nên chúng tôi cần sử dụng các vòng lặp, dưới đây là mã dành cho bạn.

Mã:

 Sub Goal_Seek_Example2 () Dim k As Long Dim ResultCell As Range Dim ChangeCell As Range Dim TargetScore As Integer TargetScore = 90 For k = 2 to 5 Set ResultCell = Cells (8, k) Set ChangeCell = Cells (7, k) ResultCell.GoalSeek TargetScore, ChangeCell Tiếp theo k End Sub 

Mã này sẽ lặp qua tất cả các điểm của học sinh và đến điểm kiểm tra cuối cùng cần thiết để đạt được điểm trung bình chung là 90.

Vì vậy, chúng tôi nhận được kết quả cuối cùng bây giờ là,

Học sinh A chỉ cần đạt 83 điểm để đảm bảo 90 phần trăm tổng thể và Học sinh D cần đạt 93 điểm.

Nhưng hãy nhìn vào Student B & C, họ cần đạt 104 điểm mỗi môn trong bài kiểm tra cuối kỳ, điều này hoàn toàn không khả thi.

Như thế này bằng cách sử dụng phân tích GOAL SEEK, chúng tôi có thể tìm thấy con số cần thiết để đạt được con số mục tiêu giữa dự án hoặc quy trình.

Những điều cần ghi nhớ

• Goal Seek có sẵn với cả công cụ trang tính cũng như công cụ VBA.
• Ô kết quả phải luôn chứa một công thức.
• Chúng tôi cần nhập giá trị mục tiêu và thay đổi tham chiếu ô cho công cụ tìm kiếm mục tiêu.