Trong thời đại công nghệ hiện nay, Excel vẫn là công cụ phổ biến nhất để quản lý dữ liệu, đặc biệt là trong lĩnh vực bán lẻ, kho bãi, sản xuất và quản lý chuỗi cung ứng. Một trong những ứng dụng quan trọng nhất của Excel là tạo file nhập xuất tồn (hay còn gọi là “file nhập xuất tồn kho”) giúp doanh nghiệp theo dõi tình trạng hàng hóa, lập báo cáo tồn kho, tính toán lợi nhuận và đưa ra quyết định kinh doanh kịp thời. Bài viết này sẽ dẫn dắt bạn qua từng bước thực hiện, từ cách lập cấu trúc bảng tính, nhập dữ liệu, áp dụng công thức, đến cách tự động hóa và xuất báo cáo. Nếu bạn muốn nâng cao hiệu quả quản lý kho và giảm thiểu lỗi thủ công, hãy đọc hết bài này nhé!
Có thể bạn quan tâm: Cách Tạo Các Dòng Bằng Nhau Trong Excel
1. Lý do bạn nên dùng Excel cho file nhập xuất tồn
- Độ linh hoạt: Excel cho phép bạn tùy chỉnh cột, tính năng, công thức tùy ý. Bạn không cần mua phần mềm chuyên dụng.
- Khả năng mở rộng: Từ vài hàng trăm dòng dữ liệu đến hàng.rpc.000 dòng, Excel vẫn có thể xử lý (đối với phiên bản mới hơn Excel 365, tối đa 1,048,576 dòng).
- Tính tương thích: File Excel có thể được chia sẻ dễ dàng, được hỗ trợ rộng rãi trong các tổ chức.
- Tích hợp dữ liệu: Bạn có thể kết nối Excel với các nguồn dữ liệu khác (SQL, Access, CSV) để tự động cập nhật.
- Sử dụng công thức và macro: Tính năng mạnh mẽ giúp tính toán tồn kho, giá trị, tỷ lệ lãi, cũng như tự động hoá quy trình nhập xuất.
Có thể bạn quan tâm: Cách Tạo Biểu Đồ Gantt Trong Excel 2026: Hướng Dẫn Chi Tiết Từng Bước
2. Chuẩn bị dữ liệu đầu vào
Trước khi bắt đầu tạo file, bạn cần xác định các loại dữ liệu mà file sẽ lưu trữ:
| Loại dữ liệu | Mô tả |
|---|---|
| Thông tin sản phẩm | Mã hàng, tên hàng, đơn vị tính, giá bán, giá mua, nhà cung cấp |
| Thông tin kho | Tên kho, địa chỉ, số điện thoại liên hệ |
| Nhập kho | Ngày nhập, mã hàng, số lượng nhập, giá nhập, đơn vị tính, số phiếu/đơn hàng |
| Xuất kho | Ngày xuất, mã hàng, số lượng xuất, giá bán, đơn vị tính, khách hàng |
Bạn có thể lưu trữ dữ liệu này trong một bảng tính riêng biệt hoặc trong cùng file nhưng ở các sheet khác nhau. Việc tách dữ liệu giúp bảo trì dễ dàng hơn và tránh lỗi khi cập nhật.
Có thể bạn quan tâm: Cách Tính Định Mức Sản Phẩm Trong Excel
3. Thiết kế cấu trúc file
3.1. Tạo các sheet
| Sheet | Mục đích |
|---|---|
| Giá trị_đầu_kho petrol | Bảng dữ liệu giá trị và số lượng tồn kho ban đầu (nếu có). |
| Nhập_kho | Nhập dữ liệu nhập hàng. |
| Xuất_kho | Nhập dữ liệu xuất hàng. |
| Tồn_kho | Tính toán tồn kho cuối kỳ và báo cáo. |
| Bảng_sản_phẩm | Danh sách các sản phẩm, thông tin chi tiết. |
| Bảng_kho | Danh sách các kho, thông tin liên quan. |
| Báo_cáo | Tạo báo cáo tổng hợp (bảng pivot, biểu đồ). |
3.2. Định dạng cột
- Cột A: Ngày (định dạng ngày).
- Cột B: Mã hàng (text).
- Cột C:
Một ID duy nhất cho mỗi giao dịch, có thể là số tự động. - Cột D: Số lượng (số thực).
- Cột E: Đơn giá (định dạng số tiền).
- Cột F بنسبة: Tính giá trị (Số lượng × Đơn giá).
- Cột G: Ghi chú (đoạn văn ngắn).
Trong sheet Bảng_sản_phẩm:
- Cột A: Mã hàng (primary key).
- Cột B: Tên sản phẩm.
- Cột C: Đơn vị tính.
- Cột D: Giá mua (có thể lưu trữ giá trị trung bình).
- Cột E: Giá bán.
- Cột F: Nhà cung cấp.
4. Nhập dữ liệu nhập kho
- Mở sheet Nhập_kho.
- Nhập dữ liệu theo cấu trúc đã thiết lập.
- Để tránh lỗi, bạn nên sử dụng Validation:
- Chọn cột B (Mã hàng) → Data → Data Validation → List →
=$B$2:$B$1000(đường dẫn tới danh sách mã hàng trong sheet Bảng_sản_phẩm). Chọn cột A (Ngày) → Data → Data Validation → Date.
Tính giá trị nhập (cột F):
=D2E2
Kéo công thức xuống đủ dòng.
5. Nhập dữ liệu xuất kho
Quá trình tương tự như nhập kho. Tuy nhiên, cần kiểm tra tồn kho trước khi cho phép xuất. Tạo một công thức kiểm tra hedge:
=IF(ĐịnhKỳ tồn trước - D2 >= 0, "OK", "THÊM HÀNG")
Trong đó, ĐịnhKỳ tồn trước là giá trị tồn kho trước giao dịch hiện tại. Bạn có thể tính bằng cách trừ giá trị nhập và nhập trước đó.
6. Tính toán tồn kho
6.1. Tính tồn kho theo ngày
Bạn có thể dùng công thức SUMIFS để tính tổng số lượng nhập và xuất theo ngày, rồi lấy hiệu số:
=SUMIFS(ĐịnhKỳ nhập, Ngày, ">="&Ngày_bắt đầu, Ngày, "<="&Ngày_kết thúc) - SUMIFS(ĐịnhKỳ xuất, Ngày, ">="&Ngày_bắt đầu, Ngày, "<="&Ngày_kết thúc)
6.2. Tính tồn kho theo mã hàng
Sử dụng công thức SUMIFS với điều kiện mã hàng:
=SUMIFS(ĐịnhKỳ nhập, Mã hàng, A2) - SUMIFS(ĐịnhKỳ xuất, Mã hàng, A2)
Bạn có thể tạo một bảng tồn kho động, ví dụ:
| Mã hàng | Tên | Đơn vị | Tồn kho സ്വീകര |
|---|---|---|---|
Trong đó, cột Tồn kho sử dụng công thức trên.
6.3. Tính giá trị tồn kho
Tính giá trị tồn kho bằng cách nhân số lượng tồn với giá mua trung bình (hoặc giá bán, tùy mục tiêu):
Có thể bạn quan tâm: Cách Tạo Tiêu Đề Trang Sau Trong Excel 2026
= Số lượng tồn Giá mua trung bình
Bạn có thể tính giá mua trung bình bằng công thức AVERAGEIF:
=AVERAGEIF(Mã hàng, A2, Giá mua trong bảng_sản_phẩm)
7. Tự động hoá quy trình với Macro
Nếu bạn muốn tự động hoá việc nhập dữ liệu, tính toán và báo cáo, hãy sử dụng VBA.
7.1. Kết nối dữ liệu
Sub ImportData() Dim wsImport As Worksheet Set wsImport = ThisWorkbook.Sheets("Nhập_kho") ' Lấy dữ liệu từ cột A, B, D, E Dim lastRow As Long lastRow = wsImport.Cells(wsImport.Rows.Count, "A").End(xlUp).Row Dim i As Long For i = 2 To lastRow ' Kiểm tra tồn kho trước khi nhập If CheckStock(wsImport.Cells(i, "B").Value, wsImport.Cells(i, "D").Value) Then ' Xử lý nhập Call AddStock(wsImport.Cells(i, "B").Value, wsImport.Cells(i, "D").Value, wsImport.Cells(i, "E").Value) Else MsgBox "Không đủ tồn, cần nhập thêm hàng:" & wsImport.Cells(i, "B").Value End If Next i
End Sub
7.2. Hàm kiểm tra tồn kho
Function CheckStock(ByVal productID As String, ByVal qty As Double) As Boolean Dim wsStock As Worksheet Set wsStock = ThisWorkbook.Sheets("Tồn_kho") Dim currentQty As Double currentQty = Application.WorksheetFunction.SumIf(wsStock.Range("A:A"), productID, wsStock.Range("D:D")) If currentQty >= qty Then CheckStock = True Else CheckStock = False End If
End Function
7.3. Hàm thêm tồn kho
Sub AddStock(ByVal productID As String, ByVal qty As Double, ByVal unitPrice As Double) Dim wsStock As Worksheet Set wsStock = ThisWorkbook.Sheets("Tồn_kho") Dim lastRow As Long lastRow = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row + 1 wsStock.Cells(lastRow, "A").Value = productID wsStock.Cells(lastRow, "B").Value = qty wsStock.Cells(lastRow, "C").Value = unitPrice
End Sub
Những đoạn mã trên giúp bạn tự động kiểm tra tồn kho trước khi nhập, và cập nhật dữ liệu tồn kho một cách nhanh chóng.
8. Xây dựng báo cáo
Bạn có thể tạo báo cáo bằng cách sử dụng PivotTable hoặc Power Pivot để tổng hợp dữ liệu.
8.1. PivotTable tổng hợp tồn kho
- Chọn dữ liệu trong sheet Tồn_kho.
- Insert → PivotTable → New Worksheet.
- KILT:
- Rows: Mã hàng, Tên sản phẩm.
- Columns: Ngày (hoặc kho).
- Values: Tổng số lượng (Số lượng), Tổng giá trị (Giá trị).
- Định dạng bảng, thêm biểu đồ.
8.2. Báo cáo theo ngày, theo kho
Sử dụng công thức PIVOT hoặc INDEX MATCH để tạo bảng báo cáo.
=SUMIFS(ĐịnhKỳ nhập, Ngày, B Angelo, Mã hàng, A2)
Bạn có thể tạo bảng báo cáo tùy chỉnh, ví dụ:
| Ngày | Kho | Mã hàng | Tên | Số lượng nhập | Số lượng xuất | Tồn kho |
|---|---|---|---|---|---|---|
8.3. Tính toán lãi ròng
=Lợi nhuận = (Giá bán trung bình - Giá mua trung bình) Số lượng tồn
Bạn có thể tính lãi ròng cho từng sản phẩm, kho, hoặc tổng hợp.
9. Tối ưu hóa file Excel
- Sử dụng tên dải (Named Range): Giúp công thức dễ đọc và tránh lỗi khi thêm/đổi cột.
- Định dạng Conditional Formatting: Ví dụ, tô đỏ các hàng tồn kho dưới mức cảnh báo, tô xanh các hàng tồn kho cao hơn.
- Bảo vệ sheet: Giữ an toàn dữ liệu bằng cách bảo vệ ô công thức, chỉ cho phép nhập dữ liệu trong ô được xác định.
- Sử dụng hàm OFFSET hoặc INDEX thay cho VLOOKUP: Độ linh hoạt cao hơn, tránh lỗi khi cột thay đổi.
- Tối ưu hóa macro: Sử dụng
Application.ScreenUpdating = FalsevàApplication.Calculation = xlCalculationManualkhi chạy macro để tăng tốc.
10. Kết luận
Việc tạo file nhập xuất tồn trong Excel không chỉ giúp doanh nghiệp quản lý hàng hóa hiệu quả hơn, mà còn giảm thiểu sai sót, nâng cao tính minh bạch và hỗ trợ quyết định kinh doanh nhanh chóng. Bằng cách thiết kế cấu trúc sheet hợp lý, áp dụng công thức SUMIFS, AVERAGEIF, và các hàm điều kiện, bạn có thể tự động tính toán tồn kho, giá trị tồn, lãi ròng, và tạo báo cáo creditors.
Nếu bạn muốn nâng cao hơn, hãy tìm hiểu thêm về Power Query để tự động nhập dữ liệu từ file CSV hoặc SQL, hoặc Power Pivot để phân tích dữ liệu phức tạp hơn. Bên cạnh đó, macro VBA sẽ giúp bạn tự động hoá quy trình nhập xuất, giảm thời gian và công sức.
Hãy bắt đầu từ những bước đơn giản nhất: xác định dữ liệu, tạo sheet, nhập dữ liệu, sau đó dần dần mở rộng bằng công thức và macro. Khi file của bạn đã ổn định, bạn có thể chia sẻ với đồng nghiệp, làm báo cáo cho cấp trên, và cuối cùng là đưa ra các chiến lược kinh doanh dựa trên dữ liệu thực tế.
Chúc bạn thành công trong việc xây dựng file nhập xuất tồn chuyên nghiệp và hiệu quả!
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ẻ.