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

Lưu ý:  Chúng tôi muốn cung cấp cho bạn nội dung trợ giúp mới nhất ở chính ngôn ngữ của bạn, nhanh nhất có thể. 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. Vui lòng cho chúng tôi biết ở cuối trang này rằng thông tin có hữu ích với bạn không? Dưới đây là bài viết bằng Tiếng Anh để bạn tham khảo dễ hơn.

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

Hàm tùy chỉnh, như macro, sử dụng Visual Basic for Applications (VBA) ngôn ngữ lập trình. Chúng khác với macro trong hai cách đáng kể. Trước tiên, họ sử dụng hàm quy trình thay vì quy trình phụ . Có nghĩa là, bắt đầu với một câu lệnh hàm thay vì một con điều khoản và kết thúc với Cuối hàm thay vì End Sub. Thứ nhì, họ thực hiện các phép tính thay vì thực hiện 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 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 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), giúp mở trong cửa sổ riêng biệt với Excel mới.

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

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

Ví dụ về biểu mẫu đặt hàng 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 làm theo các bước sau đây:

  1. Nhấn Alt + F11 để mở trình soạn thảo Visual Basic (trên máy tính, nhấn FN + ALT + F11), rồi bấm chèn > mô-đun. Cửa sổ mô-đun mới 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ã 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 ý: Để tạo mã của bạn dễ đọc hơn, bạn có thể dùng phím Tab để thụt lề dòng. Thụt lề chỉ nhận lợi ích của bạn, và tùy chọn, như mã sẽ chạy có hoặc không có nó. 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ẽ tương tự thụt lề. Để di chuyển (tức là ở bên trái) một tab ký tự, nhấn Shift + Tab.

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

=DISCOUNT(D7,E7)

Excel tính toán chiết khấu 10 phần trăm trên 200 đơn vị tại 47,50 mỗi đơn vị và trả về $950.00.

Trong dòng đầu tiên của mã VBA, hàm DISCOUNT(quantity, price), bạn chỉ ra rằng hàm chiết khấu yêu cầu 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 những 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 để G8:G13 để có kết quả hiển thị dưới đây.

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

Ví dụ về biểu mẫu đặt hàng bằng hàm tùy chỉnh

Các nếu tuyên bố trong khối mã sau đây sẽ kiểm tra đối số số lượng và xác định xem số lượng các mục đã bán có 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 các mục đã bán được lớn hơn hoặc bằng 100, VBA thực hiện câu lệnh sau đây, vốn nhân giá trị số lượng giá trị giá và sau đó nhân kết quả với 0,1:

Discount = quantity * price * 0.1

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

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

Discount = 0

Cuối cùng, câu lệnh sau đây làm tròn giá trị được gán cho biến số chiết khấu để hai chữ số thập phân:

Discount = Application.Round(Discount, 2)

VBA có không có hàm ROUND, nhưng Excel nào. Do đó, sử dụng hàm ROUND trong câu lệnh này, bạn biết VBA tìm kiếm các phương pháp Round (hàm) trong ứng dụng vào đối tượng (Excel). Bạn thực hiện điều này bằng cách thêm từ ứng dụng trước từ Round. 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.

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 với câu lệnh cuối hàm. Ngoài việc tên hàm, câu lệnh hàm thường chỉ rõ một hoặc nhiều đối số. Bạn có thể, Tuy nhiên, tạo một hàm với đối số không. Excel bao gồm một số hàm dựng sẵn — RAND và bây giờ, ví dụ — mà không sử dụng các đối số.

Theo dõi câu lệnh hàm, một quy trình hàm bao gồm một hoặc nhiều câu lệnh VBA đưa ra quyết định và thực hiện các phép tính bằng cách dùng các đối số chuyển qua hàm. Cuối cùng, đến nơi 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ị cho một biến số với cùng tên hàm. Giá trị này được trả về cho công thức gọi hàm.

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

Hành động một quy trình hàm có thể thực hiện (ngoài thực hiện phép tính) Hiển thị hộp thoại. Bạn có thể sử dụng câu lệnh InputBox trong một hàm tùy chỉnh như một phương tiện của bắt đầu vào từ người dùng thực hiện hàm. Bạn có thể sử dụng một câu lệnh MsgBox như một phương tiện truyền thông tin cho người dùng. Bạn cũng có thể dùng hộp thoại tùy chỉnh hoặc biểu mẫu người dùng, nhưng có một chủ đề ngoài phạm vi giới thiệu này.

Thậm chí đơn giản macro và hàm tùy chỉnh có thể khó đọc. Bạn có thể làm 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 của chú thích. Bạn thêm chú thích bằng trước văn bản giải thích với một dấu nháy đơn. Ví dụ, ví dụ sau đây Hiển thị chiết khấu hàm với chú thích. Thêm chú thích như thế này sẽ giúp bạn dễ dàng hơn cho bạn hoặc người khác để giữ nguyên mã VBA của bạn dưới dạng thời gian Pass. Nếu bạn cần phải thực hiện thay đổi với mã trong tương lai, bạn sẽ có một thời gian dễ dàng tìm hiểu về những gì bạn đã làm Ban đầu.

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

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

Một cách khác để tài liệu macro và hàm tùy chỉnh của bạn là cung cấp tên mang tính mô tả cho chúng. Ví dụ, chứ không phải là tên macro nhãn, bạn có thể đặt tên cho nó MonthLabels để mô tả mục đích cụ thể hơn macro đóng vai trò. Sử dụng tên mang tính mô tả cho macro và hàm tùy chỉnh là đặ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 quy trình có mục đích tương tự nhưng không giống nhau.

Làm thế nào tài liệu của bạn macro và hàm tùy chỉnh của bạn là một vấn đề của tùy chọn cá nhân. Điều quan trọng là lựa chọn phương pháp tài liệu và dùng nhất quán.

Để sử dụng một hàm tùy chỉnh, sổ làm việc có chứa mô-đun mà bạn đã tạo hàm phải đang mở. Nếu sổ làm việc đó được mở, bạn nhận được #NAME? lỗi khi bạn tìm cách 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 trước tên hàm với tên của sổ làm việc trong đó có hàm trong đó. Ví dụ, nếu bạn tạo một hàm được gọi là chiết khấu trong một sổ làm việc được gọi là Personal.xlsb và bạn gọi hàm đó từ sổ làm việc khác, bạn phải nhập =personal.xlsb!discount(), chứ không chỉ =discount().

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

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

Một cách dễ dàng hơn để hiển thị hàm tùy chỉnh của bạn tại luôn là lưu trữ chúng trong một sổ làm việc khác và sau đó lưu sổ làm việc đó dưới dạng một bổ trợ. Bạn có thể sau đó thực hiện bổ trợ sẵn dùng mỗi khi bạn chạy Excel. Đây là cách thực hiện điều này:

  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 Microsoft Office Button, rồi bấm Lưu dưới dạng

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

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

    Trong Excel 2007, bấm vào Microsoft Office Button, và bấm 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ý , hãy chọn Bổ trợ Excel. Sau đó bấm vào nút đi .

  6. Trong hộp thoại Bổ trợ , hãy chọn hộp kiểm bên cạnh tên mà bạn dùng để lưu sổ làm việc của bạn, như minh họa dưới đây.

    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 như , mở danh sách thả xuống Lưu dưới dạng , và chọn Bổ trợ Excel. Lưu sổ làm việc bên dưới nhận ra tên, chẳng hạn như MyFunctions.

  3. Sau khi bạn đã 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 bổ trợ của bạn, hãy bấm mở, sau đó chọn hộp bên cạnh của bạn bổ trợ trong hộp Bổ trợ sẵn dùng .

Sau khi bạn làm theo các bước sau đây, hàm tùy chỉnh của bạn sẽ sẵn dùng mỗi khi bạn chạy Excel. Nếu bạn muốn thêm vào thư viện hàm, trở về trình soạn thảo Visual Basic. Nếu bạn tìm trong trình soạn thảo Visual Basic Project Explorer bên dưới đầu đề VBAProject, bạn sẽ thấy một mô-đun có tên sau khi 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 Project Explorer sẽ 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, định vị điểm chèn của bạn sau khi điều khoản cuối hàm chấm dứt hàm cuối cùng trong cửa sổ mã, và bắt đầu nhập. Bạn có thể tạo nhiều hàm khi bạn cần theo cách này, và họ sẽ luôn có sẵn trong thể loại người dùng xác định trong hộp thoại Chèn hàm .

Nội dung này ban đầu đã được biên soạn bởi Mark tránh và Craig Stinson như là một phần của Microsoft Office Excel 2007 Inside Outcuốn sách của họ. Nó đã được Cập Nhật để áp dụng cho các phiên bản mới hơn của Excel cũng.

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.

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.

×