Excel là công cụ không thể thiếu đối với dân văn phòng, kế toán, tài chính, hay bất kỳ ai thường xuyên làm việc với dữ liệu. Ngoài những hàm cơ bản như SUM, AVERAGE, IF, Excel còn sở hữu một kho tàng các hàm nâng cao giúp bạn tiết kiệm thời gian, xử lý dữ liệu chính xác và chuyên nghiệp hơn. Bài viết này sẽ tổng hợp những hàm nâng cao Excel thông dụng và quan trọng nhất, kèm ví dụ dễ hiểu và cách sử dụng chi tiết từng bước để bạn áp dụng ngay.
Hàm INDEX + MATCH – Thay thế VLOOKUP mạnh mẽ hơn
Công dụng: Truy xuất dữ liệu chính xác và linh hoạt hơn VLOOKUP, không bị giới hạn chỉ tìm kiếm từ trái sang phải như VLOOKUP.
Cú pháp:
=INDEX(array, MATCH(lookup_value, lookup_array, 0))
Ví dụ thực hành:
Giả sử bạn có bảng dữ liệu:
| Mã SP | Tên SP | Giá |
| SP01 | Bút bi | 5.000 |
| SP02 | Vở kẻ ngang | 10.000 |
Bạn muốn tìm giá của SP02:
=INDEX(C2:C3, MATCH(“SP02”, A2:A3, 0)) → kết quả: 10.000
Hàm INDIRECT – Tham chiếu động
Công dụng: Tạo tham chiếu đến một ô hoặc vùng dữ liệu bằng văn bản, rất hữu ích khi bạn muốn trích dữ liệu từ các sheet khác nhau theo tên động.
Cách dùng:
=INDIRECT(“A”&1)
→ Trả về nội dung ô A1.
Ví dụ nâng cao:
Nếu ô A1 chứa “Sheet2”, công thức:
=INDIRECT(“‘”&A1&”‘!B2”)
→ Trích giá trị từ ô B2 của sheet có tên Sheet2.
Hàm OFFSET – Dịch chuyển vùng dữ liệu
Công dụng: Trích xuất dữ liệu động, tạo phạm vi thay đổi theo điều kiện, rất hữu ích khi kết hợp với biểu đồ động.
Cách dùng:
=OFFSET(B2,2,1)
→Trả về ô cách B2 2 dòng, 1 cột, tức là ô C4.
Ví dụ nâng cao:
Tạo vùng động với số hàng xác định bằng COUNTA:
=OFFSET(A1,0,0,COUNTA(A:A),1)
→ Trả về vùng có số hàng đúng bằng số ô có dữ liệu trong cột A.
Hàm TEXT – Định dạng lại dữ liệu số/ngày
Công dụng: Biến số hoặc ngày thành văn bản theo định dạng tùy chọn.
Cách dùng:
=TEXT(TODAY(),”dd/mm/yyyy”)
→ Hiển thị ngày hiện tại theo định dạng 27/05/2025.
Ví dụ khác:
=TEXT(10000,”#,##0 VNĐ”) → 10,000 VNĐ
Hàm IFERROR – Xử lý lỗi thông minh
Công dụng: Tránh hiển thị lỗi như #DIV/0!, #N/A,… giúp bảng tính gọn gàng, chuyên nghiệp hơn.
Cách dùng:
=IFERROR(A1/B1,”Lỗi chia cho 0″)
→ Nếu B1 = 0 thì kết quả sẽ là “Lỗi chia cho 0”
Hàm SUMPRODUCT – Tính toán mảng nhiều điều kiện
Công dụng: Tổng hợp dữ liệu có điều kiện, tính trọng số, thống kê có điều kiện mà không cần dùng mảng mệnh đề IF lồng nhau.
Ví dụ thực hành:
=SUMPRODUCT((A2:A5=”Hà Nội”)*(B2:B5))
→ Tính tổng giá trị ở cột B nếu cột A là “Hà Nội”.
Giải thích: Biểu thức (A2:A5=”Hà Nội”) tạo mảng TRUE/FALSE, khi nhân với (B2:B5) sẽ lọc ra các giá trị tương ứng.
Công Thức Mảng – ARRAYFORMULA (Google Sheets) / {} trong Excel
Công dụng: Tính toán hàng loạt không cần copy công thức xuống từng dòng.
Cách dùng trong Excel:
={A1:A5*B1:B5}
Lưu ý: Cần nhấn Ctrl+Shift+Enter trong các bản Excel cũ. Excel 365 đã hỗ trợ tự động mảng động.
Hàm XLOOKUP (Excel 365 trở lên)
Công dụng: Tìm kiếm chính xác hoặc gần đúng, tìm từ trái sang phải hoặc ngược lại, thay thế VLOOKUP và HLOOKUP.
Cách dùng:
=XLOOKUP(“SP01”,A2:A10,C2:C10)
→ Trả về giá trị tương ứng với mã SP01.
Hàm FILTER (Excel 365 trở lên)
Công dụng: Lọc dữ liệu động theo điều kiện, thay thế bộ lọc thủ công.
Ví dụ:
=FILTER(A2:B10,B2:B10>100)
→ Lọc ra những dòng có cột B > 100.
Hàm LET – Đặt tên biến trong công thức
Công dụng: Làm công thức ngắn gọn, dễ đọc hơn, giúp tái sử dụng biến trong công thức nhiều lần mà không cần tính lại.
Cách dùng:
=LET(x, A1*2, y, x+3, y + 5)
→ x = A1*2 → y = x + 3 → kết quả = y + 5
Hàm LAMBDA – Tạo hàm tùy chỉnh
Công dụng: Viết hàm do bạn định nghĩa như lập trình. Có thể lưu trong Name Manager để sử dụng lại ở nhiều nơi.
Cách dùng:
=LAMBDA(a, a*a)(5)
→ Trả về 25.
Lưu hàm: Vào Formulas → Name Manager → New → Dán công thức LAMBDA → Đặt tên.
Hàm XMATCH – Tìm vị trí linh hoạt hơn MATCH
Công dụng: Trả về vị trí của một giá trị trong mảng, hỗ trợ tìm từ đầu/cuối, gần đúng/chính xác.
Ví dụ:
=XMATCH(5,{2,4,5,6,7}) → Trả về 3
So sánh với MATCH: XMATCH có thêm nhiều tùy chọn mode tìm kiếm linh hoạt.
Hàm UNIQUE – Lọc ra giá trị duy nhất
Công dụng: Trả về danh sách các giá trị không trùng nhau từ một cột hoặc bảng.
Ví dụ:
=UNIQUE(A2:A10)
→ Trả về danh sách các giá trị duy nhất trong cột A.
Tổng Kết
Những hàm nâng cao trong Excel là trợ thủ đắc lực giúp bạn:
- Tăng tốc độ xử lý bảng tính
- Tối ưu và tự động hóa công việc
- Giảm lỗi, tăng độ chính xác và chuyên nghiệp
Nếu bạn thường xuyên làm việc với Excel, hãy lưu lại và luyện tập dần các hàm này – chắc chắn bạn sẽ “lên trình Excel” nhanh chóng.
Xem thêm 1 số bài viết: hàm nâng cao trong Excel
Cách tạo mục lục tự động trong Word chi tiết nhất (có ví dụ)
