Hướng dẫn cách quản lý kho trên excel

Sau đây xin hướng dẫn các bạn thiết kế quản lý kho nhập xuất tồn bằng excel minh họa bằng hình vẽ, rất mong có thể giúp ích cho những người muốn lập và thiết kế quản lý kho trên excel

BƯỚC 1: Tạo sheets : tổng hợp vật tư như mẫu sau

Tại cột MÃ VẬT TƯ đặt chuột tại A10 kéo xuống A32 : để đặt tên mãng dữ liệu

vào Iinsert/Name/ Define Name/ Names in workbook: MVT (nghĩa là mã vật tư) đặt mã :01

Hoặc bạn có thể vào Nambox ở góc bên phải trên thanh công cụ cho nhanh: click vào name book / gõ MVT / nhấn Enter

Mục đích của việc đặt mãng này để sau này khi làm phiếu nhập và phiếu xuất sẽ nhận dữ liệu mã vật tư để lấy cũng như đơn giá tồn và số lượng xuất………

BƯỚC 2: Thiết thêm một sheets tổng hợp việc nhập xuất kho BẢNG KÊ NHẬP XUẤT : đặt tên sheet là Nhập – Xuất

+Tại cột mã vật tư: ô K13 kéo xuống khoảng 200 dòng vào Iinsert/Name/ Define Name/ Names in workbook: MVTNX (nghĩa là mã vật tư nhập xuất) hoặc vào nhanh Namebox

Cũng Tại cột mã vật tư: ô K13 kéo xuống khoảng 200 dòng / Data/ Validation../ Allow: list, Source: = MVT (đã đặt tại mã 01) => mục đích để khi bạn nhấp vào đây sẽ sổ ra một danh sách mã vật tư để bạn lựa chọn vật tư cần nhập hay xuất mà không cần nhập tay

+Tại cột số lượng nhập: ô N13 kéo xuống khoảng 200 dòng vào Iinsert/Name/ Define Name/ Names in workbook: SLN (nghĩa là số lượng nhập) hoặc vào Namebox

+ Tại cột đơn gián nhập =IF(P13=””,””,P13/N13) ý nghĩa = nếu thành tiền nhập P13= trống, thì bỏ trống, ngược lại lấy thành tiền nhập chia cho số lượng nhập

+Tại cột số thành tiền nhập: ô P13 kéo xuống khoảng 200 dòng vào Iinsert/Name/ Define Name/ Names in workbook: TTN (nghĩa là thành tiền nhập) hoặc vào Namebox

+Tại cột số lượng xuất: ô Q13 kéo xuống khoảng 200 dòng vào Iinsert/Name/ Define Name/ Names in workbook: SLX (nghĩa là số lượng xuất) hoặc vào nhanh Namebox

+Tại cột thành tiền xuất: ô S13 kéo xuống khoảng 200 dòng vào Iinsert/Name/ Define Name/ Names in workbook: TTX (nghĩa là thành tiền xuất) hoặc vào nhanh Namebox

+ Tại cột đơn giá xuất trong kỳ ta đặt công thức sau = IF(LEFT(A13,2)=”PX”,IF(K13=””,0,VLOOKUP(K13,’Tong hop’!$N$10:$O$32,2,0)),””)

Ý nghĩa : nếu giá trị cột A3 lấy hai ký tự = PX (phiếu xuất), thì lấy giá trị nếu Mã vật tư K3 = trống thì lấy 0, ngược lại tìm Mã vật tư ô K 13, lấy vùng giá trị bên bảng tông hợp cột mã vật tư và đơn giá Xuất kho , lấy cột 2, 0: lấy giá trị chính xác nhất cuả giá trị cần tìm (#1 giá trị tương đối)

Sau đó kéo công thức xuống 200 dòng bạn cũng có thê đặt mãng cột N và O này là ĐGX : đơn giá xuất hoặc quét khối như công thức trên, ưu điểm khi đặt mãng thì khi xóa giá trị nào bên bảng tổng hợp thì bên sheets nhập – xuất này phần xóa sẽ bị mất mãng do đó công thức sẽ báo lỗi #N/A do không tìm được vùng đinh dạnh kia đã bị xoa, nhưng nếu bạn xóa = bàn phím Delete thì ko có vấn đề gì xảy ra cả

+ Tại cột Thành tiền xuất đặt công thức = =IF(Q13=””,0,ROUND(Q13*R13,0))

Nghĩa là nếu cột số lượng Q13 = trống thì lấy 0, ngược lại lấy Q13 nhân với đơn giá xuất R13 công thức này có tác dụng nếu cột nào ko có số lượng xuát thì nó lấy kho nếu chỉ làm công thức = số lượng nhân đơn giá xuất thì dòng nào trống nó báo #N/A khiến cho hàm SUM tổng hợp thành tiền xuất ko lấy được giá trị

+ Tại cột N12 đặt hàm =SUM(N13:N200) để tính tổng số lượng nhập trong kỳ

+ Tại cột P12 đặt hàm =SUM(P13:P200) để tính tổng thành tiền nhập trong kỳ

+ Tại côt Q12 đặt hàm =SUM(Q13:Q200) tính tổng số lượng xuất trong kỳ

+ Tại cột S12 đặt hàm =SUM(S13:S200) tính tổng thành tiền xuất trong kỳ

Các cột đơn giá bỏ trống không cần tính, có thể làm nhanh hơn tính tổng sum cột N12 số lượng nhập sau đó kéo công thức sang và xóa cột số liệu nhập là xong ko cần dài dòng như bước trên

*Ta làm thêm cột kiểm tra tồn cuối kỳ mục đích để luôn luôn cập nhật trạng thái kho của ta còn số lượng tồn là bao nhiêu tránh tình trạng âm kho khi xuất ra do đó mỗi khi xuất ra hàng hóa gì ta nhìn vào cột này biết ngay số lượng tồn là bao nhiêu để xuất đã hết hay còn

+ Tại cột T13 kiểm tra số lượng tồn cuối = =IF($K13=””,””,VLOOKUP($K13,’Tong hop’!$A$10:$K$32,10,0))

Giải thích : nếu cột giá trị mã vật tư K13= trống thì bỏ trống, ngược lại tìm giá trị tồn mã vật tư K13 ở bên bảng tổng hợp nhập xuất tồn quét khối từ cột mã vật tư cho đến hết cột Tồn cuối kỳ; ở công thức này ta lấy giá trị tuyệt đối khi quét khối là cố định vùng nhấn F4 đẻ khi bạn kéo công thức xuống không bị nhẩy vùng số liệu gây sai sót, lấy cột thứ 10 tức cột số lượng tồn cuối kỳ bên bảng tông hợp

+ Tại cột U 13 kiểm tra thành tiền tồn cuối kỳ =IF($K13=””,””,VLOOKUP($K13,’Tong hop’!$A$10:$K$343,11,0))

Các bạn có thể tải về để xem chi tiết: