计算机应用基础 课件 项目五 Excel 数据处理-Excel 2010 的应用1

项目五

Excel数据处理

——Excel2010的应用主讲人：XXX项目导读

MicrosoftExcel2010是微软公司出品的Office2010办公集成套装软件的一个应用程序之一，是目前最流行、功能最为强大的电子表格处理工具之一。它可以用来制作电子表格，完成复杂的数据运算以及进行数据的分析和预测，并且具有强大的制作图表功能。Microsoft

Excel2010可以通过比以往更多的方法分析、管理和共享信息，从而帮助您做出更好的决策。全新的分析和可视化工具可跟踪和突出重要的数据趋势，将文件上传到网站并与其他人同时在线协作。无论是生成财务报表，还是管理个人支出，使用Excel2010都能够更高效、灵活地实现目标。Excel系列办公软件被广泛应用在公司管理、会计财务管理、审计分析等方面学习目标能力目标能制作工资表能制作工资分析表能设计图表能对表中的数据进行统计能创建数据透视表知识目标掌握Excel2010工作簿、工作表的区别和联系理解并掌握公式与函数的含义掌握对数据进行分析与处理的各种概念和方法素质目标通过项目小组的活动，以培养学生的团队精神培养学生具有积极进取、勇于创新的思想和意识逐步培养学生具有勤于动手、善于思考的学习习惯培养学生的社会实践能力，以提高学生的综合素质任务分解制作员工工资发放表01制作员工工资分析表02制作员工工资统计表03生成员工工资图表04创建数据透视表05PART01制作员工工资发放表任务描述制作满足应用需要的电子表格是使用表格管理数据的第一步，而基本操作是后续操作的重要基础。本任务首先利用Excel2010制作一份员工工资表，包括员工的序号、姓名、基本工资、加班工资、出差津贴、绩效考核等项目，以及养老保险、医疗保险和住房公积金等应扣的项目，如下图任务实施——活动一：制作信息表（1）启动Excel2010，新建一个工作簿。（2）修改工作表的名称。

右击工作表标签或者双击要更改名称的工作表标签。这时可以看到工作表标签以高亮度显示，在其中输入“员工工资发放表”，并按“Enter”键即可任务实施——活动二：添加信息表内容在信息项中输入员工工资信息，如“序号”“姓名”“应发工资”和“应扣部分”等项目信息任务实施——活动三：格式化信息表格式化工作表是指对工作表中的数字格式、字体、字形、字号、颜色、对齐方式、单元格的边框、底纹图案等格式的设置，可以使所制作的工作表更加美观。1．编辑信息表的样式（1）在“设置单元格格式”对话框中，选择“字体”选项卡，可以设置单元格内字体的格式，如字体大小、颜色等属性。（2）在“设置单元格格式”对话框中，选择“填充”选项卡，可以为单元格设置背景颜色。（3）在“设置单元格格式”对话框中，选择“边框”选项卡，可以为选定的单元格设置外边框和内边框。任务实施——活动三：格式化信息表2．调整列宽与行高（1）执行“开始”→“格式”→“列宽”（或“标准列宽”），弹出“列宽”（或“标准列宽”）对话框。（2）在“列宽”（或“标准列宽”）文本框中输入数值，如输入“13”。（3）单击“确定”按钮，此时，每列单元格都以更改后的数值显示列宽度。（4）选中要调整行高的任意一个单元格，然后执行“开始”→“格式”→“行高”，弹出“行高”对话框，在“行高”文本框中输入数值。（5）单击“确定”按钮，即可以设定行的高度。如果选中的单元格分布在多个行，那么在“行高”对话框中设置可同时改变多个行的行高。任务实施——活动四：保存文件执行“文件”→“保存”，或单击常用工具栏中的“保存”按钮。在“另存为”对话框的“保存位置”下拉列表框中选择合适的路径，然后在“文件名”文本框中输入工作簿名称。单击“保存”按钮。这样，就完成了保存当前工作簿的操作能力拓展1．自动套用格式自动套用格式是指已经设置好数字格式、字体、字形、字号、对齐方式、列宽、行高、边框和底纹等，用户可以有选择地进行套用，可更快速地设置格式。（1）对选定的区域应用“自动套用格式”选择要格式化的区域，然后执行“开始”→“格式”→“样式”，弹出“自动套用格式”对话框，在对话框中设置所需格式后，单击“确定”按钮。（2）删除“自动套用格式”选择要删除自动套用格式的区域，在“格式”菜单的“自动套用格式”对话框中，选择“格式”列表框底端的选项为“无”，单击“确定”按钮。能力拓展2．查找和替换数据先在菜单栏中执行“编辑”→“查找”，弹出“查找和替换”对话框，选择“查找”选项卡，在“查找内容”文本框中输入要查找的数据，然后单击“查找下一个”按钮，若想查看下一个查找结果，则再次单击“查找下一个”按钮即可先在菜单栏中执行“编辑”→“替换”，弹出“查找和替换”对话框的“替换”选项卡。在“查找内容”文本框中输入要替换的数据，在“替换为”文本框中输入要替换为的数据，然后单击“查找下一个”按钮，Excel2010会自动在工作表中选定相关的单元格查找数据替换数据相关知识1．中文Excel2010的启动与退出中文Excel2010的启动（3种方法）①执行“开始”→“程序”→“MicrosoftOffice”→“MicrosoftOfficeExcel2010”，即可启动中文Excel2010；②双击桌面上的Excel2010图标，即可启动中文Excel2010；③弹出“我的电脑”，找到已存在的Excel2010文档（工作簿）所在的文件夹，在Excel2010文档（工作簿）的图标上双击，就可启动中文Excel2010，并自动打开相应的Excel文档中文Excel2010的退出（5种方法）①双击中文Excel2010窗口最左上角的控制菜单按钮；②单击窗口最左上角的控制菜单按钮，选择“关闭”功能；③选择中文Excel2010的“文件”菜单的“退出”功能；④单击窗口右上角的“关闭”按钮；⑤按“Alt”+“F4”组合键相关知识2．中文Excel2010窗口的组成相关知识2．中文Excel2010窗口的组成01标题栏：用于显示当前使用的应用程序名称和工作簿名称02编辑栏：用来定位和选择单元格数据，以及显示活动单元格中的数据和公式03菜单栏：Excel2010最为丰富的命令集合，几乎所有Excel

2010命令都可以从菜单栏中选择执行04工作簿窗口05功能区：包括常用工具栏和格式工具栏相关知识3．Excel2010的基本概念单元格：是由行、列交叉形成的最小单位。由列标（字母A～IV）和行号（阿拉伯数字）来标识，列标在前，行号在后活动单元格：当前选中的单元格，是有一个位于单元格右下角的小黑块填充柄，当光标指向填充柄时，其形状变为黑十字单元格区域：是一组被选中的单元格工作表：是组成工作簿的基本单位，每张工作表以工作表标签的形式显示在工作表的编辑区底部工作簿：由众多工作表组成，用来存储和处理工作数据的Excel文件相关知识4．Excel2010的对象结构一个工作簿最多可包含独立的255个工作表，工作表由行和列组成。行和列交叉的位置称为单元格，它是工作表中的最基本的单元。在Excel2010中可以按“Ctrl”+“↓”组合键将光标移到一个工作表的最后一行。按“Ctrl”+“→”组合键可以将光标移动到最后一列。一个工作表最多可以有1048576行和16384列，它所存放的数据量完全由计算机的内存大小而定。按“Ctrl”+“Home”组合键可以将光标移动到当前页的第一行，按“Ctrl”+“End”组合键可以将光标移动到当前页的最后一行相关知识5．Excel2010的基本操作①执行“文件”→“新建”，在弹出的“新建工作簿”任务窗格中单击“新建”选项组的“空白工作簿”超链接。②单击常用工具栏中的“新建”按钮。③创建基于模板的工作簿，在“新建工作簿”任务窗格中，单击“模板”选项组中的“本机上的模板”超链接，在弹出的“模板”对话框中选择“电子方案表格”选项卡，并在列表框中选择需要的模板，单击“确定”按钮。④按“Ctrl”+“N”组合键①执行“文件”→“打开”。②单击常用工具栏中的“打开”按钮。③在弹出的任务窗格中，选择“打开”选项组中文档的名称或“其他…”超链接。④按“Ctrl”+“O”组合键当新建的工作簿第一次存盘时，可以直接执行“文件”→“保存”，或单击“常用”工具栏中的“保存”按钮。系统将弹出“另存为”对话框。若编辑一个已存在的文件，可执行“文件”→“保存”或单击“常用”工具栏中的“保存”按钮，将按原文件名保存。按“Ctrl”+“S”组合键，也可进行保存保存工作簿打开工作簿新建工作簿相关知识6．工作表的编辑（1）选定一个单元格用鼠标在需要选定的单元格上单击，即可选定该单元格。（2）选定单元格区域①选定相邻的一组单元格。②选定多个不相邻的单元格区域。（3）选择一些特殊的单元格区域①整行：单击工作表中某行的行号，即可选定该行。②整列：单击工作表中某列的列标，即可选定该列。③整个工作表：单击工作表行号和列标的交叉处，即可选中整个工作表。④相邻的行或列：在工作表行号或列标上按住鼠标左键，并拖动选定要选择的所有行。⑤不相邻的行或列：单击第一个行号或列标，按住“Ctrl”键，再单击其他行号或列标。相关知识6．工作表的编辑（4）工作表数据的输入向单元格输入数据有三种方法：单击单元格后输入；双击单元格，当出现插入光标时输入；单击单元格，在编辑栏输入。①文本数据的输入。选中单元格，再输入文本型数据（默认左对齐）。②数值型数据的输入。先选中单元格，再输入数值，然后在数据编辑框中单击输入按钮图标“√”或直接按“Enter”键即可（默认右对齐）。③日期和时间型数据的输入。在中文版Excel2010中，当在单元格中输入系统可识别的时间和日期型数据时，单元格的格式就会自动转换为相应的“时间”或者“日期”格式，而不需要专门设置。④数据序列的填充输入。数据序列的填充是指一次同时在相邻单元格中复制多个数据。⑤数据序列的输入。相关知识6．工作表的编辑（5）进入单元格数据编辑状态的方法①选中单元格之后，按“F2”键。②双击单元格，出现插入点时，状态栏上就绪状态可变为编辑。（6）单元格内容的修改和删除①修改单元格内容：单击单元格可进行完全修改；双击单元格可进行部分修改。②清除单元格：只清除内容，单元格本身仍保留。③清除单元格的内容、格式、批注等三项（编辑清除全部）。④只清除单元格的内容（按“Del”键）。相关知识6．工作表的编辑（7）单元格的复制①利用菜单复制。②利用快捷菜单。②利用快捷菜单。（8）单元格的移动①利用鼠标和键盘。②利用菜单复制。③利用快捷菜单。（9）单元格的插入选定要插入的单元格或单元格区域，选择“插入”菜单的“单元格”选项，弹出“插入”对话框。选择插入方式，然后单击“确定”按钮。相关知识6．工作表的编辑（10）插入行、列或多行、多列（11）删除单元格、行或列选定要删除的行、列或单元格，选择“编辑”菜单的“删除”选项，弹出“删除”对话框，选中需要操作的单选按钮，并单击“确定”按钮。A插入行：在需要插入新行的位置单击任意单元格，然后选择“插入”菜单中的“行”选项B插入列：在需要插入新列的位置单击任意单元格，然后选择“插入”菜单中的“列”选项C插入多行/列：选定与需要插入的新行（列）下侧（右侧）相邻的若干行（列）相关知识6．工作表的编辑（12）单元格的合并合并单元格是指将跨越几行或几列的多个单元格合并为一个单元格。Excel2010只把选定区域左上角的数据放入合并后所得的合并单元格中。方法1选择要合并单元格的单元格区域。单击单元格工具栏中的“合并及居中”按钮即可方法2选择要合并的单元格。选择“格式”菜单中的“单元格”选项，弹出“单元格格式”对话框。在“单元格格式”对话框中，选择“对齐”选项卡。勾选“文本控制”中的“合并单元格”复选框，单击“确定”按钮相关知识7．工作表基本操作重命名工作表方法1：双击要更改名称的工作表标签，可以看到工作表标签以高亮度显示，在其中输入新名称，并按“Enter”键即可。方法2：使用菜单命令重命名工作表移动和复制工作表①用鼠标拖动法移动、复制工作表。②用菜单移动、复制工作表插入和删除工作表①插入。选定当前工作表，将光标指向该工作表标签，单击鼠标右键，在弹出的快捷菜单中选择“插入”选项。②删除。单击工作表标签，选择“编辑”菜单中的“删除工作表”命令相关知识8．设置单元格的格式0102030405设置边框①选择需要设置边框的单元格区域。②选择“格式”菜单中的“单元格”选项，弹出“单元格格式”对话框。③在“单元格格式”对话框中选择“边框”选项卡设置对齐方式①选择需要设置对齐方式的单元格区域。②选择“格式”菜单中的“单元格”选项，弹出“单元格格式”对话框。③在“单元格格式”对话框中选择“对齐”选项卡设置底纹①选择需要设置底纹的单元格区域。②选择“格式”菜单中的“单元格”选项，弹出“单元格格式”对话框。③在“单元格格式”对话框中选择“图案”选项卡设置字体①选择需要设置字体的单元格区域。②选择“格式”菜单中的“单元格”选项，弹出“单元格格式”对话框。③在“单元格格式”对话框中选择“字体”选项卡设置数字格式①选择需要设置数字格式的单元格区域。②选择“格式”菜单中的“单元格”选项，弹出“单元格格式”对话框。③在“单元格格式”对话框中选择“数字”选项卡PART02制作员工工资分析表任务描述利用Excel2010强大的数据处理功能，制作一个员工工资分析表，可以清楚地分析出各个工资段的人数等情况，如下图任务实施——活动一：制作员工工资发放表给单元格B12添加批注。（1）单击需要添加批注的单元格B12。（2）在“插入”菜单中，选择“批注”选项。（3）在弹出的批注框中输入批注文本“提示：外聘的专家，按部门主管发放工资”（如右图）。（4）完成文本输入后，单击批注框外部的工作表区域，即可完成批注的添加任务实施——活动二：计算工资的总额1．根据备注养老保险＝基本工资×8%，医疗保险＝基本工资×2%，失业保险＝基本工资×1%，住房公积金＝基本工资×8%。在养老保险栏的第一行“G5”单元格中输入“＝”，再选中第一位员工“包宏伟”的基本工资“C5”单元格，输入“*0.08”，即“G5”单元格中输入“＝C5*0.08”，按“Enter”键确认，此时的公式变成了数值480。选中“G5”单元格，稍微移动鼠标，当光标变为实心的十字时，向下拖动鼠标，公式可自动填充完成。或者双击鼠标将“养老保险”中所有的单元格填充。任务实施——活动二：计算工资的总额1．根据备注按照相同的方法，在相应的单元格中输入“＝C5*0.02”“＝C5*0.01”和“＝C5*0.08”，依次填充“医疗保险”“失业保险”和“住房公积金”这些扣款项目，生成表格如右图任务实施——活动二：计算工资的总额2．利用函数COUNTIF求出各个工资段的人数（1）在L4单元格中输入“工资高于5600元的人数”，然后选定需要输入函数的单元格

L5。（2）选择“插入”菜单中的“函数”选项，弹出“插入函数”对话框，在“或选择类别”下拉列表框中选“统计”选项，选择“选择函数”列表框中的“COUNTIF”函数（如右图），单击“确定”按钮。（3）在打开的“函数参数”对话框中根据提示对话框，在“Range”中输入“K5:K19”，在“Griteria”中输入条件“>＝5600”，单击“确定”按钮，即可求出工资高于5600元的人数任务实施——活动二：计算工资的总额2．利用函数COUNTIF求出各个工资段的人数（4）在L6单元格中输入“工资高于5500元低于5600元的人数”，单击L7单元格，在地址栏中输入＝COUNTIF(K5:K19,">＝5500")-L5，按“Enter”键求出5500～5600元工资段的人数任务实施——活动二：计算工资的总额2．利用函数COUNTIF求出各个工资段的人数（5）在L8单元格中输入“工资高于5200元低于5500元的人数”，单击L9单元格，在地址栏中输入“＝COUNTIF(K5:K19,">＝5200")-L5-L7”，按“Enter”键求出工资高于5200元低于5500元的人数。（6）在L10单元格中输入“工资低于5200元的人数”，单击L11单元格，在地址栏中输入“＝COUNTIF(K6:K20,"<5200")”，按“Enter”键求出工资低于5200元的人数任务实施——活动二：计算工资的总额3．利用函数计算出总人数（1）在L13单元格中输入“员工总人数”，选定单元格L14。（2）选择“插入”菜单中的“函数”选项，弹出“插入函数”对话框，并选择“选择函数”列表框中的“SUM”函数，单击“确定”按钮，弹出“函数参数”对话框。（3）在对话框中，单击“Number1”输入框右端的图标按钮，然后在表格中按住“Ctrl”键，逐个选定各分数段的人数所在的单元格（如下图）。（4）单击“确定”按钮，即在单元格L14中显示求出的公司员工的总人数任务实施——活动二：计算工资的总额4．利用函数计算平均工资（1）（1）在单元格L16中输入“实发平均工资”。（2）选定单元格L17，选择“插入”菜单中的“函数”选项，弹出“插入函数”对话框，选择“选择函数”列表框中的“AVERAGE”函数，单击“确定”按钮（如下图）。（3）根据提示对话框，选中需要求平均值的区域K5:K19。（4）单击“确定”按钮，即可求出平均的实发工资任务实施——活动三：编辑员工工资发放表设置字体的样式、颜色、边框等能力拓展1．求和函数SUM语法：SUM(numberl,number2⋯)numberl,number2…为需要求和的数值参数。说明：（1）直接输入参数表中的数字、逻辑值及数字的文本表达式将被计算。（2）如果参数为数组或引用，则只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。（3）如果参数为错误值或为不能转换成数字的文本，则将会导致错误。2．最大值函数Max语法：Max(numberl,number2,number3⋯)numberl,number2,number3⋯为准备从中求取最大值的数值序列。说明：与之相对应的函数是最小值函数Min。语法：Min(numberl,number2,number3⋯)能力拓展3．排序函数Rank语法：Rank(number,ref,order)number为需要找到排位的数字。其中，ref为数字列表数组或对数字列表的引用，ref中区域要绝对引用。order为一个数字，指明排位的方式。说明：函数Rank对重复数的排位相同，但重复数的存在将影响后续数值的排位。在一列按升序排列的整数中，如果整数10出现两次，其排位为5，则11的排位为7（没有排位为6的数值）。能力拓展4．统计函数COUNTIF语法：COUNTIF(range,criteria)range为需要计算其中满足条件的单元格数目的单元格区域。criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。如条件可以表示为32、“32”、“>32”，或“apples”。说明：函数COUNTIF的功能是统计某个单元格区域中符合指定条件的单元格数目。5．求平均值函数AVERAGE语法：AVERAGE(numberl,number2⋯)numberl,number2…为要计算平均值的参数，这些参数可以是数字或涉及数字的名称数组或引用。如果数组或单元格引用参数中有文字逻辑值或空单元格，则忽略其值；但是如果单元格包含零值，则计算在内。能力拓展6．查找函数VLOOKUP语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value表示要查找的对象；table_array表示查找的表格区域；col_index_num表示要查找的数据在table_array区域中处于第几列的列号；range_lookup表示查找类型，其中1表示近似匹配，0表示精确匹配；即VLOOKUP(查找值,查找范围,查找列数,精确匹配或者近似匹配)。在工作中几乎都使用精确匹配，该项的参数一定要选择为false或0。否则返回值会出乎意料。在A2:F12区域中提取工号为100003、100004、100005、100007、100010五人的全年总计销量，并对应地输入到I4:I8中，如下图能力拓展6．查找函数VLOOKUP能力拓展6．查找函数VLOOKUP（1）在I4单元格输入“Vloo