BT Excel XN Da [PDF]

Zitiervorschau

Sinh viên cần điền thông tin cá nhân vào các ô màu vàng Họ tên SV:

Nguyễn Văn A

Lớp :

10

Ngày sinh

12/12/1992

Mã số SV :

12345

Số máy :

Công ty Đá Hóa An khai thác, sản xuất đá xây dựng và bán cho các khách hàng. Khách hàng dùng xe tải để chuyên chở sản phẩm đá.và công ty dùng cân điện tử để cân tải trọng xe. Trước khi vào kho để lấy đá thành phẩm, các xe này phải lên cân điện tử để cân bì xe. Sau khi lấy hàng xong, khi trở ra xe phải cân lần hai, trọng lượng xe có hàng hóa. Hệ thống cân điện tử và phần mềm máy tính sẽ ghi lại các thông tin sau : Ngày, Số phiếu. Số xe. Trọng lựong bì xe (kg), thời điểm cân bì (ngày, giờ). Trọng lựong xe có hàng hóa (kg), thời điểm cân xe và hàng (ngày, giờ). Mã KH, Mã SP, Mã Kho. (Xem bảng dữ liệu minh họa sau). Stt 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Ngày 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010

Số Phiếu

Số xe

126698 126720 126730 126741 126753 126758 126765 126778 126785 126792 126811 126821 126844 126852 126858

60M9466 61N1492 60M9122 60L9466 72M8508 60S3987 60V3097 60S6916 61L8536 60S1079 57K2032 60M9122 60L9918 57L4739 60M4267

TL xe không 13200 13400 12730 13080 12770 13580 12480 13850 12000 13530 13190 12840 12930 12180 13600

TG cân xe không 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010

TL xe có hàng 38570 31910 38020 35950 39710 34760 33290 43080 41030 42810 41120 37720 33330 33430 32980

TG cân xe có hàng 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010

Mã KH 3CTHP 2BD 3CTT1 3CTHP 3HMT 1TTT3 3NDN 3HMT 2GH 1TT6 1PT2 3CTT1 1TTT3 1TT6 3NDN

12

Tên KH Công Ty TNHH VT TK HTX Vận Tải - Xây V Công ty TNHH Tấn TV Công Ty TNHH VT TK Công Ty TNHH MTVV Công Ty TNHH ThuậV Công Ty CP Kinh DoV Công Ty TNHH MTVK DNTN Gia Hòa V Cty TNHH Trung TiếV Công Ty TNHH SX KK Công ty TNHH Tấn TK Công Ty TNHH ThuậV Cty TNHH Trung TiếV Công Ty CP Kinh DoV

Mã Kho

Tên Kho Kho Tân Bản Kho Tân Vạn Kho Tân Vạn Kho Tân Bản Kho Tân Vạn Kho Tân Vạn Kho Tân Vạn Kho Tân Bản Kho Tân Vạn Kho Tân Vạn Kho Tân Bản Kho Tân Bản Kho Tân Vạn Kho Tân Vạn Kho Tân Vạn

Mã SP 042 V12 V12 042 V57 V042 V46C D57 V042 V12 04D MS V042 V1022 V46C

Tên SP Đá 0x4 A Đá 1X2 Đá 1X2 Đá 0x4 A Đá 5X7 Đá 0X4 A Đá 4X6 CB Đá 5x7 Đá 0X4 A Đá 1X2 Đá 0x4 Đ Đá Mi Sàng Đá 0X4 A Đá 10X22 Đá 4X6 CB

Số tấn 25.37 18.51 25.29 22.87 26.94 21.18 20.81 29.23 29.03 29.28 27.93 24.88 20.4 21.25 19.38

Số khối 13.35 11.36 15.52 12.04 16.33 11.15 12.61 17.72 15.28 17.96 14.7 16.59 10.74 13.04 11.75

Đơn giá / tấn 73,800 112,900 112,900 73,800 74,600 73,800 74,600 74,600 73,800 112,900 65,300 87,800 73,800 118,500 74,600

Tỉ lệ % giảm giá 5% 10% 5% 5% 5% 0% 5% 5% 10% 0% 0% 5% 0% 0% 5%

Tiền hàng Tiền thanh toán (chưa thuế) (có thuế) 1,778,691 1,880,801 2,712,479 1,603,416 1,909,238 1,563,084 1,474,805 2,071,530 1,928,173 3,305,712 1,823,829 2,075,241 1,505,520 2,518,125 1,373,461

1,956,560 2,068,881 2,983,727 1,763,758 2,100,162 1,719,392 1,622,286 2,278,683 2,120,990 3,636,283 2,006,212 2,282,765 1,656,072 2,769,938 1,510,807

Các Bảng Danh Mục Danh Mục Kho Mã Kho K V

Tên Kho Kho Tân Bản Kho Tân Vạn

Danh Mục Sãn Phẩm Mã SP 042 04D D12 D121 V12 V46C V57 VMB VMS

Tên SP Đá 0x4 A Đá 0x4 Đ Đá 1x2 A Đá 1x2 Đá 1X2 Đá 4X6 CB Đá 5X7 Đá Mi.Bụi Đá Mi.Sàng

Đvt Tấn Tấn Tấn Tấn Tấn Tấn Tấn Tấn Tấn

Đơn giá 73,800 65,300 112,900 112,900 112,900 74,600 74,600 72,900 87,800

Danh Mục Nhóm SP

Danh Mục Nhóm KH

Mã nhóm 12 46 04 57 M VS

Mã nhóm 1 2 3 4

Tên nhóm Đá 1x2 Đá 4x6 Đá 0x4 Đá 5x7 Đá mi Đá vệ sinh

Danh Mục Khách Hàng Mã KH 23TD 3AD1 4AK 1AT1 1TTT3 2TTT7 3TVG1 4TVT1 1VBT

Tên KH Địa Chỉ Công ty TNHH Xây Dựng 3T và D 179 Phạm Ngũ Lão P4 Q Công Ty TNHH TM DV và XD Anh 14/34 đường số 4, Tổ 11, Công Ty TNHH TM DV XD VT Phú 714 Điện Biên Phủ, P.10, Cty TNHH MTV Như An Thắng 25/10 ấp Trung Thắng, x Công Ty TNHH Thuận Thành Tài K1/62A Tân Bản, Bửu Hòa Công Ty TNHH Thuận Thành Tài K1/62A, Tân Bản, Bửu Hò DNTN Thủy Vân Giang 1150/10B ấp Đông Chiêu x Công Ty TNHH XD TM Thịnh Việt D41 Lê Hồng Phong, P.Trà DNTN Võ Bình Thanh K4/178 Tân Mỹ, Bửu Hòa

Mã số thuế 0303172720 0305238085 0304113799 3701059705 3600664939 3600664939 3700976804 1800751367 3600725010

Tỉ trọng 1.90 1.90 1.63 1.63 1.63 1.65 1.65 1.75 1.50

Nhóm 04 04 12 12 12 46 57 M M

nh Mục Nhóm KH Tên nhóm Tỉ lệ % giảm giá Bán lẻ (trả tiền ngay 0% Bán nợ trả trước 10% Bán nợ trả sau 5% KH khác 0%

Phần chi tiết

Mỗi câu 1 điểm

Stt

Ngày

Số Phiếu

Số xe

1 2 3 4 5 6 7 8 9 910 911 912 913 914 915 916 917 918 919 920 921 922

11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010

126698 126720 126730 126741 126753 126758 126765 126778 126785 135504 135505 135515 135562 135566 135568 135573 135575 135577 135580 135594 135601 135606

60M9466 61N1492 60M9122 60L9466 72M8508 60S3987 60V3097 60S6916 61L8536 60S6953 60S2348 57H9293 57H9387 60S9732 60M9031 57K3178 57M0841 60V2880 57H8946 60M9031 61L9076 61N8276

TL xe không 13200 13400 12730 13080 12770 13580 12480 13850 12000 12820 13090 12410 12890 14080 13710 12640 12840 13510 13740 13650 13430 12930

TG cân xe không 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010

TL xe có hàng 38570 31910 38020 35950 39710 34760 33290 43080 41030 38500 39790 39120 34790 41860 39240 34570 34120 39960 43820 39640 47370 44810

TG cân xe có hàng

Mã KH

10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 10/31/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010 11/23/2010

3CTHP 2BD 3CTT1 3CTHP 3HMT 1TTT3 3NDN 3HMT 2GH 3CTT1 3CTT1 1PT2 3QL 3CTT1 3CTT1 4DNHT 3LTP 3CTT1 4CTND1 3CTT1 1PT2 1PT2

1/ Đặt tên cho tất cả các bảng dữ liệu trong các sheet Danh Mục và Số Liệu Hoàn Chỉnh (tự chọn tên cho tiện dụng) Định dạng (trang trí) các bảng dữ liệu trên sao cho đẹp mắt (vẽ khung, tô màu, font chữ, canh lề, …). 2/ Trong bảng dữ liệu trên, thêm các cột Tên KH sau cột Mã KH, cột Tên kho sau cột Mã kho, cột Tên SP sau cột Mã SP. Đặt công thức để thể hiện nội dung tương ứng vào các cột vừa thêm và các cột Đơn giá, Tỉ lệ % giảm giá. Lưu ý : Tỉ lệ % giảm giá tùy thuộc vào nhóm của KH (cột 3 trong bảng DMNhomKH) Ký tự đầu tiên trong mã KH là mã nhóm KH 3/ Đặt công thức tính toán vào các cột Số tấn, Số khối. Yêu cầu : Số khối = Số tấn / Tỉ trọng (tỉ trọng lấy ở cột 5 trong bảng Danh mục Sản phẩm) Số tấn, Số khối được làm tròn lấy 2 số lẻ. 4/ Đặt công thức tính toán vào các cột Đơn giá, Tiền hàng, Tiền thanh toán. Yêu cầu : Đơn Giá : lấy ở cột 4 trong bảng Danh Mục Sản Phẩm Tiền hàng = Số khối x Đơn giá x (1 - Tỉ lệ % giảm giá) (làm tròn kết quả thành số nguyên).

Tiền thanh toán = Tiền hàng + Thuế GTGT

(thuế suất là 10%)

Mã Kho K V V K V V V K V K K K K V K K K K K K K K

họn tên cho tiện dụng)

lệ % giảm giá.

Tên Kho Kho Tân Bản Kho Tân Vạn Kho Tân Vạn Kho Tân Bản Kho Tân Vạn Kho Tân Vạn Kho Tân Vạn Kho Tân Bản Kho Tân Vạn Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Vạn Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản

Mã SP 042 V12 V12 042 V57 V042 V46C D57 V042 D121 D121 D122 D46C V12 D121 DMB 042 D121 042 D121 04D 04D

Số tấn 25.37 18.51 25.29 22.87 26.94 21.18 20.81 29.23 29.03 29.3 24.08 28.71 22.1 31.52 25.11 20.32 20.43 27.76 30.08 25.11 27.88 30.38

Tỉ trọng 1.9 1.63 1.63 1.9 1.65 1.9 1.65 1.65 1.9 1.63 1.63 1.63 1.65 1.63 1.63 1.75 1.9 1.63 1.9 1.63 1.9 1.9

Số khối 13.3526316 11.3558282 15.5153374 12.0368421 16.3272727 11.1473684 12.6121212 17.7151515 15.2789474 17.9754601 14.7730061 17.6134969 13.3939394 19.3374233 15.404908 11.6114286 10.7526316 17.0306748 15.8315789 15.404908 14.6736842 15.9894737

Đơn giá / tấn 73800 112900 112900 73800 74600 73800 74600 74600 73800 112900 112900 118500 74600 112900 112900 72900 73800 112900 73800 112900 65300 65300

Tỉ lệ % giảm giá 0.05 0.1 0.05 0.05 0.05 0 0.05 0.05 0.1 0.05 0.05 0 0.05 0.05 0.05 0 0.05 0.05 0 0.05 0 0

Tiền hàng (chưa thuế) 936153 1153866 1664098 843903 1157114 822676 893821 1255473 1014828 1927958 1584479 2087199 949228 2074035 1652253 846473 753867 1826625 1168371 1652253 958192 1044113

Tiền thanh toán (có thuế) 1029768.3 1269252.276 1830507.267 928293.3 1272825.2 904943.3684 983203.1333 1381020.067 1116310.453 2120753.773 1742926.65 2295919.325 1044151.333 2281438.871 1817478.745 931120.4571 829253.7 2009287.534 1285207.579 1817478.745 1054010.737 1148523.895

Phiếu Cân tháng 11/2010 Stt

Ngày

Số Phiếu

Số xe

1 2 3 4 5 6 7 8 9 910 911 912 913 914 915 916 917 918 919 920 921 922

11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/1/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010 11/24/2010

126698 126720 126730 126741 126753 126758 126765 126778 126785 135504 135505 135515 135562 135566 135568 135573 135575 135577 135580 135594 135601 135606

60L9466 60M9122 60M9466 60S3987 60S6916 60V3097 61N1492 72M8508 61L8536 60S6953 60S2348 57H9293 57H9387 60S9732 57H8946 57K3178 57M0841 60M9031 60M9031 60V2880 61L9076 61N8276

TL xe không 13080 12730 13200 13580 13850 12480 13400 12770 12000 12820 13090 12410 12890 14080 13740 12640 12840 13710 13650 13510 13430 12930

TG cân xe TL xe có TG cân xe Mã KH không hàng có hàng 10/31/2010 35950 10/31/2010 3CTHP 10/31/2010 38020 10/31/2010 3CTT1 10/31/2010 38570 10/31/2010 3CTHP 10/31/2010 34760 10/31/2010 1TTT3 10/31/2010 43080 10/31/2010 3HMT 10/31/2010 33290 10/31/2010 3NDN 10/31/2010 31910 10/31/2010 2BD 10/31/2010 39710 10/31/2010 3HMT 10/31/2010 41030 10/31/2010 2GH 11/23/2010 38500 11/23/2010 3CTT1 11/23/2010 39790 11/23/2010 3CTT1 11/23/2010 39120 11/23/2010 1PT2 11/23/2010 34790 11/23/2010 3QL 11/23/2010 41860 11/23/2010 3CTT1 11/23/2010 43820 11/23/2010 4CTND1 11/23/2010 34570 11/23/2010 4DNHT 11/23/2010 34120 11/23/2010 3LTP 11/23/2010 39240 11/23/2010 3CTT1 11/23/2010 39640 11/23/2010 3CTT1 11/23/2010 39960 11/23/2010 3CTT1 11/23/2010 47370 11/23/2010 1PT2 11/23/2010 44810 11/23/2010 1PT2

Sinh viên không làm phần tổng hợp vào sheet này

1/2010 Tên KH

Mã Kho

Công Ty TNHHK Công ty TNHH V Công Ty TNHHK Công Ty TNHH V Công Ty TNHHK Công Ty CP Ki V HTX Vận Tải - V Công Ty TNHHV DNTN Gia Hòa V Công ty TNHH K Công ty TNHH K Công Ty TNHHK Công ty TNHH K Công ty TNHH V Công Ty TNHHK DNTN Thương K M Công ty TNHH K Công ty TNHH K Công ty TNHH K Công ty TNHH K Công Ty TNHHK Công Ty TNHHK

Tên Kho Kho Tân Bản Kho Tân Vạn Kho Tân Bản Kho Tân Vạn Kho Tân Bản Kho Tân Vạn Kho Tân Vạn Kho Tân Vạn Kho Tân Vạn Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Vạn Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản Kho Tân Bản

Mã SP 042 V12 042 V042 D57 V46C V12 V57 V042 D121 D121 D122 D46C V12 042 DMB 042 D121 D121 D121 04D 04D

Tên SP Đá 0x4 A Đá 1X2 Đá 0x4 A Đá 0X4 A Đá 5x7 Đá 4X6 CB Đá 1X2 Đá 5X7 Đá 0X4 A Đá 1x2 Đá 1x2 Đá 10x22 Đá 4x6 CB Đá 1X2 Đá 0x4 A Đá Mi Bụi Đá 0x4 A Đá 1x2 Đá 1x2 Đá 1x2 Đá 0x4 Đ Đá 0x4 Đ

Số tấn 22.87 25.29 25.37 21.18 29.23 20.81 18.51 26.94 29.03 25.68 26.7 26.71 21.9 27.78 30.08 21.93 21.28 25.53 25.99 26.45 33.94 31.88

Số khối 12.04 15.52 13.35 11.15 17.72 12.61 11.36 16.33 15.28 15.75 16.38 16.39 13.27 17.04 15.83 12.53 11.2 15.66 15.94 16.23 17.86 16.78

Đơn giá / Tỉ lệ % tấn giảm giá 73,800 5% 112,900 5% 73,800 5% 73,800 0% 74,600 5% 74,600 5% 112,900 10% 74,600 5% 73,800 10% 112,900 5% 112,900 5% 118,500 0% 74,600 5% 112,900 5% 73,800 0% 72,900 0% 73,800 5% 112,900 5% 112,900 5% 112,900 5% 65,300 0% 65,300 0%

Tiền hàng Tiền thanh (chưa thuế) toán (có thuế) 1,603,416 1,763,758 2,712,479 2,983,727 1,778,691 1,956,560 1,563,084 1,719,392 2,071,530 2,278,683 1,474,805 1,622,286 1,880,801 2,068,881 1,909,238 2,100,162 1,928,173 2,120,990 2,754,308 3,029,739 2,863,709 3,150,080 3,165,135 3,481,649 1,552,053 1,707,258 2,979,544 3,277,498 2,219,904 2,441,894 1,598,697 1,758,567 1,491,941 1,641,135 2,738,220 3,012,042 2,787,557 3,066,313 2,836,895 3,120,585 2,216,282 2,437,910 2,081,764 2,289,940

Phần tổng hợp Mỗi câu 1 điểm

SV dùng bảng dữ liệu hoàn chỉnh trong sheet SL Hoàn Chỉnh để làm các câu sau : Lưu ý :

Đặt tên cho các vùng dữ liệu, cột cần tính toán. Bảng DMKH rất nhiều dòng, cần che giấu cho gọn

5/ Lập bảng tổng hợp doanh số tháng 11/2010 phân tích theo ngày như mẫu sau Yêu cầu : Đặt bảng kết quả trong sheet PhieuCan, có thể dùng các hàm COUNTIF, SUMIF hoặc dùng lệnh CONSOLIDATE hay PIVOT TABLE Ngày

Số chuyến

11/1/2010 11/24/2010 11/30/2010

27 20 0

Số tấn 658.92 528.54 0

Số khối 383.95 303.3 0

Tiền hàng

Tiền thanh toán

51500675 44730573 0

0 0 0

6/ Lập bảng điều kiện (vùng tiêu chuẩn) và dùng lệnh Advanced Filter để trích lọc danh sách các phiếu cân thỏa đồng thời các điều kiện sau : Lấy hàng vào các ngày chủ nhật. Mỗi chuyến xe chở hơn 30 tấn sản phẩm Xe có biển số TpHCM (có số đầu là 5, thí dụ 57K1234) Dùng hàm DCOUNT với bảng điều kiện trên để kiểm chứng lại kết quả. 7/ Lập bảng tổng hợp số khối theo Kho như bảng sau : Mã kho Tên kho Số khối K ? ? V ? ?

Sum of Số khối Mã Kho K V Grand Total 8/ Vẽ biểu đồ kiểu Pie minh họa bảng tổng hợp trên (nếu đã làm xong câu 7).

Tên Kho Kho Tân Bản Kho Tân Vạn 3359.760575 9810.364197 3359.760575 9810.364197

9/ Trình bày phương pháp và thực hiện các bước để liệt kê ra được thông tin của 3 khách hàng mà có doanh số (Tiền thanh toán)Tiền cao nhất trong tháng, cụ thể là lập được bảng sau : Mã KH

Tên KH

? ? ?

? ? ?

thanh toán cao nhất ? ? ?

10/ Vẽ biểu đồ kiểu Column minh họa bảng tổng hợp trên (nếu đã làm xong câu 9).

Grand Total 3359.760575 9810.364197 13170.12477

Câu 5 Ngày 11/1/2010 11/24/2010 11/30/2010

Câu 7 Sum of Số khối Mã Kho K V Grand Total

Số chuyến 27 20 0

số tấn 658.92 528.54 0

Số khối 383.95 303.3 0

Tên Kho Kho Tân Bản Kho Tân V Grand Total 3359.76057480567 3359.76057480567 9810.364 9810.36419663214 3359.76057480567 9810.364 13170.1247714378

Tiền hàng 51500675 44730573 0

Tiền thanh toán 56650744 49203630 0