Tuần siêu khuyến mãiClaude Skills — GIẢM 20%
Tips

Cách tạo bảng theo dõi kho hàng trong Excel: Từng bước một

Powerdrill Bloom·
Cách tạo bảng theo dõi kho hàng trong Excel: Từng bước một

Để tạo một trình theo dõi kho hàng trong Excel, hãy xây dựng hai bảng. Bảng đầu tiên liệt kê các mặt hàng của bạn, với các cột cho ID mặt hàng, tên, đơn giá, tồn kho đầu kỳ và điểm đặt hàng lại. Bảng thứ hai ghi lại mọi biến động, với ngày tháng, ID mặt hàng, loại (Nhập hoặc Xuất) và số lượng. Sau đó, sử dụng SUMIFS để cộng tổng lượng nhập và xuất, rồi trừ chúng để có lượng tồn kho hiện có.

Thêm một cột trạng thái để gắn cờ các mặt hàng bằng hoặc dưới điểm đặt hàng lại và làm nổi bật các hàng đó bằng định dạng có điều kiện. Một bản tóm tắt ngắn ở trên cùng hiển thị tổng giá trị kho hàng và số lượng mặt hàng cần đặt hàng lại.

Hướng dẫn này bao gồm từng bước, từ cấu trúc bảng đến các công thức và cảnh báo. Nó cũng đi qua một ví dụ đầy đủ, một kho cung ứng văn phòng nhỏ và một cách nhanh hơn để xây dựng trình theo dõi từ một tệp xuất dữ liệu. Các bước được tham khảo từ các trang hỗ trợ của Microsoft, đọc vào ngày 18 tháng 11 năm 2026.

Trình theo dõi kho hàng làm được gì

Một trình theo dõi kho hàng trả lời ba câu hỏi tại bất kỳ thời điểm nào:

  • Chúng ta có bao nhiêu mỗi mặt hàng? Đây là lượng tồn kho hiện có.
  • Nó trị giá bao nhiêu? Đây là giá trị kho hàng, dựa trên đơn giá.
  • Chúng ta cần đặt hàng gì? Đây là những mặt hàng bằng hoặc dưới điểm đặt hàng lại của chúng.

Một trình theo dõi tốt cũng lưu giữ lịch sử. Khi số lượng kiểm kê có vẻ sai, bạn có thể cuộn ngược lại nhật ký biến động và tìm xem kho hàng đã nhập hoặc xuất khi nào.

Một trình theo dõi kho hàng trong Excel hoạt động tốt cho các kho hàng quy mô nhỏ và vừa. Nó phù hợp cho các kho chứa đồ, cửa hàng nhỏ, văn phòng phẩm, phụ tùng thay thế và thiết bị sự kiện. Khi bạn theo dõi hàng nghìn mặt hàng tại nhiều địa điểm, một hệ thống quản lý kho chuyên dụng thường sẽ phù hợp hơn.

Lập kế hoạch trình theo dõi: hai bảng

Trước khi nhập bất kỳ công thức nào, hãy quyết định cấu trúc của trình theo dõi kho hàng. Nhiều trình theo dõi đơn giản sử dụng một bảng với một cột số lượng duy nhất mà mọi người ghi đè lên. Cách đó sẽ nhanh chóng bị hỏng. Không ai biết khi nào một con số thay đổi hoặc tại sao.

Thiết kế tốt hơn là tách biệt danh sách mặt hàng khỏi các biến động:

Bảng Mỗi hàng cho một Các cột chính
Mặt hàng (Items) Sản phẩm hoặc linh kiện ID mặt hàng, Tên, Danh mục, Đơn giá, Tồn kho đầu kỳ, Điểm đặt hàng lại
Biến động (Movements) Thay đổi kho hàng Ngày, ID mặt hàng, Loại (Nhập hoặc Xuất), Số lượng, Ghi chú

Bảng Items giữ các dữ kiện hiếm khi thay đổi. Bảng Movements tăng lên mỗi ngày. Các công thức trong bảng Items đọc bảng Movements và tính toán lượng tồn kho hiện tại.

Thiết kế này có ba lợi ích. Bạn không bao giờ ghi đè lên một con số. Mọi thay đổi đều có ngày tháng. Và bạn có thể trả lời các câu hỏi sau này, chẳng hạn như lượng hàng đã xuất trong tháng Ba là bao nhiêu.

Nó cũng giúp trình theo dõi kho hàng an toàn hơn khi chia sẻ. Nhân viên chỉ thêm hàng vào bảng Movements. Bảng Items, với các công thức của nó, có thể được khóa lại.

Đặt mỗi bảng trên một trang tính riêng. Đặt tên các trang tính là "Items" và "Movements" để các công thức dễ đọc hơn.

Xây dựng danh sách mặt hàng dưới dạng bảng

Bắt đầu với trang tính Items. Nhập các tiêu đề ở hàng 1: Item ID, Name, Category, Unit cost, Opening stock, và Reorder point. Sau đó nhập mỗi mặt hàng trên một hàng.

Tiếp theo, chuyển vùng dữ liệu thành một bảng Excel. Microsoft giải thích lý do: "Để giúp việc quản lý và phân tích một nhóm dữ liệu liên quan trở nên dễ dàng hơn, bạn có thể chuyển một dải ô thành một bảng Excel." Để tạo một bảng:

  1. Chọn bất kỳ ô nào trong dữ liệu.
  2. Chọn Home > Format as Table.
  3. Chọn một kiểu bảng.
  4. Đảm bảo ô chọn cho tiêu đề (headers) đã được tích, sau đó chọn OK.

Đặt tên bảng là "Items" trong tab Table Design. Làm tương tự cho nhật ký biến động trên trang tính kia và đặt tên là "Movements."

Bảng giúp ích theo hai cách. Chúng tự động mở rộng khi bạn thêm hàng. Và các công thức trong một cột bảng sẽ tự động điền xuống dưới. Microsoft cho biết một công thức được nhập trong một ô "sẽ ngay lập tức được áp dụng cho tất cả các ô khác trong cột bảng đó."

Giữ ID mặt hàng ngắn gọn và duy nhất, chẳng hạn như A-101. Sử dụng chúng ở mọi nơi thay vì tên, vì tên có thể bị viết khác nhau.

Thêm danh sách thả xuống vào nhật ký biến động

Lỗi nhập liệu là lý do chính khiến số lượng kho hàng bị sai lệch. Một danh sách thả xuống sẽ ngăn chặn hầu hết các lỗi đó.

Microsoft giải thích mục đích trong một dòng. "Sử dụng xác thực dữ liệu để hạn chế loại dữ liệu hoặc các giá trị mà người dùng nhập vào một ô, như danh sách thả xuống." Đối với cột Type:

  1. Chọn cột Type trong bảng Movements.
  2. Chọn Data > Data Validation.
  3. Trong tab Settings, tại ô Allow, chọn List.
  4. Trong ô Source, nhập In,Out.
  5. Đảm bảo In-cell dropdown đã được tích, sau đó chọn OK.

Làm tương tự cho cột Item ID, nhưng trỏ nguồn (source) vào cột Item ID của bảng Items. Bây giờ mọi người chỉ có thể chọn một mặt hàng hiện có.

Danh sách thả xuống cũng giúp tăng tốc độ nhập liệu. Một nhân viên kho có thể ghi lại một lần giao hàng trong vài giây bằng cách chọn mặt hàng và loại, sau đó nhập số lượng.

Bạn cũng có thể hạn chế cột Quantity. Trong ô Allow, chọn Whole Number và đặt mức tối thiểu là 1. Điều đó sẽ chặn các mục nhập trống và số âm.

Tính toán tồn kho hiện có với SUMIFS

Bây giờ hãy thêm các cột tính toán vào bảng Items. SUMIFS cộng các con số đáp ứng nhiều hơn một điều kiện. Cú pháp của Microsoft là:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Thêm một cột tên là "Stock in." Trong ô đầu tiên của nó, nhập:

=SUMIFS(Movements[Quantity],Movements[Item ID],[@[Item ID]],Movements[Type],"In")

Công thức này cộng mọi số lượng trong bảng Movements nơi ID mặt hàng khớp với hàng đó và loại là In. Bảng sẽ tự động điền công thức xuống cho mọi mặt hàng.

Thêm cột "Stock out" với cùng công thức, nhưng đổi "In" thành "Out."

Sau đó thêm "On hand":

=[@[Opening stock]]+[@[Stock in]]-[@[Stock out]]

Cuối cùng, thêm "Stock value":

=[@[On hand]]*[@[Unit cost]]

Kiểm tra thủ công một mặt hàng sau khi bạn xây dựng các công thức. Lọc bảng Movements theo mặt hàng đó, cộng các hàng In và Out, rồi so sánh kết quả với cột On hand.

Gắn cờ các mặt hàng cần đặt hàng lại

Một trình theo dõi kho hàng hữu ích nhất khi nó cho bạn biết cần phải làm gì. Thêm cột "Status" với công thức này:

=IF([@[On hand]]<=[@[Reorder point]],"Reorder","OK")

Bây giờ mỗi hàng sẽ hiển thị Reorder hoặc OK. Thiết lập các điểm đặt hàng lại dựa trên thời gian hàng về và lượng bạn sử dụng trong thời gian đó.

Làm cho các hàng Reorder nổi bật bằng định dạng có điều kiện:

  1. Chọn cột Status.
  2. Chọn Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
  3. Nhập Reorder và chọn một màu tô, chẳng hạn như đỏ nhạt.
  4. Chọn OK.

Microsoft lưu ý rằng khi hộp thoại Text that Contains xuất hiện, bạn "nhập tùy chọn định dạng bạn muốn áp dụng và nhấp OK." Phần làm nổi bật sẽ cập nhật mỗi khi kho hàng thay đổi.

Bạn cũng có thể sắp xếp hoặc lọc cột Status để chỉ xem các hàng Reorder trước khi đặt hàng.

Tra cứu chi tiết mặt hàng với XLOOKUP

Nhật ký biến động hiển thị các ID mặt hàng, vốn khó đọc. Một cột tra cứu sẽ thêm tên mặt hàng bên cạnh mỗi biến động.

Microsoft mô tả XLOOKUP là một cách "để tìm mọi thứ trong một bảng hoặc dải ô theo hàng." Cú pháp của nó là:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Thêm một cột "Name" vào bảng Movements và nhập:

=XLOOKUP([@[Item ID]],Items[Item ID],Items[Name],"Unknown ID")

Nếu ai đó nhập một ID không tồn tại, ô sẽ hiển thị "Unknown ID." Điều đó giúp dễ dàng phát hiện lỗi.

Microsoft lưu ý rằng "XLOOKUP không khả dụng trong Excel 2016 và Excel 2019." Trên các phiên bản đó, hãy sử dụng INDEX và MATCH, hoặc VLOOKUP với ID mặt hàng ở cột đầu tiên.

Thêm bản tóm tắt ở trên cùng

Mảnh ghép cuối cùng của trình theo dõi kho hàng là một bản tóm tắt. Một bản tóm tắt ngắn giúp bất kỳ ai cũng có thể thấy tình trạng kho hàng mà không cần cuộn chuột. Thêm một vài ô phía trên bảng Items, hoặc trên một trang tính "Summary" riêng biệt.

Sử dụng các công thức sau:

  • Tổng giá trị kho hàng: =SUM(Items[Stock value])
  • Mặt hàng cần đặt lại: =COUNTIF(Items[Status],"Reorder")
  • Đơn vị đã xuất tháng này: =SUMIFS(Movements[Quantity],Movements[Type],"Out",Movements[Date],">="&DATE(2026,11,1))

COUNTIF tuân theo một mẫu đơn giản. Theo lời của Microsoft, nó hỏi "Bạn muốn tìm ở đâu?" và "Bạn muốn tìm cái gì?"

Bạn cũng có thể thêm biểu đồ thanh về giá trị kho hàng theo danh mục. Chọn một vùng tóm tắt và chọn Insert > Chart. Giữ bản tóm tắt nhỏ gọn, với ba đến năm con số quan trọng.

Một ví dụ thực tế: kho cung ứng văn phòng

Dưới đây là một ví dụ đầy đủ về trình theo dõi kho hàng đang được sử dụng. Một văn phòng nhỏ theo dõi năm mặt hàng cung ứng. Bảng Items trông như thế này trước tháng 11:

ID mặt hàng Tên Đơn giá Tồn kho đầu kỳ Điểm đặt hàng lại
A-101 Sổ tay A5 $2.50 120 50
A-102 Bút bi (hộp) $6.00 40 20
B-201 Giấy in (ram) $4.20 80 30
B-202 Hộp mực $45.00 12 5
C-301 Thùng vận chuyển (vừa) $0.80 300 100

Trong tháng 11, bảng Movements ghi lại những thay đổi này. Sổ tay: 85 xuất và 50 nhập. Bút: 25 xuất. Giấy: 60 xuất và 40 nhập. Mực: 9 xuất. Thùng: 180 xuất.

Sau khi các công thức chạy, các cột tính toán hiển thị:

ID mặt hàng Nhập kho Xuất kho Hiện có Giá trị kho Trạng thái
A-101 50 85 85 $212.50 OK
A-102 0 25 15 $90.00 Reorder
B-201 40 60 60 $252.00 OK
B-202 0 9 3 $135.00 Reorder
C-301 0 180 120 $96.00 OK

Ví dụ này cũng cho thấy tại sao nhật ký biến động lại quan trọng. Giấy in trông có vẻ ổn ở mức 60 ram, nhưng nhật ký cho thấy 60 ram đã xuất trong một tháng. Nếu tốc độ đó tiếp tục, giấy sẽ chạm điểm đặt hàng lại trong vòng vài tuần. Một cột số lượng duy nhất sẽ che giấu xu hướng đó.

Kiểm tra thủ công một hàng. Sổ tay bắt đầu ở mức 120, thêm 50 và mất 85, vậy 120 + 50 − 85 = 85. Con số này khớp với cột On hand.

Bản tóm tắt hiển thị tổng giá trị kho hàng là $785.50 và 2 mặt hàng cần đặt hàng lại. Bút và mực đều dưới điểm đặt hàng lại, vì vậy chúng sẽ nằm trong đơn hàng tuần này.

Tạo trình theo dõi kho hàng trong Excel với AI

Xây dựng trình theo dõi bằng tay mất khoảng một hoặc hai giờ. Phần chậm nhất thường là dữ liệu: làm sạch tệp xuất từ hệ thống bán hàng, khớp tên mặt hàng và sửa các ID trùng lặp.

Một công cụ AI có thể thực hiện việc làm sạch và phân tích đó cho bạn. Dưới đây là phương pháp gồm ba bước, sử dụng Powerdrill Bloom.

Bước 1: Tải lên tệp Excel, CSV hoặc PDF của bạn

Tải lên tệp xuất kho hàng, danh sách nhà cung cấp hoặc bảng kiểm kê tháng trước của bạn. Excel AI Assistant làm việc trực tiếp với các tệp bảng tính.

Bao gồm cả danh sách mặt hàng và các biến động nếu bạn có, ngay cả khi chúng nằm trong các tệp riêng biệt.

Tải lên tệp xuất kho để tạo trình theo dõi kho hàng trong Excel với Powerdrill Bloom

Bước 2: Yêu cầu trình theo dõi bằng ngôn ngữ tự nhiên

Mô tả những gì bạn cần. Ví dụ: "Xây dựng một trình theo dõi kho hàng với lượng tồn kho hiện có, giá trị kho hàng và cờ đặt hàng lại cho các mặt hàng ở mức 20 đơn vị trở xuống." Yêu cầu nó liệt kê các mặt hàng cần đặt hàng trong tuần này.

Bạn cũng có thể đặt các câu hỏi tiếp theo, chẳng hạn như danh mục nào đang chiếm dụng nhiều tiền mặt nhất.

Bước 3: Xem lại kết quả và xuất chúng

Kiểm tra số lượng hiện có của một vài mặt hàng so với lần kiểm kê cuối cùng của bạn. Sau đó, xuất bảng đã làm sạch trở lại Excel, hoặc chuyển các phát hiện thành một báo cáo ngắn hoặc các trang trình bày cho nhóm của bạn.

Điều này hữu ích khi trình theo dõi kho hàng bị chậm trễ. Một tệp xuất hàng tháng từ hệ thống bán hàng của bạn có thể cho thấy những gì thực sự đã xuất, bạn có thể so sánh với nhật ký.

Giữ thiết kế hai bảng cho các cập nhật hàng ngày. Sử dụng bước AI khi bạn cần làm sạch nhanh hoặc đánh giá hàng tháng.

Xem lại lượng tồn kho hiện có trước khi xuất trình theo dõi kho hàng

Các lỗi phổ biến cần tránh

Hầu hết các vấn đề với trình theo dõi kho hàng đến từ cách mọi người nhập dữ liệu, không phải từ các công thức. Đây là những vấn đề thường gặp nhất với trình theo dõi kho hàng trong Excel.

  • Ghi đè số lượng. Thay vào đó, hãy ghi lại mọi thay đổi như một biến động mới.
  • Sử dụng tên thay vì ID. "Bút" và "Hộp bút" trở thành hai mặt hàng khác nhau. Hãy sử dụng các ID duy nhất.
  • Không có tồn kho đầu kỳ. Nếu không có số lượng bắt đầu, mọi con số hiện có đều sai.
  • Đơn vị hỗn hợp. Theo dõi theo hộp hoặc theo chiếc, không phải cả hai, cho cùng một mặt hàng.
  • Không kiểm kê thường xuyên. So sánh trình theo dõi với số lượng kiểm kê thực tế hàng tháng hoặc hàng quý.
  • Điểm đặt hàng lại không bao giờ được xem xét. Cập nhật chúng khi mức sử dụng hoặc thời gian giao hàng của nhà cung cấp thay đổi.
  • Xóa các hàng nhật ký. Không bao giờ xóa một biến động để sửa lỗi. Thay vào đó, hãy thêm một mục điều chỉnh kèm theo ghi chú.
  • Ghi đè lên công thức. Bảo vệ trang tính Items để không ai thay thế công thức bằng một con số.

Để tìm các mặt hàng nằm trên kệ quá lâu, hãy xem hướng dẫn của chúng tôi về cách phát hiện hàng tồn kho chậm luân chuyển. Để dự báo nhu cầu, hãy xem danh sách các công cụ AI để quản lý kho và dự báo nhu cầu.

Khi dữ liệu kho hàng của bạn nằm trong một tệp xuất lộn xộn, hãy thử Powerdrill Bloom để làm sạch nó thành một trình theo dõi sẵn sàng sử dụng.

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

Làm cách nào để tạo một trình theo dõi kho hàng đơn giản trong Excel?

Tạo một bảng Items với ID mặt hàng, tên, đơn giá, tồn kho đầu kỳ và điểm đặt hàng lại. Thêm một bảng Movements ghi lại từng thay đổi. Sau đó sử dụng SUMIFS để tính toán lượng nhập, xuất và tồn kho hiện có.

Công thức nào tính toán tồn kho hiện có trong Excel?

Tồn kho hiện có bằng tồn kho đầu kỳ cộng lượng nhập trừ lượng xuất. Sử dụng SUMIFS để tổng hợp số lượng Nhập và Xuất cho từng mặt hàng từ nhật ký biến động.

Làm cách nào để Excel gắn cờ kho hàng thấp?

Thêm một cột Status với công thức IF trả về "Reorder" khi tồn kho hiện có bằng hoặc dưới điểm đặt hàng lại. Sau đó làm nổi bật các ô đó bằng định dạng có điều kiện.

Tôi nên cập nhật trình theo dõi kho hàng bao lâu một lần?

Ghi lại mỗi biến động khi nó xảy ra, hoặc ít nhất một lần mỗi ngày. Sau đó so sánh trình theo dõi với số lượng kiểm kê thực tế hàng tháng hoặc hàng quý, và sửa bất kỳ khoảng cách nào bằng một mục điều chỉnh.

Tôi có thể theo dõi kho hàng trong Excel cho nhiều địa điểm không?

Có. Thêm một cột Location vào bảng Movements và thêm Location làm điều kiện thứ hai trong SUMIFS. Mỗi mặt hàng sau đó sẽ có một con số hiện có cho mỗi địa điểm.

Nguồn: Hỗ trợ Microsoft, Tổng quan về bảng Excel · Hỗ trợ Microsoft, Áp dụng xác thực dữ liệu cho các ô · Hỗ trợ Microsoft, Hàm SUMIFS · Hỗ trợ Microsoft, Hàm XLOOKUP · Hỗ trợ Microsoft, Sử dụng định dạng có điều kiện để làm nổi bật thông tin trong Excel · Hỗ trợ Microsoft, Sử dụng hàm COUNTIF trong Microsoft Excel.