Excel 2016数据处理与分析（微课版）PPT完整全套教学课件

第1章Excel基础主要内容1.1认识Excel1.2Excel文件操作1.1认识Excel1.1.1Excel中的基本元素.xlsx1.1.1Excel中的基本元素工作簿1.1.1Excel中的基本元素PPT模板下载：/moban/行业PPT模板：/hangye/节日PPT模板：/jieri/PPT素材下载：/sucai/PPT背景图片：/beijing/PPT图表下载：/tubiao/优秀PPT下载：/xiazai/PPT教程：/powerpoint/Word教程：/word/Excel教程：/excel/资料下载：/ziliao/PPT课件下载：/kejian/范文下载：/fanwen/试卷下载：/shiti/教案下载：/jiaoan/字体下载：/ziti/

工作表1.1.1Excel中的基本元素单元格1.1.2Excel的工作界面1.1.2Excel的工作界面工作表:Sheet1的工作区域默认工作簿名称:工作簿1工作表标签快速访问工具栏

Excel中的功能操作分为文件、开始、插入、页面布局、公式、数据、审阅、视图等等。各选项卡中收录相关的功能群组，方便使用者切换、选用。1.1.2Excel的工作界面行号1~1048576,共2^20=1048576行列号A~XFD，共2^14=16384列名称框地址栏1.1.2Excel的工作界面鼠标的正常形状编辑栏组活动单元格:A1功能区当要使用“功能区”时，只要将鼠标移到任一个选项卡上单击即可开启；但当鼠标移到其他地方再单击时，“功能区”又会自动隐藏了。1.1.2Excel的工作界面在功能区的右下角按下按钮，可以打开对话框做更详细的设定。例如我们想要改变单元格的某些属性，就可以单击“开始”选项卡中“字体”功能区右下角的“启动器”按钮，打开“设定单元格格式”对话框来调整。1.1.2Excel的工作界面如果觉得功能区占用太大的版面位置，可以将“功能区”隐藏起来。1.1.3工作表操作单击工作表标签右侧的加号。右键单击工作表标签，通过快捷菜单中选择。通过菜单选择插入工作表。新建工作表1.1.3工作表操作单击工作表标签，拖动其到所需的位置。调整工作表顺序1.1.3工作表操作通过菜单选择删除工作表。右键单击工作表标签，在快捷菜单中选择。删除工作表1.1.3工作表操作右键单击工作表标签，在快捷菜单中选择。双击工作表标签，对其进行编辑。重命名工作表1.1.3工作表操作右键单击工作表标签，在快捷菜单中选择。按下Ctrl键的同时将其拖动到所需的位置。复制工作表1.1.3工作表操作右键单击工作表标签，选择相应的操作。更改工作表标签颜色1.1.3工作表操作右键单击工作表标签，选择相应的操作。右键单击任意工作表标签，然后打开“取消隐藏”对话框，选择相应的操作。隐藏和取消隐藏工作表1.1.4数据区域的选择整行整列连续区域整个数据区域整个工作表区域连续区域的选择1.1.4数据区域的选择借助Ctrl键。在“名称框”中输入用逗号分隔的区域的地址。通过“定位条件”对话框。不连续区域的选择1.1.5单元格的基本操作“数字”单元格的格式设置“对齐”“字体”“边框”“填充”“保护”1.1.5单元格的基本操作“单元格样式”单元格的样式设置“套用表格格式”“条件格式”1.2Excel文件操作1.2Excel文件操作xlsx:不包含宏的工作簿文件（默认）xlsm:包含宏的工作簿文件xltx:不包含宏的工作簿模板文件xltm:包含宏的工作簿模板文件xlsa:加载项文件xlsb:与旧的xls格式相似，但具有新功能的二进制文件xlsk:备份文件……Excel文件格式包括1.2Excel文件操作Excel文件的保存1.2Excel文件操作Excel文件的保存1.2Excel文件操作Excel文件的保护1.2Excel文件操作Excel文件的保护再见！第1章

数据输入的格式设置主要内容2.1数据验证2.2多级菜单2.1数据验证2.1.1案例说明制作如图所示的表格。2.1.2知识要点分析1.数据输入

先选择单元格，再输入数据。数据会在单元格和编辑栏中同时显示输入的内容。用①Enter键（活动单元格向下移动）；②Tab键（活动单元格向右移动）；③单击编辑栏上的“

”按钮（活动单元格不改变）三种方法确认输入。如果要放弃刚才输入的内容，单击编辑栏上的“

”按钮或使用键盘上的Esc键。2.1.2知识要点分析1.数据输入

文本的输入文本：由字母、汉字、数字等符号形成的字符串。输入文本：直接输入文本内容。输入数值文本：在数值前加单引号。如：‘2006010101。默认格式下，Excel将输入的文本在单元格中左对齐。2.1.2知识要点分析1.数据输入数值型数据可以直接输入。默认格式下，Excel将输入的数值在单元格中右对齐。注意：当单元格中数据以科学计数法表示或者填满了“#”符号时，表示这一列没有足够的宽度来正确显示数字，在这种情况下，需要改变数字格式或者改变列宽。3.06×1063.06E+062.1.2知识要点分析1.数据输入日期和时间的输入输入日期：用斜杠/或连字符-作为分隔符。如：2008/5/12输入时间：用冒号:作为分隔符。如：2:28或14:28:04输入系统的日期：按组合键Ctrl+;

输入系统的时间：按组合键Ctrl+Shift+;2.1.2知识要点分析2.数据验证单元格中的数据是0~100之间整数。2.1.2知识要点分析2.数据验证单元格中的数据是0~100之间整数。2.1.2知识要点分析2.数据验证单元格中的数据是0~100之间整数。2.1.2知识要点分析2.数据验证单元格中的数据是0~100之间整数。2.1.2知识要点分析2.数据验证固定序列值的输入。序列源中的数据一定要用英文逗号分隔！2.1.2知识要点分析3.数据填充自动填充

根据已输入的数据，在连续单元格中自动填充数据

自动填充数值序列：首先在起始的两个单元格中分别输入序列的前两项数值，然后选中这两个单元格，将鼠标箭头移至其右下角，当指针变成黑色十字形时，按住左键向下方或右方拖曳到最后一个单元格。如果选中一个值拖动鼠标结果会怎样？2.1.2知识要点分析3.数据填充自动填充

等比数列无法像等差数列一样以拉曳填充控点的方式来建立。例如，要建立2,4,8，……这样的数列，我们可以：

在第一个单元格中输入2，再选中需要生成序列的一个单元格区域。2.1.2知识要点分析3.数据填充自动填充

等比数列无法像等差数列一样以拉曳填充控点的方式来建立。例如，要建立2,4,8，……这样的数列，我们可以：单击“开始”菜单下“编辑”组中的“填充”命令。2.1.2知识要点分析3.数据填充自动填充

自动填充相同的数据：选定单元格区域，输入值，然后按Ctrl＋Enter键，即可实现在选定的单元格区域中一次性输入相同的值。

2.1.2知识要点分析3.数据填充自动填充

自动填充含数值的文本序列：输入第一项数据，拖曳后文本不变，但数值变化。2.1.2知识要点分析3.数据填充用户自定义填充序列

Excel允许用户自定义填充序列。例如，在填充序列中没有二十四节气，我们可以将其添加到序列中。选择“文件-选项-高级-编辑自定义列表”命令，在“选项”对话框中选择“自定义序列”选项卡。2.1.2知识要点分析3.数据填充用户自定义填充序列2.1.2知识要点分析3.数据填充用户自定义填充序列2.1.2知识要点分析3.数据填充用户自定义填充序列2.1.2知识要点分析3.数据填充快速填充例如，有一批的数据，现在要求将“设备品名”栏中的中文信息提取出来，放在对应行的“设备名”中。2.1.2知识要点分析3.数据填充快速填充

(1)在B2单元格输入要提取的中文名字，回车后，再在B3单元格中输入要提取的第一个汉字“路”，这时系统就会弹出感知填充的提示信息，直接按下Enter键，即可在相应的单元格中提取到所要的中文信息。2.1.2知识要点分析3.数据填充快速填充如果“快速填充”未生成预览，则可能未启用。需启用此功能，然后再使用它。（单击“文件”|“选项”|“高级”）2.1.2知识要点分析3.数据填充快速填充(2)通过单击“数据”|“快速填充”。2.1.3操作步骤1.制作表头输入表头信息合并单元格，调整表头信息的格式2.1.3操作步骤2.输入设置可以自动产生固定的两个值：“男，女”可以通过“数据验证”控制！选中需要指定的单元格，打开“数据验证”对话框…2.1.3操作步骤2.输入设置2.1.3操作步骤2.输入设置固定的一些值，可以通过“数据验证”控制！2.1.3操作步骤2.输入设置建立数据表的整体框架。选择“籍贯”数据列。打开“数据验证”对话框。2.1.3操作步骤2.输入设置2.1.3操作步骤2.输入设置固定的长度，可以通过“数据验证”控制！固定的长度，可以通过“数据验证”控制！固定的长度，可以通过“数据验证”控制！2.2多级菜单2.2.1案例说明在前一节学生信息表中再添加两个字段：学院、专业。如下图所示。2.2.2知识要点分析1.单元格区域命名单元格区域命名就是给一个单元格区域起一个“名称”。使用名称的好处：使公式含义更容易理解。提高公式编辑的准确性。快速定位到特定位置。名称可以作为变量来使用。可以方便地应用于所有的工作表。2.2.2知识要点分析2.名称的命名规则单元格区域命名就是给一个单元格区域起一个“名称”。名称的命名规则：名称的第一个字符必须是字母或下划线，不能使用单元格地址。名称中的字符可以是字母、数字，不能含有空格，可以使用句点。名称的长度不能超过255个字符。名称中的字母不区分大小写。命名时，不要使用Excel的内部名称。2.2.2知识要点分析2.名称的命名规则如果我们希望将一个单元格区域命名，可以通过“公式”菜单下“定义的名称”组中的“名称管理器”命令打开“名称管理器”对话框。2.2.2知识要点分析2.名称的命名规则如果我们希望将一个单元格区域命名，可以通过“公式”菜单下“定义的名称”组中的“名称管理器”命令打开“名称管理器”对话框。2.2.2知识要点分析2.名称的命名规则我们也可以通过“名称框”快速定义“名称”。先要选择“名称”要引用的区域，然后在“名称框”中输入要定义的“名称”。选择“名称”要引用的区域输入名称2.2.2知识要点分析2.名称的命名规则Excel还提供了对带有行列标题的多个单元格区域定义名称的方法：选中需要定义名称的多个单元格区域，通过“公式”菜单下“定义的名称”组中的“根据所选内容创建”命令打开“以选定区域创建名称”对话框。2.2.2知识要点分析3.名称的使用我们可以通过输入的方式在公式中使用定义的名称，也可以通过<F3>键打开“粘贴名称”对话框来使用定义的名称。2.2.2知识要点分析4.多级菜单的下拉式输入现有销售产品的类别、名称和型号，要实现多级关系的下拉式录入。2.2.2知识要点分析4.多级菜单的下拉式输入分别选择不同的区域进行命名。例如，定义名称“电器”，其引用的区域为:B2:B5。2.2.2知识要点分析4.多级菜单的下拉式输入分别选择不同的区域进行命名。定位B2:I17的“常量”区域。打开“根据所选内容创建”对话框，在弹出的对话框中选择“最左列”。完成单元格区域的命名。2.2.2知识要点分析4.多级菜单的下拉式输入选择C2:C16区域。鼠标左键依次单击“数据”|“数据工具”|“数据验证”，在“允许”下选择“序列”，在“来源”处输入“=indirect(B2)”，即可实现类别名称的选择录入。随着B列所选内容的不同，C列可选项也会随之变化。2.2.2知识要点分析4.多级菜单的下拉式输入我们通过单元格区域命名，并使用indirect函数实现了三级菜单的下拉式录入。以此类推，更多级的下拉菜单可以很方便的实现！2.2.3操作步骤1.完善表格2.2.3操作步骤2.完善新加字段的多级输入设置建立一张“学院及专业”工作表。2.2.3操作步骤2.完善新加字段的多级输入设置按照前面的方法完成“学院”“专业”两个字段的多级菜单输入设置。再见！第3章公式与函数主要内容3.1制作九九乘法表3.2学生成绩表的处理3.3完善学生信息表3.4

100以内的加减法测试3.5制作货物出库单3.6分析销售数据3.7随机生成考试座位号3.1制作九九乘法表1.问题描述

制作如图所示九九乘法表。2.知识要点数据运算Excel的数据计算是通过公式实现的，它可以对工作表中的数据进行算术运算、比较运算和文本连接运算等。3.1制作九九乘法表算术运算符运算符意义举例运算结果+加法运算2+57-减法运算取负数2-7-2-5-2*乘法运算2.1*1.53.15/除法运算13/52.6%百分百运算13%0.13^幂运算2^33^0.581.73205083.1制作九九乘法表比较运算符运算符意义举例运算结果<

小于2<5"ab"<"abc"TrueTrue>

大于2>5"123">"99"FalseFalse<=小于等于2<=（5+3）/2"a"<="a"&"b"TrueTrue>=大于等于5-3>=2"abc">="abcd"TrueFalse=等于2=(4\2)"abc"="abc"TrueTrue<>

不等于2<>3^0.5"abc"<>"ABC"TrueTrue3.1制作九九乘法表字符串运算符

字符串的运算只有连接运算。其运算符是“&”。它表示把两个字符串首尾连接成一个字符串。例如："学习"&"Excel"，其运算结果为“学习Excel”。3.1制作九九乘法表3.1制作九九乘法表单元格引用

单元格引用的作用在于标识工作表中的单元格或单元格区域，并通过单元格引用来标识公式中所使用的数据地址，这样在创建公式时就可以直接通过单元格引用的方法来快速创建公式并完成计算，提高计算数据的效率。3.1制作九九乘法表相对引用。

相对引用是指单元格的公式中直接使用了其他单元格的地址。绝对引用。

绝对引用是指单元格公式中引用其他单元格地址的行或列前面添加了“$”符号。绝对引用无论是被移动还是被复制时，其引用的地址都不改变。3.1制作九九乘法表3.方案步骤1.制作表头2.编写3.使用字符函数3.2学生成绩表的处理1.问题描述

计算如图所示的表格中红色框中的各个数据。2.知识要点

函数的使用

函数实际上是一些预定义的公式，运用一些称为参数的特定的顺序或结构进行计算。Excel2016提供了财务、统计、逻辑、文本、日期与时间、查找与引用、数学和三角、工程等多类函数，可大大简化公式的输入过程，只需设置函数相应的参数即可进行计算。3.2学生成绩表的处理基本函数求和函数SUM()求平均值函数AVERAGE()最大值函数MAX()最小值函数MIN()计数函数COUNT（）条件计数函数COUNTIF()条件函数IF()3.2学生成绩表的处理3.方案步骤完善表头计算相关数据统计各类人数3.2学生成绩表的处理1.问题描述

在原“学生信息表”的基础上增加“身份证号”、“出生日期”和“计算机入学测试成绩”等三个字段。如图所示。3.3完善学生信息表同时还要求：

按“计算机入学测试成绩”降序排列。

按“籍贯”分类统计人数。

只显示“河南省”考生“入学测试成绩”在60分以上的学生。2.知识要点字符函数LEFT（）函数。2.RIGHT（）函数3.MID（）函数4.AND（）函数5.OR（）函数6.VLOOKUP（）函数3.3完善学生信息表数据引入和分列1.数据引入。EXCEL2016提供了强大的“PowerQuery”,即数据查询功能，能帮助我们不用费时费人力粘贴、不用多数人接受不了的VBA、不用专业的SQL查询语句，只需要鼠标点下去，就能完成多工作簿的数据汇总。3.3完善学生信息表2.分列

在Excel中有一个功能叫分列，它的功能主要体现在对数据的有效拆分。假如需要统计一下公司员工的年龄，人事部门在做员工档案的时候只记录了员工的身份证号，没有记其出生日期，有没有什么办法可以快速统计出员工的出生日期？Excel提供了分列功能来快速的从身份证号码里拆分出他们的出身日期3.3完善学生信息表数据排序1.单条件排序。

对工作表中的一列数据进行排序，可使用单条件排序功能。2.多条件排序。

工作表中多列的内容进行排序，可使用多条件排序。3.3完善学生信息表分类汇总

分类汇总是Excel中最常用的功能之一，它能够快速地以某一个字段为分类项，对数据列表中的数值字段进行各种统计和计算，如求和、计数、平均值、最大值、最小值、乘积等。

3.3完善学生信息表3.方案步骤完善表格完善数据按“成绩”排序按“籍贯”分类统计人数筛选3.3完善学生信息表3.4

100以内的加减法测试1.问题描述

设计一个如图所示100以内的加减法运算自动判分表。要求系统自动产生若干道算术题，由学生回答。系统要给出未答题数量并自动显示统计出的正确与错误的成绩。3.4

100以内的加减法测试2.知识要点随机函数

RAND()函数：返回一个大于等于0,小于1的均匀分布的随机数。

RANDBETWEEN(M,N)

函数：返回一个[M,N]范围内均匀分布的随机整数。3.4

100以内的加减法测试2.“宏”

宏是一个指令集，是一系列的命令和函数，用来告诉Excel完成用户指定的动作。宏类似于计算机程序，但它是完全运行于Excel之中的，可以使用“宏”来完成枯燥的、频繁的重复性工作。且“宏”完成动作的速度比用户自己要快很多。3.4

100以内的加减法测试3.方案步骤制作表格出题自动评判重复出题3.5制作货物出库单1.问题描述

制作如图所示的“出库单”，要求：“货号”字段由下拉框选择输入。当“货号”字段的值输入完成后，“商品”、“单价”字段的值自动填充。“数量”字段应该是大于等于1的整数。“小计”字段的值自动填充，同时“合计”的值不能超过999999.99。将“小计”字段各个位的值拆开，自动填充到“金额”项的相应字段。3.5制作货物出库单2.知识要点1.单元格区域命名2.名称的命名规则3.名称的使用3.5制作货物出库单3.方案步骤

制作表格

单元格设置“货号”、“商品”和“单价”字段的设置“数量”字段的设置“小计”字段的计算

“合计”字段的计算

“金额”的设置

3.6分析销售数据1.问题描述

本案例是一个公司的销售明细，通过常用统计函数的使用可对销售数据进行分析，如平均销售业绩、销售排名、销售业绩优秀、销售业绩达标、销售业绩段的人数等。如图所示。3.6分析销售数据2.方案步骤1.汇总销售数据总排名

在I3单元格计算出每个员工的总销售额

在J3单元格对数据进行降序排位

在K3单元格对数据进行百分比排位

在L3单元格设置判断条件，

在P3单元格、P4单元格、P5单元格，求出每月的平均销售额、每月的优秀率、每月的达标率。3.6分析销售数据在P6单元格、在P7单元格、在P8单元格，求出每月第一名、第二名、第三名的销售额。在P9单元格、P10单元格、P11单元格，求出每月倒数第一名、倒数第二名、倒数第三名的销售额。在P12单元格求出每月的中值数，即销售额居中的数据。在P13单元格求出销售额出现频率最多的数据2.对每个部门的情况进行统计分析制作相应的表头。在P16单元格向下填充计算出每个部门的人数。在Q16单元格计算出每月每个部门的平均销售额在W16单元格求出每个部门上半年的平均销售额3.6分析销售数据3.统计销售段人数在Q22单元格统计出一月份到六月份6万以下的人数在Q23单元格统计出一月份到六月份6到7万以下的人数。在Q24单元格统计出一月份到六月份7到8万以下的人数。在Q25单元格统计出一月份到六月份8到9万以下的人数。在Q26单元格统计出一月份到六月份9万以上的人数。3.7随机生成考试座位号

在各种考试中，考生需要对照自己的准考证号，对号入座，这时需要为考生分配考试座位号。如图所示为学生的原始记录，共有379条记录。3.7随机生成考试座位号1.问题描述本例中，我们先用随机函数生成0到1之间的随机数，再利用RANK函数生成随机名次，然后用公式为每个考场随机分配50名考生。3.7随机生成考试座位号2.知识点讲解INDEX(

)函数返回表格或区域中的值或值的引用。函数INDEX()有两种形式，数组形式和引用形式。数组形式通常返回数值或数值数组；引用形式通常返回引用。3.7随机生成考试座位号3.方案步骤在C1和D1单元格分别输入“随机”和“随机名次”。在C2单元格输入公式“=RAND()”并向下填充到C380单元格。在D2单元格输入公式“=RANK(C2,C$2:C$380)”并向下填充到D380单元格。如图3-100所示。3.7随机生成考试座位号4.在F1单元格输入“考号”，在G1：N1单元格分别输入第一考场到第

八

考场（共379考生，需8个考场）。5.在F2，F3单元格输入1和2，选择这两个单元格后向下填充到50（假设

每场50个考生）。6.在G2单元格输入公式并向下向右填充到N51单元格。完成考生座位号分配。如图所示。G2单元格输入的公式含义是在G2单元格同时显示学生的“姓名”和“学号”。3.7随机生成考试座位号再见！第4章数据分析基础主要内容4.1数据筛选4.2使用条件格式标识数据4.3数据透视表4.4合并运算4.5模拟分析

筛选数据列表的意思就是将不符合用户特定条件的行隐藏起来，这样可以更方便的让用户对数据进行查看。Excel提供了两种筛选数据列表的命令。4.1

数据筛选自动筛选“自动筛选”一般用于简单的条件筛选，筛选时将不满足条件的数据暂时隐藏起来，只显示符合条件的数据。高级筛选

如果条件比较多，可以使用“高级筛选”来进行。使用高级筛选功能可以一次把我们想要看到的数据都找出来。4.1

数据筛选

条件格式，可以根据单元格内容对单元格应用条件格式，从而使单元格的外观与众不同。条件格式是用于可视化数值型数据的有用工具。使用Excel中的条件格式功能，可以预置一种单元格格式，并在指定的某种条件被满足时自动应用于目标单元格。可以预置的单元格格式包括边框、底纹、字体颜色等。此功能可以根据用户的要求，快速对特定单元格进行必要的标识，以起到突出显示的作用。4.2使用条件格式标识数据1.条件格式简介

条件格式功能允许以单元格的内容为基础，选择性地或自动地应用单元格格式。

条件格式可以方便地快速识别错误的单元格或特定类型的单元格。

可以使用某种格式来轻松地标识特定的单元格。要对单元格或区域应用条件格式规则，可首先选定单元格，然后使用“开始”|“样式”|“条件格式”下拉列表中的其中一个命令来指定某个规则。4.2使用条件格式标识数据2.使用图形的条件格式Excel中用于显示图形的三个条件格式选项：数据条，色阶和图标集。这些条件格式类型，有助于更好的可视化区域内的数值。1、数据条

数据条条件格式可直接在单元格中显示水平条。水平条的长度取决于单元格中的值与该区域内其他单元格的值的相对比例。4.2使用条件格式标识数据2、色阶

色阶可以让表格数据更直观。色阶是指在一个单元格区域中显示双色渐变或三色渐变，颜色的底纹表示单元格中的值，并且渐变颜色能够随数据值的大小而改变。3、图标集

另一个条件格式选项是在单元格中显示图标。所显示的图标取决于单元格的值。4.2使用条件格式标识数据3.创建基于公式的规则Excel的条件格式功能非常强大，但有时它可能也无法完成所需操作。幸运的是，可以通过编写条件格式公式来扩展它的功能。

要指定基于公式的条件格式，首先选择单元格，然后选择“开始”|“样式”|“条件格式”|“新建规则”。这时将显示“新建格式规则”对话框。在该对话框中单击“使用公式确定要设置格式的单元格”规则类型，然后即可指定公式。4.2使用条件格式标识数据4.使用条件格式管理规则复制含有条件格式的单元格删除条件格式定位含有条件格式的单元格4.2使用条件格式标识数据

数据透视表可能是Excel中技术最复杂的组件，然而只需要单击几下鼠标，就能以数十种不同的方式切分数据表，并得出你希望得到的任何汇总类型。1.认识数据透视表

数据透视表在本质上是一个从数据库生成的动态汇总报表。这里所指的数据库既可以位于一个工作表中，也可以位于外部数据文件中。在创建数据透视表后，既可以按照任何想到的方式重新排列信息，也可以插入特殊的公式以执行各种新的计算。4.3数据透视表

创建数据透视表时，通常需要汇总一个或多个数据字段。相反的，类别字段的值将会在数据透视表中显示为行、列或筛选项。

字段：从源列表或数据库中的字段衍生出的数据分类。

组：一组被视为单个项的项。

项：字段中的元素，在数据透视表中作为行或列的标题显示。

汇总函数：用来对数据字段中的值进行合并的计算类型。4.3数据透视表行标签：在数据透视表中拥有行方向的字段。列标签：数据透视表中具有列方向的字段。表筛选：数据透视表中具有分页方向的字段。∑数值：数值字段提供要汇总的数据值。总

计：用于显示数据透视表中一行或一列中所有单元格的总和的

行或列。4.3数据透视表2.创建数据透视表

创建数据透视表的操作比较复杂，为了便于用户使用，Excel提供了数据透视表和数据透视图向导，在该向导的指导下，用户只要按部就班的进行操作，就可以轻松的完成数据透视表的制作过程。4.3数据透视表指定数据指定数据透视表的放置位置。指定数据透视表布局。指定数据透视表布局。选择或填入“自动”选项卡中的“起始”和“终止”日期，4.3数据透视表3.更改值汇总方式

对数据透视表中的数据汇总时最常使用求和方法。但是，也可以使用“值字段设置”对话框中指定的其他许多不同汇总方法来显示数据。要显示此对话框的方法是，右击数据透视表中的任何值，然后从快捷菜单中选择“值字段设置”。4.3数据透视表4.更改值显示方式

在创建数据透视表后，用户可以根据需要设置值的不同显示方式，以方便对数据进行分析。要以不同的形式显示数值，可使用“值显示方式”选项卡上的下拉控件。有很多选项可供选择，其中包括作为统计或分类汇总的百分比。4.3数据透视表5.切片器

利用切片器可以进行快速筛选，还可以指定当前筛选状态，从而轻松、准确地了解筛选的数据。切片器提供可用于单击筛选表格数据或数据透视表数据的按钮。除快速筛选外，切片器还可以指示当前筛选状态，以便了解筛选后的数据透视表中显示哪些内容。4.3数据透视表6.数据透视表显示报表筛选页

利用数据透视表的“显示报表筛选页”功能，就可以创建一系列链接在一起的数据透视表，每一张工作表显示报表筛选字段中的一项。4.3数据透视表Excel中的“合并计算”功能可以汇总或合并多个数据源区域中的数据。合并计算的数据源区域可以是同一个工作表中的不同表格，也可以是同一工作簿中的不同工作表，还可以是不同工作表中的表格。1.按类别合并

如图所示有两张结构相同的数据表，利用合并计算可以轻松地将这两张表进行合并汇总。4.4合并运算2.创建分户报表

合并计算可以按类别进行合并，如果引用区域的行、列方向均包含了多个类别时，则可以利用合并计算功能将引用区域中的全部类别汇总到同一表格上并显示所有明细。4.4合并运算3.有选择地合并计算如图所示列出了南京天水加油站的3个月的信息，如果要汇总0号柴油的“销售数量”和“销售金额”，可以用“合并计算”的功能。尽管在“销售数量”和“销售金额”两列之间还包含其他文本型数据列，“合并计算”仍然可以有选择的进行计算。4.4合并运算

“模拟运算表”实际上是对工作表中的一个单元格区域进行模拟运算，它可以反映一个计算公式中某些数值的变化对计算结果的影响。模拟运算表为同时求解某一运算中所有可能的变化值提供了捷径，由于它可以将不同的计算结果以列表的方式同时显示出来，因而便于查看、比较和分析数据。4.5

模拟分析Excel中模拟运算表有两种形式，即“单变量模拟运算表”和“双变量模拟运算表”。

“单变量模拟运算表”：主要用来分析当其他因素不变时，一个参数的变化对目标值的影响。例如要计算一笔贷款的分期付款额，可用财务函数PMT来计算，而要分析不同利率对每月贷款的影响时，则需要用单变量模拟运算。4.5

模拟分析

“双变量模拟运算表”：用来分析当其他因素不变时，两个参数的变化对目标值的影响。双变量模拟运算表中的两个参数使用同一个公式，这个公式必须引用两个不同的单元格。4.5

模拟分析再见！第5章规划求解主要内容5.1规划求解基础5.2求解取料5.3求解任务分配5.1规划求解基础规划求解是MicrosoftExcel加载项程序，可用于模拟分析。使用“规划求解”查找一个单元格（称为目标单元格）中公式的优化（最大或最小）值，受限或受制于工作表上其他公式单元格的值。“规划求解”与一组用于计算目标和约束单元格中公式的单元格（称为决策变量或变量单元格）一起工作。“规划求解”调整决策变量单元格中的值以满足约束单元格上的限制，并产生对目标单元格期望的结果。5.1规划求解基础规划求解的特点：

有多个可以调整的单元格。可以通过更改其他单元格来确定某个单元格的最大值或最小值。可以指定可调整单元格可能的数值约束。一个问题可以有多个解。5.1规划求解基础1.规划求解问题的特点

规划求解是一组命令的组成部分，这些命令有时也称作假设分析工具，即该过程通过更改单元格中的值来查看这些更改对工作表中公式结果的影响，规划求解主要是为工作表中的目标单元格中的公式找到一个优化值，在保证工作表中的其他数据保持在设置的范围之内时通过改变输入值从而求出最优解。5.1规划求解基础规划问题的特点一般来讲，适合使用规划求解的问题具有如下特点：目标单元格的解都有单一的目标，如求运输的最佳路线，求值班人员的最佳安排时间表，求产品的最低成本等等，要求求目标函数的最优解。对于目标单元格的解存在有明确的可以用不等式表达的约束条件和限制。3.可以把问题的表达描述为：一组约束条件及限制（不等式），一个目标方程。4.输入值直接或间接地影响约束条件和目标单元格的解。5.利用Excel可以简单的求得问题满足约束条件和限制求得的目标最优解。5.1规划求解基础2.规划求解问题的组成部分

在Excel中，一个规划求解问题由以下三个部分组成。可变单元格：可变单元格是实际问题中有待解决的未知因素。目标函数：目标函数表示规划求解要达到的最终目标，如求最大利润、最短路径、最小成本、最佳产品搭配等。约束条件：约束条件是实现目标的限制条件，规划求解是否有解与约束条件有密切的关系，它对可变单元格中的值起着直接的限制作用。5.1规划求解基础3.规划求解问题的步骤使用数值与公式建立工作表。确保单元格格式符合逻辑性，例如，如果不能生产半个产品，则需要将这些单元格格式设置为不能含有小数值。选择“数据”|“分析”|“规划求解”命令，将显示“规划求解参数”对话框。5.1规划求解基础4.指定目标单元格5.指定含有可变单元格的区域6.指定约束条件7.根据需要更改规划求解选项8.单击“求解”按钮，使用规划求解解决问题。5.1规划求解基础4.在Excel中调用规划求解工具

“规划求解”工具是Excel的加载宏，在默认安装的MicrosoftExcel2016中需要加载后才能使用，加载该工具的步骤为：

单击“文件”选项卡下的“选项”，在弹出的“Excel选项”对话框中单击左侧列表中的“加载项”选项卡，然后在右下方“管理”组合框中选择“Excel加载项”，并单击“转到”按钮，如图所示。5.1规划求解基础

在弹出的“加载宏”的对话框中勾选“规划求解加载项”复选框，并单击“确定”按钮完成操作，如图所示。在Excel主界面点击“数据”选项卡，在“分析”选项组中，显示“规划求解”，如图所示。5.2求解取料问题1.问题描述

某公司将4种不同含硫量的液体原料（分别记为甲、乙、丙、丁）混合生产两种产品（分别记为A和B），按生产工艺的要求，原料甲、乙、丁必须首先倒入池中混合（也可一直用到一种原料），混合后的液体再与原料丙混合生成A和B。已知甲、乙、丙、丁的含硫量分别是3%，1%，2%和1%，进货价格分别为7、16、10、15（千元/吨）；产品A和B的含硫量分别不能超过2.5%和1.5%，售价分别为9和15（千元/吨）。根据市场信息，甲、乙、丙的供应没有限制，原料丁的供应量最多为50吨，产品A和B的市场需求分别为100吨和200吨。应如何安排生产产品A和产品B的数量，使得在最大程度降低总成本同时计算丁原料的实际需求？5.2求解取料问题2.知识要点SUMPRODUCT()函数

功能：用来在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。5.2求解取料问题3.制作步骤在D9和D10单元格分别输入公式：“D9=SUM(D2:D5)”和“D10=SUM(E2:E5)”。在E9和E10单元格分别输入公式：“E9=SUMPRODUCT(D2:D5,B2:B5)/B9”和“E10=SUMPRODUCT(B2:B5,E2:E5)/B10”。5.2求解取料问题4.B12单元格实际丁原料的使用量，输入公式：

“B12=SUM(D5:E5)”。5.B13单元格为总成本，设定为目标单元格输入公式：“B13=SUMPRODUCT(C2:C5,D2:D5+E2:E5)”5.2求解取料问题6.选中B13单元格，打开“规划求解参数”对话框，在“设置目标单元格”文本框中选择B13单元格，选中“最小值”单选按钮，在可变单元格文本框中选择D2:E5单元格区域。5.2求解取料问题7.单击“添加”按钮，添加约束条件：D2:E5>=0，产量不能为负数D9:D10=B9:B10，实际产量与需求一直E9:E10<=C9:C10,生产出来的成品的含硫量不能超过上限B12<=50，丁原料的供应是有限定的。“选择求解方法”对应的文本框选择单线性规划5.2求解取料问题8.单击“求解”按钮开始运算，并显示运算结果，如图所示。然后单击“确定”按钮保存此结果，结果如图所示。5.3求解任务分配1.问题描述

某医院新建一个病房，需要配备护士，周一到周日分别最少需要16、12、12、15、14、16、14人，按规定一个护士一周要连续上班5天，医院病房给配备了20名护士，是否够用？5.3求解任务分配2.知识要点

护士是每周上班5天，如果周一是第一天上班，则要连续上到周五，如果周四第一天上班，则上星期四、五、六、日、一，依次类推。例如计算星期一护士上班人数，除了计算星期一安排分配上班护士人数，还需要统计从星期四到星期日分配上班护士的人数。5.3求解任务分配3.制作步骤

根据已知条件建立关系表格，在B1单元格输入星期一，向右填充到H1单元格，建立一周。护士编号为护士1到护士20，A2单元格输入护士1，向下填充到A21，建立20格护士的名单，并录入每天需要的护士数，如图所示5.3求解任务分配1.B2：H21单元格区域用于记录护士实际上班情况，用0表示未分配，1表示分配任务，且是该护士本周第一次上班的时间，此区域作为规划求解的可变单元格。2.J列用于统计护士是否有上班，根据任务分配问题的特性，每位护士最多只能上班5天，J2单元格公式填充至J21单元格。J22=SUM(B2:H2)。5.3求解任务分配3.第22行用于计算每天上班的人数，B22公式为“=SUM($B$2:B21,E2:$H$21)”向右填充到E22单元格。F22的公式为“=SUM(B2:F21)”。向右填充到H22单元格。J22单元格公式为“=SUM(B2:F21)”。5.3求解任务分配4.

选中J22单元格，打开“规划求解对话框”，在“设置目标单元格”文本框中选择J22，选中最小值，设定“可变单元格”为B2：H21。添加约束条件如图5-16所示。选中“使无约束变量为非负数”。选择求解方法为“单纯线性规划”。5.3求解任务分配5.单击“求解”，显示如图所示，单击“继续”按钮。5.3求解任务分配6.显示如图所示，单击“确定”按钮。显示如图所示，给出求解结果。再见！第6章数据可视化6.1Excel-图表基础Excel-图表基础借助图表的方式进行数据的可视化其根本目的是：使得人们对信息的认知更加直观化和图形化。人类对“形象化”信息的接受能力要远高于“原始数据”。计算机恰恰相反“原始数据”-计算机-“形象化”-人可视化的意义Excel-图表基础分类轴表示自变量，一般为X轴；数据轴表示因变量，一般设为Y轴。在一些复杂情况下，可以使用多个数据轴分类轴和数据轴Excel-图表基础当前Excel共提供多种内置的图表类型。常见的图表类型Excel-图表基础在计算机的世界中，几乎左右的功能性应用软件其基本操作都是类似的：选定要操作的对象-选定单元格区域。确定要施加在操作对象的操作-选定图表类型给出上述操作的其他必须信息-指定一些项目。创建图表的基本步骤6.2Excel-柱形图Excel-柱形图可视化问题Excel-柱形图统计五级成绩的人数，涉及到了判断-COUNTIF函数需要根据结果数据插入柱形图要点与思路6.3Excel-股价图Excel-股价图可视化问题Excel-股价图股价图包含四种子类型盘高-盘低-收盘图开盘-盘高-盘低-收盘图成交量-盘高-盘低-收盘图成交量-开盘-盘高-盘低-收盘图注意工作表中数据要根据上述顺序安排要点与思路6.4Excel-条形图对比选票Excel-条形图对比选票可视化问题Excel-条形图对比选票条形图又称为横向柱形图，他相对于柱形图有其独特优势条形图由于是横向的，当图的分类较多，而且分类字段名称又较长时，比较适合分类轴是垂直轴，有更多空间显示分类名称。要点与思路6.5Excel-背景饼图Excel-背景饼图可视化问题Excel-背景饼图圆饼图是用扇形面积，也就是圆心角的度数表示数量。圆饼图主要用来表示整体与局部之间的数量关系，即分组数据的内部构成比例一般而言，饼图仅有一个要绘制的数据系列，且要绘制的数值没有负值或零值。要点与思路6.6Excel-自动缩放列表Excel-自动缩放列表可视化问题Excel-自动缩放列表先创建一个标准图表然后进行一些调整，使得该图表在添加新的数据时可以自动扩大，删除数据时自动缩小。要点与思路6.7Excel-自动标识图表中的最大值和最小值Excel-自动标识图表中的最大值和最小值可视化问题Excel-自动标识图表中的最大值和最小值动态的标识出最大值和最小值，会使图表显得更直观、更形象。动态标识依赖于对最大、最小值的自动计算要点与思路第7章

VBA应用主要内容7.1VBA基础7.2库存查询7.3报名结果统计7.1VBA基础7.1.1VBA的开发环境VisualBasic编辑器VBE（VisualBasicEditor）提供了完整的开发和调试VBA代码的环境。7.1.1VBA的开发环境VBA是VisualBasicforApplications的简称。其意思是开发环境被整合到了某个应用程序的VisualBasic语言。

VBA就是一种工具，一种可以创造工具的工具。

VBA提供了给你充分的自由，做几乎任何其他编程语言或者环境能做的事情，避免重复的手动劳动。

VBA使用的是VB6的语法，采用的是COM组件库。可以方便能操作Office的各种功能。7.1.1VBA的开发环境VBA能干什么？如果你想避免重复劳动，或者需要多个文档协作，VBA是最好的选择。理论上VBA可以操作任何WindowsAPI（WindowsApplicationProgrammingInterface，Windows应用程序编程接口），和任意用COM封装的组件。它们可以使自身的功能和办公紧密结合。7.1.1VBA的开发环境VBA不能干什么？

VBA的功能取决于其库。如果没有库那么VBA也不能帮你。此外，如果你开发的项目过于庞大，则应该换一门更专业的编程平台（比如.Net、Python等等），以便更好的组织代码和查错。

VBA比较适合短小的，与工作任务很直接的问题。7.1.1VBA的开发环境可以使用3种方法在Excel中打开VBE窗口。方法一：右击工作表标签，在弹出的快捷菜单中选择“查看代码”项。方法二：单击“开发工具”选项卡“代码”选项组中的“VisualBasic”按钮。方法三：在工作表中使用【Alt+F11】组合键。1.打开VBE编辑窗口7.1.1VBA的开发环境2.VBE窗口的组成菜单栏工具栏工程资源管理器属性窗口属性窗口7.1.1VBA的开发环境3.在VBE中编写代码在VBE中编写代码实际上就是写一个VBA子过程，子过程名就是宏名。子过程定义格式为：Sub子过程名（[<形参列表>]）

[<语句组>]EndSub7.1.1VBA的开发环境3.在VBE中编写代码通过“开发工具”|“代码”|“VisualBasic”或者打开“宏”的编辑窗口。也可以打开一个新的窗口。7.1.2Sub和Function“Sub”这个关键字标志着一个程序过程的开始，“EndSub”则表示其结束。例如：SubTest()MsgBox"Hello,World"EndSub7.1.2Sub和FunctionFunction被叫做自定义函数，是我们自己写的一个函数程序。它和Sub除了关键字的差别外，Function与Sub最大的不同就在于Function需要返回一个值，而Sub不需要。例如：FunctionWeekDayName(WAsInteger)AsStringSelectCaseWCase1WeekDayName="Monday"Case2WeekDayName="Tuesday"Case3WeekDayName="Wednesday"Case4WeekDayName="Thursday"Case5WeekDayName="Friday"Case6WeekDayName="Saturday"Case7WeekDayName="Sunday"EndSelectEndFunction7.1.3变量与类型常量①字面常量：实际值数值常量：1563.141.25E10字符串常量："学生成绩管理系统""Office"布尔常量：TureFalse7.1.3变量与类型常量②符号常量：用标识符表示常量ConstPIAsSingle=3.14159程序执行语句s=r*r*PI时，自动将PI用3.14159替换。③系统常量：系统预先定义的常量vbOK代表确定vbCancel代表取消。7.1.3变量与类型变量变量是用来存放程序运行过程中用到的各种中间数据。它是内存中存储单元的符号地址，是内存中一个命名的存储单元。在整个程序的执行过程中，变量的值是可以变化的，也就是说存储单元中存放的信息是可以改变的。但在程序执行的每个瞬间，变量的值都是明确的、已知的。变量是指在程序运行期间取值可以变化的量。一个变量有3个基本要素：变量名、变量的数据类型和变量值。7.1.3变量与类型变量①变量的命名规则变量名必须以英文字母或汉字为起始字符。变量名可以包含字母、汉字、数字或下划线，但不能包含空格和标点符号。变量名的长度不能超过255个字符，不区分大小写。变量名不能使用VBA的关键字。7.1.3变量与类型变量②变量的声明显式变量声明Dim变量名[As类型名][,变量名[As类型名],……]例如DimscoreAsIntegerDimaverAsSingle,strAsString,flagAsBoolean,w7.1.3变量与类型变量②变量的声明隐式变量声明没有显式声明的变量（隐式变量）的数据类型是变体型。7.1.3变量与类型数据类型2^15-12^31-13.402823*10^387.1.3变量与类型数组数组是由一组具有相同数据类型的变量组成的集合。显式声明一维数组。Dim数组名([下标下界To]下标上界)[As数据类型]说明：下标下界缺省值为0，如果非0，要使用To选项。Dima(5)AsInteger整型数组a，包含6个元素，a(0)、a(1)、a(2)、a(3)、a(4)和a(5)，下标为0~5Dimb(1To5)AsSingle单精度数组b，包含5个元素，b(1)、b(2)、b(3)、b(4)和b(5)，下标为1~5。7.1.3变量与类型运算符--算术运算符7.1.3变量与类型运算符--关系运算符7.1.3变量与类型运算符--逻辑运算符7.1.3变量与类型运算符--字符串运算符

＋：要求两个运算符的类型一致；&：允许两个运算符的类型不一致，但其结果一定是字符型数据。7.1.3变量与类型运算符--字符串运算符“&”强制两个表达式作为字符串连接。

"中国"&"北京" '结果为"中国北京""123"&"456" '结果为"123456"123&456 '结果为"123456““+”运算符用来连接两个字符串（注意：加法）"123"+"456" '两个字符串连接，结果为"123456"123+456 '两个数相加，结果为579例如：7.1.4对象面向对象方法中的对象由一组属性和一组行为构成，是系统中用来描述客观事物的一个实体，它是用来构成系统的一个基本单位。对象的几个特性：方法：表示一种动作。函数：表示一种具有返回值的功能。属性：表示对象所带有的某种信息。7.1.5COM组件COM是ComponentObjectModel的简称，在Office中使用的对象模型是COM。你可以把软件看作是一个机器，而组件就是机器的一个个零件。组件之间相互协作，共同完成任务。7.1.6几个完整的VBA程序例7.1对一个单元格区域内内红色的数字求和。7.1.6几个完整的VBA程序例7.1对一个单元格区域内内红色的数字求和。需明确以下几个方面的问题：（1）如何表示一个单元格范围，并以此作为问题的输入，即到底要对哪些单元格应用这个自定义的求和。（2）如何遍历一个个的单元格。（3）如何获取一个单元格的值。（4）如何判断一个单元格的文字是红色的。（5）如何求和。（6）如何输出。7.1.6几个完整的VBA程序例7.1对一个单元格区域内内红色的数字求和。（2）如何遍历一个个的单元格。关于遍历一个像Selection这样的集合，VBA提供了一个很方便的语法，叫作ForEach…Next。其语法为：ForEach<obj>In<Collection>'DoSomethingwith<obj>Next7.1.6几个完整的VBA程序例7.1对一个单元格区域内内红色的数字求和。（4）如何判断一个单元格的文字是红色的。在VBA中，对象r{Range}有一个Font{Font}属性，表示字体的所有信息，例如边框、加粗、斜体等，当然，还有文字颜色Color{Double}。颜色是由RGB组成的，红色对应的是R=255，G=0，B=0。在VBA中写作RGB(255,0,0)。也就是说如果r.Font.Color=RGB(255,0,0)为真，那就说明当前单元格中的数据就是红色的。当然VBA还提供了一些颜色常量来方便书写，如红色就是vbRed。所以上面的代码等价于r.Font.Color=vbRed。7.1.6几个完整的VBA程序例7.1对一个单元格区域内内红色的数字求和。Sub计算红色单元格数据和()DimsumAsSingleDimrAsRangesum=0ForEachrInSelectionIfr.Font.Color=vbRedThensum=sum+r.ValueNextMsgBoxsumEndSub7.2库存查询7.2.1案例说明有一个库存文件，现在要求从库存表中查询到包含某个关键字的设备的库存情况。7.2.2知识要点分析从“设备名称”字段中遍历查询，寻找包含指定关键字的设备名称。可以使用InStr()函数解决这个问题。

如果有包含的设备名称，那么，我们就将该设备名称和其对应的“库存量”的值写到“查询结果”工作表的指定位置，并且统计共找到了多少个包含指定关键字的设备名称。7.2.3操作步骤创建一个名称为“Check_Stock”的宏，将代码写在其中。

为了方便使用，我们可以在表格上插入一个按钮控件，将其指定给该宏。SubCheck_Stock()DimS$,J&,L$,F_Key$,M%DimEP_Name$,Total&Total=3DoTotal=Total+1IfWorksheets("查询结果").Range("A"&Total).Value<>""ThenWorksheets("查询结果").Range("A"&Total).Value=""Worksheets("查询结果").Range("B"&Total).Value=""Worksheets("查询结果").Range("C"&Total).Value=""ElseExitDoEndIfLoopF_Key=Range("C1").ValueM=1:S="B":J=2:Total=3DoJ=J+1L=S&JEP_Name=Worksheets("库存表").Range(L).ValueIfEP_Name=""ThenExitDoIfInStr(EP_Name,F_Key)<>0ThenTotal=Total+1:Worksheets("查询结果").Range("A"&Total).Value=Worksheets("库存表").Range("A"&J).ValueWorksheets("查询结果").Range("B"&Total).Value=EP_NameWorksheets("查询结果").Range("C"&Total).Value=Worksheets("库存表").Range("D"&J).ValueEndIfLoopWorksheets("查询结果").Range("B2").Value="共找到"&Total-3&"种产品"EndSub7.3报名结果统计7.3.1案例说明将学生参赛报名简表文件，汇总到报名信息汇总表中。同时还要将指导教师和参赛队联系人的姓名和电子邮箱地址统计到“E-mail”工作表中。7.3.2知识要点分析在“汇总表”中添加一个按钮，具体执行时，要先打开一个提取文件对话框，选择需要提取信息的报名简表文件，然后将报名简表中的相关信息提取出来，写入汇总表的相应位置。同时还要将报名简表中的联系人和指导教师的“姓名”和“邮箱”信息提取出来，写入“E_Mail”表中。7.3.3操作步骤我们创建一个名称为“collect”的宏，将以上分析的处理的代码写在Subcollect()下面。为了方便使用，我们可以在表格上插入一个按钮控件，将其指定给Subcollect()事件。7.3.3操作步骤由于比赛允许一个指导教师指导多支参赛队伍，因此，在我们统计指导教师和参赛队联系人的姓名和电子邮箱地址时可能会出现重复的情况，请大家自己试着写一个VBA代码删除上面说到的重复数据。同时，也请大家试着看能不能通过Excel提供的系统功能和函数来解决这个问题。再见！第8章

综合应用主要内容8.1销售数据分析8.2采购成本分析8.1销售数据分析8.1.1问题描述1.将“Excel_素材.xlsx”文件另存为“Excel.xlsx”(“.xlsx”为文件扩展名)，后续操作均基于此文件。2.命名“产品信息”工作表的单元格区域A1:D78名称为“产品信息”；命名“客户信息”工作表的单元格区域A1:G92名称为“客户信息”。8.1销售数据分析3.在“订单明细”工作表中，完成下列任务:①根据B列中的产品代码，在C列、D列和E列填入相应的产品名称、产品类别和产品单价(对应信息可在“产品信息”工作表中查找)。②设置G列单元格格式，折扣为0的单元格显示“-”，折扣大于0的单元格显示为百分比格式，并保留0位小数(如15%)。③在H列中计算每订单的销售金额，公式为“金额=单价×数量×(1-折扣)”，设置E列和H列单元格为货币格式，保留2位小数。8.1销售数据分析4.在“订单信息”工作表中，完成下列任务:①根据B列中的客户代码，在E列和F列填入相应的发货地区和发货城市(提示:需首先清除B列中的空格和不可见字符)，对应信息可在“客户信息”工作表中查找。②在G列计算每订单的订单金额，该信息可在“订单明细”工作表中查找(注意:一个订单可能包含多个产品)，计算结果设置为货币格式，保留2位小数。③使用条件格式，将每订单订货日期与发货日期间隔大于10天的记录所在单元格填充颜色设置为“红色”，字体颜色设置为“白色，背景1”。8.1销售数据分析5.在“产品类别分析”工作表中，完成下列任务:①在B2:B9单元格区域计算每类产品的销售总额，设置单元格格式为货币格式，保留2位小数;并按照销售额对表格数据降序排序。②在单元格区域D1:L17中创建复合饼图，并根据样例文件图8-1设置图表标题、绘图区、数据标签的内容及格式。8.1销售数据分析6.在所有工作表的最右侧创建一个名为“地区和城市分析”的新工作表，并在该工作表A1:C19单元格区域创建数据透视表，以便按照地区和城市汇总订单金额。数据透视表设置需与样例文件图8-2保持一致。8.1销售数据分析7.在“客户信息”工作表中，根据每个客户的销售总额计算其所对应的客户等级(不要改变当前数据的排序)，等级评定标准可参考“客户等级”工作表;使用条件格式，将客户等级为1级~5级的记录所在单元格填充颜色设置为“红色”，字体颜色设置为“白色，背景1”。8.为文档添加自定义属性，属性名称为“机密”，类型为“是或否”，取值为“是”。8.1.3方案步骤要求1的操作步骤如下:（1）打开“Excel_素材.xl