10 Hàm Excel nâng cao trên Microsoft 365 mà dân kế toán/văn phòng phải biết

Trong kỷ nguyên số, Excel vẫn là công cụ không thể thiếu cho mọi doanh nghiệp, đặc biệt là dân kế toán và văn phòng. Tuy nhiên, nếu bạn vẫn đang loay hoay với các hàm truyền thống như VLOOKUP hay SUMIF, bạn đang bỏ lỡ sức mạnh thực sự của Microsoft 365. Bài viết này sẽ mở ra cánh cửa đến 10 hàm Excel nâng cao, dựa trên công nghệ mảng động (dynamic arrays), giúp bạn biến đổi cách làm việc, phân tích dữ liệu, tạo báo cáo động chỉ trong nháy mắt. Hãy sẵn sàng tiết kiệm thời gian, nâng cao độ chính xác và đưa ra quyết định thông minh hơn bao giờ hết!

Excel luôn là xương sống của mọi hoạt động văn phòng và kế toán. Từ việc quản lý sổ sách, lập báo cáo tài chính, theo dõi doanh thu đến phân tích dữ liệu phức tạp, không có công cụ nào linh hoạt và mạnh mẽ như Excel. Tuy nhiên, với sự phát triển không ngừng của công nghệ, phiên bản Excel trong Microsoft 365 đã được trang bị những tính năng và hàm mới mang tính cách mạng, đặc biệt là các hàm mảng động (Dynamic Arrays).

Nếu bạn vẫn đang sử dụng các phương pháp truyền thống như VLOOKUP hay INDEX-MATCH, bạn có thể đang bỏ lỡ cơ hội tiết kiệm hàng giờ làm việc và nâng cao độ chính xác của dữ liệu. Những hàm nâng cao này không chỉ giúp bạn giải quyết các vấn đề phức tạp một cách dễ dàng hơn mà còn mở ra những khả năng phân tích và trình bày dữ liệu hoàn toàn mới.

Bài viết này sẽ giới thiệu 10 hàm Excel nâng cao trên Microsoft 365 mà mọi kế toán viên và nhân viên văn phòng nên nằm lòng. Chúng sẽ thay đổi cách bạn tương tác với dữ liệu, từ việc tìm kiếm, lọc, sắp xếp đến tổng hợp thông tin, giúp bạn làm việc hiệu quả hơn, thông minh hơn và ít lỗi hơn.

Tại sao những hàm này lại quan trọng với dân Kế toán/Văn phòng?

• Tăng hiệu quả công việc: Tự động hóa nhiều tác vụ thủ công, giảm thời gian xử lý dữ liệu.
• Nâng cao độ chính xác: Giảm thiểu lỗi do nhập liệu thủ công hoặc công thức phức tạp.
• Báo cáo động và linh hoạt: Dễ dàng tạo các báo cáo có thể tự động cập nhật khi dữ liệu nguồn thay đổi.
• Phân tích dữ liệu chuyên sâu: Khám phá insights mới từ dữ liệu mà trước đây khó có thể thực hiện.
• Tiết kiệm thời gian và công sức: Tập trung vào phân tích và đưa ra quyết định thay vì "đấu tranh" với bảng tính.

1. XLOOKUP – Tìm kiếm thông minh vượt trội

XLOOKUP là sự kế thừa hoàn hảo cho VLOOKUP và HLOOKUP, mang đến khả năng tìm kiếm linh hoạt và mạnh mẽ hơn rất nhiều. Nó khắc phục hầu hết các nhược điểm của các hàm tìm kiếm truyền thống, trở thành "must-have" cho mọi người dùng Excel.

• Tại sao hữu ích:
  • Tìm kiếm cả sang trái và sang phải.
  • Không giới hạn về cột trả về.
  • Xử lý lỗi tìm kiếm không có sẵn (N/A) trực tiếp trong hàm.
  • Hỗ trợ tìm kiếm gần đúng và tìm kiếm ký tự đại diện.
  • Tìm kiếm từ dưới lên hoặc từ trên xuống.

Cú pháp: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Ví dụ thực tế: Bạn có danh sách sản phẩm (Mã sản phẩm, Tên sản phẩm, Giá bán) và muốn tìm giá bán của một sản phẩm bất kỳ dựa vào Tên sản phẩm, ngay cả khi Tên sản phẩm nằm bên phải Mã sản phẩm.

=XLOOKUP("Bánh mì", A2:A10, C2:C10, "Không tìm thấy")

Mẹo: Dùng match_mode = 0 cho tìm kiếm chính xác (mặc định), hoặc -1 cho khớp nhỏ hơn gần nhất, 1 cho khớp lớn hơn gần nhất. Kết hợp với search_mode để tìm kiếm từ dưới lên trong các trường hợp cần giá trị mới nhất.

2. FILTER – Lọc dữ liệu động theo điều kiện

FILTER là một hàm mảng động cho phép bạn trích xuất các hàng hoặc cột từ một phạm vi dữ liệu dựa trên các điều kiện cụ thể. Kết quả trả về là một mảng động, tự động cập nhật khi dữ liệu nguồn thay đổi hoặc điều kiện lọc được điều chỉnh.

• Tại sao hữu ích:
  • Tạo các báo cáo con động (sub-reports) chỉ với một công thức.
  • Dễ dàng lọc dữ liệu theo nhiều điều kiện khác nhau.
  • Không cần sử dụng bộ lọc thủ công hay bảng Pivot cho những tác vụ đơn giản.

Cú pháp: FILTER(array, include, [if_empty])

Ví dụ thực tế: Lọc tất cả các giao dịch bán hàng của "Khu vực Miền Nam" có doanh thu lớn hơn 1.000.000 VNĐ từ bảng dữ liệu bán hàng.

=FILTER(A2:D100, (B2:B100="Miền Nam") * (C2:C100 > 1000000), "Không có dữ liệu")

Mẹo: Dùng dấu * để kết hợp điều kiện AND, dấu + để kết hợp điều kiện OR.

3. SORT – Sắp xếp dữ liệu dễ dàng

SORT cho phép bạn sắp xếp một phạm vi dữ liệu theo một hoặc nhiều cột theo thứ tự tăng dần hoặc giảm dần. Giống như FILTER, kết quả là một mảng động.

• Tại sao hữu ích:
  • Tạo danh sách hoặc báo cáo đã được sắp xếp tự động.
  • Không cần sắp xếp thủ công dữ liệu nguồn, giữ nguyên thứ tự ban đầu.
  • Sắp xếp theo nhiều tiêu chí.

Cú pháp: SORT(array, [sort_index], [sort_order], [by_col])

Ví dụ thực tế: Sắp xếp danh sách nhân viên theo tên (cột 2) theo thứ tự bảng chữ cái tăng dần.

=SORT(A2:C50, 2, 1)

Mẹo: sort_order = 1 cho tăng dần, -1 cho giảm dần. by_col = TRUE nếu muốn sắp xếp theo cột thay vì hàng.

4. UNIQUE – Lấy danh sách duy nhất

UNIQUE trích xuất một danh sách các giá trị duy nhất từ một phạm vi dữ liệu, loại bỏ các mục trùng lặp. Đây là một hàm cực kỳ hữu ích cho việc tạo danh sách không trùng lặp (ví dụ: danh sách khách hàng duy nhất, danh mục sản phẩm).

• Tại sao hữu ích:
  • Tạo danh sách thả xuống (Data Validation List) động.
  • Đếm số lượng mục duy nhất.
  • Dễ dàng tổng hợp các danh mục mà không có trùng lặp.

Cú pháp: UNIQUE(array, [by_col], [exactly_once])

Ví dụ thực tế: Lấy danh sách các khu vực kinh doanh duy nhất từ cột Khu vực trong bảng dữ liệu bán hàng.

=UNIQUE(B2:B100)

Mẹo: Dùng exactly_once = TRUE nếu bạn chỉ muốn các giá trị chỉ xuất hiện đúng một lần trong danh sách nguồn.

5. LET – Đơn giản hóa công thức phức tạp

LET cho phép bạn định nghĩa tên cho các kết quả tính toán trong một công thức. Điều này giúp công thức trở nên dễ đọc, dễ hiểu và hiệu quả hơn, đặc biệt với những công thức dài và phức tạp.

• Tại sao hữu ích:
  • Tăng tính dễ đọc và bảo trì của công thức.
  • Cải thiện hiệu suất bằng cách tính toán một biểu thức chỉ một lần, ngay cả khi nó được sử dụng nhiều lần trong công thức.
  • Giúp chia nhỏ các công thức phức tạp thành các phần nhỏ hơn, dễ quản lý hơn.

Cú pháp: LET(name1, value1, [name2, value2], ..., calculation)

Ví dụ thực tế: Tính tổng doanh thu của một khu vực nhất định và áp dụng thuế suất, sử dụng LET để định nghĩa các biến.

=LET(
    sales_data, B2:C100,
    region, "Miền Bắc",
    tax_rate, 0.1,
    filtered_sales, FILTER(sales_data, INDEX(sales_data, 0, 1)=region),
    total_sales, SUM(INDEX(filtered_sales, 0, 2)),
    total_sales * (1 + tax_rate)
)

Mẹo: Luôn đặt tên biến rõ ràng, dễ hiểu để tối đa hóa lợi ích của LET.

6. TEXTSPLIT – Tách văn bản linh hoạt

TEXTSPLIT là một hàm mới mạnh mẽ cho phép bạn tách chuỗi văn bản thành các cột hoặc hàng riêng biệt dựa trên dấu phân cách (delimiter) được chỉ định. Đây là công cụ tuyệt vời để xử lý dữ liệu không đồng nhất.

• Tại sao hữu ích:
  • Tách tên đầy đủ thành Họ, Tên đệm, Tên.
  • Phân tích địa chỉ hoặc các chuỗi thông tin có cấu trúc.
  • Xử lý dữ liệu được nhập sai định dạng hoặc cần phân tích chi tiết.

Cú pháp: TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Ví dụ thực tế: Tách chuỗi "Nguyen Van A, 123 Tran Hung Dao, Quan 1" thành các cột riêng biệt cho Tên, Địa chỉ, Quận.

=TEXTSPLIT("Nguyen Van A, 123 Tran Hung Dao, Quan 1", ", ")

Mẹo: Bạn có thể chỉ định nhiều dấu phân cách bằng cách đặt chúng vào một mảng, ví dụ: {",", ";"}. Dùng row_delimiter nếu muốn tách ra hàng thay vì cột.

7. SEQUENCE – Tạo chuỗi số động

SEQUENCE là một hàm đơn giản nhưng cực kỳ mạnh mẽ, cho phép bạn tạo một chuỗi số tự động theo một mẫu nhất định (số hàng, số cột, giá trị bắt đầu, bước nhảy). Nó là nền tảng cho nhiều giải pháp mảng động phức tạp hơn.

• Tại sao hữu ích:
  • Tạo số thứ tự tự động cho bảng.
  • Tạo danh sách ngày, giờ.
  • Hỗ trợ trong các công thức phức tạp hơn để tạo chỉ mục, mảng tạm thời.

Cú pháp: SEQUENCE(rows, [columns], [start], [step])

Ví dụ thực tế: Tạo một danh sách 10 số thứ tự bắt đầu từ 1 và tăng dần 1 đơn vị.

=SEQUENCE(10)

Ví dụ khác: Tạo lịch 7 ngày bắt đầu từ ngày hiện tại.

=TODAY()+SEQUENCE(1,7,0,1)

Mẹo: Dùng SEQUENCE để tạo các mảng động có kích thước linh hoạt, ví dụ, tạo một bảng tính toán động với số lượng hàng tùy theo dữ liệu.

8. SORTBY – Sắp xếp theo một cột khác

Trong khi SORT sắp xếp một phạm vi dựa trên thứ tự của các cột trong chính phạm vi đó, SORTBY cho phép bạn sắp xếp một phạm vi dữ liệu dựa trên các giá trị của một phạm vi (hoặc mảng) khác. Điều này rất hữu ích khi bạn muốn sắp xếp một danh sách nhưng tiêu chí sắp xếp nằm ở một nơi khác.

• Tại sao hữu ích:
  • Sắp xếp danh sách tên nhân viên theo ID hoặc mức lương nằm ở cột khác.
  • Tạo các báo cáo được sắp xếp theo các tiêu chí phức tạp hơn.

Cú pháp: SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

Ví dụ thực tế: Sắp xếp danh sách Tên sản phẩm (A2:A10) theo Giá bán (B2:B10) từ cao xuống thấp.

=SORTBY(A2:A10, B2:B10, -1)

Mẹo: Bạn có thể sắp xếp theo nhiều cột khác nhau bằng cách thêm các cặp by_array và sort_order.

9. CHOOSECOLS – Chọn các cột cụ thể

CHOOSECOLS cho phép bạn trích xuất một hoặc nhiều cột cụ thể từ một mảng hoặc phạm vi dữ liệu lớn hơn. Đây là công cụ tuyệt vời để tạo các báo cáo hoặc bảng tóm tắt chỉ chứa các thông tin cần thiết.

• Tại sao hữu ích:
  • Tạo báo cáo gọn gàng, chỉ hiển thị các cột quan trọng.
  • Giảm thiểu lỗi khi copy/paste các cột thủ công.
  • Kết hợp với các hàm khác để tạo báo cáo động, tùy chỉnh.

Cú pháp: CHOOSECOLS(array, col_num1, [col_num2], ...)

Ví dụ thực tế: Từ một bảng dữ liệu bán hàng lớn (A1:G100) gồm Mã SP, Tên SP, Giá, Số lượng, Doanh thu, Khu vực, Ngày bán, bạn chỉ muốn tạo một báo cáo hiển thị Tên SP, Doanh thu và Ngày bán.

=CHOOSECOLS(A1:G100, 2, 5, 7)

Mẹo: Bạn có thể sử dụng số âm cho col_num để chọn cột từ phải sang trái (ví dụ: -1 là cột cuối cùng).

10. VSTACK – Ghép nối các mảng theo chiều dọc

VSTACK (Vertical STACK) là một hàm mảng động cho phép bạn ghép nối hai hoặc nhiều mảng hoặc phạm vi dữ liệu theo chiều dọc (ghép chồng lên nhau). Điều này cực kỳ hữu ích khi bạn cần tổng hợp dữ liệu từ nhiều nguồn khác nhau vào một bảng duy nhất.

• Tại sao hữu ích:
  • Tổng hợp dữ liệu bán hàng từ các bảng dữ liệu hàng tháng/quý.
  • Kết hợp danh sách khách hàng từ các phòng ban khác nhau.
  • Tạo một nguồn dữ liệu duy nhất cho bảng Pivot hoặc phân tích khác.

Cú pháp: VSTACK(array1, [array2], ...)

Ví dụ thực tế: Bạn có dữ liệu bán hàng Quý 1 ở A2:D10 và dữ liệu Quý 2 ở F2:I10. Bạn muốn tổng hợp cả hai vào một bảng duy nhất.

=VSTACK(A2:D10, F2:I10)

Mẹo: Đảm bảo các mảng bạn ghép nối có cùng số lượng cột và các loại dữ liệu tương ứng để tránh lỗi hoặc kết quả không mong muốn.

Lời kết

Microsoft 365 đã mang đến một cuộc cách mạng trong cách chúng ta sử dụng Excel. Các hàm mảng động và những cải tiến như XLOOKUP không chỉ là những tính năng mới mà là những công cụ thay đổi cuộc chơi, giúp bạn làm việc thông minh hơn, nhanh hơn và chính xác hơn.

Đối với dân kế toán và văn phòng, việc nắm vững 10 hàm này sẽ không chỉ nâng cao hiệu suất làm việc mà còn mở rộng khả năng phân tích và đưa ra quyết định dựa trên dữ liệu. Hãy bắt đầu thực hành ngay hôm nay và trải nghiệm sự khác biệt mà chúng mang lại. Đừng để mình bị tụt hậu trong thế giới Excel ngày càng phát triển!

Chúc bạn thành công!