Hướng dẫn cách tự tạo hàm UDF trong Excel bằng lệnh VBA

Đã bao giờ bạn gặp khó khăn khi làm việc với Excel vì không tìm được hàm phù hợp chưa? Nếu bạn đã từng ước rằng mình có thể tự tạo hàm mới thì Gitiho xin khẳng định bạn có thể thực sự làm được điều đó. Trong bài viết ngày hôm nay, chúng ta sẽ cùng đi tìm hiểu cách tạo hàm UDF trong Excel bằng lệnh VBA nhé.

Tại sao nên sử dụng hàm UDF trong Excel?

Giới thiệu các loại hàm và hàm UDF trong Excel

Về cơ bản, hàm là một phương pháp xử lý dữ liệu đầu vào (Input) để đưa ra dữ liệu mới (Output). Dựa trên định nghĩa này, Excel có vô số hàm để người dùng sử dụng. Tất cả những hàm này có thể được phân loại làm ba nhóm dưới đây.

Nhóm hàm Worksheet

Nhóm hàm Worksheet bao gồm các hàm xử lý dữ liệu cơ bản. Đây là các loại hàm thông dụng nhất khi người dùng thao tác với trang tính Excel. Nhóm hàm này có thể được chia nhỏ làm các hàm tính toán, hàm tham chiếu, hàm văn bản. Một số ví dụ cụ thể về nhóm hàm Worksheet như sau:

Hàm SUMIF: Hàm tính toán tổng giá trị với điều kiện xác định.

Hàm VLOOKUP: Hàm tham chiếu dữ liệu thông qua một phạm vi giá trị trong trang tính.

Hàm MID: Hàm văn bản trích xuất chuỗi ký tự nằm ở giữa nội dung ô tính.

Bên cạnh các hàm tiêu biểu kể trên, hệ thống Excel còn cung cấp cho chúng ta rất nhiều các hàm Worksheet khác để thao tác hiệu quả. Nếu bạn còn chưa biết hết các hàm này, hãy tìm hiểu ngay nhé. 

Nhóm hàm thủ tục

Nhóm hàm thủ tục (Procedure Function) bao gồm các cấu trúc lập trình được sử dụng để người dùng tạo các Macro thay vì thao tác trên trang tính thông thường. Với các Macro này, người dùng có thể thực hiện một số hoạt động để Excel sẽ trả về một giá trị ứng với mã lập trình được gọi bởi hàm thủ tục đã nhập.

Hàm UDF

Với hai nhóm hàm trên, người dùng đã có thể thực hiện hầu hết các công việc xử lý dữ liệu trên Excel. Tuy nhiên, một vài tình huống sẽ đòi hỏi nhiều hơn là các hàm Excel sẵn có. Trong trường hợp này, bạn cần tự tạo cho mình một hàm mới. Nhóm hàm UDF trong Excel (User-defined Function) sẽ giúp bạn làm được điều này bằng sự trợ giúp của công cụ VBA.
 

Điểm mạnh của hàm UDF trong Excel

Chúng mình có thể liệt kê một vài lí do sau đây bạn nên tự tạo hàm trong Excel để thao tác với trang tính của mình.

1. Hàm UDF làm gọn Worksheet

Thay vì nhập cả dải ô để thực hiện các phép tính nối tiếp để cho ra kết quả, bạn có thể sử dụng hàm UDF trong Excel để thu gọn các phép tính đó về một công thức duy nhất. Chắc chắn rằng, trang tính của bạn sẽ được thu gọn lại và dễ theo dõi hơn rất nhiều.

2.  Hàm UDF tăng hiệu suất làm việc

Hàm UDF trong Excel giúp bạn tăng hiệu suất làm việc nhờ tính năng lưu trữ hàm. Sau khi bạn tự tạo hàm trong Excel để giải quyết một trường hợp cụ thể, bạn hoàn toàn có thể sử dụng lại hàm đó trong bất cứ trường hợp nào tương tự về sau. Bạn không còn phải đi tra cứu một chuỗi các công thức hàm có sẵn nữa, mà chỉ cần nhập hàm UDF là đã hoàn thành công việc xử lý dữ liệu rồi. Ngoài ra, điều này sẽ hạn chế tối đa lỗi nhập liệu trong quá trình bạn thao tác trên trang tính.

3. Hàm UDF là minh chứng cho sự linh hoạt của VBA Excel

Với hàm UDF trong Excel, bạn có thể truy cập trực tiếp vào Macro trong phạm vi Worksheet của mình. Bạn toàn quyền sử dụng các tính năng VBA Excel như toán tử logic, vòng lặp VBA, cũng như các tính năng mạnh mẽ khác. Như vậy, hàm UDF chính là minh chứng cho khả năng vô hạn của Excel trong xử lý dữ liệu.

Với các điểm mạnh này, chắc chắn bạn sẽ muốn học cách tự tạo hàm trong Excel. Vậy thì chúng ta cùng đi tìm hiểu ngay thôi.

Làm thế nào để tạo hàm UDF trong Excel?

Xác định thông tin tạo hàm UDF trong Excel

Việc đầu tiên bạn cần làm trước khi bắt tay vào tự tạo hàm trong Excel là xác định rõ thông tin đầu ra tương ứng với đầu vào của hàm, tức là trả lời hai câu hỏi:

1. Hàm này sẽ trả về thông tin gì?
2. Để hàm trả về thông tin đó, ta cần cung cấp dữ liệu gì?

Chúng mình sẽ tìm hiểu một ví dụ tạo hàm UDF trong Excel cụ thể để đếm chính xác số lượng kí tự "X" viết hoa trong trang tính. Như vậy, hai câu hỏi trên được trả lời như sau:

1. Hàm này sẽ trả về thông tin gì?

Giá trị số nguyên thể hiện tổng kí tự "X" viết hoa trong dải ô.

2. Để hàm trả về thông tin đó, ta cần cung cấp dữ liệu gì?

Dải ô cần thực hiện đếm kí tự.

Tạo hàm UDF trong Excel bằng lệnh VBA

Sau khi đã xác định được thông tin về hàm cần tạo, chúng tachọn tab Developer > nhóm Code > Visual Basic.

Cửa sổ VBA Excel hiện lên. Bạn nhấn chuột phải tại Sheet đang mở (Sheet1) > Insert > Module.

 

Tại Module này, bạn sẽ tiến hành viết lệnh tự tạo hàm trong Excel dựa. Dưới đây là một ví dụ về hàm UDF chúng mình đã tạo để thực hiện yêu cầu đếm tổng số kí tự "X" viết hoa trong phạm vi trang tính.

Nếu bạn đã biết về lập trình Macro thì bạn sẽ dễ dàng nhận ra hàm UDF trong Excel có cấu trúc tương tự với một hàm UDF thông thường. Tuy nhiên, nếu bạn chưa làm quen với việc lập trình này, hãy cùng chúng mình phân tích từng thành phần trong Macro nhé.

Phân tích các thành phần của hàm UDF trong Excel

Hãy bắt đầu với dòng đầu tiên:

Giải thích

Hàm được định nghĩa với tên là "NumXs", áp dụng cho dải ô bạn mong muốn (Range) để trả về kết quả là một số nguyên (Integer).
 

Lưu ý

Khi tự tạo hàm trong Excel, bạn chỉ cần thay đổi tên hàm và định dạng kết quả cần trả về tùy vào mục đích tạo hàm. Đối với những phần khác trong dòng này, bạn không cần thay đổi thường xuyên.

Bảy dòng tiếp theo tạo hàm UDF trong Excel được tạo để kiểm tra từng ô tính:

Giải thích

Cài đặt chức năng đếm (iCnt) bằng 0, nối tiếp bởi vòng lặp VBA Excel với cấu trúc For Next cho phép các dòng lệnh lập trình trong khoảng từ "For Each" đến "Next c" lặp lại cho từng ô tính. Chúng ta xác định được giá trị trong mỗi ô là "X" và lưu trữ chúng vào biến sTemp.

Sau đó, một vòng lặp For Next khác được sử dụng kết hợp công thức IF với để đếm số lượng ký tự trong biến. Nếu là "X" thì giá trị tại iCnt sẽ tăng thêm một đơn vị.

• Dòng tiếp theo cực kì quan trọng với lệnh tự tạo hàm trong Excel

Giải thích

Biến NumXs sẽ có giá trị bằng kết quả trong iCnt.

Lưu ý

Biến NumXs có tên trùng với tên hàm được định nghĩa tại dòng đầu tiên của lệnh. Chúng ta định giá trị cho NumXs theo cách này để VBA Excel hiểu giá trị này nên được trả về từ hàm UDF trong Excel.

Dòng cuối cùng trong lệnh tạo hàm UDF

Giải thích

Dòng này thông báo cho VBA Excel rằng đã kết thúc hàm UDF.

Như vậy, chúng ta đã viết xong các thành phần của lệnh tự tạo hàm trong Excel. Hãy cùng thử áp dụng hàm này vào trang tính với chúng mình nhé.

Áp dụng hàm UDF trong Excel vào trang tính

Tương tự như cách sử dụng các hàm Worksheet khác, chúng ta nhập công thức hàm vào ô tính. Giả sử, chúng ta cần xác định số ký tự "X" viết hoa trong phạm vi ô A2:A15, chúng ta điền công thức hàm UDF trong Excel như sau:

=NumXs(A2:A15)

Kết quả sẽ hiện lên trong ô tính như sau:

Vậy là hàm đã hoạt động hiệu quả và giải quyết được yêu cầu đặt ra từ đầu bài. Nếu trang tính của các bạn có quá nhiều dòng, các bạn có thể kết hợp viết hàm tìm dòng cuối cùng chứa dữ liệu trước khi áp dụng hàm đếm ký tự NumXs.

Xem thêm: Hướng dẫn viết hàm tìm dòng cuối cùng có dữ liệu trong VBA

Tổng kết

Qua bài viết ngày hôm nay, chúng ta đã tìm hiểu cách tự tạo hàm UDF trong Excel với lệnh VBA. Đây là công cụ vô cùng hữu ích để các bạn giải quyết các khó khăn với giới hạn các hàm có sẵn trên Excel. Hãy đọc thêm các bài viết trên blog Gitiho để cập nhật các kiến thức VBA Excel nhé. Nếu bạn cần một lộ trình học tập hiệu quả, hãy đăng kí khóa học Tuyệt đỉnh VBA với chúng mình.

Gitiho chúc các bạn thành công!