Goal Seek là một trong những công cụ Phân tích điều gì xảy ra trong Excel giúp bạn tìm giá trị đầu vào chính xác của công thức để có được kết quả đầu ra mong muốn. Nó cho biết cách một giá trị trong công thức ảnh hưởng đến giá trị khác. Nói cách khác, nếu bạn có giá trị mục tiêu mà bạn muốn đạt được, bạn có thể sử dụng mục tiêu tìm kiếm để tìm ra giá trị đầu vào phù hợp để đạt được giá trị đó.
Ví dụ: giả sử bạn đạt tổng cộng 75 điểm trong một môn học và bạn cần ít nhất 90 điểm để đạt điểm S trong môn học đó. Rất may, bạn có một bài kiểm tra cuối cùng có thể giúp bạn nâng điểm trung bình của mình. Bạn có thể sử dụng Goal Seek để tìm ra số điểm bạn cần trong bài kiểm tra cuối cùng đó để đạt điểm S.
Goal Seek sử dụng phương pháp thử-và-sai để theo dõi lại vấn đề bằng cách nhập các dự đoán cho đến khi nó đi đến kết quả phù hợp. Goal Seek có thể tìm giá trị đầu vào tốt nhất cho công thức chỉ trong vài giây mà sẽ mất nhiều thời gian để tìm ra theo cách thủ công. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách sử dụng công cụ Tìm kiếm mục tiêu trong Excel với một số ví dụ.
Các thành phần của chức năng tìm kiếm mục tiêu
Hàm Tìm kiếm Mục tiêu bao gồm ba tham số, viz:
- Đặt ô - Đây là ô trong đó công thức được nhập. Nó chỉ định ô mà bạn muốn đầu ra mong muốn.
- Giá trị - Đây là Mục tiêu / Giá trị Mong muốn mà bạn muốn có được từ hoạt động tìm kiếm mục tiêu.
- Bằng cách thay đổi ô - Điều này chỉ định ô có giá trị cần được điều chỉnh để có được đầu ra mong muốn.
Cách sử dụng Mục tiêu Tìm kiếm trong Excel: Ví dụ 1
Để chứng minh cách nó hoạt động trong một ví dụ đơn giản, hãy tưởng tượng bạn điều hành một quầy bán trái cây. Trong ảnh chụp màn hình bên dưới, ô B11 (bên dưới) hiển thị số tiền bạn phải trả để mua trái cây cho quầy hàng của mình và ô B12 hiển thị tổng doanh thu của bạn khi bán những loại trái cây đó. Và ô B13 hiển thị phần trăm lợi nhuận của bạn (20%).
Nếu bạn muốn tăng lợi nhuận của mình lên ‘30%’, nhưng bạn không thể tăng khoản đầu tư của mình, vì vậy bạn phải tăng doanh thu của mình để thu được lợi nhuận. Nhưng đến bao nhiêu? Tìm kiếm mục tiêu sẽ giúp bạn tìm thấy nó.
Bạn sử dụng công thức sau trong ô B13 để tính phần trăm lợi nhuận:
= (B12-B11) / B12Bây giờ, bạn muốn tính toán tỷ suất lợi nhuận sao cho tỷ lệ phần trăm lợi nhuận của bạn là 30%. Bạn có thể thực hiện việc này với Goal Seek trong Excel.
Điều đầu tiên cần làm là chọn ô có giá trị bạn muốn điều chỉnh. Mỗi khi bạn sử dụng Goal Seek, bạn phải chọn một ô có chứa công thức hoặc hàm. Trong trường hợp của chúng tôi, chúng tôi sẽ chọn ô B13 vì ô này chứa công thức để tính phần trăm.
Sau đó, chuyển đến tab ‘Dữ liệu’, nhấp vào nút ‘Điều gì sẽ xảy ra nếu Phân tích’ trong nhóm Dự báo và chọn ‘Tìm kiếm mục tiêu’.
Hộp thoại Tìm kiếm Mục tiêu sẽ xuất hiện với 3 trường:
- Đặt ô - Nhập tham chiếu ô có chứa công thức (B13). Đây là ô sẽ có đầu ra mong muốn của bạn.
- Giá trị - Nhập kết quả mong muốn mà bạn đang cố gắng đạt được (30%).
- Bằng cách thay đổi ô - Chèn tham chiếu ô cho giá trị đầu vào mà bạn muốn thay đổi (B12) để đi đến kết quả mong muốn. Chỉ cần nhấp vào ô hoặc nhập tham chiếu ô theo cách thủ công. Khi bạn chọn ô, Excel sẽ thêm dấu ‘$’ trước ký tự cột và số hàng để biến ô đó thành ô tuyệt đối.
Khi bạn hoàn tất, hãy nhấp vào 'OK' để kiểm tra.
Sau đó, một hộp thoại 'Trạng thái Tìm kiếm Mục tiêu' sẽ bật lên và thông báo cho bạn nếu nó tìm thấy bất kỳ giải pháp nào như được hiển thị bên dưới. Nếu một giải pháp đã được tìm thấy, giá trị đầu vào trong 'ô đang thay đổi (B12)' sẽ được điều chỉnh thành giá trị mới. Đây là những gì chúng ta muốn. Vì vậy, trong ví dụ này, phân tích xác định rằng, để bạn đạt được mục tiêu lợi nhuận 30%, bạn cần đạt được doanh thu là $ 1635,5 (B12).
Nhấp vào ‘OK’ và Excel sẽ thay đổi các giá trị ô hoặc nhấp vào ‘Cancel’ để loại bỏ giải pháp và khôi phục giá trị ban đầu.
Giá trị đầu vào (1635,5) trong ô B12 là giá trị chúng tôi phát hiện ra bằng cách sử dụng Tìm kiếm mục tiêu để đạt được mục tiêu của chúng tôi (30%).
Những điều cần nhớ khi sử dụng Goal Seek
- Ô Đặt phải luôn chứa công thức phụ thuộc vào ô ‘Bằng cách thay đổi ô’.
- Bạn chỉ có thể sử dụng Goal Seek trên một giá trị đầu vào ô duy nhất tại một thời điểm
- "Bằng cách thay đổi ô" phải chứa một giá trị chứ không phải một công thức.
- Nếu Goal Seek không thể tìm thấy giải pháp chính xác, nó sẽ hiển thị giá trị gần nhất mà nó có thể tạo ra và cho bạn biết rằng thông báo 'Goal-Seek có thể không tìm thấy giải pháp'.
Phải làm gì khi Tìm kiếm mục tiêu trong Excel không hoạt động
Tuy nhiên, nếu bạn chắc chắn rằng có một giải pháp, có một số cách bạn có thể thử để khắc phục sự cố này.
Kiểm tra các thông số và giá trị Tìm kiếm Mục tiêu
Có hai điều bạn nên kiểm tra: trước tiên, hãy kiểm tra xem tham số ‘Đặt ô’ có tham chiếu đến ô công thức hay không. Thứ hai, đảm bảo rằng ô công thức (Đặt ô) phụ thuộc, trực tiếp hoặc gián tiếp, vào ô thay đổi.
Điều chỉnh cài đặt lặp lại
Trong cài đặt Excel, bạn có thể thay đổi số lần thử mà Excel có thể thực hiện để tìm ra giải pháp phù hợp cũng như độ chính xác của nó.
Để thay đổi cài đặt tính toán lặp lại, hãy nhấp vào 'Tệp' từ danh sách tab. Sau đó, nhấp vào 'Tùy chọn' ở dưới cùng.
Trên cửa sổ Tùy chọn Excel, bấm vào 'Công thức' ở ngăn bên trái.
Trong phần "Tùy chọn tính toán", hãy thay đổi các cài đặt sau:
- Số lần lặp lại tối đa - Nó cho biết số lượng các giải pháp khả thi mà excel sẽ tính toán; số càng cao thì nó có thể thực hiện nhiều lần lặp hơn. Ví dụ: nếu bạn đặt 'Số lần lặp tối đa' thành 150, Excel sẽ kiểm tra 150 giải pháp khả thi.
- Thay đổi tối đa - Nó cho biết độ chính xác của kết quả; số nhỏ hơn cho độ chính xác cao hơn. Ví dụ: nếu giá trị ô đầu vào của bạn bằng ‘0’ nhưng Goal Seek dừng tính toán ở ‘0,001’, việc thay đổi giá trị này thành ‘0,0001’ sẽ giải quyết được vấn đề.
Tăng giá trị ‘Lần lặp tối đa’ nếu bạn muốn Excel thử nghiệm nhiều giải pháp khả thi hơn và giảm giá trị ‘Thay đổi tối đa’ nếu bạn muốn có kết quả chính xác hơn.
Ảnh chụp màn hình bên dưới hiển thị giá trị mặc định:
Không có tài liệu tham khảo về vòng tròn
Khi một công thức tham chiếu trở lại ô của chính nó, nó được gọi là tham chiếu vòng. Nếu bạn muốn Excel Goal Seek hoạt động bình thường, các công thức không nên sử dụng tham chiếu vòng tròn.
Ví dụ 2 về tìm kiếm mục tiêu trong Excel
Giả sử bạn đang vay một số tiền trị giá "$ 25000" từ một người nào đó. Họ cho bạn vay số tiền này với lãi suất 7% mỗi tháng trong thời hạn 20 tháng, số tiền trả lại là ‘$ 1327’ mỗi tháng. Nhưng bạn chỉ có thể đủ khả năng trả ‘$ 1,000’ mỗi tháng trong 20 tháng với lãi suất 7%. Goal Seek có thể giúp bạn tìm số tiền cho vay tạo ra khoản thanh toán hàng tháng (EMI) là ‘$ 1000’.
Nhập ba biến đầu vào mà bạn sẽ cần để tính toán PMT của mình, tức là lãi suất 7%, kỳ hạn 20 tháng và số tiền gốc là 25.000 đô la.
Nhập công thức PMT sau vào ô B5, công thức này sẽ cung cấp cho bạn số tiền EMI là $ 1.327,97.
Cú pháp của hàm PMT:
= PMT (Lãi suất / 12, Kỳ hạn, Tiền gốc)Công thức:
= PMT (B3 / 12, B4, -B2)
Chọn ô B5 (ô công thức) và chuyển đến Dữ liệu -> Phân tích Nếu Điều gì xảy ra -> Tìm kiếm Mục tiêu.
Sử dụng các thông số này trên cửa sổ 'Tìm kiếm mục tiêu':
- Đặt ô - B5 (ô chứa công thức tính EMI)
- Giá trị - 1000 (kết quả công thức / mục tiêu bạn đang tìm kiếm)
- Bằng cách thay đổi ô - B2 (đây là số tiền vay mà bạn muốn thay đổi để đạt được giá trị mục tiêu)
Sau đó, nhấn ‘OK’ để giữ giải pháp đã tìm thấy hoặc ‘Hủy’ để loại bỏ giải pháp đó.
Phân tích cho bạn biết rằng số tiền tối đa bạn có thể vay là $ 18825 nếu bạn muốn vượt quá ngân sách của mình.
Đó là cách bạn sử dụng Goal Seek trong Excel.