Tạo các hàm tùy chỉnh trong Excel

Mặc dù Excel bao gồm nhiều chức năng của trang tính dựng sẵn, rất có thể là nó không có một hàm cho tất cả các loại tính toán mà bạn thực hiện. Các nhà thiết kế của Excel không thể dự đoán được tất cả các nhu cầu về tính toán của người dùng. Thay vào đó, Excel cung cấp cho bạn khả năng tạo các hàm tùy chỉnh, được giải thích trong bài viết này.

Bạn đang tìm kiếm thông tin về cách tạo một hàm JavaScript tùy chỉnh mà bạn có thể chạy trên Excel for Windows, Excel for Mac hoặc Excel dành cho web ? Nếu bạn đang có, hãy xem bài viết tổng quan về chức năng tùy chỉnh Excel.

Các hàm tùy chỉnh, chẳng hạn như macro, sử dụng ngôn ngữ lập trình Visual Basic for Applications (VBA) . Chúng khác với macro theo hai cách có ý nghĩa. Trước tiên, họ sử dụng các thủ tục hàm thay vì quy trình phụ . Tức là, họ bắt đầu với một câu lệnh của hàm thay vì tuyên bố phụ và kết thúc bằng hàm End thay vì kết thúc phụ. Thứ hai, họ thực hiện các phép tính thay vì lấy hành động. Một số loại báo cáo, chẳng hạn như câu lệnh chọn và định dạng phạm vi, được loại trừ khỏi các hàm tùy chỉnh. Trong bài viết này, bạn sẽ tìm hiểu cách tạo và sử dụng các hàm tùy chỉnh. Để tạo các hàm và macro, bạn làm việc với trình soạn thảo Visual Basic (VBE), thao tác này sẽ mở ra trong một cửa sổ mới riêng biệt từ Excel.

Giả sử công ty của bạn cung cấp một khoản chiết khấu số lượng 10 phần trăm trên việc bán sản phẩm, được cung cấp đơn hàng là dành cho nhiều hơn 100 đơn vị. Trong các đoạn văn sau đây, chúng tôi sẽ thể hiện một hàm để tính toán chiết khấu này.

Ví dụ dưới đây cho thấy một biểu mẫu đơn hàng liệt kê từng mục, số lượng, giá cả, chiết khấu (nếu có) và giá cả được mở rộng.

Mẫu đơn hàng trong ví dụ mà không có hàm tùy chỉnh

Để tạo một hàm chiết khấu tùy chỉnh trong sổ làm việc này, hãy thực hiện theo các bước sau:

  1. Nhấn Alt + F11 để mở trình soạn thảo Visual Basic (trên máy Mac, nhấn FN + Alt + F11), rồi bấm chèn > mô-đun. Một cửa sổ mô-đun mới sẽ xuất hiện ở phía bên phải của trình soạn thảo Visual Basic.

  2. Sao chép và dán mã sau đây vào mô-đun mới.

    Function DISCOUNT(quantity, price)
       If quantity >=100 Then
         DISCOUNT = quantity * price * 0.1
       Else
         DISCOUNT = 0
       End If
     
     DISCOUNT = Application.Round(Discount, 2)
    End Function
    

Lưu ý: Để giúp mã của bạn dễ đọc hơn, bạn có thể sử dụng phím tab để thụt lề dòng. Thụt lề chỉ dành cho lợi ích của bạn và là tùy chọn, như mã sẽ chạy bằng hoặc không có. Sau khi bạn nhập một dòng thụt lề, trình soạn thảo Visual Basic giả định dòng tiếp theo của bạn sẽ được thụt lề tương tự. Để chuyển ra (có nghĩa là, ở bên trái) một ký tự tab, nhấn Shift + Tab.

Bây giờ bạn đã sẵn sàng để sử dụng hàm DISCOUNT mới. Đóng trình soạn thảo Visual Basic, chọn ô G7 và gõ như sau:

= DISCOUNT (D7, E7)

Excel tính giá giảm 10 phần trăm trên các đơn vị 200 tại $47,50 trên mỗi đơn vị và trả về $950,00.

Trong dòng đầu tiên của mã VBA, hàm DISCOUNT (số lượng, giá), bạn chỉ ra rằng hàm DISCOUNT cần có hai đối số, số lượnggiá. Khi bạn gọi hàm trong một ô trang tính, bạn phải bao gồm hai đối số đó. Trong công thức = DISCOUNT (D7, E7), D7 là đối số số lượng và E7 là đối số giá . Bây giờ, bạn có thể sao chép công thức chiết khấu vào G8: G13 để có được kết quả được hiển thị bên dưới.

Hãy xem xét cách Excel diễn giải quy trình hàm này. Khi bạn nhấn Enter, Excel sẽ tìm được chiết khấu tên trong sổ làm việc hiện tại và thấy rằng nó là một hàm tùy chỉnh trong một mô-đun VBA. Các tên đối số được đặt trong dấu ngoặc đơn, số lượnggiá cả, có chỗ dành sẵn cho các giá trị mà trong đó tính toán của chiết khấu là dựa trên.

Biểu mẫu thứ tự ví dụ với một hàm tùy chỉnh

Câu lệnh if trong khối mã sau đây sẽ kiểm tra tham đối số lượng và xác định xem số lượng mục được bán lớn hơn hoặc bằng 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Nếu số lượng mục được bán lớn hơn hoặc bằng 100, VBA sẽ thực hiện câu lệnh sau đây, mà nhân giá trị số lượng theo giá trị giá và sau đó nhân kết quả bởi 0,1:

Discount = quantity * price * 0.1

Kết quả được lưu trữ dưới dạng chiết khấubiến số. Một câu lệnh VBA lưu một giá trị trong một biến được gọi là một câu lệnh gán , bởi vì nó đánh giá biểu thức ở bên phải của dấu bằng và gán kết quả là tên biến số ở bên trái. Vì chiết khấu biến đổi có cùng tên với quy trình hàm, giá trị được lưu trữ trong biến được trả về công thức trang tính có tên là hàm Discount.

Nếu số lượng nhỏ hơn 100, VBA sẽ thực thi câu lệnh sau:

Discount = 0

Cuối cùng, câu lệnh sau đây làm tròn giá trị được gán cho biến giảm đến hai vị trí thập phân:

Discount = Application.Round(Discount, 2)

VBA không có hàm ROUND nhưng Excel. Do đó, để sử dụng vòng trong câu lệnh này, bạn cho biết VBA tìm phương pháp tròn (hàm) trong đối tượng ứng dụng (Excel). Bạn thực hiện điều đó bằng cách thêm ứng dụng Word trước khi từ Round. Sử dụng cú pháp này bất cứ khi nào bạn cần truy nhập một hàm Excel từ một mô-đun VBA.

Một hàm tùy chỉnh phải bắt đầu với một câu lệnh hàm và kết thúc bằng một câu lệnh kết thúc hàm. Ngoài tên hàm, câu lệnh của hàm thường xác định một hoặc nhiều đối số. Tuy nhiên, bạn có thể tạo một hàm không có đối số nào. Excel bao gồm một số hàm dựng sẵn — RAND và bây giờ, ví dụ-không sử dụng các đối số.

Theo dõi tuyên bố hàm, một thủ tục hàm bao gồm một hoặc nhiều câu lệnh trong VBA đưa ra quyết định và thực hiện các phép tính bằng cách sử dụng các đối số được truyền cho hàm. Cuối cùng, một nơi nào đó trong quy trình hàm, bạn phải bao gồm một câu lệnh gán một giá trị thành một biến số có cùng tên với hàm. Giá trị này được trả về công thức gọi hàm.

Số từ khóa VBA mà bạn có thể sử dụng trong các hàm tùy chỉnh nhỏ hơn số bạn có thể sử dụng trong macro. Các hàm tùy chỉnh không được phép thực hiện bất kỳ điều gì khác hơn là trả về một giá trị cho một công thức trong một trang tính hoặc một biểu thức được sử dụng trong một macro VBA hoặc hàm. Ví dụ, các hàm tùy chỉnh không thể đổi kích cỡ Windows, sửa một công thức trong một ô hoặc thay đổi tùy chọn phông chữ, màu, hoặc mẫu cho văn bản trong một ô. Nếu bạn bao gồm mã "hành động" của loại này trong quy trình hàm, hàm trả về #VALUE! .

Một hành động một thủ tục hàm có thể thực hiện (ngoài các phép tính thực hiện) sẽ hiển thị hộp thoại. Bạn có thể sử dụng một câu lệnh Inputbox trong một hàm tùy chỉnh như một phương tiện bắt đầu nhập từ người dùng thực hiện hàm. Bạn có thể sử dụng một câu lệnh hộp msgnhư một phương tiện thông tin về việc truyền đạt cho người dùng. Bạn cũng có thể sử dụng các hộp thoại tùy chỉnh hoặc UserForms, nhưng đó là một chủ đề vượt quá phạm vi giới thiệu này.

Các macro đơn giản và các hàm tùy chỉnh có thể khó đọc. Bạn có thể làm cho chúng dễ hiểu hơn bằng cách nhập văn bản giải thích trong biểu mẫu chú thích. Bạn thêm chú thích bằng cách trước văn bản giải thích bằng dấu nháy đơn. Ví dụ, ví dụ sau đây Hiển thị hàm DISCOUNT với chú thích. Thêm chú thích như thế này giúp bạn dễ dàng hơn cho bạn hoặc những người khác để duy trì mã VBA của bạn theo thời gian. Nếu bạn cần thực hiện thay đổi đối với mã trong tương lai, bạn sẽ có thêm thời gian tìm hiểu những gì bạn đã làm Ban đầu.

Ví dụ về hàm VBA với chú thích

Một dấu nháy cho biết Excel bỏ qua tất cả mọi thứ ở bên phải trên cùng một dòng, vì vậy bạn có thể tạo ra chú thích hoặc trên các dòng của chính họ hoặc ở bên phải của các dòng có chứa mã VBA. Bạn có thể bắt đầu một khối tương đối dài của mã với chú thích giải thích về mục đích tổng thể và sau đó dùng chú thích trong tài liệu cho các câu lệnh riêng lẻ.

Một cách khác để tài liệu macro và các hàm tùy chỉnh của bạn là cung cấp cho họ tên mô tả. Ví dụ, chứ không phải tên một nhãnmacro, bạn có thể đặt tên cho nó monthlabels để mô tả cụ thể hơn mục đích phục vụ macro. Sử dụng tên mô tả cho macro và các hàm tùy chỉnh đặc biệt hữu ích khi bạn đã tạo nhiều quy trình, đặc biệt là nếu bạn tạo các quy trình có các mục đích tương tự nhưng không giống nhau.

Cách bạn tài liệu macro và các hàm tùy chỉnh của bạn là một vấn đề tùy chọn cá nhân. Điều quan trọng là áp dụng một số phương pháp tài liệu, và sử dụng nó một cách nhất quán.

Để dùng hàm Custom, sổ làm việc có chứa mô-đun bạn đã tạo hàm đó phải được mở. Nếu sổ làm việc đó không mở ra, bạn sẽ nhận được một #NAME? lỗi khi bạn tìm cách sử dụng hàm. Nếu bạn tham chiếu hàm trong một sổ làm việc khác, bạn phải đặt tên hàm với tên của sổ làm việc trong đó hàm. Ví dụ, nếu bạn tạo một hàm có tên là chiết khấu trong sổ làm việc có tên là Personal. xlsb và bạn gọi hàm that từ sổ làm việc khác, bạn phải nhập = Personal. xlsb! DISCOUNT (), not simply = DISCOUNT ().

Bạn có thể tự lưu một số tổ hợp phím (và các lỗi đánh máy có thể) bằng cách chọn các hàm tùy chỉnh từ hộp thoại chèn hàm. Các hàm tùy chỉnh của bạn xuất hiện trong thể loại người dùng được xác định:

hộp thoại chèn hàm

Một cách dễ dàng hơn để làm cho các chức năng tùy chỉnh của bạn sẵn dùng tại mọi thời điểm là lưu trữ chúng trong một sổ làm việc riêng biệt, rồi lưu sổ làm việc đó dưới dạng phần bổ trợ. Sau đó, bạn có thể làm cho phần bổ trợ sẵn dùng bất cứ khi nào bạn chạy Excel. Sau đây là cách thực hiện:

  1. Sau khi bạn đã tạo các hàm bạn cần, hãy bấm tệp > lưu như.

    Trong Excel 2007, bấm vào nút Microsoft Office, rồi bấm vào lưu dưới dạng

  2. Trong hộp thoại lưu dưới dạng , mở danh sách thả xuống lưu dưới dạng , rồi chọn bổ trợ Excel. Lưu sổ làm việc dưới tên được thông báo, chẳng hạn như Myfunctions, trong thư mục Addins . Hộp thoại lưu dưới dạng sẽ đề xuất thư mục đó, vì vậy tất cả những gì bạn cần làm là chấp nhận vị trí mặc định.

  3. Sau khi đã lưu sổ làm việc, hãy bấm vào tệp > tùy chọn Excel.

    Trong Excel 2007, bấm vào Microsoft Office Button, rồi bấm vào tùy chọn Excel.

  4. Trong hộp thoại tùy chọn Excel , hãy bấm vào thể loại bổ trợ.

  5. Trong danh sách thả xuống quản lý , chọn bổ trợ Excel. Sau đó bấm nút đi .

  6. Trong hộp thoại bổ trợ, chọn hộp kiểm bên cạnh tên mà bạn đã sử dụng để lưu sổ làm việc của bạn, như được hiển thị bên dưới.

    hộp thoại bổ trợ

  1. Sau khi bạn đã tạo các hàm bạn cần, hãy bấm tệp > lưu như.

  2. Trong hộp thoại lưu dưới dạng , mở danh sách thả xuống lưu dưới dạng , rồi chọn bổ trợ Excel. Lưu sổ làm việc dưới tên đã biết, chẳng hạn như Myfunctions.

  3. Sau khi đã lưu sổ làm việc, hãy bấm công cụ > bổ trợ Excel.

  4. Trong hộp thoại bổ trợ, hãy chọn nút duyệt để tìm phần bổ trợ của bạn, bấm vào mở, rồi chọn hộp bên cạnh bổ trợ của bạn trong hộp bổ trợ sẵn dùng .

Sau khi bạn làm theo các bước này, các hàm tùy chỉnh của bạn sẽ sẵn dùng mỗi lần bạn chạy Excel. Nếu bạn muốn thêm vào thư viện hàm của mình, hãy quay lại trình soạn thảo Visual Basic. Nếu bạn tìm trong dự án trình soạn thảo Visual Basic Explorer bên dưới đầu đề dự án VBAProject, bạn sẽ thấy một mô-đun được đặt tên theo tệp bổ trợ của bạn. Bổ trợ của bạn sẽ có phần mở rộng. xlam.

mô-đun tên trong vbe

Bấm đúp vào mô-đun trong dự án Explorer khiến trình soạn thảo Visual Basic Hiển thị mã hàm của bạn. Để thêm một hàm mới, hãy đặt vị trí điểm chèn của bạn sau khi kết thúc hàm End chấm dứt hàm cuối cùng trong cửa sổ mã, rồi bắt đầu nhập. Bạn có thể tạo nhiều hàm như bạn cần theo cách này, và chúng sẽ luôn sẵn dùng trong thể loại người dùng được xác định trong hộp thoại chèn hàm .

Nội dung này ban đầu được tác giả bởi Mark Dodge và Craig Stinson như một phần trong sách của họ Microsoft Office Excel 2007 trong ra ngoài. Từ đó đã được Cập Nhật để áp dụng cho các phiên bản mới hơn của Excel.

Bạn cần thêm trợ giúp?

Bạn luôn có thể nhờ chuyên gia trong Cộng đồng Kỹ thuật Excel, tìm sự hỗ trợ trongCộng đồng Giải pháp hoặc đề xuất tính năng hay cải tiến mới trên Excel User Voice.

Lưu ý:  Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Cho chúng tôi biết thông tin này có hữu ích hay không? Dưới đây là bài viết bằng tiếng Anh để bạn tham khảo..​

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×