Excel2010(等级考试二级)课件

Office高级应用选修班2023.9目录前言课程目标及评价说明第1章计算机基础知识（4学时）第2章Word2010

（10学时）第3章Excel2010

（10学时）第4章PowerPoint2010

（4学时）考试指导第5章课内（指导）课外耕耘二、课程教育评价及期末综评30%平时作业平均成绩20%课程学习表现100%期末总评=50%期末考试++前言：课程目标及评价说明一、课程目标1.具体目标参见各章教学课件：“学习目标〞要求2.平时上机任务：每章习题和教师自主按排3.期末考试：考试题型及范围为“计算机根底复习系统〞最终目标：通过MSOffice高级应用全国二级考试第3章通过Excel创立并处理

电子表格课程简介通过电子表格软件进行数据的管理与分析与已成为人们当前学习和工作的必备技能之一。Excel是一款目前相当流行、应用广泛的电子表格软件，它是由微软开发、属于MSOFFICE套装办公软件中的主要组件之一。本教材以Excel2023为蓝本，学习Excel的重要功能及应用。课程学习内容创立根本电子表格，在表格中输入各类数据。对数据及表格结构进行格式化，使之更加美观。对工作表及工作簿进行各类操作。在工作表中使用公式和函数简化计算。对数据进行各种汇总、统计分析和处理运用图表对数据进行分析。通过宏快速重复执行多个任务在不同的人员和程序间共享数据。第3章通过Excel创立并处理电子表格3.1Excel制表基础3.2工作簿与多工作表操作3.3Excel公式和函数3.4在

Excel中创建图表3.5Excel数据分析与处理3.6Excel与其他程序的协同与共享本节案例背景：Excel最根本的功能就是制作表格，在表格中记录相关数据，以便日常生活和工作中信息的记录、查询与管理。书娟是一位年青的女性，她刚刚结婚，要负责家庭财政收支；本节将通过Excel制作家庭收支流水账这一简单案例，帮助书娟实现以下根本目标：了解Excel工作环境中的常用术语掌握在Excel中输入数据的各类方法掌握数据及表格结构的格式化方法掌握打印输出表格的方法。了解公式的简单应用3.1Excel制表根底3.1.1在表格中输入和编辑数据1.Excel表格术语通过桌面快捷方式、“开始〞菜单等途径，均可启动Excel。Excel的窗口界面有别于其他程序〔如图3.2所示〕，必须掌握一些Excel特有的常用术语含义及其作用：工作簿与工作表工作表标签行号和列标单元格、单元格地址与活动单元格名称框编辑栏3.1Excel制表根底3.1.1在表格中输入和编辑数据图3.2Excel2023的窗口界面列标活动单元格名称框工作表标签编辑栏行号3.1Excel制表根底3.1.1在表格中输入和编辑数据输入和编辑数据是制作一张表格的起点和根底，在Excel中，可以利用多种方法到达快速输入数据的目的。2.直接输入各种类型的数据在Excel中，可以输入数值、文本、日期等各种类型的数据。输入数据的根本方法：在需要输入数据的单元格中单击鼠标，输入数据，然后按回车键Enter、Tab或方向键。3.1Excel制表根底3.1.1在表格中输入和编辑数据按照图3.3所示为创立一个家庭收支情况表输入根底数据：图3.3在表格中输入根底数据3.1Excel制表根底3.1.1在表格中输入和编辑数据具体操作步骤：启动Excel，窗口中自动翻开一个空白工作簿。在A1单元格中单击鼠标，输入标题文字“家庭收支流水账〞按回车键Enter，光标自动向下跳到B1单元格。从B1单元格从左向右依次输入表格的列标题：序号、发生日期、摘要、收入金额、支出金额、余额。在“序号〞所在A列的A2单元格中输入第一个序号“’01〞。注意：在数字前需要先行输入一个西文单撇号“’〞，将其指定为文本格式，才能正确显示出数字前面的“0〞。3.1Excel制表根底3.1.1在表格中输入和编辑数据在“发生日期〞所在B列的B2单元格中输入第一个日期“2/1〞，以斜杠“/〞分隔月日，按回车键Enter确认，单元格中自动显示“2月1日〞。提示：Excel提供多种日期显示格式，其具体的设置方式可参见后面的设置数据格式中的介绍。在“余额〞所在F列的F2单元格中输入第一个计算公式：“=D3-E3〞(余额＝收入-支出)，按回车键Enter确认。依次输入摘要及收入金额。对工作簿进行保存。Excel2023版本默认的文件名后缀为.xlsx，还可以将其保存为其他类型，如存为Excel97-2003工作簿格式〔.xls〕等。3.1Excel制表根底3.1.1在表格中输入和编辑数据3.向单元格中自动填充数据序列填充是Excel提供的最常用的快速输入技术之一。1〕序列填充的根本方法拖动填充柄使用“填充〞命令利用鼠标右键快捷菜单3.1Excel制表根底3.1.1在表格中输入和编辑数据2〕可以填充的内置序列数字序列，如1、2、3、……；2、4、6、……日期序列，如2023年、2023年、2023年……；1月、2月、3月……；1日、2日、3日……文本序列，如01、02、03、……；一、二、三……其他Excel内置序列：如英文日期JAN、FEB、MAR……；星期序列星期日、星期一、星期二……，子、丑、寅、卯……3.1Excel制表根底3.1.1在表格中输入和编辑数据3〕填充公式首先在第一个单元格中输入某个公式，然后拖动该单元格的填充柄，即可填充公式本身而不仅仅是填充公式计算结果。4〕自定义常用序列如“第一小组、第二小组、第三小组、第四小组〞基于已有工程列表的自定义填充序列要点：首先在工作表的单元格依次输入一个序列的每个工程值方法：“文件〞选项卡→“选项〞→“高级〞→“常规〞→“编辑自定义列表〞→“自定义序列〞对话框3.1Excel制表根底3.1.1在表格中输入和编辑数据删除自定义序列

在“自定义序列〞对话框的左侧列表中选择需要删除的序列，单击右侧的“删除〞按钮。直接定义新工程列表依次单击“文件〞选项卡→“选项〞→“高级〞，在“常规〞区中单击“编辑自定义列表〞按钮，在“自定义序列〞对话框中单击“新序列〞，然后在右侧的“输入序列〞文本框中依次输入序列的各个条目：从第一个条目开始输入，每个条目后按Enter键确认。3.1Excel制表根底3.1.1在表格中输入和编辑数据4〕完成案例中序号和日期的填充翻开案例工作簿文档“3.1家庭收支流水账〔1〕〞，完成日期和序号的填充。小技巧：输入序列的第一个工程后，用鼠标直接双击填充柄，序号将自动向下填充到相邻列最后一个数据所在的行。3.1Excel制表根底3.1.1在表格中输入和编辑数据4.控制数据的有效性数据有效性，用于定义可以在单元格中输入或应该在单元格中输入的数据类型、范围、格式等。可以通过配置数据有效性以防止输入无效数据，或者在录入无效数据时自动发出警告。数据有效性可以实现的根本功能：将数据输入限制为指定序列的值，以实现大量数据的快速输入。将数据输入限制为指定的数值范围，如指定最大值最小值、指定整数、指定小数、限制为某时段内的日期、限制为某时段内的时间等。将数据输入限制为指定长度的文本，如身份证号只能是18位文本。限制重复数据的出现，如学生的学号不能相同。3.1Excel制表根底3.1.1在表格中输入和编辑数据设置数据有效性的根本方法：在“数据〞选项卡上的“数据工具〞组中单击“数据有效性〞按钮，从随后弹出的“数据有效性〞对话框中指定各种数据有效性控制条件即可。案例：翻开案例工作簿文档“3.1家庭收支流水账〔2〕〞，对其中的摘要列设置数据有效性，目的是可以通过下拉列表方式选择摘要内容，摘要栏中允许输入的工程包括：工资奖金、房租、水电气、日常生活费、学习用品、服装鞋帽。3.1Excel制表根底3.1.1在表格中输入和编辑数据5.对数据进行编辑修改根本方法：双击单元格进入编辑状态，进行修改；或者单击要修改的单元格，然后在编辑栏中进行修改。删除数据：按Delete/Del键；或者在“开始〞选项卡上的“编辑〞组中，单击“去除〞，可指定删除格式还是内容。继续对案例工作簿“3.1家庭收支流水账〔2〕〞进行修改：将“摘要〞改为“收支摘要〞二字；将F3单元格中的错误公式删除。3.1Excel制表根底3.1.2对表格进行根本整理和修饰为对让表格看起来更加漂亮，也为了增加易读性，需要对输入了数据的表格进行格式化。格式化内容包括：行列操作、字体设置、对齐方式、数字格式、单元格边框和底纹。1.选择单元格或区域在对表格进行修饰前，需要先选择单元格或单元格区域做为修饰对象。在Excel中，选择单元格或区域的方法多种多样，常用快捷方法见表3.1中所列。3.1Excel制表根底操

作常用快捷方法选择单元格用鼠标单击单元格选择整行单击行号选择一行；用鼠标在行号上拖动选择连续多行；按下Ctrl键单击行号选择不相邻多行选择整列单击列标选择一列；用鼠标在列标上拖动选择连续多列；按下Ctrl键单击列标选择不相邻多列选择一个区域在起始单元格中单击鼠标，按下左键不放拖动鼠标选择一个区域；按住Shift的同时按箭头键以扩展选定区域；单击该区域中的第一个单元格，然后在按住Shift的同时单击该区域中的最后一个单元格选择不相邻区域先选择一个单元格或区域，然后按下Ctrl键不放选择其他不相邻区域选择整个表格单击表格左上角的“全选”按钮

，或者在空白区域中按下Ctrl+A组合键选择有数据的区域按Ctrl+箭头键可移动光标到工作表中当前数据区域的边缘；按Shift+箭头键可将单元格的选定范围向指定方向扩大一个单元格；在数据区域中按下Ctrl+A或者Ctrl+Shift+*组合键，选择当前连续的数据区域；按Ctrl+Shift+箭头键可将单元格的选定范围扩展到活动单元格所在列或行中的最后一个非空单元格，或者如果下一个单元格为空，则将选定范围扩展到下一个非空单元格表3.1选择单元格及区域的方法3.1Excel制表根底3.1.2对表格进行根本整理和修饰2.行列操作调整行高、列宽插入行或列删除行或列移动行或列隐藏行或列其根本方法如表3.2中所列。行列操作可以通过鼠标，也可以通过菜单实现。提示：以上各项功能〔除移动行列外〕还可以通过鼠标右键快捷菜单实现：在单元格或行列上单击右键，从弹出的快捷菜单中选择相应的命令即可。3.1Excel制表根底行列操作基本方法调整行高用鼠标拖动行号的下边线；或者依次选择“开始”选项卡→“单元格”组中的“格式”下拉列表→“行高”命令，在对话框中输入精确值。调整列宽用鼠标拖动列标的右边线；或者依次选择“开始”选项卡→“单元格”组中的“格式”下拉列表→“列宽”命令，在对话框中输入精确值。隐藏行用鼠标拖动行号的下边线与上边线重合；或者或者依次选择“开始”选项卡→“单元格”组中的“格式”下拉列表→“隐藏和取消隐藏”→“隐藏行”命令隐藏列用鼠标拖动列标的右边线与左边线重合；或者依次选择“开始”选项卡→“单元格”组中的“格式”下拉列表→“隐藏和取消隐藏”→“隐藏列”命令插入行依次选择“开始”选项卡→“单元格”组中的“插入”下拉列表→“插入工作表行”命令，将在当前行上方插入一个空行插入列依次选择“开始”选项卡→“单元格”组中的“插入”下拉列表→“插入工作表列”命令，将在当前列左侧插入一个空行删除行或列选择要删除的行或列，在“开始”选项卡的“单元格”组中单击“删除”按钮移动行列选择要移动的行或列，将鼠标光标指向所选行或列的边线，当光标变为

状时，按下左键拖动鼠标即可实现行或列的移动。表3.2行列操作方法3.1Excel制表根底3.1.2对表格进行根本整理和修饰3.设置字体及对齐方式设置字体与字号：单击“文件〞选项卡上的“字体〞组中相应按钮。单击“数字〞右侧的对话框启动器，翻开的“设置单元格格式〞对话框的“字体〞选项卡，进行详细设置。设置对齐方式单击“文件〞选项卡上的“对齐方式〞组中相应按钮。单击“对齐方式〞右侧的对话框启动器，翻开“设置单元格格式〞对话框的“对齐〞选项卡，进行详细设置。3.1Excel制表根底3.1.2对表格进行根本整理和修饰4.设置数字格式数字格式是指表格中数字的外观形式。通常情况下，输入单元格中的数据是未经格式化的，尽管Excel会尽量将其显示为最接近的格式，但并不能满足所有需求。通常来说，需要对数据进行数字格式设置，这样不仅美观，而且更便于阅读，或者使其显示的精度更高。例如，当试图在单元格中输入一个人的18位身份证号时，你可能会发现直接输入一串数字后结果是不对的，这时就需要通过数字格式的设置才能正确显示。3.1Excel制表根底3.1.2对表格进行根本整理和修饰1〕可供选择的数字格式：常规：默认格式。数值货币会计专用日期时间百分比分数科学记数：用指数符号(E)显示数字:2.00E+05=200000文本：主要用于设置那些外表看来是数字，但实际是文本的数据。例如序号001、002，就需要设置为文本格式才能正确显示出前面的零。特殊：包括邮政编码、中文小写数字和中文大写数字。自定义3.1Excel制表根底3.1.2对表格进行根本整理和修饰2〕设置数字格式的根本方法通过“文件〞选项卡上“数字〞组中的相应按钮快速设置。单击“数字〞组右侧的对话框启动器，在“设置单元格格式〞对话框的“数字〞选项卡中，进行更加详细的设置或自定义格式。提示：如果一个单元格显示出一连串的##########标记，这通常意味着单元格宽度不够，无法显示全部数据长度，这时可以加宽该列或改变数字格式。3.1Excel制表根底3.1.2对表格进行根本整理和修饰5.添加边框和底纹默认情况下，工作表中的网格线只用于显示，不会被打印。改变边框和底纹的根本方法：在“文件〞选项卡上的“字体〞组中，单击“边框〞按钮，选择边框；单击“填充颜色〞按钮，设置背景底纹。“文件〞选项卡→“单元格〞组→“格式〞按钮→“设置单元格格式〞命令→“设置单元格格式〞对话框，→“边框〞选项卡或“填充〞选项卡。3.1Excel制表根底3.1.2对表格进行根本整理和修饰6.完成案例的根底格式设置①翻开案例工作簿文档“3.1家庭收支流水账〔3〕〞。②调整行高列宽③插入及隐藏行列④完成数据的输入⑤设置字体及对齐方式⑥设置数字格式⑦设置边框和底线3.1Excel制表根底3.1.3格式化工作表高级技巧除了手动进行各种格式化操作外，Excel还提供有各种自动格式化的高级功能，以方便大家快速进行格式化操作1.自动套用格式Excel本身提供大量预置好的表格格式，可自动实现包括字体大小、填充图案和对齐方式等单元格格式集合的应用，从而快速实现报表格式化。1〕指定单元格样式该功能只对某个指定的单元格设定预置格式“开始〞选项卡→“样式〞组→“单元格样式〞按钮→从翻开预置样式列表中选择某一个预定样式。自定义样式：单击样式列表下方的“新建单元格样式〞命令动手操作：翻开案例工作簿文档“3.1家庭收支流水账〔4〕〞，试为该表格的标题“家庭收支流水账〞所在的单元格套用不同的单元格样式。3.1Excel制表根底3.1.3格式化工作表高级技巧2〕套用表格格式自动套用表格格式，将把格式集合应用到整个数据区域。注意，自动套用格式只能应用于不含合并单元格的数据列表中。“开始〞选项卡→“样式〞组→“套用格式〞按钮→从预置格式列表中选择某一个预定样式。自定义快速格式：单击预置格式列表下方的“新建表样式〞命令。取消套用格式：“表格工具设计〞选项卡→“表格样式〞组右下角的“其他〞箭头→最下方的“去除〞命令。翻开案例工作簿文档“3.1家庭收支流水账〔4〕〞，试为该表格的数据区域B3:G19套用不同的表格样式，演示不同格式组合效果。3.1Excel制表根底3.1.3格式化工作表高级技巧2.设定与使用主题主题是一组格式集合，其中包括主题颜色、主题字体〔包括标题字体和正文字体〕和主题效果〔包括线条和填充效果〕等。通过应用文档主题，可以快速设定文档格式基调并使具看起来更加美观且专业。1〕使用主题“页面布局〞选项卡→“主题〞组→“主题〞按钮→选择主题类型。2〕自定义主题“页面布局〞选项卡→“主题〞组→“颜色〞按钮→自行设定颜色组合。“页面布局〞选项卡→“主题〞组→“字体〞按钮→自行设定字体组合。“页面布局〞选项卡→“主题〞组→“效果〞按钮→选择一组主题效果。保存自定义主题：“页面布局〞选项卡→“主题〞组→“主题〞按钮→从主题列表最下方选择“保存当前主题〞命令→输入主题名称。提示：新建主题将会显示在主题列表最上面的“自定义〞区域以供选用。3.1Excel制表根底3.1.3格式化工作表高级技巧3.条件格式条件格式将会基于设定的条件来自动更改单元格区域的外观，可以突出显示所关注的单元格或单元格区域、强调异常值、使用数据条、颜色刻度和图标集来直观地显示数据。例如：一份成绩表中谁的成绩最好，谁的成绩最差？不管这份成绩单中有多少人，利用条件格式都可以快速找到并以特殊格式标示出这些特定数据所在的单元格。1〕利用预置条件实现快速格式化“开始〞选项卡→“样式〞组→“条件格式〞按钮→选择规那么→选择预置的条件格式。3.1Excel制表根底3.1.3格式化工作表高级技巧各项条件规那么的功能说明：突出显示单元格规那么：通过比较运算符限定数据范围，例如，在一份工资表中，将所有大于10000元的工资数用红色字体突出显示。工程选取规那么：可以设定前假设干个最高值或后假设干个最低值、高于或低于该区域平均值的单元格特殊格式。例如，在一份学生成绩单中，用绿色字体标示某科目排在后5名的分数。数据条：帮助查看某个单元格相对于其他单元格的值。数据条越长，表示值越高。在观察大量数据〔如节假日销售报表中最畅销和最滞销的玩具〕中的较高值和较低值时，数据条尤其有用。色阶：通过使用两种或三种颜色的渐变效果来比较单元格区域中数据，一般情况下，颜色的深浅表示值的上下。例如，在绿色和黄色的双色色价中，可以指定数值越大的单元格的颜色越绿，而数值越小的单元格的颜色越黄。图标集：使用图标集对数据进行注释，每个图标代表一个值的范围。例如，在三色交通灯图标集中，绿色的圆圈代表较高值，黄色的圆圈代表中间值，红色的圆圈代表较低值。3.1Excel制表根底3.1.3格式化工作表高级技巧2〕自定义规那么实现高级格式化自定义规那么：“开始〞选项卡→“样式〞组→“条件格式〞按钮→“管理规那么〞命令→“新建规那么〞按钮→在“选择规那么类型〞列表框中选择规那么类型→在“编辑规那么说明〞区设定条件及格式修改规那么：“开始〞选项卡→“样式〞组→“条件格式〞按钮→“管理规那么〞命令→在“条件格式规那么管理器〞对话框的规那么列表中选择要修改的规那么→“修改规那么〞按钮进行修改3〕为案例家庭收支流水账设置条件格式翻开案例工作簿文档“3.1家庭收支流水账〔5〕〞，将家庭收支流水账中花费超过300元的支出用红色、斜体、加删除线的格式标出。3.1Excel制表根底3.1.4工作表的打印输出在输打印出前应对表格进行相关的打印设置，以使其输出效果更加美观。案例工作簿表格：3.1家庭收支流水账〔6〕打印要求：横向并水平居中打印在A5纸上，设置表格第3行为重复打印标题，工作表数据区域B1:G19设为打印区域。1.页面设置包括对页边距、页眉页脚、纸张大小及方向等工程的设置。“页面布局”选项卡→“页面设置”组页边距纸张大小设定打印区域设置页眉页脚3.1Excel制表根底3.1.4工作表的打印输出2.设置打印标题当工作表纵向超过一页长、或横向超过一页宽的时候，需要指定在每一页上都重复打印标题行或列，以使数据更加容易阅读和识别。“页面布局〞选项卡→“页面设置〞组中→“打印标题〞按钮→“页面设置〞对话框的“工作表〞选择卡→“顶端标题行〞框/“左端标题列〞。3.1Excel制表根底3.1.4工作表的打印输出3.设置打印范围并打印“文件”选项卡→“打印”命令设定打印份数指定打印机设置打印范围及缩放打印预览打印输出3.1Excel制表根底3.2工作簿与多工作表操作本节案例背景：默认情况下，一个工作簿文件中最多可以包含255张工作表。例如：一个单位的员工工资每月都要存放在一张表格中，一年就需要12张表格，将他们存放在一个工作簿文件中，管理和分析数据就会非常方便了。书娟在单位是一位人事管理员，负责公司所有职工的人事档案工作。她通过Excel管理家庭及工作事务。本节将通过对家庭收支流水账、员工工资表的处理，帮助书娟实现以下工作目标：了解工作簿与工作表的关系，掌握工作簿的新建、翻开、保存等根本操作。掌握如何通过模板创立一个工作簿以及如何自行创立模板。掌握如何保护工作簿与工作表中的数据不被修改的方法掌握工作表插入、删除、移动、复制、隐藏等根本操作。如何同时对多张工作表进行数据输入、格式化等操作了解如何通过控制工作窗口的视图来查看大型表格、比较多个表格。3.2工作簿与多工作表操作3.2.1工作簿根本操作Excel的工作簿实际上就是保存在磁盘上的工作文件，一个工作簿文件可以同时包含多个工作表。假设把工作簿比作一本书，那么工作表就是书中的每一页。1.创立一个工作簿启动Excel→“文件〞选项卡→“新建〞命令，右侧显示可用模板列表：创立空白文档提示：按Ctrl+N组合键可以快速新建空白工作簿；可以也先将“新建〞按钮添加到快速启开工具栏中，然后在需要创立空白文档时单击该按钮。基于模板创立当连接到Internet上时，可以访问Office上提供的模板另外，还可以自行创立模板并使用。3.2工作簿与多工作表操作3.2.1工作簿根本操作2.保存工作簿并为其设置密码可以在保存工作簿文件时为其设置翻开或修改密码，以保证数据的平安性。“快速访问工具栏”中的“保存”按钮，或者“文件”选项卡→“保存”/“另存为”命令依次选择保存位置、保存类型，并输入文件名单击“另存为”对话框右下方的“工具”按钮从打开的下拉列表中选择“常规选项”命令输入密码3.2工作簿与多工作表操作3.2.1工作簿根本操作注意：一定要牢记自己设置的密码，否那么将再不能翻开或修改自己的文档，因为Excel不提供取回密码帮助。3.关闭工作簿与退出Excel只关闭当前工作簿而不影响其他正在翻开的Excel文档：“文件〞选项卡→“关闭〞命令；退出Excel程序：“文件〞选项卡→“退出〞命令，如果有未保存的文档，将会出现提示保存的对话框。3.2工作簿与多工作表操作3.2.1工作簿根本操作4.翻开工作簿方法1：直接在资源管理器的文本夹下找到相应的Excel文档，用鼠标双击。方法2：启动Excel，从“文件〞选项卡上单击“最近所用文件〞，在右侧的文件列表中显示最近编辑过的Excel工作簿名，单击相应的文件名。方法3：启动Excel，从“文件〞选项卡上单击“翻开〞，在“翻开〞对话框中选择相应的文件名。3.2工作簿与多工作表操作创立和使用工作簿模板模板是一种文档类型，模板中已事先根据需要添加了一些常用的文本或数据，并进行了适当的格式化，其中还可以包含公式和宏，并以特定的文件类型保存在特定的位置。当需要创立类似的文档时，就可在模板根底进行简单的修改，以快速完成常用文档的创立，而不必从空白页面开始。使用模板是节省时间和创立格式统一的文档的绝佳方式。Excel本身提供大量内置模板可供选用，Excel2023模板文件的后缀名为.xltx。另外，还可以自己创立模板并使用。案例：以3.1节中创立完成的文档家庭收支流水账为根底创立一个名为“家庭收支流水账〞的模板。3.2工作簿与多工作表操作创立和使用工作簿模板创建一个模板①

打开要用作模板的工作簿案例文件“3.2家庭收支流水账”。②

对工作簿中的内容进行调整：模板中只需包含那些每个类似文件都有的公用项目，而对于那些不同的内容可以删除，公式保留。③

在“文件”选项卡上单击“另存为”，打开对话框。④

在“文件名”框中输入模板名称：“家庭收支流水账”。⑤

打开“保存类型”列表，从中单击“Excel模板”命令。如果在模板中包含宏，则应单击“Excel启用宏的模板”。⑥单击“保存”按钮，新建模板自动存放在Excel的模板文件夹中。注意：在“另存为”对话框中不要改变文档的存放位置，以确保在要用该模板创建新工作簿时该模板可用。3.2工作簿与多工作表操作创立和使用工作簿模板启动Excel,单击“文件”选项卡上的“新建”。在“可用模板”下，单击“我的模板”，打开“新建”对话框。在“个人模板”列表中双击要使用的模板“家庭收支流水账”，将会打开一个基于所选模板的新文档。输入新的数据，适当进行格式调整，然后将该文档保存为正常的Excel工作簿。2.使用自定义模板创立新工作簿3.2工作簿与多工作表操作创立和使用工作簿模板3.修改模板在基于模板创立的新文档中进行修改调整，并不会对模板本身产生影响。如果要对模板本身进行编辑修改，应从“文件〞选项卡上单击“翻开〞，选择模板文件存放的位置，找到要编辑的模板名并翻开它进行修改。Excel2023默认的模板文件保存位置为：

C:\Users\[实际用户名]\AppData\Roaming\Microsoft\Templates\3.2工作簿与多工作表操作工作簿的隐藏与保护1.隐藏工作簿当同时翻开多个工作簿时，可以暂时隐藏其中的一个或几个，需要时再显示出来。根本方法：单击“视图〞选项卡→“窗口〞组→“隐藏〞按钮，当前工作簿被隐藏起来。取消隐藏：“视图〞选项卡→“窗口〞组→“取消隐藏〞按钮→在“取消隐藏〞对话框中工作簿名称。3.2工作簿与多工作表操作工作簿的隐藏与保护2.保护工作簿当不希望他人对工作簿的结构或窗口进行改变时，可以设置工作簿保护。此处的工作簿保护不能阻止他人更改工作簿中的数据。如果想要到达保护数据的目的，可以进一步设置工作表保护，或者在保存工作簿文档时设定翻开或修改密码。打开需要保护的工作簿文档“审阅”选项卡“更改”组“保护工作簿”按钮保护结构设定密码提示：如果不提供密码，那么任何人都可以取消对工作簿的保护。如果使用密码，一定要牢记自己的密码，否那么自己也无法再对工作簿的结构和窗口进行设置。3.2工作簿与多工作表操作工作簿的隐藏与保护取消对工作簿的保护：再次在“审阅〞选项卡上的“更改〞组中，单击“保护工作簿〞按钮，如果设置了密码，那么在弹出的对话框中输入密码即可。3.2工作簿与多工作表操作3.2.4工作表根本操作1.插入工作表方法1：单击工作表标签右边的“插入工作表〞按钮，在最右边插入一张空白工作表。方法2：鼠标右键单击工作表标签→快捷菜单中的“插入〞命令→“插入〞对话框中双击表格类型。其中双击“工作表〞可在当前工作表前插入一张空白工作表。方法3：“开始〞选项卡→“单元格〞组→“插入〞按钮下的黑色箭头→下拉列表中的“插入工作表〞。3.2工作簿与多工作表操作3.2.4工作表根本操作2.删除工作表在要删除的工作表标签上单击鼠标右键，从弹出的快捷菜单中选择“删除〞命令，即可删除当前选定的工作表。3.改变工作表名称在工作表标签上双击鼠标→输入新表名→回车确认“开始〞选项卡→“单元格〞组→“格式〞按钮→“重命名工作表〞命令→输入新的工作表名→回车确认3.2工作簿与多工作表操作3.2.4工作表根本操作4.设置工作表标签颜色为工作表标签设置颜色可以突出显示某张工作表。在工作表标签上单击鼠标右键→快捷菜单中的“工作表标签颜色〞命令→选择颜色“开始〞选项卡→“单元格〞组→“格式〞按钮→“工作表标签颜色〞命令→选择一种颜色。3.2工作簿与多工作表操作3.2.4工作表根本操作5.移动或复制工作表改变工作表在工作簿中的位置或将其移动到另个一个工作簿中在同一工作簿或不同的工作簿中快速生成工作表的副本在工作表标签上单击鼠标右键→从快捷菜单中选择“移动或复制”命令；“开始”选项卡→“单元格”组→“格式”按钮→“移动或复制工作表”命令。从“工作簿”下拉列表中选目标工作簿。提示：要将工作表移动或复制到另一个工作簿中，须先将该工作簿打开，否则“工作簿”列表中看不到相应的文件名。在“下列选定的工作表之前”指定工作表要插入的位置如果要复制工作表，需要单击选中“建立副本”复选框3.2工作簿与多工作表操作3.2.4工作表根本操作提示：用鼠标直接拖开工作表标签即可在同一工作簿中移开工作表，拖动的同时按下Ctrl键即可复制工作表。6.显示或隐藏工作表方法1：在工作表标签上单击鼠标右键→快捷菜单中的“隐藏〞命令方法2：“开始〞选项卡→“单元格〞组→“格式〞按钮→“隐藏工作表〞命令取消隐藏：从上述相应菜单中选择“取消隐藏〞命令，在翻开的“取消隐藏〞对话框中选择相应的工作表。3.2工作簿与多工作表操作3.2.4工作表根本操作7.完成案例表格“3.2家庭收支流水账〞的设置将工作表“sheet1〞更名为“2月〞。删除表“sheet2〞和“sheet3〞基于新创立的模板“家庭收支流水账〞，在工作表“2月〞前插入3张工作表。将“sheet1〞的“工作表标签颜色〞设为红色，同时将“sheet1〞更名为“1月〞、并删除多余的工作表“sheet2〞和“sheet3〞。用复制的方法生成3-12月份工作表，并依次更名。隐藏工作表“3月〞。3.2工作簿与多工作表操作3.2.5工作表的保护为了防止他人对单元格的格式或内容进行修改，可以设定工作表保护。默认情况下，当工作表被保护后，所有单元格都会被锁定，不能进行任何更改。例如，不能在锁定的单元格中插入、修改、删除数据或者设置数据格式。当允许局部单元格被修改时，需要在保护工作表之前，对允许在其中更改或输入数据的区域解除锁定。3.2工作簿与多工作表操作3.2.5工作表的保护1.保护整个工作表保护整个工作表，使得任何一个单元格都不允许被更改。“审阅”选项卡→“更改”组→“保护工作表”按钮。选择允许他人更改的项目。设置“取消工作表保护时使用的密码”。要牢记自己的密码。重复确认密码后完成设置。3.2工作簿与多工作表操作3.2.5工作表的保护2.取消工作表的保护“审阅〞选项卡→“更改〞组→“撤消工作表保护〞→在“密码〞框中输入设置保护时使用的密码。3.解除对局部工作表区域的保护保护工作表后，默认情况下所有单元格都将无法被编辑。但在实际工作中，有些单元格中的原始数据还是允许输入和编辑的，为了能够更改这些特定的单元格，可以在保护工作表之前先取消对这些单元格的锁定。3.2工作簿与多工作表操作3.2.5工作表的保护在工作表中选择要解除锁定的单元格区域；或者需要隐藏的公式所在的单元格区域。“开始”选项卡→“单元格”组→“格式”按钮→“设置单元格格式”命令解除选定区域保护：在“保护”选项卡单击“锁定”取消对该复选框的选择。隐藏选定区域公式：在“保护”选项卡中保持“锁定”复选框被选中的同时再单击选中“隐藏”复选框。“审阅”选项卡→“更改”组→“保护工作表”→输入保护密码设定允许更改项目。3.2工作簿与多工作表操作3.2.5工作表的保护提示：只对工作表中的某个单元格或区域进行保护——先解除整个工作表中全部单元格的锁定→再对需要保护的单元格区域进行锁定→最后设置“保护工作表〞。4.允许特定用户编辑受保护的工作表区域如果一台计算机中有多个用户，或者在一个工作组中包括多台计算机，那么可通过该项设置允许其他用户编辑工作表中指定的单元格区域，以实现数据共享。“审阅〞选项卡→“更改〞组→“允许用户编辑区域〞按钮→“新建〞按钮→选定可编辑区域→输入区域标题名称→“权限〞按钮→指定可访问该区域的用户→“允许用户编辑区域〞对话框中“保护工作表〞按钮→设定保护密码及可更改工程。3.2工作簿与多工作表操作3.2.6同时对多张工作表进行操作Excel允许同时对一组工作表进行相同的操作，如输入数据、修改格式等。这为快速处理一组结构和根底数据相同或相似的表格提供了极大的方便。1.选择多张工作表选择全部工作表：在工作表标签上单击鼠标右键，从弹出的快捷菜单中选择“选定全部工作表〞命令。选择连续的多张工作表：按下Shift键不放，单击首尾表标签。选择不连续的多张工作表：按下Ctrl键不放，依次单击表标签。取消工作表组合：单击组合工作表以外的任一张工作表，或者从右键快捷菜单中选择“取消组合工作表〞命令。3.2工作簿与多工作表操作3.2.6同时对多张工作表进行操作2.同时对多张工作表进行操作当同时选择多张工作表形成工作表组合后，在其中一张工作表中所做的任何操作都会同时反映到组中其他工作表中，这样可能快速格式化一组结果相同的工作表、在一组工作表中输入相同的数据和公式等。首先选择一组工作表，然后在组内的一张工作表中输入数据和公式、进行格式化操作等。取消工作表组合后，再对每张表进行个性化设置，如输入不同的数据等。3.2工作簿与多工作表操作3.2.6同时对多张工作表进行操作3.填充成组工作表先在一张工作表中输入数据并进行格式化操作，将这张工作表中的格式及格式填充到其他同组的工作表中，以便快速生成一组根本结构相同的工作表。首先在一张工作表中输入根底数据，同时插入多张空表：翻开案例工作簿“3.2员工工资表〞。对工作表中的数据进行格式化操作：对“1月工资表〞中的数据区域A3:M38，套用一个预置表格样式。3.2工作簿与多工作表操作3.2.6同时对多张工作表进行操作在工作表选择包含填充内容及格式的单元格区域，同时选中其他工作表：选择单元格区域A1:M38，然后选中所有工作表。“开始〞选项卡→“编辑〞组→“填充〞按钮→“成组工作表〞命令→选择需要填充的工程。继续进行其他填充：保持“2月工资表〞为当前工作表，重新选定全部工作表。在“2月工资表〞的“序号〞所在的A列中输入序号1、2、3、……到数据的最后一行。查看每张表格相同的表格数据及格式应用情况。3.2工作簿与多工作表操作3.2.7工作窗口的视图控制1.多窗口显示与切换在Excel中，可以同时翻开多个工作簿；一个工作簿中的工作表可以划分为多个临时窗口。对这些同时翻开或划分出的窗口，可以进行排列及切换，以便于比较及引用。定义窗口：“视图〞选项卡→“窗口〞组→“新建窗口〞按钮。切换窗口：“视图〞选项卡→“窗口〞组→“切换窗口〞按钮并排查看：“视图〞选项卡→“窗口〞组→“并排查看〞按钮。全部重排：“视图〞选项卡→“窗口〞组→“全部重排〞按钮→选择“排列方式〞。隐藏窗口：“视图〞选项卡→“窗口〞组→“隐藏〞按钮。3.2工作簿与多工作表操作3.2.7工作窗口的视图控制2.冻结窗口当一个工作表超长超宽时，操作滚动条查看超出窗口大小的数据时，由于已看不到行列标题，可能无法分清楚某行或某列数据的含义。这时可以通过冻结窗口来锁定行列标题不随滚动条滚动。在工作表中的某个单元格中单击鼠标，该单元格上方的行和左侧的列将在锁定范围之内→“视图〞选项卡→“窗口〞组→“冻结窗格〞按钮→“冻结拆分窗格〞命令，当前单元格上方的行和左侧的列始终保持可见，不会随着操作滚动条而消失。取消窗口冻结：从“冻结窗格〞下拉表中选择“取消冻结窗格〞。3.2工作簿与多工作表操作3.2.7工作窗口的视图控制3.拆分窗口“视图〞选项卡→“窗口〞组→“拆分〞按钮，将以当前单元格为坐标，将窗口拆分为四个，每个窗口中均可进行编辑。再次单击“拆分〞可取消窗口拆分效果。4.窗口缩放“视图〞选项卡→“显示比例〞组→对当前窗口的显示进行缩放设置3.2工作簿与多工作表操作3.3Excel公式和函数本节案例背景：Excel提供大量实用函数满足各类计算的需要。通过公式和函数计算出的结果不但正确率有保证，而且在原始数据发生改变后，计算结果能够自动更新。在人事档案管理和工资表的创立过程中，公式和函数给书娟的工作提供了相当的便利，极大地提高了工作的效率和效果。本节将通过对员工档案表和员工工资表的处理，帮助书娟实现以下工作目标：了解公式和函数的根本作用，了解在公式中各种引用的含义及方法。掌握公式的构成、输入方法，学会构建公式以帮助计算。了解Excel提供了哪些类型的函数，掌握函数的根本输入方法。掌握Excel中常用、重要函数的使用方法，并在实际工作中应用它们。了解公式及函数应用过程中各种常见问题及解决方法。723.3.1使用公式的根本方法1.认识公式公式是一组表达式，由单元格引用、常量、运算符、括号组成，复杂的公式还可以包括函数，用于计算生成新的值。在Excel中，公式总是以等号“=〞开始。单元格引用：即单元格地址，用于表示单元格在工作表上所处位置的坐标。例如，显示在第B列和第3行交叉处的单元格，其引用形式为“B3〞。常量：固定的数值或文本。例如，数字“210〞和文本“姓名〞均为常量。运算符：运算符用于连接常量、单元格引用，从而构成完整的表达式。常用的运算符有：算术运算符，字符连接符，关系运算符。733.3Excel公式和函数3.3.1使用公式根本方法2.公式的输入与编辑1〕输入公式=常量/单元格引用/表达式必须以等号“=〞开始，在公式中所使用的运算符都必须是西文的半角字符例如：=30，=B3，=A5*10%，=C8&C9743.3Excel公式和函数3.3.1使用公式根本方法2〕修改公式用鼠标双击公式进入编辑状态，在单元格或者编辑栏中均可对公式进行修改即可。删除公式：按Del键。3.公式的复制与填充拖动公式单元格右下角的填充柄“开始〞选项卡→“编辑〞组→“填充〞按钮。进行公式的复制填充时，填充的实际上是公式而非数据本身，填充时公式中对单元格的引用采用的是相对引用。753.3Excel公式和函数3.3.1使用公式根本方法4.单位格引用在公式中最常用的是单元格引用。可以在单元格中引用一个单元格、一个单元格区域、引用另一个工作表或工作簿中的单元格或区域。单元格引用方式分为以下几类：相对引用：如“=A1〞。绝对引用：如“=$A$1〞。混合引用：如“=A$1〞、“=$A1〞、“=$A$1〞763.3Excel公式和函数3.3.2名称的定义与引用为单元格或区域指定一个名称，是实现绝对引用的方法之一。可以定义为名称的对象包括：常量、单元格或单元格区域、公式。1.了解名称的语法规那么唯一性原那么：名称在其适用范围内必须始终唯一，不可重复。有效字符：名称中第一个字符必须是字母、下划线(_)或反斜杠(\)，名称中不能使用大小写字母“C〞、“c〞、“R〞或“r〞。不能与单元格地址相同：例如，名称是A1、B$2等。不能使用空格：可选用下划线(_)和句点(.)作为单词分隔符。名称长度有限制：一个名称最多可以包含255个西文字符。不区分大小写：例如，Sales与SALES视为同名。773.3Excel公式和函数3.3.2名称的定义与引用2.为单元格或单元格区域定义名称定义好的名称将会在公式及函数中被引用。1〕快速定义名称选择要命名的单元格或单元格区域，在编辑栏的“名称框〞中输入名称后按Enter键确认。在案例工作簿文档“3.3员工档案及工资表〞的“员工档案表〞工作表中，将数据列表区域A3:N38的名称定义为“全体员工资料〞。783.3Excel公式和函数3.3.2名称的定义与引用2〕将现有行和列标题转换为名称选择要命名的区域，必须包括行或列标题→“公式〞选项卡→“定义的名称〞组→“从所选内容创立〞按钮→在对话框中，通过选中“首行〞、“左列〞、“末行〞或“右列〞复选框来指定包含标题的位置。在案例工作簿文档“3.3员工档案及工资表〞的“员工档案表〞中，将“根本工资〞和“工龄工资〞两列的首行转换为相应列数据的名称。793.3Excel公式和函数3.3.2名称的定义与引用3〕使用“新名称〞对话框定义名称“公式〞选项卡→“定义的名称〞组→“定义名称〞按钮→在“名称〞文本框中输入名称→在“范围〞下设定名称的适用范围→在“备注〞说明性批注。→“引用位置〞框中修改命名对象〔可以是单元格区域、常量、公式〕，可选择以下操作之一。在案例工作簿文档“3.3员工档案及工资表〞的“员工档案表〞中，将工龄工资常量“50〞元定义为名称“工龄工资_每年〞。803.3Excel公式和函数3.3.2名称的定义与引用3.引用名称名称可直接用来快速选定已命名的区域，更重要的是可以在公式中引用名称以实现精确引用。1〕通过“名称框〞引用单击编辑栏中“名称框〞右侧的黑色箭头，翻开“名称〞下拉列表→单击选择某一名称提示：通过名称框翻开的“名称〞下拉列表中不包括常量和公式的名称。813.3Excel公式和函数3.3.2名称的定义与引用2〕在公式中引用“公式〞选项卡→“定义的名称〞组→“用于公式〞按钮→选择名称4.更改或删除名称如果更改了某个已定义的名称，那么工作簿中所有已引用该名称的位置均会自动随之更新。更改名称：“公式〞选项卡→“定义的名称〞组→“名称管理器〞按钮→在名称列表中选择要更改的名称→“编辑〞按钮→在“编辑名称〞对话框中修改名称属性。删除名称：“公式〞选项卡→“定义的名称〞组→“名称管理器〞按钮→在名称列表中选择名称→“删除〞按钮。提示：假设公式中已引用的某个名称被删除，可能导致公式出错。823.3Excel公式和函数3.3.3使用函数的根本方法1.认识函数函数实际上特殊的公式，主要是为解决那些复杂计算需求而提供的一种预置算法，如求和函数SUM，平均值函数EVERAGE，条件函数IF等。函数通常表示为：函数名〔[参数1],[参数2],……〕函数中的参数可以是常量、单元格地址、数组、已定义的名称、公式、函数等。输入函数时必须以等号“=〞开始。2.Excel函数分类Excel提供大量工作表函数，并按其功能进行分类。Excel2023目前默认提供的函数类别共13大类，见表3.3中所列。833.3Excel公式和函数表3.3Excel2023函数类别函数类别常用函数示例及说明财务函数NPV(rate,value1,[value2],...)

返回一项投资的净现值。日期和时间函数YEAR(serial_number)返回某日期对应的年份数学和三角函数INT(number)将数字向下舍入到最接近的整数。统计函数AVERAGE(number1,[number2],...)返回参数的算术平均值查找和引用函数VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值数据库函数DCOUNTA(database,field,criteria)返回满足指定条件的非空单元格的个数。文本函数MID(text,start_num,num_chars)返回文本字符串中从指定位置开始的特定数目的字符逻辑函数IF(logical_test,[value_if_true],[value_if_false])若指定条件的计算结果为TRUE，将返回某个值；若该条件的计算结果为FALSE，则返回另一个值。信息函数ISBLANK(value)检验单元格值是否为空，若为空则返回TRUE工程函数CONVERT(number,from_unit,to_unit)将数字从一个度量系统转换到另一个度量系统中。兼容性函数RANK(number,ref,[order])返回一个数字在数字列表中的排位多维数据集函数CUBEVALUE(connection,member_expression1,member_expression2…)从多维数据集中返回汇总值与加载项一起安装的用户自定义函数如果在系统中安装了某一包含函数的应用程序，该程序做为Excel的加载项，其所包含的函数作用自定义函数显示在这里以供选用。843.3Excel公式和函数3.3.3使用函数的根本方法3.函数的输入与编辑函数的输入方式与公式类似，可以直接在单元格中输入“=函数名(所引用的参数)〞，但是要想记住每一个函数名并正确输入所有参数是有相当困难的。因此，通常情况采用参照的方式输入一个函数。1〕通过“函数库〞组插入“公式〞选项卡→“函数库〞组中的某一函数类别→从函数列表中单击函数→在“函数参数〞对话框中输入或选择参数853.3Excel公式和函数3.3.3使用函数的根本方法2〕通过“插入函数〞按钮插入“公式〞选项卡→“函数库〞组→“插入函数〞按钮，翻开“插入函数〞对话框→在“选择类别〞下拉表中选择函数类别，或者在“搜索函数〞框中输入函数的简单描述后单击“转到〞按钮→在“选择函数〞列表中选择函数→在“函数参数〞对话框中输入参数。3〕修改函数在包含函数的单元格的双击鼠标，进入编辑状态，对函数参数进行修改后按Enter键确认。863.3Excel公式和函数3.3.4Excel中常用函数的应用1.Excel中常用函数简介1〕求和函数SUM(number1,[number2],...])功能：将指定的参数number1、number2……相加求和。例如：=SUM(A1:A5)是将单元格A1至A5中的所有数值相加=SUM(A1,A3,A5)是将单元格A1、A3和A5中的数字相加。873.3Excel公式和函数3.3.4Excel中常用函数的应用2〕条件求和函数SUMIF(range,criteria,[sum_range])功能：对指定单元格区域中符合指定条件的值求和。提示：在函数中任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号(")括起来。如果条件为数字，那么无需使用双引号。例如：=SUMIF(B2:B25,">5")表示对B2:B25区域大于5的数值进行相加；=SUMIF(B2:B5,"John",C2:C5)，表示对单元格区域C2:C5中与单元格区域B2:B5中等于“John〞的单元格对应的单元格中的值求和。883.3Excel公式和函数3.3.4Excel中常用函数的应用3〕多条件求和函数SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：对指定单元格区域中满足多个条件的单元格求和。例如：=SUMIFS(A1:A20,B1:B20,">0",C1:C20,"<10")表示对区域A1:A20中符合以下条件的单元格的数值求和：B1:B20中的相应数值大于零、且C1:C20中的相应数值小于10。893.3Excel公式和函数3.3.4Excel中常用函数的应用4〕绝对值函数ABS(number)功能：返回数值number的绝对值例如：=ABS(-2)表示求-2的绝对值；=ABS(A2)，表示对单元格A2中的数值求取绝对值。5〕向下取整函数INT(number)功能：将数值number向下舍入到最接近的整数例如：=INT(8.9)表示将8.9向下舍入到最接近的整数，结果为8；=INT(-8.9)表示将-8.9向下舍入到最接近的整数，结果为-9。903.3Excel公式和函数3.3.4Excel中常用函数的应用6〕四舍五入函数ROUND(number,num_digits)功能：将指定数值number按指定的位数num_digits进行四舍五入。例如：=ROUND(25.7825,2)，表示将数值25.7825四舍五入为小数点后两位。提示：如果希望始终进行向上舍入，可使用ROUNDUP函数；如果希望始终始终进行向下舍入，那么应使用ROUNDDOWN函数。913.3Excel公式和函数3.3.4Excel中常用函数的应用7〕取整函数TRUNC(number,[num_digits])功能：将指定数值number的小数局部截去，返回整数。num_digits为取整精度，默认为0。例如：=TRUNC(8.9)表示取8.9的整数局部，结果为8；=TRUNC(-8.9)表示取-8.9的整数局部，结果为-8。923.3Excel公式和函数3.3.4Excel中常用函数的应用8〕垂直查询函数VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])功能：搜索指定单元格区域的第一列，然后返回该区域相同行上任何指定单元格中的值。例如：=VLOOKUP(1,A2:C10,2)要查找的区域为A2:C10，因此A列为第1列，B列为第2列，C列那么为第3列。表示使用近似匹配搜索A列〔第1列〕中的值1，如果在A列中没有1，那么近似找到A列中与1最接近的值，然后返回同一行中B列〔第2列〕的值。=VLOOKUP(0.7,A2:C10,3,FALSE)表示使用精确匹配在A列中搜索值0.7。如果A列中没有0.7这个值，那么所以返回一个错误#N/A。933.3Excel公式和函数3.3.4Excel中常用函数的应用9〕逻辑判断函数IF(logical_test,[value_if_true],[value_if_false])功能：如果指定条件的计算结果为TRUE，IF函数将返回某个值；如果该条件的计算结果为FALSE，那么返回另一个值。提示：在Excel2023中，最多可以使用64个IF函数进行嵌套，以构建更复杂的测试条件。也就是说，IF函数也可以作为value_if_true和value_if_false参数包含在另一个IF函数中。943.3Excel公式和函数3.3.4Excel中常用函数的应用9〕逻辑判断函数〔续〕例如：=IF(A2>=60,"及格","不及格")表示，如果单元格A2中的值大于等于60，那么显示“及格〞字样，否那么显示“不及格〞字样；=IF(A2>=90,"优秀",IF(A2>=80,"良好",IF(A2>=60,"及格","不及格")))表示以下对应关系：953.3Excel公式和函数单元格A2中的值公式单元格显示的内容A2>=90优秀90>A2>=80良好80>A2>=60及格A2<60不及格3.3.4Excel中常用函数的应用10〕当前日期和时间函数NOW()功能：返回当前计算机系统的日期和时间。当将数据格式设置为数值时，将返回当前日期和时间所对应的序列号，该序列号的整数局部说明其与1900年1月1日之间的天数。11〕函数YEAR(serial_number)功能：返回指定日期对应的年份。返回值为1900到9999之间的整数例如：=YEAR(A2)当在A2单元格中输入日期2023/12/27时，该函数返回年份2023。注意：公式所在的单元格不能是日期格式。963.3Excel公式和函数3.3.4Excel中常用函数的应用12〕当前日期函数 TODAY()功能：返回今天的日期。通过该函数，可以实现无论何时翻开工作簿时工作表上都能显示当前日期；该函数也可以用于计算时间间隔，可以用来计算一个人的年龄。例如：=YEAR(TODAY())-1963假设一个人出生在1963年，该公式使用TODAY函数作为YEAR函数的参数来获取当前年份，然后减去1963，最终返回对方的年龄。973.3Excel公式和函数3.3.4Excel中常用函数的应用13〕平均值函数AVERAGE(number1,[number2],...)功能：求指定参数number1、number2……的算术平均值，最多可包含255个参数。例如：=AVERAGE(A2:A6)表示对单元格区域A2到A6中的数值求平均值=AVERAGE(A2:A6,C6)表示对单元格区域A2到A6中数值与C6中的数值求平均值。983.3Excel公式和函数3.3.4Excel中常用函数的应用14〕条件平均值函数AVERAGEIF(range,criteria,[average_range])功能：对指定区域中满足给定条件的所有单元格中的数值求算术平均值例如：=AVERAGEIF(A2:A5,"<5000")表示求单元格区域A2:A5中小于5,000的数值的平均值；=AVERAGEIF(A2:A5,">5000",B2:B5)表示对单元格区域B2:B5中与单元格区域A2:A5中大于5000的单元格所对应的单元格中的值求平均值。993.3Excel公式和函数3.3.4Excel中常用函数的应用15〕多条件平均值函数AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：对指定区域中满足多个条件的所有单元格中的数值求算术平均值例如：=AVERAGEIFS(A1:A20,B1:B20,">70",C1:C20,"<90")表示对区域A1:A20中符合以下条件的单元格的数值求平均值：B1:B20中的相应数值大于70、且C1:C20中的相应数值小于90。1003.3Excel公式和函数3.3.4Excel中常用函数的应用16〕计数函数COUNT(value1,[value2],...)功能：统计指定区域中包含数值的个数。只对包含数字的单元格进行计数。例如：=COUNT(A2:A8)表示统计单元格区域A2到A8中包含数值的单元格的个数。17〕计数函数COUNTA(value1,[value2],...)功能：统计指定区域中不为空的单元格的个数。可对包含任何类型信息的单元格进行计数。例如：=COUNTA(A2:A8)表示统计单元格区域A2到A8中非空单元格的个数。1013.3Excel公式和函数3.3.4Excel中常用函数的应用18〕条件计数函数COUNTIF(range,criteria)功能：统计指定区域中满足单个指定条件的单元格的个数例如：=COUNTIF(B2:B5,">55")表示统计单元格区域B2到B5中值大于55的单元格的个数。1023.3Excel公式和函数3.3.4Excel中常用函数的应用19〕多条件计数函数COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)功能：统计指定区域内符合多个给定条件的单元格的数量。可以将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。例如：=COUNTIFS(A2:A7,">80",B2:B7,"<100")统计单元格区域A2到A7中包含大于80的数，同时在单元格区域B2到B7中包含小于100的数的行数。1033.3Excel公式和函数3.3.4Excel中常用函数的应用20〕最大值函数MAX(number1,[number2],...)功能：返回一组值或指定区域中的最大值例如：=MAX(A2:A6)表示从单元格区域A2:A6中查找并返回最大数值。21〕最小值函数MIN(number1,[number2],...)功能：返回一组值或指定区域中的最小值例如：=MIN(A2:A6)表示从单元格区域A2:A6中查找并返回最小数值。1043.3Excel公式和函数3.3.4Excel中常用函数的应用22〕排位函数RANK.EQ(number,ref,[order])RANK.AVG(number,ref,[order])功能：返回一个数值在指定数值列表中的排位；如果多个值具有相同的排位，使用函数RANK.AVG将返回平均排位；使用函数RANK.EQ那么返回实际排位。例如：=RANK.EQ("3.5",A2:A6,1)表示求取数值3.5在单元格区域A2:A6中的数值列表中的升序排位。1053.3Excel公式和函数3.3.4Excel中常用函数的应用23〕文本合并函数CONCATENATE(text1,[text2],...)功能：将几个文本项合并为一个文本项。可将最多255个文本字符串联接成一个文本字符串。联接项可以是文本、数字、单元格地址或这些工程的组合。例如：=CONCATENATE(B2,"",C2)表示将单元格B2中的字符串、空格字符以及单元格C2中的值相连接，构成一个新的字符串。提示：也可以用文本连结运算符“&〞代替CONCATENATE函数来联接文本项。例如，=A1&B1与=CONCATENATE(A1,B1)返回的值相同。1063.3Excel公式和函数3.3.4Excel中常用函数的应用24〕截取字符串函数MID(text,start_num,num_chars)功能：从文本字符串中的指定位置开始返回特定个数的字符。例如：=MID(A2,7,4)表示从单元格A2中的文本字符串中的第7个字符开始提取4个字符。1073.3Excel公式和函数3.3.4Excel中常用函数的应用25〕左侧截取字符串函数LEFT(text,[num_chars])功能：从文本字符串最左边开始返回指定个数的字符，也就是最前面的一个或几个字符。例如：=LEFT(A2,4)表示从单元格A2中的文本字符串中提取前四个字符。26〕右侧截取字符串函数RIGHT(text,[num_chars])功能：从文本字符串最右边开始返回指定个数的字符，也就是最后面的一个或几个字符。例如：=RIGHT(A2,4)表示从单元格A2中的文本字符串中提取后四个字符。1083.3Excel公式和函数3.3.4Excel中常用函数的应用27〕删除空格函数TRIM(text)功能：删除指定文本或区域中的空格。在从其他应用程序中获取带有不规那么空格的文本时，可以使用函数TRIM。例如：=TRIM("第1季度")表示删除中文文本的前导空格、尾部空格以及字间空格。28〕字符个数函数LEN(text)功能：统计并返加指定文本字符串中的字符个数。例如：=LEN(A2)表示统计位于单元格A2中的字符串的长度。1093.3Excel公式和函数3.3.4Excel中常用函数的应用2.常用函数在实际工作中的应用在案例工作簿文档“3.3员工档案及工资表〔1〕〞中运用公式及函数。1〕运用公式及函数完善员工档案表在“员工档案表〞工作表中，需要运用公式和函数分别提取员工的生日、计算出员工的年龄、工龄以及工龄工资。①提取员工生日：公式1：=CONCATENATE(MID(F4,7,4),"年",MID(F4,11,2),"月",MID(F4,13,2),"日")公式2：=MID(F5,7,4)&"年"&MID(F5,11,2)&"月"&MID(F5,13,2)&"日"1103.3Excel公式和函数3.3.4Excel中常用函数的应用②计算员工年龄：输入函数“=INT((TODAY()-G4)/365)〞③计算员工的工龄：输入函数“=INT((TODAY()-J4)/365)〞④计算工龄工资：每满一年工龄工资增加50元，用工龄乘以50即可计算工龄工资，可以通过绝对引用或已定义名称来计算。使用绝对引用：“=K4*根底数据!$B$4〞，使用定义名称：常量“50〞已事先被命名为“工龄工资_每年〞。输入公式“=K5*工龄工资_每年〞1113.3Excel公式和函数3.3.4Excel中常用函数的应用⑤计算根底工资：输入函数“=SUM(L4:M4)〞。⑥统计全部员工数量：在“根底数据〞工作表的“员工总人数〞处输入函数“=COUNTA(员工档案表!A4:A38)〞。⑦统计女员工的数量：在“根底数据〞工作表的“女性员工〞处输入函数“=COUNTIF(员工档案表!C4:C38,"女")〞。1123.3Excel公式和函数3.3.4Excel中常用函数的应用⑧统计工资数据在“根底数据〞工作表中的相应单元格中依次输入以下函数以计算相关数据：根本工资总额：=SUM(根本工资)，根本工资列已被定义名称，所以可直接被求和函数引用。管理人员工资总额：=SUMIF(员工档案表!D4:D38,"管理",根本工资)，用条件求和函数计算“部门〞属于“管理〞的所有人员的根本工资总和。平均根本工资：=AVERAGE(根本工资)。本科生平均根本工资：=AVERAGEIF(员工档案表!I4:I38,"本科",根本工资)，用条件求平均值函数计算“学历〞为“本科〞的所有人员的平均根本工资。最高根本工资：=MAX(根本工资)最低根本工资：=MIN(根本工资)1133.3Excel公式和函数3.3.4Excel中常用函数的应用2〕运用公式及函数完善1月工资表在“1月工资表〞中，需要利用函数和公式获取员工的姓名、所在的部门、员工的根底工资，并计算应付工资、应交个人所得税、实付工资等工资工程。①获取员工姓名、部门和根底工资：利用VLOOKUP函数从员工档案表中直接获取相应数据。获取姓名：=VLOOKUP(B4,全体员工资料,2,FALSE)获取部门：=VLOOKUP(B4,全体员工资料,4,FALS