Công thức (Formula Tools)
Nhóm Công thức trên tab DVD Addin gom chín lệnh xử lý công thức theo cả vùng cho bảng khối lượng, bảng dự toán và bảng thanh toán: dựng dòng tổng hạng mục, kéo đơn giá giữa hai bảng, chốt số lẻ, khóa dấu $ và truy vết khi một ô ra số lạ.
Điểm chung của cả nhóm là viết thẳng vào công thức của hàng trăm ô chỉ với một lần bấm — thứ mà thao tác tay từng ô rất dễ sai và rất tốn thời gian trước hạn nộp hồ sơ.
Các lệnh trong nhóm
| Lệnh | Chức năng |
|---|---|
| Xem công thức | Tách công thức của ô đang chọn thành từng thành phần bấm được để soi giá trị |
| Truy vết Lookup | Nhảy tới đúng ô nguồn mà công thức dò tìm đã lấy giá trị về |
| Ô phụ thuộc | Liệt kê những ô có công thức đang tham chiếu tới ô hiện hành |
| Tự động tổng | Sinh SUBTOTAL(9;…) tại mỗi key mới của cột key |
| Điền kết quả | Ghép kết quả vào ô diễn giải theo dạng diễn giải = kết quả |
| Tham chiếu | Nối hai bảng bằng công thức tham chiếu trực tiếp theo cột key |
| Hàm làm tròn | Thêm hoặc gỡ ROUND / ROUNDUP / ROUNDDOWN quanh công thức trong vùng chọn |
| Khóa tham chiếu | Đổi kiểu tham chiếu giữa A1, $A1, A$1, $A$1 cho cả vùng |
| Kiểm tra lỗi | Bật/tắt cảnh báo lỗi công thức (tam giác xanh) cho vùng chọn hoặc toàn Excel |
Ba lệnh đầu nằm trong menu Truy vết và dùng chung cửa sổ Truy vết công thức (hai thẻ). Tự động tổng là nút chính của nhóm, mở cửa sổ công cụ với nhóm thẻ Tính toán — nơi Điền kết quả và Tham chiếu cũng là hai thẻ kề bên. Ba lệnh còn lại của Menu Tính toán mở cửa sổ riêng cùng kiểu: chọn tuỳ chọn rồi bấm Áp dụng, cửa sổ vẫn mở để bạn đổi vùng chọn và chạy tiếp.
Quy trình hoàn thiện một bảng khối lượng
- Kéo đơn giá từ sheet đơn giá sang — mở Tham chiếu, chọn Vùng nguồn (cột đầu là mã hiệu, cột cuối để trống chờ công thức) và Vùng đích (cột đầu là mã hiệu, cột cuối là đơn giá), bấm Gán.
- Chốt số lẻ — chọn cột khối lượng, mở Hàm làm tròn, để Hành động = Thêm hàm, Hàm = ROUND, Số chữ số thập phân =
2, bấm Áp dụng. - Khóa vùng tra cứu trước khi kéo công thức xuống — mở Khóa tham chiếu, chọn Loại khóa = Khóa cả hàng và cột — $A$1, bấm Áp dụng trên đúng vùng hẹp cần khóa.
- Dựng dòng tổng cho từng hạng mục — mở Tự động tổng, chọn Vùng cột key (cột hạng mục) và Vùng số liệu (cột khối lượng, cùng số dòng), bấm Tạo SUBTOTAL.
- Ghép cách tính vào cột diễn giải — mở Điền kết quả, chọn Vùng diễn giải và Vùng kết quả, bấm Điền kết quả.
- Soát ô ra số lạ — chọn ô nghi ngờ rồi dùng Xem công thức để bấm vào từng thành phần, hoặc Truy vết Lookup để nhảy thẳng tới ô đơn giá gốc.
- Dọn tam giác xanh trước khi in — chọn vùng, mở Kiểm tra lỗi, để Phạm vi áp dụng = Vùng đã chọn, bỏ tick Bật kiểm tra lỗi, bấm Áp dụng.
Ví dụ
Bảng khối lượng phần móng và cột tầng 1 trên sheet KHOI LUONG, sau khi chạy lần lượt Hàm làm tròn → Tự động tổng → Điền kết quả:
| Hàng | A (Hạng mục) | C (Diễn giải) | D (Khối lượng) |
|---|---|---|---|
| 6 | MÓNG M1 | =SUBTOTAL(9;D7:D8) → 47,36 | |
| 7 | 4 hố x 2,5 x 2,5 x 1,6 = 40 | =ROUND(4*2,5*2,5*1,6;2) → 40,00 | |
| 8 | Giằng: 2 x 18,4 x 0,4 x 0,5 = 7,36 | =ROUND(2*18,4*0,4*0,5;2) → 7,36 | |
| 9 | CỘT TẦNG 1 | =SUBTOTAL(9;D10:D11) → 8,63 | |
| 10 | 12 cột x 0,3 x 0,3 x 3,6 = 3,89 | =ROUND(12*0,3*0,3*3,6;2) → 3,89 | |
| 11 | 8 cột x 0,4 x 0,4 x 3,7 = 4,74 | =ROUND(8*0,4*0,4*3,7;2) → 4,74 |
Với Tự động tổng, hai vùng đưa vào là A6:A11 (cột key) và D6:D11 (cột số liệu): công thức chỉ được ghi tại D6 và D9 — đúng các hàng có key mới — còn các hàng chi tiết giữ nguyên. Sau đó SUBTOTAL(9;…) bỏ qua dòng bị lọc ẩn nên khi bạn lọc riêng một hạng mục, dòng tổng vẫn đúng nghĩa.
Mẹo và lưu ý
- Hàm làm tròn, Khóa tham chiếu, Điền kết quả và Tham chiếu đều ghi đè trực tiếp và không hoàn tác được bằng
Ctrl+Z— lưu file (hoặc nhân bản sheet) trước khi chạy trên bảng lớn. - Tự động tổng và Điền kết quả yêu cầu hai vùng cùng số dòng và thẳng hàng nhau; lệch một dòng là cộng sai khoảng hoặc ghép nhầm kết quả sang dòng khác.
- Với Tham chiếu, vùng đích phải có từ 2 cột trở lên: cột đầu luôn là key, cột cuối là giá trị. Hàng nào không khớp key thì không được gán công thức — nên rà chính tả mã hiệu và khoảng trắng thừa trước.
- Khóa tham chiếu áp loại khóa cho toàn bộ tham chiếu trong công thức của vùng chọn, kể cả những ô lẽ ra nên để trôi; hãy chọn vùng hẹp đúng phần cần xử lý.
- Kiểm tra lỗi chỉ bật/tắt cờ cảnh báo, không sửa công thức và không làm mất lỗi thật như
#REF!. Phạm vi Toàn ứng dụng Excel là thiết lập của Excel trên máy bạn, có hiệu lực với mọi workbook — nhớ bật lại sau khi in xong. - Truy vết Lookup chỉ nhận diện XLOOKUP, VLOOKUP, HLOOKUP, INDEX và luôn dò theo kiểu khớp chính xác; công thức dùng OFFSET, INDIRECT hay dò gần đúng có thể ra ô đích không như thật.
- Điền kết quả và Tham chiếu cần tài khoản DVDAddin còn hạn; trong thời gian dùng thử vẫn dùng đủ.
Xem thêm
- Giải thích & Sửa công thức (Coach) — nhờ AI diễn giải hoặc sửa công thức khi tự soát chưa ra.
- Hàm vùng và tra cứu —
dvdXlookup,dvdELookup,dvdSumVisiblecho các tình huống công thức mà hàm Excel gốc chưa xử lý gọn. - Ô và Dòng — chèn/xóa dòng, gộp ô và dọn dẹp bảng trước khi dựng công thức.