Tài liệu tham khảo Tin học Đại cương 2013

Link download:
http://www.mediafire.com/download/b6limo1mo4cp0rc/TAI_LIEU_TINDC_2012-2013.pdf

Data Grocery Table:
http://www.mediafire.com/download/7if0kiidr34id9u/Grocery.xlsx
Exercise:

Assume that You have created a table of Grocery as above figure.

1.      Create  summary Tables as:

Group	SumOfUnit	AverageOfRevenue
Milk
ice cream
Fruit
Cereal

And

Store	Total group	SumOfUnit
South
North
East
West

2.      Draw a column graph for summary of Group Table; a pie chart for the second one

3.      Copy sheet 1 to another sheet and give name as Filtering

4.      In Filtering sheet complete the below question:

a.       Filter to another address to show the list of products for the year 2007 and 2005.

b.      Filter to another place to show the list of products which have quantity>=900 or Group=”milk”

c.       Filter to another place to show the list of products in store=”south” or product=”milk”

5.      Use Pivot Table to summary the product by year and store: calculate the number of product; total unit and total Revenue.

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.