Phần này sẽgiúp các bạn làm kếtoán trên Excel từcách tổchức
dữliệu đến việc tạo các sổkếtoán tổng hợp và chi tiết. Đểnắm
được phần này yêu cầu bạn phải nắm khá vững vềnghiệp vụkế
toán doanh nghiệp và có một sốkiến thức cơbản vềMicrosoft
office excel chủyếu là phần đã được giới thiệu ởphần trên.
65 trang |
Chia sẻ: lelinhqn | Lượt xem: 1175 | Lượt tải: 0
Bạn đang xem trước 20 trang nội dung tài liệu Thực hành tổ chức số liệu kế toán trên Excel, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
Phần 2 – Tổ chức số liệu kế toán trên Excel
Phần này sẽ giúp các bạn làm kế toán trên Excel từ cách tổ chức
dữ liệu đến việc tạo các sổ kế toán tổng hợp và chi tiết. Để nắm
được phần này yêu cầu bạn phải nắm khá vững về nghiệp vụ kế
toán doanh nghiệp và có một số kiến thức cơ bản về Microsoft
office excel chủ yếu là phần đã được giới thiệu ở phần trên.
Trước tiên ta tạo 1 Sheet chứa danh sách các sổ kế toán, các
bảng biểu và các báo cáo của công ty để dễ dàng quản lý hơn,
từ Sheet này bạn có thể rất nhanh chóng nhảy tới các Sheet
khác bằng cách nhấp chuột.
Cách làm như sau:
- Khởi động Excel, chương trình sẽ tự động mở ra một
Wookbook mới với mặc định là 3 Sheet trắng. Bạn vào Menu
File\Save để lưu lại với tên KtExcel.
2.1. Sổ Nhật ký chung
Đây là sổ lưu trữ tất cả các nghiệp vụ phát sinh của doanh
nghiệp. Dữ liệu từ sổ này sẽ được sử dụng để tạo nên hầu hết tất
cả các sổ sách và báo cáo của doanh nghiệp.
2.1.1. Thiết kế sổ Nhật ký chung
Bước 1: Mở File KtExcel đã thiết kế ở trên, ở Sheet trắng bên
cạnh Sheet Menu ta đổi tên thành SoNKC bằng cách nhấp
chuột phải lên trên tên Sheet sau đó chọn Rename rồi nhập tên
sổ.
Bước 2: Thiết kế sổ Nhật ký chung
Bạn di chuột về góc phía trên bên trái của sheet và nhập vào tên
công ty. Sau đó di chuột xuống dòng bên dưới nhập vào tiêu đề
của sổ sao cho đẹp mắt và dễ nhìn.
Tiếp theo bạn tạo mẫu sổ theo mẫu dưới đây:
2.1.2. Cách nhập dữ liệu vào sổ Nhật ký chung
Khi công ty có các chứng từ kế toán phát sinh như hóa đơn mua
hàng, bán hàng, phiếu nhập kho, phiếu xuất kho… ta lần lượt
nhập vào Sổ nhật ký chung với các thông tin có trên chứng từ
kế toán:
- Ngày tháng: Nhập ngày ghi sổ của chứng từ kế toán
- Chứng từ:
+ Số hiệu: Nhập vào số hiệu của chứng từ kế toán
+ Ngày, tháng: Ngày tháng của chứng từ kế toán
- Diễn giải: Nhập nội dung của chứng từ kế toán
- TK Nợ: Nhập số hiệu tài khoản bên nợ của nghiệp vụ kế toán
phát sinh trên chứng từ.
- TK Có: Nhập số hiệu tài khoản bên có của nghiệp vụ kế toán
phát sinh trên chứng từ.
- Số lượng: Nhập số lượng hàng hóa, dịch vụ phát sinh trên
chứng từ.
- Số tiền phát sinh: Nhập số tiền hàng hóa, dịch vụ phát sinh
trên chứng từ.
Lưu ý: Với mỗi dòng trên Sổ nhật ký chung ta chỉ nhập một
định khoản, với nghiệp vụ kế toán nhiều hơn một định khoản
thì mỗi định khoản sẽ được nhập vào một dòng.
Ví dụ:
Hóa đơn thanh toán tiền điện thoại cho Viễn thông Hà Nội số
012345 ngày 10/01/2009 số tiền 500.000đ, thuế GTGT
50.000đ.
Với nghiệp vụ này trong Sổ nhật ký chung bạn phải nhập thành
hai dòng như sau:
Nợ TK 642 500.000
Có TK 111 500.000
Và một dòng ghi nhận thuế GTGT đầu vào:
Định khoản:
Nợ TK 133 50.000
Có TK 111 50.000
Trong Sổ nhật ký chung sẽ được nhập vào như sau:
Cuối tháng khi đã nhập hết các chứng từ phát sinh trong tháng
ta sẽ làm các bút toán kết chuyển để xác định kết quả lãi lỗ.
Ở hàng nhập nghiệp vụ kết chuyển cuối kỳ tại cột Số tiền phát
sinh:
- Với nghiệp vụ kết chuyển thuế GTGT được khấu trừ:
=SUMIF($E$5:$E27;133;$G$5:$G27)+BCDSPS!D13
Trong đó BCDSPS!D13 chính là địa chỉ ô lưu số dư đầu kỳ của
Tài khoản 133 trong Bảng cân đối số phát sinh sẽ được giới
thiệu chi tiết sau.
- Với nghiệp vụ kết chuyển doanh thu:
Ví dụ với nghiệp vụ kết chuyển cuối kỳ là
Nợ TK 511
Có TK 911
Ta nhập vào công thức sau:
=SUMIF($F$7:$F25; 511; $H$7:$H25)
Tương tự với bút toán kết chuyển Nợ TK 515/Có TK 911 ta
cũng làm tương tự chỉ cần thay đổi điều kiện tính tổng.
- Với các bút toán kết chuyển chi phí:
Ví dụ với nghiệp vụ kết chuyển là
Nợ TK 911
Có TK 642
Ta nhập vào công thức sau:
=SUMIF($E$7:$E25;642;$G$7:$G25)
Tương tự với các bút toán kết chuyển chi phí khác ta cung làm
tương tự chỉ cần thay đổi điều kiện tính tổng.
- Với bút toán kết chuyển cuối cùng là kết chuyển lỗ, lãi:
+ Nếu lãi: Tại hàng nhập bút toán kết chuyển lãi ta nhập vào cột
Số tiền phát sinh (ví dụ là ô G32) công thức:
=SUMIF($F$7:$F31;911;$G$7:$G31)-
SUMIF($E$7:$E31;911;$G$7:$G31)
+ Nếu lỗ: Tại hàng nhập bút toán kết chuyển lãi ta nhập vào cột
Số tiền phát sinh (ví dụ là ô G32) công thức:
=SUMIF($E$7:$E31;911;$G$7:$G31)
SUMIF($F$7:$F31;911;$G$7:$G31)
Lưu ý: Tùy theo số lượng chứng từ phát sinh mà vùng tham
chiếu và vùng tính tổng trong các hàm trên sẽ có sự thay đổi.
Hàm trên chỉ có tính chất tham khảo.
2.2. Bảng cân đối số phát sinh
2.2.1. Thiết kế Danh mục tài khoản
Trước khi thiết kế Bảng cân đối số phát sinh, ta sẽ tạo một bảng
danh mục tài khoản để tiện cho các công việc về sau.
Bảng danh mục tài khoản được thiết kế như sau:
- Thêm một sheet vào trước sheet SoNKC, đổi tên sheet đó
thành DMTK. Thiết kế bảng danh mục tài khoản như sau:
Tiếp đó bạn nhập các tài khoản và tên tài khoản tương ứng vào
cột TK và Tên TK.
Ở cột Tên sổ cái bạn nhập bằng chữ in và thêm vào trước tên tài
khoản chữ “SỔ CÁI”. Ví dụ: Tương ứng với tài khoản 111 thì
Tên sổ cái là “SỔ CÁI TK 111 - TIỀN MẶT”. Mục đích của
việc này là khi ta thiết kế sổ cái cho các tài khoản thì ứng với
mỗi tài khoản thì tên của sổ cái sẽ được lấy ra từ đây để ta đỡ
mất công nhập đi nhập lại.
2.2.2. Thiết kế Bảng cân đối số phát sinh
Chuyển sang sheet bên cạnh sheet SoNKC sau đó đổi tên sheet
này thành BCDSPS.
Thiết kế Bảng cân đối số phát sinh trên sheet BCDSPS như sau:
2.2.3. Xử lý dữ liệu trong Bảng cân đối số phát sinh
Tại cột Mã TK ta lần lượt nhập theo thứ tự trong bảng danh
mục tài khoản các tài khoản phát sinh trong kỳ kế toán.
Tại cột Tên Tài khoản ta nhập vào công thức sau:
=IF(TYPE(VLOOKUP($C9;TAIKHOAN;2;0))=16;0;
VLOOKUP($C9;TAIKHOAN;2;0))
Hàm này sẽ trả về tên của các tài khoản tương ứng với mã tài
khoản được nhập trong cột Mã TK.
Di chuột về góc dưới bên phải của ô vừa nhập công thức đến
lúc chữ thập màu trắng chuyển thành màu đen (Fill handle), giữ
và kéo chuột đến hết hàng có số hiệu tài khoản tương ứng ở bên
cột Mã TK.
Trong hàm nhập tại cột Tên Tài khoản ở phần trên TAIKHOAN
là một bảng tham chiếu thuộc bảng Danh mục tài khoản, sử
dụng kiểu bảng tham chiếu này rất tiện lợi vì bất cứ khi nào
muốn dùng bảng tham chiếu này bạn chỉ việc gõ đúng tên bảng
tham chiếu này trong các hàm cần sử dụng đến bảng tham
chiếu. Bảng tham chiếu này được tạo như sau:
Vào menu Insert/Name/Define…
Hộp thoại Define Name xuất hiện
Trong hộp Names in workbook nhập tên bảng tham chiếu.
Trong hộp Refers to nhập vào dấu bằng (=)
Nhấp chuột vào nút ở cuối hộp Refers to, sau đó bạn hãy chọn
vùng tham chiếu trên bất cứ sheet nào có dữ liệu mà bạn muốn
tạo vùng tham chiếu. Ở đây ta tạo vùng tham chiếu ở bảng
Danh mục tài khoản thuộc sheet DMTK.
Bảng tham chiếu TAIKHOAN ở đây sẽ là A4:C83 trong bảng
Danh mục tài khoản trong sheet DMTK.
Nhấp chuột vào OK.
Bảng tham chiếu này sẽ còn được sử dụng nhiều trong các hàm
ở các sổ kế toán sau.
- Cột Số dư đầu kỳ:
+ Bên Nợ: Nhập số dư Nợ đầu kỳ của các tài khoản có số dư
nợ. Số dư Nợ đầu kỳ này chính là số dư nợ cuối kỳ trước.
+ Bên Có: Nhập số dư Có đầu kỳ của các tài khoản có số dư có.
Số dư Có đầu kỳ này chính là số dư Có cuối kỳ trước.
Lưu ý: Nếu đây là Bảng cân đối phát sinh đầu tiên bạn lập thì
bạn phải nhập số dư nợ đầu kỳ của các tài khoản phát sinh vào.
Nhưng nếu bạn đã có Bảng cân đối phát sinh của kỳ trước thì
bạn chỉ việc lấy dữ liệu từ bảng cân đối phát sinh của kỳ kế toán
trước bằng cách nhập vào cột Số dư đầu kỳ:
Bên Nợ:
=VLOOKUP(C9;[KtExcel1.xls]BCDSPS!C$9:I$39;6;0)
Bên Có:
=VLOOKUP(C9;[KtExcel1.xls]BCDSPS!C$9:I$39;7;0)
Trong đó KtExcel1.xls là file sổ kế toán của kỳ kế toán trước.
Sau đó dung Fill handle kéo xuống hết bảng cân đối tài khoản.
- Cột Số phát sinh trong kỳ:
+ Bên Nợ: Nhập vào ô F9 hàm sau:
=SUMIF(SoNKC!$E$7:$E$32;C9;SoNKC!$G$7:$G$32)
Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản.
+ Bên Có: Nhập vào ô G9 hàm sau:
=SUMIF(SoNKC!$F$7:$F$32;C9;SoNKC!$G$7:$G$32)
Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản.
- Cột Số dư cuối kỳ:
+ Bên Nợ: Nhập vào ô H9 hàm sau:
=IF((D9+F9)-(E9+G9)<0;0;(D9+F9)-(E9+G9))
Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản.
+ Bên Có: Nhập vào ô I9 hàm sau:
=IF((E9+G9)-(D9+F9)<0;0;(E9+G9)-(D9+F9))
Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản.
Tại hàng cuối của Bảng cân đối số phát sinh ở cột Số dư đầu kỳ
bên Nợ nhập hàm:
=SUM(D9:D39)
Sau đó dùng Fill handle kéo sang ngang cho hết Bảng cân đối
số phát sinh.
Bạn phải lưu ý rằng một điều rất cơ bản của Bảng cân đối số
phát sinh là:
Số dư đầu kỳ bên Nợ = Số dư đầu kỳ bên Có
Số phát sinh trong kỳ bên Nợ = Số phát sinh trong kỳ bên Có
Số dư cuối kỳ bên Nợ = Số dư cuối kỳ bên Có
Vì vậy khi bạn thấy tổng bên Nợ và bên Có của cột Số dư đầu
kỳ, Số phát sinh trong kỳ hoặc Số dư cuối kỳ thì phải kiểm tra
lại ngay.
2.3. Sổ cái tài khoản
2.3.1. Thiết kế sổ cái tài khoản
Ta sẽ có 3 mẫu sổ cái:
- Sổ cái tài khoản có số dư bên Nợ
- Sổ cái tài khoản có số dư bên Có
- Sổ cái tài khoản không có số dư
Sổ cái có số dư bên nợ
Đổi tên sheet 3 thành SoCai1.
Sổ cái tài khoản có số dư bên Nợ sẽ có mẫu như sau:
Lưu ý: Bạn phải dùng AutoFilter cho vùng dữ liệu trong Sổ cái
như sau:
Chọn cả vùng sẽ chứa dữ liệu trong sổ cái sau đó vào menu
Data/Filter/AutoFilter.
Trong đó:
- Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh
mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành.
- Chứng từ:
+ SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản
ánh có tài khoản trên sổ cái hiện hành.
+ Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản
ánh có tài khoản trên sổ cái hiện hành.
- Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ
phản ánh có tài khoản trên sổ cái hiện hành.
- Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản trên sổ cái
hiện hành.
- Ghi Có: Tài khoản đối ứng bên Có của tài khoản trên sổ cái
hiện hành.
- Số dư Nợ: Số dư nợ của tài khoản trên sổ cái hiện hành
- SDĐK: Số dư đầu kỳ
- SPSTK: Số phát sinh trong kỳ
- Cộng SPS: Cộng số phát sinh trong kỳ
- SDCK: Số dư cuối kỳ
Sổ cái có số dư bên Có
Thêm một sheet bên sheet SoCai1, đặt tên là SoCai2.
Sổ cái tài khoản có số dư bên Có sẽ có mẫu như sau:
Trong đó:
- Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh
mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành.
- Chứng từ:
+ SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản
ánh có tài khoản trên sổ cái hiện hành.
+ Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản
ánh có tài khoản trên sổ cái hiện hành.
- Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ
phản ánh có tài khoản trên sổ cái hiện hành.
- Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản trên sổ cái
hiện hành.
- Ghi Có: Tài khoản đối ứng bên Có của tài khoản trên sổ cái
hiện hành.
- Số dư Có: Số dư Có của tài khoản trên sổ cái hiện hành
- SDĐK: Số dư đầu kỳ
- SPSTK: Số phát sinh trong kỳ
- Cộng SPS: Cộng số phát sinh trong kỳ
- SDCK: Số dư cuối kỳ
Sổ cái không có số dư
Thêm một sheet bên sheet SoCai2, đặt tên là SoCai3.
Sổ cái tài khoản không có số dư có mẫu như sau:
Trong đó:
- Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh
mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành.
- Chứng từ:
+ SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản
ánh có tài khoản trên sổ cái hiện hành.
+ Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản
ánh có tài khoản trên sổ cái hiện hành.
- Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ
phản ánh có tài khoản trên sổ cái hiện hành.
- Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản trên sổ cái
hiện hành.
- Ghi Có: Tài khoản đối ứng bên Có của tài khoản trên sổ cái
hiện hành.
- SPSTK: Số phát sinh trong kỳ
- Cộng SPS: Cộng số phát sinh trong kỳ
Lưu ý: Bạn phải dùng AutoFilter cho vùng dữ liệu trong các Sổ
cái như sau:
Chọn cả vùng sẽ chứa dữ liệu trong sổ cái sau đó vào menu
Data/Filter/AutoFilter.
2.3.2. Xử lý dữ liệu trên sổ cái tài khoản
Ta thấy 3 mẫu sổ cái trên khá giống nhau và cách xử lý dữ liệu
trên cả 3 sổ này cũng vậy, chúng chỉ hơi khác nhau một chút về
cách lấy dữ liệu về số dư đầu và cuối từ một cơ sở dữ liệu khác
(cụ thể là từ Bảng cân đối số phát sinh).
Sổ cái có số dư bên Nợ
Nhập vào ô A7 tài khoản trên sổ cái mà bạn muốn xem.
Ô A7 được định dạng như sau:
Nhấp chuột vào ô A7 sau đó vào menu Format/Cells. Hộp thoại
Format Cells xuất hiện ta chọn trong hộp Category mục Custom
sau đó trong hộp danh sách định dạng bên cạnh ta chọn định
dạng là ba dấu chấm phẩy (;;;).
Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện
trên sổ nhưng vẫn tồn tại.
Trên dòng tiêu đề sổ ta nhập vào hàm sau:
=VLOOKUP($A$7,TAIKHOAN,3,0)
Hàm này sẽ trả về tên của sổ cái tương ứng với tài khoản mà
bạn đã nhập vào ô A7.
- Tại ô I8 (Số dư nợ đầu kỳ) ta nhập vào hàm sau:
=VLOOKUP($A$7,BCDSPS!$C$9:$I$39,2,0)
Hàm này sẽ trả về số dư nợ đầu kỳ của tài khoản mà bạn đã
nhập vào ô A7 được lấy từ Bảng cân đối tài khoản trong sheet
BCDSPS.
- Tại ô A10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!A7,"
")
Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà
nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô B10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7,"
")
Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ
phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô C10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7,"
")
Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp
vụ phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô D10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7,"
")
Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp
vụ phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô E10 nhập vào hàm sau:
=IF(SoNKC!$F7=$A$7,SoNKC!E7,"")
Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
- Tại ô F10 nhập vào hàm sau:
=IF(SoNKC!$E7=$A$7,SoNKC!F7,"")
Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
- Tại ô G10 nhập vào hàm sau:
=IF(SoNKC!$E7=$A$7,SoNKC!$G7,"")
Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
- Tại ô H10 nhập vào hàm sau:
=IF(SoNKC!$F7=$A$7,SoNKC!$H7,"")
Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
Đặt chuột vào ô A10, kéo và giữ chuột đến ô H10, dùng Fill
handle kéo chuột xuống hết Sổ cái tài khoản.
- Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ
phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm
sau:
=SUM(G9:G39)
Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào
ô A7.
- Tại ô H40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ
phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm
sau:
=SUM(H9:H39)
Hàm này sẽ trả về tổng phát sinh có của tài khoản đã nhập vào
ô A7.
- Tại ô I41 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ
phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm
sau:
=I8+G40-H40
Hàm này sẽ trả về số dư Nợ cuối kỳ của tài khoản đã nhập vào
ô A7.
Lưu ý: Mẫu sổ cái thiết kế phải có số dòng nhiều hơn hoặc tốt
nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều
này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu.
Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ
liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ
liệu là do không thỏa mãn điều kiện của hàng.
Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp
chuột vào đó sẽ có một danh sách những nội dung diễn giải các
nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ
(Nonblanks), nhấp chuột vào đó.
Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và
chỉ cho hiển thị những dòng nào có dữ liệu (không trống).
Sổ cái có số dư bên Có
Nhập vào ô A7 tài khoản trên sổ cái mà bạn muốn xem.
Ô A7 được định dạng như ô A7 của Sổ cái có số dư bên Nợ.
Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện
trên sổ nhưng vẫn tồn tại.
Trên dòng tiêu đề sổ ta nhập vào hàm sau:
=VLOOKUP($A$7,TAIKHOAN,3,0)
Hàm này sẽ trả về tên của sổ cái tương ứng với tài khoản mà
bạn đã nhập vào ô A7.
- Tại ô I8 (Số dư Có đầu kỳ) ta nhập vào hàm sau:
=VLOOKUP($A$7,BCDSPS!$C$9:$I$39,3,0)
Hàm này sẽ trả về số dư Có đầu kỳ của tài khoản mà bạn đã
nhập vào ô A7 được lấy từ Bảng cân đối tài khoản trong sheet
BCDSPS.
- Tại ô A10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!A7,"
")
Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà
nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô B10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7,"
")
Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ
phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô C10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7,"
")
Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp
vụ phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô D10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7,"
")
Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp
vụ phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô E10 nhập vào hàm sau:
=IF(SoNKC!$F7=$A$7,SoNKC!E7,"")
Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
- Tại ô F10 nhập vào hàm sau:
=IF(SoNKC!$E7=$A$7,SoNKC!F7,"")
Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
- Tại ô G10 nhập vào hàm sau:
=IF(SoNKC!$E7=$A$7,SoNKC!G7,"")
Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
- Tại ô H10 nhập vào hàm sau:
=IF(SoNKC!$F7=$A$7,SoNKC!H7,"")
Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
Đặt chuột vào ô A10, kéo và giữ chuột đến ô H10, dùng Fill
handle kéo chuột xuống hết Sổ cái tài khoản.
- Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ
phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm
sau:
=SUM(G9:G39)
Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào
ô A7.
- Tại ô H40
(tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh
mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau:
=SUM(H9:H39)
Hàm này sẽ trả về tổng phát sinh có của tài khoản đã nhập vào
ô A7.
- Tại ô I41 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ
phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm
sau:
=I8+G40-H40
Hàm này sẽ trả về số dư Có cuối kỳ của tài khoản đã nhập vào ô
A7.
Lưu ý: Mẫu sổ cái thiết kế phải có số dòng nhiều hơn hoặc tốt
nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều
này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu.
Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ
liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ
liệu là do không thỏa mãn điều kiện của hàng.
Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp
chuột vào đó sẽ có một danh sách những nội dung diễn giải các
nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ
(Nonblanks), nhấp chuột vào đó.
Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và
chỉ cho hiển thị những dòng nào có dữ liệu (không trống).
Sổ cái không có số dư
Nhập vào ô A7 tài khoản trên sổ cái mà bạn muốn xem.
Ô A7 được định dạng như ô A7 của Sổ cái có số dư bên Nợ.
Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện
trên sổ nhưng vẫn tồn tại.
Trên dòng tiêu đề sổ ta nhập vào hàm sau:
=VLOOKUP($A$7,TAIKHOAN,3,0)
Hàm này sẽ trả về tên của sổ cái tương ứng với tài khoản mà
bạn đã nhập vào ô A7.
- Tại ô A10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!A7,"
")
Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà
nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô B10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7,"
")
Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ
phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô C10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7,"
")
Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp
vụ phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô D10 nhập vào hàm sau:
=IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7,"
")
Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp
vụ phản ánh có chứa tài khoản đã nhập vào ô A7.
- Tại ô E10 nhập vào hàm sau:
=IF(SoNKC!$F7=$A$7,SoNKC!E7,"")
Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
- Tại ô F10 nhập vào hàm sau:
=IF(SoNKC!$E7=$A$7,SoNKC!F7,"")
Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
- Tại ô G10 nhập vào hàm sau:
=IF(SoNKC!$E7=$A$7,SoNKC!G7,"")
Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
- Tại ô H10 nhập vào hàm sau:
=IF(SoNKC!$F7=$A$7,SoNKC!H7,"")
Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản đã
nhập vào ô A7, được lấy từ sổ Nhật ký chung.
Đặt chuột vào ô A10, kéo và giữ chuột đến ô H10, dùng Fill
handle kéo chuột xuống hết Sổ cái tài khoản.
- Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ
phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm
sau:
=SUM(G9:G39)
Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào
ô A7.
- Tại ô H40
(tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh
mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau:
=SUM(H9:H39)
Hàm này sẽ trả về tổng phát sinh có của tài khoản đã nhập vào
ô A7.
Lưu ý: Mẫu sổ cái thiết kế phải có số dòng nhiều hơn hoặc tốt
nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều
này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu.
Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ
liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ
liệu là do không thỏa mãn điều kiện của hàm trong hàng đó.
Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp
chuột vào đó sẽ có một danh sách những nội dung diễn giải các
nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ
(Nonblanks), nhấp chuột vào đó.
Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và
chỉ cho hiển thị những dòng nào có dữ liệu (không trống).
2.4. Sổ chi tiết tài khoản
Sổ chi tiết tài khoản là sổ chi tiết của một tài khoản, nhìn vào sổ
chi tiết tài khoản ta có thể biết số phát sinh của các tài khoản
con của tài khoản đó. Tổng phát sinh của các tài khoản con của
một tài khoản bằng số phát sinh của tài khoản đó.
2.4.1. Thiết kế sổ chi tiết tài khoản
Thêm vào một sheet bên cạnh sheet SoCai3 đặt tên là SCT_Tài
khoản tương ứng, ví dụ SCT_642.
Sổ chi tiết tài khoản bao gồm các cột sau:
- Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh
mà nghiệp vụ phản ánh có tài khoản trên sổ chi tiết hiện hành.
- Chứng từ:
+ SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản
ánh có tài khoản trên sổ chi tiết hiện hành.
+ Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản
ánh có tài khoản trên sổ chi tiết hiện hành.
- Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ
phản ánh có tài khoản trên sổ chi tiết hiện hành.
- TK Đối ứng: Tài khoản đối ứng với tài khoản trên sổ chi tiết
hiện hành trong nghiệp vụ kế toán phát sinh.
- Mã CP: Mã tài khoản con của tài khoản trên sổ chi tiết hiện
hành.
- Ghi nợ TK: Giá trị phát sinh bên Nợ của tài khoản trên sổ chi
tiết hiện hành. Tùy thuộc là sổ chi tiết của tài khoản nào ta sẽ
ghi số hiệu tài khoản đó. Ví dụ: Ghi nợ TK642.
- Các cột Mã của tài khoản con: Tùy thuộc vào tài khoản trên sổ
chi tiết có những tài khoản con nào mà sẽ có bấy nhiêu cột. Ví
dụ như tài khoản 642 có các cột như:
+ CPPB: Chi phí trả trước phân bổ vào chi phí quản lý trong kỳ.
+ CPKH: Chi phí khấu hao tài sản cố định trích vào chi phí
quản lý trong kỳ.
+ CPMN: Chi phí dịch vụ mua ngoài phục vụ quản lý trong kỳ.
+ CPDT: Chi phí điện thoại phục vụ nhu cầu quản lý trong kỳ.
+ CPK: Chi phí bằng tiền khác phục vụ quản lý trong kỳ.
+ CPTL: Chi phí tiền lương nhân viên quản lý.
…
Lưu ý: Đặt tên của các cột này phải giống hệt mã tài khoản con
của tài khoản trên sổ chi tiết hiện hành. Những mã này được
nhập trên cột Mã KH trong Sổ nhật ký chung tương ứng với
các nghiệp vụ phát sinh có tài khoản phản ánh là tài khoản trên
Các file đính kèm theo tài liệu này:
- pages_from_excel_ke_toan.pdf