CS170 – Computer Applications for Business Fall 2019 • Assignment 11
This assignment is designed to gain additional proficiency in the use of MS Excel Tables and Pivot Tables. For this assignment, you will upload your Excel file to Canvas so that it can be accessed by just clicking on the file’s link.
To get credit for this assignment:
1 Deliver the assignment11.xlsx file to Canvas on time.
2 Your TA should be able to open your file by clicking on its link.
• Follow the requirements listed on the next page.
• Create the Excel Table and the Pivot Tables as indicated.
• Enter your name on cell J1 of the worksheets.
• Submit your assignment11.xlsx file to Canvas using the Canvas-> Assignments link.
The Voters worksheet shows 4000 fictitious voters. Some research needs to be done to locate a group of information. These are the data fields:
• Voter ID
• Party Affiliation
• Their precinct
• Age group
• When they last voted
• Years they’ve been registered
• Ballot status
A. Convert the Voters dataset into an Excel Table Style Light 16 and set the table name to Voter
B. To answer questions in VoterAnswers worksheet, locate the records of the Voters dataset that comply with all the following characteristics:
1. Count Not DEMOCRAT or REPUBLICAN
2. Largest Precinct
3. Count last voted in 2006
4. Count Voters younger than 41 years old
C. Add a Total row and use the properties of the Total Row to calculate:
1. the count of voters in precinct 2401
2. the count of voters who voted in 11/2005
Pivot Tables and Pivot Charts
Pivot Table 1 :
A. Use the Voters worksheet and create a Pivot Table to display a table filtering on BALLOTSTATUS, with the Precincts, Age Group as row labels and Party as column labels.
B. Filter the information to remove BALLOTSTATUS ABT
C. Set the sum values to be count
D. Rename the PivotTable’s worksheet: Pivot Table 1.
E. Answer questions in VoterAnswers sheet
Pivot Table 2 & Pivot Chart:
A. Use the West Coast Coffee worksheet and create another Pivot Table that displays the average of Sales $ organized by: Product and Month as row labels (in that order) and State as column labels.
B. Collapse the Product rows and create a 3D Clustered Column Pivot Chart.
C. Apply Chart Style 5 and Quick Layout 3.
D. Enter as Title of the Chart: Average Sales Totals.
E. Rename the PivotTable’s worksheet: Pivot Table 2.
F. Move the Pivot Chart to its own worksheet. Rename this new worksheet as Pivot Chart.
1. Chapters 13 and 14 of the Fluency7 textbook
2. Support.office.com Table and Pivot Table Tutorials
3. Lectures and Recitations
本网站支持 Alipay WeChatPay PayPal等支付方式
E-mail: firstname.lastname@example.org 微信号:vipnxx