Excel 2019 (Phần 28): What-if Analysis (Phân tích Nếu-thì)

Excel bao gồm các công cụ mạnh mẽ để thực hiện những phép tính toán học phức tạp, bao gồm cả What-if Analysis (Phân tích Nếu-thì). Tính năng này có thể giúp bạn thử nghiệm và trả lời các câu hỏi với dữ liệu của mình, ngay cả khi dữ liệu chưa đầy đủ. Trong bài viết này, bạn sẽ học cách sử dụng một công cụ phân tích có tên là Goal Seek.

Goal Seek

Bất cứ khi nào bạn tạo một công thức hoặc hàm trong Excel, bạn đều phải kết hợp các phần khác nhau lại để tính toán kết quả. Goal Seek hoạt động theo cách ngược lại: Nó cho phép bạn bắt đầu với kết quả mong muốn và công cụ sẽ tính toán giá trị đầu vào cung cấp cho bạn kết quả đó. Bài viết sẽ sử dụng một vài ví dụ để chỉ ra cách sử dụng Goal Seek.

Ví dụ 1 về cách sử dụng Goal Seek

Giả sử bạn đã đăng ký vào một lớp học. Bạn hiện có 65 điểm và bạn cần đạt ít nhất 70 điểm để qua môn này. May mắn thay, bạn có một bài tập cuối cùng có thể giúp nâng mức điểm trung bình. Bạn có thể sử dụng Goal Seek để tìm ra điểm bạn cần đạt trong bài tập cuối cùng để qua môn này.

Trong hình ảnh bên dưới, bạn có thể thấy rằng điểm của 4 bài tập đầu tiên là 58, 70, 72 và 60. Mặc dù chưa biết bài thứ 5 sẽ có điểm số như thế nào, nhưng bạn có thể viết một công thức - hoặc hàm - để tính ra được điểm số cuối cùng cần đạt. Trong trường hợp này, mỗi bài tập có trọng số như nhau, vì vậy tất cả những gì chúng ta phải làm là tính trung bình cho cả 5 đầu điểm bằng cách nhập =AVERAGE(B2:B6). Khi sử dụng Goal Seek, ô B6 sẽ hiển thị điểm tối thiểu cần đạt được cho bài tập cuối cùng đó.

1. Chọn ô có giá trị bạn muốn thay đổi. Bất cứ khi nào sử dụng Goal Seek, bạn sẽ cần chọn một ô đã chứa công thức hoặc hàm. Ví dụ sẽ chọn ô B7 vì nó chứa công thức =AVERAGE(B2:B6).

2. Từ tab Data, hãy nhấp vào lệnh What-If Analysis, sau đó chọn Goal Seek từ menu drop-down.

3. Một hộp thoại sẽ xuất hiện với ba trường. Trường đầu tiên, Set cell:, sẽ chứa kết quả mong muốn. Trong ví dụ, ô B7 đã được chọn.

Trường thứ hai, To value:, là kết quả mong muốn. Ví dụ sẽ nhập 70 vì cần ít nhất kiếm được số đó để vượt qua lớp học.

Trường thứ ba, By changing cell:, là ô mà Goal Seek sẽ đặt câu trả lời của nó. Ví dụ sẽ chọn ô B6 vì ta muốn xác định điểm cần đạt được trong bài tập cuối cùng.

4. Khi bạn hoàn tất, hãy nhấp vào OK.

5. Hộp thoại sẽ cho bạn biết liệu Goal Seek có thể tìm ra đáp án hay không. Bấm OK.

6. Kết quả sẽ xuất hiện trong ô được chỉ định. Trong ví dụ, Goal Seek đã tính toán rằng sẽ cần phải đạt ít nhất 90 điểm trong bài tập cuối cùng để qua môn.

Ví dụ 2 về cách sử dụng Goal Seek

Giả sử bạn đang lên kế hoạch cho một sự kiện và muốn mời nhiều người nhất có thể mà không vượt quá ngân sách $500. Bạn có thể sử dụng Goal Seek để tìm ra số lượng người cần mời. Trong ví dụ dưới đây, ô B5 chứa công thức =B2+B3*B4 để tính tổng chi phí đặt phòng cộng với chi phí cho mỗi người.

1. Chọn ô có giá trị bạn muốn thay đổi. Ví dụ sẽ chọn ô B5.

2. Từ tab Data, hãy nhấp vào lệnh What-If Analysis, sau đó chọn Goal Seek từ menu drop-down.

3. Một hộp thoại sẽ xuất hiện với ba trường. Trường đầu tiên, Set cell:, sẽ chứa kết quả mong muốn. Trong ví dụ, ô B5 đã được chọn.

Trường thứ hai, To value:, là kết quả mong muốn. Ví dụ sẽ nhập 500 vì chỉ muốn chi tiêu $500.

Trường thứ ba, By changing cell:, là ô mà Goal Seek sẽ đặt câu trả lời của nó. Ví dụ sẽ chọn ô B4 vì muốn biết có thể mời bao nhiêu khách mà không phải chi nhiều hơn $500.

4. Khi bạn hoàn tất, hãy nhấp vào OK.

5. Hộp thoại sẽ cho bạn biết liệu Goal Seek có thể tìm ra đáp án hay không. Bấm OK.

6. Kết quả sẽ xuất hiện trong ô được chỉ định. Trong ví dụ, Goal Seek đã tính toán câu trả lời là khoảng 18,62. Trong trường hợp này, câu trả lời cuối cùng cần phải là một số nguyên, vì vậy cần làm tròn câu trả lời lên hoặc xuống. Vì làm tròn lên sẽ vượt quá ngân sách, nên ví dụ sẽ làm tròn xuống còn 18 khách.

Như bạn có thể thấy trong ví dụ trên, một số tình huống sẽ yêu cầu câu trả lời là một số nguyên. Nếu Goal Seek cung cấp cho bạn một số thập phân, bạn sẽ cần làm tròn lên hoặc xuống, tùy thuộc vào tình huống.

Các loại phân tích what-if khác

Đối với các dự án nâng cao hơn, bạn có thể muốn xem xét những loại phân tích giả định khác: Kịch bản và bảng dữ liệu. Thay vì bắt đầu từ kết quả mong muốn và làm việc ngược lại, như với Goal Seek, các tùy chọn này cho phép bạn kiểm tra nhiều giá trị và xem kết quả thay đổi như thế nào.

Các kịch bản cho phép bạn thay thế các giá trị cho nhiều ô (tối đa 32) cùng một lúc. Bạn có thể tạo bao nhiêu kịch bản tùy thích và sau đó so sánh chúng mà không cần thay đổi các giá trị theo cách thủ công. Ví dụ dưới đây đang sử dụng các tình huống để so sánh những địa điểm khác nhau cho một sự kiện sắp tới.

Bảng dữ liệu cho phép bạn lấy một hoặc hai biến trong công thức và thay thế chúng bằng nhiều giá trị khác nhau tùy thích, sau đó xem kết quả trong bảng. Tùy chọn này đặc biệt mạnh mẽ vì nó hiển thị nhiều kết quả cùng một lúc, không giống như các kịch bản hoặc Goal Seek. Trong ví dụ dưới đây, có thể xem 24 kết quả có khả năng xảy ra đối với khoản vay mua ô tô.