Trong quá trình làm việc với Excel, việc chỉ sử dụng các hàm cơ bản như SUM, IF hay COUNT sẽ khó đáp ứng những yêu cầu xử lý dữ liệu ngày càng phức tạp. Việc thành thạo các hàm Excel nâng cao sẽ giúp bạn tự động hóa công việc, phân tích dữ liệu nhanh hơn và nâng cao hiệu suất làm việc trong kế toán, tài chính, nhân sự hay kinh doanh. Bài viết dưới đây sẽ tổng hợp những hàm hiện đại và hữu ích nhất mà bất kỳ người dùng Excel nào cũng nên biết.
Nhóm hàm tìm kiếm và tham chiếu (Lookup & Reference)
Hàm XLOOKUP: Giải pháp mới thay thế hoàn hảo cho VLOOKUP và HLOOKUP
XLOOKUP được đánh giá là một trong những các hàm Excel nâng cao mạnh mẽ nhất trên Excel hiện đại. Hàm này khắc phục gần như toàn bộ nhược điểm của VLOOKUP và HLOOKUP truyền thống.
Cú pháp: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Ưu điểm nổi bật:
- Tra cứu theo chiều ngang hoặc chiều dọc.
- Không cần đếm số cột trả về.
- Có thể tìm kiếm từ dưới lên trên.
- Hỗ trợ giá trị mặc định nếu không tìm thấy.
Ví dụ: =XLOOKUP(A2,D:D,E:E,”Không có dữ liệu”)
Công thức trên giúp tìm mã sản phẩm trong cột D và trả về giá trị tương ứng ở cột E.
Hàm INDEX & MATCH: Giải pháp cho các truy vấn dữ liệu phức tạp
Khi cần tìm kiếm linh hoạt hơn, INDEX kết hợp MATCH được xem là lựa chọn tối ưu trong các hàm nâng cao trong Excel.
Công thức: =INDEX(array, row_num, [column_num])
Ý nghĩa:
- MATCH xác định vị trí dữ liệu cần tìm.
- INDEX trả về giá trị tại vị trí tương ứng.
Ưu điểm:
- Không bị giới hạn tìm kiếm từ trái sang phải.
- Ít bị lỗi khi thêm hoặc xóa cột.
- Xử lý dữ liệu lớn hiệu quả.
Đây là kỹ thuật thường xuyên được sử dụng trong các báo cáo quản trị doanh nghiệp.
Hàm INDIRECT & OFFSET: Xử lý vùng dữ liệu động (Dynamic Ranges)
INDIRECT và OFFSET hỗ trợ tạo vùng dữ liệu thay đổi theo từng điều kiện.
Ví dụ: =SUM(OFFSET(A1,0,0,B1,1))
Nếu B1 bằng 10 thì Excel sẽ tự động cộng từ A1 đến A10.
INDIRECT cho phép tham chiếu gián tiếp thông qua chuỗi văn bản: =INDIRECT(“A”&B1)
Hai công thức này giúp xây dựng Dashboard hoặc báo cáo động vô cùng hiệu quả.
Nhóm hàm mảng động (Dynamic Array Functions)
Excel 365 mang đến nhiều các hàm Excel nâng cao giúp xử lý dữ liệu theo dạng mảng mà không cần sao chép công thức.
Hàm FILTER: Lọc dữ liệu cực nhanh không cần dùng công cụ Filter thủ công
FILTER cho phép trích xuất dữ liệu theo điều kiện chỉ với một công thức.
Công thức: =FILTER(array, include, [if_empty])
Đây là một hàm Excel nâng cao rất hữu ích khi làm báo cáo tự động.
Hàm UNIQUE: Loại bỏ dữ liệu trùng lặp tự động
UNIQUE giúp lấy danh sách giá trị duy nhất mà không cần Remove Duplicate.
Công thức: =UNIQUE(array, [by_col], [exactly_once])
Ứng dụng:
- Lấy danh sách khách hàng.
- Danh sách sản phẩm.
- Danh sách nhân viên không bị lặp.
Việc sử dụng UNIQUE giúp giảm thời gian xử lý dữ liệu đáng kể.
Hàm SORT: Sắp xếp dữ liệu theo bất kỳ tiêu chí nào
SORT giúp tự động sắp xếp dữ liệu khi nguồn thay đổi.
Công thức ví dụ lồng hàm UNIQUE vào bên trong hàm SORT: =SORT(UNIQUE(A2:A1000))
Trong đó:
- Cột số 2 được dùng để sắp xếp.
- Giá trị 1 tương ứng thứ tự tăng dần.
Đây là một trong các hàm trong Excel nâng cao được ứng dụng nhiều trong Dashboard.
Hàm SEQUENCE: Tạo dãy số tự động trong báo cáo
SEQUENCE tạo chuỗi số liên tục cực kỳ tiện lợi.
Ví dụ: =SEQUENCE(5;2;2;2)
Hàm này thường được dùng để đánh số thứ tự hoặc tạo lịch biểu tự động.
Nhóm hàm Logic và tính toán nâng cao
Bên cạnh nhóm tìm kiếm và mảng động, các hàm Excel nâng cao còn hỗ trợ xử lý điều kiện và tính toán rất hiệu quả.
Hàm IFS & SWITCH: Xử lý nhiều điều kiện thay cho hàm IF lồng nhau rắc rối
IFS giúp thay thế IF lồng nhiều tầng.
Ví dụ: =IFS(A2>=90,”Xuất sắc”,A2>=80,”Giỏi”,A2>=65,”Khá”,TRUE,”Trung bình”)
Trong khi đó SWITCH phù hợp với việc kiểm tra nhiều giá trị cố định.
=SWITCH(A2,1,”Nam”,2,”Nữ”,”Khác”)
Đây là hàm nâng cao trong Excel giúp công thức ngắn gọn và dễ bảo trì hơn.
Hàm SUMIFS, COUNTIFS, AVERAGEIFS: Tính toán theo nhiều tiêu chí
Đây là nhóm công thức xuất hiện rất nhiều trong các hàm Excel nâng cao trong kế toán.
Ví dụ SUMIFS: =SUMIFS(C:C,A:A,”Miền Bắc”,B:B,”Laptop”)
COUNTIFS: =COUNTIFS(A:A,”Nam”,B:B,”>25″)
AVERAGEIFS: =AVERAGEIFS(C:C,A:A,”Phòng Kinh doanh”)
Các công thức này giúp thống kê doanh thu, số lượng đơn hàng hoặc tính trung bình theo nhiều điều kiện khác nhau.
Kết hợp hàm để xử lý ngày tháng (DATEDIF, EDATE, EOMONTH)
Ngày tháng là dữ liệu phổ biến trong doanh nghiệp và kế toán.
DATEDIF tính khoảng cách giữa hai mốc thời gian: =DATEDIF(A2,B2,”Y”)
EDATE cộng hoặc trừ số tháng: =EDATE(A2,6)
EOMONTH trả về ngày cuối cùng của tháng: =EOMONTH(A2,0)
Ví dụ hàm DATEDIF tính khoảng cách giữa hai mốc thời gian
Việc kết hợp ba công thức này giúp lập kế hoạch tài chính, quản lý hợp đồng và theo dõi thời hạn thanh toán hiệu quả hơn.
Tổng kết
Việc thành thạo các hàm Excel nâng cao sẽ giúp người dùng xử lý dữ liệu nhanh chóng, giảm sai sót và nâng cao năng suất làm việc. Từ các hàm tra cứu như XLOOKUP, INDEX & MATCH đến FILTER, UNIQUE, SUMIFS hay DATEDIF đều mang lại khả năng tự động hóa mạnh mẽ cho công việc văn phòng, kế toán và phân tích dữ liệu.
Nếu bạn đang tìm kiếm một chiếc laptop cấu hình mạnh để học tập, làm việc với Excel và các ứng dụng văn phòng mượt mà, hãy đến Viettel Store để lựa chọn laptop chính hãng với nhiều ưu đãi hấp dẫn, bảo hành uy tín và hỗ trợ trả góp linh hoạt, đáp ứng mọi nhu cầu làm việc chuyên nghiệp.
Xem thêm:
Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *
Tạo bình luận mới