Cách làm bảng độ nhạy bằng Data Table trong Excel: một biến, hai biến và lỗi thường gặp
Bảng độ nhạy trong Excel được lập bằng chức năng Data Table (bảng dữ liệu) trong nhóm What-If Analysis: Excel lần lượt thay từng giá trị thử vào một hoặc hai ô giả định, tính lại mô hình và ghi kết quả vào bảng. Cách làm chỉ gồm vài bước, nhưng đặt sai ô đầu vào hay để sai chế độ tính toán sẽ cho ra một bảng số trông hợp lý mà Excel không báo lỗi. Bài viết hướng dẫn bảng một biến và hai biến trên một mô hình NPV đơn giản, giải thích cách Excel tính bảng và các lỗi hay gặp, đã chạy thử trên Excel.
1. Bảng độ nhạy trong Excel là gì?
Bảng độ nhạy trong Excel là một vùng ô cho biết kết quả của mô hình (NPV, giá trị cổ phiếu, lợi nhuận) thay đổi thế nào khi một hoặc hai giả định đầu vào nhận các giá trị khác nhau. Công cụ dùng để lập bảng là Data Table, nằm ở thẻ Data, nhóm What-If Analysis. Microsoft định nghĩa bảng dữ liệu là "một vùng ô trong đó có thể thay đổi giá trị ở một số ô để có các câu trả lời khác nhau cho một bài toán" (nguyên văn: A data table is a range of cells in which you can change values in some of the cells).
Theo Microsoft, Excel có ba công cụ phân tích "nếu… thì…": kịch bản (Scenario Manager), bảng dữ liệu (Data Table) và tìm mục tiêu (Goal Seek). Kịch bản và bảng dữ liệu nhận các bộ giá trị đầu vào rồi tính ra kết quả; Goal Seek làm ngược lại, từ kết quả mong muốn tìm giá trị đầu vào. Mỗi bảng dữ liệu nhận nhiều nhất hai ô đầu vào; khi cần thay đồng thời từ ba giả định trở lên, Microsoft hướng dẫn chuyển sang Scenario Manager.
Bài này chỉ hướng dẫn thao tác. Vì sao cần phân tích độ nhạy, chọn biến và biên độ thế nào, khác phân tích kịch bản ra sao được trình bày trong bài Phân tích độ nhạy và phân tích kịch bản trong định giá.
2. Chuẩn bị mô hình
Data Table chỉ hoạt động đúng khi kết quả cần phân tích được tính bằng công thức từ các ô giả định, không có số nào gõ cứng ở giữa. Vì vậy bước chuẩn bị quan trọng nhất là tách riêng một khối giả định và để mọi phép tính tham chiếu vào khối đó.
Ví dụ xuyên suốt bài: Công ty KV (doanh nghiệp giả định) xem xét một dây chuyền sản xuất vốn đầu tư 12 tỷ đồng, hoạt động 5 năm, khấu hao đường thẳng 2,4 tỷ đồng mỗi năm, không có giá trị thanh lý. Dòng tiền ròng hằng năm được tính theo:
Trong đó là giá bán và là chi phí biến đổi (nghìn đồng/sản phẩm), là sản lượng (nghìn sản phẩm/năm), là chi phí cố định bằng tiền, là khấu hao, là thuế suất. Với , , , tỷ đồng và : tỷ đồng mỗi năm.
Bố cục bảng tính như Hình 1. Khối giả định nằm ở B2:B8, ô B8 là suất chiết khấu 12%/năm. Dòng tiền năm 0 ở B11 là =-B6; ô C11 là =((B2-B4)*B3/1000-B5-B6/5)*(1-B7)+B6/5 (chia 1.000 để đổi triệu đồng sang tỷ đồng), cố định các tham chiếu bằng phím F4 rồi kéo sang D11:G11. Kết quả nằm ở B13 =NPV(B8;C11:G11)+B11 và B14 =IRR(B11:G11), cho NPV 1,266 tỷ đồng và IRR 16,18%. Vì sao năm 0 phải cộng ngoài hàm NPV được giải thích trong bài Cách tính NPV, IRR, XNPV, XIRR trong Excel.
Các công thức dưới đây ghi theo Excel cài định dạng vùng Việt Nam, khi đó đối số thường được ngăn bằng dấu chấm phẩy; trên Excel định dạng tiếng Anh (Mỹ) thì đổi thành dấu phẩy, ví dụ =NPV(B8,C11:G11)+B11. Tên menu giữ theo bản tiếng Anh; bản Excel tiếng Việt có thể hiển thị tên khác.

3. Cách làm bảng độ nhạy một biến
Bảng độ nhạy một biến thay một giả định bằng một dãy giá trị và cho kết quả của một hay nhiều công thức tương ứng. Ví dụ dưới đây xem NPV và IRR của Công ty KV thay đổi thế nào khi giá bán dao động từ 90 đến 110 nghìn đồng.
Các bước, theo bố cục dạng cột mà Microsoft hướng dẫn:
- Nhập các giá trị thử 90, 95, 100, 105, 110 vào cột I, từ I4 đến I8.
- Ở hàng ngay trên giá trị thử đầu tiên và lệch sang phải một cột, nhập công thức kết quả: J3 là
=B13(NPV), K3 là=B14(IRR). Có thể đặt nhiều công thức trên cùng hàng này, miễn là tất cả phụ thuộc vào cùng một ô đầu vào. - Chọn vùng I3:K8, gồm hàng công thức, cột giá trị thử và vùng kết quả trống.
- Vào Data → What-If Analysis → Data Table. Để trống "Row input cell", nhập B2 (ô giá bán trong mô hình) vào "Column input cell", bấm OK.
Gọi là "Column input cell" vì giá trị thử xếp theo cột: Excel lấy lần lượt từng giá trị ở cột I, đặt vào ô B2, tính lại mô hình rồi ghi NPV và IRR vào hàng tương ứng. Nếu giá trị thử xếp theo hàng, công thức đặt ở ô bên trái giá trị đầu tiên, lệch xuống một hàng, và khai báo "Row input cell".
| Giá bán (nghìn đồng/sản phẩm) | Dòng tiền năm (tỷ đồng) | NPV ở 12% (tỷ đồng) | IRR |
|---|---|---|---|
| 90 | 2,08 | −4,502 | −4,59% |
| 95 | 2,88 | −1,618 | 6,40% |
| 100 | 3,68 | 1,266 | 16,18% |
| 105 | 4,48 | 4,149 | 25,19% |
| 110 | 5,28 | 7,033 | 33,70% |
Mỗi nghìn đồng giá bán làm NPV thay đổi khoảng 0,577 tỷ đồng, vì 1 nghìn đồng trên 200 nghìn sản phẩm là 0,2 tỷ đồng doanh thu, còn 0,16 tỷ đồng sau thuế mỗi năm, nhân với hệ số chiết khấu 5 năm ở 12% là 3,605. Giá bán chỉ cần thấp hơn giả định khoảng 2,2% là NPV về 0: bảng cho thấy NPV đổi dấu giữa 95 và 100; vì NPV thay đổi đều 0,577 tỷ đồng cho mỗi nghìn đồng giá bán, mức giá làm NPV bằng 0 là 100 − 1,266 / 0,577 ≈ 97,8 nghìn đồng. Trong Excel, mức này cũng tìm được bằng Goal Seek: đặt B13 bằng 0 bằng cách thay đổi B2. Ngưỡng này cùng ý tưởng với sản lượng hòa vốn trong bài Điểm hòa vốn là gì?, nhưng tính theo giá trị hiện tại thay vì lợi nhuận kế toán.
4. Cách làm bảng độ nhạy hai biến
Bảng độ nhạy hai biến cho kết quả của một công thức khi hai giả định cùng thay đổi, xếp thành lưới. Ví dụ dưới đây lập NPV theo giá bán (theo cột) và suất chiết khấu (theo hàng).
- Ở ô góc trên bên trái của bảng (I12), nhập công thức kết quả
=B13. Bảng hai biến chỉ có một công thức, đặt ở đúng ô góc này. - Nhập các suất chiết khấu 10%, 12%, 14% vào hàng bên phải ô công thức (J12:L12), và các giá bán 90 đến 110 vào cột bên dưới (I13:I17).
- Chọn toàn bộ vùng I12:L17.
- Vào Data → What-If Analysis → Data Table. Nhập B8 (suất chiết khấu) vào "Row input cell" và B2 (giá bán) vào "Column input cell", bấm OK.
| Giá bán / Suất chiết khấu | 10% | 12% | 14% |
|---|---|---|---|
| 90 | −4,115 | −4,502 | −4,859 |
| 95 | −1,083 | −1,618 | −2,113 |
| 100 | 1,950 | 1,266 | 0,634 |
| 105 | 4,983 | 4,149 | 3,380 |
| 110 | 8,015 | 7,033 | 6,127 |
Đơn vị: NPV, tỷ đồng. Số liệu giả định.
Ô giữa bảng (giá 100, suất chiết khấu 12%) phải bằng đúng kết quả của mô hình, 1,266 tỷ đồng. Đây là cách kiểm tra nhanh nhất rằng bảng được khai báo đúng. Đặt giá trị cơ sở ở giữa hàng và giữa cột thì ô trung tâm luôn trùng với kết quả mô hình, nên kiểm tra được ngay bằng mắt.
Hình 2 vẽ lại ba cột của bảng. Trong biên độ đã thử, NPV nhạy với giá bán hơn nhiều so với suất chiết khấu: tăng suất chiết khấu từ 10% lên 14% làm NPV ở giá 100 giảm khoảng 1,3 tỷ đồng, trong khi giảm giá bán 5 nghìn đồng làm NPV giảm khoảng 2,9 tỷ đồng. So sánh này chỉ đúng với biên độ đã chọn, như phân tích ở bài khái niệm.

5. Excel tính bảng dữ liệu như thế nào?
Excel điền vùng kết quả bằng một công thức mảng đặc biệt là TABLE. Khi chạy thử trên Excel 2024 cho Windows (phiên bản 16.0), các ô kết quả của Bảng 1 chứa {=TABLE(;B2)} và của Bảng 2 chứa {=TABLE(B8;B2)}: đối số thứ nhất là ô đầu vào hàng, đối số thứ hai là ô đầu vào cột. Hàm này không nhập như hàm thông thường mà do Excel tạo ra khi dùng hộp thoại Data Table.
Từ cách làm này có ba hệ quả thực tế:
- Không sửa hay xóa riêng một ô kết quả được, vì cả vùng là một mảng. Muốn xóa bảng, chọn toàn bộ vùng kết quả rồi xóa; muốn đổi giá trị thử, sửa các ô ở hàng hoặc cột tiêu đề, kết quả tự cập nhật.
- Mỗi giá trị thử (bảng một biến) hoặc mỗi cặp giá trị (bảng hai biến) ứng với một lần Excel thay đầu vào và tính lại mô hình. Bảng 2 có 15 cặp nên mô hình được tính 15 lần; một bảng 9 × 9 là 81 lần. Microsoft cũng ghi rằng bảng dữ liệu không dùng tính toán đa luồng, nên với mô hình lớn, nhiều bảng dữ liệu làm mọi thao tác trong file chậm đi.
- Microsoft lưu ý rằng mỗi khi trang tính tính lại, bảng dữ liệu cũng tính lại, kể cả khi dữ liệu không đổi. Vì vậy Excel có chế độ tính "Automatic except for data tables" (Formulas → Calculation Options), tự động tính mọi thứ trừ bảng dữ liệu. Trên một số bản Microsoft 365 mới, tùy chọn này mang tên "Partial". Ở chế độ này, chọn bảng và nhấn F9 để tính lại bảng khi cần.
6. Lỗi thường gặp khi làm bảng độ nhạy trong Excel
Phần lớn lỗi dưới đây không làm Excel báo lỗi mà cho ra một bảng số đầy đủ nhưng sai. Các kết quả được chạy thử trên Excel 2024 cho Windows (phiên bản 16.0) với mô hình Công ty KV.
6.1. Ô đầu vào nằm ở sheet khác
Data Table yêu cầu ô đầu vào nằm cùng sheet với bảng. Đặt bảng ở sheet "DoNhay" và khai báo ô đầu vào là ô giá bán ở sheet mô hình, Excel báo "Input cell reference is not valid". Tài liệu hỗ trợ cũ của Microsoft (bài KB 95811, viết cho Excel 97–2002, nay chỉ còn bản lưu trữ) mô tả ô đầu vào phải là một ô trên trang tính đang mở, nằm ngoài vùng bảng, và đây là thiết kế có chủ đích. FM Magazine cũng lưu ý ô đầu vào và ô kết quả phải nằm cùng sheet với bảng.
Có hai cách xử lý. Cách đơn giản là đặt bảng ngay trên sheet mô hình, như Hình 1. Nếu muốn để bảng ở sheet riêng, tạo một ô giả định trên sheet bảng (ví dụ DoNhay!E2 chứa 100), cho ô giá bán của mô hình tham chiếu tới ô đó (=DoNhay!E2), rồi khai báo E2 làm ô đầu vào. Khi chạy thử, cách này cho kết quả trùng với Bảng 1.
6.2. Đảo ô đầu vào hàng và cột
Nếu khai báo nhầm B2 vào "Row input cell" và B8 vào "Column input cell", Excel đưa các mức 10%, 12%, 14% vào ô giá bán và đưa 90 đến 110 vào ô suất chiết khấu (tức 9.000% đến 11.000%). Bảng vẫn hiện đủ 15 số, tất cả quanh −12,1 tỷ đồng. Ở ví dụ này số sai dễ nhận ra, nhưng khi hai biến có cùng đơn vị và biên độ gần nhau (ví dụ WACC và tăng trưởng dài hạn), bảng đảo trông hoàn toàn hợp lý.
Lưu ý: Quy tắc dễ nhớ: "Row input cell" là ô nhận các giá trị nằm trên hàng tiêu đề (chạy ngang), "Column input cell" là ô nhận các giá trị nằm ở cột tiêu đề (chạy dọc). Sau khi lập bảng, luôn kiểm tra ô ứng với giá trị cơ sở có bằng kết quả mô hình hay không.
6.3. Khai báo nhầm ô đầu vào
Nếu Bảng 1 khai báo ô đầu vào là B3 (sản lượng) thay vì B2 (giá bán), Excel hiểu 90 đến 110 là sản lượng nghìn sản phẩm và cho NPV từ −11,423 đến −9,116 tỷ đồng. Bảng không báo lỗi và các số trông như một kết quả thật. Trường hợp ngược lại, khi công thức kết quả không phụ thuộc vào ô đầu vào đã khai báo, mọi dòng của bảng có cùng một giá trị. Microsoft cũng nêu rằng mọi công thức trong bảng một biến phải tham chiếu tới ô đầu vào, và bảng hai biến phải tham chiếu tới cả hai ô.
6.4. Giá trị thử tham chiếu tới chính ô đầu vào
Để tiện, nhiều người nhập giá trị thử dạng =B2-10, =B2-5, =B2, =B2+5, =B2+10, để bảng tự đổi khi giả định cơ sở đổi. Cách này làm bảng sai: khi Excel đặt một giá trị thử vào B2, các ô tiêu đề cũng tính lại theo B2 mới. Khi chạy thử, cột tiêu đề vẫn hiện 90 đến 110 nhưng kết quả lần lượt là −4,502; −7,386; −7,386; −4,502; 1,266, trong khi đúng phải là −4,502; −1,618; 1,266; 4,149; 7,033. Giá trị thử nên nhập bằng số cố định, hoặc tham chiếu tới một ô nằm ngoài chuỗi tính toán của mô hình (ví dụ ô "giá cơ sở" riêng, không phải B2).
6.5. Bảng không cập nhật khi mô hình thay đổi
Ở chế độ "Automatic except for data tables", mô hình vẫn tự tính lại nhưng bảng thì không. Khi chạy thử, đổi chi phí biến đổi từ 60 lên 62 nghìn đồng làm NPV ở B13 giảm từ 1,266 xuống 0,112 tỷ đồng, nhưng ô tương ứng trong bảng vẫn hiện 1,266 cho đến khi nhấn F9. Bảng sai kiểu này đặc biệt nguy hiểm khi dán sang báo cáo.
Lưu ý: Chế độ tính có thể đã được đặt sẵn từ trước, nhất là với file nhận từ người khác hoặc file có nhiều bảng dữ liệu. Trước khi dùng số trong bảng, kiểm tra Formulas → Calculation Options và nhấn F9 nếu cần.
6.6. Mô hình có tham chiếu vòng
Tham chiếu vòng (circular reference) thường gặp khi lãi vay được tính trên dư nợ bình quân, trong khi dư nợ cuối kỳ lại phụ thuộc dòng tiền sau lãi vay. Mô hình như vậy chỉ tính được khi bật tính lặp (File → Options → Formulas → Enable iterative calculation; mặc định tối đa 100 lần lặp, sai số 0,001). Khi chạy thử một mô hình vòng đơn giản, bảng dữ liệu cho kết quả đúng nếu bật tính lặp. Nếu tắt tính lặp, mọi dòng của bảng hiện cùng một giá trị cũ, không báo lỗi ở trong bảng.
Ngay cả khi đúng, mỗi ô của bảng lúc này là một vòng lặp đầy đủ, nên bảng chậm hơn nhiều và mỗi ô mang sai số hội tụ riêng (trong lần chạy thử, lệch khoảng 0,00001 so với nghiệm đúng). Có thể tránh vòng lặp bằng cách tính lãi trên dư nợ đầu kỳ, hoặc đặt một ô "công tắc" để chuyển lãi vay sang tính trên dư nợ đầu kỳ khi cần ngắt vòng.
Cách xử lý vòng lặp lãi vay trong mô hình tài chính được trình bày trong bài Cách lập mô hình ba báo cáo tài chính
7. Câu hỏi thường gặp
Có làm được bảng độ nhạy ba biến không?
Một Data Table chỉ nhận tối đa hai ô đầu vào. Muốn thay đổi ba biến, có thể lập nhiều bảng hai biến, mỗi bảng ứng với một giá trị của biến thứ ba, hoặc dùng Scenario Manager cho các bộ giả định đi cùng nhau.
Data Table khác Goal Seek thế nào?
Data Table trả lời câu hỏi "nếu giả định bằng các giá trị này thì kết quả là bao nhiêu", cho cả một dãy kết quả. Goal Seek trả lời câu hỏi ngược lại: "giả định phải bằng bao nhiêu để kết quả đạt một mức cho trước", như mức giá 97,8 nghìn đồng làm NPV bằng 0 ở mục 3. Hai công cụ thường dùng cùng nhau.
Vì sao bảng hiện cùng một số ở mọi ô?
Thường do công thức kết quả không phụ thuộc vào ô đầu vào đã khai báo (mục 6.3), do mô hình có tham chiếu vòng nhưng tắt tính lặp (mục 6.6), hoặc do bảng chưa được tính lại (mục 6.5). Kiểm tra theo thứ tự đó.
Kết luận
Bảng độ nhạy trong Excel được lập bằng Data Table: công thức kết quả đặt ở góc bảng, giá trị thử xếp theo hàng hoặc cột, và ô đầu vào là ô giả định nằm trong mô hình trên cùng sheet. Excel tính lại mô hình cho mỗi giá trị thử hoặc mỗi cặp giá trị, nên bảng chỉ đúng khi mô hình không có số gõ cứng, ô đầu vào khai báo đúng hàng và cột, và chế độ tính toán cho phép bảng cập nhật. Kiểm tra ô ứng với giá trị cơ sở trước khi dùng bảng là bước đơn giản nhất để phát hiện các lỗi này.
Bài viết mang tính giáo dục, không phải khuyến nghị đầu tư.
Nguồn tham khảo
- Microsoft Support, Calculate multiple results by using a data table: https://support.microsoft.com/en-us/office/calculate-multiple-results-by-using-a-data-table-e95e2487-6ca6-4413-ad12-77542a5ea50b
- Microsoft Support, Change formula recalculation, iteration, or precision in Excel: https://support.microsoft.com/en-us/office/change-formula-recalculation-iteration-or-precision-in-excel-73fc7dac-91cf-4d36-86e8-67124f6bcce4
- Microsoft Knowledge Base 95811, XL: Error Message: "Input Cell Reference Is Not Valid" (bản lưu trữ không chính thức, áp dụng Excel 97–2002): https://mskb.pkisolutions.com/kb/95811
- FM Magazine, How to use Data Tables in Excel (11/2024): https://www.fm-magazine.com/news/2024/nov/how-to-use-data-tables-in-excel/
- Microsoft Learn, Excel Recalculation: https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation
- Microsoft Support, Remove or allow a circular reference in Excel: https://support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-in-excel-8540bd0f-6e97-4483-bcf7-1b49cd50d123
Đọc tiếp: Phân tích độ nhạy và phân tích kịch bản trong định giá