Excel Quản Lý Toàn Diện Kho Hàng Hóa: Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tế

Có thể bạn quan tâm: Excel Merge Cell Và Giữ Lại Toàn Bộ Dữ Liệu – Hướng Dẫn Cách Thực Hiện Từ Cơ Bản Đến Nâng Cao

Giới thiệu

Trong thời đại công nghiệp 4.0, quản lý kho hàng hóa không còn là công việc đơn giản mà đã trở thành một hệ thống phức tạp, đòi hỏi sự chính xác, linh hoạt và khả năng phân tích dữ liệu nhanh chóng. Trong khi các phần mềm ERP và WMS (Warehouse Management System) vẫn chiếm ưu thế, Excel với tính linh hoạt, tính sẵn có và khả năng tùy biến cao vẫn là công cụ không thể thiếu trong nhiều doanh nghiệp, đặc biệt là các doanh nghiệp vừa và nhỏ (SMEs) muốn tối ưu hóa quy trình quản lý kho mà không đầu tư lớn vào phần mềm chuyên dụng.

Bài viết này sẽ đưa bạn qua toàn bộ quy trình xây dựng một hệ thống quản lý kho hàng hóa toàn diện bằng Excel, từ thiết lập bảng dữ liệu cơ bản, áp dụng các hàm nâng cao, tạo báo cáo, dashboard, tới tự động hoá quy trình bằng VBA và Power Query. Bạn sẽ được học cách biến một bảng tính đơn giản thành một công cụ quản lý kho chuyên nghiệp, đáp ứng được nhu cầu kiểm soát tồn kho, theo dõi đơn hàng, lập báo cáo và dự báo nhu cầu.

Có thể bạn quan tâm: Cách Mở Khóa Excel Khi Đang Đọc Chỉ Để Giải Pháp “excel Is Locked For Editing”

1. Tại sao Excel lại phù hợp cho quản lý kho

1. Độ phổ biến cao – Hầu hết doanh nghiệp đều có sẵn sổ Excel, không cần mua phần mềm mới.
2. Tùy biến linh hoạt – Bạn có thể tự thiết kế bảng tính, hàm, macro theo nhu cầu riêng.
3. Khả năng xử lý dữ liệu lớn – Với tính năng Power Query, Power Pivot, Excel có thể xử lý hàng triệu dòng dữ liệu.
4. Tích hợp dễ dàng – Excel dễ dàng nhập xuất dữ liệu với các hệ thống khácplitude, ERP, hoặc các nguồn dữ liệu ngoài.
5. Chi phí thấp – Không cần chi phí cấp phép phần mềm, chỉ cần kiến thức và thời gian đầu tư.

Có thể bạn quan tâm: Cách Sử Dụng Hàm If Để Kiểm Tra Giá Trị Trong Một Phạm Vi Và Trả Về Kết Quả Trong Excel

2. Xây dựng cấu trúc dữ liệu cơ bản

2.1. Các bảng cần thiết

Lưu ý: Mỗi bảng nên được lưu trữ trong một sheet riêng biệt hoặc trong một workbook, tùy thuộc vào quy mô dữ liệu.

2.2. Thiết lập dữ liệu chuẩn

• Kiểm tra dữ liệu: Sử dụng Data Validation để giới hạn các giá trị nhập vào (ví dụ: chỉ cho phép số nguyên dương cho cột Quantity).
• Định dạng ngày: Đảm bảo cột ngày luôn có cùng định dạng (dd/mm/yyyy).
• Trùng lặp: Sử dụng Conditional Formatting để đánh dấu các bản ghi lặp (ví dụ: ProductID trùng lặp).

3. Các hàm Excel cơ bản cho quản lý kho

Chú ý:ването, XLOOKUP là hàm mạnh mẽ hơn VLOOKUP, nhưng nếu bạn làm việc với phiên bản Excel cũ (<2026) hãy dùng VLOOKUP hoặc INDEX/MATCH.

4. Tính toán tồn kho

4.1. Công thức tính tồn kho

Tồn kho hiện tại = (Tổng số lượng nhập) – (Tổng số lượng xuất)

=SUMIFS(EnterQty, ProductID, @ProductID, WarehouseID, @WarehouseID) - SUMIFS(ExitQty, ProductID, @ProductID, WarehouseID, @WarehouseID)

4.2. Sử dụng PivotTable để tổng hợp

• Chọn dữ liệu từ bảng Nhập kho và Xuat kho.
• Chèn PivotTable.
• Lưới: ProductID, WarehouseID.
• Giá trị: Sum of Quantity (đối với Nhập kho) và Sum of Quantity (đối với Xuat kho).
• Tạo cột tính toán: Tồn kho = Nhập - Xuất.

Khi dữ liệu thay đổi, chỉ cần refresh PivotTable để cập nhật.

5. Báo cáo và Dashboard

5.1. Báo cáo tồn kho

• Sử dụng PivotTable với các trường ProductID, ProductName, WarehouseID, WarehouseName, Quantity.
• Thêm slicer theo Product Category và Warehouse để lọc nhanh.
• Định dạng bảng báo cáo: màu xanh cho tồn kho > 100, vàng cho tồn kho 50-100, đỏ cho tồn kho < 50.

5.2. Dashboard tổng quan

• Charts:
• Đồ thị cột: Tồn kho theo product.
• Đồ thị tròn: Tỷ lệ tồn kho theo warehouse.
• Đồ thị đường: Xu hướng nhập/ xuất theo thời gian.
• Slicers: Lọc theo thời gian (tháng/năm), warehouse, product category.
• Dynamic titles: Sử dụng hàm CONCATENATE hoặc TEXTJOIN để hiển thị tiêu đề tùy biến.

Tips: Đặt Dashboard trong một sheet riêng, dùng các cell tham chiếu dữ liệu từ các PivotTables.

6. Tự động hoá quy trình với VBA

6.1. Tạo macro nhập kho

Có thể bạn quan tâm: Excel Không Đủ Tài Nguyên Hệ Thống Để Hiển Thị Hoàn Chỉnh: Nguyên Nhân, Hậu Quả Và Giải Pháp

Sub NhapKho() Dim wsEntry As Worksheet, wsStock As Worksheet Dim lastRow As Long, i As Long Set wsEntry = Worksheets("Nhập kho") Set wsStock = Worksheets("Tồn kho") lastRow = wsEntry.Cells(wsEntry.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow Dim pid As String, wid As String, qty As Long pid = wsEntry.Cells(i, "B").Value wid = wsEntry.Cells(i, "C").Value qty = wsEntry.Cells(i, "D").Value ' Tìm tồn kho hiện tại Dim stockRow As Long stockRow = Application.Match(pid & "|" & wid, wsStock.Columns("A:A").Value, 0) If IsError(stockRow) Then stockRow = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row + 1 wsStock.Cells(stockRow, "A").Value = pid & "|" & wid wsStock.Cells(stockRow, "B").Value = pid wsStock.Cells(stockRow, "C").Value = wid wsStock.Cells(stockRow, "D").Value = qty Else wsStock.Cells(stockRow, "D").Value = wsStock.Cells(stockRow, "D").Value + qty End If Next i MsgBox "Nhập kho hoàn tất!"
End Sub

Giải thích: Hàm này lặp qua từng dòng nhập kho, tìm tồn kho tương ứng và cập nhật số lượng. Nếu chưa có bản ghi tồn kho, thì tạo mới.

6.2. Tự động cập nhật báo cáo

• Gán macro RefreshAll vào nút “Refresh” trên Dashboard.
• Sử dụng ThisWorkbook.RefreshAll để refresh tất cả PivotTables và Power Queries.

6.3. Gửi báo cáo qua Email

Sub GiaoBaoCao() Dim OutApp As Object, OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = "kho@example.com" .Subject = "Báo cáo tồn kho - " & Format(Date, "dd/mm/yyyy") .Body = "Xin chào," & vbCrLf & vbCrLf & _ "Đính kèm là báo cáo tồn kho hiện tại." & vbCrLf & vbCrLf & _ "Trân trọng." .Attachments.Add ActiveWorkbook.FullName .Send End With MsgBox "Báo cáo đã được gửi!"
End Sub

Lưu ý: Để macro có thể gửi email, Outlook phải được cài đặt và cấu hình.

7. Sử dụng Power Query và Power Pivot

7.1. Power Query: Nhập dữ liệu từ file CSV/Excel

1. Chọn Data → Get Data → From File → From Workbook.
2. Chọn file chứa bảng nhập kho / xuất kho.
3. Trong Power Query Editor, làm sạch dữ liệu: loại bỏ dòng trống, chuyển đổi kiểu dữ liệu, tạo cột hợp nhất (ProductID|WarehouseID).
4. Load dữ liệu vào mô hình dữ liệu (Power Pivot).

7.2. Power Pivot: Mô hình dữ liệu

• Tạo mối quan hệ giữa các bảng: ProductID liên kết với bảng Sản phẩm, WarehouseID liên kết với bảng Kho.
• Tạo tính toán DAX: Tồn Kho = SUMX(FILTER(HOUP, HoUPProductID = EARLIER(HOUPProductID) && HoUPWarehouseID = EARLIER(HOUPWarehouseID)), HoUPQuantity).

7.3. Lợi ích

• Xử lý dữ liệu lớn (tỷ lệ hàng triệu dòng) mà không làm chậm Excel.
• Tạo báo cáo động, tự động cập nhật khi dữ liệu nguồn thay đổi.

8. Bảo mật và quản trị dữ liệu

1. Bảo vệ workbook: Đặt mật khẩu bảo vệ trang tính và workbook.
2. Quản lý quyền truy cập: Sử dụng tính năng “Protect Sheet” để chỉ cho phép chỉnh sửa một số ô nhất định.
3. Backup định kỳ: Lưu bản sao (backup) mỗi tuần, lưu trong hệ thống lưu trữ đám mây (SharePoint, OneDrive).
4. Version control: Sử dụng tính năng “Track changes” hoặc phiên bản Excel mới hơn (Excel 365) với “OneDrive” để theo dõi lịch sử thay đổi.

9. Kiến nghị thực hành tốt (Best Practices)

10. Case Study: Doanh nghiệp procedurally

10.1. Bối cảnh

• Doanh nghiệp: Cửa hàng bán lẻ điện tử, 3 kho (Phòng A, Phòng B, Phòng C).
• Nhu cầu: Theo dõi tồn kho 3 kho, thống kê doanh thu, lập báo cáo định kỳ cho ban lãnh đạo.

10.2. Giải pháp

1. Bảng dữ liệu: Tạo các bảng Sản phẩm, Kho, Nhập kho, Xuat kho, Đơn hàng, Tồn kho.
2. Macro: Tạo macro NhapKho, XuatKho, RefreshAll.
3. Dashboard: Đồ thị doanh thu theo tháng, tồn kho theo kho, dự báo nhu cầu (sử dụng hàm FORECAST).
4. Power Query: Nhập dữ liệu bán hàng từ hệ thống POS, kết nối tới bảng Đơn hàng.
5. Bảo mật: Bảo vệ workbook, backup hàng ngày.

10.3. Kết quả

• Tăng năng suất: Nhân viên có thể cập nhật tồn kho chỉ trong 3 phút.
• Cải thiện quyết định: Ban lãnh đạo có thể xem báo cáo tồn kho thực tế ngay lập tức.
• Tiết kiệm chi phí: Không cần đầu tư phần mềm ERP, chi phí duy trì Excel thấp hơn 1/10.

11. Kết luận

Excel, với tính linh hoạt và khả năng tùy biến cao, có thể trở thành một “đai” mạnh mẽ trong quản lý kho hàng hóa, đặc biệt khi doanh nghiệp muốn giảm chi phí đầu tư phần mềm và tăng tính linh hoạt trong quản lý dữ liệu. Bằng cách thiết lập cấu trúc dữ liệu chuẩn, áp dụng các hàm tính toán nâng cao, xây dựng báo cáo và dashboard, đồng thời tự động hoá quy trình bằng VBA hay Power Query, bạn có thể xây dựng một hệ thống quản lý kho toàn diện, đáp ứng nhu cầu thực tế và mở rộng trong tương lai.

Bước tiếp theo: Hãy thử triển khai một bảng tính mẫu với các bảng dữ liệu đã nêu, thực hành nhập dữ liệu, tính toán tồn kho, tạo PivotTable và dashboard. Sau đó, dần dần bổ sung macro và Power Query để tự động hoá quy trình. Bạn sẽ thấy Excel không chỉ là công cụ tính toán mà còn là nền tảng quản lý dữ liệu hiệu quả cho kho hàng hóa của mình.

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.