Hàm Excel tính khối lượng xây dựng cho người bóc tách

Hôm bóc khối lượng tầng 3, mình ngồi cạnh một bạn kỹ sư. Bạn mở file ra, cột bê tông dầm ghi 41,7 m³. Mình hỏi: con số này từ đâu. Bạn chỉ xuống dưới, ba dòng, mỗi dòng một đoạn dầm, cộng tay bằng máy tính điện thoại. Bốn mươi dòng nữa của cột đó cũng cộng tay như vậy.

Hỏi thêm mới biết bạn học Excel ở trường đúng hai buổi, đủ để biết SUM. Còn cả file dự toán của công trình thì bạn làm bằng cách chép số từ máy tính điện thoại sang.

Vấn đề không phải bạn dốt. Vấn đề là không ai dạy hàm Excel theo kiểu nghề xây dựng cần gì. Người ta dạy VLOOKUP bằng ví dụ mã hàng, tên nhân viên, điểm thi — nghe hợp lý nhưng không dính gì tới công việc bạn làm mỗi ngày.

Nên mình viết lại theo thứ tự công việc: bóc khối lượng, tra đơn giá, cộng theo hạng mục, làm tròn để gửi đi, rồi lọc ra để in.

Trước khi viết hàm, sửa cái bảng cho đúng

Đây là chỗ mất thời gian nhất mà ít ai chịu sửa. Đa số bảng khối lượng trên công trường trình bày theo lối truyền thống: dòng tiêu đề hạng mục in đậm xen giữa dữ liệu, ô hợp nhất, dòng trống ngăn cách giữa các phần. Nhìn đẹp. In ra thì giống hồ sơ thật.

Nhưng bảng đó không lọc được. Không tổng hợp được. Không vẽ biểu đồ được. Bạn đặt AutoFilter lên, chọn "Cốt thép" xong là mấy dòng tiêu đề hạng mục trôi lung tung, cột tổng cộng vẫn cộng cả phần bị ẩn nên ra số sai.

Cách chữa chỉ một câu: tách dữ liệu ra khỏi trình bày. Dữ liệu để phẳng trên một sheet riêng — mỗi dòng một đầu việc, mỗi cột một trường, không ô hợp nhất, không dòng trống. Sheet in thì tạo riêng, lấy số từ sheet dữ liệu sang.

Và một chi tiết nhỏ nhưng đổi hẳn cách làm việc: chuyển vùng dữ liệu thành bảng bằng Ctrl+T. Làm xong, công thức viết ở dòng đầu tự động mở rộng xuống hết bảng khi bạn thêm dòng mới, và tham chiếu của bạn đọc thành [@Số lượng] thay vì E7 — sáu tháng sau ngồi đọc lại bạn vẫn hiểu mình đang nhân cái gì với cái gì.

Cái bẫy trước mọi cái bẫy khác: số thật và chữ trông như số

Bảng khối lượng hay bị lỗi này nhất vì số liệu dán từ nhiều nguồn. Có người gửi từ file Word, có người gõ tay, Excel mặc định cột đó về dạng Text nên 123,45 sau khi dán thành chuỗi ký tự. Nó nằm bên phải ô, hoặc bên trái, nhìn không khác gì số thật.

Cách thử trong nửa giây: bôi ba ô bất kỳ trong cột đó, nhìn thanh trạng thái dưới cùng màn hình. Nếu hiện Average và Sum thì đó là số thật. Nếu chỉ hiện Count thì đó là chữ.

Sửa thì bôi cột, vào Data rồi Text to Columns, bấm Finish luôn là xong. Làm thủ công từng ô mất cả buổi và vẫn sót.

Cộng theo hạng mục: SUMIFS là hàm bạn dùng nhiều nhất

Giả sử sheet dữ liệu tên KL, có các cột: Hạng mục, Tầng, Mã hiệu, Đơn vị, Khối lượng, Đơn giá, Thành tiền. Bạn muốn biết tổng bê tông dầm của riêng tầng 3.

=SUMIFS(KL!E:E; KL!A:A; "Bê tông dầm"; KL!B:B; "Tầng 3")

Điểm mạnh của SUMIFS là cộng được nhiều điều kiện cùng lúc mà không phải sắp xếp lại dữ liệu. Trên file thật, mình hay viết thế này để lấy tổng cốt thép của một tầng:

=SUMIFS(KL!$E:$E; KL!$A:$A; $A5; KL!$B:$B; $C$2)

Ô C2 là ô mình gõ tên tầng vào. Đổi C2 từ "Tầng 3" sang "Tầng 4" là cả bảng tổng hợp tự tính lại. Cách này tiện cho lúc chủ đầu tư hỏi vặn từng tầng.

Một lỗi rất hay gặp: vùng điều kiện và vùng cộng không khớp số dòng. Viết KL!E2:E100 nhưng điều kiện KL!A1:A100 thì kết quả lệch một dòng mà Excel không báo gì cả. Nó im lặng trả số sai. Vì vậy khi bảng dữ liệu đã thành Table, dùng KL[Khối lượng] và KL[Hạng mục] — hai vùng luôn cùng chiều dài, hết lệch.

SUMPRODUCT khi điều kiện phức tạp hơn

SUMIFS không so sánh được theo vùng, không nhân hai cột với nhau. Lúc đó dùng SUMPRODUCT. Ví dụ đếm tiền thép theo đường kính nhỏ hơn 18:

=SUMPRODUCT((KL[Bảng]="Cốt thép")*(KL[Đường kính]<18)*KL[Khối lượng]*KL[Đơn giá])

Hàm này nặng hơn, nên đừng trải nó ra vài trăm dòng trên file lớn. Với bảng khối lượng vài nghìn dòng thì vẫn chạy êm.

SUBTOTAL — hàm của người hay lọc bảng

Đây là hàm mình ưu tiên cho dòng tổng ở cuối bảng vì nó hiểu lọc. Khi bạn lọc ra chỉ xem phần móng, SUBTOTAL(109; ...) cộng đúng phần đang nhìn thấy. SUM thì vẫn cộng cả dòng bị ẩn, và đây là lý do nhiều bảng khối lượng in ra có dòng tổng sai mà không ai phát hiện.

=SUBTOTAL(109; KL[Thành tiền])

Số 109 nghĩa là cộng và bỏ qua dòng đã ẩn thủ công. Dùng 9 thì nó bỏ qua kết quả của SUBTOTAL lồng bên trong nhưng vẫn tính dòng ẩn. Khác biệt này nhỏ, nhưng gặp trong file người khác thì dễ gây tranh cãi.

Tra đơn giá: VLOOKUP và INDEX MATCH

Bảng dự toán cần lấy đơn giá từ một bảng giá riêng. Đây là lúc VLOOKUP phát huy, với điều kiện bảng giá đã sắp xếp theo mã hiệu tăng dần và mã hiệu nằm ở cột trái cùng.

=VLOOKUP($C5; DonGia!$A:$D; 3; 0)

Số 0 ở cuối là tham số quan trọng nhất và cũng là thứ hay bị bỏ quên. Không có nó, hàm chạy ở chế độ tra tương đối và trả về đơn giá của một mã hiệu khác gần giống. Bảng dự toán sai giá mà nhìn công thức thì không thấy sai.

VLOOKUP có hai điểm yếu cứng. Một, nó chỉ tra được sang phải, nên muốn tra ngược phải đảo cột trong bảng giá — việc mà bạn không nên làm nếu bên khác cũng đang dùng bảng đó. Hai, khi chèn thêm cột vào giữa bảng giá, số 3 không tự đổi và hàm lấy sai cột.

INDEX MATCH giải quyết cả hai:

=INDEX(DonGia!$C:$C; MATCH($C5; DonGia!$A:$A; 0))

Tra được cả hai chiều, và chèn cột vào giữa cũng không lệch. Công thức dài hơn một chút, đổi lại ít phải sửa về sau. Nếu máy bạn dùng Microsoft 365 thì XLOOKUP gọn nhất — tra hai chiều, có giá trị mặc định khi không tìm thấy:

=XLOOKUP($C5; DonGia!$A:$A; DonGia!$C:$C; "Chưa có giá")

Chỗ này phải cẩn thận

XLOOKUP, FILTER, SORT, UNIQUE chỉ có trên bản Microsoft 365. Bạn viết công thức trên máy mình, gửi file cho bên thầu phụ dùng Office 2019 mua đứt, mở ra là một rừng #NAME?. Không phải file hỏng, chỉ là hàm không tồn tại ở phiên bản đó. Nếu file phải gửi ra ngoài cho nhiều bên, dùng VLOOKUP hoặc INDEX MATCH cho lành.

Làm tròn khối lượng trước khi gửi đi

Khối lượng tính ra thường là số lẻ nhiều chữ số thập phân, ví dụ 41,708333 m³. Đem con số đó vào hợp đồng thì bên nào cũng cãi. Cách làm trên công trường là làm tròn hai chữ số thập phân, và ghi rõ quy ước đó trong biên bản.

=ROUND(E5; 2)

Cẩn thận với ROUND trên số đã bị chia. Nếu bạn ROUND từng dòng rồi cộng lại, tổng có thể lệch nhỏ so với cộng rồi mới làm tròn. Với khối lượng lớn thì sai số này tích lại thành chuyện. Quy ước mình vẫn dùng: làm tròn ở cấp dòng, ghi rõ ở ghi chú bảng, và dòng tổng cộng từ số đã làm tròn — để con số trên giấy khớp với con số khi cộng tay mà kiểm tra lại.

Hai hàm nữa đáng nhớ:

CEILING và MROUND là hai hàm ít ai dạy nhưng lại đúng cái nghề cần. Đặt 46,2 chuyến xe thì thực tế phải gọi 47 chuyến.

Một bảng khối lượng thật, đi từ đầu đến cuối

Để bạn thấy nó ghép lại thế nào, lấy ví dụ một hạng mục nhỏ: cốt thép dầm sàn tầng 3 của nhà phố 5 tầng, một tầng hầm. File có hai sheet.

Sheet KL gồm các cột Hạng mục, Tầng, Mã hiệu, Đường kính, Đơn vị, Khối lượng. Sheet DonGia gồm Mã hiệu, Mô tả, Đơn vị, Đơn giá.

Bảng khối lượng sau khi bóc xong ở tầng 3 trông thế này:

Ở sheet dự toán, dòng tổng thép tầng 3 viết bằng SUMIFS lọc theo Tầng:

=SUMIFS(KL[Khối lượng]; KL[Tầng]; "Tầng 3"; KL[Hạng mục]; "Cốt thép")

Ra 5.939,00 kg. Đổi chữ "Tầng 3" trong ô điều khiển sang "Tầng 4" là con số nhảy ngay, không phải sửa công thức.

Rồi lấy đơn giá từ sheet DonGia bằng INDEX MATCH, và nhân ra tiền:

Đến đây mới thấy một chuyện mà cộng tay không bao giờ lộ ra: chênh lệch giữa đơn giá D10 ở hai cấu kiện. Nếu bảng giá có hai mã hiệu khác nhau, VLOOKUP tra nhầm cột sẽ ra đơn giá sai cho một trong hai dòng, và tổng tiền lệch vài trăm nghìn. Không phải lỗi to, nhưng đủ để hồ sơ bị trả lại.

Kiểm tra trước khi gửi: ba việc mất mười phút

Việc thứ nhất, dán đè bằng Values. Bảng khối lượng hay có liên kết sang file gốc của người bóc trước. Gửi đi, bên nhận mở ra, Excel hỏi có muốn cập nhật liên kết không, bấm Update, số liệu nhảy theo file mà họ không có. Chọn cả bảng, Ctrl+C, rồi Paste Special chọn Values. Liên kết đứt, số đứng yên.

Việc thứ hai, bật Trace Precedents cho vài ô tổng quan trọng để xem nó thật sự cộng từ đâu. Có lần mình phát hiện một dòng tổng đang cộng vùng thiếu mất mười dòng cuối, do ai đó chèn dòng mà không mở rộng vùng. Excel không báo lỗi gì, nó chỉ trả số nhỏ hơn sự thật.

Việc thứ ba, kiểm tra giới hạn mười lăm chữ số có nghĩa của Excel. Mã hiệu công việc dài mười tám ký tự toàn số sẽ bị cắt đuôi sau chữ số thứ mười lăm, và số cuối cùng đổi thành 0. Mã hiệu của bạn thành mã khác. Cách phòng: để cột mã hiệu ở dạng Text trước khi nhập.

Về chuyện Power Query, nói thật một câu

Mình từng nghĩ Power Query là thứ chỉ dân văn phòng cần. Đến lúc phải gộp mười hai file báo cáo khối lượng theo tháng thành một bảng để tổng hợp, mỗi file một cấu trúc cột hơi khác nhau, mình mới thấy sai. Gộp bằng tay mất một buổi, tháng sau lặp lại đúng một buổi nữa. Power Query làm một lần, cấu hình khoảng bốn mươi phút, tháng sau chỉ bấm Refresh.

Nó không khó bằng tiếng đồn. Nhưng nó cũng không phải thứ bạn cần học ngay tuần đầu làm dự toán. Dùng khi nào thấy mình đang gộp tay lần thứ ba thì hãy học.

Còn cái này thì Excel không làm tốt

Nói thẳng để bạn khỏi mất công tìm. Excel cộng, tra, lọc thì rất tốt. Nhưng nó không hiểu quan hệ công việc, nên vẽ tiến độ trong Excel là vẽ thanh bằng tay và mỗi lần đổi ngày phải kéo lại. Việc đó thuộc về MS Project.

Và Excel không quản lý phiên bản. Hai người cùng sửa một file gửi qua lại bằng email là mười bữa sau có hai bản khối lượng khác nhau, không bản nào được gọi là bản đúng. Cách chữa rẻ nhất không phải mua phần mềm gì, mà đặt quy ước đặt tên file có ngày và tên người sửa, và một người chịu trách nhiệm làm bản gốc.

Bộ bảng dựng sẵn để khỏi làm lại từ số không

Mấy cái trên mình viết lại từ tài liệu Hướng dẫn sử dụng Excel cho người làm xây dựng — bản song ngữ Việt – Anh, 117 trang, gồm 12 chương và 2 phụ lục. Phần đáng nói không nằm ở công thức riêng lẻ mà ở bảy mẫu bảng dùng được ngay, trong đó có:

• Bảng khối lượng có tính toán kích thước
• Bảng dự toán tra đơn giá bằng VLOOKUP
• Bảng theo dõi khối lượng thực hiện so với hợp đồng
• Sổ nhật ký vật tư nhập – xuất – tồn
• Bảng chấm công và tính lương khoán theo khối lượng

Và bảng tra hàm theo công việc xây dựng ở cuối tài liệu — tra theo việc cần làm chứ không theo bảng chữ cái, đúng kiểu lúc đang ngồi giữa dòng dự toán mà cần gấp một hàm.

Giá 79.000đ. 5 trang xem trước ở ngay trang bán để bạn thấy trước khi quyết định, không phải nghe mô tả.

Thanh toán chuyển khoản trong nước, không cần thẻ quốc tế, không cần ví điện tử gì. Nhận hàng trong 24h qua email.

Xem tại: https://globalmarket.vin/vi/product/huong-dan-excel

Còn nếu bạn mới bắt đầu và chưa biết SUMIFS khác gì SUM, thì cứ bắt đầu ở chỗ dễ nhất: mở file khối lượng đang làm, chuyển vùng dữ liệu thành bảng bằng Ctrl+T, rồi thay một dòng tổng cộng tay bằng SUBTOTAL. Một việc thôi. Tuần sau làm việc tiếp theo.