Bài tập thực hành Excel - Tin ứng dụng | Trường Đại Học Duy Tân

Bài thực hành Tin Ứng Dụng BÀI TẬP THỰC HÀNH EXCEL BÀI 1.
Mục đích: Ôn tập lại cách định dạng dữ liệu trong bảng tính. Sử dụng các hàm cơ bản và hàm
thống kê đơn giản COUNTIF, SUMIF
Tổ chức bảng dữ liệu như sau:
BẢNG CHI TIẾT HÀNG XUẤT TỪ NGÀY 01/02/03 ĐẾN 30/02/03 TÊN ĐƠN SỐ DOANH STT NGÀY MÃ TỈNH MÃ HH HÀNG HOÁ ĐVT GIÁ LƯỢNG SỐ 1 02/02/03 SG S12 425 2 02/02/03 ĐN X50 340 3 06/02/03 HN S10 440 4 07/02/03 ĐN GHN 650 5 08/02/03 HN GTQ 725 6 15/02/03 HN S12 430 7 06/02/03 SG X30 225 TỔNG CỘNG DANH MỤC HÀNG HOÁ MÃ HH TÊN HÀNG HOÁ ĐVT ĐƠN GIÁ S12 Sắt 12 Tấn 120000 X50 Xi Măng P500 Tấn 850000 S10 Sắt 10 Tấn 100000 GHN Gạch hoa m2 45000 GTQ Gạch men TQ m2 40000 X30 Xi Măng P300 Tấn 700000 VET Ván ép Tấn 35000 Yêu cầu:
1. Dựa vào MÃ HÀNG HOÁ và bảng DANH MỤC HÀNG HOÁ để xác định TÊN HÀNG HOÁ, ĐVT, và ĐƠN GIÁ
2. TÍNH DOANH SỐ = ĐƠN GIÁ * SỐ LƯỢNG
3. Tính tổng cho các cột SỐ LƯỢNG, ĐƠN GIÁ, và DOANH SỐ
4. Tính tổng doanh số và đếm số lượng tỉnh theo tỉnh và lưu vào bảng sau: THỐNG KÊ THEO TỈNH MÃ TỈNH TỔNG D.THU S.LƯỢNG TỈNH SG ĐN HN
5. Trang trí và lưu bảng tính Khoa Công Nghệ Thông Tin 1
Bài thực hành Tin Ứng Dụng BÀI 2
Mục đích: Sử dụng các hàm thống kê đơn giản, các hàm CSDL Dsum, Dmax, Dmin, Daverage, Dcount, DcountA..
Nhập CSDL sau vao bảng tính A B C D E F G H 1
Công Ty Khách Sạn Bình Minh. 2 BẢNG THEO DÕI DOANH THU 3 Đơn vị tính: 1000 đ . 4 NGÀY NGÀY LƯU LOẠI ĐƠN TIỀN STT TÊN KHÁCH ĐẾN ĐI TRÚ PHÒNG GIÁ PHÒNG 5 1 Võ Chí Công 14/05/2002 22/05/2002 TR-A 6 2 Võ Vô Tư 16/05/2002 03/06/2002 L1-B 7 3 Dương Thanh Liêm 30/05/2002 11/06/2002 L2-C 8 4 Dương Chính Trực 02/06/2002 07/06/2002 L1-A 9 5 Đỗ Cân Bằng 05/06/2002 28/06/2002 TR-C 10 6 Đỗ Văn Minh 09/06/2002 15/06/2002 L2-A 11 7 Bao Thanh Thiên 12/06/2002 07/07/2002 TR-B 12 8 Tôn Công Sách 21/06/2002 06/07/2002 L1-B 13 9 Triển Chiêu 25/06/2002 04/07/2002 L1-A 14 10 Lưng Gù 28/06/2002 05/07/2002 L1-C 15 16 Bảng Giá Tiền Phòng Tên 17 Hạng L1 L2 TR vùng là 18 A 150 125 105 Row7 Tên 19 B 120 95 85 vùng là 20 C 100 75 65 Col7 Tên Yêu cầu: vùng là Table7 Cáu 1: LƯU Số ngày
TRÚ bằng NGÀY ĐI - NGÀY ĐẾN +1.
Cáu 2: Tính ĐƠN GIÁ cho mỗi loại phòng theo bảng giá tiền phòng
Có thể đặt tên cho các vùng như trên: Table7,Row7, Col7 để đơn giản khi sử dụng cộng thức.
Dùng hàm INDEX kết hợp với hàm MATCH để đối chiếu với bảng
Table7 mà lấy đơn giá cho từng loại phòng.
Cáu 3: Tính tiền phòng biết rằng: TIỀN PHÒNG bằng LƯU TRÚ nhân với ĐƠN GIÁ, trong đó:
Nếu số ngày lưu trú lớn 20 ngày thì giảm 10%.
Nếu số ngày lưu trú từ 10 đến 20 ngày thì giảm 5%.
Nếu số ngày lưu trú nhỏ hơn hoặc bằng 10 thì không giảm.
Câu 4. Sử dụng các hàm SCDl, Thực hiện các thống kê sau STT YÊU CẦU KẾT QUẢ 1
Tổng số ngày lưu trú của các khách hàng trong tháng 5 ? 2
Tổng số ngày lưu trú của các khách hàng trong tháng 6 ?` 3
Tổng số tiền mà phòng Hạng A đã cho thuê ? 4
Tổng số ngày mà khách hàng đã ở trong tháng 6 và ở hạng B ? 5
Tính tiền phòng lớn nhất mà phòng L1 thu được ? 6
Tính số ngày ở trung bình của loại phòng L2 ? 7
Đếm xem có bao nhiêu lần khách hàng đã thuê phòng TR hoặc L1 ? 8
Tính số ngày ở ít nhất của phòng Hạng A ? 9
Số ngày lưu trú trung bình của phòng hạng A hoặc B ?
Câu 5. Thực hiện trang trí và lưu bảng tính. Khoa Công Nghệ Thông Tin 2
Bài thực hành Tin Ứng Dụng BÀI 3.
Mục đích: Sử dụng hàm CSDL.
Mở một Workbook mới và lập bảng tính sau: A B C D E F G H I J 1
DANH SÁCH LƯƠNG DOANH NGHIỆP THÁNG 11//2005 2 MASO HỌ TÊN C.VỤ PHÁI TĐVH LGCB N.CÔNG P.CẤP THƯỞNG T.LƯƠNG 3 AFD8 Thu Giang 460 23 4 CFC1 Lê Hương 310 24 5 CMT5 Như Thông 330 23 6 BMC7 Minh Hoàng 430 25 7 AMD7 Thanh 320 24 Quang 8 CFT3 Anh Đào 320 22 9 CFC6 Hao Lài 360 26 10 CFT4 Thanh Thuý 350 23 11 BMD5 Quang Tùng 390 20 12 CMC9 Chí Công 380 23 13 14 15 MS1 C.VỤ MS2 PHÁI MS3 TĐVH 16 A TP F Nữ D Đại Học 17 B PP M Nam C Cao Đẳng 18 C NV T Trung Cấp 19 20 C.VỤ TP PP NV 21 PCCV 60000 45000 3000 0
MASO cho trên gồm 4 kí tự:
Kí tự đầu là chức vụ. Kí tự thứ hai là PHÁI. Kí tự thứ ba là TĐVH.
Kí tự thứ tư là Năm Công Tác. Yêu cấu:
CÁU 1: Căn cứ vào MASO chèn các thông tin vào các cột PHÁI, C.VỤ, TĐVH.
Dùng hàm VLOOKUP để dò tìm.
CÁU 2: Tính phụ cấp (P.CẤP) = PCCV+Năm công tác nhân 6000. Với PCCV được cho ở bảng phụ trên.
CÁU 3: Tính thưởng, biết:
N.CÔNG>=25 thì thưởng 120000.
23<=N.CÔNG<25 thì thưởng 70000.
Còn lại thưởng thưởng 20000.
CÁU 4: Tính T.LƯƠNG = (LGCB * 2100 * N.CÔNG)/26 + P.CẤP + THƯỞNG.
CÁU 5: Tính tổng cho các cột P.CẤP, THƯỞNG, T.LƯƠNG.
CÁU 6: Tính tổng P.CẤP, THƯỞNG, T.LƯƠNG theo phái và lưu theo mẫu sau. PHÁI P.CẤP THƯỞNG T.LƯƠNG Nữ 312000 350000 4093000 Nam 408000 350000 4195000 Tổng Cộng 720000 700000 8288000
Tính tổng P.CẤP theo phái là nữ : =SUMIF(Các Giá Trị Cột PHÁI,"=Nữ", Các Giá Trị Cột P.CẤP) Khoa Công Nghệ Thông Tin 3
Bài thực hành Tin Ứng Dụng
Tính tổng P.CẤP theo phái là nam : =SUMIF(Các Giá Trị Cột PHÁI,"=Nam", Các Giá Trị Cột P.CẤP)
Tính tổng THƯỞNG, T.LƯƠNG tương tự.
CÁU 7: Tính tổng cộng các cột P.CẤP, THƯỞNG, T.LƯƠNG ở bảng tính trên. So sánh các giá trị
này với các giá trị tổng cộng của bảng tính chính. CÁU 8:
Đếm có bao nhiêu người có T.LƯƠNG >=800000.
Đếm có bao nhiêu người có C.VỤ là NV.
Và lưu vào bảng tính phụ sau.
Có bao nhiêu người có T.Lương lớn hơn 800000 ? Có bao nhiêu người là NV C.VỤ ?
CÁU 9: Trang trí và lưu bảng tính với tên Bai Tap 11.XLS. Khoa Công Nghệ Thông Tin 4
Bài thực hành Tin Ứng Dụng BÀI 4
Mục đích: Các thao tác trên danh sách dữ liệu: SORT, FILTER.
Mở một Workbook mới và nhập bảng tính sau: A B C D E F G 1
DANH SÁCH HỌC VIÊN ĐƯỢC CẤP HỌC BỔNG - NĂM HỌC 2018-2019 2 3 STT HỌ TÊN NG.SINH NOI SINH MAĐ ĐTB H.BỔNG TUỔI 4 1 Thu Giang 15/05/1978 Đà Nẵng A01 5 2 Như Thông 13/02/1975 Đà Nẵng B03 6 3 Lê Hương 11/11/1978 Quảng Nam C04 7 4
Minh Hoàng 12/10/1976 TT-Huế A03 8 5 Thu Hiền 01/04/1980 Quảng Nam C02 9 6 Anh Đào 18/02/1978 Đà Nẵng B01 10 7 Hao Lài 01/01/1980 Quảng Nam B05 11 8
Thanh Thuý 09/09/1978 Đà Nẵng A02 12 9
Quang Tùng 10/10/1979 Quảng Nam C01 13 10 Chí Công 24/09/1978 TT-Huế B02 BẢNG ĐIỂM MÃ ĐIỂM CƠ SỞ C.MÔN N.NGỮ C01 10.0 9.0 9.0 C04 7.0 8.0 10.0 A03 2.0 5.5 6.5 B05 5.5 7.5 5.5 B01 9.0 9.5 9.5 B03 8.5 10.0 8.0 A02 9.0 3.5 9.5 B02 6.0 8.5 10.0 A01 4.5 7.0 3.0 C02 7.0 6.0 8.0 Yêu cầu:
Cáu 1: Căn cứ vào MAĐ và Bảng Điểm (Table9) để tính điểm trung bình (ĐTB).
Cáu 2: Để tính điểm trung bình cần phải biết 3 cột điểm, do đó sử dụng hàm AVERAGE với 3 lần
dùng VLOOKUP, mỗi lần chỉ thay đổi cột trả giá trị:
= AVERAGE (VLOOKUP (MAĐ, Table9, 2, 0), VLOOKUP (..., ..., 3, 0), VLOOKUP (..., ..., 4, 0))
Cáu 3: Tính học bổng (H.BỔNG) theo điều kiện sau: Nếu >=9 thì ĐTB H.BỔNG là 240000. Nếu >=8 thì ĐTB H.BỔNG là 180000. Nếu >=7 thì ĐTB H.BỔNG là 120000.
Ngoài ra không có học bổng.
Cáu 4: Trích ra bảng tính khác và lưu vào Sheet2 với những điều kiện sau:
Những học viên có mức học bổng 240000.
Những học viên có mức học bổng 180000.
Những học viên có mức học bổng 120000.
Những học viên không có học bổng.
Lập bảng điều kiện lọc: Các vùng điều kiện H.BỔNG H.BỔNG H.BỔNG H.BỔNG lọc về học bổng 240000 180000 120000 0 Khoa Công Nghệ Thông Tin 5
Bài thực hành Tin Ứng Dụng
Để trích lọc ra bảng tínhnằm ở
khác: Kích chuột vào ô trống của Sheet sẽ trích lọc đến, Sheet
kích chọn Menu Data Filter Advancel Filter, xuất hiện hộp thoại sau: Địa chỉ vùng dữ liệu cần trích lọc Địa chỉ bảng điều kiện trích lọc Địa chỉ vùng chứa dữ liệu khi lọc
Cáu 5: Trích ra bảng tính khác và lưu vào Sheet3 với những điều kiện sau: Từ 20 đến 39 40 hoặc 41 tuổi. Trên 40 tuổi. Các vùng điều kiện TUỔI TUỔI TUỔI TUỔI lọc về tuổi >=20 <=39 40 >40 41
Cáu 6: Thực hiện thao tác sắp xếp theo thứ tự Họ Tên, NG.Sinh, ĐTB. Sau mỗi lần sắp xếp hãy quan sát sự thay đổi.
Cáu 7: Trang trí và lưu bảng tính với tên Bai Tap 12.XLS.
Mục đích: Vẽ biểu đồ.
Mở một Workbook mới và lập bảng tính sau: A B C D 1 SỐ LIỆU VỀ DÂN SỐ 2 ĐVT: triệu người 3 T.PHỐ NĂM 95 NĂM 96 TĐỘ TĂNG GIẢM/ NĂM 4 CKT 3.75 4.16 5 VKT 2.15 2.45 6 VTV 1.55 1.75 Yêu cầu:
Câu 1: TĐỘ TĂNG GIẢM/ NĂM = (NĂM 96 - NĂM 95) / NĂM 95
Câu 2: Mô tả tổng quát dân số của các thành phố qua 2 năm 1995 và 1996
Câu 3: Mô tả tốc độ tăng giảm dân số qua 2 năm
Câu 4. Trang trí và lưu bảng tính Khoa Công Nghệ Thông Tin 6
Bài thực hành Tin Ứng Dụng
BÀI 5. Cho các bảng số liệu sau:
Bảng 1. Công ty xuất nhập khẩu Vạn Thành
BẢNG THỐNG KÊ HÀNG HOÁ MUA BÁN QUÝ 1 NĂM 2004 NGH THÀN NGÀY KHÁC H IỆP MÃ TÊN SỐ H TIỀN THANH CHỨNG TỪ HÀNG HÀNG HÀNG LƯỢNG THUẾ TOÁN VỤ TIỀN 25/01/2006 Công ty Bến Thành M XD 456 31/03/2006 Cửa hàng Bách Hợp M PB 568 25/02/2006 Công ty Bến Thành M PB 347 05/04/2006 Công ty Bến Thành M XD 975 03/05/2006 Đại lý Tiến Thành B DC 347 13/06/2006 Cửa hàng Bách Hợp B DC 976 21/06/2006 Đại lý Tiến Thành B GN 568 24/07/2006 Đại lý Tiến Thành B GN 765 27/08/2006 Cửa hàng Bách Hợp B GN 345 Bảng 2:
BẢNG TRA TÊN HÀNG VÀ ĐƠN GIÁ Đơn Giá từng tháng Mã Hàng Tên Hàng Tháng 1 Tháng 2 Tháng 3 GN Gạo Nàng Hương 12000 13500 11000 XD Xăng m92 10500 10500 10000 PB Phân Bón 6000 6500 58000 DC Đường cát 9000 9500 9300 YÊU CẦU:
Câu 1: Nhập và định dạng dữ liệu của hai bảng trên vào bảng tính
Câu 2: Dựa vào mã hàng ở bảng 1 và bảng 2, điền vào giá trị cho cột Tên Hàng
Câu 3: Tính Thành Tiền=Số lượng * Đơn giá, trong đó Đơn giá của mỗi mặt hàng thì dựa vào mã
hàng và ngày chứng từ ở bảng 1 và tra bảng 2
Câu 4: Tính Tiền Thuế = Thành Tiền * Phần trăm Thuế, với Phần trăm thuế là 10% và nếu đơn vị
nào mua hàng (nghiệp vụ là M) thì được tính giảm thuế, còn bán hàng (nghiệp vụ là B) thì tính thêm thuế.
Câu 5: Tính Thanh Toán=Thành tiền +Tiền Thuế
Câu 6. Sử dụng các hàm của Database để thực hiện các thống kê số liệu sau: STT YÊU CẦU KẾT QUẢ 1
Tổng Số lượng Gạo Nàng Hương ? 2
Tổng thanh toán của Xăng m92 mà công ty Bách Hợp đã Bán ? ? 3
Khối lượng hàng hoá nhỏ nhất mà Công ty Bách Hợp đã bán ? 3
Tổng Thanh toán của các khách hàng trong tháng 3 ? 4
Tổng Thuế trong tháng 1 và 2 ? 5
Tổng Thanh toán của Cửa hàng Bách Hợp ? 6
Mức Thuế mà Đại lý Vĩnh Thịnh và Cửa hàng Bách Hợp đã thanh ? toán Khoa Công Nghệ Thông Tin 7
Bài thực hành Tin Ứng Dụng 7
Mức thuế mà công ty Minh Viễn thanh toán trong tháng 1 ? 8
Tổng thuế của các mặt hàng có số lượng >=500 ? 9
Tổng số lượng các mặt hàng phải nộp thuế ? 10
Tổng số lượng các mặt hàng được giảm thuế ? 11
Giá trị thanh toán nhỏ nhất của các mặt hàng được giảm Thuế ? 12
Tổng số lượng của mặt hàng được hàng trong tháng 3 ?
Câu 7. Với số liệu từ Bảng 1, sử dụng tính năng Subtotal để tổng hợp lại các lại các số liệu sao cho
có thể theo dõi được các thông tin:
a). Về số lượng hàng hoá và mức Thanh toán cho mỗi khách hàng
b). Về Thành Tiền và Số lượng cho mỗi mặt hàng.
c). Mức thành tiền lớn nhất cho mỗi mặt hàng
d). Số tiền thuế mà mỗi mặt hàng đã thanh toán
e).Số tiền đã thanh toán cho các nghiệp vụ mua hoặc bán
f). Số lượng hàng hoá lớn nhất mà mỗi khách hàng đã mua hoặc bán
g). Tên và số lượng hàng hoá tiêu thụ ít nhất của mỗi khách hàng Khoa Công Nghệ Thông Tin 8
Bài thực hành Tin Ứng Dụng BÀI 6
Mục đích: Tổ chức dữ liệu, thao tác và sử dụng các hàm cơ bản trên bảng tính Excel.
Đề: Hãy tổ chức công tác quản lý lương của doanh nghiệp X bằng Microsoft Excel. Biết
rằng công ty X là một doanh nghiệp kinh doanh nhỏ, số lượng nhân viên không nhiều (30
nhân viên). Thu nhập của nhân viên trong công ty hằng tháng dựa trên hai khoản chính: tiền
lương và tiền thưởng. Sau đây là cách thức tính lương của doanh nghiệp:
1. Ngày công thực tính=ngày công thực làm, nếu ngày công thực làm<=số ngày làm
việc chuẩn trong tháng. Mỗi ngày công thực làm vượt quá số ngày làm việc chuẩn sẽ được tính gấp đôi.
2. Lương chính=(Bậc lương*Hệ số lương)*(Ngày công thực tính/Số ngày làm việc chuẩn).
3. Phu cấp=(Mức phụ cấp*Hệ số lương)*(Ngày công thực tính/Số ngày làm việc
chuẩn), mức phụ cấp căn cứ vào chức vụ nhân viên đó đang đảm nhận.
4. Lương=Lương chính+Phụ cấp.
5. Tổng thưởng=Quỹ lương-Tổng lương toàn nhân viên. Doanh nghiệp tự phân bổ quỹ
lương dựa vào tính hình kinh doanh trong tháng.
6. Thưởng mỗi người=(Tổng thưởng/Tổng số ngày công thực tính)*Ngày công thực tính của mỗi người.
7. Tổng lương của mỗi người=Lương+Thưởn.
8. Hàng tháng nhân viên được ứng lương vào thời điểm giữa tháng. Số tiền lương ứng
căn cứ vào số ngày công đã làm, mức lương chính và phụ cấp của từng nhân viên đến
thời điểm đó. Phần lương ứng được lập riêng trong một bảng, kết quả sẽ được liên kết
với bảng chính, người làm lương không cần phải cập nhật lại số tiền tạm ứng vào cuối tháng.
9. Bảo hiểm xã hội=5% lương không kể thưởng do nhân viên chịu. Ngoài ra, doanh
nghiệp chịu thêm 15%. Hàng tháng doanh nghiệp phải nộp 17% cho cơ quan BHXH,
3% doanh nghiệp giữ lại để chi trực tiếp cho công nhân viên về các khoản trợ cấp, ốm
đau, thai sản, tai nạn lao động.
10. Bảo hiểm y tế=1% lương không kể thưởng do nhân viên chịu. Ngoài ra, doanh nghiệp chịu 2%.
11. Thuế thu nhập dựa trên tổng lương (kể cả thưởng), được tính theo phương pháp lũy tiến như sau: Từ Đến Mức thuế <=3.000.000 0% Khoa Công Nghệ Thông Tin 9
Bài thực hành Tin Ứng Dụng 3.000.001 4.000.000 5% 4.000.001 6.000.000 10% >6.000.000 15%
12. Còn lại=Tổng lương-Tạm ứng-BHXH-BHYT-Thuế thu nhập
Một số danh mục phục vụ cho công việc tính lương: BẢNG DANH MỤC CHỨC VỤ BẢNG BẬC LƯƠNG Mã chức Mức phụ Mã bậc Chức vụ Bậc lương vụ cấp lương GĐ Giám đốc 400 1 1000 KT Kỹ thuật 100 2 1100 NV Nhân viên - 3 1200 PGĐ Phó GĐ 300 4 1300 PP Phó phòng 150 5 1400 TP Trưởng phòng 200 6 1500 CÁC THAM SỐ CHUNG Quỹ lương 50.000.000 Hệ số lương 3.000 Số ngày làm việc chuẩn 26 Khoa Công Nghệ Thông Tin 10
Bài thực hành Tin Ứng Dụng
BÀI 7. Cho các bảng số liệu sau:
BẢNG TRA TÊN HÀNG VÀ ĐƠN GIÁ ĐƠN GIÁ THEO QUÝ MA VT TÊN HÀNG QUÝ 1 QUÝ 2 QUÝ 3 QUÝ 4 Nhập Xuất Nhập Xuất Nhập Xuất Nhập Xuất N7610 Nokia 7610 5256 5265 5256 5257 5257 5266 5270 5490 N6060 Nokia 6060 2281 2283 2279 2281 2290 2295 2290 2300 SX640 SamSung X640 2788 2797 278 2789 2773 2773 2790 2900 ME398 Motorola E398 3346 3355 3335 3337 3343 3343 3350 3350 O2XDA O2 XDA 11776 11785 11789 11789 11793 11793 11790 14900 SE530 SamSung E530 5168 5168 5157 5166 5156 5165 5170 5600 SCF75 Siemens CF75 2931 2932 2944 2953 2939 2939 2950 2799 LT960 Lenovo ET960 7986 7993 7998 8005 7997 7997 7999 9990 MC390 Motorola C390 2338 2339 2337 2343 2337 2343 2350 2399 INA10 Inno A10 4085 4087 4100 4102 4106 4100 4100 4199 Bảng 2:
BẢNG THỐNG KÊ NHẬP XUẤT QUÍ 1 Ngày CT Mã VT Số Lượng Nhập Số Lượng Xuất Thành tiền 05/01/06 N6060 43 08/02/06 ME398 30 02/02/06 O2XDA 25 26/03/06 LT960 26 30/03/06 INA10 44 Bảng 3:
BẢNG THỐNG KÊ NHẬP XUẤT QUÍ 2 Ngày CT Mã VT Số Lượng Nhập Số Lượng Xuất Thành tiền 07/04/06 N6060 37 20/04/06 ME398 78 27/05/06 SE530 70 26/05/06 SCF75 66 10/06/06 MC390 42 Bảng 4:
BẢNG THỐNG KÊ NHẬP XUẤT QUÍ 3 Ngày CT Mã VT Số Lượng Nhập Số Lượng Xuất Thành tiền 01/07/06 N7610 25 25/07/06 ME398 29 15/08/06 O2XDA 17 1/09/06 LT960 30 15/09/06 INA10 60 Khoa Công Nghệ Thông Tin 11
Bài thực hành Tin Ứng Dụng Bảng 5:
BẢNG THỐNG KÊ NHẬP XUẤT QUÍ 4 Ngày CT Mã VT Số Lượng Nhập Số Lượng Thành tiền Xuất 02/10/06 EX640 42 25/10/06 N7610 14 08/11/06 SE530 99 07/12/06 SCF75 99 25/12/06 SX640 13 YÊU CẦU:
Câu 1: Nhập bảng số liệu vào máy tính theo yêu cầu sau:
Mỗi bảng số liệu nằm trên 1 sheet, đặt tên các sheet theo qui định: BangDonGia, Quy1, Quy2, Quy3, Quy4
Câu 2: Tính thành tiền của mỗi Quý biết rằng:
Thành tiền =Số lượng * Đơn giá, với Đơngiasr dữa vào Số lượng Nhập hoặc Xuất để định đơn giá
nhập hay xuất. Nếu Số lượng Nhập >0 thì Đơn Giá Nhập, ngược lại nếu Số Lượng Xuất >0 thì Đơn Giá Xuất.
Nếu hàng hoá là nhập thì Thành tiền là số âm (Chi ra), ngược lại: hàng hoá là xuất thì Thành tiền là dương (thu vào)
Đơn giá Nhập hoặc Xuất cho mỗi mặt hàng thì dựa vào Ma VT ở mỗi Quý và tra ở bảng TraGia
Câu 3. Chèn thêm một Sheet mới, đặt tên là TongHop. Sử dụng chức năng Consolidate để tổng hợp
tình hình nhập xuất hàng hoá. Yêu cầu
+ Các số liệu tổng hợp lấy từ các Sheet Quy 1 Quy 2, Quy 3, Quy 4.
+ Số liệu tổng hợp đặt ở Sheet TongHop
+ Tạo sự liên kết giữa các Sheet số liệu và số liệu tổng hợp được. Khoa Công Nghệ Thông Tin 12
Bài thực hành Tin Ứng Dụng
BÀI 8 :Cho bảng số liệu sau Bảng 1 Ma KH Tên Khách Hàng CTLB
Công ty Thực Phẩm Long Biên CHVS Cửa hàng Vissan STMC Siêu thị Minh Châu NHDP Nhà Hàng Đông Phương DLTV
Đại lý Thực Phẩm Tiến Vũ Bảng 2.
BẢNG TRA ĐƠN GIÁ HÀNG HOÁ THEO NGHIỆP VỤ VÀ THÁNG Nghiệp Vụ Nhập Xuất Giá theo Tháng Từ 1-4 Từ 5 -8 Từ 9-12 Từ 1-4 Từ 5 -8 Từ 9-12 Gạo 3500 3800 4100 3900 4200 48500 Đường 5700 6200 6000 5500 6500 7000 Sữa 8200 7800 9000 8500 9200 8800 Tiêu 12700 11900 13000 11000 15000 14200 Ca Cao 21500 20000 23000 19000 24000 22000 Thịt 37000 38500 39000 36800 40000 41500 Dừa 2800 2100 2600 2450 2300 3100 Bảng 3
BẢNG THỐNG KÊ HÀNG HOÁ NHẬP XUẤT NĂM 2004 Ngày CT Nghiệp Ma KH Tên Hàng Hoá Số Thành Thuế Thanh Vụ KH Lượng Tiền toán 11/01/06 Xuất DLTV Ca Cao 96 22/02/06 Nhập CTLB Sữa 87 25/02/06 Xuất DLTV Gạo 51 02/03/06 Nhập NHDP Tiêu 32 03/03/06 Nhập CHVS Dừa 57 06/03/06 Xuất CTLB Thịt 78 09/03/06 Nhập NHDP Gạo 51 24/03/06 Xuất CHVS Ca Cao 51 21/04/06 Xuất STMC Đường 64 11/06/06 Xuất DLTV Tiêu 44 20/06/06 Xuất CTLB Sữa 66 06/07/06 Nhập STMC Dừa 85 04/09/06 Nhập DLTV Dừa 71 19/09/06 Xuất STMC Thịt 14 16/10/06 Nhập NHDP Ca Cao 31 25/10/06 Xuất DLTV Đường 84 27/11/06 Xuất CHVS Sữa 62 18/12/06 Nhập NHDP Gạo 72 26/12/06 Nhập DLTV Đường 28 YÊU CẦU:
Câu 1: Nhập bảng số liệu vào bảng tính theo yêu cầu sau:
+ Mỗi bảng số liệu nằm trên mỗi Sheet. Đặt tên sheet theo quy định như sau: DanhSachKH, HangHoa, NhapXuat
Câu 2: Tính các cột số liệu như sau:
a). Cột Tên KH dựa vào MaKH ở Sheet NhapXuat và tra ở Sheet DanhSachKH
b) Cột ThanhTien=Số lượng * Đơn Giá, trong đó Đơn Giá Cho mỗi mặt hàng dựa vào Tên hàng hoá,
Ngày Công thức, và nghiệp vụ ở Sheet XuatNhap và tra ở bảng HangHoa Khoa Công Nghệ Thông Tin 13
Bài thực hành Tin Ứng Dụng
c) Tính tiền thuế =Thành tiền *10% và mức thuế và nếu Nghiệp vụ là nhập thì giảm thuế (Số âm),
ngược là Nghiệp vu là xuất thì tính thêm Thuế (số dương)
d) Tính Thanhtoan=Thành tiền +Thuế
Câu 3: Với các số liệu đã cho ở Sheet XuatNhap, sử dụng Pivot Table để tổng hợp số liệu sao cho
co thể theo dõi được các thông tin sau:
a) Khách hàng nào nhập xuất mặt hàng gì?. mức thanh toán cho mỗi mặt hàng là bao nhiêu
b). Hàng Hoá mà khách hàng nhập xuất, tổng số lượng và tổng thanh toán cho mỗi mặt hàng là bao nhiêu?
c) Khách hàng nào nhập xuất hàng hoá gì với mức thanh toán là bao nhiêu, thời gian nhập xuất là lúc nào
d) nghiệp vụ Nhập hoặc xuất và mức thuế được giảm hoặc tính thêm cho từng loại hàng hoá. Khách
nào nhập, xuất mặt hàng nào? Và mức thuế là bao nhiêu?
e) Số lượng hàng Nhập hoặc Xuất theo từng tháng cho từng mặt hàng, thay đổi Pivot Table sao cho
có thể theo dõi cho từng khách hàng nhập xuất hàng hoá theo tháng.
f) Tổng Thanh toán của từng mặt hàng theo nghiệp vụ nhập hoặc Xuất và tỷ lệ phần trăm của từng
mức thanh toán so với tổng thanh toán.
g) Tổng thanh toán của từng Quý và tỷ lệ phần trăm Thanh toán của từng quý so với cả năm. Biết rằng:
Quý 1 bao gồm nhập xuất trong tháng 1,2,3
Quý 2 bao gồm nhập xuất trong tháng 4,5,6
Quý 3 bao gồm nhập xuất trong tháng 7,8,9
Quý 4 bao gồm nhập xuất trong tháng 10,11,12
h) Tổng thanh toán của khách hàng từng quý và tỷ lệ phần trăm của mức thanh toán so với từng quý và năm
i) tên Hàng hoá, tên khách hàng và số lượng hàng hoá nhập xuất lớn nhất, nhỏ nhất cho từng loại hàng hoá Khoa Công Nghệ Thông Tin 14
Bài thực hành Tin Ứng Dụng
BÀI 9 Cho các bảng số liệu như sau: Bảng 1 DANH SÁCH HÀNG HOÁ Tên hàng Dầu ăn Mì gói Đường Mì chính Gia vị ĐVT Lít Thùng Kg Gói Gói Đơn giá 25000 45000 8400 7500 3000
DANH SÁCH NHÂN VIÊN VÀ PHÂN BỐ KHU VỰC Khoa Công Nghệ Thông Tin 15
Bài thực hành Tin Ứng Dụng
Quý 1 (Từ tháng 1 đến tháng 3)
Quý 3 (Từ tháng 6 đến tháng 9) Tên nhân viên Khu vực Tên nhân viên Khu vực Hoa Đà Nẵng Hoa hn Vũ Huế Vũ Huế Thành KonTum Thành KonTum
Quý 2 (Từ tháng 4 đến tháng 6)
Quý 4 (Từ tháng 9 đến tháng 12) Tên nhân viên Khu vực Tên nhân viên Khu vực Hoa Đà Nẵng Hoa Đà Nẵng Vũ Huế Vũ Huế Thành KonTum Thành hcm Bảng 3
BẢNG TỔNG HỢP TÌNH HÌNH TIÊU THỤ HÀNG HOÁ Tên NV Ngày CT Khu vực Hàng hoá Số lượng Thành tiền Vũ 13/01/2006 Gia vị 62 Lan 30/01/2006 Đường 55 Lan 24/02/2006 Dầu ăn 49 Hoa 18/03/2006 Mì gói 69 Vũ 25/04/2006 Dầu ăn 40 Vũ 25/04/2006 Đường 27 Lan 25/04/2006 Gia vị 56 Vũ 10/05/2006 Mì chính 42 Hoa 25/05/2006 Mì gói 37 Hoa 03/06/2006 Mì gói 32 Lan 10/06/2006 Đường 98 Thành 24/06/2006 Gia vị 12 Hoa 11/07/2006 Mì gói 34 Hoa 17/07/2006 Mì gói 31 Thành 19/08/2006 Mì gói 32 Vũ 22/09/2006 Đường 18 Lan 25/09/2006 Dầu ăn 94 Hoa 06/10/2006 Gia vị 15 Thành 06/10/2006 Mì chính 59 Vũ 13/10/2006 Dầu ăn 92 Hoa 05/11/2006 Dầu ăn 23
Câu 1: Nhập các Bảng số liệu vào bảng tính theo yêu cầu sau:
- Mỗi bảng số liệu nằm trên một Sheet. Đặt tên các Sheet theo quy định sau: HangHoa, NhanVien, BangTongHop.
- Lưu lại bảng tính trên C:\ với tên File BiaTapTongHop.xls.
Câu 2: Dựa vào NgayCT ở Sheet BangTongHop và tra trong Sheet NhanVien, hãy điền số liệu vào cột Khu Vực.
Câu 3: Tính Thành tiền = Số Lượng * Đơn giá, trong đó Đơn giá cho từng loại mặt hàng thì dựa vào tên ở Sheet Hàng Hoá
BangTongHop và tra trong Sheet HangHoa.
Câu 4: Xây dựng các biểu đồ theo yêu cầu sau:
a) Biểu đồ biểu diễn tình hình tiêu dùng hàng hoá theo từng khu vực với số liệu là Tên
hàng hoá và mức tổng Thành tiền của hàng hoá đó theo từng Khu vực. Khi chọn tên Khu
vực ở Sheet BangTongHop thì trên biểu đồ chỉ thể hiện các số liệu liên quan tới khu vực đó
và đồng thời tô nền xanh cho các hàng số liệu liên quan đến khu vực đang chọn để biểu diễn trên biểu đồ. Khoa Công Nghệ Thông Tin 16
Bài thực hành Tin Ứng Dụng
b) Biểu đồ biểu diễn số lượng hàng hoá tiêu thụ ở các khu vực theo từng loại hàng hoá ở tại
khu vực đó. Khi chọn tên hàng hoá ở Sheet BangTongHop thì trên biểu đồ chỉ thể hiện các
số liệu liên quan tới từng loại hàng hoá đó và đồng thời trên Bảng tính chỉ hiện thị những
hàng số liệu liên quan đến hàng hoá đang chọn để biểu diễn trên biểu đồ, các hàng số liệu
không liên qua thì không hiển thị.
Lọc các hàng hóa có số lượng từ 50 đến 70 cho các khu vực Huế , Đà nẵng từ tháng 1 đến tháng 5 năm 2006 Khoa Công Nghệ Thông Tin 17
Bài thực hành Tin Ứng Dụng BÀI 10
Mục đích: Tìm kiếm mục tiêu với Goal Seek.
Tiền là phương tiện để mua bán hàng và dịch vụ, do vậy nó có giá trị. Nếu
một cá nhân hoặc cơ quan nào cho vay, gởi ký thác vào ngân hàng hoặc đầu tư một
khoản tiền kinh doanh, thì người cho vay được hưởng một khoản tiền lãi do người
sử dụng số tiền đó phải trả. Tương tự như vậy, nếu một người hoặc một cơ quan vay
một khoản tiền, thì người vay có trách nhiệm chi trả một khoản tiền do sử dụng tiền
của người khác gọi là tiền lãi. Một lượng tiền cho vay hoặc vay gọi là tiền vốn. Các
phép tính tiền lãi thường dựa trên lãi suất và khi tính lãi thường dựa trên lãi gộp.
Lúc thực hiện các phép tính về lãi gộp thì giả sử toàn bộ thời gian được chia ra
thành nhiều khoảng lãi suất liên tục và lãi tích luỹ từ một thời khoảng tính lãi này
đến một khoảng tính lãi kế tiếp.
Giả sử chúng ta cho vay một khoản tiền vốn (P) để thu lãi với lãi suất không
đổi (i) trong một thời khoảng tính lãi kế tiếp (n) năm, ta có công thức tính lãi gộp như sau: F=P(1+i)n
Bài toán 1: Một hộ nông dân dự định vay ngân hàng số tiền là 50.000.000 đồng đê
đầu tư sản xuất, lãi suất vay hàng năm là 7%. Khả năng chi trả cao nhất (tính cả vốn
lẫn lãi) mà hộ nông dân có thể đạt được là 60.000.000 đồng.
a. Vậy thời gian mà hộ này sử dụng số tiền này là bao lâu.
b. Nếu lãi suất là 6.5%, thì thời gian sử dụng vốn là bao nhiêu?
c. Nếu hộ nông dân muốn trả cả vốn lẫn lãi 60000000 trong vòng 3 năm thì lãi
suất của ngân hàng là bao nhiêu?
Bài toán 2: Một nông dân gởi vài ngân hàng số tiền là 20.000.000 đồng, lãi suất
hàng năm lài 5%, biết rằng lãi suất ngân hàng không đổi trong vòng 20 năm tới.
a. Nếu nông dân này không gởi thêm hay rút ra khoản tiền nào thì sau 20 năm
số tiền cả vốn lẫn lãi mà nông dân này có được là bao nhiêu?
b. Nếu người nông dân muốn rút khoản tiền là 30.000.000 (tính cả vốn lẫn lãi)
thì số tiền mà nông dân này phải gởi là bao nhiêu?
c. Nếu muốn nhận được số tiền là 40.000.000 sau 20 năm thì thì nông dân này
tìm ngân hàng có lãi suất tiền gởi hàng năm là bao nhiêu?
Với số tiền gởi 20.000.000 và lãi suất ngân hàng 7%, thì sau bao nhiêu năm người
nông dân sẽ nhân được số tiền 30.000.000 Khoa Công Nghệ Thông Tin 18
Bài thực hành Tin Ứng Dụng BÀI THỰC HÀNH MS ACCESS BÀI THỰC HÀNH SỐ 1 Mục tiêu:
- Tạo cơ sở dữ liệu (Database) và các thao tác trên cửa sổ CSDL
- Thiết kế và chỉnh sửa cấu trúc bảng (Table), tạo khóa chính, tạo mối quan hệ giữa các bảng
- Nhập dữ liệu cho các bảng
- Thực hiện sắp xếp, trích lọc trên các bảng dữ liệu Bài 1:
1.Tạo mới một cơ sở dữ liệu với tên
để quản lý hóa đơn mua bán hàng QLBANHANG.MDB
2.Thiết kế cấu trúc các bảng sau, tạo khóa chính cho
các trường in đậm trong mỗi bảng, tạo
thuộc tính lookup cho các trường của bảng Bảng KHACHHANG: lưu
trữ thông tin của khách hàng, gồm các trường(fields) sau: Field Name Data Type Description Field Properties MAKH Text Mã khách hàng Field Size 4 Format > Input mask LL00 Indexed Yes(No Duplicates) HOLOT Text Họ lót Field Size 30 TENKH Text Tên khách hàng Field Size 10 PHAI Yes/No Giới tính khách hàng. Default Yes Giới tính là Nam: Yes(- 1), Nữ: No(0) DIACHI Text Địa chỉ khách hàng Field Size 50 DIENTHOAI Text Số điện thọai khách Field Size 10 hàng
Bảng NHACUNGCAP: Lưu trữ thông tin của những nhà cung cấp Field Name Data Description Field Properties Type MANCC Text Mã nhà cung cấp Field Size 4 Format > Input mask LL00 Indexed Yes(No Duplicates) TENNCC Text Tên nhà cung cấp Field Size 30 DIACHI Text Địa chỉ nhà cung cấp Field Size 50 DIENTHOAI Text
Số điện thọai nhà cung Field Size 10 cấp Bảng
:Lưu trữ thông tin các mặt hàng gồm các trường sau: DMHANG Field Name Data Type Description Field Properties MAHANG Text Mã hàng Field Size 4 Format > Input mask LL00 Indexed Yes(No Duplicates) TENHANG Text Tên hàng Field Size 30 MANCC Text Mã nhà cung cấp Field Size 4 Format > Input mask LL00 ĐVT Text Đơn vị tính Field Size 10
MANCC có thuộc tính Lookup dạng Combo
Box với dữ liệu được lấy từ trường MANCC của bảng NHACUNGCAP
Hướng dẫn: Trong phần Field Properties bạn chọn sang tab Lookup, chọn Display Control
= Combo Box, Row Source = bảng NHACUNGCAP , Bound Column = 1. Khoa Công Nghệ Thông Tin 19
Bài thực hành Tin Ứng Dụng
Hoặc là tại trường MANCC, trong mục Data type, chọn kiểu Lookup Wizard thay cho kiểu
Text. Trong hộp thọai Lookup Wizard, chọn mục I want the lookup column...
Và tiếp tục làm theo chỉ dẫn.
Bảng HOADON: Lưu trữ thông tin về các hóa đơn, thông tin mua bán hàng của
công ty gồm các trường sau: Field Name Data Type Description Field Properties MAHD Text Mã hóa đơn Field Size 4 Format > Input mask LL00 Indexed Yes(No Duplicates) NGAYHD Date/Time Ngày mua bán Format dd/mm/yy hàng Input mask 99/99/99 Validation Rule >= #01/01/1900# And <= #12/30/2999# Ban phai nhap ngay hoa Validation Text don tu 01/01/1990 den 31/12/2999 MAKH Text Mã khách hàng Field Size 4 Format > Input mask LL00 LOAIHD Text Lọai hóa đơn Field Size 1 (M:mua hàng, B: Default Value “M” bán hàng) Validation Rule “M” or “B” Validation Text Ban chi duoc phep nhap loai hoa don M hay B
MAKH có thuộc tính Lookup dạng Combo Box với dữ liệu được lấy từ trường MAKH của bảng KHACHHANG Bảng
Lưu trữ thông tin chi tiết về các mặt hàng mua bán của từng CTHOADON:
hóa đơn,gồm các trường sau: Field Name Data Type Description Field Properties MAHD Text Mã hóa đơn Field Size 4 Format > Input mask LL00 MAHANG Text Mã nhà cung cấp Field Size 4 Format > Input mask LL00 SOLUONG Mumber Số lượng mua hay bán DONGIA Mumber
Đơn giá của từng mặt hàng
MAHD có thuộc tính Lookup dạng Combobox với dữ liệu được lấy từ trường MAHD của bảng HOADON
MAHANG có thuộc tính Lookup dạng Combobox với dữ liệu được lấy từ trường MAHANG của bảng DMHANG
Chú ý: Bảng CTHOADON không có khóa chính. Khoa Công Nghệ Thông Tin 20