Hướng dẫn cho thấy cách sử dụng bảng dữ liệu cho phân tích What-If trong Excel. Tìm hiểu cách tạo bảng một biến và hai biến để xem tác động của một hoặc hai giá trị đầu vào trên công thức của bạn và cách thiết lập bảng dữ liệu để đánh giá nhiều công thức cùng một lúc.
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 số và muốn biết cách thay đổi các đầu vào đó thay đổi kết quả. Thay vì kiểm tra từng biến riêng lẻ, hãy tạo một Bảng dữ liệu phân tích what-if và quan sát tất cả các kết quả có thể với một cái nhìn nhanh chóng!
Bảng dữ liệu trong Excel là gì?
Trong Microsoft Excel, một bảng dữ liệu là một trong những công cụ Phân tích What-If cho phép bạn thử các giá trị đầu vào khác nhau cho các công thức và xem các thay đổi trong các giá trị đó ảnh hưởng đến đầu ra của công thức như thế nào.
Các bảng dữ liệu đặ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ả.
Hiện tại, có tồn tại một bảng dữ liệu biến và bảng dữ liệu hai biến. Mặc dù giới hạn ở tối đa hai ô nhập khác nhau, bảng dữ liệu cho phép bạn kiểm tra bao nhiêu giá trị biến đổi theo ý muốn.
Cách tạo bảng dữ liệu một biến trong Excel
Bảng dữ liệu một biến trong Excel cho phép kiểm tra một loạt các giá trị cho một ô nhập đơn và cho thấy những giá trị đó ảnh hưởng đến kết quả của một công thức liên quan như thế nào.
Để giúp bạn hiểu rõ hơn về tính năng này, chúng tôi sẽ theo dõi một ví dụ cụ thể thay vì mô tả các bước chung.
Giả sử bạn đang xem xét gửi tiền tiết kiệm của mình vào ngân hàng, trả lãi 5% mà hợp chất hàng tháng. Để kiểm tra các tùy chọn khác nhau, bạn đã xây dựng các mục sau máy tính lãi kép Ở đâu:
- B8 chứa công thức FV tính toán số dư cuối kỳ.
- B2 là biến bạn muốn kiểm tra (đầu tư ban đầu).
Và bây giờ, hãy thực hiện phân tích What-If đơn giản để xem số tiề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 đến 6.000 đô la.
Dưới đây là các bước để tạo bảng dữ liệu một biến:
- 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 ra một định hướng cột bảng dữ liệu, vì vậy chúng tôi 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ả.
- 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 của chúng tôi). 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 (nếu bạn quyết định thay đổi công thức trong tương lai, bạn sẽ chỉ cần cập nhật một ô). Chúng tôi chọn tùy chọn thứ hai và nhập công thức đơn giản này vào E2:
=B8
Tiền boa. 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 ô nhập, hãy nhập (các) công thức bổ sung ở bên phải của công thức đầu tiên, như được hiển thị trong ví dụ này. - 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).
- Đi đến Dữ liệu tab> Công cụ dữ liệu nhóm, nhấp vào Những gì nếu phân tích và sau đó nhấp vào Bảng dữ liệu…
- bên trong Bảng dữ liệu cửa sổ hộp thoại, bấm vào Cột nhập ô hộp (vì Đầu tư các giá trị 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 tôi chọn B3 có chứa giá trị đầu tư ban đầu.
- Nhấp chuột đồng ý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.
- Áp dụng mong muốn định dạng số kết quả (Tiền tệ trong trường hợp của chúng tôi), và bạn tốt để đi!
Bây giờ, bạn có thể xem nhanh bảng dữ liệu một biến, kiểm tra số dư có thể và chọn kích thước tiền gửi tối ưu:
Bảng dữ liệu hướng hàng
Ví dụ trên cho thấy cách thiết lập một theo chiều dọc, hoặc là định hướng cột, bảng dữ liệu trong Excel. Nếu bạn thích một ngang bố trí, đây là những gì bạn cần làm:
- 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ả). Trong 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 ô nằm ở bên trái giá trị biến đầu tiên của bạn và một ô bên dưới (E4 trong trường hợp của chúng tôi).
- Tạo một bảng dữ liệu như được thảo luận ở trên, nhưng nhập giá trị đầu vào (B3) vào Ô nhập hàng cái hộp:
- Nhấp chuột đồng ývà bạn sẽ có kết quả như sau:
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 sự 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 cách thay đổi hai giá trị đầu vào của cùng công thức thay đổi đầu ra.
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 vào có thể, một trong một hàng và một trong một cột.
Để xem nó hoạt động như thế nào, hãy sử dụng cùng một máy tính lãi suất và kiểm tra ảnh hưởng của kích thước của đầu tư ban đầu và số năm trên số dư. Để hoàn thành nó, hãy thiết lập bảng dữ liệu của bạn theo cách này:
- 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. Hãy chắc chắn rằng bạn có đủ các cột trống ở bên phải và các 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 toán số dư:
=B8 - 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).
- Nhập tập hợp các giá trị biến khác ở bên phải của 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:
- 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 ô trong đó các giá trị được tính sẽ xuất hiện. Chúng tôi chọn phạm vi E2: H8.
- Tạo bảng dữ liệu theo cách đã quen thuộc: Dữ liệu tab> Những gì nếu phân tích nút> Bảng dữ liệu…
- bên trong Ô nhập hàng hộp, nhập tham chiếu đến ô nhập cho các giá trị biến trong hàng (trong ví dụ này, đó là B6 chứa Năm giá trị).
- bên trong Cột nhập ô hộp, nhập tham chiếu đến ô nhập cho các giá trị biến trong cột (B3 chứa Đầu tư ban đầu giá trị).
- Nhấp chuột đồng ý.
- Tùy chọn, định dạng đầu ra theo cách bạn cần (bằng cách áp dụng Tiền tệ định dạng trong trường hợp của chúng tôi) và phân tích kết quả:
Bảng dữ liệu để so sánh nhiều kết quả
Nếu bạn muốn đánh giá nhiều hơn một 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 như được hiển thị trong các ví dụ trước và nhập (các) công thức bổ sung:
- Ở bên phải của công thức đầu tiên trong trường hợp theo chiều dọc bảng dữ liệu được tổ chức theo cột
- Dưới công thức đầu tiên trong trường hợp ngang bảng dữ liệu được sắp xếp theo hàng
Để bảng dữ liệu “đa công thức” hoạt động chính xác, tất cả các công thức nên tham khảo cùng một ô nhập.
Ví dụ: hãy thêm một công thức nữa vào bảng dữ liệu một biến để 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. Đây là những gì chúng tôi làm:
- Trong ô B10, tính toán quan tâm với công thức này:
=B8-B3 - Sắp xếp dữ liệu nguồn của bảng dữ liệu như chúng ta đã làm trước đó: các giá trị biến trong D3: D8 và E2 được liên kết với B8 (Thăng bằng công thức).
- 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 đến B10 (quan tâm công thức):
- Chọn phạm vi bảng dữ liệu mở rộng (D2: F8).
- Mở Bảng dữ liệu hộp thoại bằng cách nhấn vào Dữ liệu tab> Những gì nếu phân tích > Bảng dữ liệu…
- bên trong Cột nhập ô hộp, cung cấp ô nhập (B3) và bấm đồng ý.
Voilà, bây giờ bạn có thể quan sát ảnh hưởng của các giá trị biến của bạn trên cả hai công thức:
Bảng dữ liệu trong Excel – 3 điều bạn nên biết
Để sử dụng hiệu quả các bảng dữ liệu trong Excel, vui lòng ghi nhớ 3 sự thật đơn giản sau:
- Để bảng dữ liệu được tạo thành công, (các) ô nhập phải nằm trên cùng tờ như bảng dữ liệu.
- Microsoft Excel sử dụng hàm TABLE (row_input_cell, colum_input_cell) để tính kết quả bảng dữ liệu:
- Trong bảng dữ liệu một biến, một trong các đối số được bỏ qua, tùy thuộc vào bố cục (hướng theo cột hoặc hướng hàng). Ví dụ: trong bảng dữ liệu một biến ngang của chúng tôi, công thức là
=TABLE(, B3)Trong đó B3 là ô nhập cột. - Trong bảng dữ liệu hai biến, cả hai đối số được đặt ra. 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. Để chắc chắn về điều này, hãy chọn bất kỳ ô nào có giá trị được tính toán, nhìn vào thanh công thức và lưu ý {dấu ngoặc nhọn} xung quanh công thức. Tuy nhiên, đây không phải là một công thức mảng thông thường – bạn không thể nhập nó vào thanh công thức cũng như không thể chỉnh sửa một công thức hiện có. Nó chỉ là “cho chương trình”.
- Trong bảng dữ liệu một biến, một trong các đối số được bỏ qua, tùy thuộc vào bố cục (hướng theo cột hoặc hướng hàng). Ví dụ: trong bảng dữ liệu một biến ngang của chúng tôi, công thức là
- Vì các kết quả của bảng dữ liệu được tính toán với một 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 các giá trị trong các ô riêng lẻ chứa kết quả. Bất cứ khi nào bạn cố gắng làm điều này, một thông báo lỗi “Không thể thay đổi một phần của bảng dữ liệu“Sẽ xuất hiện.
Tuy nhiên, bạn có thể dễ dàng xóa toàn bộ mảng của các giá trị kết quả. Đây là cách thực hiện:
- Tùy thuộc vào nhu cầu của bạn, chọn tất cả các ô của bảng dữ liệu hoặc chỉ các ô có kết quả.
- Bấm phím Xóa.
Làm xong! 🙂
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ột 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ị với giá trị của riêng bạn bằng cách thực hiện các bước sau:
- Chọn tất cả các ô kết quả.
- Xóa công thức BẢNG trong thanh công thức.
- Nhập giá trị mong muốn và nhấn Ctrl + Enter.
Điều này sẽ chèn cùng một giá trị trong tất cả các ô đã chọn:
Khi công thức TABLE không còn nữa, 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.
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 đổi làm chậm Excel của bạn, bạn có thể vô hiệu hóa tính toán lại tự động trong đó và tất cả các bảng dữ liệu khác.
Đối với điều này, đi đến Công thức tab> Phép tính nhóm, nhấp vào Tùy chọn tính toán và sau đó nhấp vào Tự động ngoại trừ bảng dữ liệu.
Điều 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.
Đến tự tính toán lại bảng dữ liệu của bạn, 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.
Đây là cách bạn tạo và sử dụng bảng dữ liệu trong Excel. Để xem kỹ hơn các ví dụ được thảo luận trong hướng dẫn này, bạn có thể tải xuống mẫu của chúng tôi Bảng dữ liệu Excel sách bài tập. Tôi cảm ơn bạn đã đọc và sẽ rất vui khi gặp lại bạn vào tuần tới!
Bạn cũng có thể quan tâm:
The post Cách tạo bảng một biến và hai biến appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/cach-tao-bang-mot-bien-va-hai-bien.html
Không có nhận xét nào:
Đăng nhận xét