MS Office高级应用与设计课件 第9章 数据计算

第9章

数据计算第三篇Excel2016应用01知识目标掌握公式的基本概念与语法，理解公式的核心构成要素：等号、运算符、常量、函数与单元格引用；熟悉算术、比较等不同类型运算符的优先级规则。02技能目标掌握求和、均值等统计函数，以及IF、VLOOKUP等核心逻辑与查询函数；运用LEFT/RIGHT等文本函数处理数据，解决各类办公场景计算需求。03素质目标在数据处理与操作中，告别繁琐的手工计算，利用公式和函数实现数据的自动化运算与分析，大幅减少人为错误，让数据分析工作更高效、结果更精准可靠。核心宗旨：熟练运用常用函数解决实际问题，提升数据处理效率与准确性。本章学习目标1公式应用公式是对工作表中的数据进行计算的等式。它以等号“=”作为开始，由一系列元素组成，告诉Excel如何处理数据，是实现自动化计算与分析的核心工具。等号（=）：起点公式必不可少的开头，输入“=”后，Excel才会将后续内容识别为计算指令而非普通文本。运算符：计算规则指定执行的计算类型，如算术运算符（+、-、*、/）、比较运算符（=、>、<）等。常量：固定数值直接输入公式的数字或文本值，在计算过程中不会发生变化，例如“100”、“折扣系数”等。单元格引用：数据来源指向工作表中其他单元格或区域的地址（如A1、B2:C5），公式会自动获取并使用这些单元格中的实时数据进行计算。函数：预设的复杂公式Excel内置的预定义公式，用于执行特定的复杂计算任务，如求和(SUM)、平均值(AVERAGE)等，能大幅简化操作。（1）认识公式——Excel的“计算引擎”01算术运算符：基础计算核心包含%、^、*、/、+、-，用于处理数值的常规数学运算，是公式中最常用的运算符类型。02文本运算符：内容拼接纽带仅包含和号“&”，用于将多个文本字符串、数字或单元格内容连接成一个整体，实现信息的组合展示。03比较运算符使用=、>、<、>=、<=、<>比较两个值，结果返回TRUE或FALSE，常用于条件判断。04逻辑运算符包含NOT、AND、OR，用于对多个逻辑条件进行复合判断，构建复杂的决策逻辑。核心法则优先级决定运算顺序，如无括号，严格遵循系统预设的先后次序执行。常规顺序：先乘除后加减公式：=2+3*4

结果：14

（先计算3*4，再加2）括号干预：改变优先级公式：=(2+3)*4

结果：20

（先计算括号内2+3，再乘4）（2）运算符引用运算符是Excel特有的工具，主要应用于函数参数中，用于引用工作表的单元格或单元格区域。区域运算符(冒号:)引用两个单元格之间的所有连续单元格，是最常用的引用方式。示例：SUM(B1:D5)（计算B1到D5矩形区域的和）联合运算符(逗号,)引用多个不相邻、相互独立的单元格或区域，将它们合并计算。示例：SUM(B1,D5)（同时计算B1单元格和D5单元格的和）交叉运算符(空格)引用多个区域之间的重叠部分，只有同时属于所有区域的单元格才会被选中。示例：SUM(A5:B6B2:D4)（仅计算两个区域重叠的B5:B6）课堂思考：请辨析公式SUM(A1:C3,B2:D4)与SUM(A1:C3B2:D4)的计算结果有何本质不同？试着在Excel中输入公式验证你的猜想。（3）引用运算符单元格引用作用：精准定位并引用工作表中的单个单元格，是最基础的引用形式。示例：A1单元格区域引用作用：引用一个连续的矩形范围内的所有单元格，常用于批量数据计算与分析。示例：A1:B5整列引用作用：引用工作表中某一列的全部单元格，适用于对整列数据进行统一处理。示例：A:A整行引用作用：引用工作表中某一行的全部单元格，可用于处理横向的整行数据信息。示例：1:1工作表引用作用：在同一工作簿内，跨不同工作表引用数据，实现数据的跨表关联与计算。示例：Sheet2!B3工作簿引用作用：突破单个文件限制，引用其他外部Excel工作簿中的数据，整合多源信息。示例：[Sales.xlsx]Sheet1!B2（4）单元格引用范围图示直观展示了Excel中三种引用方式在公式复制时，单元格地址随位置变化的不同逻辑，是理解引用锁定机制的核心参考。01相对引用(A1)引用会相对于公式位置的变化而自动调整单元格地址。适合同类数据的批量计算，是最常用的引用方式。02绝对引用($A$1)用$符号锁定单元格地址，无论公式复制到哪里，引用都保持不变。常用于引用固定的常量，如税率、单价等。03混合引用($A1/A$1)只锁定行或只锁定列，兼顾相对与绝对的特性。常用于制作复杂的数据表格，如九九乘法表、多维数据汇总等场景。💡效率秘籍：F4键在编辑公式时，选中单元格引用，反复按F4键，可在相对、绝对、行锁定、列锁定四种模式间一键快速切换，极大提升操作效率。（5）单元格引用类型01.输入公式：标准化的四步流程先选中目标单元格，输入等号“=”作为公式起始，随后输入运算内容或引用单元格，最后按下Enter键确认执行，完成公式录入。02.修改公式：灵活的编辑方式支持两种便捷修改方式：一是双击包含公式的单元格直接在编辑栏修改内容；二是点击单元格后，在上方的公式编辑栏中进行精准调整。03.复制与填充：高效复用公式选中含公式的单元格，将鼠标移至右下角直至指针变为黑色十字形（填充柄），按住左键向下或向右拖拽，即可批量复制并自动调整单元格引用。💡效率技巧：智能填充无需手动拖拽，直接双击单元格右下角的填充柄，Excel会智能识别数据列的长度，自动将公式快速填充到数据末尾，大幅提升操作效率。（6）公式编辑2函数应用01核心定义函数是Excel预先定义好的内置公式，就像一个个封装好的“小程序”，能够帮我们快速执行特定的、复杂的计算任务，是数据处理的核心工具。02标准格式结构为“函数名(参数1,参数2,...)”。函数名（如SUM）指定操作，参数是计算依据，括号必须成对出现，缺一不可。03核心优势相比手动输入公式，函数更高效、更准确，能大幅减少输入错误；同时它功能强大，可轻松完成手动难以实现的复杂数据分析与处理。04丰富分类Excel提供了庞大的函数库，涵盖数学与三角、统计、文本处理、日期时间、逻辑判断以及查找引用等类别，满足各类数据场景需求。（1）认识函数——Excel的“超级公式”01手动输入：高效直达适合熟练掌握函数的用户。直接在单元格输入“=”和函数名首字母，Excel将自动弹出智能提示列表，快速选择即可完成输入，是效率最高的方式。02插入函数对话框：通用向导这是最通用、最推荐初学者的方法。点击编辑栏左侧的fx按钮，在对话框中可按描述搜索或按类别筛选函数，界面引导清晰，参数填写不易出错。03自动求和按钮：常用捷径在“开始”选项卡“编辑”组中找到“∑”按钮。点击可快速插入求和函数，下拉菜单还包含平均值、计数、最大值等高频函数，是处理日常数据的首选捷径。04公式选项卡：分类检索切换到“公式”选项卡，利用“函数库”组按逻辑、文本、财务等类别精准查找。此方法适合需要探索特定领域函数，或对函数类别有明确需求的进阶场景。（2）插入函数的方法IF函数是逻辑判断的核心，能根据指定条件返回不同结果，是处理成绩、状态等场景的基础工具。1.SUM求和函数语法:=SUM(A1:A10)，用于快速计算区域内所有数值的总和，是最基础的统计函数。2.AVERAGE平均值语法:=AVERAGE(A1:A10)，计算选中区域内所有数值的算术平均数，常用于成绩、数据均值分析。3.MAX/MIN极值函数语法:=MAX(A1:A10)/=MIN(A1:A10)，快速定位数据中的最大值与最小值，用于筛选极端数据。4.COUNT/COUNTA计数COUNT统计数字单元格，COUNTA统计非空单元格。前者用于数据量统计，后者用于记录数统计。5.IF函数进阶：嵌套使用实现多条件判断通过嵌套IF函数，可以处理更复杂的分级场景。例如判断成绩等级：=IF(C2>=90,"优秀",IF(C2>=60,"及格","不及格"))（3）常用函数6.SUMIF：条件求和函数语法：=SUMIF(B:B,"苹果",C:C)

功能：对满足单个条件的单元格进行求和，是处理带条件统计数据的基础工具，能快速计算特定类别数据的总和。7.COUNTIF：条件计数函数语法：=COUNTIF(C:C,">90")

功能：统计指定区域内满足单个条件的单元格个数，常用于考试成绩分段统计、数据频次分析与筛选统计场景。8.VLOOKUP：垂直查询核心函数语法：=VLOOKUP(查找值,查找区域,返回列数,匹配类型)

功能：在表格首列查找指定值，并返回同一行中指定列的数据，是跨表数据关联与信息查询的最常用函数。01.查找值(Value)即“你要找什么”，可以是具体的数值、文本，或者指向具体内容的单元格引用（如示例中的E2）。02.查找区域(Table)即“去哪里找”，必须包含查找值和要返回的值，且查找值必须位于该区域的第一列。03.返回列数(Col_num)找到匹配值后，需要返回查找区域中第几列的数据。例如参数为“2”，就表示返回该区域第二列的内容。04.匹配类型(Match)FALSE为精确匹配，TRUE为模糊匹配。在日常数据查询工作中，强烈建议始终使用FALSE以确保查询结果的准确性。（3）常用函数通过直观的表格演示，可以清晰看到LEFT、RIGHT、MID函数如何精准地从混合字符串中分离出我们需要的特定信息，是处理非标准化数据的高效工具。9.LEFT函数：从左截取语法：=LEFT(文本,提取个数)。示例：=LEFT(A1,3)。用于从文本字符串的最左侧开始，提取指定数量的字符，适用于固定前缀信息的提取。10.RIGHT函数：从右截取语法：=RIGHT(文本,提取个数)。示例：=RIGHT(A1,2)。用于从文本字符串的最右侧开始，提取指定数量的字符，适合提取后缀、尾号等信息。11.MID函数：中间截取语法：=MID(文本,起始位置,提取个数)。示例：=MID(A1,2,4)。从字符串的任意指定位置开始提取，是功能最灵活的文本截取函数。从身份证号提取出生年月日从地址串中分离电话号码从产品编码中提取分类码（3）常用函数公式是基础以等号“=”作为所有计算的起点，规范的输入格式是确保Excel正确解析指令的首要前提。引