Trong bài viết này chúng ta sẽ tìm hiểu tất tần tật chủ đề What-If Analysis trong Excel nhé!

Note: Bạn có thể kéo xuống dưới cùng để download tài liệu của bài viết này.

Bạn đang xem: Cách sử dụng what if analysis trong excel

What-If Analysis

What-If Analysis trong Excel cho phép bạn thử các giá trị (scenarios) khác nhau cho các công thức. Ví dụ sau đây sẽ giúp bạn nắm vững What-If Analysis một cách nhanh chóng và dễ dàng.

Giả sử bạn sở hữu một cửa hàng sách và hiện đang có hơn 100 cuốn sách trong kho. Giả bạn bán một cuốn sách với certain % giá cao nhất là 50 đô và certain % giá thấp nhất là 20 đô.

*

Nếu bạn bán được 60% với giá cao nhất, ô D10 sẽ tính tổng lợi nhuận là 60 * $50 + 40 * $20 = $3800.

Tạo Scenarios (kịch bản) khác nhau

Nhưng nếu bạn bán 70% với giá cao nhất thì sao? Hay bạn bán 80% với giá cao nhất? Hoặc 90%, hoặc thậm chí 100%? Mỗi tỷ lệ phần trăm khác nhau sẽ cho ra một scenarios khác nhau. Bạn có thể sử dụng Scenario Manager để tạo các scenarios này.

Lưu ý: Bạn chỉ cần nhập một tỷ lệ phần trăm khác vào ô C4 để xem kết quả tương ứng với tình huống trong ô D10. Tuy nhiên, what-if analysis cho phép bạn dễ dàng so sánh kết quả của các scenarios khác nhau.

1. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

*

2. Click Scenario Manager.

*

Hộp thoại Scenario Manager xuất hiện.

3. Thêm một scenario bằng cách click vào Add.

*

4. Nhập tên (60% cao nhất), chọn ô C4 (% được bán với giá cao nhất) cho Changing cells và click OK.

*

5. Nhập giá trị tương ứng 0.6 và click OK một lần nữa.

*

6. Tiếp theo, thêm 4 scenarios khác (70%, 80%, 90% và 100%).

Cuối cùng, Scenario Manager của bạn phải nhất quán với hình bên dưới:

*
Lưu ý: để xem kết quả của một scenario,, hãy chọn scenario và click vào nút Show. Excel sẽ thay đổi giá trị của ô C4 tương ứng để bạn xem kết quả tương ứng trên sheet.

Scenario Summary

Để dễ dàng so sánh kết quả của các scenario này, bạn hãy thực hiện các bước sau.

1. Click vào nút Summary button trong Scenario Manager.

2. Tiếp theo, bạn chọn ô D10 (total profit) cho ô kết quả và click OK.

*

Kết quả:

*

Kết luận: nếu bạn bán 70% với giá cao nhất, bạn sẽ thu được tổng lợi nhuận là $4100, còn nếu bạn bán 80% với giá cao nhất, bạn sẽ thu được tổng lợi nhuận là $4400,...

Goal Seek

Lợi nhuận mong muốn mà bạn muốn thu được là $4700, vậy bạn cần phải bán bao nhiêu cuốn sách với giá cao nhất? Bạn có thể nhờ tính năng Goal Seek của Excel giúp tìm ra câu trả lời.

1. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

2. Click Goal Seek.

Hộp thoại Goal Seek xuất hiện.

3. Chọn ô D10.

4. Click vào hộp "To value" và nhập 4700.

5. Click vào "By changing cell" và chọn ô C4.

6. Click OK.

Kết quả: Bạn cần bán 90% số sách với giá cao nhất để thu được tổng lợi nhuận chính xác là $4700.

Data Tables

Thay vì tạo các scenario khác nhau, bạn có thể tạo data table để nhanh chóng thử các giá trị khác nhau cho các công thức. Bạn có thể tạo data table một biến hoặc data table hai biến.

Giả sử bạn sở hữu một cửa hàng sách và có 100 cuốn sách trong kho. Bạn bán certain % nhất định với giá cao nhất là 50 đô la và certain % nhất định cho giá thấp hơn là 20 đô la. Nếu bạn bán 60% với giá cao nhất, ô D10 bên dưới sẽ có công thức tính lợi nhuận là 60 * $50 + 40 * $20 = $3800.

One Variable Data Table

Để tạo data table một biến, bạn hãy thực hiện các bước sau.

1. Chọn ô B12 và nhập = D10 (tham khảo ô total profit).

2. Nhập các tỷ lệ phần trăm khác nhau vào column A.

3. Chọn phạm vi A12: B17.

Chúng ta sẽ tính được tổng lợi nhuận nếu bạn bán 60% cho giá cao nhất hoặc 70% cho giá cao nhất,...

4. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

5. Click Data Table.

6. Click vào hộp "Column input cell" (tỷ lệ phần trăm nằm trong một cột) và chọn ô C4.

Chúng ta chọn ô C4 vì tỷ lệ phần trăm tham chiếu đến ô C4 (% được bán với giá cao nhất) cùng với công thức trong ô B12. Excel hiện biết rằng nó nên thay thế ô C4 bằng 60% hoặc 70% để tính tổng lợi nhuận.

Lưu ý: đây là data table một biến nên chúng ta để trống ô nhập Row.

7. Click OK.

Kết quả.

Kết luận: nếu bạn bán 60% với giá cao nhất, bạn thu được tổng lợi nhuận là $3800, nếu bạn bán 70% với giá cao nhất, bạn sẽ thu được tổng lợi nhuận là $4100,...

Lưu ý: formula bar chỉ ra rằng các ô chứa công thức array. Do đó, bạn không thể xóa một kết quả duy nhất. Để xóa kết quả, hãy chọn phạm vi B13: B17 và nhấn Delete.

Two Variable Data Table

Để tạo một Data Table hai biến, bạn hãy thực hiện các bước sau.

1. Chọn ô A12 và nhập = D10 (tham khảo ô total profit).

2. Nhập các đơn vị lợi nhuận khác nhau (giá cao nhất) vào row 12.

3. Nhập các tỷ lệ phần trăm khác nhau vào column A.

4. Chọn phạm vi A12: D17.

Chúng ta sẽ tính toán tổng lợi nhuận cho các kết hợp khác nhau của "unit profit (highest price)" và "% sold for the highest price".

5. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

6. Click Data Table.

7. Click vào "Row input cell" (đơn vị lợi nhuận nằm trong một hàng) và chọn ô D7.

8. Click vào "Column input cell" (phần trăm nằm trong một cột) và chọn ô C4.

Chúng ta chọn ô D7 vì lợi nhuận đơn vị tham chiếu đến ô D7. Tương tự vậy, chúng ta chọn ô C4 vì tỷ lệ phần trăm tham chiếu đến ô C4. Cùng với công thức trong ô A12, Excel hiện biết rằng nó nên thay thế ô D7 bằng $50 và ô C4 bằng 60% để tính tổng lợi nhuận, thay ô D7 bằng $50 và ô C4 bằng 70% để tính tổng lợi nhuận,...

9. Click OK.

Kết quả:

Kết luận: nếu bạn bán 60% với giá cao nhất, với lợi nhuận đơn vị là $50, bạn thu được tổng lợi nhuận là $3800, nếu bạn bán 80% với giá cao nhất, với lợi nhuận đơn vị là $60, bạn thu được tổng lợi nhuận là $5200,...

Lưu ý: formula bar chỉ ra rằng các ô chứa công thức array. Do đó, bạn không thể xóa một kết quả duy nhất. Để xóa kết quả, hãy chọn phạm vi B13: D17 và nhấn Delete.

Goal Seek

Nếu bạn biết kết quả bạn muốn từ một công thức, hãy sử dụng Goal Seek trong Excel để tìm giá trị đầu vào và tạo ra kết quả với công thức này.

Ví dụ 1 về Goal Seek

Sử dụng Goal Seek trong Excel để tìm điểm trong bài kiểm tra thứ tư có final grade. là 70.

1. Công thức trong ô B7 tính final grade.

2. Điểm của bài kiểm tra thứ tư trong ô B5 là ô input.

3. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

4. Click Goal Seek.

Hộp thoại Goal Seek xuất hiện.

5. Chọn ô B7.

6. Click vào "To value" và nhập 70.

7. Click vào "By changing cell"" và chọn ô B5.

8. Click OK.

Kết quả: Điểm 90 trong kỳ thi thứ tư tạo ra điểm cuối cùng là 70.

Ví dụ 2 về Goal Seek

Sử dụng Goal Seek trong Excel để tìm số tiền cho vay tạo ra monthly payment là $1500.

1. Công thức trong ô B5 tính toán monthly payment.

Giải thích: chức năng PMT tính toán khoản thanh toán cho một khoản vay. Nếu bạn chưa bao giờ nghe nói về chức năng này trước đây thì không sao cả. Số tiền vay càng cao thì số tiền phải trả hàng tháng càng cao. Giả sử, bạn chỉ có thể chi trả $1500 một tháng vậy thì số tiền vay tối đa của bạn là bao nhiêu?

2. Số tiền cho vay trong ô B3 là ô input.

3. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

4. Click Goal Seek.

Hộp thoại Goal Seek xuất hiện.

5. Chọn ô B5.

6. Click vào hộp "To value" và nhấn -1500 (negative, bạn đang thanh toán tiền).

7. Click vào hộp "By changing cell" và chọn ô B3.

8. Click OK.

Kết quả: Khoản vay $250,187 tạo ra khoản thanh toán hàng tháng là $1500.

Goal Seek Precision

Goal seek trả về các giải pháp gần đúng. Bạn có thể thay đổi cài đặt lặp lại trong Excel để tìm ra giải pháp chính xác hơn.

1. Công thức trong ô B1 tính bình phương giá trị trong ô A1.

2. Sử dụng goal seek để tìm giá trị đầu vào tạo ra kết quả công thức là 25.

Kết quả: Excel trả về một giải pháp gần đúng.

3. Trên tab File, click Options, Formulas.

4. Trong tùy chọn Calculation, hãy giảm giá trị Maximum Change bằng cách insert một số zeros. Giá trị mặc định là 0,001.

5. Click OK.

6. Sử dụng Goal Seek một lần nữa. Excel sẽ trả về một giải pháp chính xác hơn.

Tìm hiểu thêm về Goal Seek

Có nhiều vấn đề mà Goal Seek không thể giải quyết. Goal Seek yêu cầu một ô đầu vào và một ô đầu ra (công thức). Sử dụng Solver trong Excel để giải quyết các vấn đề với nhiều ô input. Đôi khi bạn cần bắt đầu với một giá trị input khác để tìm ra giải pháp.

1. Công thức trong ô B1 dưới đây cho kết quả là -0,25.

2. Sử dụng Goal Seek để tìm giá trị input tạo ra kết quả công thức là +0,25.

Kết quả: Excel không tìm ra được giải pháp.

3. Click Cancel.

4. Bắt đầu với giá trị input lớn hơn 8.

5. Sử dụng lại Goal Seek lần nữa để Excel tìm ra giải pháp.

Giải thích: y = 1 / (x - 8) không liên tục tại x = 8 (không chia hết cho 0). Trong ví dụ này, Goal seek không thể tiếp cận một phía của trục x (x> 8) nếu nó bắt đầu ở phía bên kia của trục x (x 2 + bx + c = 0 trong đó a ≠ 0. Một phương trình bậc hai có thể được giải bằng cách sử dụng công thức bậc hai. Bạn cũng có thể sử dụng tính năng Goal Seek của Excel để giải phương trình bậc hai.

1. Ví dụ, chúng ta có công thức y = 3x2 - 12x + 9.5 Thật dễ dàng để tính y cho bất kỳ x nào cho trước. Với x = 1, y = 0,5

2. Với x = 2, y = -2.5

3. Nhưng nếu chúng ta muốn biết x với bất kỳ y nào cho trước thì sao? Ví dụ: y = 24,5. Ta cần giải phương trình 3x2 - 12x + 9.5 = 24.5. Ta có thể giải phương trình bậc hai này bằng cách sử dụng công thức bậc hai.

3x2 - 12x -15 = 0a = 3, b = -12, c = -15D = b2- 4ac = (-12)2 - 4 * 3 * -15 = 144 + 180 = 324

x =-b + √Dorx =-b - √D
2a2a
x =12 + √324orx =12 - √324
66
x =12 + 18orx =12 - 18
66
x =5orx =-1

4. Bạn có thể sử dụng tính năng Goal Seek của Excel để có được kết quả chính xác giống nhau. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

5. Click Goal Seek.

Hộp thoại Goal Seek xuất hiện.

6. Chọn ô B2.

7. Click vào hộp "To value" và nhập 24.5

8. Click vào hộp "By changing cell" và chọn ô A2.

9. Click OK.

Kết quả:

Lưu ý: Excel trả về giải pháp x = 5. Excel sẽ tìm ra giải pháp khác nếu bạn bắt đầu với giá trị x gần với x = -1. Ví dụ: nhập giá trị 0 vào ô A2 và lặp lại các bước từ 5 đến 9. Để tìm nghiệm nguyên, đặt y = 0 và giải phương trình bậc hai 33x2 - 12x + 9.5 = 0. Trong trường hợp này, đặt "To value" thành 0.

Tài liệu của series

Để chuẩn bị cho việc thực hành, bạn tải tài liệu của series bằng cách điền vào form bên dưới nhé!

Giới thiệu

Excel bao gồm các công cụ mạnh mẽ để thực hiện các phép tính toán học phức tạp, bao gồm cả phân tích giả sử. 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 học này, bạn sẽ học cách sử dụng một công cụ what-if analysis (phân tích giả sử) có tên là Goal Seek.Bạn đang xem: Cách sử dụng what if analysis trong excel

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 đặt các phần khác nhau lại với nhau để 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à nó sẽ tính toán giá trị đầu vào sẽ cung cấp cho bạn kết quả đó. Chúng mình sẽ sử dụng một vài ví dụ để chỉ ra cách sử dụng Goal Seek.

Sử dụng Goal Seek (ví dụ 1):

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 nhất 70 để vượt qua lớp này. May mắn thay, bạn có một bài tập cuối cùng có thể nâng mức trung bình của bạn. Bạn có thể sử dụng Goal Seek để tìm ra điểm bạn cần trong bài tập cuối cùng để vượt qua lớp học.

Trong hình ảnh bên dưới, bạn có thể thấy rằng điểm của bốn bài tập đầu tiên là 58, 70, 72 và 60. Mặc dù chúng ta không biết bài tập 5 như thế nào thế nhưng chúng ta lại biết số điểm cần thiết để vượt qua lớp học nên có thể dự đoán được số điểm cần thiết cho bài tập cuối. 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 bài tập bằng cách nhập = AVERAGE (G2: G6). Khi chúng ta sử dụng Goal Seek, ô G6 sẽ hiển thị cho chúng ta điểm tối thiểu mà chúng ta cần đạt được cho bài tập cuối cùng đó


*

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

Từ tab Data (Dữ liệu), hãy nhấp vào lệnh What-If Analysis (Phân tích Điều gì xảy ra), sau đó chọn Goal Seek từ trình đơn thả xuống.
*

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ụ của chúng tôi, ô G7 đã được chọn.Trường thứ hai, To value:, là kết quả mong muốn. Trong ví dụ này chúng mình sẽ nhập 70 vì chúng cần ít nhất kiếm được số điểm đó để 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ó. Trong ví dụ này chúng mình sẽ nhập G6 vì chúng mình muốn xác định điểm cần đạt được trong bài tập cuối cùng.Khi bạn hoàn tất, hãy nhấp vào OK.
*

*

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

Sử dụng Goal Seek (ví dụ 2):

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 đô la. Chúng mình có thể sử dụng Goal Seek để tìm ra số lượng người cần mời. Trong ví dụ của chúng mình bên dưới, ô 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.

Chọn ô có giá trị bạn muốn thay đổi. Trong ví dụ này chúng ta sẽ chọn ô B5.
Từ tab Data (Dữ liệu), hãy nhấp vào lệnh What-If Analysis (Phân tích Điều gì xảy ra), sau đó chọn Goal Seek từ trình đơn thả xuống.
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ụ của chúng tôi, ô B5 đã được chọn.Trường thứ hai, To value:, là kết quả mong muốn. Trong ví dụ này chúng mình sẽ nhập 500 vì chúng cần ít nhất kiếm được số điểm đó để 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ó. Trong ví dụ này chúng mình sẽ nhập B4 vì chúng mình muốn xác định điểm cần đạt được trong bài tập cuối cùng.Khi bạn hoàn tất, hãy nhấp vào OK.
Kết quả sẽ xuất hiện trong ô được chỉ định. Trong ví dụ của chúng mình, 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ủa chúng mìnhcần phải là một số nguyên, vì vậy chúng tôi cần làm tròn câu trả lời lên hoặc xuống. Bởi vì làm tròn số sẽ khiến ngân sách bị vượt quá vì vậy số lượng khách mời sẽ làm tròn xuống 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 của what-if analysis

Đối với các dự án nâng cao hơn, bạn có thể muốn xem xét các 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 giá trị theo cách thủ công. Trong ví dụ dưới đây, chúng mình sẽ sử dụng các tình huống để so sánh các địa điểm khác nhau cho một sự kiện sắp tới.

Để biết thêm thông tin về các tình huống, hãy đọc bài viết này của Microsoft.

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.


Chức năng Scenario manager trong What if analysis giúp chúng ta tạo ra những kịch bản khác nhau (nói đơn giản là các trường hợp khác nhau) khi tính lợi nhuận kinh doanh.

Trong bài viết này chúng ta cùng tìm hiểu chức năng Scenario manager trong What if analysis để phân tích và tính lợi nhuận kinh doanh trong Excel.

1. What if analysis là gì?

- Hiểu đơn giản What if analysis là dạng phân tích nếu...thì... 

- Ví dụ dưới đây sẽ giúp bạn dễ dàng hiểu và vận dụng What if analysis với chức năng Scenario manager. Giả sử bạn sở hữu một cửa quần áo và có 100 bộ trong kho. Bạn bán 1 tỷ lệ % nhất định với giá cao nhất là 30$ và số % còn lại với giá thấp hơn là 20$.


- Trong ô C7 nhập công thức:=B4*C4; Nếu bạn bán 60% với giá cao nhất, ô D10 sẽ tính tổng lợi nhuận là 60 * 30$ + 40 * 20$ = 2600$ như hình trên.

2. Sử dụng Scenario manager trong what if analysis tạo kịch bản khác nhau

- Trong trường hợp trên bạn bán 60% sản phẩm với giá cao nhất, nhưng giờ bạn muốn bán 70% hay 80% hoặc 90% với giá cao nhất thì sao? hoặc thậm chí 100%? Như vậy, mỗi tỷ lệ khác nhau là một kịch bản (trường hợp) khác nhau. Bạn có thể sử dụng Trình quản lý kịch bản - Scenario manager để tạo các kịch bản này.

Lưu ý: Bạn có thể chỉ cần nhập một tỷ lệ phần trăm khác vào ô C4 để xem kết quả tương ứng của một kịch bản trong ô D10. Tuy nhiên, what if analysis cho phép bạn dễ dàng so sánh kết quả của các tình huống khác nhau, các bước thực hiện như sau:

Bước 1: Chọn thẻ Data → nhóm Data tools → What if analysis → Scenario manager;


Bước 2: Hộp thoại Scenario manager xuất hiện, thêm một kịch bản bằng cách nhấp vào Add, và thực hiện các thao tác từ 1 đến 3 như hình sau:


Lưu ý: để xem kết quả của một kịch bản, chọn kịch bản và nhấp vào nút Hiển thị. Excel sẽ thay đổi giá trị của ô C4 cho bạn để xem kết quả tương ứng trên trang tính.

* Để dễ dàng so sánh kết quả của các kịch bản này, hãy thực hiện các bước sau.

Bước 1. Nhấp vào nút Summary trong Scenario manager (hình trên).

Bước 2. Tiếp theo, chọn ô D10 (tổng lợi nhuận) cho ô kết quả và nhấp vào OK.


⇒ Như vậy, nếu bạn bán 70% với giá cao nhất, bạn có được tổng lợi nhuận là $ 2700, nếu bạn bán 80% cho mức giá cao nhất, bạn có được tổng lợi nhuận là $ 2800, v.v ...

Xem thêm: Vtv6 xem trực tiếp bóng đá, xem trực tiếp bóng đá hôm nay vtv6

Hy vọng với bài hướng dẫn về cách sử dụng chức năng scenario manager của What if analysis trong excel giúp ích cho các bạn, chúc các bạn có thể vận dụng tốt vào bài toán thực tế trong công việc, nếu thấy bài viết hay hãy like và chia sẻ nhé các các bạn.