Hướng dẫn cách tự động kiểm tra mã tài sản trong bảng quản lý tài sản Excel VBA

Chuyên mục:
Excel VBA

Trong công việc hành chính, chúng ta thường hay phải đi kiểm tra các thiết bị, tài sản trong từng phòng ban. Làm thế nào để có thể chỉ cần quét mã là chúng ta biết sản phẩm đó có tồn tại hay không? Chỉ cần kết hợp một chút VBA và công thức Excel là chúng ta có thể làm được rồi. Hãy cùng Học Excel Online tìm hiểu cách làm nào.

Xem nhanh

Cách tự động kiểm tra mã tài sản trong bảng quản lý tài sản Excel VBA

Mục tiêu

Chúng ta cùng xem ví dụ dưới đây:

Trong hình, ta có 1 bảng Mã tài sản. Kèm theo mỗi mã là thông tin của tài sản đó.

Khi nhập mã vào ô F2 (ô check mã), bấm vào 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 thử hình dung nếu bảng mã tài sản của chúng ta có tới 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 làm việc chính xác và tiết kiệm rất nhiều thời gian phải không nào.

Cách thực hiện

Trên Excel, các bạn có thể nhập mẫu tương tự các 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 các phần nội dung phía sau, bao gồm hàm kiểm tra nội dung và lệnh Macro trong VBA để tự động hóa việc kiểm tra.

Bước 1: Kiểm tra xem mã tại ô F2 có tồn tại trong cột Mã tài sản 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 đó:

Vùng cần đếm: Cột Mã tài sản (cột A), từ A2 tới A11 (toàn bộ nội dung có trong cột Mã tài sản)
Điều kiện đếm: mã tại ô F2

Nếu kết quả hàm COUNTIF trên ra số > 0 thì mã tại ô F2 có tồn tại, còn kết quả = 0 thì mã không tồn tại

Bước 2: Tìm dòng chứa mã tài sản đó nếu mã có tồn tại

Trong trường hợp mã có tồn tại, chúng ta sẽ xét xem mã đó nằm ở dòng thứ mấy trong bảng. Để làm điều này chúng ta dùng hàm MATCH như sau:

=MATCH(F2,$A$1:$A$11,0)

Trong đó:

Giá trị cần tìm: Mã tại ô F2
Vùng tìm kiếm: cột A, từ dòng thứ 1 tới dòng cuối (hoặc có thể nhập $A:$A để chọn toàn bộ cột Mã tài sản)
Phương thức tìm kiếm: chính xác theo mã, do đó sử dụng số 0

Có thể kết hợp thêm hàm IF để biện luận thêm 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ả tại ô E4 = 0 (E4 là ô sử dụng hàm COUNTIF kiểm tra 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, còn kết quả tại E4 khác 0 thì trả về kết quả của hàm MATCH.

Kết quả thu được với mã tài sản trên là:

Bước 3: Viết code trong VBA cho lệnh Kiểm tra

Mở cửa sổ VBA (phím tắt Alt + F11) và tạo 1 Module mới, trong Module đó tạo 1 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 vào kết quả của các hàm chúng ta đã làm ở trên
Dòng 7, 8, 9: Trường hợp mã không tồn tại, chúng ta sẽ thoát thủ tục (Exit Sub)
Dòng 10, 11, 12: Trường hợp mã có tồn tại (Else = còn lại), chúng ta sẽ gán vào vị trí cột C, dòng là giá trị SoDong (ví dụ biến SoDong = 5 thì ta có ô C5), gán vào đó giá trị X
Dòng 13: Cú pháp kết thúc cấu trúc IF

Như vậy, nếu check được mã có tồn tại, chúng ta sẽ có thể tự động đánh dấu X vào cột Kiểm tra (cột C) tại đúng dòng tìm thấy mã đó.

Bước cuối cùng là gán Macro KiemTra vào nút lệnh Kiểm tra và hoàn thành công việc.