Hướng dẫn lập bảng tổng hợp các tài khoản ngân hàng trên Excel
Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách lập bảng (sổ) tổng hợp các tài khoản ngân hàng trong kế toán trên Microsoft Excel.
Xem nhanh
Mục đích và vai trò của sổ (bảng) tổng hợp các tài khoản ngân hàng
– Sổ (bảng) tổng hợp các tài khoản ngân hàng với mục đích đưa ra cái nhìn chung nhất, tổng quan nhất về tình hình biến động, phát sinh đối với các ngân hàng mà đơn vị có giao dịch trong kỳ (mỗi ngân theo dõi riêng trên 1 tài khoản, 1 đối tượng). Hay nó chính là: Đơn vị có bao nhiêu tài khoản ngân hàng thì trên sổ (bảng) tổng hợp các tài khoản ngân hàng cũng cần có từng đó dòng.
– Căn cứ để lập bảng này là số liệu từ các Sổ kế toán chi tiết tiền gửi ngân hàng.
Hướng dẫn cách làm sổ kế toán chi tiết tiền gửi ngân hàng trên Excel
– Sổ (bảng) tổng hợp các tài khoản ngân hàng thường được lập vào cuối kỳ kế toán (có thể là cuối tháng, cuối quý hoặc cuối năm).
Mẫu sổ (bảng) tổng hợp các tài khoản ngân hàng
Về mẫu sổ thì khá linh hoạt. Tuy nhiên, vẫn phải đảm bảo được việc phản ánh chính xác, tổng hợp được những phát sinh tăng, phát sinh giảm của các ngân hàng mà đơn vị có giao dịch trong kỳ.
Trong bài viết này Học Excel Online sử dụng mẫu sổ (bảng) tổng hợp các tài khoản ngân hàng như sau, bạn có thể tải về ở link cuối bài viết:
Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:
Hướng dẫn lập sổ (bảng) tổng hợp các tài khoản ngân hàng trên Excel
Trong phần này, Học Excel Online sẽ hướng dẫn lập sổ (bảng) tổng hợp các tài khoản ngân hàng trên Excel bằng cách sử dụng các hàm Excel khá đơn giản.
Nếu như bạn theo dõi thì trong một bài viết gần đây, Học Excel Online cũng đã hướng dẫn cách lập sổ kế toán chi tiết tiền gửi ngân hàng trên Excel. Và để cho các bạn thuận tiện trong việc theo dõi thì mình xin sử dụng lại dữ liệu trong bài đó, cụ thể như sau:
Bảng DATA:
Và bảng danh mục ngân hàng:
Hàm Excel sử dụng
Hàm Excel chính mà trong bài viết này Học Excel Online sử dụng để lập sổ (bảng) tổng hợp các tài khoản ngân hàng là hàm SUMIFS:
Cách sử dụng hàm sumifs và sumif với nhiều điều kiện – một số ví dụ công thức
Ta sẽ sử dụng hàm SUMIFS để tính ra tổng số dư đầu kỳ, tổng số thu vào và tổng số chi ra của từng ngân hàng.
Cách thực hiện
Sau khi đã nắm được mục đích, vai trò, mẫu sổ (bảng) cũng như hàm Excel sử dụng thì ở phần này hãy cùng Học Excel Online thực hiện việc lập sổ (bảng) tổng hợp các tài khoản ngân hàng.
Để cho thuận tiện trong việc tính toán và theo dõi thì mình nghĩ ta nên Define Name (đặt tên) cho các vùng tính tổng và vùng điều kiện. Chi tiết như sau:
SO_TIEN: Là vùng $J$2:$J$310 bên Sheet DATA_NKC (đây là vùng ta cần tính tổng)
CT_NO: Là vùng $H$2:$H$310 bên Sheet DATA_NKC (đây là vùng điều kiện bên Nợ)
CT_CO: Là vùng $I$2:$I$310 bên Sheet DATA_NKC (đây là vùng điều kiện bên Có)
NGAY: Là vùng $B$2:$B$310 bên Sheet DATA_NKC (đây là vùng điều kiện ngày tháng).
Về cách đặt tên trong Excel bạn có thể tham khảo Video sau:
Cách sử dụng name range trong excel, đặt tên cho vùng dữ liệu
Tính tổng số tồn (dư) đầu kỳ
Với các vùng tính tổng cũng như vùng điều kiện như trên ta sẽ có công thức để tính ra số tồn (dư) đầu kỳ như sau:
=SUMIFS(SO_TIEN, CT_NO, C11, NGAY, “<” & $G$7) – SUMIFS(SO_TIEN, CT_CO, C11, NGAY, “<” & $G$7)
Số tồn đầu kỳ ở đây được tính là số tồn tính tới trước thời điểm từ ngày (ô G7). Hay nó chính là: Tổng số phát sinh bên Nợ trừ đi tổng số phát sinh bên Có tính tới trước thời điểm từ ngày.
Tính tổng sổ thu vào (phát sinh bên Nợ).
Tiếp theo, ta sẽ tính số thu vào (số phát sinh bên Nợ) trong khoảng thời gian từ ngày (ô G8) đến ngày (ô G8). Cụ thể như sau:
Tại ô G11 bạn gõ vào công thức:
=SUMIFS(SO_TIEN, CT_NO, C11, NGAY, “>=” & $G$7, NGAY,”<=” & $G$8)
Đây là tổng số tiền phát sinh bên Nợ của từng tài khoản ngân hàng trong khoảng thời gian từ ngày đến ngày.
Tính tổng số chi ra (phát sinh bên Có)
Cũng tương tự như tính tổng số thu vào, thì với tổng số chi ra (phát sinh bên Có), ta có công thức sau:
=SUMIFS(SO_TIEN, CT_CO, C11, NGAY, “>=” & $G$7, NGAY, “<=” & $G$8)
Đây là tổng só tiền phát sinh bên Có của từng tài khoản ngân hàng trong khoảng thời gian từ ngày đến ngày.
Bạn chỉ cần thay vùng điều kiện CT_NO của công thức tính tổng số thu vào bằng CT_CO là có thể có được công thức để tính tổng số tiền chi ra.
Tính tổng tồn (dư) cuối kỳ
Ta cũng đã biết thì số dư cuối kỳ ở đây sẽ được xác định bằng: Số dư đầy kỳ + Số thu vào – Sổ chi ra. Với nguyên tắc đó thì ta sẽ có công thức đơn giản để tính ra số tồn (dư) cuối kỳ như sau:
=F11+G11-H11
Sau khi đã tính toán xong ta thực hiện FillDown các công thức đó xuống cho các dòng còn lại, rồi với hàm SUM đơn giản để tính dòng Cộng. Bạn sẽ có kết quả như sau:
Kết luận
Qua bài viết này, Học Excel Online hy vọng bạn đã nắm được cách dùng hàm Excel để lập được sổ (bảng) tổng hợp các tài khoản ngân hàng một cách chính xác, hiệu quả và hoàn toàn linh hoạt khi thay đổi khoảng thời gian muốn xem tổng hợp.
Download file đính kèm bài viết ở đây: Hướng dẫn lập bảng tổng hợp các tài khoản ngân hàng trên Excel
Cách ghi bảng tổng hợp các tài khoản ngân hàng trên excel
Hướng dẫn cách làm sổ kế toán chi tiết tiền gửi ngân hàng trên excel
Hướng dẫn làm sổ quỹ tiền mặt, 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 làm phiếu xuất kho trên excel.
Khóa học mới xuất bản
