Hướng dẫn sử dụng Goal Seek trong Excel

Nếu như chúng ta biết kết quả mà ta muốn có được từ một công thức, vậy thì chỉ cần sử dụng Goal Seek trong Excel để tìm ra được giá trị đầu vào của công thức để cho ra kết quả mà ta muốn tính.

Ví dụ trong thực tế, chúng ta có giá bán đôi giày là 200 nghìn đồng thì thu nhập là 1.639.890.000đ. Muốn thu nhập là 2 tỷ thì giá bán phải là bao nhiêu. Hoặc nếu không muốn tăng giá bán thì ta phải tăng số lượng đôi giày thì số giày bán ra phải là bao nhiêu. Bằng việc sử dụng Goal Seek trên Excel chúng ta sẽ dễ dàng giải được bài toán này. (Kết quả được tính ra cho tình huống này là giá bán phải tăng thành 214 nghìn đồng hoặc phải tăng số lượng bán ra đạt 33,232 đôi để đạt được doanh thu 2 tỷ đồng).

Trong bài này Gitiho.com sẽ hướng dẫn bạn cách sử dụng Goal Seek trong Excel bằng minh họa chi tiết.

Ví dụ 1: Sử dụng Goal Seek để tìm ra điểm số cần đạt

Sử dụng Goal Seek trong Excel để tìm ra điểm số cần đạt được trong bài kiểm tra thứ 4 để có được điểm trung bình cuối kỳ là 70.

Bước 1: Nhập công tính trung bình điểm cuối kỳ.

Chọn ô trống cần trả kết quả là điểm trung bình cuối kỳ (trong ví dụ minh họa này là ô B7), nhập công thức tính trung bình sau và nhấn phím Enter để lấy kết quả.

=AVERAGE(B2:B5)

Bước 2: Điểm của bài thi thứ 4 là ô dữ liệu đầu vào (trong minh họa này của chúng ta là ô B5).

Bước 3: Trên tab Data, trong nhóm Forecast, nhấn chuột chọn What-If Analysis. 

Bước 4: Nhấn chuột chọn Goal Seek.

Lúc này hộp thoại Goal Seek sẽ xuất hiện.

Bước 5: Nhấn chọn ô B7.

Bước 6: Nhấn vào hộp To value và gõ số 70.

Bước 7: Nhấn vào hộp By changing cell và nhấn chọn ô B5.

Bước 8: Nhấn chọn nút OK.

Lúc này chúng ta đã thu được kết quả cần tìm. Với bài kiểm tra thứ tư đạt 90 điểm thì điểm trung bình cuối kỳ ta đạt được là 70.

Ví dụ 2: Sử dụng Goal Seek để tính ra số tiền cho vay

Sử dụng Goal Seek trên Exel để tính ra số tiền cho vay để mỗi tháng được trả 1500 đô la.

Bước 1. Công thức ở trong ô B5 dùng để tính số tiền trả hàng tháng.

=PMT(B1/12,B2*12,B3)

Chú thích: Hàm PMT dùng để tính khoản thanh toán cho một khoản cho vay. Số tiền vay càng cao thì số tiền phải trả hàng tháng càng cao. Nếu như mỗi tháng chỉ có thể chi trả 1500 đô la thì có thể vay tối đa là bao nhiêu tiền.

Bước 2. Khoản tiền cho vay được để trong ô B3, đây cũng là ô dữ liệu đầu vào.

Bước 3. Trên tab Data, tìm nhóm Forecasst, nhấn chọn What-If Analysis.

Bước 4. Nhấn chọn Goal Seek.

Lúc này một hộp thoại Goal Seek sẽ xuất hiện. Thực hiện các bước sau và chúng ta có hình minh họa bên dưới.

Bước 5. Nhấn chọn ô B5.

Bước 6. Nhấn vào hộp To value và gõ -1500 (dấu trừ ở đây có nghĩa là ta đang trả tiền).

Bước 7. Nhấn vào hộp By changing cell và chọn ô B3. 

Bước 8. Nhấn chọn OK.

Ta được kết quả như hình dưới đây, với khoản tiền cho vay là 250,187 đô la thì mỗi tháng trả 1500 đô la.

Tính chính xác của Goal Seek

Sử dụng Goal Seek trả về cho chúng ta đáp án gần đúng. Bạn hoàn toàn có thể thay đổi thiết lập lặp lại trong Excel để thu được đáp án chính xác hơn.

Bước 1. Công thức trong ô B1 dưới đây được dùng để tính bình phương giá trị trong ô A1.

Bước 2. Sử dụng Goal Seek để tìm ra giá trị đầu vào để có được kết quả bình phương là 25.

Tới đây chúng ta được trả kết quả là giá trị gần đúng.

Bước 3. Trên tab File, nhấn chọn Options, chọn Formulas.

Bước 4. Dưới mục Calculation options, ta giảm giá trị của Maximum Change bằng cách thêm vào vài số 0. So với giá trị mặc định vốn có là 0,001.

Bước 5. Nhấn chọn OK.

Bước 6. Sử dụng Goal Seek lần nữa. Excel sẽ trả cho chúng ta đáp án chính xác.

Có thể bạn chưa biết

Có khá nhiều vấn đề mà Goal Seek không thể giải quyết được. Ví dụ như Goal Seek cho phép một dữ liệu đầu vào là ô đơn lẻ và một ô dữ liệu đầu ra cũng là ô đơn lẻ. Do đó nếu ta có dữ liệu đầu vào là nhiều ô thì phải giải quyết như thế nào.

Trong tình huống này ta có thể sử dụng Solver trong Excel để giải quyết vấn đề nếu chúng ta có nhiều giá trị đầu vào. Hoặc thỉnh thoảng ta cũng cần nhập giá trị đầu vào khác để tìm ra đáp án.

Bước 1. Công thức trong ô B1 dưới đây trả ra kết quả là -0.25.

Bước 2. Sử dụng Goal Seek để tìm ra giá trị đầu vào để trả kết quả từ công thức là +0.25.

Tới đây ta thu được kết quả là Excel không thể tìm ra đáp án.

Bước 3. Nhấn Cancel.

Bước 4. Bắt đầu với giá trị đầu vào lớn hơn 8.

Bước 5. Sử dụng Goal Seek một lần nữa và Excel sẽ trả cho chúng ta đáp án.

Giải thích: Trong phép tính y = 1 / (x - 8) không liên tục tại x = 8 (vì 1 không thể chia cho 0). Trong ví dụ này, Goal seek không thể tiếp cận trục hoành (x > 8) nếu nó bắt đầu ở phía bên kia trục hoành (x < 8) hoặc ngược lại.