Việc theo dõi biến động vật tư, hàng hóa trong kho là nhiệm vụ quan trọng của kế toán. Sử dụng Excel giúp công việc này trở nên dễ dàng và hiệu quả hơn. Bài viết này hướng dẫn chi tiết cách lập sổ kế toán chi tiết vật tư, sản phẩm, hàng hóa trên Excel, giúp tự động cập nhật số liệu khi thay đổi mã vật tư hoặc ngày tháng.

Mục đích của sổ kế toán chi tiết là theo dõi nhập, xuất, tồn kho (cả số lượng và giá trị) của từng loại nguyên vật liệu, công cụ dụng cụ, sản phẩm, hàng hóa tại từng kho. Sổ này là căn cứ đối chiếu với sổ sách của thủ kho, được mở chi tiết theo từng tài khoản (152, 153, 155, 156), từng kho và từng loại vật tư, sản phẩm, hàng hóa.

Bạn có thể tự tạo sổ trên Excel để rèn luyện kỹ năng hoặc tham khảo mẫu sổ được cung cấp.

Mẫu sổ kế toán chi tiết vật tư, sản phẩm, hàng hóa trên Excel

Phần tiếp theo sẽ hướng dẫn cách lập sổ chi tiết trên Excel với dữ liệu minh họa. Kỹ năng Excel rất quan trọng đối với kế toán, ngay cả khi đã sử dụng phần mềm chuyên dụng.

Ví dụ bảng dữ liệu nhập xuất tồn kho

Cần có bảng danh mục vật tư, sản phẩm, hàng hóa để tra cứu thông tin.

Bảng danh mục vật tư, sản phẩm, hàng hóa

Một số hàm Excel cơ bản được sử dụng để lập sổ này bao gồm: SUMIFS để tính số dư đầu kỳ, IF kết hợp AND để lấy số liệu phát sinh trong kỳ, và SUM để tính số tồn cuối kỳ.

Tên và đơn vị tính được lấy từ mã vật tư bằng cách dò tìm trong bảng danh mục hàng hóa sử dụng hàm VLOOKUP, INDEX + MATCH,… Ví dụ, công thức tại ô J8 là =VLOOKUP(G8,DM!A3:E6,2,0), trong đó G8 là mã vật tư, DM!A3:E6 là vùng dò trong bảng danh mục, 2 là cột trả về tên vật tư, và 0 là kiểu dò tìm tuyệt đối.

Ví dụ công thức VLOOKUP để lấy tên vật tư

Để tính số dư đầu kỳ (số tồn đến trước “từ ngày”), sử dụng hàm SUMIFS. Ví dụ, công thức tại ô M14 để tính số tiền tồn đầu kỳ là =SUMIFS(DATA!M3:M132,DATA!B3:B132,"<"&$G$9,DATA!F3:F132,$G$8)-SUMIFS(DATA!M3:M132,DATA!B3:B132,"<"&$G$9,DATA!G3:G132,$G$8). Công thức này tính tổng số tiền nhập (cột SO_TIEN) với điều kiện ngày nhỏ hơn “từ ngày” và mã nhập trùng với mã vật tư, sau đó trừ đi tổng số tiền xuất với điều kiện tương tự.

Ví dụ công thức SUMIFS để tính số dư đầu kỳ

Tương tự, tính số lượng tồn đầu kỳ tại ô L14.

Ví dụ kết quả tính số dư đầu kỳ

Để lấy số liệu phát sinh, sử dụng kết hợp hàm IF và AND. Ví dụ, công thức tại ô D15 là =IF(AND(DATA!B3>=$G$9,DATA!B3<=$G$10,DATA!F3=$G$8),DATA!B3,""). Công thức này kiểm tra xem ngày trong bảng DATA có nằm trong khoảng thời gian từ ngày đến đến ngày và mã nhập có trùng với mã vật tư hay không. Nếu đúng, trả về ngày tháng, ngược lại trả về giá trị rỗng.

Ví dụ công thức IF và AND để lấy số liệu phát sinh

Các trường thông tin khác được lấy tương tự. Sau khi xác định được ngày tháng phù hợp, có thể sử dụng cột ngày tháng này để lấy các thông tin còn lại, giúp công thức ngắn gọn và dễ hiểu hơn.

Ví dụ cách lấy thông tin khác dựa vào ngày tháng

Tính số lượng tồn tại ô L15 bằng công thức =$L$14+SUM($H$15:H15)-SUM($J$15:J15). Tương tự, tính số tiền tồn tại ô M15.

Ví dụ công thức tính số tồn

Cuối cùng, tính tổng nhập, tổng xuất và tạo cột phụ để lọc dữ liệu. Công thức cho cột phụ là =IF(D19="","","x"). Sau khi lọc, kết quả sẽ hiển thị rõ ràng hơn.

Kết quả sau khi lọc dữ liệu

Lưu ý rằng khi dữ liệu lớn, việc tính toán có thể làm chậm Excel. Có thể sử dụng công thức mảng, VBA, SQL để tối ưu hiệu suất. Tuy nhiên, bài viết này chỉ hướng dẫn cách làm với các hàm Excel cơ bản, phù hợp với người dùng chưa có nhiều kinh nghiệm.