本次美国商科代写的主要内容是完成基础的Excel数据处理
CS170 – Computer Applications for Business
学习目标：
该作业旨在介绍：
使用MS Excel的工作表的基础。
创建简单和复杂的公式，
吸引人和强调的格式。
创建解释性图表。
要获得此作业的功劳，请执行以下操作：
1.准时将assignment9.xlsx文件提交给Canvas。
方向：
请遵循下面列出的要求。
使用公式分析员工表。
使用“画布”->“分配”链接将您的assignment9.xlsx文件提交到“画布”。
要求：
1.员工表
公式：使用公式填写缺少的信息（黄色单元格U列）。将工资格式设置为$：
使用COUNTIF确定员工表上的女性人数吗？
使用COUNTIF确定员工表上的男性人数？
年薪总额是多少？
平均年薪是多少？
最高薪水是多少？
使用COUNTIF查看有多少人的加薪幅度为零
第1页，共3页
2。
使用SUMIF对西部地区所有员工的薪金求和
使用AVERGEIF查找东北地区员工的平均工资：
全体员工的工资中位数是多少？
工资最低的人在哪个地区？
使用COUNTA查看有多少封电子邮件为非空白
使用COUNTBLANK显示有多少封电子邮件为空白
使用T列显示“ 1985年1月1日”之后出生并在公司工作超过8年的所有员工的TRUE（“公司年龄”在“公司”中使用）。否则显示该员工记录的“ FALSE”
图表（使用本作业第一部分答案旁边的W列和更大的列显示数据和图表）
3D簇状柱形图：创建显示区域与平均薪水，高薪的对比。如果您创建一个包含每个区域的列，并使用公式在单独的列中为每个区域进行计算，然后将其放入工作区中以填充图表，则操作会变得更加容易。将图表图块更改为“按地区划分的薪水”。将垂直轴标题设置为Salary。
3D饼图：创建显示区域和每个区域的雇员人数。将标题设为“每个区域的雇员”。选择一种显示每个饼图百分比的样式。设置一个单独的工作区，在该工作区中每个区域都有一列，然后在其旁边的列中输入每个区域的雇员数。这将帮助您正确创建饼图
折线图：显示每个区域的折线图和每个区域的最高薪水。您可能会发现将值复制到工作区以生成图表更加容易。图表的标题是高薪。垂直轴标题为薪水
公式电子表格
使用下面列出的公式在指示的单元格（黄色背景）中输入公式。
摊还贷款付款：𝐴=𝑟（1 +𝑟）𝑛（1 +𝑟）𝑛-1
梯形的面积：A = 1 h（b1 + b2）2
球体的体积：𝑉= 4 𝜋𝑟3 3
最终速度：𝑣𝑓=√2×𝑎×𝑑+𝑣𝑖2
EffectiveRate：（1+𝑟）𝑛− 1
参考：
𝑛
第2页，共3页
Fluency7教科书第13章
朗诵和讲座
Learning Objective:
This assignment is designed to introduce:
- The basics of worksheets using MS Excel.
- Creating simple and complex formulas,
- Formatting for appeal and emphasis.
- Creating interpretive charts.
To get credit for this assignment:
1. Submit the assignment9.xlsx file to Canvas on time.
Directions:
- Follow the requirements listed below.
- Use formulas to analyze the employee sheet.
- Submit your assignment9.xlsx file to Canvas using the Canvas-> Assignments link.
Requirements:
1. EmployeespreadsheetFormulas: Complete the missing information (yellow cells column U) with formulas. Format the salaries in $:
- Use COUNTIF to determine number of females on the employees sheet?
- Use COUNTIF to determine number of males on the employees sheet?
- What is the total annualized payroll?
- What is the average annual salary?
- What is the highest salary?
- Use COUNTIF to see how many people got a zero percent pay raise
Page 1 of 3
2.
- Use SUMIF to sum salary for all employees in the West Region
- Use AVERGEIF to find the average salary of employees in the Northeast Region:
- What is the median salary of the entire employee population?
- In What region is the person with the lowest salary?
- Use COUNTA to see how many emails are non-blank
- Use COUNTBLANK to show how many emails are blank
- Use Column T to display TRUE for all employees born after “1/1/1985” and work in the company for more than 8 years (Use age in company column). Otherwise display “FALSE” for that employee record
Charts (use the columns W and greater next to the answers from the first part of this assignment to show the data and charts)
- 3D Clustered Column Chart: Create showing regions vs. average salary, high salary. It makes things easier if you create a column with each region and use formulas to calculate for each region in a separate column and put into a work area to feed the chart. Change chart tile to Salary by Region. Set vertical axis title to be Salary.
- 3D Pie Chart: Create showing regions and number of employees per region. Make the title to be Employees per Region. Pick a style that shows the percentages of each pie slice. Set up a separate work area where you have a column with each region and in the column next to it, put in the number of employees per region. This will help you to create the pie chart properly
- Line chart: Show line chart with each region and the highest salary per region. You may find it easier to copy the values to a work area to generate the chart. Title of chart is High salary. Vertical axis title is Salary
Formulas spreadsheet
Enter a formula in the cells indicated – yellow background – using the formulas listed below.
- Amortized Loan Payment: 𝐴 = 𝑃 𝑟(1+𝑟)𝑛 (1+𝑟)𝑛−1
- Area of a Trapezoid: A = 1 h(b1 +b2) 2
- Volume of a Sphere: 𝑉 = 4 𝜋𝑟3 3
- Velocity Final: 𝑣𝑓 = √2 × 𝑎 × 𝑑 + 𝑣𝑖2
- EffectiveRate:(1+ 𝑟 )𝑛 − 1
References:
𝑛
Page 2 of 3
- Chapter 13 of the Fluency7 textbook
- Recitations and Lectures