THỰC HÀNH MS - EXCEL | Đại học Kinh tế Kỹ thuật Công nghiệp

THỰC HÀNH MS - EXCEL
Bài thực hành số 1: CHIẾT TÍNH GIÁM GIÁ Khách hàng Địa chỉ Số tiền Ngày nhận Ngày trả Giảm trả hàng hàng giá Nguyễn Thị Thanh Lý Tự Trọng 15000 12/07/2010 10/25/2010 Nguyễn Thị Ngọc Đường 3/2 10000 10/5/2010 10/25/2010
Nguyễn Tam Trung Trần Quốc Toản 30000 12/10/2010 12/11/2010 Trần Minh Trí Hai Bà Trưng 50000 5/8/2010 5/18/2010 Nguyễn Thu Cúc Lý Thường Kiệt 40000 7/9/2010 7/25/2010 Nguyễn Văn Sáu Phạm Hùng 25000 10/20/2010 10/25/2010 Nguyễn Phước Hòa Phạm Ngũ Lão 20000 7/26/2010 7/27/2010 Vũ Minh Tấn Lê Lai 30000 8/27/2010 8/28/2010 Nguyễn Thị Nga Lý Quốc Sư 15000 6/10/2010 6/12/2010 Nguyễn Thị Minh Hàng Bông 10000 5/5/2010 5/12/2010 TỔNG CỘNG: ? ? Yêu cầu:
1. Nhập dữ liệu như bảng tính và định dạng bảng tính (kẻ khung, tô nền,…)
2. Chèn thêm một cột Số thứ tự (STT) trước cột KHÁCH HÀNG và điền dữ liệu cho cột STT.
3. Tính GIẢM GIÁ: nếu khách hàng trả tiền trong vòng 10 ngày kể từ ngày nhận hàng thì
được giảm 30% so với số tiền phải trả, ngược lại không được giảm giá.
4. Thêm cột TIỀN THỰC TẾ vào sau cột GIẢM GIÁ và tính cột TIỀN THỰC TẾ: TIỀN
THỰC TẾ = SỐ TIỀN TRẢ - GIẢM GIÁ.
5. Tính dòng TỔNG CỘNG cột SỐ TIỀN TRẢ, GIẢM GIÁ, TIỀN THỰC TẾ.
6. Định dạng cột NGÀY NHẬN HÀNG và NGÀY TRẢ TIỀN theo dạng: dd/mm/yyyy.
Bài thực hành số 2:
BẢNG LƯƠNG CÁN BỘ CNV
Tháng 07/2010 – Công ty TNHH 3 Sao STT Họ tên Chức vụ Lương căn Ngày công bản 1 Nguyễn Thị Thanh NV 10000 24 2 Nguyễn Thị Ngọc BV 8000 30 3 Nguyễn Tam Trung TP 10000 25 4 Trần Minh Trí GĐ 50000 28 5 Nguyễn Thu Cúc PGĐ 40000 26 6 Nguyễn Văn Sáu PP 25000 29 7 Nguyễn Phước Hòa KT 20000 30 8 Vũ Minh Tấn TP 30000 28 9 Nguyễn Thị Nga NV 15000 32 10 Nguyễn Thị Minh BV 10000 26 11 Nguyễn Thị Hải NV 15000 26 12 Nguyễn Quốc NV 15000 24 13 Triệu Tú PGĐ 40000 28 14 Trần Bội BV 10000 28 15 Phan Minh NV 7000 29 Tổng cộng: Lương trung bình: Lương cao nhất: Lương thấp nhất: Yêu cầu:
1. Nhập dữ liệu như bảng tính và định dạng bảng tính (kẻ khung, tô nền, ...)
2. Chèn thêm vào bên phải cột Ngày công những cột sau: Phụ Cấp CV, Lương, Tạm ứng đợt 1, Còn lại.
3. Phụ Cấp CV được tính như sau: nếu Chức vụ là GD thì tính 500, là PGD thì tính 400, TP
hoặc PP thì tính 300, KT thì tính 250, những trường hợp còn lại là 0.
4. Lương được tính như sau: Lương = Lương căn bản * Ngày công. Biết rằng số ngày làm
việc qui định trong tháng là 25 và số ngày phụ trội được tính gấp đôi.
5. Tạm ứng đợt 1 = 2/3*(Phụ Cấp CV + Lương), mỗi nhân viên sẽ được tạm ứng tối thiểu là
200000 và tối đa là 300000.
6. Còn lại = Phụ Cấp CV + Lương - Tạm ứng đợt 1.
7. Tính Tổng Cộng, Lương bình quân, Lương cao nhất, Lương thấp nhất cho các cột: Lương
căn bản, Ngày công, Còn lại.
Bài thực hành số 3:
1. Nhập dữ liệu cho bảng tính
2. Điền dữ liệu cho cột STT (Số thứ tự).
3. Định dạng cột Ngày sinh và Ngày L.Việc theo dạng dd/mm/yyyy.
4. Tính Tuổi = (Ngày hiện tại - Ngày sinh)/365.25, làm tròn đến hàng đơn vị.
5. Tính Thâm niên = Số năm công tác tính từ Ngày làm việc đến Ngày xét, làm tròn đến hàng đơn vị.
6. Tính Thưởng với điều kiện:
– Những người có Phái là Nam và Số ngày làm việc > 23 hoặc những người có Phái là Nữ và
Số ngày làm việc > 20 thì được thưởng 50000.
– Những người còn lại không được thưởng.
7. Tính Phụ cấp T.Niên với điều kiện:
– Những người có Thâm niên > 12 năm sẽ được lãnh 150000.
– Ngược lại thì những người nào có Tuổi > 35 sẽ được lãnh 120000.
– Những người còn lại không được lãnh Phụ cấp T.Niên.
8. Tính Thực lãnh = Thưởng + Phụ cấp T.Niên - Tạm ứng + Lương
9. Xác định các giá trị:
- V1: Tổng số người trong danh sách.
- V2: Tỉ lệ (%) nhân viên Nam.
- V3: Tỉ lệ (%) nhân viên Nữ.
- V4: Tổng tiền Thưởng phải phát.
- V5: Tổng tiền Phụ cấp T.Niên.
- V6: Số người xin Tạm ứng nhưng chưa trả.
- V7: Số người không xin Tạm ứng.
- V8: Số người xin Tạm ứng nhưng Đã trả.
10. Định dạng bảng tính (kẻ khung, tô nền, ...) và lưu file lại.
Bài thực hành số 4:
BẢNG THƯỞNG – PHỤ CẤP CHI TIẾT Quỹ thưởng 300 Tỷ giá 20200 STT Họ tên Mã NV Số ngày Ngày sinh làm việc Nguyễn Thị Thanh A352BC 22262 Nguyễn Thị Ngọc B297BC 21908 Nguyễn Tam Trung B39HD 24971 Trần Minh Trí A50HD 25693 Nguyễn Thu Cúc B9BC 26541 Nguyễn Văn Sáu B125HD 25851 Nguyễn Phước Hòa A15BC 24694 Vũ Minh Tấn A289HD 24326 Nguyễn Thị Nga A11C1B 26179 Nguyễn Thị Minh A55BC 25692 Tổng cộng:
Tỷ lệ nhân viên biên chế:
Tỷ lệ nhân viên biên hợp đồng:
1. Nhập dữ liệu và trang trí bảng tính
2. Điền dữ liệu cho cột STT (Số thứ tự).
3. Định dạng cột Ngày sinh theo dạng dd/mm/yy.
4. Thêm vào sau cột Ngày sinh các cột Tuổi, Thưởng 1, Thưởng, Phụ cấp, Tổng cộng.
5. Viết công thức điền dữ liệu cho cột Số Ngày Làm Việc (có dạng số), là các ký tự số trong MANV.
6. Tính Tuổi = Năm hiện hành - Năm sinh.
7. Tính Thưởng 1 = Tổng số tuần làm việc * 1.2 + Số ngày lẻ (dưới 1 tuần)*0.1.
8. Tính Thưởng: Nếu Thưởng 1 > Thưởng bình quân thì Thưởng = Thưởng 1. Ngược lại
Thưởng = Thưởng bình quân. Biết Thưởng bình quân = Quỹ Thưởng / Tổng số nhân viên trong danh sách.
9. Tính Phụ cấp: với những người có họ Ngũ và có 2 ký tự bên phải của MANV là BC hoặc
những người có tên Kiều và có ký tự bên trái của MANV là A thì Phụ cấp = 20% số tiền
thưởng. Những người khác không có Phụ cấp.
10. Tính Tổng cộng = (Thưởng + Phụ cấp) * Tỷ giá. Định dạng kết quả theo dạng: ###,###.# VNĐ.
11. Tính Tổng các cột Số Ngày Làm Việc, Thưởng 1, Thưởng, Phụ cấp, Tổng cộng.
12. Thêm cột Diễn Giải vào sau cột Tổng cộng. Điền dữ liệu cho cột Diễn Giải theo dạng CD - HTLD. Trong đó:
- CD (chức danh) là Lãnh Đạo nếu ký tự bên trái của MANV là A, ngược lại là Nhân Viên.
- HTLD (Hình thức lao động) là Biên chế nếu 2 ký tự bên phải của MANV là BC, ngược lại
là Hợp đồng. Ví dụ: Nhân Viên - Biên chế; Lãnh đạo - Hợp đồng, ...
13. Tại ô C16 và C17, hãy tính tỷ lệ (%) nhân viên Biên chế và tỷ lệ nhân viên Hợp đồng.
Bài thực hành số 5: BẢNG THANH TOÁN LƯƠNG STT MÃ NV HỌ VÀ TÊN P_BAN CVỤ PCCV LƯƠNG THỰC LĨNH 1 A01 Trưong Thị Chi GĐ 2 B01 Nguyễn An PGĐ 3 A02 Đặng Quốc Bình TP 4 C02 Nguyễn Đức Duy NV 5 A03 Nguyễn Vũ Dũng TP 6 B02 Phạm Cao Đăng PP 7 C01 Nguyễn Chí Khiết NV 8 B03 Phạm Hải Lan NV 9 A04 Huỳnh Phi Long NV 10 B06 Phạm Bích Loan PP Bảng phụ 1: Mã A B C Tên phòng ban Kế hoạch Hành Chính Kế toán Bảng phụ 2: C. VỤ PCCV LƯƠNG GĐ 50,000 700,000 PGĐ 40,000 650,000 TP 30,000 600,000 PP 25,000 450,000 NV 0 380,000 Yêu cầu:
1. Căn cứ vào ký tự đầu tiên của Mã NV và bảng tên phòng bạn hãy điền tên phòng ban cho
các nhân viên ở cột P_BAN.
2. Căn cứ vào C.Vụ và PCCV của bảng phụ cấp chức vụ hãy nhập tiền phụ cấp chức vụ ở cột PCCV.
3. Căn cứ vào C.Vụ và LƯƠNG của bảng phụ cấp chức vụ hãy nhập tiền Lương ở cột Lương.
4. Thực lĩnh = PCCV + LƯƠNG
Bài thực hành số 6: BÁO CÁO KINH DOANH STT NGÀY KHÁCH LOẠI SLG Đ. GIÁ TIỀN PHỤ CỘNG TỶ HÀNG HÀNG HÀNG PHÍ TIỀN LỆ 1 2-JAN Luận Gạo 520 2 8-MAR Hải Đường 630 3 22 - APR Thuận Sữa 280 4 17 - AUG Đăng Café 450 5 22 - DEC Hường Rượu 340 6 28 - NOV Đăng Rượu 700 7 19 - OCT Hải Sữa 520 8 15 - JUL Luận Café 390 9 26 - JAN Thuận Gạo 210 10 14 - FEB Hải Đường 450 TỔNG HỢP DOANH SỐ BẢNG THAM CHIẾU THEO KHÁCH HÀNG THEO LOẠI HÀNG Loại Đ.Giá Tỷ lệ % Tên Cộng Tỷ Hàng Cộng Tỷ lệ hàng phụ phí tiền lệ tiền Gạo 4000 2.50% Luận Gạo Đường 6000 3.20% Hải Đường Sữa 5500 1.00% Thuận Sữa Cafe 7000 2.80% Đăng Cafe Rượu 10000 0.75% Hường Rượu Yêu cầu:
1. Đ.Giá: Dựa vào Loại hàng và bảng tham chiếu.
2. Tiền hàng = SLG x Đ.Giá.
3. Phụ phí = Tiền hàng x Tỷ lệ % phụ phí (Tỷ lệ % phụ phí dựa vào Loại hàng và Bảng tham chiếu)
4. Cộng tiền = Tiền hàng + Phụ phí.
5. Tỷ lệ = cộng tiền của từng hợp đồng so với tổng tiền
6. Tính tổng của các cột tương ứng.
7. Tổng hợp doanh số của (Cộng tiền & Tỷ lệ ) theo khách hàng và loại hàng.
8. Lọc ra tất cả các mặt hàng là Đường hoặc Sữa và có số lượng bán ra >=500.
Bài thực hành số 7: DOANH THU DU LỊCH NĂM 2010 STT Mã DL
Tuyến DL Số ngày Giá vé Chi phí KS Ngày TT Trị giá Trị giá (USD) (VN) 1 FR05L1 ? 5 ? ? 15/02/2010 ? ? 2 IT04L2 4 10/02/2010 3 DE15L1 15 21/02/2010 4 IT05L1 5 23/02/2010 5 DE06L2 6 17/02/2010 6 FR04L2 4 05/02/2010 7 FR06L1 6 22/02/2010 8 DE10L1 10 19/02/2010 9 DE09L1 9 24/02/2010 BẢNG 1
BẢNG THỐNG KÊ TRỊ GIÁ (VN) Mã Tuyến FR DE IT Tuyến Ngày Ngày Tuyến DL Pháp Đức Ý DL TT<=15/02/2010 TT>15/02/2010 Giá vé (USD) 1500 1600 1700 Pháp ? ? Đgiá Loại 1 60 50 40 Ý ? ? KS Loại 2 40 30 20 Mô tả:
- 2 ký tự đầu của Mã du lịch cho biết Mã tuyến
- 2 ký tự cuối của Mã du lịch cho biết Loại khách sạn thuê là Loại 1 (L1) hay là Loại 2 (L2) Yêu cầu:
1. Tuyến DL: Dựa vào Mã tuyến tra trong BẢNG 1
2. Giá vé: Dựa vào Mã Tuyến tra trong BẢNG 1
3. Chi phí KS = Số ngày * Đơn giá KS. Biết rằng:
- Đơn giá KS: Dựa vào Mã tuyến tra trong BẢNG 1 kết hợp với Loại khách sạn thuê
- Nếu Số ngày >= 10 thì Chi phí KS được giảm 5%, còn ngược lại thì không giảm
4. Trị giá (USD) = Giá vé + Chi phí KS
5. Trị giá (VN) = Trị giá (USD) * Tỷ giá. Biết rằng
- Tỷ giá = 19425 nếu Ngày TT trước ngày 15/02/2010
- Còn ngược lại Tỷ giá = 20000.
6. Sắp xếp lại bảng tính theo thứ tự tăng dần của Số ngày, nếu trùng thì sắp giảm theo Trị giá (VN)
7. Rút trích thông tin của các khách hàng thuộc tuyến du lịch là Đức và có số ngày >=8.
8. Lập BẢNG THỐNG KÊ TRỊ GIÁ (VN) theo mẫu trên.
Bài thực hành số 8: BÁO CÁO CUỐI THÁNG 5/2012 STT Số NX Tên Ngày NX Số lượng Đơn giá Khuyến Thành hàng mãi tiền 1 MG26N 26 52 2 DA17X 17 60 3 XB10X 10 25 4 MG25X 25 80 5 XB19N 19 14 6 DA27N 27 50 7 MG28X 28 70 8 DA25X 25 80 BẢNG 1 BẢNG THỐNG KÊ Mã Tên Đơn giá Mặt Tổng thành hàng hàng Nhập Xuất hàng tiền Mì gói MG Mì gói 3000 3200 Xà bông DA Dầu ăn 30000 31000 Dầu ăn XB Xà bông 5500 6000 Mô tả:
Hai ký tự đầu của Số NX cho biết Mã hàng
Ký tự cuối của Số NX cho biết là hàng Nhập (N) hay là Xuất (X) Yêu cầu:
1. Nhập đúng, đầy đủ dữ liệu
2. Tiền hàng: Dựa vào hai ký tự đầu của Số NX và BẢNG 1
3. Đơn giá: Dựa vào hai ký tự đầu của Số NX và BẢNG 1 kết hợp với ký tự cuối của Số NX
để có giá trị thích hợp
4. Khuyến mãi: Nếu Số lượng >=70 nhà Ngày NX >=25 thì ghi “Có quà tặng”, ngược lại để trống
5. Thành tiền = Số lượng * Đơn giá
6. Lập bảng thống kê theo mẫu
7. Lọc ra các mặt hàng được bán vào ngày 25
Bài thực hành số 9: Mibi Fino
Khách hàng: Toàn Chủ Tịch Khu vực 1
CHIẾT TÍNH PHÍ ĐIỆN THOẠI DI ĐỘNG THÁNG 3/2013 Ngày Gọi đi Số Vùng Cước Cước Cước T. Ghi phút được gọi nội ngoại QT Cộng chú 01/03/2013 10:30 10 2 02/03/2013 7:20 11 2 03/03/2013 19:10 12 3 04/03/2013 0:50 12 1 05/03/2013 23:45 4 4 06/03/2013 2:40 15 4 07/03/2013 9:20 5 2 08/03/2013 10:10 6 3 09/03/2013 8:22 9 1 10/03/2013 2:03 8 2 Bảng thống kê Vùng Số tiền Nội vùng Ngoại vùng Quốc tế Mô tả:
− Nếu Vùng được gọi là 4: gọi Quốc tế.
− Nếu Vùng được gọi trùng với khu vực của khách hàng: gọi nội vùng, ngược lại: gọi khác vùng.
− Giờ bận: từ sau 6 giờ sáng đến trước18 giờ tối.
− Giờ rỗi: từ 18 giờ tối đến 6 giờ sáng.
1. Tính Cước Nội theo những qui định sau:
− Nếu gọi khác vùng hoặc gọi Quốc tế thì Cước Nội =0.
− Ngược lại Cước Nội = Số phút * Đơn giá, Đơn giá là 1800 nếu gọi vào giờ bận,
900 nếu gọi vào giờ rỗi.
2. Tính Cước Ngoại theo những qui định sau:
− Nếu gọi trong vùng hoặc gọi Quốc tế thì Cước Ngoại =0.
− Ngược lại Cước Ngoại = Số phút * Đơn giá, Đơn giá là 3600 nếu gọi vào giờ bận,
1800 nếu gọi vào giờ rỗi.
3. Tính Cước Quốc tế (QT) theo những qui định sau:
− Nếu gọi trong nước thì Cước Quốc tế = 0.
− Ngược lại Cước Quốc tế = Số phút * Đơn giá, trong 5 phút đầu tiên Đơn giá là
10000, từ phút thứ 6 trở đi mỗi phút là 8000 vào giờ bận, 5000 vào giờ rỗi.
4. Tính cột T.Cộng = Cước Nội + Cước Ngoại + Cước QT
5. Cột ghi chú có nội dung theo trường hợp: Trường hợp Ghi chú Gọi trong vùng Nội vùng Gọi khác vùng Ngoại vùng Gọi đi Quốc tế Quốc tế
6. Trong Bảng thống kê, hãy thống kê cột tiền T.Cộng theo vùng gọi.
7. Dựa vào Bảng thống kê, vẽ biểu đồ Pie để so sánh tỷ lệ tổng cộng cước gọi giữa các vùng.
8. Dùng Advanced Filter, hãy lọc ra những cuộc gọi nội vùng.
Bài thực hành số 10:
DOANH THU HÀNG KIM KHÍ ĐIỆN MÁY QUÝ 3 NĂM 2011 STT Số Seri Mã Tên hàng Ngày bán Đơn giá Số Thành MH lượng tiền 1 SO205-14 22/9/2011 20 2 PA555-21 15/9/2011 10 3 PA212-14 10/8/2011 30 4 HI999-14 5/8/2011 5 5 PA525-21 15/8/2011 20 6 SO305-14 7/9/2011 10 7 SO100-21 9/7/2011 5 8 HI678-21 15/7/2011 2 BẢNG 1 BẢNG THỐNG KÊ Mã mh Nhãn hiệu Loại Loại Tên hàng Loại Loại 14inch 21inch 14inch 21inch Sony SO Sony 3000 3200 Panasonic PA Panasonic 30000 31000 HI Hitachi 5500 6000 Mô tả:
Hai ký tự đầu của Số Seri cho biết Mã hàng
Hai kí tự cuối cho biết kích cỡ mặt hàng Yêu cầu:
1. Nhập đúng, đầy đủ dữ liệu
2. Mã hàng: Là hai ký tự đầu của Số Seri
3. Tên hàng: Dựa theo mã hàng và kích cỡ mặt hàng (Ví dụ: SO205-14: Sony 14 inch)
4. Thành tiền: Số lượng * Đơn giá
5. Thành tiền = Số lượng * Đơn giá
6. Lập bảng thống kê theo mẫu
7. Lọc ra các mặt hàng được bán trong tháng 9