Office 2019微任务实训教程 课件 项目3 Excel 2019电子表格

Excel2019概述利用EXCEL处理电子表格数据主讲人：张凤k0201Excel2019Excel是一款电子表格处理软件，可以高效地完成各种表格的设计和制作，对表格中的数据进行计算和分析，还可以把表格中的数据制作成精美的图表。Excel2019的工作界面工作簿工作簿：也称为“Book”，是由Excel创建并存储在磁盘上的文件，其扩展名为.xlsx。工作簿由工作表组成。工作表：也称为“Sheet”，是由行和列交叉形成的二维表格，用于存储和处理数据。单元格：行和列交叉所形成的矩形方格称为单元格，是工作表的基本组成元素。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结Excel电子表格软件基础知识Excel工作薄、工作表的构成。Excel界面组成及窗口操作。单元格及活动单元格单元格的基本操作认识单元格区域利用EXCEL处理电子表格数据主讲人：张凤k0202单元格区域Excel中，单元格区域实际就是若干单元格的集合，既可以是连续区域，也可以是非连续区域。单元格区域B2:C6单元格区域并集与交集单元格区域的表示单元格区域表示第8行8:8第6行至第9行6:9第C列C:C第C列到第D列C:D矩形区域对角的两个单元格名称对，如B2:C6两个区域的并集在单元格区域间用英文逗号隔开，如B2:D5,C4:E6两个区域的交集在单元格区域间用空格隔开，如B2:D5C4:E6单元格区域的操作选中单元格选中单元格区域不连续区域：Ctrl+单击连续区域：单击第一个单元格，Shift+单击最后一个单元格设置单元格格式开始功能区右键快捷菜单（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结单元格区域的表示单元格的选择设置单元格格式单元格的其它操作输入数据利用EXCEL处理电子表格数据主讲人：张凤k0203数据类型

在Excel开始功能区内数字功能组的数字格式列表中提供数字、货币、会计专用、短时期、长时期、时间、百分比、分数、科学记数和文本等10种基本类型，其中常用不代表任何特定格式，而是会根据用户输入数据的特点自动识别为基本数字类型数值类数据数值类数据本质上是数值，可以进行数学运算，如价格、数量等；在Excel中，数值类数据可以表现为多种具体的数据形式；数值类数据在单元格中默认为右对齐。文本类数据文本类数据本质上是不能进行数学运算的数据；文本类数据在单元格内以左对齐方式呈现。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结数据类型数值类数据可以进行数学运算数字、百分数、分数、科学计数、会计专用、货币、日期（从1900年1月1日当天算起的天数[整数，当天为1]）、时间（[小数]从0时起经过的秒数）等文本类数据不能进行数学运算的数据文字、符号、字母、姓名、民族、地址等自定义数据格式利用EXCEL处理电子表格数据主讲人：张凤k0204自定义数据格式定义：不改变数据本身，仅改变显示样式（如日期、货币、条件显示等）。作用：保持数据可计算性满足特殊显示需求（如补零、添加单位）常用格式代码代码效果示例0强制显示位数000

→005#可选位数#.##

→3.5@文本占位符"ID:"@

→ID:A01[颜色]按颜色显示[蓝色]0示例格式代码作用原数据显示000-0000-0000手机号分段13812345678138-1234-5678#,##0"万元"千分位+单位2500025,000万元[>90]"优秀";[>60]"合格";"不合格"条件提示95优秀操作步骤选中单元格

→

右键“设置单元格格式”选择“自定义”→

输入格式代码实时预览效果

→

点击确定（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结自定义格式=数据+显示逻辑作用：保持数据可计算性满足特殊显示需求操作步骤自动填充和自定义序列利用EXCEL处理电子表格数据主讲人：张凤k0205自动填充功能：快速复制数据或填充有一定规律的数据。如数字、字母、日期等作用：保持数据可计算性满足特殊显示需求（如补零、添加单位）填充柄活动单元格的右下角的方点，被称作填充柄；把鼠标指针指向填充柄，当指针变成“十”字型时，按下鼠标左键对其拖动，可自动填充。【填充】面板利用【开始】功能区→【编辑】组→【填充】面板也可以实现填充功能；执行其中的【系列】命令，将打开序列对话框，其中【行】和【列】表示填充的方向，【步长】表示每次填充的变化间隔，【终止值】表示填充的上限值。自定义序列序列是有顺序的文本类数据的集合，可用于排序或填充。Excel中内置了月份、星期、季度等序列。Excel允许用户创建自己的自定义序列。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结自动填充填充柄【开始】功能区自定义序列设置单元格格式利用EXCEL处理电子表格数据主讲人：张凤k0206单元格格式Excel单元格格式与Word单元格格式有类似之处，如字体格式、对齐方式、数字格式、样式以及行高和列宽等，其主要功能主要集中在开始功能组中。设置单元格格式对话框单元格格式对话框可以设置更多单元格格式（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结单元格格式通过设置单元格格式可以使表格更加实用、美观。设置单元格格式对话框使用条件格式利用EXCEL处理电子表格数据主讲人：张凤k0207条件格式所谓条件格式就是单元格格式可以根据条件变化而变化。利用条件格式，可以帮助用户突出显示所关注的单元格或区域，另外数据条、颜色刻度和图标集等则可直观显示数据。设置条件格式方法：点击【开始】功能区→【样式】组中→【条件格式】命令，选择合适的条件格式。设置规则。预设条件格式突出显示最前/最后数据条色阶图标集管理规则通过管理规则对话框可以对现有规则进行管理，设置多个规则的优先级，并修改或新建规则（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结条件格式条件格式基于条件自动更改单元格区域的外观。如果条件为True，则按指定格式更改；如果条件为False，则保持原格式不变。设置条件格式管理规则管理工作表利用EXCEL处理电子表格数据主讲人：张凤k0208管理工作表对工作表的管理主要包括新建、移动、复制、隐藏/显示、重命名以及更改表标签颜色等。方法：通过功能区管理工作表通过快捷菜单管理工作表通过功能区管理工作表工作表管理工具主要分布在开始功能区的【单元格】组中。通过快捷菜单管理工作表右击工作表标签将弹出工作表管理快捷菜单，以此可对工作表进行多方位管理。提示：管理工作表时，单击工作表标签可对应选定工作表，按住Ctrl键配合可选定多个非连续工作表，按住Shift键配合可定选多个连续工作表。当多个工作表被同时选定时，Excel标题栏中将显示“[组]”字样。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结管理工作表新建、删除、隐藏、重命名、修改工作表标签颜色等方法：功能区和快捷菜单通过功能区管理工作表通过快捷菜单管理工作表管理窗口利用EXCEL处理电子表格数据主讲人：张凤k0209Excel窗口Excel窗口是Excel的工作界面，通过窗口管理功能，可以实现窗口的拆分、隐藏、重排、冻结窗格等操作，以便于用户查看、比较工作表中的数据。全部重排功能：对多个打开的窗口进行重新排列，以便于查看类型：平铺水平并排垂直并排层叠新建窗口通过新建窗口可以为当前工作簿建立一个镜像窗口，结合全部重排命令，可方便用户对工作表或工作簿中的数据进行比较。冻结窗格功能：滚动工作表时，保持指定的行或列始终可见类型：冻结指定行、列冻结首行冻结首列拆分窗口功能：将工作表编辑区左右或上下拆分为可独立滚动浏览的窗格。隐藏、切换窗口隐藏窗口功能：设置当前工作簿窗口不可见切换窗口：功能在当打开的可见工作簿窗口间进行切换。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结123456切换窗口隐藏窗口拆分窗口全部重排新建窗口冻结窗格管理窗口使用公式利用EXCEL处理电子表格数据主讲人：张凤k0210运算符类型：算术运算符比较运算符文本连接运算符引用运算符表达式定义：表达式是运用运算符连接各种数据进行运算的计算式；Excel的表达式由运算符、常量、单元格地址、函数及括号等组成；运算符的优先级Excel运算默认的优先次序从高到低为:引用运算符、–(负号)、%（百分号)、^（乘方)、*(乘号)和/(除号)、+(加号)和–(减号)、&(连接符)、比较运算符。可利用英文圆括号()来改变运算顺序。公式Excel中，公式必须以“=”号开头，基本格式为“=<表达式>”。在单元格中输入公式，Excel则可自动运算并返回结果。示例：输入“=3+2*2”，单元格中将显示其运算结果“7”；输入“=A2+A7”，则表示计算A2和A7两个单元格的值之和。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结运算符算术运算符、比较运算符、文本连接运算符、引用运算符表达式定义、结构运算符的优先级公式：公式必须以“=”号开头，基本格式为“=<表达式>”引用单元格利用EXCEL处理电子表格数据主讲人：张凤k0211引用单元格定义：Excel公式中出现单元格地址及区域时，表示要引用其中的数据，被称为单元格引用。示例：公式“=A1+A2”，表示计算单元格A1和A2中数据之和引用类型相对引用列标和行号前都没有$符号，如：C3绝对引用列标和行号前都有$符号，如：$C$3混合引用只有列标前或只有行号前有$符号，如：$C3，C$3（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结定义引用类型相对引用绝对引用混合引用引用运算符单元格引用使用函数利用EXCEL处理电子表格数据主讲人：张凤k0212函数定义：函数是一些预先定义的具有特定功能（如计算、统计、分析等）的特殊表达式。函数的基本格式：函数名称([参数1],[参数2],…)说明：函数名是函数的标识；括号中可有若干参数，参数间用逗号分隔；方括号内的是可选参数，否则是必选参数。参数可以是数字、文本、逻辑值（TRUE或FALSE）、其他函数等。函数库Excel提供了大量的函数，以满足各种不同的运算需要；为便于查找和使用这些函数，根据函数的功能将其归并为若干个分类，如数学和三角函数、统计函数、日期与时间函数等。插入函数“自动求和”命令“插入函数”对话框直接输入（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结定义插入函数自动求和插入函数直接输入函数库函数定义名称利用EXCEL处理电子表格数据主讲人：张凤k0213定义名称定义：为便于引用，可以为单元格、矩形单元格区域以及多个单元格区域的集合定义名称。操作：【公式】功能区→【定义的名称】功能组管理名称通过【管理名称】对话框可以对已定义的名称进行管理。插入函数“自动求和”命令“插入函数”对话框直接输入（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结定义名称定义名字指定引用位置和范围管理名称设置数据有效性利用EXCEL处理电子表格数据主讲人：张凤k0214数据验证数据验证用以控制单元格可接受的数据类型、范围等，以防止用户输入无效数据。为单元格设置数据验证后，当用户选定单元格时可以显示输入提示信息，当键入无效数据时可以弹出出错警告等。设置数据有效性【数据】功能区→【数据工具】功能组→【数据验证】下拉箭头→【数据验证】→打开【数据验证】对话框；设置验证条件、输入信息和出错警告等。出错警告样式“停止”样式“警告”样式“信息”样式（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结数据验证作用：当单元格设置数据验证后，选定单元格时显示输入信息，常用于指导用户输入数据；直接键入无效数据时弹出出错警告消息，以提示用户键入的数据存在的问题或阻止用户输入无效数据；对非键入型输入（如复制、填充、公式计算等）的无效数据，虽不会弹出出错信息，但可通过圈释无效数据命令进行标注。出错警告样式数据排序利用EXCEL处理电子表格数据主讲人：张凤k0215数据清单数据清单是Excel中相对连续的一个二维表数据，是按记录和字段的结构特点组成的数据区域在数据清单内，第一行是一组列标题（字段名），其余行是各列对应的值（记录）。数据清单的规范列标题不重名、不为空、不为数值；每列值的数据格式要求统一；数据清单内不能有空行或空列或合并单元格；一个工作表中通常只存储一个数据清单，以便被其他应用程序正常引用；若确实需要保存多个数据清单时，通常利用空行或空列将多个数据清单进行隔离。说明：对于数据清单的规范，Excel并未给予严格限制，而是交由用户自行遵守。数据排序让数据按照一定规则排列，便于被人们观察、查找或定位，有利于发现数据价值，有利于提高数据的处理效率。Excel中可对数据清单进行单列或多列排序，可按照文本、数字、日期和时间进行升序或降序或自定义序列排列，还可依据单元格颜色、字体颜色或图标集进行排序。排序和筛选面板排序和筛选功能区单元格快捷排序菜单自定义排序单列/多列排序按列/行排序（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结数据清单数据清单是Excel中重要的数据管理单元。数据排序单列/多列排序按列/行排序关键字类型：文本、数值、时间、日期、自定义序列数据筛选利用EXCEL处理电子表格数据主讲人：张凤k0216数据筛选所谓筛选就是对数据清单中的数据按筛选条件进行过滤，符合条件的记录将继续显示，不符合条件的记录则会被隐藏。自动筛选自动筛选无需指定专门的条件区域。激活数据列表区域，执行【筛选】命令，数据清单的各列标题名单元格右端都显示下拉箭头；单击该箭头，打开自动筛选面板，利用筛选面板可创建过滤器。高级筛选执行【高级】命令将打开如图E所示的高级对话框，借助指定的【条件区域】，就可对指定的【列表区域】（即数据清单）进行高级筛选。条件区域条件区域：第一行为字段名，其他行的比较数据与同列字段名组成关系表达式；相同行的条件之间是“并且(and)”的逻辑；多行的条件之间是“或者(or)”的逻辑；（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结数据筛选筛选的本质就是提取符合条件的记录，既可在原数据区域中隐藏不符合筛选条件的记录，也可将符合条件的记录提取并复制到指定的目标区域。数据排序类型自动筛选高级筛选

分类汇总利用EXCEL处理电子表格数据主讲人：张凤k0217数据筛选数据清单中的数据按指定关键字值进行分类，分类汇总可对它们进行分类统计和计算：具有相同关键字的数据行记录被汇总生成汇总摘要行数据。分类汇总步骤按照分类字段排序；在【数据】功能区的【分级显示】组中，点击【分类汇总】工具，将打开分类汇总对话框；设置分类字段、汇总方式、汇总项选择汇总结果显示方式（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结分类汇总分类汇总前需要对数据清单按分类字段进行排序，在此基础上再进行分类汇总。分类汇总后，数据将被分级显示，可以控制明细数据的显示或隐藏。新的分类汇总默认替换当前分类汇总结果；如果设置为不予替换，则会在现有分类汇总的基础上嵌入新的分类汇总，从而实现多级嵌套。分级显示利用EXCEL处理电子表格数据主讲人：张凤k0218分级显示把数据组织成树状结构，用户可根据需要把各级数据予以展开或折叠。Excel通过创建组的形式实现分级显示，既可创建行组，也可创建列组。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结分级显示Excel的分级显示与Word中的大纲视图相当，以便用户根据需要折叠或显示各级各组数据明细。Excel的分级显示最多可达8级，用1-8的数字表示，数字越小级别越高。分级组由摘要数据和明细数据构成，不过二者只是相对概念，低一级的摘要数据包含在高一级的明细数据中。应用数据透视表利用EXCEL处理电子表格数据主讲人：张凤k0221数据透视表数据透视表是一种可以从源数据表中快速提取并汇总大量数据的交互式表格，综合了数据排序、筛选、分类汇总等功能，可方便地调整分类汇总和计算的方式。数据透视图数据透视图是数据透视表的图表形式，以图表的形式直观地、交互的地展示数据透视表中的数据。创建数据透视表选中数据区域或数据区域中任意单元格，单击【插入】功能区，【表格】组的【数据透视表】命令。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结数据透视表是集计算、汇总和分析等一体的交互式数据处理工具，数据透视图则是数据透视表的图表呈现方式。数据透视表或数据透视图的数据源，既可以是表格也可以是区域；要求符合表格规范。使用图表利用EXCEL处理电子表格数据主讲人：张凤k0222图表图表用图形等直观形式显示数据系列，以便用户更易掌握数据系列自身信息及不同数据系列之间关系。图表类型Excel提供了多种图表类型，如柱形图、饼图、折线图、条形图等。通过【插入】功能区，【图表】组中的相关命令可选择合适的图表类型。插入图表选中数据源区域，单击【插入】功能区，【图表】组中对应的图表类型，在打开的图表面板中选择合适的图表。也可以通过插入图表对话框插入图表。编辑图表激活图表，Excel功能区部分将显示图表工具栏，利用该工具栏可对当前图表进行个性化设置。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结图表的插入能直观、生动的反映表格数据；且能随着源数据的变化而自动更新。图表类型较多，应根据工作表内容来选择适合的类型。不同类型的图表可包含的图表元素也不尽相同。大多数图表都可以表现多个系列的数据，而饼图只能表现一个系列的数据。应用迷你图利用EXCEL处理电子表格数据主讲人：张凤k0223迷你图迷你图是工作表单元格中的一个微型图表，可提供数据的直观表示。使用迷你图可以显示一系列数值的趋势，或者可以突出显示最大值和最小值等。插入迷你图在插入功能区的【迷你图】功能组中，Excel提供有三种类型迷你图。单击任意类迷你图图标，将弹出创建迷你图对话框，用户只需指定迷你图所需的数据范围和放置的位置范围，就可在指定位置上创建出相应的迷你图。编辑迷你图激活迷你图所在单元格，Excel标题栏中将显示迷你图工具栏，可对迷你图进行个性化设置。（相关操作步骤见教材或本书配套的微任务云资源与服务平台）任务实施任务总结迷你图是可在单元格中显示的微型图表，用以直观显示数据系列中数据的分布形态。创建迷你图时，既可为单个数据系列创建独立的迷你图，也可为多个数据系列创建迷你图组。单个迷你图创建完成，拖动其所在单元格的填充柄，则新迷你图与原迷你图组成一个迷你图组。设置安全保护利用EXCEL处理电子表格数据主讲人：张凤k0224Excel安全保护为防止工作表或工作簿中的数据、结构或格式惨遭意外或恶意破坏