Cách sử dụng tính năng Goal Seek trong Microsoft Excel

Chia sẻ kiến thức 15/08/2022

Có đầu ra cho một công thức nhưng không biết đầu vào? Giải quyết ngược lại với Goal Seek có thể là những gì bạn cần.

Phân tích Điều gì xảy ra trong Excel cho phép bạn xem việc thay đổi một ô có thể ảnh hưởng như thế nào đến kết quả đầu ra của công thức. Bạn có thể sử dụng các công cụ của Excel cho mục đích này để tính toán ảnh hưởng của việc thay đổi giá trị của một ô trong công thức.

Excel có ba loại công cụ Phân tích Điều gì xảy ra: Trình quản lý Tình huống, Tìm kiếm Mục tiêu và Bảng Dữ liệu. Với Goal Seek, bạn có thể xác định đầu vào nào bạn cần để chuyển đổi ngược lại công thức thành một đầu ra nhất định.

Tính năng Tìm kiếm Mục tiêu trong Excel là tính năng thử nghiệm và có lỗi, vì vậy nếu nó không tạo ra những gì bạn muốn, thì Excel sẽ làm việc để cải thiện giá trị đó cho đến khi nó thực hiện được.

Công thức Tìm kiếm Mục tiêu trong Excel là gì?

Tìm kiếm Mục tiêu trong Excel về cơ bản được chia thành ba thành phần chính:

  1. Đặt ô : Ô bạn muốn sử dụng làm mục tiêu của mình.
  2. Giá trị : Giá trị bạn muốn làm mục tiêu của mình.
  3. Bằng cách thay đổi ô : Ô bạn muốn thay đổi để đạt được giá trị mục tiêu của mình.

Với ba cài đặt này được đặt, Excel sẽ cố gắng cải thiện giá trị trong ô bạn đặt trong Bằng cách thay đổi ô cho đến khi ô bạn đặt trong Đặt ô đạt đến giá trị bạn đã xác định trong Giá trị .

Nếu tất cả những điều đó nghe có vẻ khó hiểu, các ví dụ dưới đây sẽ cung cấp cho bạn một ý tưởng hay về cách hoạt động của Goal Seek.

Ví dụ 1 về Tìm kiếm Mục tiêu

Ví dụ: giả sử bạn có hai ô (A & B) và một ô thứ ba tính giá trị trung bình của hai ô này.

Bây giờ, giả sử bạn có một giá trị tĩnh cho A và bạn muốn tăng mức trung bình bằng cách thay đổi giá trị cho B. Bằng cách sử dụng Goal Seek, bạn có thể tính toán giá trị nào của B sẽ tạo ra mức trung bình mà bạn muốn.

Cách sử dụng Mục tiêu Tìm kiếm trong Excel

  1. Trong Excel, bấm vào ô C1 .
  2. Trong thanh công thức, hãy nhập mã sau:
    =AVERAGE(A1:B1)

    Công thức này sẽ cho phép Excel tính giá trị trung bình của các giá trị trong ô A1 và B1 và ​​xuất giá trị đó trong ô C1 . Bạn có thể đảm bảo công thức của mình hoạt động bằng cách nhập các số vào A1 và B1. C1 sẽ hiển thị trung bình của họ.

  3. Đối với ví dụ này, hãy thay đổi giá trị của ô A1 thành 16 .
  4. Chọn ô C1 , sau đó từ ruy-băng, chuyển đến tab Dữ liệu .
  5. Trong tab Dữ liệu , hãy chọn Phân tích điều gì xảy ra và sau đó chọn Tìm kiếm mục tiêu . Thao tác này sẽ hiển thị cửa sổ Tìm kiếm mục tiêu.
  6. Trong cửa sổ Tìm kiếm Mục tiêu, đặt ô Đặt thành C1 . Đây sẽ là ô mục tiêu của bạn. Đã đánh dấu C1 trước khi mở cửa sổ Tìm kiếm Mục tiêu sẽ tự động đặt nó trong Đặt ô .
  7. Trong Giá trị thành, hãy chèn giá trị mục tiêu bạn muốn. Đối với ví dụ này, chúng tôi muốn trung bình của mình là 26, vì vậy giá trị mục tiêu cũng sẽ là 26.
  8. Cuối cùng, trong ô Bằng cách thay đổi , hãy chọn ô bạn muốn thay đổi để đạt được mục tiêu. Trong ví dụ này, đây sẽ là ô B2 .
  9. Nhấp vào OK để Goal Seek hoạt động kỳ diệu.
 

Sau khi bạn nhấp vào OK, một hộp thoại sẽ xuất hiện thông báo cho bạn biết rằng Goal Seek đã tìm ra giải pháp.

Giá trị của ô A1 sẽ thay đổi thành giải pháp và điều này sẽ ghi đè lên dữ liệu trước đó. Bạn nên chạy Goal Seek trên bản sao của biểu dữ liệu để tránh mất dữ liệu gốc.

Ví dụ tìm kiếm mục tiêu 2

Tìm kiếm Mục tiêu có thể hữu ích khi bạn sử dụng nó trong các tình huống thực tế. Tuy nhiên, để sử dụng Goal Seek phát huy hết tiềm năng của nó, bạn cần phải có một công thức phù hợp để bắt đầu.

Đối với ví dụ này, hãy lấy một trường hợp ngân hàng nhỏ. Giả sử rằng bạn có một tài khoản ngân hàng cho bạn lãi suất 4% hàng năm trên số tiền bạn có trong tài khoản của mình.

Sử dụng Phân tích Điều gì xảy ra và Tìm kiếm Mục tiêu, bạn có thể tính toán số tiền bạn cần có trong tài khoản của mình để nhận được một số tiền thanh toán lãi suất hàng tháng.

Đối với ví dụ này, giả sử chúng tôi muốn nhận được 350 đô la mỗi tháng từ các khoản thanh toán lãi suất. Đây là cách bạn có thể tính toán điều đó:

  1. Trong ô A1 , nhập Số dư .
  2. Trong ô A2 , nhập Tỷ lệ Hàng năm .
  3. Trong ô A4 , nhập Lợi nhuận hàng tháng .
  4. Trong ô B2 gõ 4% .
  5. Chọn ô B4 và trong thanh công thức, nhập công thức bên dưới:
    =B1*B2/12

    Lợi nhuận hàng tháng sẽ bằng số dư tài khoản nhân với tỷ lệ hàng năm và sau đó chia cho số tháng trong năm, 12 tháng.

  6. Chuyển đến tab Dữ liệu , nhấp vào Phân tích Điều gì xảy ra, rồi chọn Tìm kiếm Mục tiêu .
  7. Trong cửa sổ Tìm kiếm Mục tiêu , nhập B4 vào Đặt ô .
  8. Nhập 350 vào ô Đến giá trị . (Đây là lợi nhuận hàng tháng bạn muốn nhận được)
  9. Gõ B1 vào Bằng cách thay đổi ô . (Điều này sẽ thay đổi số dư thành một giá trị sẽ mang lại 350 đô la hàng tháng)
  10. Bấm OK . Hộp thoại Trạng thái Tìm kiếm Mục tiêu sẽ bật lên.

Trong hộp thoại Trạng thái Tìm kiếm Mục tiêu, bạn sẽ thấy rằng Tìm kiếm Mục tiêu đã tìm thấy giải pháp trong ô B4. Trong ô B1, bạn có thể thấy giải pháp, giá trị này phải là 105.000.

Yêu cầu Tìm kiếm Mục tiêu

Với Goal Seek, bạn có thể thấy rằng nó chỉ có thể thay đổi một ô để đạt được giá trị mục tiêu. Điều này có nghĩa là Goal Seek không thể giải quyết và đạt được giải pháp nếu nó có nhiều hơn một biến.

Trong Excel, bạn vẫn có thể giải nhiều hơn một biến, nhưng bạn sẽ cần sử dụng một công cụ khác có tên là Solver. Bạn có thể đọc thêm về Excel’s Solver bằng cách đọc bài viết của chúng tôi về Cách sử dụng Excel’s Solver .

Giới hạn Tìm kiếm Mục tiêu

Công thức Tìm kiếm Mục tiêu sử dụng quy trình thử nghiệm và cải tiến để đạt được giá trị tối ưu, điều này có thể gây ra một số vấn đề khi công thức tạo ra một giá trị không xác định. Đây là cách bạn có thể tự kiểm tra:

  1. Trong ô A1 , nhập 3 .
  2. Chọn ô C1 , sau đó trong thanh công thức nhập công thức bên dưới:
    =1/(A1-5)

    Công thức này sẽ chia một cho A1-5 và nếu A1-5 bằng 0, kết quả sẽ là một giá trị không xác định.

  3. Chuyển đến tab Dữ liệu , nhấp vào Phân tích Điều gì sẽ xảy ra và sau đó chọn Tìm kiếm Mục tiêu .
  4. Trong Đặt ô , nhập C1 .
  5. Trong Để giá trị , hãy nhập 1 .
  6. Cuối cùng, trong Bằng cách thay đổi loại ô A1 . (Điều này sẽ thay đổi giá trị A1 thành 1 trong C1)
  7. Bấm OK .
 

Hộp thoại Trạng thái Tìm kiếm Mục tiêu sẽ cho bạn biết rằng nó có thể chưa tìm ra giải pháp và bạn có thể kiểm tra lại xem giá trị A1 có bị tắt hay không.

Bây giờ, giải pháp cho Tìm kiếm Mục tiêu này sẽ đơn giản là có 6 trong A1. Điều này sẽ cho 1/1, bằng 1. Nhưng tại một thời điểm trong quá trình thử nghiệm và cải tiến, Excel cố gắng 5 trong A1, cho kết quả là 1/0, không được xác định, giết chết quá trình.

Một cách giải quyết vấn đề Tìm kiếm Mục tiêu này là có một giá trị bắt đầu khác, một giá trị có thể tránh được giá trị không xác định trong quá trình thử nghiệm và cải tiến.

Ví dụ: nếu bạn thay đổi A1 thành bất kỳ số nào lớn hơn 5 và sau đó lặp lại các bước tương tự với Tìm kiếm mục tiêu cho 1, bạn sẽ nhận được kết quả chính xác.

Làm cho các phép tính hiệu quả hơn với tính năng tìm kiếm mục tiêu

Phân tích Điều gì xảy ra và Tìm kiếm Mục tiêu có thể tăng tốc độ tính toán với các công thức của bạn bằng một biện pháp tuyệt vời. Học các công thức đúng là một bước quan trọng khác để trở thành một người thành thạo excel.

Vân Nguyễn

Dịch từ: https://www.makeuseof.com/how-to-use-goal-seek-in-excel/

Bình luận (
0
)

Graphic Noti Complete Course

Chúc mừng học viên

Hoàng Lê Tuấn Nam

Đã xuất sắc hoàn thành

Kỹ sư phần mềm cơ bản - Basic Software Engineer đạt học bổng 5%

Bài liên quan

  • Tầng 0, tòa nhà FPT, 17 Duy Tân, Q. Cầu Giấy, Hà Nội
  • info@funix.edu.vn
  • 0782313602 (Zalo, Viber)        

yêu cầu gọi lại

error: Content is protected !!