Đối chiếu số vận đơn bằng Excel

Hôm nay ngày 11 tháng 3 năm 2011
1-Hướng dẫn thiết lập hàm để kiểm tra, đối chiếu vận đơn bằng Excel.
Đối với các cơ quan kinh doanh hàng ngày phải gửi rất nhiều các vận đơn qua bưu điện tới khách hàng, sau một tháng lượng vận đơn rất lớn, một công việc rất tốn thời gian và công sức là kiểm tra đối chiếu danh sách các vận đơn đã gửi giữa cơ quan với Bưu điện, mỗi bên nhập vận đơn không theo thứ tự và nhiều người nhập nên có thể xảy ra tình trạng nhập trùng, nhập thiếu, nhập sai số vận đơn. Sau một thời gian nghiên cứu yêu cầu của 1 cơ quan tôi đã giải quyết rất thành công với một hàm trong Excel xin được chia sẻ cùng bạn đọc.
Bước 1: Thiết lập bảng tính Excel gồm các cột như sau
Số TT, Họ và tên, Phòng, Số vận đơn, Ngày gửi, Số lượng, Trọng lượng, Người nhận, địa chỉ ta gọi là Bên A.

+Ở bên phải của bảng ta thiết lập 1 cột gọi là Bên B và 4 cột phụ như hình dưới

Bước 2: Thiết lập hàm cho cột K, L, M, N.
+Cột J là nơi ta dán số liệu vận đơn của Bưu điện bắt đầu từ ô J4, nhìn vào ví dụ chúng ta thấy số vận đơn nhập tùy tiện lung tung không theo 1 trật tự nào.
+Cột K có chữ KT1 (Kiểm tra 1) tại ô K4 ta nhập hàm =IF(J4="","",COUNTIF($D$4:$D$10000,J4))
Giải thích hàm: Hàm này sẽ kiểm tra mã Bưu điện nhập với mã của cơ quan nhập ở cột D, nếu số vận đơn cần kiểm tra bên Bưu điện đã có ở cột D thì cột KT1 sẽ để giá trị khác 0 (Tương ứng với số mã kiểm tra được nhập tại cột D). Nếu bằng 0 tức mã tại cột D chưa có.
+Cột L có chữ A thiếu tại ô L4 ta nhập hàm =IF(K4=0,J4,"")
+Cột M có chữ KT2 (Kiểm tra 2) tại ô M4 ta nhập hàm
=IF(D4="","",COUNTIF($J$4:$J$10000,D4))
Giải thích hàm: Hàm này sẽ kiểm tra mã Cột D nhập với mã của cơ quan nhập ở cột Bưu điện, nếu số vận đơn cần kiểm tra bên Cột D đã có ở cột Bưu điện thì cột KT2 sẽ để giá trị khác 0 (Tương ứng với số mã kiểm tra được nhập tại cột Bưu điện). Nếu bằng 0 tức mã tại cột Bưu điện chưa có.
+Cột N có chữ B thiếu tại ô M4 ta nhập hàm =IF(M4=0,D4,"")
+Bạn sao chép công thức của ô K4-M4-N4 xuống các ô khác trong mỗi cột .

Bước 3: Thiết lập hàm cảnh báo nhập trùng vận đơn tại cột D.
+Để cảnh báo việc nhập trùng vận đơn khi nhập ở cột D ta bôi đen các ô của cột D sau đó nháy vào Data \ Validation, chọn nhãn Settings, tại mục Allow chọn Custom, mục Formula nhập hàm =COUNTIF($D$4:$D$10000,D4)=1

+Nháy vào nhãn Error Alert tại ô Title gõ Canh bao (Không có dấu).
+Mục Error message bạn gõ nội dung thông báo, thí du: “Số vận đơn này đã có rồi, hãy xem lại” sau đó bấm OK.
+Nếu bạn nhập trùng vận đơn sẽ hiện ra hộp cảnh báo như dưới đây:

Bước 4:So sánh và kiểm tra đối chiếu vận đơn giữa cơ quan và Bưu điện.
Bạn Copy số vận đơn của Bưu điện và dán vào ô J4 và quan sát các thông báo trong các cột K-L-M-N

+Bạn có thể khép cột KT1 và KT2 lại để cho gọn mà vẫn biết được bên A và bên B thiếu những vận đơn nào?
Vậy xin được cùng chia sẻ với bạn đọc mẹo nhỏ này.

Posted in: Excel