Exercise: IS for Commerce

Exercises

1. Filter the data to find out how many students have Foot as their tutor.

2. How many of tutor Smith's students live in Private accommodation?

3. How many students live in a Hall of Residence (ie not Private accommodation)?

4. How many students are called Claire or Clare?

5. Filter the data to show only the students who have one or more middle initials.

6. Filter the students to show just those whose surname begins with S.

7. How many students came in 2005 (as shown in their userid by 05)?

8. How many overseas students are female?

9. Filter the students to show the 12 oldest. Hint: you will need to convert the dates to numbers first (and then back again after filtering)

10. Using an advanced filter, filter the data to show both male students living in Bridges Hall and unmarried female students living in Wessex Hall.

11. List the overseas students who are taking option 2, 4, 6 or 8.

1.      Input data as shown above

2.      Copy to the new sheet and give a sheet name as sorting. Sort the database table according to salary in ordering from max to min of salary

3.      Copy the original data to new sheet and give its name as EX1

4.      Use data form to input 3 peoples to the end of the database.

5.      Use Statistical Function to summary the following:

Gender	No People	Salary	Max	Average
F
M

a.       Total number of people

b.      Sum of salary

c.       Max of salary

d.      Average of salary

6.      Copy the original data from sheet 1 to the new sheet and give its name as EX2. All below actions will do in this sheet.

7.      Create a Pie chart to show the rate of salary based on Gender

8.      Filter to another address to show the list of people in the salary category of 3 or 4

9.      Filter to another address to show the list of Manager people and have salary >= 30.000$

10.  Filter to another address to show the list of Worker people or Female people

11.  Use Pivot Table to summary the salary based on Gender

12.  Use Pivot Table to summary the salary based on Category.

13.  Create a macro and define it as DFFont to change the font style (font name as Time New Roman; Size: 12; Blue color and Bold) for column Name.

Run this macro to apply to column Title; Salary.