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

 Bạn đã kiến thiết 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 những biến đầu vào đó sẽ khiến tác dụng biến hóa 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 dữ liệu What-If Analysis trong Excel và quan sát nhanh toàn bộ những hiệu 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ụ có ích để giải bài toán Nếu – Thì. Sử dụng What-If Analysis, bạn có thể sử dụng 1 số ít bộ giá trị khác nhau trong một hoặc nhiều công thức để mày mò toàn bộ những hiệu quả khác nhau .Ví dụ : bạn có thể thực thi 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 có thể chỉ định một hiệu quả 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 cung ứng 1 số ít công cụ khác nhau để giúp bạn thực thi 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). 

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

• 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ể setup những phần bổ trợ giúp bạn triển khai What-If Analysis, ví dụ điển hình như Solver add-in. Solver add-in tương tự như 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à những 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, tất cả chúng ta sẽ 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 nhờ vào vào một số ít giá trị và bạn muốn thử nghiệm với những phối hợp nguồn vào khác nhau và so sánh hiệu quả .

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

Hiện tại, Excel chỉ tương hỗ data table một và hai biến. Mặc dù bị số lượng giới hạn ở tối đa hai ô đầu vào khác nhau, data table vẫn được 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 được cho phép kiểm tra một loạt giá trị cho một ô đầu vào và cho biết những giá trị đó tác động ảnh hưởng như thế nào đến hiệu quả 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, tất cả chúng ta sẽ làm theo một ví dụ đơn cử thay vì diễn đạt những bước chung chung .Giả sử bạn đang xem xét gửi tiền tiết kiệm chi phí của mình vào một ngân hàng nhà nước, ngân hàng nhà nước trả lãi suất vay 5 % hàng tháng. Để kiểm tra những tùy chọn khác nhau, bạn đã thiết kế 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 thi một nghiên cứu và phân tích What-If đơn thuần để xem khoản tiết kiệm chi phí của bạn sẽ là bao nhiêu trong 5 năm tùy thuộc vào số tiền góp vốn đầu tư bắt đầu của bạn, giao động từ 1.000 đô la đến 6.000 đô la .Dưới đây là những 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:

Xem thêm: Quỹ đầu tư phát triển là gì? Nguyên tắc và cách hạch toán

• 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 tích hợp khác nhau của 2 bộ giá trị biến tác động ảnh hưở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ẽ đổi khác 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 khoanh vùng phạm vi giá trị đầu, một trong một hàng và một trong một cột. Cách triển khai 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ìn nhận nhiều công thức cùng một lúc, hãy thiết kế xây dựng bảng dữ liệu của bạn tương tự như những bước ở trên, nhưng thêm vào bảng những 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 giải trí đúng chuẩn, toàn bộ những 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 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 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 đã khám phá cách tạo bảng dữ 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ẽ tìm hiểu và 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 dữ 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) để 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 được cho phép xóa giá trị trong những ô riêng không liên quan gì đến nhau chứa hiệu quả. 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ể biến hóa một phần của bảng dữ liệu ” sẽ hiển thị .Tuy nhiên, bạn có thể thuận tiện xóa hàng loạt mảng những giá trị hiệu quả theo những 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 hiệu quả bảng dữ liệuVì 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 những ô riêng không liên quan gì đến nhau với những giá trị được giám sát. Bạn chỉ có thể sửa chữa thay thế tổng thể những giá trị đó bằng giá trị của riêng mình bằng cách triển khai những 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.

Xem thêm: 5 dấu hiệu thai phát triển tốt và 10 dấu hiệu thai yếu bạn cần biết

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 đo lường và thống kê lại tự động hóa trong bảng đó và tổng thể những bảng dữ liệu khác. Để triển khai, bạn vui mừng làm theo những 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.

Để thống kê giám sát lại bảng dữ liệu của bạn theo cách thủ công bằng tay, hãy chọn những ô hiệu quả của nó, tức là những ô 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 có ích khác., hãy tham gia Gitiho ngay thời điểm ngày hôm nay .

Source: https://thomaygiat.com
Category : Kỹ Thuật Số