Tự động tổng

Vị trí trên ribbon: Tab DVD Addin → Nhóm Công thức

ID lệnh: btnAutosum

Chức năng

Lệnh chèn tự động công thức SUBTOTAL(9;…) vào cột số liệu, dựa vào cột key để biết dòng nào là dòng tổng và dòng tổng đó cộng những dòng nào:

• Cột key chỉ ghi ở dòng hạng mục, các dòng chi tiết để trống (như ví dụ bên dưới): mỗi key là một dòng tổng, cộng các dòng nằm giữa key đó và key kế tiếp.
• Cột key ghi ở hầu hết các dòng: lệnh đọc cấp của từng dòng từ cột key rồi cho mỗi dòng tổng cộng các dòng cấp con nằm ngay bên dưới, đến dòng cùng cấp hoặc cấp cao hơn kế tiếp.
  • Cột STT: số La Mã (I, II…) là cấp nhóm (cấp 0); số 1 / 1.1 / 1.1.1 là cấp 1 / 2 / 3.
  • Không phải cột STT: số cấp kiểu Gantt, từ 1 đến 20.
  • Cột key toàn chữ (tên công tác…): dòng có key mà ô số liệu để trống là dòng tổng.

Đây là cách nhanh nhất để dựng dòng tổng cho bảng khối lượng nhiều hạng mục: cột hạng mục đóng vai trò cột key, cột khối lượng là cột số liệu, và mỗi hạng mục tự có một dòng cộng riêng mà không phải kéo tay từng công thức.

Cách dùng

1. Chuẩn bị bảng có một cột key (cột STT, cột cấp, mã hạng mục, tên công tác…) và một cột số liệu cần cộng.
2. Vào tab DVD Addin → nhóm Công thức → bấm Tự động tổng.
3. Cửa sổ công cụ mở ra ở thẻ Tự động tổng (nhóm thẻ Tính toán, bên cạnh Điền kết quả và Tham chiếu).
4. Ở ô Vùng cột key, chọn dải ô của cột key trên bảng tính.
5. Ở ô Vùng số liệu, chọn dải ô của cột cần cộng (cùng số dòng với cột key).
6. Bấm Tạo SUBTOTAL, rồi trong cửa sổ Xác nhận thay đổi dữ liệu giữ tick sao lưu sheet và bấm Tiếp tục. Công thức được ghi thẳng vào ô cột số liệu ở từng dòng tổng.

Video hướng dẫn

Tự động tổng · 5 phút 37 giây · 12 trường hợp · Tải video · File thực hành theo video

Các chương (18)

1. Giới thiệu
2. Mở lệnh
3. Cửa sổ Tự động tổng
4. 1 · Cột key chỉ ở dòng tổng
5. Cách đọc cột key
6. 2 · Cột STT nhiều cấp
7. 3 · Bảng cũ đã có tổng
8. 4 · Cột cấp 1, 2, 3
9. 5 · Bẫy: STT chỉ có 1, 2, 3
10. 6 · Cột tên công việc làm key
11. 7 · Nhiều cột số liệu
12. 8 · Cột hạng mục gộp ô
13. 9 · Ghi đè và hoàn tác
14. 10 · Bảng đang lọc
15. 11 · Sheet đang khoá
16. 12 · Khi nhập sai
17. Mẹo
18. Kết thúc

Hướng dẫn bằng hình

Thực hành

Tải file mẫu của lệnh này — dữ liệu y như hình dưới (sheet Khối lượng), mở ra là làm theo được ngay từng bước.

Bước 1. Chuẩn bị bảng có cột key (1) — ở đây cột hạng mục chỉ ghi ở dòng tổng, các dòng chi tiết để trống — và cột số liệu cần cộng (2), hai cột cùng số dòng.

Bước 2. Trên ribbon, mở tab DVD Addin → nhóm Công thức → bấm Tự động tổng.

Bước 3. Cửa sổ công cụ Tính toán mở ở thẻ Tự động tổng. Ở ô Vùng cột key (1) gõ hoặc bấm ⤴ chọn A4:A11, ở ô Vùng số liệu (2) chọn D4:D11, rồi bấm Tạo SUBTOTAL (3).

Bước 4. Hộp xác nhận nhắc rằng dòng tổng chỉ được chọn theo cột key, ô đích có gì cũng bị thay bằng SUBTOTAL. Giữ tick sao lưu sheet (1) rồi bấm Tiếp tục (2).

Bước 5. Mỗi dòng có key nhận công thức SUBTOTAL(9, …) cộng các dòng chi tiết đến key kế tiếp (1) — ô D4 cộng D5:D7, ô D8 cộng D9:D11. Dòng chi tiết giữ nguyên. Thanh trạng thái báo số công thức đã ghi (2).

Bước 6. Nếu cột key là cột STT kiểu I, 1, 2, 3, II, 1, 2 (hoặc 1, 1.1, 1.1.1), lệnh tự đọc cấp: dòng I cộng các dòng con bên dưới đến dòng II. Ô dòng tổng đang có số gõ tay thì được giữ lại và liệt kê trong thông báo.

Ví dụ

Bảng khối lượng phần thô:

	A (Hạng mục)	B (Chi tiết)	C (Khối lượng)
5	MÓNG		(SUBTOTAL của C6:C8)
6		Bê tông lót	12,4
7		Bê tông móng	86,5
8		Cốp pha móng	210,0
9	THÂN		(SUBTOTAL của C10:C12)
10		Cột tầng 1	34,2

Chọn Vùng cột key = A5:A12, Vùng số liệu = C5:C12, bấm Tạo SUBTOTAL: ô C5 và C9 nhận công thức SUBTOTAL(9;…) cộng đúng các dòng chi tiết thuộc hạng mục của mình.

Nếu cột key là cột STT đánh I, 1, 2, 3, II, 1, 2, dòng I nhận SUBTOTAL cộng ba dòng 1–3 bên dưới, dòng II cộng hai dòng sau nó; các dòng 1, 2, 3 là dòng chi tiết nên giữ nguyên khối lượng.

Lưu ý

• Hai vùng phải cùng số dòng và thẳng hàng nhau; lệch dòng sẽ khiến công thức cộng sai khoảng.
• Công thức chỉ được ghi vào dòng tổng; các dòng chi tiết giữ nguyên.
• SUBTOTAL(9;…) bỏ qua các dòng bị ẩn bởi bộ lọc và không cộng chồng các SUBTOTAL khác — đúng nghĩa dòng tổng hạng mục.
• Khi lệnh đọc cấp từ cột key, dòng tổng nào mà ô số liệu đang có số gõ tay (không phải công thức) sẽ không bị ghi đè — nhiều khả năng đó là dòng chi tiết bị nhận nhầm. Thông báo cuối liệt kê các ô được giữ lại (tối đa 5 địa chỉ); muốn thay thì xóa số ở các ô đó rồi chạy lại.
• Ô dòng tổng đang chứa công thức cũ thì bị ghi đè bằng SUBTOTAL; với bảng có cột key chỉ ghi ở dòng hạng mục, mọi nội dung cũ ở ô dòng tổng đều bị ghi đè. Nếu giữ tick sao lưu sheet trong cửa sổ xác nhận, bấm Ctrl+Z ngay sau lệnh để đưa sheet về như trước; bản sao lưu vẫn còn trong Khôi phục dữ liệu.
• Nếu không nhận ra dòng tổng nào, lệnh không ghi gì và thông báo lý do (ví dụ cột key toàn chữ nhưng không có dòng nào để trống ô số liệu kèm dòng chi tiết bên dưới).
• Cửa sổ công cụ ở dạng không khóa bảng tính, nên bạn có thể đổi vùng chọn rồi chạy tiếp cho bảng khác.
• Nút chọn vùng ⤴ mở hộp chọn vùng không khoá Excel: cửa sổ này tạm tránh khỏi màn hình, trong lúc chọn vẫn cuộn, đổi sheet, đổi sổ được. Bấm OK hoặc Enter trên sheet để điền địa chỉ vào ô; Hủy hoặc Esc thì ô giữ nguyên. Ô đang có địa chỉ thì hộp điền sẵn địa chỉ ấy.

Liên quan

• Điền kết quả — thẻ cùng cửa sổ, ghép kết quả tính vào ô diễn giải theo dạng "diễn giải = kết quả".
• Tham chiếu — thẻ cùng cửa sổ, gán công thức tham chiếu trực tiếp giữa hai bảng theo cột key.
• Hàm làm tròn — bọc ROUND quanh các công thức vừa tạo để chốt số lẻ theo hồ sơ.