Phân tích điều gì xảy ra với bảng dữ liệu
Với Bảng dữ liệu trong Excel, bạn có thể dễ dàng thay đổi một hoặc hai đầu vào và thực hiện phân tích What-if. Bảng Dữ liệu là một dải ô trong đó bạn có thể thay đổi giá trị trong một số ô và đưa ra các câu trả lời khác nhau cho một vấn đề.
Có hai loại Bảng dữ liệu -
- Bảng dữ liệu một biến
- Bảng dữ liệu hai biến
Nếu bạn có nhiều hơn hai biến trong vấn đề phân tích, bạn cần sử dụng Công cụ quản lý kịch bản của Excel. Để biết chi tiết, hãy tham khảo chương - Phân tích điều gì xảy ra với Trình quản lý tình huống trong hướng dẫn này.
Bảng dữ liệu một biến
Bảng Dữ liệu một biến có thể được sử dụng nếu bạn muốn xem các giá trị khác nhau của một biến trong một hoặc nhiều công thức sẽ thay đổi kết quả của các công thức đó như thế nào. Nói cách khác, với Bảng dữ liệu một biến, bạn có thể xác định cách thay đổi một đầu vào sẽ thay đổi bất kỳ số lượng đầu ra nào. Bạn sẽ hiểu điều này với sự trợ giúp của một ví dụ.
Example
Có khoản vay 5.000.000 với thời hạn 30 năm. Bạn muốn biết các khoản thanh toán hàng tháng (EMI) cho các mức lãi suất khác nhau. Bạn cũng có thể muốn biết số tiền lãi và tiền gốc được trả trong năm thứ hai.
Phân tích với Bảng dữ liệu một biến
Phân tích với Bảng dữ liệu một biến cần được thực hiện theo ba bước:
Step 1 - Đặt nền theo yêu cầu.
Step 2 - Tạo Bảng dữ liệu.
Step 3 - Thực hiện phân tích.
Hãy để chúng tôi hiểu chi tiết các bước này -
Bước 1: Đặt nền cần thiết
Giả sử rằng lãi suất là 12%.
Liệt kê tất cả các giá trị cần thiết.
Đặt tên cho các ô có chứa các giá trị để các công thức sẽ có tên thay vì tham chiếu ô.
Đặt các tính toán cho EMI, Lãi tích lũy và Tiền gốc tích lũy bằng các hàm Excel - PMT, CUMIPMT và CUMPRINC tương ứng.
Trang tính của bạn sẽ trông như sau:
Bạn có thể thấy rằng các ô trong cột C được đặt tên như đã cho trong các ô tương ứng trong cột D.
Bước 2: Tạo bảng dữ liệu
Nhập danh sách các giá trị tức là lãi suất mà bạn muốn thay thế vào ô đầu vào xuống cột E như sau:
Nhập hàm đầu tiên (PMT) trong ô một hàng ở trên và một ô ở bên phải của cột giá trị. Nhập các chức năng khác (CUMIPMT and CUMPRINC) trong các ô bên phải của hàm đầu tiên.
Bây giờ, hai hàng phía trên giá trị Lãi suất trông như sau:
Khi bạn quan sát, có một hàng trống phía trên giá trị Lãi suất. Hàng này dành cho các công thức bạn muốn sử dụng.
Bảng Dữ liệu trông như dưới đây:
Bước 3: Thực hiện phân tích với Công cụ Bảng Dữ liệu Phân tích Điều gì sẽ xảy ra
Chọn phạm vi ô chứa công thức và giá trị mà bạn muốn thay thế, tức là chọn phạm vi - E2: H13.
Nhấp vào tab DATA trên Ribbon.
Nhấp vào Phân tích điều gì xảy ra trong nhóm Công cụ dữ liệu.
Chọn Bảng dữ liệu trong danh sách thả xuống.
Data Table hộp thoại xuất hiện.
- Bấm vào biểu tượng trong ô nhập cột.
- Nhấp vào ô Interest_Rate, là C2.
Bạn có thể thấy rằng ô nhập Cột được lấy là $ C $ 2. Bấm OK.
Bảng Dữ liệu được điền với các kết quả được tính toán cho từng giá trị đầu vào như hình dưới đây -
Nếu bạn có thể trả EMI là 54.000, bạn có thể thấy rằng lãi suất 12,6% là phù hợp với bạn.
Bảng dữ liệu hai biến
Bảng Dữ liệu hai biến có thể được sử dụng nếu bạn muốn xem các giá trị khác nhau của hai biến trong một công thức sẽ thay đổi kết quả của công thức đó như thế nào. Nói cách khác, với Bảng dữ liệu hai biến, bạn có thể xác định cách thay đổi hai đầu vào thay đổi một đầu ra. Bạn sẽ hiểu điều này với sự trợ giúp của một ví dụ.
Example
Có khoản vay 50.000.000. Bạn muốn biết các kết hợp khác nhau giữa lãi suất và thời hạn vay sẽ ảnh hưởng đến khoản thanh toán hàng tháng (EMI) như thế nào.
Phân tích với Bảng dữ liệu hai biến
Phân tích với Bảng dữ liệu hai biến cần được thực hiện theo ba bước:
Step 1 - Đặt nền theo yêu cầu.
Step 2 - Tạo Bảng dữ liệu.
Step 3 - Thực hiện phân tích.
Bước 1: Đặt nền cần thiết
Giả sử rằng lãi suất là 12%.
Liệt kê tất cả các giá trị cần thiết.
Đặt tên cho các ô có chứa các giá trị để công thức sẽ có tên thay vì tham chiếu ô.
Đặt tính toán cho EMI bằng hàm Excel - PMT.
Trang tính của bạn sẽ trông như sau:
Bạn có thể thấy rằng các ô trong cột C được đặt tên như đã cho trong các ô tương ứng trong cột D.
Bước 2: Tạo bảng dữ liệu
Kiểu =EMI trong ô F2.
Gõ danh sách giá trị đầu vào đầu tiên, tức là lãi suất xuống cột F, bắt đầu bằng ô bên dưới công thức, tức là F3.
Nhập danh sách giá trị đầu vào thứ hai, tức là số lần thanh toán trên hàng 2, bắt đầu bằng ô ở bên phải công thức, tức là G2.
Bảng Dữ liệu trông như sau:
Thực hiện phân tích với Bảng dữ liệu của công cụ phân tích điều gì sẽ xảy ra
Chọn phạm vi ô chứa công thức và hai bộ giá trị mà bạn muốn thay thế, tức là chọn phạm vi - F2: L13.
Nhấp vào tab DATA trên Ribbon.
Nhấp vào Phân tích điều gì xảy ra trong nhóm Công cụ dữ liệu.
Chọn Bảng dữ liệu từ danh sách thả xuống.
Hộp thoại Bảng Dữ liệu xuất hiện.
- Bấm vào biểu tượng trong ô nhập hàng.
- Nhấp vào ô NPER, đó là C3.
- Một lần nữa, hãy nhấp vào biểu tượng trong hộp ô nhập Hàng.
- Tiếp theo, nhấp vào biểu tượng trong hộp ô nhập cột.
- Bấm vào ô Lãi suất_Rate, là C2.
- Một lần nữa, hãy nhấp vào biểu tượng trong hộp ô nhập cột.
Bạn sẽ thấy rằng ô nhập Hàng được lấy là $ C $ 3 và ô nhập Cột được lấy là $ C $ 2. Bấm OK.
Bảng Dữ liệu được lấp đầy với các kết quả được tính toán cho mỗi sự kết hợp của hai giá trị đầu vào -
Nếu bạn có thể trả EMI là 54.000, lãi suất 12,2% và 288 EMI là phù hợp với bạn. Điều này có nghĩa là thời hạn của khoản vay sẽ là 24 năm.
Tính toán bảng dữ liệu
Bảng dữ liệu được tính toán lại mỗi khi trang tính chứa chúng được tính toán lại, ngay cả khi chúng không thay đổi. Để tăng tốc độ tính toán trong trang tính có chứa Bảng dữ liệu, bạn cần thay đổi các tùy chọn tính toán thànhAutomatically Recalculate trang tính nhưng không phải là Bảng dữ liệu, như được đưa ra trong phần tiếp theo.
Tăng tốc độ tính toán trong trang tính
Bạn có thể tăng tốc độ tính toán trong trang tính chứa Bảng dữ liệu theo hai cách:
- Từ Tùy chọn Excel.
- Từ Ribbon.
Từ Tùy chọn Excel
- Bấm vào tab TỆP trên Ruy-băng.
- Chọn Tùy chọn từ danh sách trong ngăn bên trái.
Hộp thoại Tùy chọn Excel xuất hiện.
Từ ngăn bên trái, hãy chọn Formulas.
Chọn tùy chọn Automatic except for data tables Dưới Workbook Calculationtrong phần Tùy chọn tính toán. Bấm OK.
Từ ruy-băng
Bấm vào tab CÔNG THỨC trên Ruy-băng.
Nhấn vào Calculation Options trong nhóm Tính toán.
Lựa chọn Automatic Except for Data Tables trong danh sách thả xuống.
