Hướng dẫn write function in excel - viết hàm trong excel

Sau khi bạn đã lưu sổ làm việc, nhấp vào Công cụ> excel bổ trợ.

Trong hộp thoại bổ trợ, chọn nút Duyệt để tìm bổ trợ của bạn, nhấp vào Mở, sau đó chọn hộp bên cạnh bổ trợ của bạn trong hộp bổ trợ có sẵn. Visual Basic for Applications (VBA) programming language. They differ from macros in two significant ways. First, they use Function procedures instead of Sub procedures. That is, they start with a Function statement instead of a Sub statement and end with End Function instead of End Sub. Second, they perform calculations instead of taking actions. Certain kinds of statements, such as statements that select and format ranges, are excluded from custom functions. In this article, you’ll learn how to create and use custom functions. To create functions and macros, you work with the Visual Basic Editor (VBE), which opens in a new window separate from Excel.

Giả sử công ty của bạn cung cấp giảm giá 10 phần trăm cho việc bán sản phẩm, với điều kiện đơn đặt hàng là cho hơn 100 đơn vị. Trong các đoạn sau, chúng tôi sẽ trình bày một chức năng để tính toán giảm giá này.

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

Để tạo chức năng giảm giá tùy chỉnh trong sổ làm việc này, hãy làm theo các bước sau:

1. Nhấn Alt+F11 để mở Trình soạn thảo Visual Basic (trên Mac, nhấn FN+ALT+F11), sau đó nhấp vào Chèn> Mô -đun. Một 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. Alt+F11 to open the Visual Basic Editor (on the Mac, press FN+ALT+F11), and then click Insert > Module. A new module window appears on the right-hand side of the Visual Basic Editor.

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

   ________1

Lưu ý: & nbsp; Để làm cho mã của bạn dễ đọc hơn, bạn có thể sử dụng phím tab để các dòng thụt lề. Các thụt lề chỉ vì lợi ích của bạn, và là tùy chọn, vì 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ả sử dòng tiếp theo của bạn sẽ được thụt vào tương tự. Để di chuyển ra (nghĩa là bên trái) một ký tự tab, nhấn Tab+Tab.To make your code more readable, you can use the Tab key to indent lines. The indentation is for your benefit only, and is optional, as the code will run with or without it. After you type an indented line, the Visual Basic Editor assumes your next line will be similarly indented. To move out (that is, to the left) one tab character, press Shift+Tab.

Bây giờ bạn đã sẵn sàng để sử dụng chức năng giảm giá mới. Đóng Trình chỉnh sửa Visual Basic, chọn ô G7 và nhập các mục sau:

=DISCOUNT(D7,E7)

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

Trong dòng đầu tiên của mã VBA, giảm giá chức năng (số lượng, giá cả), bạn chỉ ra rằng chức năng giảm giá yêu cầu hai đối số, số lượng và giá cả. Khi bạn gọi chức năng trong ô bảng tính, bạn phải bao gồm hai đối số đó. Trong Công thức = Giảm giá (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 giảm giá thành G8: G13 để có được kết quả được hiển thị bên dưới.

Hãy cùng xem xét cách Excel diễn giải quy trình chức năng này. Khi bạn nhấn Enter, Excel tìm kiếm giảm giá tên trong sổ làm việc hiện tại và thấy rằng đó là một chức năng tùy chỉnh trong mô -đun VBA. Các tên đối số được đặt trong ngoặc đơn, số lượng và giá cả, là người giữ chỗ cho các giá trị mà tính toán chiết khấu được dựa trên.Enter, Excel looks for the name DISCOUNT in the current workbook and finds that it is a custom function in a VBA module. The argument names enclosed in parentheses, quantity and price, are placeholders for the values on which the calculation of the discount is based.

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

Giảm giá = Số lượng * Giá * 0,1

Kết quả được lưu trữ dưới dạng giảm giá biến. Một câu lệnh VBA lưu trữ một giá trị trong một biến được gọi là câu lệnh gán, bởi vì nó đánh giá biểu thức ở phía 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. Bởi vì chiết khấu biến có cùng tên với quy trình chức năng, giá trị được lưu trữ trong biến được trả lại cho công thức bảng tính được gọi là hàm giảm giá.

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

Giảm giá = 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 giá cho hai vị trí thập phân:

Giảm giá = application.Round (giảm giá, 2)

VBA không có chức năng tròn, nhưng Excel thì có. Do đó, để sử dụng vòng trong câu lệnh này, bạn bảo VBA tìm phương thức tròn (hàm) trong đối tượng ứng dụng (Excel). Bạn làm điều đó bằng cách thêm ứng dụng Word trước vòng. Sử dụng cú pháp này bất cứ khi nào bạn cần truy cập chức năng Excel 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 chức năng và kết thúc với một câu lệnh Hàm cuối. Ngoài tên hàm, câu lệnh hàm thường chỉ định một hoặc nhiều đối số. Tuy nhiên, bạn có thể tạo ra một chức năng không có đối số. Excel bao gồm một số chức năng tích hợp trong các chức năng và bây giờ, ví dụ, ví dụ như không sử dụng các đối số.

Theo báo cáo chức năng, một quy trình chức năng 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 tính toán bằng cách sử dụng các đối số được truyền cho hàm. Cuối cùng, ở đâu đó trong thủ tục chức năng, bạn phải bao gồm một câu lệnh gán giá trị cho một biến có cùng tên với hàm. Giá trị này được trả lại 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. Các chức năng tùy chỉnh không được phép làm bất cứ điều gì khác ngoài việc trả lại giá trị cho một công thức trong bảng tính hoặc biểu thức được sử dụng trong một macro hoặc hàm VBA khác. Ví dụ: các chức năng tùy chỉnh không thể thay đổi kích thước Windows, chỉnh sửa công thức trong ô hoặc thay đổi tùy chọn phông chữ, màu sắc hoặc mẫu cho văn bản trong ô. Nếu bạn bao gồm mã hành động của loại này thuộc loại này trong một quy trình chức năng, hàm sẽ trả về #Value! lỗi.

Một hành động mà một quy trình chức năng có thể thực hiện (ngoài việc thực hiện tính toán) là 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 để nhận đầu vào từ người dùng thực hiện chức năng. Bạn có thể sử dụng câu lệnh MSGBOX như một phương tiện truyền tải thông tin 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 người dùng, nhưng đó là một chủ đề vượt quá phạm vi của phần giới thiệu này.InputBox statement in a custom function as a means of getting input from the user executing the function. You can use a MsgBox statement as a means of conveying information to the user. You can also use custom dialog boxes, or UserForms, but that’s a subject beyond the scope of this introduction.

Ngay cả các macro đơn giản và các chức năng tùy chỉnh cũng có thể khó đọc. Bạn có thể làm cho chúng dễ hiểu hơn bằng cách gõ văn bản giải thích dưới dạng bình luận. Bạn thêm nhận xét bằng cách trước văn bản giải thích với dấu nháy đơn. Ví dụ, ví dụ sau đây cho thấy chức năng giảm giá với nhận xét. Thêm nhận xét như thế này giúp bạn hoặc người khác dễ dàng duy trì mã VBA của bạn hơn khi thời gian trôi qua. Nếu bạn cần thực hiện thay đổi mã trong tương lai, bạn sẽ có một thời gian dễ dàng hơn để hiểu những gì bạn đã làm ban đầu.

Một dấu nháy đơn bảo Excel bỏ qua mọi thứ ở bên phải trên cùng một dòng, do đó bạn có thể tự tạo các nhận xét trên các dòng hoặc ở phía bên phải của các dòng chứa mã VBA. Bạn có thể bắt đầu một khối mã tương đối dài với một nhận xét giải thích mục đích chung của nó và sau đó sử dụng các bình luận nội tuyến để ghi lại các câu lệnh cá nhân.

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

Cách bạn ghi lại các macro và chức năng tùy chỉnh của bạn là vấn đề sở thích 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.

Để sử dụng chức năng tùy chỉnh, sổ làm việc chứa mô -đun mà bạn tạo chức năng phải được mở. Nếu sổ làm việc đó không mở, bạn sẽ nhận được #Name? Lỗi khi bạn cố gắng sử dụng chức năng. Nếu bạn tham chiếu chức năng trong một sổ làm việc khác, bạn phải đi trước tên chức năng với tên của sổ làm việc trong đó chức năng cư trú. Ví dụ: nếu bạn tạo một hàm có tên là giảm giá trong sổ làm việc có tên cá nhân.xlsb và bạn gọi chức năng đó từ một sổ làm việc khác, bạn phải gõ = cá nhân.xlsb! Giảm giá (), không chỉ đơn giản là = giảm giá ().=personal.xlsb!discount(), not simply =discount().

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

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

1. Sau khi bạn đã tạo các chức năng bạn cần, nhấp vào Tệp> Lưu dưới dạng.File > Save As.

   Trong Excel 2007, nhấp vào nút Microsoft Office và nhấp vào Lưu dưới dạngMicrosoft Office Button, and click Save As

2. Trong hộp thoại Lưu dưới dạng, hãy mở danh sách thả xuống dưới dạng loại và chọn add-in Excel. Lưu sổ làm việc dưới một tên dễ nhận biết, chẳng hạn như MyFifts, trong thư mục Addins. Hộp thoại Save As 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.Save As dialog box, open the Save As Type drop-down list, and select Excel Add-In. Save the workbook under a recognizable name, such as MyFunctions, in the AddIns folder. The Save As dialog box will propose that folder, so all you need to do is accept the default location.

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

   Trong Excel 2007, nhấp vào nút Microsoft Office và nhấp vào Tùy chọn Excel.Microsoft Office Button, and click Excel Options.

4. Trong hộp thoại Tùy chọn Excel, nhấp vào danh mục bổ trợ.Excel Options dialog box, click the Add-Ins category.

5. Trong danh sách trình bày quản lý, chọn excel bổ sung. Sau đó nhấp vào nút GO.Manage drop-down list, select Excel Add-Ins. Then click the Go button.

6. Trong hộp thoại bổ trợ, chọn hộp kiểm bên cạnh tên 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.Add-Ins dialog box, select the check box beside the name you used to save your workbook, as shown below.

1. Sau khi bạn đã tạo các chức năng bạn cần, nhấp vào Tệp> Lưu dưới dạng.File > Save As.

2. Trong Excel 2007, nhấp vào nút Microsoft Office và nhấp vào Lưu dưới dạngSave As dialog box, open the Save As Type drop-down list, and select Excel Add-In. Save the workbook under a recognizable name, such as MyFunctions.

3. Trong hộp thoại Lưu dưới dạng, hãy mở danh sách thả xuống dưới dạng loại và chọn add-in Excel. Lưu sổ làm việc dưới một tên dễ nhận biết, chẳng hạn như MyFifts, trong thư mục Addins. Hộp thoại Save As 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.Tools > Excel Add-Ins.

4. Sau khi bạn đã lưu sổ làm việc, nhấp vào Tệp> Tùy chọn Excel.Add-Ins dialog box, select the Browse button to find your add-in, click Open, then check the box beside your Add-In in the Add-Ins Available box.

Trong Excel 2007, nhấp vào nút Microsoft Office và nhấp vào Tùy chọn Excel.

Trong hộp thoại Tùy chọn Excel, nhấp vào danh mục bổ trợ.Insert Function dialog box.

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

Cần sự giúp đỡ nhiều hơn?

Bạn luôn có thể hỏi một chuyên gia trong cộng đồng công nghệ Excel & nbsp; hoặc nhận được hỗ trợ trong cộng đồng câu trả lời.