Hướng dẫn lập công thức thông báo khi xuất kho bị âm trên Excel – Học Excel Online Miễn Phí
Ví dụ về 1 mẫu quản trị kho trên Excel như sau :
Chúng ta có 2 bảng kê, gồm bảng kê nhập và bảng kê xuất :
Bạn đang đọc: Hướng dẫn lập công thức thông báo khi xuất kho bị âm trên Excel – Học Excel Online Miễn Phí
Yêu cầu : khi lập phiếu xuất kho phải theo dõi được việc xuất âm kho .
Thế nào là xuất âm kho
Nhắc lại 1 chút về khái niệm xuất âm kho để những bạn hiểu rõ yếu tố này hơn nhé :
Xuất âm kho là việc số lượng xuất kho trên sổ sách vượt quá số lượng sản phẩm & hàng hóa trong thực tiễn tồn kho. Việc này dẫn tới sau khi xuất số lượng hàng còn tồn trên sổ sách sẽ bị âm ( Số tồn sau khi xuất kho = Tổng số xuất kho – Tổng số tồn kho trước khi xuất )Dù đã có ứng dụng, nhưng kiến thức và kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa ? Hãy để tôi giúp bạn, ĐK khóa học Excel :
Việc tồn kho bị âm là trái với thực tiễn. Bởi thực tiễn không có việc xuất âm. Và lỗi xuất âm là lỗi khá nghiêm trọng trong việc làm quản trị kho .
Do đó việc theo dõi xuất âm kho được thực thi theo 2 cách :
- Cách 1: Quản lý âm kho trong Bảng kê
- Cách 2: Quản lý âm kho ngay khi lập phiếu xuất kho
Để tốt nhất cho việc làm thì tất cả chúng ta nên quản trị theo cách 2 là quản trị âm kho ngay khi lập phiếu xuất kho
Lập công thức tính tồn kho trong phiếu xuất kho
Việc xác lập tồn kho trong phiếu xuất kho sẽ giúp tất cả chúng ta xác lập được số lượng giới hạn số lượng hàng có để xuất, từ đó sẽ cảnh báo, phát hiện được khi nào xuất âm .
Việc tính tồn kho phụ thuộc vào vào :
- Điều kiện thời gian: là thời điểm lập phiếu xuất kho. Chỉ tính với những mặt hàng nhập – xuất kho đến thời điểm đó mà thôi. Không tính những mặt hàng nhập, xuất sau thời điểm đó
- Điều kiện tên mặt hàng: tùy từng mặt hàng được lựa chọn để tính cho mặt hàng đó.
Như vậy ở đây tất cả chúng ta cần tính theo nhiều điều kiện kèm theo ( hơn 1 điều kiện kèm theo ) => Sử dụng hàm Sumifs
Nguyên tắc tính tồn kho = Tổng nhập – Tổng xuấtXét với mặt hàng A, vị trí ô J3
a. Tổng nhập kho
SUMIFS ( USD C USD 3 : USD C USD 10, USD B USD 3 : USD B USD 10, $ I3, $ A $ 3 : USD A $ 10, ” < = ” và USD K USD 1 ) Giải thích công thức :
- $C$3:$C$10: vùng cần tính tổng là cột số lượng trong bảng kê nhập. Lưu ý độ rộng các vùng phải bằng nhau. Các vùng đều cần cố định
- $B$3:$B$10: vùng điều kiện thứ 1, xét với tên hàng nên chọn cột tên hàng.
- $I3: điều kiện thứ 1 là tên hàng tại ô I3, điều kiện này nằm trong vùng điều kiện thứ 1. Cố định cột I để khi sao chép công thức sang cột khác không bị sai lệch tham chiếu tới điều kiện này.
- $A$3:$A$10: vùng điều kiện thứ 2 là thời gian, ngày. Xét cho điều kiện là ngày lập phiếu
- “<=”&$K$1: là xét từ ngày 15/5 trở về trước, do đó phải kết hợp với dấu nhỏ hơn hoặc bằng.
b. Tổng xuất kho
SUMIFS ( USD G USD 3 : USD G USD 10, USD F USD 3 : USD F USD 10, $ I3, $ E $ 3 : USD E $ 10, ” < = ” và USD K USD 1 ) Tương tự như tính tổng Nhập kho nhưng xét cho Bảng Xuất kho Số tồn kho J3 = SUMIFS ( USD C USD 3 : USD C USD 10, USD B USD 3 : USD B USD 10, $ I3, $ A $ 3 : USD A $ 10, ” < = ” và USD K USD 1 ) - SUMIFS ( USD G USD 3 : USD G USD 10, USD F USD 3 : USD F USD 10, $ I3, $ E $ 3 : USD E $ 10, ” < = ” và USD K USD 1 ) Áp dụng tương tự như cho những loại sản phẩm khác .
Thiết lập cảnh báo khi xuất âm kho
Để cảnh báo xuất âm kho, bạn có thể sử dụng 2 công cụ là:
Conditional formatting: tự động định dạng khi phát hiện việc xuất âm, giúp bạn nhận biết bằng màu sắc, định dạng trong ô
Data validation: tự động đưa ra cảnh báo khi xuất âm, cho phép hoặc từ chối việc xuất âm dựa vào quyết định của người dùng.
Thực hiện như sau :
a. Sử dụng Conditional formatting:
Tạo thêm 1 cột Tồn sau khi xuất = Tồn trước khi xuất – Số lượng xuất
Chọn hàng loạt vùng Phiếu xuất rồi chọn Conditional formattiing .
Trong Conditional formatting thiết kế xây dựng công thức như sau :
Ý nghĩa: Xét từng giá trị ở cột L, bắt đầu từ ô L3. Nếu Giá trị nào nhỏ hơn 0 thì sẽ định dạng tô màu vàng
Kết quả như sau :
Như vậy tất cả chúng ta hoàn toàn có thể thấy khi loại sản phẩm B xuất 10 thì kho sẽ bị âm
b. Sử dụng Data validation
Cách này không cần tới cột phụ, chỉ cần sử dụng trực tiếp Data validation trong cột Số lượng xuất như sau :
Trong Cột K vùng số lượng xuất : K3 : K7 chọn Data / Data validation
Trong hành lang cửa số Data validation chọn
- Allow = Whole number (chỉ nhập dạng số),
- Data = Less than or equal to (nhỏ hơn hoặc bằng),
- Maximum =J3 (giá trị lớn nhất bằng J3. Khi chọn vùng K3:K7 thì Data validation sẽ hiểu tương ứng: tại K3 thì <=J3, K4<=J4… K7<=J7
Khi đó nếu tại cột K nhập giá trị lớn hơn cột J thì sẽ Open thông tin của Data validation về việc nhập quá số lượng giới hạn được cho phép .
Các bạn hoàn toàn có thể thiết lập những chính sách cảnh bảo của Data validation trong mục Error Arlet để hoàn toàn có thể báo lỗi đơn cử, được cho phép nhập số lượng lớn hơn số tồn ( gây ra xuất âm ) hay không .Kết luận
Chỉ với 2 kỹ thuật cơ bản trong Excel bạn đã có thể kiểm soát việc xuất âm kho rồi.
Tuy nhiên để làm được điều đó thì những bạn cần đặc biệt quan trọng quan tâm về cấu trúc của những bảng kê Nhập, bảng kê Xuất và cách sử dụng công thức Sumifs. Công thức SUMIFS được sử dụng rất phong phú trong kế toán .
Các bạn hoàn toàn có thể tải về file mẫu tại địa chỉ : http://bit.ly/2sGfXR0
Ngoài ra để hoàn toàn có thể nâng cao kiến thức và kỹ năng về Excel và ứng dụng Excel vào quản trị dự án Bất Động Sản, những bạn hoàn toàn có thể tham gia khóa học EX101 – Excel từ cơ bản tới chuyên viên của mạng lưới hệ thống Học Excel Online. Khóa học sẽ giúp bạn nắm vững những hàm, những công cụ trong Excel, ứng dụng Conditional formatting trong việc tạo gantt chart quản trị quy trình tiến độ dự án Bất Động Sản. Chi tiết khóa học xem tại :
Source: https://dichvubachkhoa.vn
Category : Mua Bán Đồ Cũ
