第1章 Excel公式应用与数据处理

第1章Excel公式应用与数据处理东北财经大学——数据处理与程序设计数据处理与程序设计——东北财经大学数据表格处理软件-Excel2010MicrosoftExcel是非常强大、灵活、高效的电子表格程序，对于从事统计、财务、会计、金融和贸易工作的人，经常会面对名目繁多的表格以及烦琐的数据计算，用Excel电子表格程序处理数据和表格就变得既容易又方便实用，还可以把一大批数据变成漂亮的图表。Excel被专业人士认为是Office中最成功的办公自动化软件之一。Excel具有强大的运算与分析能力。从Excel2007开始，改进的功能区使操作更直观、更快捷，实现了质的飞跃。在Excel2010中还可以使用SQL语句，能灵活地对数据进行整理、计算、汇总、查询、分析等处理，尤其在面对大数据量工作表的时候，SQL语言能够发挥其更大的威力，快速提高办公效率。Excel2010，可以通过比以往更多的方法分析、管理和共享信息，从而做出更好、更明智的决策。全新的分析和可视化工具可跟踪和突出显示重要的数据趋势。可以在移动办公时从几乎所有Web浏览器或Smartphone访问重要数据。甚至可以将文件上载到网站并与其他人同时在线协作。无论是要生成财务报表还是管理个人支出，使用Excel2010都能够更高效、更灵活地实现目标。数据处理与程序设计——东北财经大学数据处理与程序设计——东北财经大学完善工作薄:账本1.xlsx要求：1、序号、日期自动填充英文’3/12、所有数据居中3、将此工作表命名为：流水问题1：键入enter时，移到右单元格？选项—高级—编辑选项问题2：假设日期列为第一季度、第二季度。。。如何使用自定义序列进行智能填充？选项—高级—常规—编辑自定义列表回车问题3：对支出超过200元以上的用红色斜体字表示选中—条件格式—新建规则—仅为包含以下内容的单元格设置格式数据处理与程序设计——东北财经大学数据处理与程序设计——东北财经大学问题4：支出类别用下拉列表选择数据处理与程序设计——东北财经大学选中单元格，数据--数据有效性--设置--序列（输入时英文且逗号隔开）东北财经大学管科学院东北财经大学管科学院第一节公式定义与运算符目录第二节单元格引用第三节Excel公式格式数据处理与程序设计——东北财经大学第一节公式定义与运算符第一节公式定义与运算符数据处理与程序设计——东北财经大学公式定义公式定义公式是指使用运算符和函数，对工作表数据以及普通常量进行运算的方程式。它的特征是以“=”开头，由常量、单元格引用、函数与运算符组成。1.公式的输入公式是在工作表中对数据进行分析和运算的等式，它的特征是以“=”开头，由常量、单元格引用、函数与运算符组成。公式一般可以直接输入，其操作方法为：先选取要输入公式的单元格，然后输入计算公式，既可以直接在单元格中输入，也可以在编辑栏中输入，最后按回车键或鼠标单击编辑栏中的“√”按钮结束公式的输入。2.公式的修改在输入公式后，如果需要对公式进行修改，可以有两种方法。第一，可以通过双击公式所在的单元格，将光标移动到需要修改的对象的位置，删除不需要的内容，或输入补充的内容；第二，选择公式所在的单元格，在编辑栏中对公式进行修改。数据处理与程序设计——东北财经大学运算符在Excel2010中，运算符是指在公式中用于进行计算的加、减、乘、除，以及其他运算符等符号。运算符可以分为算术运算符、比较运算符、文本运算符和引用运算符。1.运算符分类（1）算术运算符算术运算符作用示例+（加号）计算两个数值的和3+3=6-（减号，负号）计算两个数值的差，或表示负值8-5=3*（乘号）计算两个数值的积3*3=9/（除号）计算两个数值的商6/3=2%（百分比号）将数值转换成百分比格式（15+25）%=40%^（乘方）计算数值的乘方3^2=9数据处理与程序设计——东北财经大学运算符（2）比较运算符比较运算符作用示例=（等号）比较两个数值是否相等A1=5（大于号）比较第一个数值是否大于第二个数值A16（小于号）比较第一个数值是否小于第二个数值A18=（大于等于号）比较第一个数值是否大于等于第二个数值A1=5=（小于等于号）比较第一个数值是否小于等于第二个数值A1=4（不等于号）比较两个数值是否不相等A1B3数据处理与程序设计——东北财经大学运算符（3）文本连接运算符“&”例如：=A5&B5&C5（4）引用运算符三个：冒号“:”逗号“,”空格引用运算符作用示例：（冒号）引用两个单元格地址间所有的单元格A1：B5，（逗号）引用多个不连续的单元格或区域A1，B2：B4（空格）引用两个区域间的共有单元格区域SUM(A1:B4B2:D6)（运算符练习.xlsx）数据处理与程序设计——东北财经大学运算符2.运算符的优先级从左至右，从上至下：（冒号），（逗号）（空格）-（负号）%（百分比号）^（乘方）*（乘号）/（除号）+（加号）-（减号）&（文本连接符）=（等号）（大于号）（小于号）=（大于等于号）=（小于等于号）（不等于号）各类别运算符优先级引用运算符、算术运算符、文本连接运算符、比较运算符数据处理与程序设计——东北财经大学第二节单元格引用第二节单元格引用数据处理与程序设计——东北财经大学单元格的引用在使用公式进行计算时，除了直接使用常量数据以外，还可以引用单元格数据。引用单元格是通过特定的单元格符号来标识工作表上的单元格或者单元格区域，指明公式中所使用的数据位置。通过单元格的引用，可以在公式中使用工作表中不同单元格的数据，或者在多个公式中使用同一单元格的数据。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格，甚至其他应用程序中的数据。单元格的引用分为相对引用、绝对引用和混合引用。相对引用直接写、公式常见方式绝对引用“$”符号混合引用行、列有相对有绝对数据处理与程序设计——东北财经大学单元格引用1.相对引用单元格的相对引用是指在生成公式时，对单元格或者单元格区域的引用基于它们与公式单元格的相对位置关系。使用相对引用后，系统将会记住建立公式的目标单元格和被引用的单元格的相对位置关系，在粘贴这个公式时，新的公式单元格和被引用的单元格仍然保持这种相对位置。在公式的应用中，相对引用是用得比较多的引用方式。例如D4=C3+5，这里表示D4单元格内容为其左侧相邻列（C列与D列的相对位置关系）上侧相邻行（4行与3行的相对位置关系）单元格内的数值加上5的结果。2.绝对引用单元格的绝对引用是指在生成公式时，对单元格或者单元格区域的引用基于它们与公式单元格的绝对位置。不论包含公式的单元格处在什么位置，公式中所引用的单元格的位置都不会发生改变。绝对引用时，在单元格地址行号和列标前都加上绝对引用符号“$”，如$C$3，表示单元格C3是绝对引用。数据处理与程序设计——东北财经大学单元格引用3.混合引用单元格的绝对引用是指在生成公式时，对行采用相对引用而对列采用绝对引用，或者是对列采取相对引用而对行采取绝对引用。$C3表示绝对引用列标，C$3表示绝对引用行号，当相对引用的公式发生位置变化时，绝对引用的行号或列标不变，但相对引用的行号或列标则发生变化。问题1：在运算符练习.xlsx中使得:D5为辽宁大连理工D6为江苏南京理工D7为四川成都理工正在编辑状态的单元格引用地址的相对引用、绝对引用和混合引用，可以采取反复按F4键的方法进行切换。数据处理与程序设计——东北财经大学第三节Excel公式格式第三节Excel公式格式数据处理与程序设计——东北财经大学Excel公式格式同一个（当前）工作表公式格式的引用例：D4=B4+C4不同工作表公式格式的引用例：D4=sheet2！B4+sheet3！C4不同工作簿公式格式的引用例：D4=[book2]sheet2！B4+[book3]sheet3！C4不同磁盘的不同工作簿公式格式的引用例：D4=’d:\abc\[book2]sheet2’！B4+’e:\xxx\[book3]sheet3’！C4销售表1.xlsx销售表2.xlsx练习1：完善销售表1.XLSX中的”汇总1”工作表，其内容为“手机”和“打印机”销售总和。练习2：完善销售表2.XLSX中的”汇总2”工作表，其内容为“手机”、“打印机”、“计算机”的销售总和。数据处理与程序设计——东北财经大学数据处理与程序设计——东北财经大学公式中的名称Excel2010中可以使用“定义名称”的方式简化公式的计算。用户可以自定义复杂运算的名称进行保存，以用于后期的使用。1.选择“定义名称”选项“公式”→“定义名称”→“新建名称”，在“名称”框里输入一个公式名称“总分”，然后在“引用位置”里编写公式内容如“=sum(Sheet2!D2:F2)”，最后点击“确定”按钮，生成一个自定义的公式名称“总分”。2.将名称用于公式选择好目标单元格后点击“用于公式”，即可看到自定义的“总分”公式，点击即可将自定义公式“总分”的计算内容填入编辑栏，点击输入确认按钮即可得到公式计算结果。可以通过自动填充功能将公式计算方式应用于其他记录。3.名称管理在完成“定义名称”之后，可以点击“名称管理器”，使用“名称管理器”将自定义的公式名称进行管理，包括新建、编辑和删除名称。练习3：打开excel操作002.XLSX，定义名称“总分1”并应用于总分列。EXCEL操作002.xlsx