Trong công việc hành chính, chúng tôi thường phải kiểm tra các trang thiết bị, tài sản trong từng bộ phận. Làm sao chúng ta chỉ cần quét mã là có thể biết được sản phẩm còn tồn tại hay không? Chỉ cần kết hợp một chút công thức VBA và Excel là chúng ta có thể làm được. Hãy cùng Học Excel Online tìm hiểu cách thực hiện.
Cách tự động kiểm tra mã thuộc tính trong bảng quản lý thuộc tính VBA trong Excel
Mục tiêu
Hãy xem ví dụ dưới đây:
Trong hình, chúng ta có một bảng Mã tài sản. Kèm theo mỗi mã là thông tin của thuộc tính đó.
Khi nhập mã vào ô F2 (ô đánh dấu), bấm nút Kiểm tra, Excel sẽ tự động đánh dấu X tại vị trí mã tài sản trong cột C tương ứng với mã tài sản đó.
Hãy tưởng tượng nếu bảng mã tài sản của chúng ta có hàng trăm, hàng nghìn mã thì việc kiểm tra tự động này sẽ giúp chúng ta thao tác chính xác và tiết kiệm rất nhiều thời gian đúng không?
Đang làm
Trên Excel, bạn có thể nhập một biểu mẫu tương tự như nội dung như hình trên. Trong phạm vi bài viết này Học Excel Online sẽ hướng dẫn nội dung sau bao gồm hàm kiểm tra nội dung và lệnh Macro trong VBA để tự động kiểm tra.
Bước 1: Kiểm tra xem mã trong ô F2 có tồn tại trong cột Mã thuộc tính hay không
Cách kiểm tra rất đơn giản, chúng ta có thể sử dụng hàm COUNTIF như sau:
= COUNTIF ($ A $ 2: $ A $ 11, F2)
Trong đó:
- Khu vực được tính: Cột Mã BĐS (cột A), từ A2 đến A11 (tất cả nội dung nằm trong cột Mã BĐS)
- Điều kiện đếm: mã trong ô F2
Nếu hàm COUNTIF ở trên cho kết quả là một số> 0, mã trong ô F2 tồn tại và nếu kết quả = 0, mã không tồn tại.
Bước 2: Tìm dòng chứa mã nội dung nếu nó tồn tại
Trong trường hợp mã tồn tại, chúng tôi sẽ xem dòng nào trong bảng. Để làm điều này, chúng tôi sử dụng hàm MATCH như sau:
= MATCH (F2, $ A $ 1: $ A $ 11,0)
Trong đó:
- Giá trị cần tìm: Mã trong ô F2
- Vùng tìm kiếm: cột A, từ dòng đầu tiên đến dòng cuối cùng (hoặc có thể nhập $ A: $ A để chọn toàn bộ cột Mã BĐS)
- Phương pháp tìm kiếm: chính xác theo mã, nên sử dụng số 0
Hàm IF có thể được kết hợp để lập luận thêm về trường hợp mã trong ô F2 không tồn tại:
= IF (E4 = 0,0, MATCH (F2, $ A $ 1: $ A $ 11,0))
Nếu kết quả trong ô E4 = 0 (E4 là ô sử dụng hàm COUNTIF để kiểm tra xem mã có tồn tại hay không, kết quả = 0 là mã không tồn tại), thì trả về kết quả = 0 và kết quả tại E4 là khác 0 sau đó trả về kết quả của hàm MATCH.
Kết quả nhận được với mã thuộc tính trên là:
Bước 3: Viết mã trong VBA cho lệnh Kiểm tra
Mở cửa sổ VBA (phím tắt Alt + F11) và tạo một Mô-đun mới, trong Mô-đun đó tạo một Sub như sau:
Câu lệnh VBA trên có ý nghĩa:
- Dòng 2 và 3: Khai báo 2 biến CheckMa và SoDong
- Dòng 4 và 5: Xác định giá trị của 2 biến dựa trên kết quả của các hàm mà chúng ta đã làm ở trên
- Dòng 7, 8, 9: Trong trường hợp mã không tồn tại, chúng ta sẽ thoát khỏi thủ tục (Thoát Sub)
- Dòng 10, 11, 12: Trường hợp tồn mã (Else = còn lại) thì ta sẽ gán vào vị trí của cột C, hàng là giá trị của SoDong (ví dụ biến SoDong = 5 thì ta có ô C5 ), gán giá trị X đó
- Dòng 13: IF. cấu trúc kết thúc cú pháp
Do đó, nếu mã được kiểm tra về sự tồn tại, chúng tôi sẽ có thể tự động đánh dấu X trong cột Kiểm tra (cột C) tại dòng chính xác nơi mã được tìm thấy.
Bước cuối cùng là gán Macro KiemTra vào nút Test và hoàn thành công việc.
Bạn có thể tham khảo thêm một số nội dung liên quan về VBA:
Bắt đầu với Excel Macro và Lập trình VBA
Cách gán macro cho biểu tượng nút điều khiển được tạo bởi Shapes trong Excel
Nguồn : Blog hocexcel