信息技术 课件 单元2 Excel 2016电子表格处理-

单元2Excel2016电子表格处理汇报内容02项目5道克图文公司员工考核数据处理03目录项目6开支明细表数据处理01项目4数据快速录入及计算项目4数据快速录入及计算学习目标课前自学课中学习课后提升学习目标1．知识目标1）了解Excel2016的功能和窗口组成。2）掌握相对引用、绝对引用、混合引用和三维引用的概念和用法。3）掌握熟练自动填充的用法。4）熟练掌握等差数列和等比数列的操作用法。5）掌握公式中的运算符类型。6）掌握数据清单中输入和编辑函数。2．能力目标1）能够使用Excel2016快速录入数据。2）能够熟练使用Excel2016输入和编辑公式。3．素养目标1）能够树立不怕困难，吃苦耐劳的精神，能够攻坚克难完成实训任务。2）能够在Excel2016快速录入数据中培养熟能生巧的意识。3）具有团队精神，能够和团队成员协商，共同完成实训任务。4）通过各类型数据自动填充任务练习，培养脚踏实地、力戒虚荣的意识。5）通过快速录入实训培养一丝不苟、精益求精的工匠精神。课前自学（一）Eecel2016快速录入1．Excel2016简介Excel2016是微软公司推出的办公自动化套装软件Office2016的主要应用程序之一，其是一个功能非常强大的电子表格应用软件，具有强大的数据计算与分析处理功能。2．工作簿、工作表和单元格基本概念工作簿是存储数据、数据运算公式以及数据格式化等信息的文件。用户在Excel中处理的各种数据最终都是以工作簿文件的形式存储在磁盘上。其默认的扩展名是.xlsx。每个工作簿通常都由多张工作表（Sheet）组成，工作表（Sheet）是一个由行和列交叉排列的二维表格，也称作电子表格。单元格（Cell）就是工作表中行和列交叉的部分，是工作表的最基本的数据单元，也是电子表格软件处理数据的最小单位。3．Excel2016的工作环境数据验证

Excel2016启动（启动方法同Word2016）后，出现如图所示的窗口，主要包括快速访问工具栏、标题栏、功能区（多个选项卡）、编辑栏（Excel2016特有）、工作表编辑区、状态栏等4．在多个区域输入相同的内容启动Excel2016，选择空白工作簿，在工作表sheet1中按ctrl健选择多个不连续的区域，松开ctrl，输入学号“’2021010101”（切换到英文状态，先输入英文的单引号’，再输入2021010101），按住ctrl健敲enter健结束。5．自动填充（1）快速录入性别（2）自动填充数字输入数字1，然后选择第一个“1”的单元格，拖拽单元格右下面的填充柄，鼠标指针变成“+”时，拖拽鼠标左键，就在下面连续的单元格中输入相同的“1”在某单元格，输入数字1，然后选择第一个“1”的单元格，拖拽单元格右下面的填充柄，鼠标指针变成“+”时，按住ctrl拖拽鼠标左键，就在下面连续的单元格中输入1、2、3、4、5……。（3）自动填日期与数字相反，直接拖拽日期的单元格填充填充柄，日期自动增1；按住ctrl拖拽鼠标左键，拖拽日期的单元格填充填充柄，日期不变。6.等差数列、等差数列等差数列，三个输入方法等比数列，两个输入方法（二）Excel2016计算公式公式是电子表格的灵魂和核心内容。每当用户输入或者修改数据之后，公式便会自动地或者在用户操作之后重新将有关数据计算一遍，并将最新结果显示在屏幕上。Excel2016中公式的包括三个部分。（1）“=”符号：表示用户输入的内容是公式不是数据（注意输入公式必须以“=”开头）。（2）运算符：用以指明公式中元素进行计算的类型。（3）参与计算的元素（运算数）。每个运算数可以是不改变的数值（常量数值）、单元格或单元格区域引用、标志、名称或工作表函数。1．公式中的运算符类型Excel2016包含四种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。（1）算术运算符：+（加号）、.（减号或负号）、*（星号或乘号）、/（除号）、%（百分号）、^（乘方）。例如，在某个单元格中输入：“=2+5^2”后回车，结果为27。（2）比较运算符：=（等号）、>（大于号）、<（小于号）、>=（大于等于号）、<=（小于等于号）、<>（不等于）。用以比较两个值，结果是一个逻辑值，不是TRUE就是FALSE。例如：在某个单元格中输入“=3<8”,结果为FALSE。（3）文本运算符：&，使用“&”加入或者连接一个或多个字符串以产生一大片文本。例如在某个中单元输入“="山东"&"商职学院"”（注意文本输入时须加英文引号）后回车，将产生“山东商职学院”的结果。4）单元格引用运算符：:（冒号）、,（逗号）、（空格）冒号（:）是区域运算符，表示一个单元格区域，是对两个引用以及两个引用之间的所有单元格进行引用。如“=SUM(B2:E2)”。逗号（，）是并集运算符，将多个引用合并为一个引用。例如，在学生成绩表.xlsx文件sheet1的F2单元格中输入“=SUM（B2:C2,D2:E2）”，结果是和输入“=SUM（B2:E2)”一样。注意，Excel2016中的并集同数学中并集概念不一样，相同的部分会计算两次。空格（）是交集运算符，只处理各单元格区域中共有的单元格中的数据。例如，在学生成绩表.xlsx文件sheet1的C8单元格中输入“=SUM(B2:C6C2:E6)”，结果是“410”，和输入“=SUM（C2:C5)”的结果一样。2．数据清单中输入和编辑公式数据清单（二维表格）中公式的运算量要用单元格地址（而不是数据），以便于复制引用公式。数据清单中公式只需要输入一次，其它公式的计算可以使用自动填充得到；也可通过事先把数据清单变成智能表格（选择数据清单，使用“插入”选项卡中的“表格”命令），所有需要填充的公式只需要输入一次。如果需要修改某公式，则先单击包含该公式的单元格，在编辑栏中修改即可；也可以双击该单元格，直接在单元格中修改，或者仍然在编辑栏中修改。3．相对引用和绝对引用（1）相对引用相对引用是指当把一个含有单元格或单元格区域地址的公式复制到新的位置时，公式中的单元格地址或单元格区域随着改变，公式的值将会依据改后的单元格或单元格区域的值重新计算。公式中没有$。（2）绝对引用绝对引用是指在公式中的单元格地址或单元格区域地址不随着公式位置的改变而发生改变。不论公式的单元格处在什么位置，公式中所引用的单元格位置都是其在工作表中的确切位置。绝对单元格引用的形式是在每一个行标及列标前加一个$符号。（3）混合引用混合引用是指单元格或单元格区域的地址表示为部分是相对引用，部分是绝对引用。如$B2、B$2。（4）相对引用与绝对引用之间的切换如果创建了一个公式并希望将相对引用更改为绝对引用（反之亦然），则先选定包含该公式的单元格，然后在编辑栏中选择要更改的引用并按F4键。每次按F4键时，Excel2016会在以下组合间切换：绝对列与绝对行（例如，$C$1），相对列与绝对行（C$1)，绝对列与相对行（$C1)以及相对列与相对行（C1)。例如，在公式中选择地址$A$1并按F4键，引用将变为A$1。再一次按F4键，引用将变为$A1，以此类推。（5）三维引用三维地址引用。在Excel中，不但可以引用同一工作表中的单元格，还能引用不同工作表中的单元格，引用格式为：[工作薄名]+工作表名！+单元格引用。例如在Book1中引用Book2的sheet1的第三行第五列，可表示为；[Book2]Sheet1!E3。函数的输入方法（1）从键盘上直接输入函数（2）使用公式选项卡的公式类别选择相应函数（3）使用智能表格进行函数输入课中学习任务2-1：各类型数据自动填充（一）任务介绍某人于2020年1月1号开始从某机构借钱，借款一年（365）天，名义借款1000，到个人账户上1000元，年利率3%，一年后到期还款1030元。若一年到期后，没钱还前面借的1030元，又借了1030还上一笔款，再过一年后又是这样操作。每次借出一笔用来还上一笔的本金和利息。以此类推，到了2120年1月1号，未还本金变成多少？，具体任务要求如下：1）正确输入各类型的数据。2）数据清单中第一、二行数据自己手工输入，其它行数据使用自动填充。（二）任务实施任务2-1：各类型数据自动填充（一）任务介绍某人于2020年1月1号开始从某机构借钱，借款一年（365）天，名义借款1000，到个人账户上1000元，年利率3%，一年后到期还款1030元。若一年到期后，没钱还前面借的1030元，又借了1030还上一笔款，再过一年后又是这样操作。每次借出一笔用来还上一笔的本金和利息。以此类推，到了2120年1月1号，未还本金变成多少？，具体任务要求如下：1）正确输入各类型的数据。2）数据清单中第一、二行数据自己手工输入，其它行数据使用自动填充。（二）任务实施任务2-2：期末考试成绩表计算（一）任务介绍期末考试后，辅导员要求小明对班级的期末成绩进行计算排名，具体任务要求如下：1）使用公式和函数对期末考试成绩表总分、平均分进行计算。2）使用rank函数对期末考试成绩表名次进行计算。（二）任务实施课后提升（一）巩固提升实训任务2-1：图文店考核信息计算（二）拓展提升实训任务2-2：班级学号的输入项目5道克图文公司员工考核数据处理学习目标课前自学课中学习课后提升学习目标1．知识目标1）了解Excel2016常用函数的种类。2）掌握常用函数的用法。3）掌握嵌套函数的计算。4）掌握在智能表格中进行函数输入和编辑的操作方法。5）掌握删除重复项的操作方法。6）掌握函数的各种录入方法。2．能力目标1）能够使用Excel2016常用函数进行公司相关信息计算。2）能够使用Excel2016函数进行公司相关考核和业务计算。3．素养目标1）能够树立不怕困难，吃苦耐劳的精神，能够攻坚克难完成实训任务。2）能够在Excel2016函数计算中培养脚踏实地、熟能生巧的意识。3）具有团队精神，能够和团队成员协商，共同完成实训任务。4）通过Excel2016函数计算中培养一丝不苟、精益求精的工匠精神。课前自学1．数学函数函

数功

能应用举例结

果INT(number)求小于number的最大整数=INT(6.5)6MOD(number,divisor)求number/divisor的余数=MOD(13,2)1ROUND(number,n)按n指定的位数四舍五入=ROUND(34.23,1)34.2SQRT(number)求number的平方根值=SQRT(25)5SUM(number1,number2,…)求若干个数的和=SUM(2,4,5)11SUMIF(Range,Criteria,Sum_range)单条件求和Range：条件的单元格区域。Criteria：需要满足的条件。Sum_range：需要求和的实际单元格区域。

SUMIFS(Sum_range,Range1,criteria1,Range2,criteria2)多条件求和Sum_range：需要求和的实际单元格区域。Range1：条件1的单元格区域。Criteria1：需要满足的条件1。Range2：条件2的单元格区域。Criteria2：需要满足的条件2。

2．统计函数函

数功

能应用举例结果AVERAGE(number1,number2,…)求参数中数值的平均值=AVERAGE(4,6)5COUNT()求参数中数值数据的个数=COUNT(4,6)=COUNT(”张三”,6)21COUNTA()求参数中数值非空数据的个数=COUNTA(“张三”,6)2COUNTIF((Range,criteria)按单个条件计数Range：条件的单元格区域。Criteria：需要满足的条件。

COUNTIF(Range1,criteria1,Range2,criteria2)多条件计数Range1：条件1的单元格区域。Criteria1：条件1。Range2：条件2的单元格区域。Criteria2：条件2。

MAX(number1,number2,…)求参数中数值的最大值=MAX(4,6)6MIN(number1,number2,…)求参数中数值的最小值=MIN(4,6)43．文本函数函

数功

能应用举例结

果LEFT(text,n)取text左边n个字符=LEFT(“ABCD”,3)“ABC”LEN(text)求text的字符个数=LEN(“ABCD”)4MID(text,n,p)从text中第n个字符开始连续取p个字符=MID(“ABCD”,3,1)“C”RIGHT(text,n)取text右边n个字符=RIGHT(“ABCD”,3)“BCD”4．日期和时间函数函

数功

能应用举例结

果DATE(year,month,day)生成日期=DATE(2007,1,2)2007-1-2DAY(date)取日期的天数=DAY(date(2007,1,2))2Year(date)取日期的年=YEAR(date(2007,1,2))2007MONTH(date)取日期的月份=MONTH(date(2007,1,2))1NOW()取现在的年月日时间=NOW()

5．逻辑函数函

数功

能应用举例结

果AND(逻辑表达式1,逻辑表达式2,逻辑表达式3,……)判断各逻辑表达式是否成立，都成立，结果为TRUE；只要有一个不成立，结果为FALSE。=AND(5>8,7<9)FALSEOR(逻辑表达式1,逻辑表达式2,逻辑表达式3,……)判断各逻辑表达式是否成立，只有一个成立，结果为TRUE；都不成立，结果为FALSE。=OR(5>8,7<9)TRUEIF(条件表达式，值1，值2)当条件表达式为真时，返回值1，否则返回值2。

6．查找和引用函数函

数功

能应用举例结

果=ROW()返回所在行的行号在D12单元格输入“=ROW()”。12=COLUMN()返回所在列的列号在D12单元格输入“=COLUMN()”。4课中学习任务2-3：“山东道克图文公司员工考核表”数据处理（一）任务介绍人力资源部马经理需要对山东道克图文公司员工考核数据处理，打开山东道克图文公司员工考核表（素材）.xlsx，另存为山东道克图文公司员工考核表（结果）.xlsx，她进行了如下计算处理。1）根据身份证号信息计算年龄。2）根据工号前四位计算入店年份。3）计算员工所在道克图文店。4）考核是否优秀和等级的计算。5）员工奖金及其它信息的计算。6）图文店业务统计计算。（二）任务实施1．职员基本信息计算2．图文店考核信息计算3．图文店业务统计课后提升（一）巩固提升实训任务2-3：员工性别信息的计算（二）拓展提升实训任务2-4：“山东道克图文公司员工考核表”隔行填色项目6开支明细表数据处理学习目标课前自学课中学习课后提升学习目标1．知识目标1）掌握设置单元格边框、行高、列宽、对齐方式的操作方法。2）掌握创建表格并设置表格样式的操作方法。3）掌握数据验证的作用和操作方法。4）掌握运用条件格式筛选数据的操作方法。5）掌握自定义多条件筛选数据的操作方法。6）掌握多关键字排序的操作方法。7）掌握数据透视表的作用和创建方法。8）掌握图表的常用类型和选取数据创建图表的操作方法。2．能力目标1）能够熟练对工作表进行美化。2）能够根据需要选取相应数据制作报表。3．素养目标1）通过对实际问题的解决，培养学生的信息素养。2）体会成功的快乐，激进学习兴趣，提高自主学习能力。3）具备协调沟通的团队精神。课前自学（一）格式化表格

Excel表格提供了丰富的格式化命令，利用这些命令，可以对工作表内的数据及外观进行修饰，制作出符合日常习惯又美观的表格。

单元格或单元格区域的格式化主要包含六部分：数字、对齐、字体、边框、图案和保护，如图所示，为“设置单元格格式”对话框中包含的六部分。（二）数据处理数据验证

数据验证是Excel提供的用于检验数据有效性的功能。在Excel中，为了避免在输入数据时出现过多错误，可以通过在单元格中设置数据有效性以防止输入无效数据，或者在录入无效数据时自动发出警告，从而保证数据输入的准确性，提高工作效率。

对于学生成绩，有效值为0-100的数值，比如成绩为90，误输入为900，若已经为学生成绩列设置数据验证，则此时会出现警告提示，保证数据有效性。数据验证在“数据”项卡下的“数据工具”组中，如图所示。

有一点需要注意，验证功能只对新数据有效，在已经填好数据的单元格增加验证功能没有作用。数据验证是在当单元格的值发生变化时触发的，如果原有的值不符合标准，同时该值没有发生变化，就不会触发验证功能。所以，如果决定使用验证功能，最好在未填写数据时就设置好，而不是等数据都填写后再设置。条件格式Excel提供的条件格式功能可以迅速为满足某些条件的单元格或单元格区域设定某项格式，条件格式会基于设定的条件来自动更改单元格区域的外观，可以突出显示所关注的单元格或单元格区域、强调异常值、使用数据条、色阶和图标集来直观地显示数据。如图所示。（1）突出显示单元格规则

用比较运算符限定数据范围，对该数据范围内的单元格设定格式。

规则选项有大于、小于、介于、等于、文本包含、发生日期、重复值和其他规则。

例如：查找高数科目成绩大于85的值，如图所示。（2）项目选取规则

对选定单元格区域中的前若干个最高值或后若干个最低值及高于或低于该区域平均值的单元格设定特殊格式。

规则有前10项、前10%、最后10项、最后10%、高于平均值、低于平均值和其他规则。

例如：查找高数科目成绩前十名的值，如图所示。（3）数据条

数据条的长度代表单元格中的值。数据条越长，值越高；数据条越短，值越低。

最常用的数据条有两种样式：渐变填充和实心填充。

例如：高数科目成绩是渐变填充效果，体育科目成绩是实心填充效果，如图所示。（4）色阶

使用两种或者三种颜色的渐变效果直观地比较单元格区域中数据，用来显示数据的变化。一般情况下，颜色的深浅表示值得高低。

例如：用“绿-白色阶”表示高数科目的成绩值。成绩越高，颜色越绿，成绩越低，颜色越白。如图所示。（5）图标集

可以使用图标集对数据进行注释，每个图标代表一个值得范围。

预设了方向、形状、标记、等级四种图标集。

例如：用三色交通灯来表示高数科目的成绩。如图所示。排序

对数据进行排序有助于快速直观地组织并查找所需数据，可以对一列或多列中的数据文本、数值、日期和时间按升序或者降序的方式进行排序。还可以按照自定义序列、格式（包括单元格颜色、字体颜色等）进行排序。（1）快速简单排序按“总分”列进行升序排序。单击“总分”数据列中的任意一个单元格，选择“数据”选项卡→“排序和筛选”组→“升序”按钮，则按总分成绩从小到大排序；若选择“降序”，则按总分成绩从大到小排序，如图2-63所示。（2）复杂多条件排序按“高数”成绩升序，高数成绩相同的，按照“英语”成绩降序排序。单击数据列中的任意一个单元格，选择“数据”选项卡→“排序和筛选”组中的“排序”按钮→打开“排序”对话框→在“主要关键字”中选择“高数”、“数值”、“升序”→单击“添加条件”→在“次要关键字”中选择“英语”、“数值”、“降序”→单击”确定”按钮，如图所示。（3）按照字体颜色排序将“高数”成绩相同的行，字体颜色设置为红色，并将其所在行设置在数据表的顶端。单击数据列中的任意一个单元格，选择“数据”选项卡→“排序和筛选”组中的“排序”按钮→打开“排序”对话框→在“主要关键字”中选择“高数”、“排序依据”选择“字体颜色”、“次序”选择“红色”“在顶端”→单击“确定”按钮，如图2-65所示。筛选

通过筛选功能可以快速的从数据列表中查找符合条件的数据或者排除不符合条件的数据。对数据列表中的数据进行筛选后，就会仅显示满足指定条件的行，不满足条件的行就会被隐藏。更方便用户查看数据。首先单击内容数据表的任一单元格，选择“数据”选项卡→“排序和筛选”组→“筛选”按钮，此时，字段名右侧会出现一个下拉箭头。单击需要筛选字段的下拉箭头，设置筛选条件或自定义自动筛选方式。例如：筛选出“计算机”成绩大于85的数据行，操作步骤如图所示。筛选结果若取消筛选，则再单击“筛选”按钮取消选中即可。数据透视表

数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、组织数据。建好数据透视表后，可以对其中的字段重新安排，以便从不同角度查看数据。数据透视表能够将筛选、排序、分类汇总等操作依次完成，并生成汇总报表，生成数据透视表的关键步骤如下页图2-68所示。图2-68将性别、班级、姓名、总分4个字段分别拖入到相应区域，生成的数据透视表如图所示。将“性别”筛选为男，列标签改为“班级”，行标签改为“姓名”，效果如图所示。迷你图迷你图是放入单个单元格中的小型图，每个迷你图代表所选内容中的一行数据。选中Q3单元格，选择“插入”选项卡→“迷你图”组→“折线图”按钮，打开创建迷你图对话框，数据范围选择F3:N3，位置Q3，单击确定。拖动Q3右下角的填充柄，可以将迷你图填充到最后。迷你图的样式及颜色，高点、低点、首点、尾点等的设置，在“迷你图工具--设计”上下文选项卡下进行。图表相对于迷你图，图表作为表格中的嵌入对象，类型更丰富、创建更灵活、功能更全面、作用更强大。图表是工作表数据的图形化表示，通过图表，数据信息变得一目了然，更加详细地表示数据的大小、变化、走向、趋势及数据间的差异等，更方便进行数据的比较和分析。在Excel中，图表由多个部分组成，这些组成部分被称为图表元素。一个完整的图表通常由图表区、绘图区、图表标题、图例等组成。最常用的图表类型有：柱形图，折线图，饼图，条形图，面积图等。图表的基本组成：图表区，绘图区，横坐标轴和纵坐标轴，图例，图标标题，坐标轴标题，数据标签等。课中学习任务2-4：“开支明细表”整理与分析（一）任务介绍

小张是一名参加工作不久的大学生，平时习惯使用Excel表格来记录每月的个人开支情况。在2021年底，他将每个月各类支出的明细数据录入了文件名为“开支明细表.xlsx”的Excel工作簿文档中。请根据下列要求帮助小赵对明细表进行整理和分析：1）在工作表“小张的美好生活”的第一行添加表标题“小张2021年开支明细表”，并通过合并单元格，放于整个表的上端、居中。2）将工作表应用一种主题，并增大字号，适当加大行高列宽，设置居中对齐方式，除表标题“小张2021年开支明细表”外为工作表分别增加恰当的边框和底纹以使工作表更加美观。3）将每月各类支出及总支出对应的单元格数据类型都设为“货币”类型，无小数、有人民币货币符号。4）通过函数计算每个月的总支出、各个类别月均支出、每月平均总支出；并按每个月总支出升序对工作表进行排序。5）利用“条件格式”功能：将月单项开支金额中大于1000元的数据所在单元格以不同的字体颜色与填充颜色突出显示；将月总支出额中大于月均总支出110%的数据所在单元格以另一种颜色显示，所用颜色深浅以不遮挡数据为宜。6）在“年月”与“服装服饰”列之间插入新列“季度”，数据根据月份由函数生成，例如：1至3月对应“1季度”、4至6月对应“2季度”……7）复制工作表“小张的美好生活”，将副本放置到原表右侧；改变该副本表标签的颜色，并重命名为“按季度汇总”；删除“月均开销”对应行。8）通过分类汇总功能，按季度升序求出每个季度各类开支的月均支出金额。9）在“按季度汇总”工作表后面新建名为“折线图”的工作表，在该工作表中以分类汇总结果为基础，创建一个带数据标记的折线图，水平轴标签为各类开支，对各类开支的季度平均支出进行比较，给每类开支的最高季度月均支出值添加数据标签。课后提升（一）巩固提升

实训任务2-5：“股票走势数据表”处理

小张就职于一家金融公司，需要对股票走势进行分析计算。打开“股票走势.xlsx”文件，对“股票走势”数据表按以下要求操作。1）设置标题，文字为黑体、20磅、粗体，并在A1:N1区域中合并居中，行高30。2）在J3单元格中，使用MIN函数求出该股票今日最低价，并将该公式向下填充J4:J7区域，在K3单元格中，使用MAX函数中求出该股票今日最高价，并将该公式向下填充K4:K7区域，在L3单元格中，利用公式计算今日涨跌。今日涨跌=最高价-最低价，并将该公式向下填充M4:M7区域，在M3单元格中，利用公式计算今日涨幅，今日涨幅=今日涨跌/昨收盘，并将该公式向下填充M4:M7区域。对今日涨幅列数据进行降序排序。3）在K9单元格中，使用AVERAGE函数求出今日平均涨幅。4）设置A2:N7区域中行的行高设置为30，并为该区表格设置所有框线，在N3单元格中插入折线图迷你图，并向下填充N4:N7区域。5）设置A1:N7区域的单元格对齐方式为水平方向居中、垂直方向居中。6）以B2:C7和I2:I7区域数据为数据源，在当前表中生成一个二维簇状柱形图，图表设计布局为“布局1”，图表标题为“股票价格变化图”，为图表添加图表元素“数据标签”。最终效果如图所示。（二）拓展提升

实训任务2-6：“产品销售表”统计分析

销售部助理小王需要根据2012年和2013年的图书产品销售情况进行统计分析，以便制订新一年的销售计划和工作任务。现在，请按照如下需求，在文档“产品销售表.xlsx”中完成以下工作并保存。1）在“销售订单”工作表的“图书编号”列中，使用VLOOKUP函数填充所对应“图书名称”的“图书编号”，“图书名称”和“图书编号”的对照关系请参考“图书编目表”工作表。

2）将“销售订单”工作表的“订单编号”列按照数值升序方式排序，并将所有重复的订单编号数值标记为紫色（标准色）字体，然后将其排列在销售订单列表区域的顶端。3）在“2013年图书销售分析”工作表中，统计2013年各类图书在每月的销售量，并将统计结果填充在所对应的单元格中。为该表添加汇总行，在汇总行单元格中分别计算每月图书的总销量。4）在“2013年图书销售分析”工作表中的N4:N11单元格中，插入用于统计销售趋势的迷你折线图，各单元格中迷你图的数据范围为所对应图书的1月～12月销售数据。并为各迷你折线图标记销量的最高点和最低点。5）根据“销售订单”工作表的销售列表创建数据透视表，并将创建完成的数据透视表放置在新工作表中，以A1单元格为数据透视表的起点位置。将工作表重命名为“2012年书店销量”。6）在“2012年书店销量”工作表的数据透视表中，设置“日期”字段为列标签，“书店名称”字段为行标签，“销量（本）”字段为求和汇总项。并在数据透视表中显示2012年期间各书店每季度的销量情况。提示：为了统计方便，请勿对完成的数据透视表进行额外的排序操作。谢谢观看单元2Excel2016电子表格处理汇报内容02项目5道克图文公司员工考核数据处理03目录项目6开支明细表数据处理01项目4数据快速录入及计算项目6开支明细表数据处理学习目标课前自学课中学习课后提升学习目标1．知识目标1）掌握设置单元格边框、行高、列宽、对齐方式的操作方法。2）掌握创建表格并设置表格样式的操作方法。3）掌握数据验证的作用和操作方法。4）掌握运用条件格式筛选数据的操作方法。5）掌握自定义多条件筛选数据的操作方法。6）掌握多关键字排序的操作方法。7）掌握数据透视表的作用和创建方法。8）掌握图表的常用类型和选取数据创建图表的操作方法。2．能力目标1）能够熟练对工作表进行美化。2）能够根据需要选取相应数据制作报表。3．素养目标1）通过对实际问题的解决，培养学生的信息素养。2）体会成功的快乐，激进学习兴趣，提高自主学习能力。3）具备协调沟通的团队精神。课前自学（一）格式化表格

Excel表格提供了丰富的格式化命令，利用这些命令，可以对工作表内的数据及外观进行修饰，制作出符合日常习惯又美观的表格。

单元格或单元格区域的格式化主要包含六部分：数字、对齐、字体、边框、图案和保护，如图所示，为“设置单元格格式”对话框中包含的六部分。（二）数据处理数据验证

数据验证是Excel提供的用于检验数据有效性的功能。在Excel中，为了避免在输入数据时出现过多错误，可以通过在单元格中设置数据有效性以防止输入无效数据，或者在录入无效数据时自动发出警告，从而保证数据输入的准确性，提高工作效率。

对于学生成绩，有效值为0-100的数值，比如成绩为90，误输入为900，若已经为学生成绩列设置数据验证，则此时会出现警告提示，保证数据有效性。数据验证在“数据”项卡下的“数据工具”组中，如图所示。

有一点需要注意，验证功能只对新数据有效，在已经填好数据的单元格增加验证功能没有作用。数据验证是在当单元格的值发生变化时触发的，如果原有的值不符合标准，同时该值没有发生变化，就不会触发验证功能。所以，如果决定使用验证功能，最好在未填写数据时就设置好，而不是等数据都填写后再设置。条件格式Excel提供的条件格式功能可以迅速为满足某些条件的单元格或单元格区域设定某项格式，条件格式会基于设定的条件来自动更改单元格区域的外观，可以突出显示所关注的单元格或单元格区域、强调异常值、使用数据条、色阶和图标集来直观地显示数据。如图所示。（1）突出显示单元格规则

用比较运算符限定数据范围，对该数据范围内的单元格设定格式。

规则选项有大于、小于、介于、等于、文本包含、发生日期、重复值和其他规则。

例如：查找高数科目成绩大于85的值，如图所示。（2）项目选取规则

对选定单元格区域中的前若干个最高值或后若干个最低值及高于或低于该区域平均值的单元格设定特殊格式。

规则有前10项、前10%、最后10项、最后10%、高于平均值、低于平均值和其他规则。

例如：查找高数科目成绩前十名的值，如图所示。（3）数据条

数据条的长度代表单元格中的值。数据条越长，值越高；数据条越短，值越低。

最常用的数据条有两种样式：渐变填充和实心填充。

例如：高数科目成绩是渐变填充效果，体育科目成绩是实心填充效果，如图所示。（4）色阶

使用两种或者三种颜色的渐变效果直观地比较单元格区域中数据，用来显示数据的变化。一般情况下，颜色的深浅表示值得高低。

例如：用“绿-白色阶”表示高数科目的成绩值。成绩越高，颜色越绿，成绩越低，颜色越白。如图所示。（5）图标集

可以使用图标集对数据进行注释，每个图标代表一个值得范围。

预设了方向、形状、标记、等级四种图标集。

例如：用三色交通灯来表示高数科目的成绩。如图所示。排序

对数据进行排序有助于快速直观地组织并查找所需数据，可以对一列或多列中的数据文本、数值、日期和时间按升序或者降序的方式进行排序。还可以按照自定义序列、格式（包括单元格颜色、字体颜色等）进行排序。（1）快速简单排序按“总分”列进行升序排序。单击“总分”数据列中的任意一个单元格，选择“数据”选项卡→“排序和筛选”组→“升序”按钮，则按总分成绩从小到大排序；若选择“降序”，则按总分成绩从大到小排序，如图2-63所示。（2）复杂多条件排序按“高数”成绩升序，高数成绩相同的，按照“英语”成绩降序排序。单击数据列中的任意一个单元格，选择“数据”选项卡→“排序和筛选”组中的“排序”按钮→打开“排序”对话框→在“主要关键字”中选择“高数”、“数值”、“升序”→单击“添加条件”→在“次要关键字”中选择“英语”、“数值”、“降序”→单击”确定”按钮，如图所示。（3）按照字体颜色排序将“高数”成绩相同的行，字体颜色设置为红色，并将其所在行设置在数据表的顶端。单击数据列中的任意一个单元格，选择“数据”选项卡→“排序和筛选”组中的“排序”按钮→打开“排序”对话框→在“主要关键字”中选择“高数”、“排序依据”选择“字体颜色”、“次序”选择“红色”“在顶端”→单击“确定”按钮，如图2-65所示。筛选

通过筛选功能可以快速的从数据列表中查找符合条件的数据或者排除不符合条件的数据。对数据列表中的数据进行筛选后，就会仅显示满足指定条件的行，不满足条件的行就会被隐藏。更方便用户查看数据。首先单击内容数据表的任一单元格，选择“数据”选项卡→“排序和筛选”组→“筛选”按钮，此时，字段名右侧会出现一个下拉箭头。单击需要筛选字段的下拉箭头，设置筛选条件或自定义自动筛选方式。例如：筛选出“计算机”成绩大于85的数据行，操作步骤如图所示。筛选结果若取消筛选，则再单击“筛选”按钮取消选中即可。数据透视表

数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、组织数据。建好数据透视表后，可以对其中的字段重新安排，以便从不同角度查看数据。数据透视表能够将筛选、排序、分类汇总等操作依次完成，并生成汇总报表，生成数据透视表的关键步骤如下页图2-68所示。图2-68将性别、班级、姓名、总分4个字段分别拖入到相应区域，生成的数据透视表如图所示。将“性别”筛选为男，列标签改为“班级”，行标签改为“姓名”，效果如图所示。迷你图迷你图是放入单个单元格中的小型图，每个迷你图代表所选内容中的一行数据。选中Q3单元格，选择“插入”选项卡→“迷你图”组→“折线图”按钮，打开创建迷你图对话框，数据范围选择F3:N3，位置Q3，单击确定。拖动Q3右下角的填充柄，可以将迷你图填充到最后。迷你图的样式及颜色，高点、低点、首点、尾点等的设置，在“迷你图工具--设计”上下文选项卡下进行。图表相对于迷你图，图表作为表格中的嵌入对象，类型更丰富、创建更灵活、功能更全面、作用更强大。图表是工作表数据的图形化表示，通过图表，数据信息变得一目了然，更加详细地表示数据的大小、变化、走向、趋势及数据间的差异等，更方便进行数据的比较和分析。在Excel中，图表由多个部分组成，这些组成部分被称为图表元素。一个完整的图表通常由图表区、绘图区、图表标题、图例等组成。最常用的图表类型有：柱形图，折线图，饼图，条形图，面积图等。图表的基本组成：图表区，绘图区，横坐标轴和纵坐标轴，图例，图标标题，坐标轴标题，数据标签等。课中学习任务2-4：“开支明细表”整理与分析（一）任务介绍

小张是一名参加工作不久的大学生，平时习惯使用Excel表格来记录每月的个人开支情况。在2021年底，他将每个月各类支出的明细数据录入了文件名为“开支明细表.xlsx”的Excel工作簿文档中。请根据下列要求帮助小赵对明细表进行整理和分析：1）在工作表“小张的美好生活”的第一行添加表标题“小张2021年开支明细表”，并通过合并单元格，放于整个表的上端、居中。2）将工作表应用一种主题，并增大字号，适当加大行高列宽，设置居中对齐方式，除表标题“小张2021年开支明细表”外为工作表分别增加恰当的边框和底纹以使工作表更加美观。3）将每月各类支出及总支出对应的单元格数据类型都设为“货币”类型，无小数、有人民币货币符号。4）通过函数计算每个月的总支出、各个类别月均支出、每月平均总支出；并按每个月总支出升序对工作表进行排序。5