Hàm vùng và tra cứu

Nhóm này gồm 14 hàm làm việc với vùng dữ liệu: dò tìm nâng cao (một chiều, hai chiều, nhiều kết quả, tìm trên mọi trang tính), lọc và lấy giá trị duy nhất từ mảng, tính tổng/đếm theo màu nền hoặc theo dòng đang hiển thị, và tự động ẩn dòng theo điều kiện.

Các ví dụ dưới đây dùng dấu chấm phẩy ; để ngăn cách tham số (Excel chạy trên 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 ;.

Bảng tra nhanh:

Hàm	Công dụng
dvdXlookup	Dò tìm mở rộng, có xử lý lỗi và chế độ tìm xấp xỉ.
dvdELookup	Lấy kết quả của lần khớp cuối cùng trong vùng.
dvdMCLookup	Trả về tất cả các dòng khớp, gồm nhiều cột, ra một vùng đích.
dvdMVLookup	Trả về tất cả giá trị khớp từ một cột lệch so với cột dò.
dvdTableLookup	Dò tìm hai chiều theo tiêu đề hàng và tiêu đề cột.
dvdLookupAllSheets	Dò tìm một giá trị trên tất cả các trang tính của workbook.
dvdUnique	Trả về mảng các giá trị duy nhất của một vùng.
dvdUniqueV	Trả về giá trị duy nhất, ghi phần còn lại ra vùng đích.
dvdUnique2DArray	Giữ lại các dòng duy nhất của mảng theo một cột khóa.
dvdFilter2DArray	Lọc mảng hai chiều theo điều kiện của một cột.
dvdSumVisible	Cộng các ô đang hiển thị, bỏ qua dòng và cột bị ẩn.
dvdSumIfColor	Cộng các ô có màu nền giống ô mẫu.
dvdCountIfColor	Đếm các ô có màu nền giống ô mẫu.
dvdAutoHide	Tự động ẩn dòng không thỏa điều kiện và đánh lại số thứ tự.

dvdXlookup

Dò tìm một giá trị trong vùng dò và trả về giá trị tương ứng ở vùng kết quả, có tùy chọn giá trị thay thế khi không tìm thấy hoặc khi lỗi.

Cú pháp:

=dvdXlookup(LookupValue; LookupArray; ReturnArray; [IfNotFound]; [IfError]; [MatchMode])

Tham số	Bắt buộc	Mô tả
LookupValue	Có	Giá trị cần tìm kiếm trong LookupArray.
LookupArray	Có	Phạm vi dữ liệu nơi thực hiện tìm kiếm.
ReturnArray	Có	Phạm vi trả kết quả.
IfNotFound	Không	Giá trị trả về nếu không tìm thấy; bỏ trống thì trả #N/A.
IfError	Không	Giá trị trả về nếu có lỗi; bỏ trống thì trả #N/A.
MatchMode	Không	0 = chính xác (mặc định), -1 hoặc 1 = xấp xỉ, tương tự XLOOKUP.

Ví dụ:

=dvdXlookup(A6; 'Đơn giá'!$A$5:$A$500; 'Đơn giá'!$E$5:$E$500; "Chưa có đơn giá"; "Lỗi dữ liệu"; 0)
// → 1.850.000 (đơn giá của mã công tác trong ô A6)

=dvdXlookup(D6; $H$5:$H$20; $I$5:$I$20; ""; ""; 1)
// → hệ số của khoảng cự ly vận chuyển gần nhất không vượt quá D6

Lưu ý

• Hàm luôn dò khớp chính xác trước; chỉ khi không tìm thấy và MatchMode khác 0 mới chuyển sang xấp xỉ.
• Chế độ xấp xỉ chỉ áp dụng cho dữ liệu số: -1 lấy giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tìm, 1 lấy giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tìm.

dvdELookup

Dò từ dưới lên và trả về kết quả của lần khớp cuối cùng — dùng khi một mã công tác xuất hiện nhiều lần và bạn cần bản ghi mới nhất.

Cú pháp:

=dvdELookup(LookupValue; LookupRange; ResultRange)

Tham số	Bắt buộc	Mô tả
LookupValue	Có	Giá trị cần tìm kiếm.
LookupRange	Có	Phạm vi chứa giá trị cần tìm.
ResultRange	Có	Phạm vi chứa kết quả.

Ví dụ:

=dvdELookup(A6; $B$5:$B$300; $F$5:$F$300)
// → khối lượng của lần nghiệm thu gần nhất cho mã công tác A6

Lưu ý

• Không tìm thấy thì hàm trả về chuỗi Không tìm thấy kết quả.
• Hai vùng nên có cùng số dòng vì kết quả được lấy theo vị trí tương ứng.

dvdMCLookup

Trả về tất cả các dòng khớp với giá trị tìm kiếm, kèm đầy đủ các cột, ghi ra một vùng đích trên trang tính.

Cú pháp:

=dvdMCLookup(ReturnCols; LookupColIndex; LookupValue; outputRange)

Tham số	Bắt buộc	Mô tả
ReturnCols	Có	Các cột chứa giá trị trả về.
LookupColIndex	Có	Chỉ số cột dùng để dò tìm, tính từ 1 trong vùng ReturnCols.
LookupValue	Có	Giá trị cần tìm.
outputRange	Có	Vùng đích để hiển thị kết quả.

Ví dụ:

=dvdMCLookup($A$5:$F$500; 2; $H$3; $H$6:$M$40)
// → "Done", đồng thời toàn bộ các dòng có mã hạng mục ở H3 được ghi vào H6:M40

Lưu ý

• Ô chứa công thức chỉ hiển thị Done; dữ liệu nằm ở outputRange, được xóa nội dung cũ trước khi ghi — hãy chừa vùng trống đủ rộng.
• Hàm là volatile: Excel tính lại mỗi khi bảng tính thay đổi.
• Không có dòng nào khớp thì hàm trả #N/A.

dvdMVLookup

Trả về mọi giá trị khớp của một cột lệch so với cột dò: kết quả đầu tiên hiện tại ô công thức, các kết quả còn lại được ghi xuống vùng đích.

Cú pháp:

=dvdMVLookup(LookupValue; LookupCol; OffsetCol; outputRange)

Tham số	Bắt buộc	Mô tả
LookupValue	Có	Giá trị cần tìm.
LookupCol	Có	Cột chứa giá trị cần tìm.
OffsetCol	Có	Số cột dịch sang phải so với LookupCol để lấy giá trị trả về.
outputRange	Có	Vùng đích chứa các giá trị thứ 2 trở đi (dành cho Excel 2019/2016 không có mảng động).

Ví dụ:

=dvdMVLookup($H$3; $B$5:$B$500; 4; $J$4:$J$40)
// → khối lượng đầu tiên của mã công tác H3; các khối lượng còn lại ghi vào J4:J40

Lưu ý

• Vùng đích được xóa nội dung cũ trước khi ghi; hãy chọn vùng trống nằm ngoài bảng dữ liệu.
• Hàm là volatile: Excel tính lại mỗi khi bảng tính thay đổi.

dvdTableLookup

Dò tìm hai chiều: lấy giá trị nằm ở giao của dòng và cột được xác định bởi tiêu đề.

Cú pháp:

=dvdTableLookup(LookupRowValue; LookupColumnValue; TableRange)

Tham số	Bắt buộc	Mô tả
LookupRowValue	Có	Giá trị cần tìm trong cột đầu tiên của bảng.
LookupColumnValue	Có	Giá trị cần tìm trong hàng đầu tiên của bảng.
TableRange	Có	Bảng dữ liệu, bao gồm cả tiêu đề hàng và tiêu đề cột.

Ví dụ:

=dvdTableLookup("D16"; "Cấp bền B22.5"; $A$5:$H$30)
// → 1.284 (tra định mức theo đường kính thép và cấp bền bê tông)

Lưu ý

• Không tìm thấy dòng hoặc cột tương ứng thì hàm trả chuỗi Không tìm thấy kết quả.

dvdLookupAllSheets

Dò tìm một giá trị trên tất cả các trang tính đang hiển thị của workbook và trả về giá trị ở cột chỉ định.

Cú pháp:

=dvdLookupAllSheets(lookupValue; lookup_column; result_column)

Tham số	Bắt buộc	Mô tả
lookupValue	Có	Giá trị cần tìm kiếm.
lookup_column	Có	Số thứ tự cột chứa giá trị cần tìm (cột A là 1).
result_column	Có	Số thứ tự cột chứa giá trị trả về.

Ví dụ:

=dvdLookupAllSheets(A6; 2; 6)
// → khối lượng ở cột F của dòng có mã công tác A6, tìm trên mọi sheet khối lượng

Lưu ý

• Hàm bỏ qua chính trang tính chứa công thức và các trang tính đang ẩn; không tìm thấy thì trả #N/A.
• Cột được tính theo số thứ tự cột của trang tính, không phải theo vị trí trong một vùng.

dvdUnique

Trả về mảng một cột chứa các giá trị duy nhất của một vùng.

Cú pháp:

=dvdUnique(rng)

Tham số	Bắt buộc	Mô tả
rng	Có	Phạm vi dữ liệu đầu vào.

Ví dụ:

=dvdUnique($B$6:$B$500)
// → danh sách hạng mục không trùng: Móng, Cột, Dầm, Sàn

Lưu ý

• Hàm trả về mảng: Excel 365/2021 tự tràn (spill); Excel 2019 trở về trước cần chọn sẵn vùng rồi nhấn Ctrl+Shift+Enter, hoặc dùng dvdUniqueV.
• Giá trị được so sánh không phân biệt chữ hoa và chữ thường.

dvdUniqueV

Trả về giá trị duy nhất đầu tiên tại ô công thức và ghi các giá trị duy nhất còn lại xuống vùng đích — dành cho phiên bản Excel không hỗ trợ mảng động.

Cú pháp:

=dvdUniqueV(inputRange; outputRange)

Tham số	Bắt buộc	Mô tả
inputRange	Có	Dải ô chứa các giá trị cần tìm giá trị duy nhất.
outputRange	Có	Dải ô để hiển thị các giá trị duy nhất còn lại (dành cho Excel 2019/2016).

Ví dụ:

=dvdUniqueV($B$6:$B$500; $H$7:$H$40)
// → "Móng" tại ô công thức; các hạng mục còn lại ghi vào H7:H40

Lưu ý

• Vùng đích được xóa nội dung cũ trước khi ghi.
• Hàm là volatile: Excel tính lại mỗi khi bảng tính thay đổi.
• Các ô trống trong vùng nguồn được bỏ qua.

dvdUnique2DArray

Giữ lại các dòng duy nhất của một mảng hai chiều dựa trên một cột làm khóa.

Cú pháp:

=dvdUnique2DArray(sArray; colIndex; HasTitle)

Tham số	Bắt buộc	Mô tả
sArray	Có	Mảng hai chiều đầu vào.
colIndex	Có	Chỉ số cột làm khóa duy nhất, tính từ 1.
HasTitle	Có	TRUE nếu vùng dữ liệu có dòng tiêu đề.

Ví dụ:

=dvdUnique2DArray($A$5:$F$500; 2; TRUE)
// → bảng nhà thầu phụ, mỗi mã chỉ giữ lại dòng xuất hiện đầu tiên

Lưu ý

• Hàm trả về mảng hai chiều: Excel 365/2021 tự tràn; phiên bản cũ cần nhập dạng công thức mảng bằng Ctrl+Shift+Enter.
• HasTitle bằng TRUE thì dòng tiêu đề luôn được giữ nguyên ở đầu kết quả.

dvdFilter2DArray

Lọc một mảng hai chiều theo điều kiện áp dụng cho một cột và trả về các dòng thỏa điều kiện.

Cú pháp:

=dvdFilter2DArray(DataArray; ColumnIndex; Criteria; HasTitle)

Tham số	Bắt buộc	Mô tả
DataArray	Có	Mảng hai chiều chứa dữ liệu cần lọc.
ColumnIndex	Có	Chỉ số cột áp dụng điều kiện lọc, tính từ 1.
Criteria	Có	Điều kiện lọc, ví dụ ">5", "abc", "abc*".
HasTitle	Có	TRUE nếu vùng dữ liệu có dòng tiêu đề.

Ví dụ:

=dvdFilter2DArray($A$5:$F$500; 6; ">0"; TRUE)
// → bảng chỉ còn các công tác có khối lượng lớn hơn 0

=dvdFilter2DArray($A$5:$F$500; 2; "BT*"; TRUE)
// → các dòng có mã công tác bắt đầu bằng "BT"

Lưu ý

• Hàm trả về mảng hai chiều: Excel 365/2021 tự tràn; phiên bản cũ cần nhập dạng công thức mảng bằng Ctrl+Shift+Enter.
• Điều kiện chấp nhận toán tử so sánh (">5", ">=2", "<>0") và ký tự đại diện (*, ?).

dvdSumVisible

Cộng giá trị của các ô đang hiển thị, bỏ qua ô nằm trong dòng hoặc cột bị ẩn.

Cú pháp:

=dvdSumVisible(SumRng)

Tham số	Bắt buộc	Mô tả
SumRng	Có	Vùng tính tổng giá trị của các ô được hiển thị.

Ví dụ:

=dvdSumVisible($F$6:$F$500)
// → tổng thành tiền của các dòng còn hiển thị sau khi lọc hạng mục

Lưu ý

• Hàm là volatile: Excel tính lại mỗi khi bảng tính thay đổi, nên tránh dùng hàng loạt trên bảng rất lớn.
• Ô ẩn do lọc AutoFilter, do ẩn thủ công hay do dvdAutoHide đều được bỏ qua.

dvdSumIfColor

Cộng giá trị của các ô có màu nền giống với màu nền của một ô mẫu.

Cú pháp:

=dvdSumIfColor(rngSum; rngCellColor)

Tham số	Bắt buộc	Mô tả
rngSum	Có	Vùng tính tổng giá trị.
rngCellColor	Có	Ô mang màu nền dùng làm điều kiện.

Ví dụ:

=dvdSumIfColor($F$6:$F$500; $H$3)
// → tổng khối lượng của các dòng được tô cùng màu với ô H3 (ví dụ màu đánh dấu phát sinh)

Lưu ý

• Đổi màu nền của một ô không làm Excel tính lại; sau khi tô màu hãy nhấn Ctrl+Alt+F9 để cập nhật kết quả.
• Hàm so sánh theo màu nền tô trực tiếp, không nhận diện màu do định dạng có điều kiện tạo ra.

dvdCountIfColor

Đếm số ô trong vùng có màu nền giống với màu nền của một ô mẫu.

Cú pháp:

=dvdCountIfColor(RangeToCount; ReferenceCell)

Tham số	Bắt buộc	Mô tả
RangeToCount	Có	Phạm vi các ô cần đếm.
ReferenceCell	Có	Ô chứa màu nền dùng làm tham chiếu.

Ví dụ:

=dvdCountIfColor($B$6:$B$500; $H$4)
// → 12 (số hạng mục được tô màu cảnh báo chậm tiến độ)

Lưu ý

• Đổi màu nền của một ô không làm Excel tính lại; sau khi tô màu hãy nhấn Ctrl+Alt+F9 để cập nhật kết quả.
• Hàm so sánh theo màu nền tô trực tiếp, không nhận diện màu do định dạng có điều kiện tạo ra.

dvdAutoHide

Ẩn tự động các dòng không thỏa điều kiện và đánh lại số thứ tự cho những dòng còn hiển thị.

Cú pháp:

=dvdAutoHide(Target; Cells; Conditions; [OrdersRange]; [Title])

Tham số	Bắt buộc	Mô tả
Target	Có	Vùng các dòng thuộc phạm vi xử lý ẩn/hiện.
Cells	Có	Vùng chứa giá trị để so với điều kiện.
Conditions	Có	Điều kiện, ví dụ "0", ">=2", "<>0"; khai báo được nhiều điều kiện bằng một vùng.
OrdersRange	Không	Vùng đánh số thứ tự lại sau khi ẩn.
Title	Không	Chuỗi hiển thị trong ô công thức, mặc định DVDAutoHide.

Ví dụ:

=dvdAutoHide($A$6:$A$500; $F$6:$F$500; "<>0"; $A$6:$A$500; "Ẩn dòng khối lượng 0")
// → giữ lại các dòng có khối lượng khác 0, ẩn phần còn lại và đánh số thứ tự liên tục

Lưu ý

• Dòng nào có ít nhất một ô trong Cells thỏa tất cả điều kiện thì được giữ hiển thị; các dòng còn lại bị ẩn.
• Việc ẩn dòng chỉ chạy sau khi Excel tính xong, nên kết quả có thể xuất hiện chậm một nhịp so với lúc gõ công thức.
• Hàm là volatile: Excel tính lại mỗi khi bảng tính thay đổi.
• Đặt công thức ở ô nằm ngoài vùng Target để công thức không bị ẩn theo.