Nếu như bạn đang tìm kiếm 1 công cụ để tính toán các giá trị logic, hay cụ thể là các mục tiêu bạn muốn đạt được với các số liệu có sẵn thì Goal Seek là 1 lựa chọn không thể bỏ qua. Vậy nên, ở bài viết này mình sẽ hướng dẫn bạn cách để sử dụng Goal Seek tìm ra mục tiêu trong Excel chi tiết, có bài tập kèm lời giải.
Bài viết được thực hiện trên laptop hệ điều hành Windows với phiên bản Excel 2016, ngoài ra bạn cũng có thể thực hiện trên các phiên bản Excel 2003, 2007, 2010, 2013, 2019 với thao tác tương tự.
Goal Seek trong Excel là 1 công cụ rất hữu dụng để tìm kiếm các mục tiêu mà bạn mong muốn trong 1 bài toán trên Excel. Công cụ này có thể rút ngắn các bước tính toán, giảm thời gian bạn bỏ vào 1 bài toán rất nhiều và hiệu quả cũng rất cao.
Ngoài ứng dụng tìm kiếm mục tiêu đơn thuần thì Goal Seek còn có những ứng dụng hữu ích khác như:
- Dùng để tìm kiếm mục tiêu cần đạt được.
- Dùng để tìm kiếm các điểm hòa vốn.
- Dùng để tìm kiếm mục tiêu doanh thu lợi nhuận.
- Rất quan trọng với những bạn làm trong lĩnh vực kinh tế.
Giả sử mình có 1 bảng điểm như hình bên dưới:
Ví dụ 1
Với điểm trung bình hiện có là 5.1 và bạn cần ít nhất 5.5 để đạt điểm vượt qua lớp. Điều này sẽ phụ thuộc vào điểm của môn Anh văn, vậy bạn cần đạt điểm môn Anh văn tối thiểu là bao nhiêu để vượt qua lớp, Goal Seek sẽ giúp bạn giải quyết vấn đề này.
Bước 1: Mở file Excel có số điểm cần tính > Chọn thẻ Data.
Mở file Excel có số điểm cần tính > Chọn thẻ Data
Bước 2: Ở phần Data Tools > Chọn lệnh What-If Analysis > Chọn Goal Seek… từ menu thả xuống.
Ở phần Data Tools > Chọn lệnh What-If Analysis > Chọn Goal Seek… từ menu thả xuống
Bước 3: Ở hộp thoại Goal Seek xuất hiện > Bạn nhập các dữ liệu như sau > Nhấn OK để chạy Goal Seek.
- Set cell: Ô chứa công thức tính toán của bài toán, ví dụ công thức tính điểm vượt qua lớp trong ô D11 [Ô được nhập vào phải là công thức thì Goal Seek mới chấp nhận].
- To value: Nhập giá trị kết quả mong muốn, ví dụ điểm để vượt qua lớp là 5,5.
- By changing cell: Đây là ô nơi Goal Seek sẽ đưa ra giá trị cần để đạt được kết quả vừa nhập vào ô To value, ví dụ điểm môn Anh văn cần đạt được trong ô D10.
Ở hộp thoại Goal Seek xuất hiện > Bạn nhập các dữ liệu như hình > Nhấn OK để chạy Goal Seek.
Bước 4: Goal Seek sẽ thực hiện tính toán và đưa ra kết quả là môn Anh văn cần đạt được 8,5 điểm để vượt qua lớp.
Goal Seek sẽ thực hiện tính toán và đưa ra kết quả là môn Anh văn cần đạt được 8,5 điểm để lên lớp
Mua phần mềm Microsoft Office tại Thế Giới Di Động:
Ví dụ: Ông A vay một khoản 500,000,000 đồng để mua nhà, trong thời hạn 120 tháng, với lãi suất 7.5%/năm. Sử dụng công thức =PMT[Lãi suất vay/12, số tháng trả góp, số tiền vay], ông A tính được số tiền trả góp hàng tháng là -5,935,088.46 đồng [số âm thể hiện khoản phải trả].
Tuy nhiên, ông A chỉ có khả năng trả góp 3,500,000 đồng/tháng, nên cần phải vay trong thời hạn dài hơn. Bạn có thể sử dụng Goal Seek để tìm ra số tháng vay cụ thể với khoản trả góp hàng tháng là 3,500,000 đồng.
Ví dụ 2
Bước 1: Mở file Excel > Chọn thẻ Data.
Ở hộp thoại Goal Seek xuất hiện > Bạn nhập các dữ liệu như hình > Nhấn OK
Bước 4: Goal Seek sẽ thực hiện tính toán, phân tích và đưa ra kết luận với khả năng trả góp 3,500,000 đồng/tháng, bạn có thể chọn thời hạn vay là 358 tháng.
Goal Seek sẽ thực hiện tính toán, phân tích và đưa ra kết luận với khả năng trả góp 3,500,000 đồng/tháng, bạn có thể chọn thời hạn vay là 358 tháng
Lưu ý:
- Trong ví dụ 2, giá trị nhập vào ô To value là -3,500,000 [số âm thể hiện khoản phải trả], nếu bạn nhập số dương thì Goal Seek sẽ thông báo không tìm được dữ liệu theo yêu cầu.
- Trong quá trình phân tích mô hình dữ liệu, Goal Seek sẽ thực hiện số lần lặp tối đa là 100 để tìm giá trị yêu cầu. Nếu Goal Seek không tìm được giá trị cuối cùng, có nghĩa là mô hình dữ liệu của bạn không hoạt động. Hãy điều chỉnh lại mô hình để đạt được kết quả như ý muốn.
- Bạn có thể sử dụng Goal Seek để giải quyết nhiều bài toán thực tế khác nữa, chỉ cần bạn đưa ra được kết quả mong muốn và Goal Seek sẽ giúp bạn tìm ra các mục tiêu.
Khi sử dụng Goal Seek để tính toán thì có thể bạn sẽ nhận được kết quả không như mong đợi, với đáp án gần đúng. Tuy nhiên, bạn hoàn toàn có thể thiết lập lại cài đặt để tính toán chính xác hơn như sau:
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.
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.
Sử dụng Goal Seek để tìm ra giá trị đầu vào để có được kết quả bình phương là 25
Ở đây bạn được Goal Seek tính toán ra với kết quả gần đúng.
Ở đây bạn được Goal Seek tính toán ra với kết quả gần đúng
Bước 3: Chọn thẻ File > Nhấn chọn Options.
Chọn thẻ File > Nhấn chọn Options
Bước 4: Chọn phần Formulas > Dưới mục Calculation options > Bạn 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 > Nhấn OK.
Chọn phần Formulas > Dưới mục Calculation options > Bạn 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 > Nhấn OK
Bước 5: Sử dụng Goal Seek lần nữa và Excel sẽ trả cho chúng ta đáp án chính xác nhất.
Sử dụng Goal Seek lần nữa và Excel sẽ trả cho chúng ta đáp án chính xác nhấ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ì không thể giải quyết được bằng Goal Seek.
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.
Công thức trong ô B1 dưới đây trả ra kết quả là -0.25
Bước 2: Và khi 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.
khi 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
Bước 3: Goal Seek sẽ báo lỗi và không thể thực hiện được > Bạn nhấn Cancel để tắt báo lỗi đi.
Goal Seek sẽ báo lỗi và không thể thực hiện được > Bạn nhấn Cancel để tắt báo lỗi đi
Bước 4: Bắt đầu với giá trị đầu vào lớn hơn 8.
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.
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 ý nghĩa của ví dụ trên: 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="">
Và đó là cách sử dụng Goal Seek giúp bạn tìm ra mục tiêu cần tìm trong Excel chi tiết, có ví dụ dễ hiểu. Nếu có thắc mắc về cách làm thì bạn hãy để lại bình luận bên dưới nhé. Hy vọng bài viết sẽ hữu ích với bạn. Chúc bạn thành công!