Cách sử dụng Tìm kiếm mục tiêu trong Excel để thực hiện phân tích What-If

Hướng dẫn giải thích cách sử dụng Mục tiêu tìm kiếm trong Excel 2016, 2013 và 2010 để có được kết quả công thức bạn muốn bằng cách thay đổi giá trị đầu vào.

Phân tích What-If là một trong những tính năng mạnh mẽ nhất của Excel và là một trong những tính năng ít được hiểu nhất. Trong hầu hết các thuật ngữ chung, Phân tích What-If cho phép bạn kiểm tra các tình huống khác nhau và xác định một loạt các kết quả có thể xảy ra. Nói cách khác, nó cho phép bạn thấy tác động của việc thực hiện một thay đổi nhất định mà không thay đổi dữ liệu thực. Trong hướng dẫn cụ thể này, chúng tôi sẽ tập trung vào một trong những công cụ Phân tích What-If của Excel – Tìm kiếm mục tiêu.

Mục tiêu tìm kiếm trong Excel là gì?

Tìm kiếm mục tiêu là công cụ Phân tích What-If tích hợp của Excel cho biết cách một giá trị trong công thức tác động đến giá trị khác. Chính xác hơn, nó xác định giá trị nào bạn nên nhập trong một ô nhập liệu để có kết quả mong muốn trong một ô công thức.

Điều tốt nhất về Tìm kiếm mục tiêu Excel là nó thực hiện tất cả các tính toán đằng sau hậu trường và bạn chỉ được yêu cầu chỉ định ba tham số sau:

• Tế bào công thức
• Mục tiêu / giá trị mong muốn
• Các tế bào để thay đổi để đạt được mục tiêu

Công cụ Goal Seek đặc biệt hữu ích để thực hiện phân tích độ nhạy trong mô hình tài chính và được sử dụng rộng rãi bởi các chuyên gia quản lý và chủ doanh nghiệp. Nhưng có nhiều cách sử dụng khác có thể hữu ích cho bạn.

Chẳng hạn, Goal Seek có thể cho bạn biết bạn phải bán bao nhiêu trong một khoảng thời gian nhất định để đạt được lợi nhuận ròng hàng năm $ 100.000 (ví dụ 1). Hoặc, số điểm bạn phải đạt được cho bài kiểm tra cuối cùng của mình để nhận được tổng điểm vượt qua là 70% (ví dụ 2). Hoặc, bạn cần nhận bao nhiêu phiếu để giành chiến thắng trong cuộc bầu cử (ví dụ 3).

Nhìn chung, bất cứ khi nào bạn muốn một công thức trả về một kết quả cụ thể nhưng không chắc chắn giá trị đầu vào nào trong công thức cần điều chỉnh để có kết quả đó, hãy ngừng đoán và sử dụng hàm Tìm kiếm mục tiêu Excel!

Ghi chú. Mục tiêu tìm kiếm chỉ có thể xử lý một giá trị đầu vào tại một thời điểm. Nếu bạn đang làm việc trên một mô hình kinh doanh nâng cao với nhiều giá trị đầu vào, hãy sử dụng Bộ giải bổ trợ để tìm ra giải pháp tối ưu.

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

Mục đích của phần này là hướng dẫn bạn cách sử dụng chức năng Tìm kiếm mục tiêu. Vì vậy, chúng tôi sẽ làm việc với một bộ dữ liệu rất đơn giản:

Bảng trên chỉ ra rằng nếu bạn bán 100 mặt hàng với giá 5 đô la mỗi mặt hàng, trừ đi khoản hoa hồng 10%, bạn sẽ kiếm được 450 đô la. Câu hỏi là: Bạn phải bán bao nhiêu mặt hàng để kiếm được 1.000 đô la?

Hãy xem cách tìm câu trả lời với Tìm kiếm mục tiêu:

1. Thiết lập dữ liệu của bạn để bạn có một tế bào công thức và một thay đổi tế bào phụ thuộc vào ô công thức.
2. Đi đến Dữ liệu tab> Dự báo nhóm, nhấp vào Những gì nếu phân tích và chọn Mục tiêu tìm kiếm
   
3. bên trong Tìm kiếm mục tiêu hộp thoại, xác định các ô / giá trị để kiểm tra và nhấp đồng ý:
   • Đặt ô – tham chiếu đến ô chứa công thức (B5).
   • Giá trị – kết quả công thức bạn đang cố gắng đạt được (1000).
   • Bằng cách thay đổi tế bào – tham chiếu cho ô nhập mà bạn muốn điều chỉnh (B3).

   

4. Các Tìm kiếm mục tiêu Trạng thái hộp thoại sẽ xuất hiện và cho bạn biết nếu một giải pháp đã được tìm thấy. Nếu thành công, giá trị trong “ô thay đổi” sẽ được thay thế bằng một ô mới. Nhấp chuột đồng ý để giữ giá trị mới hoặc Hủy bỏ để khôi phục lại bản gốc.

   Trong ví dụ này, Goal Seek đã phát hiện ra rằng 223 mặt hàng (được làm tròn đến số nguyên tiếp theo) cần được bán để đạt doanh thu 1.000 đô la.

   

Nếu bạn không chắc chắn bạn sẽ có thể bán nhiều mặt hàng đó, thì có lẽ bạn có thể đạt được doanh thu mục tiêu bằng cách thay đổi giá mặt hàng? Để kiểm tra kịch bản này, hãy thực hiện phân tích Tìm kiếm mục tiêu chính xác như được mô tả ở trên ngoại trừ việc bạn chỉ định khác Thay đổi tế bào (B2):

Kết quả là, bạn sẽ phát hiện ra rằng nếu bạn tăng đơn giá lên 11 đô la, bạn có thể đạt doanh thu 1.000 đô la chỉ bằng cách bán 100 mặt hàng:

Mẹo và ghi chú:

• Tìm kiếm mục tiêu Excel không thay đổi công thức, nó chỉ thay đổi giá trị đầu vào mà bạn cung cấp cho Bằng cách thay đổi tế bào cái hộp.
• Nếu Mục tiêu tìm kiếm không thể tìm thấy giải pháp, nó sẽ hiển thị giá trị gần nhất nó đã đưa ra
• Bạn có thể khôi phục giá trị đầu vào ban đầu bằng cách nhấp vào Hoàn tác hoặc nhấn phím tắt Hoàn tác (Ctrl + Z).

Ví dụ về sử dụng Tìm kiếm mục tiêu trong Excel

Dưới đây bạn sẽ tìm thấy một vài ví dụ nữa về cách sử dụng chức năng Tìm kiếm mục tiêu trong Excel. Sự phức tạp của mô hình kinh doanh của bạn không thực sự quan trọng miễn là công thức của bạn trong Đặt ô phụ thuộc vào giá trị trong Thay đổi tế bào, trực tiếp hoặc thông qua các công thức trung gian trong các tế bào khác.

Ví dụ 1: Đạt được mục tiêu lợi nhuận

Vấn đề: Đó là một tình huống kinh doanh điển hình – bạn có số liệu bán hàng trong 3 quý đầu tiên và bạn muốn biết bạn phải bán bao nhiêu trong quý vừa qua để đạt được lợi nhuận ròng mục tiêu trong năm, giả sử, 100.000 đô la.

Giải pháp: Với dữ liệu nguồn được sắp xếp như hiển thị trong ảnh chụp màn hình ở trên, hãy thiết lập các tham số sau cho chức năng Tìm kiếm mục tiêu:

• Đặt ô – công thức tính tổng lợi nhuận ròng (D6).
• Giá trị – kết quả công thức bạn đang tìm kiếm (100.000 đô la).
• Bằng cách thay đổi tế bào – ô để chứa tổng doanh thu cho quý 4 (B5).

Kết quả: Phân tích Tìm kiếm mục tiêu cho thấy rằng để có được lợi nhuận ròng hàng năm $ 100.000, doanh thu quý IV của bạn phải là $ 185,714.

Ví dụ 2: Xác định điểm vượt qua bài kiểm tra

Vấn đề: Kết thúc khóa học, một sinh viên làm 3 bài kiểm tra. Điểm vượt qua là 70%. Tất cả các bài kiểm tra có cùng trọng số, vì vậy điểm tổng thể được tính bằng cách tính trung bình 3 điểm. Học sinh đã thực hiện 2 trong 3 bài kiểm tra. Câu hỏi là: Học sinh cần đạt điểm nào cho kỳ thi thứ ba để vượt qua toàn bộ khóa học?

Giải pháp: Hãy thực hiện Tìm kiếm mục tiêu để xác định điểm tối thiểu trong bài kiểm tra 3:

• Đặt ô – công thức tính trung bình điểm của 3 bài kiểm tra (B5).
• Giá trị – điểm vượt qua (70%).
• Bằng cách thay đổi tế bào – 3^{lần thứ} điểm thi (B4).

Kết quả: Để có được điểm tổng thể mong muốn, học sinh phải đạt tối thiểu 67% trong bài kiểm tra cuối cùng:

Ví dụ 3: Phân tích What-If của cuộc bầu cử

Vấn đề: Bạn đang tranh cử một số vị trí được bầu trong đó đa số hai phần ba (66,67% số phiếu) được yêu cầu để giành chiến thắng trong cuộc bầu cử. Giả sử có 200 tổng số thành viên bỏ phiếu, bạn có bao nhiêu phiếu để đảm bảo?

Hiện tại, bạn có 98 phiếu, khá tốt nhưng chưa đủ vì nó chỉ chiếm 49% tổng số cử tri:

Giải pháp: Sử dụng Tìm kiếm mục tiêu để tìm ra số phiếu tối thiểu “Có” bạn cần nhận:

• Đặt ô – công thức tính tỷ lệ phần trăm của phiếu “Có” hiện tại (C2).
• Giá trị – tỷ lệ phần trăm yêu cầu của phiếu “Có” (66,67%).
• Bằng cách thay đổi tế bào – số phiếu “Có” (B2).

Kết quả: Phân tích What-If với Goal Seek cho thấy rằng để đạt được hai phần ba hoặc 66,67%, bạn cần 133 phiếu “Có”:

Mục tiêu Excel Tìm kiếm không hoạt động

Đôi khi Goal Seek không thể tìm ra giải pháp đơn giản vì nó không tồn tại. Trong các tình huống như vậy, Excel sẽ nhận được giá trị gần nhất và thông báo cho bạn rằng Tìm kiếm mục tiêu có thể chưa tìm thấy giải pháp:

Nếu bạn chắc chắn rằng một giải pháp cho công thức bạn đang cố gắng giải quyết đã tồn tại, hãy xem các mẹo khắc phục sự cố sau.

1. Kiểm tra kỹ các tham số Tìm kiếm mục tiêu

Trước hết, hãy chắc chắn rằng Đặt ô đề cập đến ô chứa công thức, và sau đó, kiểm tra xem ô công thức phụ thuộc trực tiếp hay gián tiếp vào ô thay đổi.

2. Điều chỉnh cài đặt lặp

Trong Excel của bạn, bấm vào Tập tin > Tùy chọn > Công thức và thay đổi các tùy chọn sau:

• Lặp lại tối đa – tăng số này nếu bạn muốn Excel thử nghiệm các giải pháp khả thi hơn.
• Thay đổi tối đa – giảm số này nếu công thức của bạn đòi hỏi độ chính xác cao hơn. Ví dụ: nếu bạn đang kiểm tra công thức có ô nhập bằng 0 nhưng Tìm kiếm mục tiêu dừng ở 0,001, cài đặt Thay đổi tối đa đến 0,0001 nên khắc phục sự cố.

Ảnh chụp màn hình bên dưới hiển thị các cài đặt lặp mặc định:

3. Không có tài liệu tham khảo thông tư

Để Tìm kiếm mục tiêu (hoặc bất kỳ công thức Excel nào) hoạt động chính xác, các công thức liên quan không nên phụ thuộc lẫn nhau, nghĩa là không nên có tài liệu tham khảo thông tư.

Đó là cách bạn thực hiện phân tích What-If trong Excel bằng công cụ Tìm kiếm mục tiêu. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Bạn cũng có thể quan tâm:

Source link

The post Cách sử dụng Tìm kiếm mục tiêu trong Excel để thực hiện phân tích What-If appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.

source https://ketoanmvb.com/cach-su-dung-tim-kiem-muc-tieu-trong-excel-de-thuc-hien-phan-tich-what-if.html