Phần mềm báo âm kho khi tính giá xuất? Tìm đúng phiếu làm âm bằng Excel

ExcelNâng cao Video 2:18

Bài toán

Cuối tháng bấm tính giá xuất kho, phần mềm báo âm kho cả chục mã; bạn lật từng phiếu xem mã nào xuất trước khi nhập.

Làm xong được gì

Danh sách mã âm kho kèm đúng phiếu đầu tiên làm âm; sửa ngày phiếu đó là tính được giá xuất.

Dùng trong bài

TableSortSUMIFS cộng dồnMINIFSConditional Formatting

File thực hành gồm

SoKho_T09.xlsx
Sheet TongHop là báo cáo tổng hợp tồn kho của 30 mã hàng (tồn đầu, nhập, xuất, tồn cuối), tồn cuối không mã nào âm. Sheet SoNhapXuat là sổ chi tiết nhập xuất kho tháng 9, 160 dòng xếp theo ngày, các mã nằm lẫn nhau. Cả hai sheet giữ hình dạng file xuất từ phần mềm: ba dòng tiêu đề gộp ô, dòng 4 là tên cột, cuối sổ có dòng Tổng cộng.
SoKho_T09_dap_an.xlsx
Bản đã làm xong: sổ đã sắp, đang lọc 9 mã từng âm, phiếu làm âm tô đỏ. Sửa ngày một phiếu nhập rồi bấm Data › Reapply (Ctrl+Alt+L) là bảng sắp lại và tính lại.

Các bước làm

1
Xoá ba dòng tiêu đề và dòng Tổng cộng
Ở sheet SoNhapXuat, bấm Ctrl+Home, Shift+Space rồi Shift+↓ hai lần để chọn dòng 1 tới 3, bấm Ctrl+- để xoá. Bấm Ctrl+↓, con trỏ chạy tới dòng Tổng cộng ở cuối sổ (dòng 162). Bấm Shift+Space rồi Ctrl+- để xoá nốt. Sổ chỉ còn dòng tên cột và 160 dòng dữ liệu, Excel mới nhận đúng bảng.
2
Chuyển sổ thành Table
Bấm ô A1 rồi bấm Ctrl+T. Hộp Create Table hiện vùng $A$1:$H$161 và đã tích My table has headers, bấm Enter. Dòng tiêu đề có sẵn nút lọc.
3
Xếp theo mã hàng, ngày, số phiếu
Vào Data › Sort. Dòng đầu chọn Sort by Mã hàng. Bấm Add Level, chọn Ngày. Bấm Add Level lần nữa, chọn Số phiếu. Thứ tự để mặc định (A to Z, Oldest to Newest) rồi OK. Xếp thêm số phiếu để trong cùng một ngày phiếu nhập NK đứng trên phiếu xuất XK. Xem dòng 6 và 7 của mã HH002 ngày 09/09: phiếu nhập NK00423 (150) nằm trên phiếu xuất XK01279 (40).
4
Thêm cột Tồn sau phiếu
Ô I1 gõ Tồn sau phiếu rồi Enter, bảng tự nới sang cột I. Ô I2 gõ công thức dưới đây, gồm ba SUMIFS. Cái đầu lấy tồn đầu kỳ của mã bên sheet TongHop. Cái thứ hai cộng cột nhập từ dòng 2 tới dòng đang đứng, cùng mã. Cái thứ ba trừ cột xuất theo cùng cách. Dấu $ khoá ở dòng 2 nên xuống tới dòng nào thì vùng cộng kéo dài tới dòng đó. Enter, cả cột tự điền tới I161. Kiểm ô I5, dòng cuối của mã HH001: ra 32, khớp tồn cuối kỳ của mã này trên sheet TongHop.
=SUMIFS(TongHop!D:D,TongHop!A:A,C2)+SUMIFS(G$2:G2,C$2:C2,C2)-SUMIFS(H$2:H2,C$2:C2,C2)
5
Thêm cột Tồn thấp nhất
Ô J1 gõ Tồn thấp nhất rồi Enter. Ô J2 gõ MINIFS để lấy số tồn nhỏ nhất trong tháng của từng mã, Enter là cả cột tự điền. Mã nào có lúc âm thì số này âm.
=MINIFS(I:I,C:C,C2)
6
Lọc các mã từng âm
Bấm nút lọc ở ô J1, chọn Number Filters › Less Than, gõ 0 rồi OK. Thanh trạng thái báo 29 of 160 records found: còn 29 dòng của 9 mã từng âm.
7
Tô đỏ phiếu làm âm
Bấm một ô đang hiện ở cột Tồn sau phiếu (ví dụ I15) rồi bấm Ctrl+Space để chọn cả cột dữ liệu. Vào Home › Conditional Formatting › Highlight Cells Rules › Less Than, gõ 0, Enter. Mỗi mã có đúng một ô đỏ, đếm đủ 9 ô, khớp 9 mã phần mềm báo xuất âm.
8
Đọc ô đỏ và thử sửa ngày một phiếu nhập
Ô đỏ là phiếu xuất làm âm, dòng ngay dưới là phiếu nhập ghi ngày muộn hơn. Dòng 15 là phiếu XK01268 ngày 05/09, xuất 15, tồn còn -5. Dòng 16 là phiếu NK00424 ngày 10/09, nhập 120: hàng về trước, hoá đơn về sau. Thử sửa ngày ở ô A16 thành 04/09/2026 rồi bấm Data › Reapply (Ctrl+Alt+L). Phiếu nhập lên trên phiếu xuất, I15:I18 thành 130, 115, 75, 45, ô đỏ mất, tồn thấp nhất của mã này thành 45. Sau lần Reapply đầu mã vẫn còn hiện trong danh sách, bấm thêm lần nữa thì mã rời khỏi danh sách.

Lưu ý khi làm với file thật

Sổ có nhiều kho: thêm điều kiện Kho vào cả ba SUMIFS và vào MINIFS.
Cùng một ngày, phiếu nhập phải đứng trên phiếu xuất. Số phiếu NK… đứng trước XK… khi xếp A → Z. Phần mềm đặt tiền tố khác thì kiểm lại thứ tự này.
Excel chỉ để tìm phiếu: sửa ngày ở phần mềm kế toán rồi chạy tính giá xuất lại.

MINIFS có từ Excel 2019: Excel 2019, 2021, 365 làm được y hệt.

File thực hành của bài này

EzTech sẽ cập nhật link tải sau.

Dữ liệu trong bài là dữ liệu minh hoạ.

Bài học khác

Tất cả bài học