Excel Module 5 SAM Project 1a | Georgia Medical Center | MANAGE AND ANALYZE TABLE скачать в хорошем качестве

Excel Module 5 SAM Project 1a | Georgia Medical Center | MANAGE AND ANALYZE TABLE

Excel Module 5 SAM Project 1a | Georgia Medical Center | MANAGE AND ANALYZE TABLE #illustratedexcel Contact Me: WhatsApp No:+1(725) 272-9378 We are providing help in all Online Courses, Computer Science, Business and Management, Business Math, Business and Finance, Business and Accounting, Human Resource Management, History, English. PROJECT STEPS 1. Justin Douglas is the director of professional development at the Georgia Medical Center. In addition to training courses offered to employees, the Georgia Medical Center also hosts continuing education seminars for medical professionals around the country. Justin has been tracking both types of courses in an Excel workbook and asks for your help in managing and analyzing the data. Go to the Training worksheet. Justin wants to sort and filter the data on the worksheet, which lists the employees and the training courses they have requested. Format the range A3:G43 as a table with headers using the Lime, Table Style Light 9 table style. 2. The training courses are offered in two sessions. Justin wants to make sure that employees who enter training course data enter only "1" or "2" in the Session column. Create a data validation rule for the Session column as follows: a. For all cells in the Session column, create an in-cell drop-down data validation rule that accepts only entries from a list. b. Use 1, 2 as the Source values. c. Provide an input message that uses Sessions as the title and the following sentence as the input message: Enter the number of sessions. d. Apply a Stop style error alert that uses Number of sessions as the title and the following sentence as the error message: Enter 1 or 2. 3. Justin received a request from another employee who wants to take a training course and wants to delete any duplicate records from the table. Add and remove records as follows: a. Find and remove a record with a duplicate Employee ID number. b. Add a new record to the end of the table and then insert the data shown in Table 1, using the in-cell drop-down list to enter the Session value. Table 1: Data for the New Record Employee ID Employee Name Department Course Session Hours Approved 2200 Amy Fuller Nursing Business Writing 2 3 No 4. The table is currently sorted by course name, but Justin prefers to have it sorted by employee ID so that he can find the employee records quickly. Sort the table in ascending order (smallest to largest) by the values in the Employee ID column. 5. Apply a table style option to bold the employee IDs in the first column of the table to make it easier to differentiate. 6. After Justin receives a request from an employee, he contacts the department manager for approval so that the employee can attend the course. He wants to print a list of employees who have not been approved for training courses. Use an advanced filter to provide this information for Justin as follows: a. In cell I4, enter No as the value to use for filtering the criteria range. b. Create an advanced filter that copies the results to another location. c. Use the table of employee and training course data (range A3:G43) as the List range. d. Use the information you set for filtering (range I3:I4) as the Criteria range. e. Copy the results to the range starting in cell I6. f. Resize columns I:O to their best fit. 7. Go to the Continuing Education worksheet, which includes a filtered table named Courses. Remove the filter from the Courses table to display all the records. 8. Apply the Lime, Table Style Light 9 table style to the Courses table to use a format similar to other tables in the workbook. 9. Justin notices that no one has enrolled in Session 2 of the Holistic Nursing course (Course ID 6755). Remove the record for Course ID 6755 from the table. 10. Georgia Medical Center charges a per person fee for attending a continuing education course to cover the costs of hiring medical experts to teach the courses. Justin wants to display the total amount charged for each course, the total for all courses, and the number of continuing education courses offered. Provide this information for Justin as follows: a. Insert a new column in the table to the right of the Open Seats column. b. Use Total Charged as the column heading of the new column.