Hàm excel vba
|
Khi sử dụng Excel, chúng ta thấy có rất nhiều hàm được viết sẵn. Nhưng còn nhiều chức năng có đủ để phục vụ cho công việc của bạn. Bạn cần một chức năng thích hợp với công việc của mình hơn tại sao ta không tự viết chúng. Bài viết này sẽ hướng dẫn bạn cách viết hàm trong Excel đơn giản nhất Xem bài học trước >> VBA Excel Cấp Tốc Bài 1 - Lọc Dữ Liệu Nhiều Điều Kiện Với Bộ Lọc Nâng Cao >> VBA Excel Cấp Tốc Bài 2 - Trong Bảng Cửu Chương >> VBA Excel Cấp Tốc Bài 3 - In Nhanh Hàng Chuỗi >> VBA Excel Cấp Tốc Bài 4 - Xóa Tự Động Hàng Chuỗi Trang Tính Không Hoạt Động >> VBA Excel Cấp Tốc Bài 5 - Copy File Từ Một Thư Mục Sang Thư Mục Khác >> VBA Excel Cấp Tốc Bài 6 - Hướng Dẫn Sử Dụng Record Macro Trong Excel Từ Cơ Bản Đến Nâng Cao >> VBA Excel Cấp Tốc Bài 7 - Cách Gộp Nhiều File Excel Vào Một File Duy Nhất >> VBA Excel Cấp Tốc Bài 8 - Cách Tạo Checkbox Hàng Chuỗi Trong Excel hướng dẫn videoViết chuỗi nối chuỗiĐầu tiên là một File trắng bạn cần phải lưu File trước khi thực hiện. vào thẻ Tệp ở gốc trên màn hình -> Lưu dưới dạng -> chọn nơi lưu Tệp -> đặt tên -> ô bên dưới ô đặt tên chọn Excel Macro -> Lưu  Open Visual Basic up. Vào Nhà phát triển -> Visual Basic -> Chèn -> Mô-đun Mở đầu chương trình là. Hàm đặt tên chương trình(chuỗi As Range, các ký tự giữa các chuỗi dưới dạng chuỗi) Enter xuống dòng tự sinh ra End Function. Tiếp theo trong vòng lặp Đối với bạn viết chuỗi kết nối lệnh. tên chương trình = tên chương trình và khai báo mỗi ký tự ban đầu và ký tự giữa các chuỗi. vd. noi_chuoi = noi_chuoi & moi_ky_tu & ky_tu_giua_cac_chuoi Nhưng khi hoàn thành vòng lặp For thì bạn sẽ dư một chuỗi ký tự kết nối ở cuối để xóa nó đi khi sử dụng lệnh. noi_chuoi = Trái(noi_chuoi, Len(noi_chuoi) - Len(ky_tu_giua_cac_chuoi)). Trong đó Left là hàm xóa còn Len là hàm tính độ dài Giờ bạn có thể sử dụng hàm noi_chuoi bằng cách bạn tích vào 1 ô bạn muốn chứa chuỗi đó gõ noi_chuoi(bôi đen vùng dữ liệu muốn kết nối vào nhau;"khoản cách giữa các chuỗi sắp được kết nối vào nhau") Đoạn mã nối chuỗi chuỗi
Kết quả Viết hàm tính tổngĐầu tiên ta cũng bắt đầu với tên chương trình. Function tên chương trình(chuỗi As String) Enter down line character sinh ra End Function. Gán giá trị cho tinh_tong bằng không(tinh_tong = 0), khai báo biến i để tách và quét từ ký tự đầu tiên đến ký tự cuối cùng. Dim i dưới dạng số nguyên Khai báo thêm hàng từng ký tự để mình tính tổng. Dim tung_ky_tu As String Tiếp theo là vòng lặp For để cho phép tôi quét từng ký tự trong chuỗi. For i = 1 To Len(chuoi) and Next to end loop Tiếp theo mình sẽ gán tung_ky_tu bằng mid(chuoi, i, 1). mid function is to parse từng ký tự trong chuỗi. Vd tung_ky_tu = Mid(chuoi, i, 1) Mặc dù Excel tích hợp nhiều hàm trang tính sẵn có, 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 yêu cầu 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 tùy chỉnh hàm, được giải thích trong bài viết này Nội dung chính Hiển thị
Các tùy chỉnh chức năng, chẳng hạn như macro, sử dụng trình thiết lập Visual Basic for Applications (VBA). Chúng khác với macro theo hai cách quan trọng. Trước tiên, họ sử dụng chức năng Thủ tục 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 được định rõ nhất, không có giới hạn như câu lệnh được chọn và định dạng phạm vi vi, sẽ được loại trừ khỏi tùy chọn hàm. Trong bài viết này, bạn sẽ tìm hiểu cách tạo và sử dụng tùy chỉnh hàm. Để 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 cao 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 chức năng DISCOUNT tùy chỉnh trong cửa sổ làm việc này, hãy làm theo các bước sau
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ó là Visual Basic, Trình soạn thảo Visual Basic giả định dòng tiếp theo của bạn sẽ giống nhau như nhau. Để chuyển ra bên ngoài (nghĩa là, sang trái) một ký tự tab, nhấn Shift+Tab Bây giờ bạn đã có sẵn sử dụng chức năng GIẢM GIÁ mới. Đóng Trình Thảo luận Visual Basic, chọn ô G7, rồi nhập nội dung sau =GIẢM GIÁ(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ả lại $950,00 Trong dòng thứ nhất của mã VBA, Hàm DISCOUNT(số lượng, giá), bạn chỉ đưa 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 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 GIẢM GIÁ sang G8. G13 để được kết quả như hình bên dưới Please xem xét cách xác định quy trình của hàm Excel. Khi bạn nhấn Enter, Excel tìm tên GIẢM GIÁ trong cửa sổ làm việc hiện tại và thấy rằng đó là một hàm tùy chỉnh trong một mô-đun VBA. Tên đối số được đặt trong đơn đặt dấu trích dẫn, số lượng và giá cả, là khoảng 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 Nếu trong khối mã sau đây kiểm tra đối số lượng và xác định xem lượng mặt hàng đã bán lớn hơn hoặc bằng 100 Nếu số lượng >= 100 Thì GIẢM GIÁ = số lượng * giá * 0. 1 Khác GIẢM GIÁ = 0 Kết thúc NếuNếu số lượng mặt hàng đã bán lớn hơn hoặc bằng 100, VBA thực hiện câu lệnh sau đây, nhân giá trị số lượng với giá trị, 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 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ụ, bởi 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 giảm giá có cùng tên với hàm thủ tục, 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 GIẢM GIÁ Nếu lượng nhỏ hơn 100, VBA thực hiện câu lệnh sau đây 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 chiết khấu thành hai vị trí tổng hợp Giảm giá = Ứng dụng. Vòng(Giảm giá, 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 (hàm) trong các đối tượng Ứng dụng (hàm Excel). Bạn làm điều đó bằng cách thêm từ Ứng dụng trước từ Vòng. Cú pháp sử dụng này bất kể khi nào bạn cần truy cậ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 Chức năng và kết thúc bằng câu lệnh Kết thúc Chức năng. 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 một hàm không có tham số. Excel bao gồm một số chức năng xây dựng sẵn— không giới hạn như RAND và NOW—không sử dụng các đố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, ở bất kỳ vị trí nào trong hàm thủ tục, bạn phải đưa vào 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 tùy chỉnh hàm 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ị cho 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 tùy chỉnh chức năng không thể thay đổi kích thướ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 sắc 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 hàm thủ tục, hàm sẽ trả về giá trị #VALUE. lỗi Một hành động mà một hàm thủ tục có thể thực hiện (ngoài công việc thực hiện tính toán) được 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 bắt đầu 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ể sử dụng hộp thoại tùy chỉnh hoặc Biểu mẫu người dù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 họ 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 flash menu trước văn bản giải thích. Ví dụ. ví dụ sau đây để thấy chức năng GIẢM GIÁ 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 Flashing đơ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 cho 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ó, sau đó sử dụng chú thích tại dòng để ghi lại các lệnh riêng lẻ Một cách khác để ghi lại macro và tùy chỉnh hàm của bạn là cung cấp cho họ tên mang tính mô tả. Ví dụ. thay vì đặt tên cho macro nhãn,bạn có thể đặt tên cho nhãn Tháng để mô tả cụ thể hơn về mục tiêu mà macro phục vụ. Việc sử dụng tên mô tả cho macro và tùy chỉnh hàm đặ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 tùy chỉnh chức năng 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 quán nhất Để sử dụng chức năng tùy chỉnh, cửa sổ làm việc chứa mô-đun bạn đã tạo chức năng phải được mở. Nếu cửa sổ đang làm việc đó không mở, bạn sẽ nhận được thông báo #NAME? . Nếu bạn tham chiếu hàm trong cửa sổ làm việc khác, bạn phải đặt tên hàm đó trước tên cửa 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 cửa sổ làm việc có tên là Cá nhân. xlsb and you call that function from other working window, you must enter =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 chức năng tùy chỉnh của bạn từ hộp thoại Chèn Hàm. Hàm chỉnh sửa 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 tùy chỉnh chức năng của bạn luôn có sẵn được sử dụng là lưu trữ chúng trong một cửa 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 phần bổ trợ có sẵn bất cứ khi nào bạn chạy phần Excel. Dưới đây là cách thực hiện
Sau khi bạn làm theo các bước này, các tùy chỉnh chức năng của bạn sẽ có sẵn mỗi khi bạn chạy Excel. Nếu bạn muốn thêm vào thư viện chức năng của mình, hãy quay lại Trình biên 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 tiê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 nhấp đúp vào mô-đun đó trong Trình khám phá dự án sẽ khiến Trình Visual Basic hiển thị mã hàm của bạn. Để thêm chức năng mới, hãy đặt điểm chèn của bạn sau câu lệnh Kết thúc Hàm chức năng dấu chấm cuối cùng trong cửa sổ Mã, sau đó 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 Nội dung ban đầu này được tạo bởi Mark Dodge và Craig Stinson như một phần trong cuốn sách của họ Microsoft Office Excel 2007 Inside Out. Kể từ đó, tính năng này cũng đã được cập nhật để áp dụng cho các phiên bản Excel mới hơn Bạn cần thêm trợ giúp?Bạn luôn có thể hỏi một chuyên gia trong Cộng đồng Kỹ thuật Excel hoặc nhận sự hỗ trợ trong Cộng đồng trả lời |
