Cách tạo và sử dụng Data Table trong What-If analysis Excel

Bạn đã xây dựng một công thức phức tạp phụ thuộc vào nhiều biến và muốn biết việc thay đổi các biến đầu vào đó sẽ khiến kết quả thay đổi như thế nào. Thay vì kiểm tra từng biến riêng lẻ, hãy lập bảng dữ liệu What-If Analysis trong Excel và quan sát nhanh tất cả các kết quả có thể xảy ra.

What-If Analysis trong Excel là gì?

What-If Analysis là một tính năng có sẵn trong Excel, đây là công cụ hữu ích để giải bài toán Nếu - Thì. Sử dụng What-If Analysis, bạn có thể sử dụng một số bộ giá trị khác nhau trong một hoặc nhiều công thức để khám phá tất cả các kết quả khác nhau.

Ví dụ: bạn có thể thực hiện What-If Analysis để xây dựng hai ngân sách mà mỗi ngân sách đều giả định một mức doanh thu nhất định. Hoặc, bạn có thể chỉ định một kết quả mà bạn muốn một công thức tạo ra, sau đó xác định bộ giá trị nào sẽ tạo ra kết quả đó. Excel cung cấp một số công cụ khác nhau để giúp bạn thực hiện kiểu phân tích phù hợp với nhu cầu của mình.

Ba loại công cụ What-If Analysis đi kèm với Excel: Scenarios (kịch bảng), Goal Seek (mục tiêu tìm kiếm), và Data Tables (bảng dữ liệu). 

• Scenarios và Data Tables 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 Tables chỉ hoạt động với một hoặc hai biến, nhưng nó có thể chấp nhận nhiều giá trị khác nhau cho các biến đó.
• Scenarios có thể có nhiều biến, nhưng nó chỉ có thể chứa tối đa 32 giá trị. 
• Goal Seek  hoạt động khác với Scenarios và Data Tables ở chỗ nó lấy một kết quả và xác định các giá trị đầu vào có thể tạo ra kết quả đó.

Ngoài ba công cụ này, bạn có thể cài đặt các phần bổ trợ giúp bạn thực hiện What-If Analysis, chẳng hạn như Solver add-in. Solver add-in tương tự như Goal Seek, nhưng nó có thể chứa nhiều biến hơn. Bạn cũng có thể tạo dự báo bằng cách sử dụng fill handle và các lệnh khác nhau được tích hợp sẵn trong Excel.

Xem thêm: Tuyệt đỉnh Excel khóa học Excel giúp bạn thành thạo What-If Analysis

Trong hướng dẫn này, chúng ta sẽ tìm hiểu cách tạo và sử dụng data table cho What-If Analysis. Data table đặc biệt hữu ích khi một công thức phụ thuộc vào một số giá trị và bạn muốn thử nghiệm với các kết hợp đầu vào khác nhau và so sánh kết quả. 

Cách tạo data table cho What-If Analysis trong Excel

Hiện tại, Excel chỉ hỗ trợ data table một và hai biến. Mặc dù bị giới hạn ở tối đa hai ô đầu vào khác nhau, data table vẫn cho phép bạn kiểm tra nhiều giá trị biến tùy thích.

Cách tạo bảng dữ liệu một biến trong Excel

Một bảng dữ liệu biến trong Excel cho phép kiểm tra một loạt giá trị cho một ô đầu vào và cho biết các giá trị đó ảnh hưởng như thế nào đến kết quả của một công thức có liên quan.

Để giúp bạn hiểu rõ hơn về tính năng này, chúng ta sẽ làm theo một ví dụ cụ thể thay vì mô tả các bước chung chung.

Giả sử bạn đang cân nhắc gửi tiền tiết kiệm của mình vào một ngân hàng, ngân hàng trả lãi suất 5% hàng tháng. Để kiểm tra các tùy chọn khác nhau, bạn đã xây dựng công thức tính lãi kép sau đây :

• B8 chứa công thức FV tính số dư cuối kỳ.
• B2 là biến bạn muốn thử nghiệm (đầu tư ban đầu).

Bây giờ, hãy thực hiện một phân tích What-If đơn giản để xem khoản tiết kiệm của bạn sẽ là bao nhiêu trong 5 năm tùy thuộc vào số tiền đầu tư ban đầu của bạn, dao động từ 1.000 đô la đến 6.000 đô la.

Dưới đây là các bước để tạo bảng dữ liệu một biến:

1. Nhập các giá trị biến trong một cột hoặc trên một hàng. Trong ví dụ này, chúng tôi sẽ tạo một bảng dữ liệu hướng cột , vì vậy chúng ta nhập các giá trị biến của chúng tôi vào một cột (D3: D8) và để lại ít nhất một cột trống ở bên phải cho các kết quả.
2. Nhập công thức của bạn vào ô một hàng ở trên và một ô ở bên phải của các giá trị biến (trong trường hợp này là E2). Hoặc, liên kết ô này với công thức trong tập dữ liệu ban đầu của bạn  và nhập công thức đơn giản =B8 vào ô E2

• Nếu bạn muốn kiểm tra tác động của các giá trị biến đối với các công thức khác tham chiếu đến cùng một ô đầu vào, hãy nhập (các) công thức bổ sung vào bên phải của công thức đầu tiên .

1. Chọn phạm vi bảng dữ liệu, bao gồm công thức của bạn, các ô giá trị biến và các ô trống cho kết quả (D2: E8).
2. Chuyển tới Data tab > Data Tools, nhấp vào nút What-If Analysis, và sau đó nhấp vào Data Table…

1. Trong cửa sổ hộp thoại Data Table…, hãy nhấp vào ô  Column Input cell (vì Giá trị Investment của chúng ta nằm trong một cột) và chọn ô biến được tham chiếu trong công thức của bạn. Trong ví dụ này, chúng ta chọn B3 chứa giá trị đầu tư ban đầu.

1. Nhấp vào OK , và Excel sẽ ngay lập tức điền vào các ô trống với kết quả tương ứng với giá trị biến trong cùng một hàng.
2. Áp dụng định dạng số mong muốn cho kết quả ( trong ví dụ này là Currency).

Bây giờ, bạn có thể xem nhanh bảng dữ liệu một biến của mình, kiểm tra số dư có thể có và chọn số tiền gửi tối ưu:

Ví dụ trên cho thấy cách thiết lập bảng dữ liệu theo chiều dọc hoặc theo hướng cột trong Excel. Nếu bạn thích bố cục ngang, hãy làm theo các bước bên dưới:

• Nhập các giá trị biến trong một hàng, để lại ít nhất một cột trống ở bên trái (cho công thức) và một hàng trống bên dưới (cho kết quả). Đối với ví dụ này, chúng tôi nhập các giá trị biến trong các ô F3: J3.
• Nhập công thức vào ô cách một cột bên trái giá trị biến đầu tiên của bạn và một ô bên dưới (E4)
• Tạo bảng dữ liệu như hướng dẫn ở trên, nhưng nhập giá trị đầu vào (B3) trong hộp Row input cell
• Nhấn OK , và bạn sẽ có một data table theo phương ngang.

Cách tạo bảng dữ liệu hai biến trong Excel

Một  bảng dữ liệu  hai biến cho thấy cách kết hợp khác nhau của 2 bộ giá trị biến ảnh hưởng đến kết quả công thức. Nói cách khác, nó cho thấy việc thay đổi hai giá trị đầu vào của cùng một công thức sẽ thay đổi kết quả đầu ra như thế nào.

Các bước để tạo bảng dữ liệu hai biến trong Excel về cơ bản giống như trong ví dụ trên, ngoại trừ việc bạn nhập hai phạm vi giá trị đầu , một trong một hàng và một trong một cột. Cách thực hiện như sau:

1. Nhập công thức của bạn vào một ô trống hoặc liên kết ô đó với công thức ban đầu của bạn. Đảm bảo bạn có đủ cột trống ở bên phải và hàng trống bên dưới để chứa các giá trị biến của bạn. Như trước đây, chúng tôi liên kết ô E2 với công thức FV ban đầu để tính số dư:=B8
2. Nhập một bộ giá trị đầu vào bên dưới công thức, trong cùng một cột (giá trị đầu tư trong E3: E8).
3. Nhập bộ giá trị biến khác vào bên phải công thức, trong cùng một hàng (số năm trong F2: H2). Tại thời điểm này, bảng dữ liệu hai biến của bạn sẽ trông giống như sau.

1. Chọn toàn bộ phạm vi bảng dữ liệu bao gồm công thức, hàng và cột của các giá trị biến và các ô chứa các giá trị được tính toán sẽ xuất hiện. Chúng ta chọn phạm vi E2: H8.
2. Tạo bảng dữ liệu như thông thường: Data tab > What-If Analysis > Data Table…
3. Trong hộp Row input cell, nhập tham chiếu đến ô đầu vào cho các giá trị biến trong hàng (trong ví dụ này, đó là B6 chứa giá trị Năm ).
4. Trong ô Column input cell, hãy nhập tham chiếu đến ô đầu vào cho các giá trị biến trong cột (B3 chứa giá trị Initial Investment).
5. Bấm OK .

1. Định dạng kết quả đầu ra nếu cần và tiến hành phần tích.

Cách tạo bảng dữ liệu để so sánh nhiều kết quả

Nếu bạn muốn đánh giá nhiều công thức cùng một lúc, hãy xây dựng bảng dữ liệu của bạn tương tự các bước ở trên, nhưng thêm vào bảng các công thức ở vị trí sau đây.

• Ở bên phải của công thức đầu tiên trong trường hợp bảng dữ liệu dọc được tổ chức thành các cột
• Bên dưới công thức đầu tiên trong trường hợp bảng dữ liệu ngang được tổ chức theo hàng

Để bảng dữ liệu "nhiều công thức" hoạt động chính xác, tất cả các công thức phải tham chiếu đến cùng một ô đầu vào . Ví dụ, thêm một công thức nữa vào bảng dữ liệu một biến số của chúng ta để tính lãi và xem nó bị ảnh hưởng như thế nào bởi quy mô của khoản đầu tư ban đầu. 

1. Trong ô B10, tính lãi theo công thức sau:=B8-B3
2. Sắp xếp dữ liệu nguồn của bảng dữ liệu với  các giá trị biến trong D3: D8 và E2 được liên kết với B8.
3. Thêm một cột nữa vào phạm vi bảng dữ liệu (cột F) và liên kết F2 với B10.

1. Chọn phạm vi bảng dữ liệu mở rộng (D2: F8).
2. Mở hộp thoại Bảng dữ liệu bằng cách nhấp vào tab  Data tab > What-If Analysis > Data Table
3. Trong hộp ô Column Input cell, cung cấp ô đầu vào (B3) và bấm OK . Bây giờ bạn có thể quan sát tác động của các giá trị biến của bạn trên cả hai công thức

Cách xóa và chỉnh sửa data table trong phân tích What-if Excel

Ở phần trước, bạn đã tìm hiểu cách tạo bảng dữ liệu để phân tích What-if Excel. Trong phần tiếp theo, chúng ta sẽ tìm hiểu kỹ hơn cách sử dụng tính năng này. Để sử dụng hiệu quả bảng dữ liệu trong Excel, hãy ghi nhớ 3 điều đơn giản sau:

1. Để một bảng dữ liệu được tạo thành công, (các) ô đầu vào phải nằm trên cùng một trang tính với bảng dữ liệu.
2. Microsoft Excel sử dụng hàm TABLE (row_input_cell, colum_input_cell) để tính toán kết quả bảng dữ liệu:
   • Trong bảng dữ liệu một biến, một trong hai đối số bị bỏ qua, tùy thuộc vào bố cục (hướng cột hoặc hướng hàng) mà công thức sẽ thay đổi, công thức trong ví dụ của chúng ta  =TABLE(, B3) với B3 là ô đầu vào cột.
   • Trong bảng dữ liệu hai biến, cả hai đối số đều có sẵn. Ví dụ, =TABLE(B6, B3) trong đó B6 là ô nhập hàng và B3 là ô nhập cột.
   • Hàm TABLE được nhập dưới dạng công thức mảng.
3. Bởi vì kết quả bảng dữ liệu được tính bằng công thức mảng, các ô kết quả không thể được chỉnh sửa riêng lẻ. Bạn chỉ có thể chỉnh sửa hoặc xóa toàn bộ mảng ô như được giải thích bên dưới.

Cách xóa bảng dữ liệu trong Excel

Như đã đề cập ở trên, Excel không cho phép xóa giá trị trong các ô riêng lẻ chứa kết quả. Bất cứ khi nào bạn cố gắng thực hiện việc này, thông báo lỗi "Không thể thay đổi một phần của bảng dữ liệu" sẽ hiển thị.

Tuy nhiên, bạn có thể dễ dàng xóa toàn bộ mảng các giá trị kết quả theo các bước bên dưới.

1. Tùy thuộc vào nhu cầu của bạn, hãy chọn tất cả các ô của bảng dữ liệu hoặc chỉ các ô có kết quả.
2. Nhấn phím Delete.

Cách chỉnh sửa kết quả bảng dữ liệu

Vì không thể thay đổi một phần của mảng trong Excel, bạn không thể chỉnh sửa các ô riêng lẻ với các giá trị được tính toán. Bạn chỉ có thể thay thế tất cả các giá trị đó bằng giá trị của riêng mình bằng cách thực hiện các bước sau:

1. Chọn tất cả các ô kết quả.
2. Xóa công thức TABLE trong thanh công thức.
3. Nhập giá trị mong muốn và nhấn Ctrl + Enter. Thao tác này sẽ chèn cùng một giá trị vào tất cả các ô đã chọn:

Sau khi công thức TABLE biến mất, bảng dữ liệu cũ sẽ trở thành một phạm vi thông thường và bạn có thể tự do chỉnh sửa bất kỳ ô riêng lẻ nào một cách bình thường.

Cách tính toán lại bảng dữ liệu theo cách thủ công

Nếu một bảng dữ liệu lớn có nhiều giá trị và công thức biến làm chậm Excel của bạn, bạn có thể tắt tính toán lại tự động trong bảng đó và tất cả các bảng dữ liệu khác. Để thực hiện, bạn vui lòng làm theo các bước sau:

1. Chuyển đến tab Formulas tab > Calculation
2. Bấm vào nút  Calculation Options
3. Sau đó click vào ô Automatic Except Data Tables. Thao tác này sẽ tắt tính toán bảng dữ liệu tự động và tăng tốc độ tính toán lại của toàn bộ sổ làm việc.

Để tính toán lại bảng dữ liệu của bạn theo cách thủ công, hãy chọn các ô kết quả của nó, tức là các ô có công thức TABLE () và nhấn F9.

Hy vọng qua bài viết này, bạn đã biết cách tạo Data table sử dụng trong phân tích What-If của Excel. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác., hãy tham gia Gitiho ngay hôm nay.