Xem mẫu
- 6/25/2013
TRUỜNG ĐẠI HỌC MỞ TPHCM
KHOA KẾ TOÁN - KIỂM TOÁN NỘI DUNG
• Lập bảng tổng hợp chi tiết công nợ
• Lập bảng tổng hợp hàng tồn kho
• Lập bảng cân đối tài khoản cấp 1, cấp 3
CHƯƠNG 4
LẬP BẢNG CÂN ĐỐI TÀI KHOẢN
VÀ BẢNG TỔNG HỢP CHI TIẾT
LẬP BẢNG CÂN ĐỐI SỐ PHÁT SINH
MỤC TIÊU
Sau khi nghiên cứu nội dung này, người học có thể:
• Tạo cấu trúc bảng cân đối tài khoản và bảng tổng hợp • Tạo một cấu trúc sổ
chi tiết. • Truy xuất dữ liệu
• Ứng dụng các hàm trong excel để truy xuất dữ liệu.
1
- 6/25/2013
Tạo cấu trúc sổ Truy xuất dữ liệu
[1]? Số dư nợ đầu kỳ
[1]=IF($B7="N",MAX(SUMIF(SHTK,BCDPS!$A7&"*",SOD
DK),0),ABS(MIN(SUMIF(SHTK,BCDPS!$A7&"*",SODDK),
0)))
[2]? Số dư có đầu kỳ
[2]=IF($B7="C",MAX(SUMIF(SHTK,BCDPS!$A7&"*",SOD
DK),0),ABS(MIN(SUMIF(SHTK,BCDPS!$A7&"*",SODDK),
0)))
Truy xuất dữ liệu Truy xuất dữ liệu
[3]? Phát sinh nợ trong kỳ
[3]=SUMIF(TKGHINO,73&”*”,SOTIENPS)
SỔ KẾ TOÁN [4]? Số phát sinh có trong kỳ
BẢNG CDSPS [4]=SUMIF(TKGHICO,A7&”*”,SOTIENPS)
MÁY
[5]? Số dư nợ cuối kỳ
[5]=MAX(0,D7+F7-G7-E7)
[6]? Số dư có cuối kỳ
[6]=MAX(0,G7+E7-D7-F7)
2
- 6/25/2013
Truy xuất dữ liệu LẬP BẢNG TH CHI TIẾT TK 131
[7]? Phát sinh nợ trong kỳ
[7]=SUM(D7:D65536) • Tạo một cấu trúc sổ
[8]? Số phát sinh có trong kỳ • Truy xuất dữ liệu
[8]=SUM(E7:E65536)
[9]? Số dư nợ cuối kỳ
[9]=SUM(F7:F65536)
[10]? Số dư có cuối kỳ
[10]=SUM(G7:G65536)
Truy xuất dữ liệu Tạo cấu trúc sổ
[11]? Phát sinh nợ trong kỳ
[11]=SUM(H7:H65536)
[12]? Số phát sinh có trong kỳ
[12]=SUM(I7:I65536)
3
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
[4]? Số dư có đầu kỳ
SỔ KẾ TOÁN
MÁY [4]=IF(ISNA(VLOOKUP(A7,BDMTK!$B$4:$F$195,5,0)),0,
ABS(MIN(0,VLOOKUP(A7,BDMTK!$B$4:$F$195,5,0))))
BẢNG THCT
TK 131 [5]? Tổng hợp số phát sinh nợ
[5]=SUMIF(SOKTMAY!$J$3:$J$274,THCT131!A7,SOKT
SỔ KẾ TOÁN MAY!$M$3:$M$274)
MÁY [6]? Tổng số phát sinh có:
[6]=SUMIF(SOKTMAY!$K$3:$K$274,THCT131!A7,SOKT
MAY!$M$2:$M$274)
Truy xuất dữ liệu Truy xuất dữ liệu
[1]? Mã khách hàng
[1] =IF(LEFT(BDMTK!B4,3)="131",BDMTK!B4,"") [7]? Số dư nợ cuối kỳ
[2]? Tên khách hàng [7]=MAX(0,C6+E6-D6-F6)
=IF(ISNA(VLOOKUP(A7,BDMTK!$B$4:$D$195,3,0))," [8]? Số dư có cuối kỳ
",VLOOKUP(A7,BDMTK!$B$4:$D$195,3,0))
[8]=MAX(0,D6+F6-C6-E6)
[3]? Số dư nợ đầu kỳ
[3]==IF(ISNA(VLOOKUP(A8,BDMTK!$B$4:$F$195,5,0
)),0,MAX(0,VLOOKUP(A8,BDMTK!$B$4:$F$195,5,0)))
4
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
[9]? Tổng số dư nợ đầu kỳ [13]? Tổng số dư nợ cuối kỳ
[9] =SUM(C7:C300) [13] =SUM(G7:G300)
[10]? Tổng số dư có đầu kỳ [14]? Tổng số dư có cuối kỳ
[10] =SUM(D7:D300) [14] =SUM(H7:H300)
Truy xuất dữ liệu LẬP BẢNG TH CHI TIẾT TK 331
[11]? Tổng số phát sinh nợ
• Tạo một cấu trúc sổ
[11] =SUM(E7:E300)
• Truy xuất dữ liệu
[12]? Tổng số phát sinh có
[10] =SUM(F7:F300)
5
- 6/25/2013
Tạo cấu trúc sổ Truy xuất dữ liệu
[1]? Mã nhà cung cấp
[1] =IF(LEFT(BDMTK!B4,3)=“331",BDMTK!B4,"")
[2]? Tên nhà cung cấp
=IF(ISNA(VLOOKUP(A7,BDMTK!$B$4:$D$195,3,0)),"
",VLOOKUP(A7,BDMTK!$B$4:$D$195,3,0))
[3]? Số dư nợ đầu kỳ
[3]==IF(ISNA(VLOOKUP(A8,BDMTK!$B$4:$F$195,5,0
)),0,ABS(MIN(0,VLOOKUP(A8,BDMTK!$B$4:$F$195,
5,0))))
Truy xuất dữ liệu Truy xuất dữ liệu
[4]? Số dư có đầu kỳ
SỔ KẾ TOÁN [4]=IF(ISNA(VLOOKUP(A7,BDMTK!$B$4:$F$195,5,0)),0,
MÁY MAX(0,VLOOKUP(A7,BDMTK!$B$4:$F$195,5,0)))
BẢNG THCT
[5]? Tổng hợp số phát sinh nợ
TK 331
[5]=SUMIF(SOKTMAY!$J$3:$J$274,THCT131!A7,SOKT
SỔ KẾ TOÁN MAY!$M$3:$M$274)
MÁY [6]? Tổng số phát sinh có:
[6]=SUMIF(SOKTMAY!$K$3:$K$274,THCT131!A7,SOKT
MAY!$M$2:$M$274)
6
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
[7]? Số dư nợ cuối kỳ [11]? Tổng số phát sinh nợ
[7]=MAX(0,C6+E6-D6-F6) [11] =SUM(E7:E300)
[8]? Số dư có cuối kỳ [12]? Tổng số phát sinh có
[8]=MAX(0,D6+F6-C6-E6) [10] =SUM(F7:F300)
Truy xuất dữ liệu Truy xuất dữ liệu
[9]? Tổng số dư nợ đầu kỳ [13]? Tổng số dư nợ cuối kỳ
[9] =SUM(C7:C300) [13] =SUM(G7:G300)
[10]? Tổng số dư có đầu kỳ [14]? Tổng số dư có cuối kỳ
[10] =SUM(D7:D300) [14] =SUM(H7:H300)
7
- 6/25/2013
LẬP BẢNG TH CHI TIẾT TK 152 Truy xuất dữ liệu
SỔ KẾ TOÁN
• Tạo một cấu trúc sổ MÁY
• Truy xuất dữ liệu
BẢNG THCT
TK 152
BẢNG DMTK
Tạo cấu trúc sổ Truy xuất dữ liệu
[1]? Mã vật tư
[1]=IF(LEFT(BDMTK!$B4,3)="152",BDMTK!B4,"")
[2]? Tên vật tư
[2]=IF(LEFT(BDMTK!$B4,3)="152",BDMTK!D4,"")
8
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
[3]? Số lượng tồn đầu kỳ
[9]? Số lượng tồn cuối kỳ:
[3]=IF(ISNA(VLOOKUP($A7,BDMTK!$B$4:$E$195,4,
0)),0,VLOOKUP($A7,BDMTK!$B$4:$E$195,4,0))
[9]=SLTĐ + SL NHAP – SL XUAT
[4]? Trị giá tồn đầu kỳ
[10]? Trị giá tồn cuối kỳ
[4]=IF(ISNA(VLOOKUP($A7,BDMTK!$B$4:$F$195,5,
0)),0,VLOOKUP($A7,BDMTK!$B$4:$F$195,5,0)) [10]=TRỊ GIÁ TĐ + TRỊ GIÁ NHẬP –TRỊ GIÁ XUẤT
[5]? Tổng số lượng nhập [11]? Đơn giá tồn cuối kỳ:
[5]=SUMIF(TKGHINO,A7,SOLUONGPS) [11]=IF(SL>0, TRỊ GIÁ/SL,0)
Truy xuất dữ liệu Truy xuất dữ liệu
[6]? Tổng trị giá nhập [12]? Tổng số lượng tồn đầu
[6]=SUMIF(TKGHINO,A7,SOTIENPS) [12]=SUM(C7:C65536)
[7]? Tổng số lượng xuất [13]? Tổng trị giá tồn đầu
[7]=SUMIF(TKGHICO,A7,SOLUONGPS) [13]=SUM(D7:D65536)
[8]? Tổng trị giá xuất [14]? Tổng số lượng nhập
[8]=SUMIF(TKGHICO,A7,SOTIENPS) [14]=SUM(E7:E65536)
[15]? Tổng trị giá nhập
[13]=SUM(F7:F65536)
9
- 6/25/2013
Truy xuất dữ liệu Tạo cấu trúc sổ
[16]? Tổng số lượng xuất
[16]=SUM(G7:G65536)
[17]? Tổng trị giá xuất
[17]=SUM(H7:H65536)
[18]? Tổng số lượng tồn cuối
[18]=SUM(I7:I65536)
[19]? Tổng trị giá tồn cuối
[13]=SUM(J7:J65536)
LẬP BẢNG TH CHI TIẾT TK 153 Truy xuất dữ liệu
SỔ KẾ TOÁN
• Tạo một cấu trúc sổ MÁY
• Truy xuất dữ liệu
BẢNG THCT
TK 153
BẢNG DMTK
10
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
[1]? Mã vật tư [6]? Tổng trị giá nhập
[1]=IF(LEFT(BDMTK!$B4,3)="153",BDMTK!B4,"") [6]=SUMIF(TKGHINO,A7,SOTIENPS)
[2]? Tên vật tư [7]? Tổng số lượng xuất
[2]=IF(LEFT(BDMTK!$B4,3)="153",BDMTK!D4,"") [7]=SUMIF(TKGHICO,A7,SOLUONGPS)
[8]? Tổng trị giá xuất
[8]=SUMIF(TKGHICO,A7,SOTIENPS)
Truy xuất dữ liệu Truy xuất dữ liệu
[3]? Số lượng tồn đầu kỳ
[3]=IF(ISNA(VLOOKUP($A7,BDMTK!$B$4:$E$19 [9]? Số lượng tồn cuối kỳ:
5,4,0)),0,VLOOKUP($A7,BDMTK!$B$4:$E$195,4,0)
[9]=SLTĐ + SL NHAP – SL XUAT
)
[4]? Trị giá tồn đầu kỳ
[10]? Trị giá tồn cuối kỳ
[4]=IF(ISNA(VLOOKUP($A7,BDMTK!$B$4:$F$195
,5,0)),0,VLOOKUP($A7,BDMTK!$B$4:$F$195,5,0)) [10]=TRỊ GIÁ TĐ + TRỊ GIÁ NHẬP –TRỊ GIÁ XUẤT
[5]? Tổng số lượng nhập [11]? Đơn giá tồn cuối kỳ:
[5]=SUMIF(TKGHINO,A7,SOLUONGPS) [11]=IF(SL>0, TRỊ GIÁ/SL,0)
11
- 6/25/2013
Truy xuất dữ liệu LẬP BẢNG TH CHI TIẾT TK 155
[12]? Tổng số lượng tồn đầu
• Tạo một cấu trúc sổ
[12]=SUM(C7:C65536)
• Truy xuất dữ liệu
[13]? Tổng trị giá tồn đầu
[13]=SUM(D7:D65536)
[14]? Tổng số lượng nhập
[14]=SUM(E7:E65536)
[15]? Tổng trị giá nhập
[15]=SUM(F7:F65536)
Truy xuất dữ liệu Tạo cấu trúc sổ
[16]? Tổng số lượng xuất
[16]=SUM(G7:G65536)
[17]? Tổng trị giá xuất
[17]=SUM(H7:H65536)
[18]? Tổng số lượng tồn cuối
[18]=SUM(I7:I65536)
[19]? Tổng trị giá tồn cuối
[19]=SUM(J7:J65536)
12
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
SỔ KẾ TOÁN [1]? Mã vật tư
MÁY [1]=IF(LEFT(BDMTK!$B4,3)="155",BDMTK!B4,"")
BẢNG THCT [2]? Tên vật tư
TK 155 [2]=IF(LEFT(BDMTK!$B4,3)="155",BDMTK!D4,"")
BẢNG DMTK
LẬP BẢNG TH CHI TIẾT TK 156 Truy xuất dữ liệu
[3]? Số lượng tồn đầu kỳ
[3]=IF(ISNA(VLOOKUP($A7,BDMTK!$B$4:$E$195
• Tạo một cấu trúc sổ
,4,0)),0,VLOOKUP($A7,BDMTK!$B$4:$E$195,4,0))
• Truy xuất dữ liệu [4]? Trị giá tồn đầu kỳ
[4]=IF(ISNA(VLOOKUP($A7,BDMTK!$B$4:$F$195
,5,0)),0,VLOOKUP($A7,BDMTK!$B$4:$F$195,5,0))
[5]? Tổng số lượng nhập
[5]=SUMIF(TKGHINO,A7,SOLUONGPS)
13
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
[6]? Tổng trị giá nhập [12]? Tổng số lượng tồn đầu
[6]=SUMIF(TKGHINO,A7,SOTIENPS) [12]=SUM(C7:C65536)
[7]? Tổng số lượng xuất [13]? Tổng trị giá tồn đầu
[7]=SUMIF(TKGHICO,A7,SOLUONGPS) [13]=SUM(D7:D65536)
[8]? Tổng trị giá xuất [14]? Tổng số lượng nhập
[8]=SUMIF(TKGHICO,A7,SOTIENPS) [14]=SUM(E7:E65536)
[15]? Tổng trị giá nhập
[15]=SUM(F7:F65536)
Truy xuất dữ liệu Truy xuất dữ liệu
[16]? Tổng số lượng xuất
[9]? Số lượng tồn cuối kỳ:
[16]=SUM(G7:G65536)
[9]=SLTĐ + SL NHAP – SL XUAT
[17]? Tổng trị giá xuất
[10]? Trị giá tồn cuối kỳ
[17]=SUM(H7:H65536)
[10]=TRỊ GIÁ TĐ + TRỊ GIÁ NHẬP –TRỊ GIÁ XUẤT
[18]? Tổng số lượng tồn cuối
[11]? Đơn giá tồn cuối kỳ:
[18]=SUM(I7:I65536)
[11]=IF(SL>0, TRỊ GIÁ/SL,0)
[19]? Tổng trị giá tồn cuối
[19]=SUM(J7:J65536)
14
- 6/25/2013
Tạo cấu trúc sổ LẬP BẢNG TH SỐ DƯ VÀ SỐ PHÁT SINH
CÁC TÀI KHOẢN KHÁC
• Tạo một cấu trúc sổ
• Truy xuất dữ liệu
Truy xuất dữ liệu Truy xuất dữ liệu
SỔ KẾ TOÁN [1]? Mã vật tư
MÁY [1]=IF(LEFT(BDMTK!$B4,3)="156",BDMTK!B4,"")
BẢNG THCT [2]? Tên vật tư
TK 156 [2]=IF(LEFT(BDMTK!$B4,3)="156",BDMTK!D4,"")
BẢNG DMTK
15
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
[3]? Số lượng tồn đầu kỳ
[9]? Số lượng tồn cuối kỳ:
[3]=IF(ISNA(VLOOKUP($A7,BDMTK!$B$4:$E$19
5,4,0)),0,VLOOKUP($A7,BDMTK!$B$4:$E$195,4,0)
[9]=SLTĐ + SL NHAP – SL XUAT
)
[4]? Trị giá tồn đầu kỳ
[10]? Trị giá tồn cuối kỳ
[4]=IF(ISNA(VLOOKUP($A7,BDMTK!$B$4:$F$195
,5,0)),0,VLOOKUP($A7,BDMTK!$B$4:$F$195,5,0)) [10]=TRỊ GIÁ TĐ + TRỊ GIÁ NHẬP –TRỊ GIÁ XUẤT
[5]? Tổng số lượng nhập [11]? Đơn giá tồn cuối kỳ:
[5]=SUMIF(TKGHINO,A7,SOLUONGPS) [11]=IF(SL>0, TRỊ GIÁ/SL,0)
Truy xuất dữ liệu Truy xuất dữ liệu
[6]? Tổng trị giá nhập [12]? Tổng số lượng tồn đầu
[6]=SUMIF(TKGHINO,A7,SOTIENPS) [12]=SUM(C7:C65536)
[7]? Tổng số lượng xuất [13]? Tổng trị giá tồn đầu
[7]=SUMIF(TKGHICO,A7,SOLUONGPS) [13]=SUM(D7:D65536)
[8]? Tổng trị giá xuất [14]? Tổng số lượng nhập
[8]=SUMIF(TKGHICO,A7,SOTIENPS) [14]=SUM(E7:E65536)
[15]? Tổng trị giá nhập
[15]=SUM(F7:F65536)
16
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
[16]? Tổng số lượng xuất SỔ KẾ TOÁN
[16]=SUM(G7:G65536) MÁY
BẢNG TH SỐ
[17]? Tổng trị giá xuất DƯ VÀ SỐ PS
[17]=SUM(H7:H65536) CÁC TK
[18]? Tổng số lượng tồn cuối BẢNG DMTK KHÁC
[18]=SUM(I7:I65536)
[19]? Tổng trị giá tồn cuối
[19]=SUM(J7:J65536)
Tạo cấu trúc sổ Truy xuất dữ liệu
[1]? Số hiệu tài khoản
[1]=IF(OR(LEFT(BDMTK!B4,3)="131",LEFT(BDMTK!B4,3)="
331",LEFT(BDMTK!B4,3)="152",LEFT(BDMTK!B4,3)="153",L
EFT(BDMTK!B4,3)="155",LEFT(BDMTK!B4,3)="156",LEFT(B
DMTK!B4,3)="157"),"",BDMTK!B4)
[2]? Loại tài khoản
=IF(ISNA(VLOOKUP(A7,BDMTK!$B$4:$C$195,2,0)),"",VL
OOKUP(A7,BDMTK!$B$4:$C$195,2,0))
17
- 6/25/2013
Truy xuất dữ liệu Truy xuất dữ liệu
[3]? Tên tài khoản
[7]? Tổng số phát sinh có:
[3]=IF(ISNA(VLOOKUP(A7,BDMTK!$B$4:$D$195,3,0)),"",VL [7][=SUMIF(SOKTMAY!$K$3:$K$274,'THCT_TK#'!$A7,
OOKUP(A7,BDMTK!$B$4:$D$195,3,0)) SOKTMAY!$M$3:$M$274)
[4]? Số dư đầu kỳ nợ
[8]? Số dư cuối kỳ nợ
[4]==IF($B7=“N",MAX(IF(ISNA(VLOOKUP($A7,BDMTK!$B$
4:$F$195,5,0)),0,VLOOKUP($A7,BDMTK!$B$4:$F$195,5,0)),0) [8]=IF(AND(A6””,MAX(0,C6+E6-D6-F6),0)
,ABS(MIN(0,IF(ISNA(VLOOKUP($A7,BDMTK!$B$4:$F$195,5
,0)),0,VLOOKUP($A7,BDMTK!$B$4:$F$195,5,0))))) [9]? Số dư cuối kỳ có
[9]=IF(AND(A6””,MAX(0,D6+F6-C6-E6),0)
Truy xuất dữ liệu Truy xuất dữ liệu
[10]? Tổng số phát sinh
[5]? Số dư đầu kỳ có
[5]=IF($B7="C",MAX(IF(ISNA(VLOOKUP($A7,BDMTK!$ [10]=SUM(D7:D65536)
B$4:$F$195,5,0)),0,VLOOKUP($A7,BDMTK!$B$4:$F$195,
5,0)),0),ABS(MIN(0,IF(ISNA(VLOOKUP($A7,BDMTK!$B [11]? Tổng số phát sinh
$4:$F$195,5,0)),0,VLOOKUP($A7,BDMTK!$B$4:$F$195,5
,0))))) [11]=SUM(E7:E65536)
[6]? Tổng số phát sinh nợ
[12]? Tổng số phát sinh
[5]=SUMIF(SOKTMAY!$J$3:$J$274,'THCT_TK#'!$A7,SOK [12]=SUM(F7:F65536)
TMAY!$M$3:$M$274)
18
- 6/25/2013
Truy xuất dữ liệu
[13]? Tổng số phát sinh
[13]=SUM(G7:G65536)
[14]? Tổng số phát sinh
[14]=SUM(H7:H65536)
[15]? Tổng số phát sinh
[15]=SUM(I7:I65536)
19
nguon tai.lieu . vn