UDF Range + Lookup + Spill

Hàm tra cứu nâng cao + xử lý range — VLOOKUP trả nhiều kết quả, XLOOKUP 2D, filter spill động, sum/count chỉ với cell visible.

Range Functions

DvdSumVisible

Tổng các cell visible trong range — bỏ qua cell ẩn (do AutoFilter hoặc Hide row):

=DvdSumVisible(A2:A100)        → tổng các cell A2:A100 đang HIỆN
=SUM(A2:A100)                  → tổng TẤT CẢ (kể cả ẩn)
=SUBTOTAL(9, A2:A100)          → tổng visible (built-in)

Khác SUBTOTAL(9, ...) built-in:

• SUBTOTAL chỉ tính cell visible-after-filter. Cell ẩn do Hide row manual vẫn được tính.
• DvdSumVisible bỏ qua CẢ HAI (filter hide + manual hide).

DvdCountVisible

Đếm cell visible:

=DvdCountVisible(A2:A100)             → đếm cell visible (kể cả trống)
=DvdCountVisible(A2:A100, "non-empty") → đếm visible AND không trống
=DvdCountVisible(A2:A100, "numeric")  → đếm visible AND là số

DvdLastNonEmpty / DvdFirstNonEmpty

Lấy cell đầu / cuối không trống trong range:

=DvdLastNonEmpty(A:A)         → cell cuối cùng có data trong cột A
=DvdFirstNonEmpty(B2:B100)    → cell đầu tiên có data trong B2:B100

Use case: tìm row cuối có data để biết bảng dài bao nhiêu.

DvdMaxByDate / DvdMinByDate / DvdSumByDate

Lấy giá trị max/min/sum tại ngày max trong 2 cột song song:

=DvdMaxByDate(B2:B100, A2:A100)        → max của cột B tại hàng có ngày max trong cột A

Nếu bảng A = ngày, B = doanh thu → output là doanh thu vào ngày mới nhất.

Phù hợp dashboard real-time.

Lookup Functions

DvdVLookupAll

VLOOKUP trả TẤT CẢ match (không chỉ first):

=DvdVLookupAll("Hà Nội", A2:C100, 3)   → spill array: tất cả giá trị cột 3 cho hàng "Hà Nội"

Built-in VLOOKUP chỉ trả first match. Hàm này spill tất cả → có thể dùng SUM, AVERAGE lên kết quả.

Có thể chỉ định:

=DvdVLookupAll("Hà Nội", A2:C100, 3, ";")  → trả 1 cell chuỗi "100; 200; 300"

DvdXLookup2D

XLOOKUP 2 chiều — tra cứu intersection của row key + column key:

=DvdXLookup2D("Q1", "Doanh thu", A1:F10)  → cell tại hàng "Q1" và cột "Doanh thu"

Bảng:

            Doanh thu  Chi phí  Lợi nhuận
   Q1       1000       600      400
   Q2       1200       700      500
   Q3       1500       900      600

=DvdXLookup2D("Q2", "Chi phí", A1:D4) → 700.

Khác INDEX/MATCH truyền thống: 1 hàm thay vì 2, dễ đọc.

DvdRangeByName

Get range theo Named Range:

=SUM(DvdRangeByName("DanhSachHangMuc"))   → tổng cột Named Range "DanhSachHangMuc"
=ROWS(DvdRangeByName("Pricing"))           → số hàng của Named Range "Pricing"

Use case: refactor formula khi đổi cấu trúc Named Range mà không phải sửa nhiều cell.

DvdReverseLookup

Tra cứu ngược — đã có VALUE, tìm KEY:

=DvdReverseLookup(500, A2:B100)   → tìm 500 trong cột B (= cột 2), trả cell tương ứng cột A

Tương đương INDEX(A:A, MATCH(500, B:B, 0)) nhưng compact.

Spill Functions (mảng động)

DvdSpillFilter

Filter range theo condition → spill kết quả:

=DvdSpillFilter(A2:C100, "> 1000", 3)

Filter các hàng có cột C (cột thứ 3) > 1000, output toàn bộ hàng.

Tham số condition: chuỗi như "> 1000", "<= 50", "= ''Hà Nội''", "contains 'Đặng'", "regex '^DA-\d+'".

Khác FILTER built-in:

• Hỗ trợ string predicate (built-in cần boolean array).
• Hỗ trợ regex và "contains".

DvdSpillUnique

Unique values — không trùng:

=DvdSpillUnique(A2:A100)                  → list các giá trị unique trong A2:A100
=DvdSpillUnique(A2:C100, "row")           → unique HÀNG (toàn bộ row distinct)
=DvdSpillUnique(A2:A100, "case-insensitive") → "Hà Nội" và "HÀ NỘI" coi là 1

Built-in UNIQUE chỉ hỗ trợ basic mode. Hàm này flexible hơn.

DvdSpillSort

Sort range:

=DvdSpillSort(A2:C100, 3)            → sort theo cột 3 (tăng dần)
=DvdSpillSort(A2:C100, 3, FALSE)     → sort cột 3 giảm dần
=DvdSpillSort(A2:C100, "C", FALSE)   → sort cột "C" giảm dần
=DvdSpillSort(A2:C100, {3, 1}, {FALSE, TRUE})  → multi-column sort

Multi-column: cột 3 giảm dần, tie-break bằng cột 1 tăng dần.

DvdSpillRows / DvdSpillColumns

Spill số hàng / cột của range:

=DvdSpillRows(A1:C10)    → array {1,2,3,...,10}
=DvdSpillColumns(A1:C10) → array {1,2,3}

Use case: ROW/COLUMN cho cả range thay vì 1 cell.

License gating

Tất cả hàm Range/Lookup/Spill đều free — chạy local.

Performance tips

Spill functions với range lớn (> 100k cells) có thể chậm — Excel tính lại mỗi khi cell phụ thuộc thay đổi. Cách tối ưu:

• Convert spill → static values khi không cần dynamic (Paste Special → Values).
• Dùng DvdSpillSort thay vì sort thủ công nhiều cột.
• Tránh nested spill (vd: =DvdSpillFilter(DvdSpillSort(...), ...) → tính 2 lần).

Liên quan

• UDF Text — DvdRegex, DvdRemoveDiacritics.
• UDF AI + Network — DvdAskGemini, DvdHttpGet.
• QS Tools — Truy vết VLOOKUP, Ô phụ thuộc.