Tự Tạo Hàm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Những Từ Đồng Nghĩa Với “excellent”: Khám Phá Và Ứng Dụng Trong Giao Tiếp Hàng Ngày

Mở Đầu

Excel là một trong những công cụ mạnh mẽ nhất trong lĩnh vực xử lý dữ liệu, phân tích kinh doanh và hỗ trợ quyết định. Mặc dù Excel đã cung cấp một kho tàng các hàm tích hợp sẵn (SUM, VLOOKUP, INDEX, MATCH, …), nhưng trong thực tế, người dùng thường gặp phải những tình huống mà các hàm có sẵn không đáp ứng được nhu cầu đặc thù. Đó là lúc tự tạo hàm trong Excel (User Defined Function – UDF) trở thành một giải pháp hữu hiệu.

Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ những kiến thức cơ bản nhất về VBA (Visual Basic for Applications) cho tới các kỹ thuật nâng cao, tối ưu hoá hiệu suất, và cách triển khai UDF trong môi trường doanh nghiệp. Ngoài ra, chúng ta sẽ thảo luận về các lưu ý bảo mật, cách chia sẻ hàm cho đồng nghiệp, và một số ví dụ thực tế để bạn có thể áp dụng ngay vào công việc hàng ngày.

Lưu ý: Để thực hiện các bước trong bài viết, bạn cần có quyền truy cập vào tab “Developer” trong Excel và khả năng bật macro. Nếu chưa thấy tab này, hãy vào File → Options → Customize Ribbon và tích vào “Developer”.

Có thể bạn quan tâm: Tứ Phân Vị Trong Excel: Khái Niệm, Công Thức, Ứng Dụng Và Các Bước Thực Hiện Chi Tiết

1. Tổng Quan Về User Defined Function (UDF)

1.1 Định Nghĩa

User Defined Function (UDF) là một hàm tự viết bằng ngôn ngữ lập trình VBA, được lưu trữ trong một module của workbook và có thể được gọi trong các công thức ô giống như các hàm tích hợp sẵn.

1.2 Lợi Ích Khi Sử Dụng UDF

1.3 Khi Nào Nên Tự Tạo Hàm?

• Khi không có hàm tích hợp đáp ứng yêu cầu (ví dụ tính “chu kỳ tài chính” tùy chỉnh).
• Khi cần thực hiện vòng lặp hoặc điều kiện phức tạp.
• Khi muốn tự động hoá các quy trình như gửi email, tạo báo cáo.
• Khi muốn đóng gói một thuật toán phức tạp (ví dụ tính chỉ số tài chính, dự báo thời gian).

Có thể bạn quan tâm: Tổng Hợp Công Thức Excel

2. Chuẩn Bị Môi Trường Phát Triển VBA

2.1 Mở Tab Developer

1. Vào File → Options → Customize Ribbon.
2. Tích chọn “Developer” trong danh sách “Main Tabs”.
3. Nhấn “OK”.

2.2 Bảo Mật Macro

• Macro Settings: File → Options → Trust Center → Trust Center Settings → Macro Settings.
• Chọn “Enable all macros” (không khuyến khích cho môi trường production) hoặc “Disable all macros with notification” để cho phép người dùng quyết định.

2.3 Tạo Module Mới

1. Mở VBA Editor bằng phím ALT + F11.
2. Trong Project Explorer, chuột phải vào VBAProject (YourWorkbook.xlsx) → Insert → Module.
3. Module mới sẽ xuất hiện dưới dạng Module1. Bạn có thể đổi tên bằng cách nhấn F4 và chỉnh Name.

3. Cú Pháp Cơ Bản Để Viết Hàm

3.1 Cấu Trúc Cơ Bản

Public Function TenHam(ThamSo1 As DataType, ThamSo2 As DataType, ...) As ReturnType ' Kiểm tra và xử lý dữ liệu đầu vào ' Thực hiện tính toán TenHam = KếtQuả
End Function

• Public hoặc Private: Public cho phép hàm được gọi từ bất kỳ sheet nào; Private chỉ dùng trong module hiện tại.
• Function thay vì Sub vì chúng ta muốn trả về giá trị.
• As DataType: xác định kiểu dữ liệu (Integer, Double, String, Variant, …). Kiểu Variant linh hoạt nhất nhưng chậm hơn.

3.2 Ví Dụ Đơn Giản: Hàm Tính Trung Bình Không Bao Gồm Giá Trị 0

Public Function AvgNoZero(rng As Range) As Double Dim cell As Range Dim sum As Double, cnt As Long For Each cell In rng If IsNumeric(cell.Value) And cell.Value <> 0 Then sum = sum + cell.Value cnt = cnt + 1 End If Next cell If cnt = 0 Then AvgNoZero = 0 Else AvgNoZero = sum / cnt End If
End Function

Cách dùng: =AvgNoZero(A1:A10)

3.3 Kiểm Tra Lỗi (Error Handling)

Public Function SafeDivide(num As Double, den As Double) As Variant On Error GoTo ErrHandler SafeDivide = num / den Exit Function
ErrHandler: SafeDivide = CVErr(xlErrDiv0) ' Trả về lỗi #DIV/0!
End Function

4. Các Kiểu Dữ Liệu Thông Dụng

Lưu ý: Khi làm việc với dải ô, thường dùng Range để truyền vào hàm, vì Excel sẽ tự chuyển đổi thành mảng 2D khi truy cập rng.Value.

5. Ví Dụ Thực Tế: Tạo Hàm Tính “Ngày Làm Việc Kế Tiếp”

5.1 Mô Tả Yêu Cầu

• Cho một ngày bắt đầu và số ngày làm việc (không tính ngày nghỉ cuối tuần và ngày lễ).
• Trả về ngày làm việc kế tiếp.

5.2 Chuẩn Bị Dữ Liệu Ngày Lễ

Giả sử chúng ta có danh sách ngày lễ trong sheet “Holidays” cột A.

Public Function NextWorkday(startDate As Date, workDays As Long) As Date Dim curDate As Date Dim i As Long Dim ws As Worksheet Dim holidayRng As Range Dim isHoliday As Boolean Set ws = ThisWorkbook.Worksheets("Holidays") Set holidayRng = ws.Range("A1", ws.Cells(ws.Rows.Count, "A").End(xlUp)) curDate = startDate For i = 1 To workDays Do curDate = curDate + 1 isHoliday = Not IsError(Application.Match(curDate, holidayRng, 0)) Loop While Weekday(curDate, vbMonday) > 5 Or isHoliday ' 6=Saturday,7=Sunday Next i NextWorkday = curDate
End Function

Cách dùng: =NextWorkday(A2, 5) – tính ngày làm việc thứ 5 sau ngày trong A2, bỏ qua cuối tuần và ngày lễ.

5.3 Tối Ưu Hoá Với Dictionary

Nếu danh sách ngày lễ lớn, việc dùng Match mỗi lần sẽ chậm. Thay vào đó, dùng Scripting.Dictionary.

Public Function NextWorkdayFast(startDate As Date, workDays As Long) As Date Dim curDate As Date, i As Long Dim dict As Object Dim ws As Worksheet, holidayRng As Range, cell As Range Set dict = CreateObject("Scripting.Dictionary") Set ws = ThisWorkbook.Worksheets("Holidays") Set holidayRng = ws.Range("A1", ws.Cells(ws.Rows.Count, "A").End(xlUp)) For Each cell In holidayRng If IsDate(cell.Value) Then dict(CLng(cell.Value)) = True Next cell curDate = startDate For i = 1 To workDays Do curDate = curDate + 1 Loop While Weekday(curDate, vbMonday) > 5 Or dict.Exists(CLng(curDate)) Next i NextWorkdayFast = curDate
End Function

Dictionary cho tra cứu O(1) thời gian, giúp hàm nhanh hơn đáng kể.

6. Tối Ưu Hiệu Suất Khi Sử Dụng UDF

6.1 Tránh Lặp Lại Tính Toán

• Cache kết quả: Lưu trữ kết quả trong một biến tĩnh hoặc Dictionary nếu hàm được gọi nhiều lần với cùng tham số.
• Sử dụng Application.Volatile False: Mặc định, hàm UDF được đánh dấu volatile, tính lại mỗi khi workbook thay đổi. Đặt False nếu hàm không phụ thuộc vào trạng thái toàn cục.

Public Function MyFastSum(rng As Range) As Double Application.Volatile False Dim arr As Variant, i As Long, j As Long, sum As Double arr = rng.Value For i = LBound(arr, 1) To UBound(arr, 1) For j = LBound(arr, 2) To UBound(arr, 2) If IsNumeric(arr(i, j)) Then sum = sum + arr(i, j) Next j Next i MyFastSum = sum
End Function

6.2 Tránh Gọi Excel Object Quá Nhiều Lần

Thay vì lặp Cells(i, j).Value trong vòng lặp, lấy toàn bộ dải ô thành một mảng một lần (rng.Value) và xử lý trong VBA.

Có thể bạn quan tâm: Tự Động Giãn Ô Trong Excel: Bí Quyết Tối Ưu Hóa Bảng Tính Nhanh Chóng Và Chính Xác

6.3 Tắt Tính Toán Tự Động Khi Chạy Macro Nặng

Sub RunHeavyTask() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' ... thực hiện công việc nặng ... Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True
End Sub

7. Đưa UDF Vào Sử Dụng Thực Tế

7.1 Gọi Hàm Từ Ô

Sau khi lưu macro, bạn chỉ cần nhập công thức như bất kỳ hàm nào khác:

=AvgNoZero(B2:B20)
=NextWorkday(A2, 10)

7.2 Sử Dụng Trong PivotTable

• Khi tạo PivotTable, vào “Fields, Items & Sets → Calculated Field” và nhập công thức =MyUDF(Field1, Field2). Lưu ý hàm phải không volatile và trả về kiểu dữ liệu mà PivotTable hỗ trợ (số, ngày, text).

7.3 Gọi Từ VBA Khác

Sub TestUDF() Dim result As Double result = AvgNoZero(ThisWorkbook.Worksheets("Sheet1").Range("A1:A100")) MsgBox "Trung bình = " & result
End Sub

8. Chia Sẻ và Phân Phối UDF

8.1 Lưu Thành Add-In (.xlam)

1. Tạo một workbook mới, viết các UDF trong module.
2. File → Save As → Excel Add-In (.xlam).
3. Trong Excel, File → Options → Add-Ins → Excel Add-ins → Browse và chọn file .xlam.
4. Sau khi bật, các hàm sẽ khả dụng trong mọi workbook.

8.2 Tạo Personal Macro Workbook

• Lưu macro vào PERSONAL.XLSB (được tạo tự động khi ghi macro lần đầu). Khi mở Excel, file này sẽ tải lên và các hàm sẽ khả dụng cho mọi workbook trên máy tính đó.

8.3 Đăng Tải Vào SharePoint hoặc OneDrive

• Đối với môi trường doanh nghiệp, bạn có thể chia sẻ file .xlam qua SharePoint, rồi yêu cầu người dùng “Add” nó như Add-In. Đảm bảo các máy tính có cùng phiên bản Excel và cài đặt macro.

9. Bảo Mật và Các Rủi Ro Khi Sử Dụng Macro

9.1 Nguy Cơ Mã Độc Hại

• Macro có thể chứa mã thực thi các hành động nguy hiểm (xóa file, gửi dữ liệu). Do đó, không mở workbook không rõ nguồn.
• Sử dụng Digital Signature để ký macro của bạn, giúp người dùng tin cậy hơn.

9.2 Thiết Lập Chính Sách Group Policy

• Trong môi trường doanh nghiệp, quản trị viên có thể cấu hình Group Policy để:
• Cho phép chỉ các Add-In đã ký.
• Vô hiệu hoá macro trên các file không tin cậy.
• Ghi log các macro được chạy.

9.3 Kiểm Tra Mã Nguồn

• Đối với các hàm phức tạp, nên có code review nội bộ, đặt comment chi tiết và tài liệu mô tả đầu vào/đầu ra.

10. Một Số Ứng Dụng Thực Tế Nâng Cao

10.1 Hàm Kết Nối API Web

Public Function GetExchangeRate(currency As String) As Double Dim http As Object, url As String, json As Object Set http = CreateObject("MSXML2.XMLHTTP") url = "https://api.exchangerate-api.com/v4/latest/USD" http.Open "GET", url, False http.send If http.Status = 200 Then Set json = JsonConverter.ParseJson(http.responseText) GetExchangeRate = json("rates")(UCase(currency)) Else GetExchangeRate = CVErr(xlErrValue) End If
End Function

Yêu cầu: Thêm module JsonConverter (thư viện VBA-JSON) để phân tích JSON.

10.2 Hàm Gửi Email Tự Động

Public Sub SendAlert(toAddress As String, subject As String, body As String) Dim outlookApp As Object, mailItem As Object Set outlookApp = CreateObject("Outlook.Application") Set mailItem = outlookApp.CreateItem(0) ' 0 = olMailItem With mailItem .To = toAddress .Subject = subject .Body = body .Send End With
End Sub

Có thể gọi từ hàm hoặc macro để gửi báo cáo khi một giá trị vượt ngưỡng.

10.3 Hàm Tính “Weighted Average” Với Trọng Số Động

Public Function WeightedAvg(valuesRng As Range, weightsRng As Range) As Double Dim v As Variant, w As Variant Dim i As Long, sumV As Double, sumW As Double v = valuesRng.Value w = weightsRng.Value For i = LBound(v, 1) To UBound(v, 1) If IsNumeric(v(i, 1)) And IsNumeric(w(i, 1)) Then sumV = sumV + v(i, 1) w(i, 1) sumW = sumW + w(i, 1) End If Next i If sumW = 0 Then WeightedAvg = CVErr(xlErrDiv0) Else WeightedAvg = sumV / sumW End If
End Function

Cách dùng: =WeightedAvg(B2:B10, C2:C10)

11. Debugging và Kiểm Thử

11.1 Sử Dụng Immediate Window

• Nhập ? AvgNoZero(Range("A1:A10")) để kiểm tra nhanh.

11.2 Breakpoint và Watch

• Đặt breakpoint bằng cách nhấn F9 trên dòng code.
• Thêm biến vào Watch để theo dõi giá trị trong khi chạy.

11.3 Unit Test Trong VBA

Có thể tạo một module TestModule và viết các hàm kiểm tra:

Public Sub TestAvgNoZero() Dim rng As Range Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A5") Debug.Assert AvgNoZero(rng) = 3.5
End Sub

Chạy TestAvgNoZero và xem kết quả trong Immediate Window.

12. Các Lỗi Thường Gặp và Cách Khắc Phục

13. Tài Nguyên Học Tập Thêm

1. Microsoft Docs – VBA Reference: https://learn.microsoft.com/en-us/office/vba/api/overview/excel
2. Excel Campus – UDF Basics: https://www.excelcampus.com/vba/user-defined-functions/
3. Chandoo.org – Advanced UDF: https://chandoo.org/wp/advanced-excel-udfs/
4. GitHub – VBA-JSON: https://github.com/VBA-tools/VBA-JSON (để xử lý JSON trong VBA)
5. Stack Overflow: Tìm kiếm “Excel UDF” để xem các câu hỏi thực tế và giải pháp.

14. Kết Luận

Việc tự tạo hàm trong Excel không chỉ mở rộng khả năng tính toán mà còn giúp tự động hoá quy trình, giảm thiểu lỗi và nâng cao độ chuyên nghiệp của báo cáo. Bằng việc nắm vững những kiến thức cơ bản về VBA, cách tối ưu hoá hiệu suất, và các biện pháp bảo mật, bạn có thể biến Excel thành một công cụ “siêu năng lực” đáp ứng mọi nhu cầu kinh doanh.

Hãy bắt đầu thử nghiệm với các hàm đơn giản, dần dần mở rộng sang các giải pháp phức tạp như tích hợp API, gửi email tự động, hoặc xây dựng Add-In chia sẻ toàn công ty. Khi đã thành thạo, bạn sẽ thấy rằng giới hạn duy nhất của Excel chính là trí tưởng tượng và khả năng lập trình của chính bạn.

Chúc bạn thành công trong hành trình “tự tạo hàm trong Excel”!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.