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

Nội dung được viết bởi Đỗ Thúy Quỳnh

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)

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

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).

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

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

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

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

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

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.

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

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.

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

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)

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

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.

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

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

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

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

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

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.

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

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.

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

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.

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

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.

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

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

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

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.

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

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.

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

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.

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

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.

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

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

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

Bước 3. Nhấn Cancel.

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

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

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

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

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.

KHÓA HỌC EXCEL ONLINE HOÀN TOÀN MIỄN PHÍ

Khóa học với hơn 14000 HỌC VIÊN đang học và đánh giá trung bình 4.76 SAO

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

0 thảo luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội
Giấy phép mạng xã hội số: 588, cấp bởi Bộ thông tin và truyền thông