Tạo hàm mới trong excel
Mặc Excel tích hợp nhiều hàm trang tính tích hợp sẵn, nhưng có khả năng là nó không có hàm cho mọi loại tính toán bạn thực hiện. Người thiết kế Excel không thể dự đoán được từng nhu cầu tính toán của người dùng. Thay vào đó, Excel cấp cho bạn khả năng tạo hàm tùy chỉnh, được giải thích trong bài viết này.
Bạn đang xem: Tạo hàm mới trong excel
Các hàm tùy chỉnh, như macro, sử Visual Basic for Applications lập trình (VBA). Chúng khác với macro theo hai cách quan trọng. Trước tiên, họ sử dụng Thủ tục hàm thay vì Thủ tục con. Nghĩa là, chúng bắt đầu bằng câu lệnh Function thay vì câu lệnh Sub và kết thúc bằng Hàm End thay vì End Sub. Thứ hai, chúng thực hiện các phép tính thay vì thực hiện hành động. Một số loại câu lệnh nhất định, chẳng hạn như câu lệnh chọn và định dạng phạm vi, sẽ đượ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 hàm và macro, bạn sẽ làm việc với Trình soạn thảo Visual Basic (VBE),sẽ mở ra trong một cửa sổ mới tách biệt với Excel.
Giả sử công ty bạn đưa ra mức chiết khấu 10 phần trăm khi bán sản phẩm, với điều kiện đơn hàng là hơn 100 đơn vị. Trong các đoạn văn sau, chúng tôi sẽ minh họa một hàm để tính toán khoả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ặt hàng, số lượng, giá, chiết khấu (nếu có) và giá mở rộng kết quả.
Để tạo hàm DISCOUNT tùy chỉnh trong sổ làm việc này, hãy làm theo các bước sau:
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 vào Chèn > Module. Một cửa sổ mô-đun mới sẽ xuất hiện ở phía bên phải của Trình Visual Basic thảo.
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 ý: Để làm cho mã của bạn dễ đọc hơn, bạn có thể sử dụng phím Tab để nhập các dòng trong dòng. Việc thu nhập chỉ dành cho lợi ích của bạn và là tùy chọn, vì mã sẽ chạy kèm theo hoặc không kèm theo. Sau khi bạn nhập một dòng đã được Visual Basic, Trình soạn thảo Visual Basic giả định dòng tiếp theo của bạn sẽ được như nhau tương tự như nhau. Để di chuyển ra ngoài (nghĩa là, sang 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 Visual Basic Thảo, chọn ô G7, rồi nhập nội dung sau:
=DISCOUNT(D7,E7)
Excel tính toán chiết khấu 10 phần trăm cho 200 đơn vị với $47,50 mỗi đơn vị và trả về $950,00.
Trong dòng thứ nhất của mã VBA, Function DISCOUNT(quantity, price), bạn đã chỉ ra rằng hàm DISCOUNT yêu cầu hai đối số, số lượng và giá. Khi bạn gọi hàm trong một ô trang tính, bạn phải bao gồm hai tham đối đó. Trong công thức =DISCOUNT(D7;E7), D7 là đối số quantity và E7 là đối số giá. Now you can copy the DISCOUNT formula to G8:G13 to get the results shown below.
Hãy xem xét cách thức Excel quy trình của hàm này. Khi bạn nhấn Enter, Excel tìm tên DISCOUNT 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. Tên đối số được đặt trong dấu ngoặc đơn, số lượng và giá ,là chỗ dành sẵn cho các giá trị mà tính toán chiết khấu dựa trên đó.
Câu lệnh If trong khối mã sau đây kiểm tra đối số số lượng và xác định xem số lượng mặt hàng đã bán lớn hơn hoặc bằng 100:
If quantity >= 100 Then DISCOUNT = quantity * price * 0.1Else DISCOUNT = 0End If Nếu số mặt hàng đã bán lớn hơn hoặc bằng 100, VBA thực thi câu lệnh sau đây, nhân giá trị số lượng với giá trị giá, rồi nhân kết quả với 0,1:
Discount = quantity * price * 0.1
Kết quả được lưu dưới dạng biến Chiết khấu. Câu lệnh VBA lưu trữ giá trị trong biến được gọi là câu lệnh nhiệm vụ, vì câu lệnh này đánh giá biểu thức ở bên phải dấu bằng và gán kết quả cho tên biến ở bên trái. Vì biến Discount có cùng tên với thủ tục hàm, giá trị được lưu trữ trong biến được trả về cho công thức trang tính có tên là hàm DISCOUNT.
Nếu số lượng nhỏ hơn 100, VBA thực thi 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 Chiết khấu thành hai vị trí thập phân:
Discount = Application.Round(Discount, 2)
VBA không có hàm ROUND nhưng hàm Excel đúng. Do đó, để sử dụng ROUND trong câu lệnh này, bạn cần yêu cầu VBA tìm kiếm phương pháp Round (function) trong đối tượng Application (hàm Excel). Bạn làm điều đó bằng cách thêm từ Application trước từ Round. Sử dụng cú pháp này bất cứ khi nào bạn cần truy nhập Excel từ mô-đun VBA.
Một hàm tùy chỉnh phải bắt đầu bằng câu lệnh Function và kết thúc bằng câu lệnh End Function. Ngoài tên hàm, câu lệnh Function thường chỉ đị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ó tham đối. Excel bao gồm một số hàm dựng sẵn—chẳng hạn như RAND và NOW—không sử dụng đối số.
Theo sau câu lệnh Hàm, một thủ tục hàm bao gồm một hoặc nhiều câu lệnh VBA giúp đưa ra quyết định và thực hiện tính toán bằng các đối số được đưa vào hàm. Cuối cùng, ở vị trí nào đó trong thủ tục hàm, bạn phải đưa vào câu lệnh gán giá trị cho biến 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 bạn có thể sử dụng trong các hàm tùy chỉnh nhỏ hơn số mà bạn có thể sử dụng trong macro. Các hàm tùy chỉnh không được phép làm bất kỳ điều gì khác ngoài việc trả giá trị về một công thức trong trang tính, hoặc một biểu thức được sử dụng trong một macro hoặc hàm VBA khác. Ví dụ: các hàm tùy chỉnh không thể đổi kích cỡ cửa sổ, sửa công thức trong một ô hoặc thay đổi các 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 đưa mã "hành động" của loại này vào một thủ tục hàm, hàm sẽ trả về giá #VALUE! lỗi.
Một hành động mà một thủ tục hàm có thể thực hiện (ngoài việc thực hiện tính toán) là hiển thị một 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 làm phương tiện nhận dữ liệu đầu vào từ người dùng thực hiện hàm. Bạn có thể sử dụng câu lệnh MsgBox làm phương tiện truyền tải 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ườidùng , nhưng đó là một chủ đề nằm ngoài phạm vi của phần giới thiệu này.
Ngay cả macro đơn giản và hàm 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 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 đặt một nháy đơn trước văn bản giải thích. Ví dụ: ví dụ sau đây cho thấy hàm DISCOUNT có chú thích. Việc thêm chú thích như thế này sẽ giúp bạn hoặc những người khác dễ dàng duy trì mã VBA của bạn khi hết thời gian. Nếu bạn cần thay đổi mã trong tương lai, bạn sẽ hiểu rõ hơn về những gì mình đã thực hiện lúc đầu.
Xem thêm: Tất Cả Ý Nghĩa Của Hoa Hướng Dương Trong Cuộc Sống Và Tình Yêu
Dấu nháy đơn yêu cầu Excel qua tất cả mọi thứ ở bên phải trên cùng một dòng để bạn có thể tạo chú thích một mình trên dòng hoặc ở bên phải 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 chú thích giải thích mục đích chung của nó, rồi sử dụng chú thích tại dòng để ghi lại các câu lệnh riêng lẻ.
Một cách khác để ghi lại macro và hàm tùy chỉnh của bạn là cung cấp cho chúng tên mang tính mô tả. Ví dụ: thay vì đặt tên cho nhãn macro,bạn có thể đặt tên cho nhãn Tháng để mô tả cụ thể hơn về mục đích mà macro phục vụ. Việc sử dụng tên mô tả cho macro và 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 khi bạn tạo quy trình có mục đích tương tự nhưng không giống nhau.
Cách bạn ghi lại macro và các hàm tùy chỉnh là một vấn đề bạn ưu tiên cá nhân. Điều quan trọng là phải áp dụng một số phương pháp hướng dẫn sử dụng và sử dụng tài liệu một cách nhất quán.
Để sử dụng hàm tùy chỉnh, sổ làm việ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ở, bạn sẽ nhận được thông báo #NAME? khi bạn tìm cách sử dụng hàm. Nếu bạn tham chiếu hàm trong sổ làm việc khác, bạn phải đặt tên hàm đó trước tên sổ làm việc có chứa 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 đó từ sổ làm việc khác thì bạn phải nhập =personal.xlsb!discount()chứ không chỉ =discount().
Bạn có thể tự lưu một số tổ hợp phím (và các lỗi nhập có thể xảy ra) bằng cách chọn hàm tùy chỉnh của bạn từ 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 Do người dùng Xác định:
Cách dễ dàng hơn để giúp các hàm tùy chỉnh của bạn luôn sẵn dùng là lưu trữ chúng trong một sổ làm việc riêng, 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 bổ trợ sẵn dùng bất cứ khi nào bạn chạy phần Excel. Dưới đây là cách thực hiện:
WindowsmacOS
Sau khi bạn đã tạo các hàm mình cần, hãy bấm Tệp > Lưu Dưới dạng.
Trong Excel 2007, bấm vào nút worldlinks.edu.vn Office ,rồi bấm vào Lưu Như
Trong hộp thoại Lưu Như, mở danh sách thả xuống Lưu Dưới dạng, rồi chọn Excel Bổ trợ. Lưu sổ làm việc dưới tên có thể nhận ra, 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.
Sau khi bạn đã lưu sổ làm việc, hãy bấm Tệp > Excel chọn.
Trong Excel 2007, bấm vào nút worldlinks.edu.vn Office ,rồi bấm vào Tùy Excel chọn.
Trong hộp Excel Chọn, bấm vào thể loại Bổ trợ.
Trong danh sách thả xuống Quản lý, chọn Excel Bổ trợ. Sau đó bấm nút Đi.
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.
Sau khi bạn đã tạo các hàm mình cần, hãy bấm Tệp > Lưu Dưới dạng.
Trong hộp thoại Lưu Như, mở danh sách thả xuống Lưu Dưới dạng, rồi chọn Excel Bổ trợ. Lưu sổ làm việc dưới tên có thể nhận ra, chẳng hạn như MyFunctions.
Sau khi bạn đã lưu sổ làm việc, hãy bấm Công > Excel Bổ trợ.
Trong hộp thoại Phần bổ trợ, chọn nút Duyệt để tìm phần bổ trợ của bạn, bấm vào Mở ,rồi đánh dấu chọn vào hộp bên cạnh Add-In 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 khi 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 Visual Basic thảo Tài liệu. Nếu bạn nhìn vào 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 tên theo tệp bổ trợ của bạn. Phần bổ trợ của bạn sẽ có phần mở rộng .xlam.
Việc bấm đúp vào mô-đun đó trong Trình Project Explorer sẽ khiến Trình Visual Basic hiển thị mã hàm của bạn. Để thêm hàm mới, hãy đặt điểm chèn của bạn sau câu lệnh End Function 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 bao nhiêu hàm tùy ý theo cách này và chúng sẽ luôn sẵn dùng trong thể loại Người dùng Xác định trong hộp thoại Chèn Hàm.
