1、计算与管理表格数据第5章办公自动化全能一本通目录 CONTENT5.1计算员工工资表5.2统计管理产品入库明细表5.4高手竞技场5.3新手加油站3 第 5 章 计算与管理表格数据5.1.1 使用公式计算数据1.输入公式公式用于简单数据的计算，通过在单元格中输入实现计算功能。如下图所示，在工作表中选择F5 单元格，先输入等号“=”，然后输入公式的其他部分“C5+D5+E5”。按【Ctrl+Enter】组合键，在F5 单元格中将显示公式的计算结果，在编辑栏中将显示公式的表达式。在Excel中，输入计算公式进行数据计算时需要遵循特定的次序或语法：最前面是等号“=”，然后才是计算公式。公式中可以包含运
2、算符、常量数值、单元格引用、单元格区域引用和函数等。本例中的公式“=C5+D5+E5”即表示C5、D5、E5单元格中的数据相加得到的和。4 第 5 章 计算与管理表格数据5.1.1 使用公式计算数据2.复制与填充公式复制与填充公式是快速计算同类数据的最佳方法，因为在复制填充公式的过程中，Excel会自动改变引用单元格的地址，从而避免手动输入公式的麻烦，提高工作效率。选择含有公式的单元格，按【Ctrl+C】组合键复制公式，选择目标单元格，然后按【Ctrl+V】组合键粘贴公式，将在目标 单元格中计算出结果，公式的引用单元格将自动发生变化。复制公式5 第 5 章 计算与管理表格数据5.1.1 使用公
3、式计算数据2.复制与填充公式选择含有公式的单元格，将鼠标指针移动到该单元格右下角的控制柄上，当鼠标指针变成十字形状时，按住鼠标左键不放，将其拖动到目标单元格。双方鼠标后即可在拖动的单元格区域中填充公式。输入类似“=G5+H5+I5”的公式，可先输入等号“=”，然后单击鼠标选择G5单元格引用其中的数据，然后输入“+”，再单击鼠标选择H5单元格，最后输入“+”，单击I5单元格。填充公式计算数值6 第 5 章 计算与管理表格数据5.1.1 使用公式计算数据3.在公式中应用绝对引用在Excel 中进行数据计算时，经常需要引用单元格中的数据，以便提高计算数据的效率。其中绝对引用指把公式复制或移动到新位置
4、后，公式中的单元格地址保持不变。应用绝对引用时，引用单元格的列标和行号之前分别加入了符号“$”。如图所示，E2单元格为省平均工资，在C4单元格中输入“$E$2*8%”，绝对引用E2单元格的数据计算结果。然后填充公式至C19，此时选中C4:C19 区域任意单元格，公式栏都显示为=$E$2*8%”。表示绝对引用E2单元格的数据，即任意员工的保险金额都是E2单元格的“省平均工资”金额乘以缴纳比例8%。在公式中应用绝对引用7 第 5 章 计算与管理表格数据5.1.1 使用公式计算数据在编辑表格数据时，如果需要在一张工作表中输入另一张工作表中的相同数据，单纯的输入显得麻烦，此时可以将一张工作表中的数据单
5、元格调用到另一张工作表中，即引用工作表中的单元格。除了直接输入公式引用，还可结合鼠标实现。如图所示首先在目标单元格中输入“=”，然后单击被调用的工作表标签，切换到该工作表后选择引用的数据所在的单元格。4.引用不同工作表中的单元格在目标单元格中输入等号STEP 1鼠标选择引用的工作表和单元格STEP 28 第 5 章 计算与管理表格数据5.1.1 使用公式计算数据 本例中，从编辑栏显示公式为“=代扣社保和公积金!G4”，可知引用不同工作表单元格的方法是输入“=工作表名称!单元格地址”。对不同工作簿中的单元格进行引用，则可使用“=工作簿存储地址工作簿名称工作表名称!单元格地址”的方法进行引用。同样
6、也可结合鼠标实现，打开该工作簿，在其中选择引用的工作表和单元格即可。引用单元格数据结果STEP 39 第 5 章 计算与管理表格数据5.1.2 使用函数计算数据使用函数计算数据时，若对所使用的函数比较熟悉，可直接在编辑栏输入函数，方法与输入公式完全相同。但Excel 提供的函数类型很多，要记住所有的函数名和参数并不容易，可通过“插入函数”对话框选择并插入所需函数。如通过“插入函数”对话框插入NOW 函数获取制表时间。1.输入函数选择函数STEP 110 第 5 章 计算与管理表格数据5.1.2 使用函数计算数据插入函数STEP 2获取当前日期和时间STEP 3NOW函数的使用：NOW函数用于返
7、回计算机系统内部时钟的当前日期和时间，语法结构为：NOW()，没有参数。NOW函数返回的日期和时间会自动更新，如第一次制表日期为“27号”，当第一次没有完成时，第二天继续制表，制表日期将自动更新为“28号”。11 第 5 章 计算与管理表格数据5.1.2 使用函数计算数据2.自动求和Excel 2016 中的自动求和功能集合了一些常用的函数，如求和函数、平均值函数等，通过它可快速插入相应函数计算相关数值。计算实发工资的“总计”金额时输入“=SUM(K5:K20)”表示计算K5:K20单元格区域中数值相加的和，即在SUM函数后输入单元格区域可计算该区域数值的和。12 第 5 章 计算与管理表格数
8、据5.1.2 使用函数计算数据3.编辑函数插入函数时，难免输入错误的单元格引用区域，此时就需要编辑函数，将其修改为正确的引用参数，从而计算出正确的数值。如下图所示首先通过自动求和功能插入SUM 函数，然后修改其中的引用参数，计算代扣个税的“总计”金额。13 第 5 章 计算与管理表格数据5.1.2 使用函数计算数据4.嵌套函数除了使用单个函数进行简单计算外，在Excel 中还可使用函数嵌套进行复杂的数据运算。函数嵌套的方法是将某一个函数或公式作为另一个函数的参数来使用。使用逻辑函数“IF”并结合嵌套函数计算“代扣个税”。14 第 5 章 计算与管理表格数据5.1.2 使用函数计算数据4.嵌套函
9、数 IF函数的语法结构为：IF（l og i c al _test,value_if_true,value_if_false），可理解为“IF（条件，真值，假值）”，表示当“条件”成立时，返回“真值”，否则返回“假值”。IF嵌套函数为“=IF(K5-35000,0,IF(K5-35003500,0.03*(K5-3500)-0,IF(K5-35004500,0.1*(K5-3500)-105,IF(K5-35009000,0.2*(K5-3500)-555,IF(K5-350035000,0.25*(K5-3500)-1005)”，看似复杂，其实很容易理解，它与7级税率表相结合。个人所得税，即
10、M5:M20单元格区域中的数值等于“全月应纳所得税额税率-速算扣除数”，这里用M5:M20单元格区域的实发工资数值减去税收起征点3500得到全月应纳所得税额，判断其属于哪个缴纳等级，然后乘以对应的税率，再减去速算扣除数，得到的个人所得税数值返回M5:M20单元格区域。比如当“K5-3500=4200,N5:N20,5500)”，表示统计N5:N20 单元格区域（税后工资）中工资大于等于4 200 且小于5 500 的员工人数。36 第 5 章 计算与管理表格数据5.3 新手加油站6.按字体颜色或单元格颜色筛选如果在表格中设置了单元格或字体颜色，通过单元格或字体颜色可快速筛选数据，单击字体颜色或
11、填充过单元格颜色字段右侧的下拉按钮，在打开的下拉列表中“按颜色筛选”选项变为可用，选择对应选项即可筛选出所需数据。如将实例中“产品入库明细表”中金额大于10 000 的数值设置了突出显示规则，之后便可按照颜色筛选出金额大于10 000 元的入库产品信息。目录 CONTENT5.1计算员工工资表5.2统计管理产品入库明细表5.4高手竞技场5.3新手加油站38 第 5 章 计算与管理表格数据5.4 高手竞技场1.计算学生成绩打开素材文件期中考试成绩表.xlsx，计算学生成绩，要求如下。01OPTION02OPTION03OPTION04OPTION使用SUM函数计算学生成绩的总分。使用AVERAG
12、E函数计算学生总成绩的平均分。使用MAX/MIN函数计算各科最高成绩和各科最低成绩。使用 COUNTIFS 函数统计平均分在 6070、7080、8085、8590、90100的学生人数。39 第 5 章 计算与管理表格数据5.4 高手竞技场2.管理区域销售汇总表打开素材文件“区域销售汇总表.xlsx”，对数据进行排序和汇总，要求如下。1234输入公式计算销售额。以“销售店”为主要关键字降序排列，以“销售数量”为次要关键字升序排列。以“销售店”为分类字段，求和汇总“销售数量”和“销售额”数据。隐藏“西门店”的具体销售数据40 第 5 章 计算与管理表格数据5.4 高手竞技场学习进步！办公自动化全能一本通海量图书方便查询免费申请样书下载配套资源优惠购书成为作者更多样书申请和资源下载需求，请登录人邮教育社区（)囊括各大品类，您想要的应有尽有教师免费申请样书，我们将安排快递迅速送达教学视频、PPT课件、教学案例、习题答案、模拟试卷等丰富资源免费下载教师可以申请最低折扣学生直接优惠购买图书欢迎写文章投稿，我们强大的编辑团队将为您提供专业和高效的编辑出版服务
