Cách tạo và sử dụng Data Table để phân tích What-If trong Excel

Bạn đã thiết kế xây dựng một công thức phức tạp phụ thuộc vào vào nhiều biến và muốn biết việc biến hóa các biến đầu vào đó sẽ khiến hiệu quả đổi khác như thế nào. Thay vì kiểm tra từng biến riêng không liên quan gì đến nhau, hãy lập bảng tài liệu What-If Analysis trong Excel và quan sát nhanh toàn bộ các hiệu quả hoàn toàn 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 dụng để giải bài toán Nếu – Thì. Sử dụng What-If Analysis, bạn hoàn toàn có thể sử dụng một số ít bộ giá trị khác nhau trong một hoặc nhiều công thức để tò mò tổng thể các hiệu quả khác nhau .Ví dụ : bạn hoàn toàn có thể triển khai What-If Analysis để kiến thiết xây dựng hai ngân sách mà mỗi ngân sách đều giả định một mức lệch giá nhất định. Hoặc, bạn hoàn toàn có thể chỉ định một tác dụng mà bạn muốn một công thức tạo ra, sau đó xác lập bộ giá trị nào sẽ tạo ra hiệu quả đó. Excel phân phối một số ít công cụ khác nhau để giúp bạn triển khai kiểu nghiên cứu và phân tích tương thích với nhu yế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 khóa học tin học văn phòng online Thủ thuật Excel cập nhật hàng tuần cho dân văn phòng để bạn có thể giải quyết công việc theo một cách thông minh và nhanh chóng để giúp bạn toả sáng nơi công sở.

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

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 tài liệu biến trong Excel được 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 tác động như thế nào đến tác dụng của một công thức có tương 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, link ô này với công thức trong tập dữ liệu khởi đầu của bạn và nhập công thức đơn thuầ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 .
3. 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).
4. 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 …
   • 
5. Trong hành lang cửa số hộp thoại Data Table …, hãy nhấp vào ô Column Input cell ( vì Giá trị Investment của tất cả 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, tất cả chúng ta chọn B3 chứa giá trị góp vốn đầu tư bắt đầu .
   • 
6. 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.
7. Á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 hoàn toàn có thể xem nhanh bảng tài liệu một biến của mình, kiểm tra số dư hoàn toàn 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 tài liệu hai biến cho thấy cách phối hợp khác nhau của 2 bộ giá trị biến ảnh hưởng tác động đến tác dụng công thức. Nói cách khác, nó cho thấy việc đổi khác hai giá trị nguồn vào của cùng một công thức sẽ biến hóa tác dụng đầ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.

   Xem thêm: Công thức tính diện tích hình chữ nhật Lớp 3, lớp 4 ( Có bài tập minh họa )

   • 
4. 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.
5. Tạo bảng dữ liệu như thông thường: Data tab > What-If Analysis > Data Table…
6. 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 ).
7. 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).
8. Bấm OK .
   • 
9. Đị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ìn nhận nhiều công thức cùng một lúc, hãy kiến thiết xây dựng bảng tài liệu của bạn tương tự như 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 tài liệu ” nhiều công thức ” hoạt động giải trí đúng chuẩn, tổng thể 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 tài liệu một biến số của tất cả chúng ta để tính lãi và xem nó bị ảnh hưởng tác động như thế nào bởi quy mô của khoản góp vốn đầu tư khởi đầ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 khoanh vùng phạm vi bảng tài liệu ( cột F ) và link F2 với B10 .
   • 
4. Chọn phạm vi bảng dữ liệu mở rộng (D2: F8).
5. 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

6. Trong hộpô

   Column Input cell, cung ứng ô đầu vào ( B3 ) và bấmOK . 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 và khám phá cách tạo bảng tài liệu để nghiên cứu và phân tích What-if Excel. Trong phần tiếp theo, tất cả chúng ta sẽ khám phá kỹ hơn cách sử dụng tính năng này. Để sử dụng hiệu suất cao bảng tài liệu trong Excel, hãy ghi nhớ 3 điều đơn thuầ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 ) để giám sát hiệu quả bảng tài 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 được cho phép xóa giá trị trong các ô riêng không liên quan gì đến nhau chứa tác dụng. Bất cứ khi nào bạn cố gắng nỗ lực triển khai việc này, thông tin lỗi ” Không thể đổi khác một phần của bảng tài 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 hề đổi khác một phần của mảng trong Excel, bạn không hề chỉnh sửa các ô riêng không liên quan gì đến nhau với các giá trị được giám sát. Bạn chỉ hoàn toàn có thể sửa chữa thay thế toàn bộ các giá trị đó bằng giá trị của riêng mình bằng cách triển khai 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 tài liệu cũ sẽ trở thành một khoanh vùng phạm vi thường thì và bạn hoàn toàn có thể tự do chỉnh sửa bất kể ô riêng không liên quan gì đến nhau nào một cách thông 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 tài 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 hoàn toàn có thể tắt đo lường và thống kê lại tự động hóa trong bảng đó và tổng thể các bảng tài liệu khác. Để thực thi, bạn vui mắt 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.

Để đo lường và thống kê lại bảng tài liệu của bạn theo cách thủ công bằng tay, hãy chọn các ô tác dụng 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 nghiên cứu và phân tích What-If của Excel. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ pháp tin học văn phòng hữu dụng khác., hãy tham gia Gitiho ngay thời điểm ngày hôm nay .
Cùng tham gia hội đồng hỏi đáp về chủ đề Tin học văn phòng

Source: https://final-blade.com
Category: Kiến thức Internet