Công thức hay dùng (Recipes)

Snippet kết hợp hàm UDF của DVDAddin với hàm có sẵn của Excel cho các tác vụ hay gặp. Danh mục đầy đủ 47 hàm xem tại Hàm UDF.

Các ví dụ dưới đây dùng dấu chấm phẩy ; để ngăn cách tham số (Excel chạy theo Regional format Việt Nam). Nếu máy bạn dùng định dạng English (US), hãy gõ dấu phẩy , thay cho ;.

Xử lý văn bản

Dọn sạch text dán từ nơi khác

=TRIM(CLEAN(dvdUnDiacriticsVi(A1)))

→ Bỏ dấu tiếng Việt + xóa ký tự non-printable + xóa khoảng trắng thừa. Hay dùng để lấy tên hạng mục ra đặt tên file hồ sơ.

Xem dvdUnDiacriticsVi.

Cứu văn bản gõ sai bảng mã

=dvdUniConvert(A1; "VNI")     → "Be6 to6ng"  thành  "Bê tông"
=dvdUniConvert(A1; "Telex")   → "Bee toong"  thành  "Bê tông"

Tách phần tử trong chuỗi mã hiệu

Mã dạng AG.11221-BT-M300:

=dvdTextSplitItem(A1; "-"; 2)     → "BT"
=dvdExtractElement(A1; 3; "-")    → "M300"

Khác nhau ở chỗ dvdTextSplitItem có tham số IgnoreEmpty để bỏ qua phần tử rỗng, còn dvdExtractElement giữ nguyên vị trí.

Đếm từ, đếm số lần xuất hiện

=dvdWordCount(A1)                   → số từ trong ô
=dvdCountOccurrences(A1; "D16")     → số lần "D16" xuất hiện trong chuỗi

Gộp tên công tác theo hạng mục

=dvdConcatIF(", "; C6:C40; B6:B40; "Móng")

→ "Bê tông lót M100, Bê tông móng M300, Cốt thép móng". Bỏ hai tham số cuối thì nối toàn bộ vùng. Cần nhiều điều kiện thì dùng dvdConcatIFS hoặc dvdJoinIFS.

Tính biểu thức viết trong dòng diễn giải

A1 = "Móng M1: 2*3.5*0.8":

=dvdEvaluate(A1)     → 5.6

→ Diễn giải khối lượng viết bằng tay vẫn ra được con số, không phải gõ lại công thức.

Cách hàm đọc chuỗi: có dấu : thì chỉ lấy phần sau dấu hai chấm, cắt bỏ mọi thứ từ dấu = trở đi, rồi bỏ hết chữ và chỉ giữ số cùng các toán tử + - * / ^ ( ) . , (chữ x được hiểu là dấu nhân, dấu phẩy được hiểu là dấu thập phân). Kết quả bằng 0 hoặc không tính được thì ô trả về chuỗi rỗng.

Format số điện thoại VN

=TEXT(VALUE(SUBSTITUTE(A1; "+84"; "0")); "0000-000-000")

→ +84901234567 thành 0901-234-567.

Đổi hoa/thường, tìm-thay theo regex

Hai việc này không có UDF — DVDAddin làm bằng lệnh ribbon, ghi thẳng vào ô nên không cần cột phụ:

• DVD Addin → Văn bản và Số → Đổi chữ: Hoa Đầu Mỗi Từ, HOA TOÀN BỘ, Hoa theo ngữ cảnh (AI) (Ctrl+Shift+S).
• DVD Addin → Văn bản và Số → Xử lý văn bản → Tìm / Thay hàng loạt… — ba chế độ khớp (văn bản thường / wildcard / regex), phạm vi từ vùng chọn tới mọi workbook đang mở, bảng Preview tick từng ô trước khi thay, và lưu được bộ tham số thành preset.

Ngày tháng và lịch

Đếm ngày làm việc trừ lễ Tết VN

Sheet Holidays cột A có danh sách ngày lễ:

=NETWORKDAYS(A1; B1; Holidays!$A$2:$A$30)

Ngày tới hạn báo cáo (ngày làm việc thứ 5 của tháng sau)

=WORKDAY(EOMONTH(TODAY(); 0); 5; Holidays!$A$2:$A$30)

Mốc Tết Nguyên đán cho bảng tiến độ

=dvdLunarToSolar(DATE(2026;1;1))     → 17/02/2026 (mùng 1 Tết Bính Ngọ)
=dvdSolarToLunar(A1)                 → "10/07/2026 AL"

→ Lấy mùng 1 Tết ra ngày dương rồi trừ/cộng để dựng cột ngày nghỉ cho tiến độ. Định dạng ô kiểu Date để dvdLunarToSolar hiển thị đúng.

Ngày tháng bằng chữ cho biên bản

=dvdLDate(A1; "Hà Nội"; 0)     → "Hà Nội, ngày ... tháng ... năm ..."
=dvdLTimeDate(B1; A1; 2)       → giờ + ngày, song ngữ (0 = VN, 1 = EN, 2 = song ngữ)

Tiền và số

Đọc số bằng chữ cho hóa đơn, phiếu giá

=dvdVnd(SUM(C2:C100))     → "Một trăm hai mươi lăm triệu đồng."
=dvdVnd(F42; 1)           → "Bằng chữ: Một triệu, hai trăm năm mươi nghìn đồng."
=dvdUsd(5000)             → "Five thousand dollars only"

→ Ô tổng cuối hợp đồng / hồ sơ thanh toán tự đọc thành chữ.

Định dạng tiền VND

Đây là Number Format, không phải hàm: Ctrl+1 → Custom → #,##0" đ". Dùng định dạng thay vì hàm để ô vẫn là số và vẫn cộng được.

Tỉ giá USD/VND

=DVDFx("USD"; "VND")                     → tỷ giá mới nhất
=DVDFx("USD"; "VND"; "2026-06-30")       → tỷ giá chốt kỳ thanh toán

→ Hàm chạy bất đồng bộ: lần đầu ô hiển thị #N/A waiting... rồi tự cập nhật. Kết quả được lưu đệm một giờ cho mỗi cặp tiền.

Làm tròn về bội số 1000

=MROUND(A1; 1000)     → 1.234.567 thành 1.235.000

Muốn bọc hàm ROUND vào hàng loạt công thức đã có sẵn trong bảng thì dùng lệnh Hàm làm tròn — thêm hoặc bỏ đồng loạt, không phải sửa từng ô.

Nội suy tra định mức theo cự ly

=dvdNoiSuy(5; 10; 120000; 150000; 7)     → 132000

→ Đơn giá vận chuyển tại cự ly 7 km, nội suy giữa mốc 5 km và 10 km.

Tra cứu và vùng dữ liệu

Lấy bản ghi mới nhất khi mã lặp nhiều lần

=dvdELookup(A6; $B$5:$B$300; $F$5:$F$300)

→ Khối lượng của lần khớp cuối cùng — đúng cho bảng nhật ký ghi tăng dần theo thời gian. Cần lấy theo ngày lớn nhất bất kể thứ tự dòng thì dùng công thức Excel:

=INDEX(History!B:B; MATCH(MAX(History!A:A); History!A:A; 0))

Lấy tất cả giá trị khớp, không chỉ giá trị đầu

=dvdMVLookup($H$3; $B$5:$B$500; 4; $J$4:$J$40)

→ Giá trị khớp đầu tiên hiện tại ô công thức, các giá trị còn lại ghi xuống J4:J40. So với VLOOKUP chỉ trả về kết quả đầu tiên.

Cần trả về nhiều cột cùng lúc thì dùng dvdMCLookup — ô công thức hiện Done, dữ liệu nằm ở vùng đích.

Dò tìm trên tất cả các sheet

=dvdLookupAllSheets(A6; 2; 6)

→ Tìm mã công tác A6 ở cột B của mọi sheet đang hiện, trả về giá trị cột F. Bỏ qua chính sheet chứa công thức và các sheet đang ẩn.

Dò tìm hai chiều theo tiêu đề hàng và cột

=dvdTableLookup("D16"; "Cấp bền B22.5"; $A$5:$H$30)

Dò ngược (từ tên ra mã)

=dvdXlookup("Đào đất"; $B$6:$B$500; $A$6:$A$500; "Không có"; "Lỗi"; 0)

→ Có sẵn giá trị thay thế cho trường hợp không tìm thấy và trường hợp lỗi, không phải bọc thêm IFERROR. Bản thuần Excel: =INDEX(A:A; MATCH("Đào đất"; B:B; 0)).

Tổng các ô đang hiển thị (sau filter)

=dvdSumVisible($F$6:$F$500)

→ So với SUM (tính cả dòng ẩn) và SUBTOTAL(9; ...) (chỉ bỏ dòng bị AutoFilter lọc, dòng ẩn tay vẫn tính). dvdSumVisible xét trạng thái ẩn của cả dòng lẫn cột chứa ô, nên bỏ qua cả ba kiểu ẩn dòng — AutoFilter, ẩn thủ công và dvdAutoHide — lẫn phần cột đang ẩn.

Tổng và đếm theo màu nền ô

=dvdSumIfColor($F$6:$F$500; $H$3)       → cộng các ô cùng màu với H3
=dvdCountIfColor($F$6:$F$500; $H$3)     → đếm các ô cùng màu với H3

Lọc bảng theo điều kiện của một cột

=dvdFilter2DArray($A$5:$F$500; 6; ">0"; TRUE)      → chỉ các dòng có khối lượng > 0
=dvdFilter2DArray($A$5:$F$500; 2; "BT*"; TRUE)     → các mã công tác bắt đầu bằng "BT"

→ Trả về mảng hai chiều: Excel 365/2021 tự tràn, bản cũ nhập bằng Ctrl+Shift+Enter.

Danh sách duy nhất

=dvdUnique($B$6:$B$500)                       → mảng một cột các giá trị duy nhất
=dvdUnique2DArray($A$5:$F$500; 2; TRUE)       → giữ dòng đầu tiên của mỗi mã, đủ các cột
=dvdUniqueV($B$6:$B$500; $H$7:$H$40)          → cho Excel 2016/2019 không có mảng động

Ẩn tự động các dòng khối lượng bằng 0

=dvdAutoHide($A$6:$A$500; $F$6:$F$500; "<>0"; $A$6:$A$500; "Ẩn dòng KL 0")

→ Giữ lại dòng có khối lượng khác 0, ẩn phần còn lại và đánh lại số thứ tự liên tục. Đặt công thức ở ô ngoài vùng Target, nếu không nó tự ẩn theo.

Top 10 hạng mục giá trị lớn nhất

=TAKE(SORT(DATA!A2:C1000; 3; -1); 10)

→ SORT và TAKE là hàm sẵn có của Excel 365/2021, không cần UDF.

Xây dựng

Trọng lượng thép

=dvdSteelWeight(16; 20)      → 369.72 kg (20 thanh D16 dài 11,7 m)
=dvdSteelWeight(8; 250)      → 250 kg (thép cuộn D8 nhập theo khối lượng)

→ Với D ≤ 8 hàm trả về đúng giá trị quantity vì thép cuộn nhập theo kg; từ D9 trở lên hàm hiểu quantity là số thanh và nhân với cây tiêu chuẩn 11,7 m.

Đếm thanh thép theo đường kính

=COUNTIF(D:D; "D16") + COUNTIF(D:D; "Ø16")

→ Đếm cả hai cách viết thông dụng trong bảng thống kê.

Tổng khối lượng theo nhóm cấu kiện

Bảng có cột A (mã cấu kiện), B (khối lượng):

=SUMIF(A:A; "C-*"; B:B)     → tất cả cột
=SUMIF(A:A; "D-*"; B:B)     → tất cả dầm
=SUMIF(A:A; "S-*"; B:B)     → tất cả sàn

Chiều dài neo/nối cốt thép

Hệ số neo lấy theo tiêu chuẩn áp dụng và điều kiện làm việc của thanh thép; ví dụ dưới dùng hệ số 40D:

=ROUND(40 * VALUE(MID(A1; 2; 2)) / 1000; 2)

→ A1 = "D16" → 40 × 16 / 1000 = 0,64 m. Thay 40 bằng hệ số của bạn.

Ước lượng số cây thép cần mua

Bảng có cột B (chiều dài thanh, m), C (số lượng), hao phí 5%, cây thép 11,7 m:

=CEILING(SUMPRODUCT(B6:B500; C6:C500) * 1.05 / 11.7; 1)

→ Đây chỉ là ước lượng theo hao phí phẳng. Muốn con số thật thì dùng Cắt tối ưu — thuật toán xếp thanh thực tế, cho ra sơ đồ cắt và lượng thép thừa từng cây. Vật tư khác có Cắt thanh 1D và Cắt tấm 2D.

Thuyết minh khối lượng tự động

=dvdExplain(F6; ; 0; 2)          → "2.40=3*0.8"
=dvdExplainE(F6:F20; ; 0; 3)     → "5+3.2+7.8=16.000"

→ dvdExplain viết kết quả = biểu thức, dvdExplainE viết biểu thức = kết quả (hợp với bảng thuyết minh hồ sơ hoàn công). Bật Wrap Text cho ô công thức khi diễn giải nhiều ô.

Mã QR dán tem cấu kiện

=dvdQR(A6)
=dvdQR("https://hoso.congtrinh.vn/bb/" & A6)

→ Ô công thức hiển thị rỗng, sản phẩm thật là hình QR chèn vào ô. Bộ ký tự mặc định là UTF-8 nên nội dung có dấu tiếng Việt vẫn quét ra đúng; muốn dùng bộ ký tự Nhật thì truyền tham số thứ hai "Shift_JIS" (khi đó chuỗi bị bỏ dấu).

Ảnh hiện trường cho phụ lục nghiệm thu

=dvdPic("Anh\" & A6; TRUE; 6)

→ Đường dẫn tương đối tính từ thư mục chứa file Excel; ảnh tự co vừa ô, chừa 6 px viền. Khi xuất PDF hàng loạt hãy dùng lệnh In hàng loạt để ảnh được cập nhật trước khi xuất.

Tô màu mặt bằng phân khu theo tiến độ

=dvdColorShapes($H$6:$I$40; "Mat bang"; 0.2)

→ H:I là hai cột tên hình và mã màu RGB; tham số cuối là độ trong suốt 0..1.

Tự động hóa bảng tính

Cột STT tự đánh khi thêm hàng

Cell A2 trở xuống:

=IF(B2=""; ""; COUNTA($B$2:B2))

→ STT chỉ chạy khi cột B (tên hạng mục) có dữ liệu.

Ô đánh dấu Đạt / Không đạt

=dvdSymbol(IF(D6="Đạt"; 2; 3))

→ ☑ khi Đạt, ☒ khi không đạt. 1 = hộp trống.

Highlight mã sai bằng Conditional Format

CF rule: =AND(B2<>""; ISERROR(VLOOKUP(B2; BangGia!A:A; 1; FALSE)))

→ Tô đỏ ô B nếu mã công tác KHÔNG có trong bảng giá.

Ô email và số điện thoại bấm được

=HYPERLINK("mailto:" & A1; A1)
=HYPERLINK("tel:" & A1; A1)

Cột song ngữ cho hồ sơ gửi tư vấn nước ngoài

=dvdTranslate(B6; "vi"; "en")

→ Cần kết nối mạng. Dùng cho từng ô riêng lẻ; kéo công thức cho hàng trăm dòng cùng lúc sẽ chậm và dễ bị chặn — bảng lớn thì dùng lệnh Dịch ngôn ngữ.

Công thức nhiều bước

Lần xuất hiện thứ n

Tìm lần xuất hiện thứ 3 của "Hà Nội" trong cột A:

=INDEX(A:A; SMALL(IF(A:A="Hà Nội"; ROW(A:A)); 3))

(Nhập bằng Ctrl+Shift+Enter trên Excel không có mảng động.)

Tiêu đề chart tự cập nhật theo tháng

=CONCATENATE("Doanh thu tháng "; TEXT(TODAY(); "MM/yyyy"))

AI trong ô công thức

Nhờ AI giải thích một công thức lạ

=dvdAIExplain(F6; TRUE)

→ Cần API key Gemini khai trong Tùy chọn → mục Trợ lý AI. Hàm chạy bất đồng bộ. Muốn hộp thoại tương tác thay vì công thức thì dùng Giải thích CT (Coach), còn khi ô đang báo #N/A #VALUE! thì dùng Sửa lỗi công thức.

Liên quan

• Hàm UDF — toàn bộ 47 hàm, chia theo bốn nhóm.
• Tham chiếu lệnh đầy đủ — 186 lệnh ribbon theo đúng vị trí trên tab.
• Best Practices — nguyên tắc chung khi dùng add-in.
• Performance — tối ưu workbook nhiều công thức volatile.