Cách sử dụng Goal Seek trong Excel đơn giản và nhanh chóng

Trong quá trình sử dụng excel thì bạn không thể bỏ qua một công cụ giúp tính toán các giá trị logic chính xác đó là Goal Seek. Hôm nay Unica sẽ giới thiệu cụ thể cho các bạn về cách sử dụng goal seek trong excel đơn giản và nhanh chóng nhất nhé.

Goal Seek trong Excel là gì?

Goal Seek được biết đến là một chức năng của Excel để phân tích what-if. Được biết đến như cách phân tích độ nhạy cảm, giúp bạn hiểu điều gì có thể xảy ra khi bạn thay đổi một hoặc nhiều biến số. Hay nói cách khác đây là cách thực hiện tính toán ngược trong Excel.

Chức năng Goal Seek Excel được sử dụng như sau:

- Cho biết tỷ lệ tăng trưởng hằng tháng bạn cần tăng gấp đôi vào cuối năm là bao nhiêu?

- Bạn có thể chi trả bao nhiêu cho công việc thiết kế tự do mà không vượt ngân sách thuê ngoài?

- Bạn cần thu được bao nhiêu doanh thu để có thể tạo ra lợi nhuận cho chiến dịch tiếp thị email của bạn?

Để tìm kiếm câu trả lời cho những câu hỏi này thay vì tự hỏi ” Điều gì sẽ xảy ra nếu ” khi xây dựng chiến lược, khi đó bạn có thể loại bỏ điều không chắc chắn và đưa cho mình một hướng đi phù hợp hơn.

Goal Seek trong Excel

Tổng quan công thức Goal seek trong Excel 

Goal seek được chia làm 3 phần chính đó là:

- Set cell (đặt ô): Là ô mà bạn muốn dùng để làm mục tiêu của mình

- To value (thành giá trị): Giá trị mà bạn muốn làm mục tiêu

- By changing cell (bằng cách thay đổi ô): Ô mà bạn muốn thay đổi để đạt được giá trị mục tiêu

Chỉ với 3 thành phần này, Excel sẽ cải thiện giá trị trong ô của bạn tại By changing cell đến khi ô bạn đặt trong Set cell đạt đến giá trị bạn đã xác định trong To value.

Ví dụ minh họa:

Giả sử có 2 ô là A và B, một ô thứ 3 tính giá trị trung bình của 2 ô này. Giả sử bạn có một giá trị cố định cho A và bạn muốn tăng mức trung bình bằng cách thay đổi giá trị của B. Với Goal seek thì bạn có thể tính toán giá trị nào của B sẽ tạo ra mức trung bình bạn muốn.

Ứng dụng của Goal Seek 

- Ngoài những ứng dụng tìm kiếm mục tiêu thì goal seek còn có những hữu ích như sau:

- Được dùng để tìm kiếm mục tiêu bạn cần đạt được

- Tìm kiếm các điểm hòa vốn

- Mục tiêu đó là nâng cao doanh thu và lợi nhuận

- Đặc biệt quan trọng với những bạn làm trong lĩnh vực kinh tế.

Cách tìm mục tiêu bằng Goal Seek trong Excel

Ví dụ 1: Dùng Goal Seek tìm ra số điểm cần đạt để lên lớp

Giả sử có một 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 số 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 để có thể vượt qua lớp, Goal Seek sẽ giúp bạn giải quyết tất cả vấn đề này.

Bước 1: Mở file Excel có số điểm cần tính > Lựa chọn thẻ Data.

Nhấn chọn Data

Bước 2: Tại phần Data Tools > Chọn vào lệnh What-If Analysis > Nhấn chọn Goal Seek… từ menu thả xuống.

Vào phần Goal seek

>> Xem thêm: Hướng dẫn cách quy đổi tỷ giá trong Excel chi tiết nhất

Bước 3: Ở hộp thoại Goal Seek > Bạn sẽ nhập các dữ liệu như sau > Nhấn OK để tiến hành chạy Goal Seek.

- Set cell: Ô chứa công thức tính 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 để có thể vượt qua lớp là 5,5.

- By changing cell: Đây là ô Goal Seek sẽ đưa ra giá trị để đạt được kết quả vừa nhập vào trong ô To value, ví dụ điểm môn Anh văn cần đạt ở trong ô D10.

Nhấn chọn OK

Bước 4: Goal Seek sẽ tính toán và đưa ra kết quả là môn Anh văn cần đạt 8.5 điểm để vượt qua lớp.

Kết quả đưa ra

Sử dụng Goal Seek tìm số tiền cho vay với khoản trả góp hàng tháng là 3.500.000 đồng

Ví dụ 2: Ông A vay một khoản 500,000,000 đồng để mua nhà, trong thời hạn là 120 tháng, 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 phải vay trong thời hạn dài hơn. Bạn có thể dùng Goal Seek để tìm ra số tháng vay cụ thể với khoản trả góp hàng tháng sẽ là 3,500,000 đồng.

Ví dụ 2

Bước 1: Mở file Excel > Sau đó chọn thẻ Data.

Chọn vào Data

Bước 2: Tại phần Data Tools > Chọn lệnh What-If Analysis > Chọn mục Goal Seek… từ menu thả xuống.

Bấm chọn Goal seek

Bước 3: Ở hộp thoại Goal Seek xuất hiện > Bạn hãy nhập các dữ liệu như sau > Rồi nhấn OK.

- Set cell: Ô này chứa công thức tính toán của bài toán, ví dụ công thức tính số tiền trả góp hàng tháng nằm trong ô C6 (Ô được nhập phải là công thức thì Goal Seek mới chấp nhận).

- To value: Khoản trả góp hàng tháng mà bạn có thể chi trả được, ví dụ khoản trả góp là 3,500,000 đồng/tháng.

- By changing cell: Nơi Goal Seek sẽ đưa ra được số tháng vay cụ thể đối với khoản trả góp hàng tháng vừa được nhập vào ô To value, trong ví dụ này là ô C3.

Chọn vào OK

Bước 4: Goal Seek sẽ thực hiện việc 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ể lựa chọn thời hạn vay trong 358 tháng.

Kết quả

Tìm hiểu tính chính xác của Goal Seek trong Excel

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, đáp án gần đúng. Tuy nhiên, bạn có thể thiết lập cài đặt để tính toán chính xác như sau:

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

Công thức ở ô B1 dùng để tính bình phương giá trị ô A1

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

Tìm ra giá trị đầu vào

Ở đây Goal Seek sẽ tính toán ra với kết quả gần đúng.

Đưa ra kết quả gần đúng

Bước 3: Chọn thẻ File > Và nhấn chọn Options.

Chọn mục Options

Bước 4: Chọn vào phần Formulas > Dưới mục Calculation options > Bạn sẽ giảm giá trị của Maximum Change bằng cách thêm vào số 0, so với giá trị mặc định vốn có là 0,001 > Cuối cùng nhấn OK.

Chọn vào OK

Bước 5: Sử dụng Goal Seek lần nữa và Excel sẽ trả cho bạn đáp án chính xác nhất.

Đáp án chính xác cuối

Những điều bạn cần biết về Goal Seek

Có rất 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à một ô đơn lẻ. Do vậy nếu bạn có dữ liệu đầu vào là nhiều ô thì không thể giải quyết được với Goal Seek.

Bạn có thể dùng Solver trong Excel để giải quyết vấn đề nếu có nhiều giá trị đầu vào. Hoặc nhập giá trị đầu vào khác để tìm ra đáp án chính xác.

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

Kết quả - 0.25

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

Kết quả là + 0.25

Bước 3: Goal Seek sẽ báo lỗi không thể thực hiện được > Bạn hãy nhấn Cancel để tắt phần báo lỗi đi.

Nhấn Cancel

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

Bắt đầu giá trị lớn hơn 8

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

Với 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). Ở 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 của trục hoành (x < 8)="" hoặc="" ngược="">.

Dùng Goal Seek một lần nữa và Excel sẽ cho bạn đáp án

Tổng kết

Unica đã tổng hợp và giới thiệu cho các bạn cách sử dụng goal seek trong excel đơn giản và hiệu quả phục vụ cho mục đích công việc của mình. Nếu muốn nắm được nhiều kỹ năng tin học văn phòng thì đừng bỏ lỡ khóa học excel trên Unica nhé.

>> Xem thêm: 5 Cách tính tổng trong excel đơn giản nhất