Hàm Excel nâng cao: XLOOKUP, INDEX/MATCH và mảng động

Bài này dành cho người đã dùng SUMIFS và VLOOKUP thành thạo, muốn bỏ hẳn thao tác tay còn sót lại. Sáu nhóm dưới đây xếp theo mức độ đổi cách làm việc, nhiều tới ít.

1. XLOOKUP thay VLOOKUP

=XLOOKUP(A2; DanhMuc[Ma]; DanhMuc[Ten]; "Không có"; 0)

Hơn VLOOKUP ở bốn điểm: tra được sang trái, không phải đếm cột, có sẵn giá trị mặc định khi không tìm thấy, và mặc định là tìm chính xác (VLOOKUP mặc định tìm gần đúng — nguồn gốc của phần lớn lỗi âm thầm).

Trả về nhiều cột cùng lúc bằng cách đưa cả vùng vào tham số thứ ba: =XLOOKUP(A2; DanhMuc[Ma]; DanhMuc[[Ten]:[DonVi]]). Kết quả tự tràn sang các ô bên phải.

Điều kiện: Microsoft 365 hoặc Excel 2021. File mở trên bản cũ hơn sẽ báo #NAME?.

2. INDEX + MATCH khi không có XLOOKUP

=INDEX(Bang[Ten]; MATCH(A2; Bang[Ma]; 0))

Chạy trên mọi phiên bản Excel, tra được hai chiều, và không vỡ khi chèn thêm cột. Kết hợp hai MATCH để tra theo cả dòng lẫn cột:

=INDEX($B$2:$G$50; MATCH($A2;$A$2:$A$50;0); MATCH(B$1;$B$1:$G$1;0))

Đây là công thức đáng thuộc lòng — nó thay thế được toàn bộ bảng tra chéo mà người ta hay làm bằng tay.

3. Mảng động — FILTER, SORT, UNIQUE

Nhóm hàm này đổi cách nghĩ nhiều nhất: một công thức trả về cả một bảng, tự tràn ra các ô bên dưới và bên phải, tự co giãn khi dữ liệu đổi.

• =UNIQUE(DonHang[KhachHang]) — danh sách khách không trùng, thay cho thao tác Remove Duplicates thủ công.
• =FILTER(DonHang; DonHang[KhuVuc]="Miền Bắc"; "Không có dòng nào") — trích bảng con theo điều kiện.
• =SORT(FILTER(...); 3; -1) — lọc rồi xếp giảm dần theo cột 3.

Ghép ba hàm này lại là có một bảng phụ tự cập nhật, không cần Pivot Table và không cần bấm Refresh. Rất hợp để làm phần "top 10" trên dashboard.

Lưu ý: vùng tràn phải trống. Có dữ liệu chắn đường thì Excel báo #SPILL! — xoá thứ chắn ở đó là xong.

4. LET — đặt tên cho phần trung gian

Công thức dài hay lặp lại cùng một đoạn tính ba bốn lần, vừa chậm vừa khó đọc. LET tính một lần rồi đặt tên:

=LET(ds; FILTER(DonHang; DonHang[Thang]=$B$1); IF(ROWS(ds)=0; "Chưa có đơn"; SUM(INDEX(ds;;4))))

Đọc lại sau sáu tháng vẫn hiểu, và chạy nhanh hơn vì phần lọc chỉ tính một lần.

5. SUMPRODUCT cho điều kiện mà SUMIFS chịu

SUMIFS chỉ so sánh trên cột có sẵn. Khi điều kiện phải tính ra mới so sánh được — ví dụ cộng doanh thu của các dòng có số lượng nhân đơn giá vượt một ngưỡng — thì dùng SUMPRODUCT:

=SUMPRODUCT((DonHang[SL]*DonHang[Gia]>10000000)*DonHang[ThanhTien])

Mỗi điều kiện trong ngoặc cho ra một dãy đúng/sai, nhân với nhau thành phép AND. Mạnh nhưng chậm trên bảng lớn — vài chục nghìn dòng trở lên thì nên tính sẵn một cột phụ rồi dùng SUMIFS.

6. Power Query — khi vấn đề không còn là hàm

Nếu công việc mỗi tháng là: mở năm file, xoá vài dòng đầu, đổi tên cột, nối lại thành một bảng — thì không hàm nào cứu được, vì đó là việc chuẩn bị dữ liệu, không phải việc tính.

Power Query có sẵn trong Excel (Data → Get Data). Ghi lại các bước làm sạch một lần, tháng sau thay file nguồn rồi bấm Refresh. Nó cũng gộp được cả một thư mục file cùng cấu trúc chỉ bằng một thao tác.

Học Power Query trong Excel không phí: đây đúng là công cụ chuẩn bị dữ liệu của Power BI, học một lần dùng được cả hai. Xem thêm các bài về Power Query.

Ngưỡng cuối cùng

Có ba dấu hiệu cho biết đã hết đất của Excel: file nặng tới mức mở mất hàng phút, nhiều người cùng sửa sinh ra nhiều phiên bản, và cần phân quyền để mỗi người chỉ xem phần của mình. Ba thứ này Excel không giải được bằng hàm.

Đọc tiếp: khi nào nên chuyển từ Excel sang Power BI, hoặc xem mẫu báo cáo thật để hình dung kết quả trước khi quyết.