File Excel Quản Lý Hạn Sử Dụng Hàng Hóa: Lập Bảng Theo Dõi Hết Hạn 2026

Trả lời nhanh: Để quản lý hạn sử dụng hàng hóa bằng Excel, lập một sheet có tối thiểu 5 cột: mã hàng, số lô, ngày nhập, hạn dùng, số lượng còn; dùng công thức trừ ngày (=D2-TODAY()) để tính số ngày còn lại, kết hợp định dạng có điều kiện tô màu đỏ/vàng theo mốc 7-30 ngày. Cách này đủ cho vài trăm dòng hàng, quá tải khi có nhiều lô và nhiều kho cùng lúc.

Vì sao bảng Excel đếm hạn dùng luôn "vỡ" sau vài tháng

Thử hình dung một cửa hàng bán sữa và thực phẩm chức năng, mỗi tuần nhập hàng từ 3-4 nhà cung cấp khác nhau, mỗi lần nhập lại có lô hạn dùng khác nhau cho cùng một mã sản phẩm. Người quản lý mở file Excel ra, thêm một dòng mới cho mỗi lần nhập, rồi copy công thức tính ngày còn lại xuống. Được vài tuần đầu thì ổn, vì số dòng còn ít và ai cũng nhớ lô nào mới lô nào cũ.

Vấn đề bắt đầu lộ ra khi số dòng tăng lên vài trăm, vài nghìn. Giả sử file có 800 dòng nhập hàng tích lũy trong 6 tháng, trong đó 500 dòng là hàng đã bán hết — nhưng không ai xóa, vì sợ mất dữ liệu lịch sử. Khi đó, để biết lô nào còn tồn và sắp hết hạn, người quản lý phải tự lọc, tự trừ số lượng đã bán khỏi số lượng đã nhập cho từng lô. Excel không tự làm việc này, trừ khi có công thức SUMIFS khá phức tạp theo cả mã hàng và số lô.

Lỗi thường gặp nhất: một mã hàng có 3 lô còn tồn, nhân viên bán hàng lại xuất nhầm lô nhập sau (hạn còn dài) trước, để lô nhập trước (hạn ngắn hơn) nằm lại trong kho đến ngày hết hạn. Đây chính là vấn đề quy tắc "FIFO" — xuất hàng theo đúng thứ tự lô nhập trước bán trước — mà một bảng tính thủ công không có cách nào ép buộc người bán phải tuân theo.

Cấu trúc cột bắt buộc cho bảng theo dõi hạn sử dụng

Một bảng theo dõi hạn dùng tối thiểu cần các cột sau, xếp theo đúng thứ tự để công thức phía sau dễ tham chiếu:

• Cột A — Mã hàng: mã sản phẩm, dùng để nhóm các lô của cùng một mặt hàng
• Cột B — Tên hàng: tên đầy đủ, để người đọc không phải nhớ mã
• Cột C — Số lô: mã lô hoặc số batch in trên bao bì/hóa đơn nhập
• Cột D — Ngày nhập: ngày nhập kho của lô đó
• Cột E — Hạn dùng: ngày hết hạn ghi trên sản phẩm
• Cột F — Số lượng nhập: số lượng ban đầu của lô
• Cột G — Số lượng đã xuất: cộng dồn số đã bán/xuất khỏi lô này
• Cột H — Số lượng còn: công thức =F2-G2

Với cấu trúc này, mỗi lần nhập một lô mới (dù là mã hàng cũ) đều tạo một dòng riêng. Đây là điểm nhiều người làm sai: họ cố gộp các lô của cùng mã hàng vào một dòng rồi cộng dồn số lượng, kết quả là mất hoàn toàn thông tin "lô nào hạn nào" — đúng lúc cần biết nhất.

Giả sử cột E2 chứa ngày hết hạn (ví dụ 15/11/2026) của lô sữa bột đầu tiên, công thức tính số ngày còn lại đặt ở cột I2:

=E2-TODAY()

Kết quả trả về số nguyên — số ngày còn lại tính từ hôm nay. Nếu ra số âm, nghĩa là lô đó đã quá hạn. Để công thức này hiển thị đúng dạng số (không bị Excel tự chuyển thành định dạng ngày), cần bôi đen cột I rồi đặt lại Format Cells thành Number trước khi nhập công thức.

Công thức cảnh báo và tô màu theo mốc ngày

Riêng công thức tính ngày còn lại chưa đủ, vì bảng vài trăm dòng không ai rảnh rà từng số một. Cách thực dụng hơn là dùng Conditional Formatting (Định dạng có điều kiện) để tự tô màu dòng theo mức độ nguy cấp.

Chọn toàn bộ vùng dữ liệu từ A2 đến H2 (kéo xuống hết số dòng đang dùng), vào Home → Conditional Formatting → New Rule → chọn "Use a formula to determine which cells to format", rồi nhập:

• Tô đỏ (đã hết hạn hoặc còn dưới 7 ngày): =AND($H2>0,$E2-TODAY()<=7)
• Tô vàng (còn 8-30 ngày): =AND($H2>0,$E2-TODAY()>7,$E2-TODAY()<=30)

Điều kiện $H2>0 ở đầu mỗi công thức rất quan trọng — nó loại các lô đã bán hết (số lượng còn bằng 0) ra khỏi diện cảnh báo, vì không ai cần lo lắng về hạn dùng của hàng không còn trong kho nữa. Thiếu điều kiện này, bảng sẽ đỏ lòe loẹt những lô đã xuất hết từ lâu, khiến người xem mất cảnh giác với những cảnh báo thật.

Ngưỡng 7 ngày và 30 ngày ở trên chỉ là ví dụ tham khảo cho ngành thực phẩm tươi; với mỹ phẩm hoặc thực phẩm chức năng có hạn dùng dài hơn, có thể đẩy ngưỡng lên 30 và 60 ngày tùy đặc tính hàng và tốc độ bán ra thực tế của từng cửa hàng.

Quy tắc bán hàng gần hết hạn trước (FIFO) khi làm thủ công

Biết lô nào sắp hết hạn chỉ là nửa việc; nửa còn lại là bắt buộc nhân viên bán đúng lô đó trước. Với bảng Excel thủ công, cách khả thi nhất là thêm một cột "Thứ tự bán" (cột J) tính bằng =RANK(E2,$E$2:$E$500,1) — hàm RANK xếp hạng tăng dần theo hạn dùng trong toàn vùng E2:E500, lô có hạn gần nhất sẽ nhận số thứ tự nhỏ nhất. Mỗi sáng, người quản lý lọc bảng theo cột J tăng dần, in ra hoặc chụp ảnh gửi nhóm Zalo nhân viên để biết lô nào cần ưu tiên bán trong ngày.

Cách này có hai hạn chế rõ ràng. Một, nó phụ thuộc hoàn toàn vào việc nhân viên nhìn đúng bảng và tự giác lấy đúng lô — không có gì ngăn họ lấy nhầm lô ở phía sau kệ hàng vì tiện tay hơn. Hai, mỗi lần có phiếu xuất kho, người nhập liệu phải tự tay trừ đúng vào dòng lô tương ứng ở cột G; nếu trừ sai lô (ví dụ trừ vào lô mới vì gõ nhanh), số liệu tồn và báo cáo giá vốn sau đó sẽ lệch mà không ai phát hiện ra ngay.

Đây chính là giới hạn cố hữu của Excel trong việc quản lý hạn dùng: công thức tính ngày và tô màu cảnh báo làm được, nhưng ép buộc quy trình xuất hàng đúng lô thì Excel không có cơ chế kiểm soát — nó chỉ là bảng tính tĩnh, không có quy tắc nghiệp vụ gắn vào từng phiếu xuất.

Khi dữ liệu nhiều lô nhiều kho, Excel bắt đầu đuối sức

Giả sử một cửa hàng mẹ và bé có 2 chi nhánh, mỗi chi nhánh nhập hàng riêng, nhưng chủ cửa hàng muốn nhìn tổng tồn kho và hạn dùng của toàn hệ thống trên một báo cáo. Lúc này file Excel phải tổng hợp dữ liệu từ 2 sheet (hoặc 2 file) riêng biệt bằng VLOOKUP hoặc INDEX/MATCH xuyên sheet — công thức bắt đầu dài và dễ gãy mỗi khi có người chèn/xóa dòng không đúng cách.

Vấn đề thứ hai nảy ra khi hàng hết hạn phải bỏ, hoặc khách trả hàng lại. Giá vốn hàng bán đã tính theo lô cụ thể, nếu khách trả hàng mà người nhập liệu không nhập lại đúng giá của lô đã xuất trước đó, báo cáo lãi gộp cuối tháng sẽ lệch — và việc dò lại xem lệch ở đâu trong một file Excel vài nghìn dòng tốn thời gian hơn hẳn việc nhập liệu ban đầu.

Những nhà bán lẻ có hàng theo lô và hạn dùng — thực phẩm, mỹ phẩm, mẹ và bé — đến điểm này thường tìm những công cụ quản lý tồn kho chuyên biệt hơn, có bảng tổng hợp tồn-nhập-xuất tự tính thay vì phải tự dựng công thức. Nếu bạn đang ở giai đoạn so sánh các lựa chọn cho ngành hàng có hạn dùng, có thể tham khảo thêm cách quản lý cửa hàng mẹ và bé theo lô hạn dùng hoặc nhìn rộng hơn các mẫu Excel theo từng ngành tại file quản lý hàng tồn kho bằng Excel theo từng ngành.

Mẫu bảng theo dõi hạn dùng cho cửa hàng nhỏ dưới 200 mã hàng

Với quy mô dưới 200 mã hàng và chưa cần tách nhiều kho, một file Excel vẫn có thể chạy ổn miễn giữ đúng nguyên tắc: mỗi lô một dòng, không gộp, không xóa dòng cũ (chỉ ẩn khi số lượng còn bằng 0). Bảng dưới đây minh họa cấu trúc tối thiểu, với số liệu giả định:

Mã hàng	Số lô	Ngày nhập	Hạn dùng	SL nhập	SL đã xuất	SL còn	Số ngày còn lại
SUA001	L0912	15/09/2026	15/11/2026	50	38	12	40
SUA001	L1003	03/10/2026	03/01/2027	60	5	55	89
BIM002	L0825	25/08/2026	25/10/2026	30	29	1	19

Nhìn bảng này, mã SUA001 có 2 lô còn tồn — lô L0912 chỉ còn 40 ngày và phải bán trước lô L1003 (còn 89 ngày) dù L1003 nhập sau. Mã BIM002 chỉ còn 1 sản phẩm tồn, hạn 19 ngày, vẫn nằm trong vùng cảnh báo vàng nên cần nhắc nhân viên ưu tiên đẩy ra trước khi hết hạn.

Để bảng này không phình to vô tội vạ, nên thêm một cột lọc nhanh "Ẩn khi SL còn = 0" bằng AutoFilter, và định kỳ (ví dụ cuối mỗi quý) chuyển các dòng đã hết hàng và không còn cảnh báo sang một sheet lưu trữ riêng, giữ sheet chính luôn gọn.

Khi nào nên dừng tự làm công thức, chuyển qua phần mềm có sẵn

Ranh giới hợp lý để chuyển đổi là khi một trong ba điều sau xảy ra: số lô cần theo dõi vượt quá vài trăm dòng đang hoạt động cùng lúc, cửa hàng có từ 2 kho/chi nhánh trở lên, hoặc việc xuất đúng lô FIFO không thể trông chờ vào ý thức nhân viên nữa mà cần hệ thống tự gợi ý lô nào xuất trước.

Với những trường hợp này, phần mềm quản lý kho và giá vốn trên Google Sheet giải quyết trực tiếp phần Excel bị đuối: nó theo dõi số lô và hạn sử dụng riêng biệt cho hàng tính giá vốn theo FIFO, tự cảnh báo lô sắp hết hạn và lô đã hết hạn, đồng thời phiếu xuất kho tự tính đúng giá vốn theo lô đang xuất — không còn phụ thuộc vào việc người nhập liệu tự trừ đúng dòng bằng tay. Phần mềm cũng quản lý được nhiều kho/chi nhánh trên cùng hệ thống và giữ nguyên giá vốn khi chuyển kho nội bộ, đúng phần mà công thức VLOOKUP xuyên sheet ở Excel hay bị lệch. Giá 299.000đ thanh toán một lần, dùng thử 7 ngày trước khi quyết định.

Câu hỏi thường gặp

Công thức tính số ngày còn lại hạn sử dụng trong Excel viết thế nào?

Nếu hạn dùng nằm ở cột E (ví dụ E2), dùng công thức =E2-TODAY() đặt ở cột khác (ví dụ I2). Kết quả là số nguyên thể hiện số ngày còn lại tính từ hôm nay, ra số âm nghĩa là lô đã quá hạn. Cần bôi đen cột đó và đặt Format Cells thành Number trước khi nhập công thức, nếu không Excel sẽ tự chuyển kết quả thành định dạng ngày.

Làm sao để bảng Excel tự tô màu cảnh báo hàng sắp hết hạn?

Dùng Conditional Formatting với hai quy tắc: tô đỏ cho lô còn dưới 7 ngày bằng công thức =AND($H2>0,$E2-TODAY()<=7), tô vàng cho lô còn 8-30 ngày bằng =AND($H2>0,$E2-TODAY()>7,$E2-TODAY()<=30). Điều kiện $H2>0 bắt buộc phải có để loại các lô đã bán hết khỏi diện cảnh báo, tránh bảng đỏ loạn vì hàng không còn trong kho.

Tại sao không nên gộp nhiều lô hàng của cùng một mã sản phẩm vào một dòng?

Mỗi lô nhập có hạn dùng riêng, nếu gộp các lô cùng mã hàng vào một dòng rồi cộng dồn số lượng, bảng sẽ mất hoàn toàn thông tin lô nào hạn nào — đúng lúc cần biết nhất để xuất hàng đúng thứ tự. Cấu trúc đúng là mỗi lần nhập một lô mới, dù mã hàng cũ, đều tạo một dòng riêng với số lô, ngày nhập, hạn dùng tách biệt.

Làm sao biết lô hàng nào cần xuất bán trước theo nguyên tắc FIFO khi làm thủ công trên Excel?

Thêm cột Thứ tự bán tính bằng =RANK(E2,$E$2:$E$500,1), hàm này xếp hạng tăng dần theo hạn dùng trong toàn vùng, lô có hạn gần nhất nhận số thứ tự nhỏ nhất. Mỗi sáng lọc bảng theo cột này tăng dần để biết lô nào cần ưu tiên bán, nhưng cách này phụ thuộc hoàn toàn vào việc nhân viên tự giác lấy đúng lô, Excel không có cơ chế ép buộc.