Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách tạo “Sổ quỹ tiền mặt” và “Sổ kế toán chi tiết quỹ tiền mặt” trong kế toán bằng hàm Excel, có thể tự động thay đổi dữ liệu khi thay đổi thời gian.
– Sổ quỹ tiền mặt mẫu số S07-DN được dùng cho thủ quỹ hoặc dùng cho kế toán tiền mặt để phản ánh tình hình thu, chi tồn quỹ tiền mặt bằng tiền việt nam của đơn vị.
– Mỗi quỹ dùng một sổ hay một số trang sổ. Sổ này cũng dùng cho kế toán chi tiết quỹ tiền mặt và tên sổ sửa lại là “Sổ kế toán chi tiết quỹ tiền mặt”. Tương ứng với 1 sổ của thủ quỹ thì có 1 sổ của kế toán cùng ghi song song.
– Để theo dõi quỹ tiền mặt, kế toán quỹ tiền mặt phải mở “Sổ kế toán chi tiết quỹ tiền mặt” (Mẫu số S07a-DN). Sổ này có thêm cột “Tài khoản đối ứng” để phản ánh số hiệu Tài khoản đối ứng với từng nghiệp vụ ghi Nợ, từng nghiệp vụ ghi Có của Tài khoản 111 “Tiền mặt”.
Trong phần này, Học Excel Online sẽ hướng dẫn cách lập sổ quỹ tiền mặt bằng cách sử dụng các hàm Excel cơ bản. Với ví dụ mẫu là bảng DATA như sau, với số liệu từ ngày 31/12/2015 đến 13/01/2016:
Hàm Excel sử dụng để lập sổ quỹ tiền mặt, sổ kế toán chi tiết quỹ tiền mặt
Ví dụ với dữ liệu về tình hình phát sinh thu chi của một đơn vị như trên, thì bạn sẽ sử dụng một số hàm Excel sau đây để có thể lập ra được sổ quỹ tiền mặt, tự động thay đổi theo khoảng thời gian.
Sử dụng hàm SUMIFS để tính số dư đầu kỳ
Sử dụng kết hợp hàm IF, AND để lấy số liệu phát sinh trong kỳ
Sử dụng hàm Sumif để tính số tồn quỹ cuối kỳ:
Nếu như các bạn đã có kiến thức cơ bản về Excel thì mình nghĩ những hàm này sẽ không làm khó được bạn bạn không nào.
Chỉ cần sử dụng một số hàm cơ bản thế thôi là chúng ta đã có thể lập được sổ quỹ, sổ kế toán chi tiết quỹ tiền mặt rồi. Phần hướng dẫn cách thực hiện sẽ giúp bạn rõ hơn.
Hướng dẫn cách thực hiện
Sau khi bạn đã có mẫu sổ quỹ tiền mặt như hình trên (bạn có thể tự thiết kế hoặc tải File đính kèm ở cuối bài viết). Ở đây chúng ta sẽ căn cứ vào thời gian để lấy ra số liệu, hay nói cách khác là khi bạn thay đổi thời gian thì số liệu cũng sẽ được cập nhật tương ứng.
Tính số dư đầu kỳ
Để tính số dư đầu kỳ ta sử dụng hà m SUMIFS như sau:
Số dư đầu kỳ ở đây là số tiền còn dư trong quỹ đến trước từ ngày (ô I7)
Công thức ở ô J13 bạn có thể hiểu như sau:
=SUMIFS(DATA!J3:J99,DATA!B3:B99,”<” & I7,DATA!F3:F99,1111) – SUMIFS(DATA!J3:J99,DATA!B3:B99,”<” & I7,DATA!G3:G99,1111)
- Hàm SUMIFS thứ nhất sẽ tính tổng số tiền với 2 điều kiện là ngày nhỏ hơn từ ngày (J7) và TK_NO là 1111 (tức đây là số tiền phát sinh bên Nợ).
- Hàm SUMIFS thứ 2 sẽ tính tổng số tiền với 2 điều kiện là ngày nhỏ hơn từ ngày (J7) và TK_CO là 1111 (tức đây là số tiền phát sinh bên Có).
- Số tiền bên Nợ – số tiền bên Có sẽ ra số dư đầu kỳ đến tại thời điểm từ ngày.
Lấy các thông tin phát sinh
Để lấy lên sổ quỹ tiền mặt các thông tin phản ánh tình hình thu, chi trong kỳ bạn sử dụng kết hợp 2 hàm IF, AND như sau:
Giải thích công thức ở ô B15 như sau:
=IF(AND(DATA!B3>=$I$7,DATA!B3<=$I$8),DATA!B3,””)
Ta sẽ đi so sánh lần lượt các ô ở cột NGAY bên Sheet DATA bắt đầu từ ô B3 với điều kiện là nó phải nằm trong khoảng từ ngày đến ngày (tức >=$I$7 và <=$I$8), nếu thoả mãn cả 2 điệu kiện đó thì sẽ trả về ngày. Các ô khác như ngày tháng chứng từ, diễn giản bạn có thể làm tương tự.
Ở đây, nếu bạn thêm cột Tài khoản đổi ứng (TKDU) nữa thì “Sổ quỹ tiền mặt” sẽ thành “Sổ kế toán chi tiết quỹ tiền mặt”, và bạn muốn điều đó thì mình giới thiệu luôn cách để bạn có thể lấy ra (TKDU):
Công thức ở ô G15 như sau:
=IF(AND(DATA!B3>=$I$7,DATA!B3<=$I$8),IF(DATA!F3=1111,DATA!G3,IF(DATA!G3=1111,DATA!F3,””)),””)
Chúng ta sẽ có 2 hàm IF lồng nhau. Hàm IF đầu tiên sẽ kiểm tra xem giá trị ngày có thoả mãn điều kiện nằm trong khoảng từ ngày đến ngày hay không, nếu thỏa mãn thì sẽ xét 1 hàm IF tiếp để lấy ra tài khoản đối ứng.
- Nếu TK_NO = 1111 (tức giá trị trong cột F bắt đầu từ ô F3 thì TKDU của nó sẽ là TK_CO (tức giá trị trong cột G bắt đầu từ G3), và ngược lại
- Nếu TK_CO = 1111 (tức giá trị trong cột G bắt đầu từ ô G3 thì TKDU của nó sẽ là TK_NO (tức giá trị trong cột F bắt đầu từ F3).
Hy vọng với phần giải thích trên bạn đã nắm được công thức để lấy ra TKDU. Từ đó có thể đưa “sổ quỹ tiền mặt” thành “Sổ kế toán chi tiết quỹ tiền mặt” rồi. Tuy nhiên, nếu bạn vẫn chưa hình dung ra thì bạn có thể tải File đính kèm dưới bài viết này để nắm rõ thêm nhé.
Tính số tồn
Tiếp theo, chúng ta sẽ đi tính số tiền tồn quỹ với hàm SUM khá đơn giản như sau:
Công thức ở ô J15 như sau:
=$J$13+SUM($H$15:H15) – SUM($I$15:I15)
Số tồn sẽ được tính bằng tồn đầu + thu – chi.
- Ở đây bạn cần cố định tồn đầu ($J$13)
- SUM($H$15:H15): Khi bạn kéo công thức xuống thì nó sẽ thành SUM($H$15:H16),SUM($H$15:H17),… tiếp theo cho các ô còn lại
- SUM($I$15:I15): Tương tự như trên, khi bạn kéo công thức xuống tới dòng nào thì sẽ trả về số tồn quỹ tương ứng ở dòng đó.
Tạo cột phụ để lọc phục vụ in ấn
Sau khi thực hiện xong các bước trên thì tới đây gần như bạn đã hoàn thành việc tạo “Sổ quỹ tiền mặt” cũng như là “Sổ kế toán chi tiết quỹ tiền mặt” một cách tự động và hoàn toàn linh hoạt khi thay đổi ngày tháng rồi. Để phục vụ cho việc in ấn sổ sách khi cần thì bạn có thể tạo thêm một cột phụ nữa để lọc ra chỉ những dòng có dữ liệu.
Tại ô L15 bạn đặt công thức như sau, sau đó FillDown cho các dòng còn lại (bạn chỉ cần FillDown tới dòng 111 của cột L). Những ô nào thoả mãn điều kiện sẽ được đánh dấu “x”.
=IF(AND(DATA!B3>=$I$7,DATA!B3<=$I$8),”x”,””)
Và khi nào bạn muốn in ấn thì bạn lọc cột L đó với điều kiện là “x”. Bạn gõ thêm dấu “x” vào ô L12, L13 vùng từ L112:L120, để khi lọc ra có kết quả giống như hình.
Một vài chú ý
Với cách làm này thì bạn cần chú ý một số điểm như sau: – Dữ liệu của bạn có bao nhiêu dòng thì bên sổ quỹ tiền mặt của bạn cũng cần có từng đó dòng. Với dữ liệu ít thì không sao tuy nhiên với dữ liệu lớn thì Excel sẽ xử lý chậm hơn do phải tính toán nhiều. – Để giải quyết vấn đề đó thì bạn có thể dùng công thức mảng hoặc nâng cao hơn là dùng VBA, SQL,…. Tuy nhiên, do mục đích chính của bài viết này là hướng dẫn các bạn dùng hàm Excel cơ bản để có thể tạo ra được “Sổ quỹ tiền mặt” và “Sổ kế toán chi tiết quỹ tiền mặt” trong kế toán, mà với hầu hết chúng ta đều có thể tự tay thực hiện. Nên mình xin phép không đề cập tới kiến thức nâng cao. Hy vọng tới đây bạn đã có thể tự tạo được cho mình được “Sổ quỹ tiền mặt” và “Sổ kế toán chi tiết quỹ tiền mặt” với dữ liệu tự động thay đổi theo ngày tháng rồi. Về File mà Học Excel Online dùng để minh hoạ cho bài viết này, bạn có thể thể tải về ở Link sau: File đính kèm bài viết: Hướng dẫn cách làm sổ quỹ tiền mặt và sổ kế toán chi tiết tiền mặt trên Excel.
Hướng dẫn cách tạo phiếu thu, phiếu chi trên excel.
Hướng dẫn làm phiếu nhập kho trên Excel.
Hướng dẫn cách xác định ngày sinh nhật của nhân viên bằng hàm excel.
Chuyển dữ liệu ngày tháng về dạng chữ trong excel.
Tôi là Nguyễn Văn Sỹ có 15 năm kinh nghiệm trong lĩnh vực thiết kế, thi công đồ nội thất; với niềm đam mê và yêu nghề tôi đã tạo ra những thiết kếtuyệt vời trong phòng khách, phòng bếp, phòng ngủ, sân vườn… Ngoài ra với khả năng nghiên cứu, tìm tòi học hỏi các kiến thức đời sống xã hội và sự kiện, tôi đã đưa ra những kiến thức bổ ích tại website nhaxinhplaza.vn. Hy vọng những kiến thức mà tôi chia sẻ này sẽ giúp ích cho bạn!