Đối với các cửa hàng có quy mô vừa và nhỏ, việc bỏ vốn để xây dựng một “Database” chuyên nghiệp là một việc không cần thiết, chúng khá tốn kém và quá phức tạp cho các chủ cửa hàng nhỏ. Nhưng không vì thế quản lý kho không quan trọng. Tuy là một công việc trông có vẻ đơn giản nhưng nếu chúng ta biết cách hệ thống hóa chúng thì sẽ mang lại nhiều tiện ích hơn chúng ta tưởng. Chúng giúp ta có thể dễ dàng trong việc quản lý cung ứng, nhập liệu và xây dựng một vài “Dashboard” đơn giản để trực quan hóa dữ liệu trong kho. Từ đó giúp ta có thể cân đối trong việc nhập hàng và đưa ra những chiến lược giải quyết hàng tồn.
Microsoft Excel là một công cụ vô cùng thích hợp để làm điều này. Quản lý kho bằng Excel là một phương pháp được khá nhiều chủ cửa hàng sử dụng. Sau đây mình sẽ xây dựng một “Project” đơn giản quản lý kho hàng bằng công cụ Excel mà không cần dùng đến quá nhiều code VBA phức tạp. Nào…Let’s get it!
Bố cục bài viết sẽ chia thành 6 phần:
4. Tạo Macros
6. Chạy thử
Đầu tiên, xây dựng “sườn” cho cơ sở dữ liệu kho hàng
File Excel của mình sẽ bao gồm 6 worksheets: Nhập liệu, dữ liệu mua hàng, dữ liệu bán hàng, kho, thông tin sản phẩm, dashboard.
Sheet “Nhập liệu”
Hãy tạo một “giao diện User” thật bắt mắt nhé. Tại bước này mình sẽ sử dụng những thao tác cơ bản trong Excel để thiết kế giao diện nhập liệu như: tô màu ô, thêm các shape, chèn ảnh, chèn icon, vẽ border, …
Đây là giao diện trong project của mình nhé, mọi người có thể tham khảo qua.
Vì đây không phải mục đích chính của “project” này nên mình “design” giao diện hơi xấu xí nha. Mọi người có thể đầu tư hơn để có một giao diện nhập liệu đẹp hơn.
Lưu ý:
Vùng này các bạn không nên tạo shape nha. Bởi vì lúc sau mình sẽ dùng những ô này để tham chiếu, phục vụ cho việc nhập liệu tự động nữa.
Video hướng dẫn: Thiết kế giao diện nhập liệu
Sheet “Thông tin sản phẩm”
Tại sheet này, gồm 2 thành phần chính đó là bảng mã sản phẩm và một “form” để thêm mã sản phẩm.
Tại bảng mã sản phẩm các bạn có thể thêm những “Attribute” cho sản phẩm như: Tên sản phẩm, nhà cung cấp, khối lượng, … Bảng này là một bảng định danh với “Primary key” là Mã sản phẩm nên các dòng sẽ là riêng biệt tại cột “Ma san pham”. Nếu các bạn muốn biết thêm thông tin về “Dimension Table”, “Primary key”, “Entity-relationship model”, … hãy đoán chờ tại phần “Học cùng diiy diyy” nhé.
Sheet “Dữ liệu mua hàng”
Sheet gồm bảng chứa các thông tin mua hàng. Bảng thông tin mua hàng gồm: Ngày, mã sản phẩm, đơn giá, số lượng được nhập từ form nhập liệu và một cột thành tiền được tính bằng cách lấy đơn giá nhân với số lượng.
Sheet “Dữ liệu bán hàng”
Sheet này tương tự sheet “Dữ liệu mua hàng” nhưng thay vì chứa thông tin mua hàng thì sheet này chứa thông tin bán hàng.
Sheet “Kho”
Sheet gồm bảng chứa thông tin kho hàng
Những bảng mua hàng và bán hàng đa số dữ liệu sẽ được cập nhập tự động chỉ có cột thành tiền là được tính toán thêm trên bảng. Nhưng đối với bảng Kho các cột không được nhập liệu mà là truy xuất dữ liệu từ 2 bảng kia.
Video hướng dẫn: Thiết lập dữ liệu Sheet "Kho"
Sheet “Dashboard”
Sheet gồm những biểu đồ trực quan hóa dữ liệu trong kho. Ví dụ: thống kê lượng hàng tồn kho theo tháng, theo sản phẩm, …
Tạo “Hyperlink” để liên kết các sheet lại với nhau
Các bước tạo “Hyperlink”:
Chọn đối tượng (ở đây là Shapes mình đã thiết kế) >> vào thẻ Insert trên thanh công cụ >> Link >> Insert link >> Place in this document >> Nhấn chọn Sheet mà mình muốn liên kết >> Nhấn OK
Video minh họa: Hướng dẫn chèn Hyperlink để tạo liên kết các Sheet
Tạo các ràng buộc dữ liệu nhập bằng “Data Validation”
Các bước tạo ràng buộc dữ liệu nhập vào (Data Validation):
Chọn vào ô tính (cell) muốn ràng buộc dữ liệu nhập >> vào thẻ Data trên thanh công cụ >> Chọn Data Validation >> tại mục Allow chọn option mà mình muốn ràng buộc như: List (danh sách), Date (ngày, tháng), … hoặc ta có thể chọn custom để có thể tự tạo ràng buộc riêng cho ô tính >> sau khi thiết lập ràng buộc xong nhấn OK.
Video minh họa: Hướng dẫn Set Data Validation cho Project
Hàm ràng buộc cho ô số lượng bán hàng:
=AND(ISNUMBER(H11),H11<VLOOKUP(H9,Kho!E:H,4,0))Tạo Marcos để tự động thêm dữ liệu
Tạo Marcos trong thẻ Developer trên thanh công cụ. Nếu thanh công cụ của bạn không có thẻ Developer thì bạn có thể mở bằng các bước sau:
Nhấp chuột phải vào khoảng trắng bất kỳ trên thanh công cụ >> Chọn Customize the Ribbon… >> Tick chọn Developer xong nhấn OK.
Các bước tạo Marcos:
Trong thẻ Developer nhấn chọn Record Marco >> Đặt tên Marco >> OK >> “Marco bắt đầu ghi các thao tác” >> Stoping Marco
Video minh họa: Hướng dẫn tạo Macros: Mua hàng, bán hàng, thêm sản phẩm
Code VBA chèn vào để tạo ràng buộc cho nhập liệu:
Mua hàng
If Excel.WorksheetFunction.CountA(Sheets("Nhaplieu").Range("D8:D11")) < 4 Then
MsgBox "Chua du so lieu"
Exit Sub
End IfBán hàng
If Excel.WorksheetFunction.CountA(Sheets("Nhaplieu").Range("H8:H11")) < 4 Then
MsgBox "Chua du so lieu"
Exit Sub
End IfThêm sản phẩm
If Excel.WorksheetFunction.CountBlank(Sheets("MaSP").Range("D4")) = 1 Then
MsgBox "Chua nhap masp"
Exit Sub
End If
If Excel.WorksheetFunction.CountIf(Sheets("MaSP").Range("A:A"), Sheets("MaSP").Range("D4")) = 1 Then
MsgBox "Masp da ton tai"
Exit Sub
End IfTạo Dashboard đơn giản
Tạo các biểu đồ trực quan hóa dữ liệu trong kho. Phần này sẽ vận dụng các kiến thức về Power Query, Pivot Table, … Trong Project chỉ tạo một Table phục vụ cho 1 Chart. Bạn có thể xây dựng nhiều bảng được liên kết với nhau để xây dựng Dashboard chuyên nghiệp hơn nhé.
Video minh họa: Tạo Dashboard với dữ liệu được truy xuất từ Power Query
Chạy demo Project
Chạy demo thử file Excel đã xây dựng nhé. Các bạn nhớ kỹ là lưu lại với đuôi “.xlsm” nếu không sẽ mất Marco đó nha.
Video minh họa: Chạy thử file Excel quản lý kho hàng
Như vậy là mình đã xây dựng xong một Project khá đơn giản về quản lý kho hàng. Tuy trông rất thô sơ, thủ công nhưng vô cùng hữu dụng cho những ai muốn áp dụng Excel trong công việc hằng ngày mà không quá thành thạo VBA. Chúc bạn thực hiện thành công.
File hoàn chỉnh:
Comments