Hướng dẫn sử dụng What If Analysis trong Excel phân tích dữ liệu

What If Analysis là gì?

What If Analysis là một tính năng hữu ích có sẵn trong Excel, được sử dụng để giải bài toán nếu thì. What If Analysis cho phép bạn sử dụng một số bộ giá trị khác nhau trong một hoặc nhiều công thức để có thể khám phá tất cả các kết quả. Ví dụ, để xây dựng hai ngân sách, trong đó mỗi ngân sách đều giả định một mức doanh thu cụ thể, bạn có thể thực hiện What If Analysis. Data Analyst là làm gì? Là người phải nắm rõ tính năng What If trong Excel để cho ra tất cả kết quả tốt nhất khi phân tích dữ liệu.

What If Analysis trong Excel gồm có ba công cụ chính đi kèm là Scenario (kịch bản), Goal Seek (mục tiêu tìm kiếm), và Data Tables (bảng dữ liệu). Trong đó:

• Scenario và Data Table hoạt động bằng cách lấy các tập hợp giá trị đầu vào và xác định kết quả có thể có.
  • Một Data Table hoạt động với một hoặc hai biến, tuy nhiên nó vẫn có thể chấp nhận nhiều giá trị khác nhau cho các biến đó.
  • Scenario hoạt động với nhiều biến, nhưng chỉ chứa tối đa 32 giá trị.
• Khác với Scenario và Data Table, Goal Seek hoạt động bằng cách lấy một kết quả, đồng thời xác định các giá trị đầu vào có thể tạo ra kết quả đó.

Ngoài 3 công cụ nêu trên, bạn có thể cài đặt các phần bổ trợ khác để giúp bạn thực hiện What If Analysis trong Excel hiệu quả hơn, chẳng hạn như Solver Add-in, đây là tính năng bổ sung tương tự như Goal Seek nhưng có thể chứa nhiều biến hơn hoặc Fill Handle, tính năng tạo dự báo.

>>>Xem thêm: Đăng ký ngay khóa học BA uy tín, chất lượng hiện nay.

Cách sử dụng What If Analysis trong Excel

Để hiểu rõ, cũng như nắm vững cách sử dụng từng công cụ của What If Analysis trong Excel, hãy cùng Topchuyengia cách thực hiện thông qua ví dụ sau:

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

Tạo Scenario

Trong trường hợp bạn bán 60% sản phẩm với giá cao nhất, nhưng hiện tại bạn lại muốn bán 70%, 80%, 90% hoặc thậm chí là 100% sản phẩm với giá cao nhất. Vậy phải làm sao? Có thể thấy rằng, với mối tỷ lệ khác nhau sẽ cần một kịch bản khác nhau, lúc này, bạn nên sử dụng trình quản lý kịch bản của What If Analysis - Scenario.

Lưu ý: Để xem kết quả của một kịch bản trong ô D10, bạn có thể chỉ cần nhập một tỷ lệ phần trăm khác vào ô C4. Tuy nhiên, với What If Analysis, bạn sẽ dễ dàng so sánh kết quả của các tình huống khác nhau.

Dưới đây là trình tự các bước thực hiện bạn có thể tham khảo:

• Bước 1: Tại nhóm Data tools, chọn thẻ Data → chọn What If Analysis → Chọn Scenario Manager.

• Bước 2: Khi hộp thoại Scenario Manager hiện ra, để thêm một kịch bản, bạn nhấp Add và thực hiện lần lượt các thao tác sau:
  • Điền “60% giá trị cao nhất” vào ô Scenario name.
  • Điền “$C$4” vào ô Changing cells.
  • Nhấp chọn OK.

• Bước 3: Tiếp tục, nhập 0.6 là giá trị tương ứng và nhấp chọn OK.
• Bước 4: Bạn tiến hành thêm 4 kịch bản khác tương tự như các bước hướng dẫn trên để thấy được Scenario Manager nhất quán như hình bên dưới:

Lưu ý: Để xem kết quả của một kịch bản, bạn hãy chọn kịch bản muốn xem và nhấp chọn nút Show. Sau đó, Excel sẽ tự thay đổi giá trị tương ứng của ô C4 trên sheet.

Bên cạnh đó, nếu muốn so sánh kết quả của các kịch bản dễ dàng, bạn thực hiện các bước sau:

• Bước 1: Trong hộp thoại Scenario Manager, bạn nhấp chọn nút Summary.
• Bước 2: Lấy ô D10 làm ô kết quả điền vào mục Result cells và nhấp OK để hiển thị kết quả.

Như vậy, nếu bán 70% với giá cao nhất thì tổng lợi nhuận mà bạn thu được là 4100$, còn nếu bán 80% với giá cao nhất thì tổng lợi nhuận thu được là 4400$.

Tạo Data Table

Như đã đề cập ở trên, Data Table và Scenario có cách hoạt động giống nhau, do đó thay vì phải tạo các kịch bản khác nhau, bạn có thể tạo Data Table trong Excel để thử các giá trị khác nhau cho các công thức nhanh chóng. Hiện tại, Excel hỗ trợ Data một biến và hai biến.

Data Table một biến

Để tạo Data Table một biến, bạn có thể thực hiện theo các bước như dưới đây:

Bước 1: Đầu tiên, bạn chọn ô B12 và nhập = D10 để hiển thị tổng lợi nhuận, sau đó nhập lần lượt các tỷ lệ phần trăm 60%, 70%, 90%, 100% vào cột A. Tiếp đó, quét chọn phạm vi A12:B17.

Bước 2: Tại tab Data, bạn nhấp chọn What If Analysis trong nhóm Forecast và chọn công cụ Data Table.

Bước 3: Khi hộp thoại Data Table xuất hiện, bạn chọn ô C4 trong ô Column input cell, vì tỷ lệ phần trăm tham chiếu đến ô C4 cùng với công thức trong ô B12. Lúc này, excel sẽ tự biết ó nên thay thế ô C4 bằng 60% hay 70% để tính tổng lợi nhuận.

Lưu ý: Chúng ta đang tạo Data Table một biến nên ô Row sẽ để trống.

Bước 4: Nhấp OK để xem kết quả.

Như vậy, nếu bán 60% với giá trị cao nhất thì tổng lợi nhuận bạn sẽ thu được là 3800$, nhưng nếu bán 70% với giá cao nhất thì tổng lợi nhuận thu được là 4100$.

Data Table hai biến

Sau đây là các bước tạo Data Table hai biến:

Bước 1: Đầu tiên, bạn chọn ô A12 và nhập = D10 để hiển thị tổng lợi nhuận. Sau đó, lần lượt nhập các đơn vị lợi nhuận khác nhau vào dòng 12 và nhập các tỷ lệ phần trăm vào cột A. Tiếp đó, quét chọn phạm vi A12:D17 để tính tổng lợi nhuận cho các kết hợp khác nhau của unit profit và % sold for the highest price.

Bước 2: Mở hộp thoại Data Table như hướng dẫn của cách tạo Data Table một biến.

Bước 3: Trong hộp thoại Data Table, chọn ô D7 trong mục Row input cell và ô C4 trong mục Column input cell.

Bước 4: Nhấp OK để xem kết quả.

Như vậy, với trường hợp tạo Data Table hai biến, nếu bán 60% với giá cao nhất và lợi nhuận đơn vị là 50$ thì tổng lợi nhuận thu được là 3800$, nhưng nến bán 80% với giá cao nhất và lợi nhuận đơn vị là 60$ thì tổng lợi nhuận là 5200$.

Tạo Goal Seek

Với trường hợp bạn muốn biết kết quả từ một công thức, hãy sử dụng công cụ Goal Seek trong Excel để có thể tìm được giá trị đầu vào và tạo ra kết quả với công thức này. Để bạn hình dung về cách thực hiện rõ hơn, chúng ta xét ví dụ tìm điểm trong bài kiểm tra thứ tư sao cho final grade là 70.

Bước 1: Tại tab Data, trong nhóm Forecast, nhấp chọn What-If Analysis và chọn Goal Seek để mở hộp thoại.

Bước 2: Tiếp đó, trong hộp thoại Goal Seek, chọn ô B7 ở mục Set cell, nhập 70 vào ô To value và chọn ô B5 trong mục By changing cell.

Bước 3: Nhấp OK để xem kết quả.

Kết quả cuối cùng cho thấy rằng để tạo ra final grade là 70 thì điểm trong kỳ thi thứ tư phải là 90.