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 nay, bạn đã hoàn thành 5 kỳ 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 phải đạt bao nhiêu điểm trong bài kiểm tra cuối kỳ để đạt được mục tiêu phần trăm 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 trang 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 đó, chúng ta có thể truy cập vào tùy chọn GỬI MỤC TIÊU.
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.
Tìm kiếm mục tiêu VBA - Ví dụ số 1
Hãy lấy ví dụ về điểm kiểm tra trung bình. Dưới đây là điểm dự đoán 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 và 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 tôi 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 phần trăm trung bình tổng thể là 90.
Vì vậy, trong môn học cuối cùng, phải đạt 95 điểm để có điểm trung bình chung là 90.
Tìm kiếm mục tiêu VBA - Ví dụ số 2
Chúng tôi đã học cách áp dụng GOAL SEEK để tìm 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, 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 Next k End Sub
Mã này sẽ lặp lại tất cả các điểm của học sinh và đến điểm kiểm tra cuối kỳ 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 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 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ư vậy, bằng cách sử dụng phân tích GOAL SEEK, chúng ta 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 ô thành công cụ tìm kiếm mục tiêu.
