Địa chỉ:
Lầu 7 Tòa nhà STA, 618 đường 3/2, Phường Diên Hồng (Phường 14, Quận 10), TP HCM
Giờ làm việc
Thứ 2 tới thứ 6: 8:00 - 17:00
20+ hàm Excel nâng cao giúp giảm 50% thời gian làm báo cáo
Suốt 30 năm qua, Excel vẫn có cho mình một vị thế nhất định nhờ khả năng biến hóa kỳ diệu qua các công thức, bất chấp sự ra đời của nhiều công cụ hiện đại. Để không tụt hậu trong kỷ nguyên dữ liệu, việc làm chủ các hàm Excel nâng cao chính là chìa khóa vàng giúp bạn tối ưu hóa công việc và khẳng định lợi thế cạnh tranh tuyệt đối.
Tại sao cần làm chủ các hàm Excel nâng cao?
Biết dùng Excel là một kỹ năng cơ bản nhưng thành thạo các hàm Excel nâng cao mới là thứ phân loại giữa một nhân viên bình thường và một chuyên gia xử lý dữ liệu. Dưới đây là những lý do tại sao bạn không nên dừng lại ở hàm SUM hay AVERAGE.
- Tự động hóa báo cáo tối đa: Thay vì phải copy-paste hay lọc dữ liệu thủ công mỗi ngày, các hàm nâng cao cho phép bạn xây dựng một “hệ thống” tự vận hành. Chỉ cần thay đổi dữ liệu nguồn, mọi kết quả và biểu đồ sẽ tự động cập nhật trong tích tắc.
- Xử lý khối lượng dữ liệu khổng lồ (Big Data): Khi bảng tính lên đến hàng chục nghìn dòng, các thao tác thủ công sẽ làm máy bị treo hoặc gây nhầm lẫn. Các hàm như XLOOKUP, SUMPRODUCT hay MAP được thiết kế để truy xuất và tính toán cực nhanh trên quy mô lớn.
- Đảm bảo tính chính xác tuyệt đối: Sai sót trong số liệu là thảm họa đối với kế toán hay tài chính. Việc sử dụng các hàm điều kiện (IFS, IFERROR) giúp kiểm soát logic chặt chẽ, loại bỏ hoàn toàn các lỗi sai sót do con người (human error).
- Nâng tầm tư duy phân tích: Làm chủ hàm nâng cao giúp bạn có khả năng “nhìn thấu” dữ liệu, từ đó đưa ra các báo cáo chuyên sâu, dự báo xu hướng và hỗ trợ lãnh đạo ra quyết định chính xác hơn.
- Lợi thế cạnh tranh vượt trội: Trong mọi bản mô tả công việc (JD) cho các vị trí lương cao như Business Analyst, Finance Manager hay Marketing Planner, “Thành thạo Excel nâng cao” luôn là yêu cầu tiên quyết.
Xem thêm: Học Excel nâng cao cho người đi làm để làm chủ dữ liệu
Hàm Excel nâng cao: Nhóm hàm tìm kiếm và tham chiếu
Hàm XLOOKUP
XLOOKUP là phiên bản nâng cấp hoàn hảo, thay thế cho cả VLOOKUP và HLOOKUP. Hàm này cho phép bạn tìm kiếm giá trị trong một mảng và trả về kết quả tương ứng ở một mảng khác mà không quan tâm đến vị trí trái hay phải. Điểm đặc biệt là nó không yêu cầu số thứ tự cột, giúp công thức không bị lỗi khi bạn chèn thêm cột vào bảng dữ liệu.
Lưu ý: XLOOKUP chỉ hỗ trợ trên Office 365 và Excel 2021 trở đi. Nếu bạn gửi file cho người dùng phiên bản cũ (như 2016), công thức sẽ bị lỗi #NAME?.
Hàm OFFSET và INDIRECT
OFFSET giúp bạn tạo ra một vùng tham chiếu di động dựa trên một điểm mốc cố định. Nó thường được dùng để tạo biểu đồ tự động cập nhật vùng dữ liệu khi có dòng mới. Trong khi đó, INDIRECT giúp biến một chuỗi văn bản thành một tham chiếu thực thụ. Điều này cực kỳ hữu ích khi bạn muốn lấy dữ liệu từ nhiều Sheet khác nhau chỉ bằng cách thay đổi tên Sheet trong một ô chỉ định.
Vì cả hai đều là hàm “biến động” (Volatile). Nếu lạm dụng quá nhiều trong các bảng tính lớn, chúng sẽ khiến Excel phải tính toán lại liên tục mỗi khi bạn nhấn chuột, gây ra tình trạng giật lag.
Bộ đôi INDEX và MATCH
Trước khi XLOOKUP ra đời, INDEX và MATCH là lựa chọn số một của các chuyên gia. Hàm MATCH dùng để xác định vị trí (số hàng hoặc số cột) của một giá trị trong một vùng chọn, sau đó hàm INDEX sẽ truy xuất giá trị chính xác tại tọa độ đó.
Sự kết hợp này mang lại linh hoạt tuyệt đối khi tìm kiếm theo cả chiều dọc và chiều ngang, đồng thời xử lý được các bảng dữ liệu có cấu trúc phức tạp mà VLOOKUP không làm được. Đây là phương pháp tối ưu hiệu suất cho các file dữ liệu cực lớn vì nó tiêu tốn ít tài nguyên xử lý hơn so với việc quét toàn bộ bảng tính.
Hàm Excel nâng cao: Nhóm hàm xử lý thời gian
Hàm WORKDAY.INTL và NETWORKDAYS.INTL
Đây là các hàm tính toán ngày làm việc chuyên sâu. NETWORKDAYS.INTL cho phép bạn tính tổng số ngày làm việc giữa hai mốc thời gian sau khi trừ đi các ngày nghỉ lễ và cuối tuần. Điểm mạnh của phiên bản “.INTL” là bạn có thể tùy chỉnh ngày cuối tuần (ví dụ: chỉ nghỉ Chủ nhật hoặc nghỉ Thứ hai).
Ghi chú nhỏ: Hãy luôn chuẩn bị một danh sách ngày lễ (Holidays) ở một vùng riêng để hàm trừ đi chính xác các ngày nghỉ đặc thù của địa phương hoặc doanh nghiệp.
Hàm EDATE và EOMONTH
EDATE trả về một ngày đúng n tháng trước hoặc sau ngày hiện tại, thường dùng để tính ngày đáo hạn hợp đồng. EOMONTH thì trả về ngày cuối cùng của tháng đó. Đây là công cụ đắc lực cho kế toán khi cần xác định kỳ báo cáo hoặc tính khấu hao tài sản hàng tháng.
Kết quả trả về thường là một dãy số định dạng Serial. Bạn cần vào thẻ Home > Number Format và chọn Short Date để hiển thị đúng định dạng ngày tháng.
Hàm DATEDIF
Dù là một trong các hàm Excel nâng cao phổ biến nhưng DATEDIF lại không xuất hiện trong danh sách gợi ý tự động của Excel. Hàm này cực kỳ mạnh mẽ trong việc tính toán khoảng cách giữa hai mốc thời gian theo đơn vị Năm, Tháng hoặc Ngày. Đây là công cụ không thể thiếu để tính tuổi, thâm niên công tác hoặc thời hạn hợp đồng. Lưu ý là bạn phải gõ tay chính xác tên hàm và tham số đơn vị phải đặt trong dấu ngoặc kép: “Y” (Năm), “M” (Tháng), “D” (Ngày).
Hàm Excel nâng cao: Nhóm hàm tính toán số học và thống kê
Hàm SUBTOTAL
Điểm khác biệt lớn nhất của SUBTOTAL so với hàm SUM hay AVERAGE là khả năng tương tác với bộ lọc (Filter). Khi bạn lọc dữ liệu, SUBTOTAL sẽ tự động bỏ qua các hàng bị ẩn và chỉ tính toán trên các giá trị đang hiển thị trên màn hình. Nếu sử dụng các mã hàm từ 101-111 (ví dụ 109 thay vì 9), hàm sẽ bỏ qua cả các hàng bị ẩn thủ công (Hide) bằng tay thay vì chỉ các hàng bị ẩn do Filter.
Hàm SUMPRODUCT
SUMPRODUCT là một trong những hàm đa năng nhất trong Excel. Nó thực hiện nhân các phần tử tương ứng trong nhiều mảng rồi cộng các tích đó lại. Hàm này có thể thay thế cho các cột phụ tính tổng doanh thu (Số lượng * Đơn giá) và thậm chí thực hiện các phép tính tổng có nhiều điều kiện phức tạp tương tự như SUMIFS.
Lưu ý: Các mảng được chọn phải có kích thước bằng nhau (cùng số hàng, số cột), nếu không hàm sẽ trả về lỗi #VALUE!.
Hàm AGGREGATE
Nếu hàm SUM thông thường sẽ “đầu hàng” khi vùng dữ liệu chứa lỗi hoặc có các hàng bị ẩn, thì AGGREGATE là giải pháp cứu cánh. Nó tích hợp 19 hàm khác nhau (như SUM, AVERAGE, MAX…) và cung cấp tùy chọn để bỏ qua các hàng lỗi, hàng ẩn hoặc các hàm con khác bên trong vùng dữ liệu. Hãy chú ý đến tham số “Option” trong hàm. Ví dụ: Option 6 sẽ bỏ qua mọi giá trị lỗi, giúp báo cáo của bạn luôn sạch sẽ và chính xác.
Hàm Excel nâng cao: Các hàm mảng động
Hàm LAMBDA và MAP
LAMBDA cho phép người dùng tự tạo ra các hàm tùy chỉnh mà không cần VBA. Khi kết hợp với MAP, bạn có thể áp dụng một logic phức tạp cho từng ô trong một vùng dữ liệu lớn một cách đồng nhất. Ví dụ, bạn có thể tạo một hàm kiểm tra logic thuế suất phức tạp một lần duy nhất và gọi lại nó ở bất kỳ đâu trong file. Để quản lý hàm LAMBDA hiệu quả, hãy sử dụng Name Manager (Ctrl + F3) để đặt tên cho công thức, giúp việc sử dụng lại trở nên chuyên nghiệp hơn.
Hàm FILTER, UNIQUE và SORT
Bộ ba này là “vũ khí” tối thượng để lọc và sắp xếp dữ liệu tự động. FILTER giúp trích xuất dữ liệu theo điều kiện, UNIQUE lọc danh sách duy nhất và SORT sắp xếp chúng ngay lập tức. Kết quả của các hàm này sẽ “tràn” (Spill) ra các ô xung quanh một cách linh hoạt. Hãy đảm bảo vùng ô bên dưới và bên phải công thức không có dữ liệu ngăn cản, nếu không bạn sẽ gặp lỗi #SPILL!.
Hàm Excel nâng cao: Nhóm hàm logic và xử lý chuỗi
Hàm IFS và LET
Hàm IFS giúp loại bỏ việc lồng quá nhiều hàm IF, làm cho công thức dễ đọc và bảo trì hơn. Hàm LET cho phép bạn đặt tên cho các kết quả tính toán trung gian bên trong một công thức. Điều này giúp Excel không phải tính toán lại cùng một biểu thức nhiều lần, từ đó tăng tốc độ xử lý cho file. Trong hàm IFS, Excel sẽ kiểm tra các điều kiện theo thứ tự từ trái sang phải. Hãy đặt các điều kiện quan trọng hoặc hẹp nhất lên đầu tiên.
Hàm TEXTJOIN
Khác với phép ghép nối “&” hay CONCAT, TEXTJOIN cho phép bạn chọn một dấu phân cách chung và có tùy chọn bỏ qua các ô trống. Đây là hàm lý tưởng để tổng hợp danh sách tên, địa chỉ hoặc mã hàng từ một vùng dữ liệu lớn.
Mẹo tối ưu hóa khi sử dụng các hàm Excel nâng cao
Để các hàm Excel nâng cao phát huy tối đa sức mạnh, bạn cần áp dụng những kỹ thuật tối ưu hóa sau đây nhằm tăng tốc độ xử lý và giảm thiểu sai sót cho bảng tính.
Sử dụng Excel Table (Ctrl + T)
Excel Table là một trong những tính năng mạnh mẽ nhất để hỗ trợ các hàm Excel nâng cao. Khi chuyển dữ liệu sang định dạng Table, vùng tham chiếu của bạn sẽ tự động co giãn. Điều này có nghĩa là khi bạn thêm dữ liệu mới, các hàm như SUMPRODUCT hay FILTER sẽ tự động nhận diện vùng mới mà bạn không cần sửa công thức thủ công. Ngoài ra, Table cho phép bạn sử dụng “Tham chiếu có cấu trúc” (Structured References) như Table1[Doanh Thu] thay vì $C$2:$C$100, giúp công thức cực kỳ dễ hiểu.
Đặt tên vùng (Named Range)
Việc nhớ các tọa độ ô như $A$2:$G$1000 rất khó khăn và dễ nhầm lẫn. Bằng cách sử dụng Name Manager (Ctrl + F3) để đặt tên cho các vùng dữ liệu, bạn có thể viết các hàm nâng cao như một câu văn logic. Ví dụ: =SUM(Doanh_Thu_Thang_1) thay vì một chuỗi tọa độ khô khan. Cách làm này không chỉ giúp bạn quản lý file tốt hơn mà còn giúp người khác dễ dàng đọc hiểu bảng tính của bạn.
Kiểm soát lỗi và Debug nhanh
Khi viết các hàm lồng nhau phức tạp, việc xác định lỗi ở đâu là rất khó. Bạn có thể sử dụng phím F9 để kiểm tra kết quả tức thời: Chỉ cần bôi đen một phần của công thức trên thanh Formula Bar và nhấn F9, Excel sẽ hiển thị giá trị tính toán của phần đó. Ngoài ra, hãy sử dụng tính năng Evaluate Formula trong thẻ Formulas để xem cách Excel thực hiện từng bước tính toán, giúp bạn tìm ra lỗ hổng logic trong chớp mắt.
Hạn chế hàm biến động (Volatile Functions)
Các hàm như INDIRECT, OFFSET, TODAY() được gọi là hàm biến động vì chúng bắt Excel tính toán lại toàn bộ bảng tính mỗi khi bạn thực hiện bất kỳ thao tác nào (ngay cả khi không liên quan đến chúng). Trong các file dữ liệu hàng trăm nghìn dòng, việc lạm dụng các hàm này sẽ khiến file cực kỳ nặng và giật lag. Nếu có thể, hãy thay thế OFFSET bằng INDEX để tăng tốc độ xử lý.
Tận dụng hàm LET để rút gọn công thức
Trong các công thức phức tạp nơi bạn phải lặp lại một biểu thức tính toán nhiều lần, hãy sử dụng hàm LET. Hàm này cho phép bạn đặt tên cho kết quả trung gian và sử dụng lại chúng. Việc này giúp Excel chỉ phải tính toán biểu thức đó một lần duy nhất, giúp file chạy nhanh hơn đáng kể và công thức trông gọn gàng hơn nhiều.
Kết luận
Việc nắm vững các hàm Excel nâng cao không chỉ là một kỹ năng văn phòng thuần túy, mà còn là một lợi thế chiến lược giúp bạn làm chủ thông tin trong kỷ nguyên số. Thay vì bị ngợp bởi những bảng tính hàng nghìn dòng, các hàm như XLOOKUP, LAMBDA hay INDEX/MATCH sẽ biến bạn trở thành một người làm việc với dữ liệu chuyên nghiệp thực thụ.
Tại sao phải tốn hàng giờ mỗi ngày cho những tác vụ lặp đi lặp lại trong khi bạn có thể để Excel làm thay mình? Đã đến lúc thoát khỏi cái bẫy thủ công để tập trung vào những giá trị cốt lõi hơn. Khóa học Excel for Business Analytics tại Starttrain không chỉ dạy bạn các hàm, mà huấn luyện bạn cách phân tích dữ liệu và trực quan hóa tự động giúp tối ưu hiệu suất gấp 10 lần.
