Bài tập Excel 3 - Tin học đại cương FTU

Họ tên: (ghi họ tên) Phần III: MICROSOFT EXCEL Lớp: (ghi lớp) MSSV: (ghi mã số sinh viên) MICROSOFT EXCEL Câu 1: (1 điểm) Mỗi câu hỏi làm trên 1 trang trong cùng 1 sheet, tạo tiêu đề đầu cuối theo mẫu, lưu file vào thư mục đã tạo. Câu 2: Cho miền CSDL 1 là Bảng quản lý lương nhân viên như sau: BẢNG PHỤ CẤP LƯƠNG THÁNG 12/2021 Code HT HP GV CB Position Principle Vice-Principle Teacher Officer Code T Gender Male No. Employee code F Basic salary Female Name 1,350,000 Gender 1 GV001F Tran Thu Ha 2 HT001T Nguyen Minh Hoang 3 GV002T Đo Mai Nam 4 GV003T Nguyen Hoang Kim 5 HP001T Nguyen Vu Tung 6 CB001T Ngo An 7 GV004F Le Thuy Diem 8 GV005T Lam Thuy Hang 9 CB002F Tran Thu Ha 10 GV006T Nguyen Minh Hoang Requirements: Question 1: (1 score) Use function to fill in Allowance column: Phụ cấp = Position CoAllowance Salary efficient 2.34 6.0 1.99 2.67 5.0 2.34 3.0 3.3 2.67 2.67 1,000,000 if the Position is “Principle” 700,000 if the Position is “Vice-Principle” 500,000 if the Position is “Teacher” 200,000 if the Position is the other. Salary = Allowance + Co-efficient * Basic salary Format the data type as VND (200,000 VND) Question 2: (1 score) Use function, base on the last character of Employee code and Database to fill Gender column. Question 3: (1 score) Use function, base on the first characters of Employee code and Database to fill Position column. Question 4: (1 score) Show the maximum, minimum and average salary: Maximum salary ? Minimum salary ? Average salary ? Thi Microsoft Excel Trang 1 Họ tên: (ghi họ tên) Phần III: MICROSOFT EXCEL Thi Microsoft Excel Lớp: (ghi lớp) MSSV: (ghi mã số sinh viên) Trang 2 Họ tên: (ghi họ tên) Phần III: MICROSOFT EXCEL Lớp: (ghi lớp) MSSV: (ghi mã số sinh viên) Question 5: (1 score) Show the detailed information of any employee with the given employee code: Employee code Name Position Co-efficient Allowance Salary … … GV004F … … … Question 6: (1 score) Filter the list of employees to pick out Officers. The results are printed out as the form below: LIST OF EMPLOYEES No. Employee code Name Gender Position Salary … … … … … … … Total: Question 7: (1 score) Filter the list of employees to pick out Teacher. The results are printed out as the form below: LIST OF EMPLOYEES No. Employee code Name Gender Position Salary … … … … … … Total: Question 8: (1 score) Create reports with detail information for each Gender: Report of the Gender: … (Type Gender here) No. Employee code Name Position … … … … Salary … … … Total: Question 9: (1 score) Create reports with detail information for each Position: Report of the Position: … (Type Position here) No. … Employee code … Name … Gender … Salary … … Total: Question 10: (1 score) Copy the Database to another position. In the new table, sort the table to order the records by "Salary" with ascending orders. Then sort the orders alphabetically (from A to Z) by the "Gender" field. Finally, sort the orders in ascending order by the "Allowance" field. Question 11: (1 score) Copy the Database to another position. In the new table, sort the orders alphabetically (from A to Z) by the "Position" field. Then sort the table to order the records by "Allowance" with descending orders. Finally, sort the orders in descending order by the "Salary" field. Question 12: (1 score) Use function to report the data as the form below: REPORT TABLE Total salary Percentage (%) No. Position Number of employees Total allowance Total salary 1 Officer … … … … 2 Principle … … … … 3 Teacher … … … … 4 Vice-principle … … … … … … Total … Thi Microsoft Excel Trang 3 Họ tên: (ghi họ tên) Phần III: MICROSOFT EXCEL Lớp: (ghi lớp) MSSV: (ghi mã số sinh viên) Question 13: (1 score) Use function to report the data as the form below: REPORT TABLE No. Total salary Gender Number of employees … Percentage (%) Total salary … … … … … … Total Question 14: (1 score) Use function to report the data as the form below: REPORT TABLE Total salary Gender Position Number of employees Total allowance … … … … … Question 15: (1 score) Use function to report the data as the form below: REPORT TABLE Total salary Gender Position Number of employees Total allowance … … … … … Question 16: (1 score) Use function to report the data as the form below: REPORT TABLE Gender Female Male Position Officer Average salary Average salary Principle Average salary Average salary Teacher Average salary Average salary Vice-principle Average salary Average salary Question 17: (1 score) Make a table of statistical table and draw a corresponding chart: STATISTICAL TABLE Gender Officer Principle Teacher Position Total salary Total salary Total salary Female Male Thi Microsoft Excel Total salary Total salary Total salary Vice-principle Total salary Total salary Trang 4