Lập thẻ kho với công thức tính dồn trong Excel và Add-in A-Tools

20:09 Nặc danh 0 Comments

Như chúng ta đã biết, một trong những công tác thường xuyên, quen thuộc của kế toán và quản lý kho là lập thẻ kho. Việc làm này có ý nghĩa để theo dõi số lượng nhập, xuất, tồn kho từng nguyên liệu, vật liệu, công cụ, dụng cụ, sản phẩm, hàng hóa ở từng kho, qua đó làm căn cứ xác định số lượng tồn kho dự trữ vật liệu, dụng cụ, sản phẩm, hàng hóa, đồng thời xác định trách nhiệm vật chất của thủ kho cũng như các thành viên liên quan khác.

Trong Excel, việc lập thẻ kho chính là chúng ta tạo ra các cột dữ liệu gồm : ngày tháng, số lượng nhập, số lượng xuất, số lượng tồn chi tiết theo từng mã hàng, trong đó tồn chi tiết được tính theo phương pháp tính cộng dồn (lũy kế). Công việc này nếu thực hiện tính toán bằng công thức Excel sẽ khá khó khăn và công thức chạy rất chậm vì ta cần phải lấy nguyên dữ liệu từ dữ liệu gốc sang.

Add-in A-Tools sẽ giúp bạn khắc phục hoàn toàn nhược điểm của công thức Excel truyền thống:

+ Theo điều kiện mã hàng cho trước, dữ liệu trích lọc đến đâu thì công thức được thực hiện đến đó. Chúng ta không phải copy công thức cho nhiều dòng dữ liệu trống nên làm nhẹ file Excel rất nhiều so với dùng công thức Excel.

+ Dữ liệu được lọc ra theo điều kiện chỉ với một công thức duy nhất, khi thay đổi điều kiện, các dữ liệu tự động được cập nhật và thay đổi theo.

Video hướng dẫn chi tiết

Cách làm

Bước 1: Đặt tên bảng dữ liệu nguồn

+ Bôi đen bảng dữ liệu nguồn.

+ Chọn "Define Name".

+ Tại mục "Name" bảng thiết lập, ta gõ tên bảng dữ liệu nguồn. Ví dụ: KHO

Bước 2: Dùng công thức A-Tools tạo thẻ kho

1. Lấy ra cột ngày,, số lượng nhập, số lượng xuất từ bảng dữ liệu nguồn

+ Đặt chuột tại ô trống muốn lấy ra dữ liệu, tìm đến menu A-Tools, chọn "SQL_Builder"

+ Kéo bảng "KHO" vào vùng Main của SQL_Builder.

+ Tích chọn tên cột muốn lấy ra dữ liệu và tên cột điều kiện. Ở ví dụ này, ta cần tích chọn các cột: ngày (NGAY), số lượng nhập (SLG N), số lượng xuất (SLG X), mã vật liệu sản phẩm hàng hóa (MA_VLSPHH)

+ Tại mục thiết lập chi tiết cho từng cột, bỏ tích chọn "Output" tại cột điều điện MA_VLSPHH, đồng thời tại mục "Criteria", ta chọn "Range" và trỏ chuột đến ô điều kiện. Ví dụ: 'Range(B3)'

+ Chọn "Tiếp tục".

+ Tại mục "Kết quả truy vấn trả về theo", ta chọn "Hàm BS_SQL".

+ Kết quả, khi ta thay đổi điều kiện mã hàng, các thông tin ngày, số lượng nhập xuất sẽ tự động thay đổi theo điều kiện ta chọn như sau:

2. Sử dụng hàm DATA() để tạo công thức cộng dồn cho cột "TON"

+ Mục đích của hàm DATA() là lấy dữ liệu tại một mảng dữ liệu xác định.

+ Cấu trúc hàm DATA() :

   DATA(Row,Col)

   Trả về giá trị tại dòng (Row) và cột (Col) trong mảng giá trị. 

   Row và Col là số nguyên dương (>=0) có thể nhận các biến hệ thống dưới đây:

          PreRow: Trả về dòng liền trước.

          Row: Trả về dòng đang hoạt động.

          PreCol: Trả về cột liền trước.

          Col: Trả về cột đang hoạt động.

Lưu ý: Chỉ số cột và chỉ số dòng lấy từ giá trị 0.

+ Kích chuột vào ô có chứa công thức của thẻ kho, công thức BS_SQL hiển thị, chúng ta thêm cột TON sử dụng công thức DATA() theo nguyên tắc "Tính dồn = Tồn trước + Nhập - Xuất" như sau:

Ta đặt tên cột mới bằng cách gõ "AS TON".

+ Nhấn Ctrl+Shift+ Enter để hiển thị kết quả (Vì ta đang sử dụng công thức mảng)

+ Thêm dòng tổng cho cột nhập, xuất và định dạng lại bảng dữ liệu đẹp mắt hơn.

Bước 3: Tạo bảng tìm kiếm cho mã hàng rất nhanh chóng và tiện ích.

+ Tạo bảng danh mục vật liệu sản phẩm hàng hóa với các thông tin chi tiết như sau:

+ Tại sheet thẻ kho, bôi đen 2 ô cần lấy ra dữ liệu điều kiện bao gồm thông tin mã hàng hóa và tên hàng hóa, ví dụ bôi đen 2 ô B3 và C3.

+ Chọn A-Tools trên thanh menu, chọn mục "Nhập liệu nâng cao".

+ Chúng ta chọn đồng ý khi nhận được thông báo "Bạn có muốn thiết lập vùng cho "Nhập nhiều cột"?"

+ Tại mục "Dữ liệu nguồn", ta trỏ chuột chọn bảng danh mục dữ liệu nguồn. A-Tools sẽ gợi ý cho chúng ta tên cột nhập liệu và cột dữ liệu nguồn tương ứng. Ta có thể thay đổi thứ tự tên cột dữ liệu nguồn tương ứng với cột nhập liệu.

+ Tích chọn "Có dòng tiêu đề" để hiển thị tên tiêu đề.

+ Chọn cột nhận/kiểm tra giá trị trong danh sách tên cột trong bảng dữ liệu nguồn.

+ Tích chọn cột cần lấy giá trị chứa duy nhất.

+ Tích chọn "Nhập nhiều cột"

+ Nhấn "Thêm" để lưu lại thông tin.

Nếu muốn sửa lại thông tin ta chỉ cần vào chức năng "Nhập liệu nâng cao". Tại bảng vùng nhập liệu, chúng ta chọn vùng thiết lập cần sửa, sau đó chọn "Sửa" để tiến hành sửa lại.

Như vậy chỉ với thao tác cơ bản , ta đã tạo ra bảng tìm kiếm nhanh cho mã hàng rất nhanh chóng và tiện lợi. Khi mã hàng được chọn, thông tin tên hàng được tự động nhảy theo mà ta không cần phải sử dụng hàm VLOOKUP tìm kiếm khó nhớ.

Như vậy, với việc sử dụng hàm BS_SQL trong A-Tools bạn sẽ tạo được báo cáo có tính cộng trừ dồn, hay tính lũy kế với lệnh SQL kết hợp với hàm DATA trở nên rất dễ dàng. Nắm được phương pháp này các bạn sẽ làm được nhiều dạng báo cáo khác trong doanh nghiệp như sổ quỹ, theo dõi trừ nợ,...

Các bạn tham khảo khóa học "Tạo báo cáo động trong Excel và Add-in A-Tools" tại đây