5 hàm Excel kỹ sư MEP nên biết (kèm ví dụ RFI, vật tư)

Kỹ sư MEP không cần biết lập trình để làm báo cáo nhanh. Chỉ cần nắm chắc 5 hàm Excel dưới đây, bạn đã tự dựng được RFI log biết đếm quá hạn, bảng tổng hợp vật tư theo tầng và cảnh báo tự động. Mỗi hàm đi kèm một ví dụ lấy từ công việc thật ở công trường.

COUNTIFS: đếm RFI quá hạn theo hệ thống

COUNTIFS đếm số dòng thỏa mãn đồng thời nhiều điều kiện. Đây là hàm quan trọng nhất cho báo cáo tuần, vì hầu hết câu hỏi của giao ban đều là "bao nhiêu cái…".

Cú pháp: =COUNTIFS(vùng_1; điều_kiện_1; vùng_2; điều_kiện_2; …)

Giả sử sheet RFI có cột C là hệ thống, cột H là hạn trả lời, cột J là trạng thái. Đếm RFI của hệ thống PCCC đang mở và đã quá hạn:

=COUNTIFS(C:C; "PCCC"; J:J; "Mở"; H:H; "<"&TODAY())

Mẹo nhỏ: đặt tên hệ thống ở một ô (ví dụ A2) và viết C:C; A2 thay vì gõ chữ "PCCC". Khi đó chỉ cần kéo công thức xuống là có bảng đếm cho HVAC, Điện, Cấp thoát nước.

SUMIFS: tổng khối lượng vật tư theo tầng

SUMIFS cộng các giá trị thỏa mãn nhiều điều kiện – dùng để tổng hợp khối lượng, chi phí theo tầng, theo hệ thống, theo nhà cung cấp.

Cú pháp: =SUMIFS(vùng_cộng; vùng_đk_1; điều_kiện_1; …)

Ví dụ bảng bóc khối lượng có cột B là tầng, cột D là loại vật tư, cột F là khối lượng. Tổng số mét ống thép DN50 ở tầng 12:

=SUMIFS(F:F; B:B; "Tầng 12"; D:D; "Ống thép DN50")

Kết hợp với bảng tầng ở hàng và loại vật tư ở cột, bạn có ngay ma trận khối lượng để đối chiếu với đơn đặt hàng – không cần PivotTable.

INDEX + MATCH và XLOOKUP: tra cứu thông tin

INDEX + MATCH tìm một giá trị theo mã và trả về thông tin ở cột khác, ví dụ tra tên nhà cung cấp, đơn giá, hạn giao hàng theo mã vật tư.

=INDEX(Danh_muc!C:C; MATCH(A2; Danh_muc!A:A; 0))

Đọc là: tìm vị trí của mã ở ô A2 trong cột A của sheet danh mục (khớp chính xác – số 0), rồi lấy giá trị cùng dòng ở cột C.

Với Excel 365 hoặc Excel 2021, bạn có thể dùng XLOOKUP gọn hơn:

=XLOOKUP(A2; Danh_muc!A:A; Danh_muc!C:C; "Không có mã")

Lưu ý quan trọng: Excel 2010, 2013, 2016 và 2019 không có XLOOKUP. Nếu file sẽ gửi cho nhiều bên (chủ đầu tư, tư vấn giám sát dùng máy cũ), hãy dùng INDEX + MATCH để tránh lỗi #NAME?.

NETWORKDAYS và TODAY: tính số ngày chờ duyệt

TODAY trả về ngày hôm nay; NETWORKDAYS đếm số ngày làm việc giữa hai mốc, tự bỏ thứ Bảy, Chủ nhật và ngày lễ bạn khai báo.

Số ngày một hồ sơ vật tư đã chờ duyệt (tính theo ngày làm việc):

=NETWORKDAYS(B2; TODAY(); Ngay_le)

Trong đó B2 là ngày trình, Ngay_le là vùng liệt kê ngày nghỉ lễ của năm. Nếu dự án làm cả thứ Bảy, dùng NETWORKDAYS.INTL với tham số cuối tuần phù hợp.

Con số này có ích hơn số ngày lịch khi làm việc với tư vấn: "hồ sơ đã chờ 8 ngày làm việc" là căn cứ rõ ràng để đề nghị đẩy nhanh.

IFERROR và IF: hiện chữ "QUÁ HẠN" thay vì lỗi

IF kiểm tra điều kiện để trả về kết quả khác nhau; IFERROR thay thông báo lỗi bằng nội dung bạn chọn. Kết hợp hai hàm giúp bảng theo dõi dễ đọc cho cả người không rành Excel.

Hiện chữ "QUÁ HẠN" khi RFI còn mở mà đã qua hạn trả lời:

=IF(AND(J2="Mở"; H2<TODAY()); "QUÁ HẠN"; "")

Tránh lỗi #N/A khi tra cứu mã chưa có trong danh mục:

=IFERROR(INDEX(Danh_muc!C:C; MATCH(A2; Danh_muc!A:A; 0)); "Chưa có trong danh mục")

Bonus: Conditional Formatting tô đỏ dòng quá hạn

Công thức cho ra chữ "QUÁ HẠN" rồi, bước cuối là làm nó nổi bật:

1. Chọn vùng dữ liệu, ví dụ A2:K500.
2. Vào Home → Conditional Formatting → New Rule → Use a formula.
3. Nhập công thức =$K2="QUÁ HẠN" (cột K là cột kết quả của hàm IF).
4. Chọn định dạng nền đỏ nhạt, chữ đỏ đậm → OK.

Dấu $ trước chữ K giữ cố định cột, để cả dòng được tô chứ không chỉ một ô.

Ghép 5 hàm thành một RFI log hoàn chỉnh như thế nào?

Cách ghép đơn giản nhất là chia file làm ba lớp: danh mục, nhật ký và tổng hợp. Mỗi lớp chỉ dùng một vài hàm, nên khi có lỗi bạn biết ngay phải tìm ở đâu.

1. Sheet DANH_MUC: liệt kê hệ thống (PCCC, HVAC, Điện, CTN), người phụ trách, danh sách ngày lễ. Sheet này hầu như không có công thức, chỉ là nguồn cho danh sách chọn (Data Validation).
2. Sheet RFI_LOG: mỗi RFI một dòng. Các cột nhập tay gồm mã, ngày phát hành, hệ thống, nội dung, hạn trả lời, trạng thái. Các cột tự tính gồm tên người phụ trách (INDEX + MATCH theo hệ thống), số ngày chờ (NETWORKDAYS + TODAY) và cảnh báo (IF). Toàn bộ dòng được tô đỏ bằng Conditional Formatting khi quá hạn.
3. Sheet TONG_HOP: bảng nhỏ với mỗi hệ thống một hàng, các cột "Đang mở", "Quá hạn", "Đã đóng tuần này" – tất cả dùng COUNTIFS. Nếu log có thêm cột khối lượng hoặc chi phí ảnh hưởng, dùng SUMIFS để cộng.

Một vài nguyên tắc giúp file bền lâu khi nhiều người cùng nhập:

• Không trộn ô (merge cell) trong vùng dữ liệu, vì COUNTIFS và SUMIFS sẽ đếm sai.
• Nhập ngày đúng định dạng ngày, không gõ "15/9" dạng chữ. Có thể kiểm tra bằng cách đổi định dạng ô sang Number: ngày thật sẽ hiện thành một con số.
• Dùng danh sách chọn cho cột trạng thái ("Mở", "Đã trả lời", "Đã đóng", "Hủy") để công thức không bị lệch vì gõ sai chính tả.
• Khóa các cột công thức bằng Protect Sheet, chỉ mở khóa các ô nhập liệu.

Làm theo ba lớp như trên, một kỹ sư quen Excel mất khoảng một buổi để dựng xong file, và báo cáo tuần sau đó chỉ còn là việc mở sheet tổng hợp.

Bảng tổng hợp: hàm nào dùng khi nào?

Kết luận

Năm hàm trên đủ để biến một bảng nhập liệu thành công cụ theo dõi có cảnh báo. Hãy bắt đầu với COUNTIFS và IF cho RFI log – bạn sẽ thấy báo cáo tuần nhanh hơn ngay tuần đầu tiên. Nếu chưa rõ RFI log cần những cột nào, đọc thêm bài RFI là gì? Cách quản lý RFI dự án MEP không bị quá hạn. Còn nếu muốn dùng ngay một file đã viết sẵn toàn bộ công thức và Dashboard, xem sản phẩm gợi ý bên dưới.