Excel培训下

MicrosoftOfficeExcel2007培训蒋志明[e-mail]课程安排Excel2007基础应用6课时Excel2007高级应用6课时课程回顾全新用户界面制作专业的Excel表格制作精美图表数据的整理分析基本函数的使用今日议题验证数据的有效性数据的透视统计透视图透视表高级函数的使用Excel2007快捷键Excel2007使用技巧总结验证数据的有效性数据有效性使用数据有效性可以控制用户输入到单元格的数据或值的类型.因此,您可以配置数据有效性以防止用户输入无效数据.数据有效性消息输入消息可以选择在用户选择单元格时显示输入信息数据有效性消息出错警告信息您还可以选择显示出错警告，它仅在用户输入无效数据后才显示出错警告有三种类型停止阻止用户在单元格中输入无效数据。“停止”警告消息具有两个选项：“重试”或“取消”。警告在用户输入无效数据时向其发出警告，但不会禁止他们输入无效数据。在出现“警告”警告消息时，用户可以单击“是”接受无效输入、单击“否”编辑无效输入，或单击“取消”删除无效输入。出错警告有三种类型信息通知用户，他们输入了无效数据，但不会阻止他们输入无效数据。这种类型的出错警告最为灵活。在出现“信息”警告消息时，用户可单击“确定”接受无效值，或单击“取消”拒绝无效值。例外情况用户通过复制或填充输入了数据。单元格中的公式计算出无效结果。宏在单元格中输入了无效数据。数据有效性何时有用？将数据限制为列表中的预定义项将数据输入限制为指定整数限制文本字符数将日期/时间限制在某一时间范围之外将数据输入限制为指定整数步骤与上例相同在“允许”框中，选择“整数”。在“数据”框中选择所需的限制类型输入允许的最小值、最大值或特定值。还可以输入返回数值的公式。例如:设置最小值为2*f1将数据输入限制为某时段内的日期前几步与上例相同在“允许”框中，选择“日期”。在“数据”框中选择所需的限制类型。例如，要设置上限和下限，可以选择“大于输入允许的开始日期、结束日期或特定日期还可以输入返回日期的公式例如:=TODAY()，将数据限制为列表中的预定义项1.选择一个或多个要进行验证的单元格。2.在“数据”选项卡上的“数据工具”组中，单击“数据有效性”。4.在“数据有效性”对话框中，单击“设置”选项卡。5.在“允许”框中，选择“序列”。将数据限制为列表中的预定义项6.单击“来源”框，然后键入用MicrosoftWindows列表分隔符（默认情况下使用逗号）分隔的列表值。7.还可以选择在单击该单元格时显示一条输入信息。8.指定输入无效数据时希望MicrosoftOfficeExcel如何响应9.测试数据以确保其正常工作。限制文本字符数步骤与上同在“允许”框中，选择“文本长度”。在“数据”框中选择所需的限制类型。例如，要设置上限和下限，可以选择“小于或等于”。输入最小长度、最大长度或特定长度的文本。使用公式计算允许输入的内容在“数据有效性”对话框中，单击“设置”选项卡。在“允许”框中，选择“自定义”。在“公式”框中，输入计算结果为逻辑值（数据有效时为TRUE，数据无效时为FALSE）的公式例如:=COUNTIF($E$4:$E$34,E7)2数据的透视统计数据的分析无处不在谁是最有价值的客户？谁的销售额最高？那种产品最好买？利润最高？苦恼！！！公司的销售表有几十、几百几千张，看的我眼花缭乱，我怎么能整理出他们之间的内在联系呢？？？二维数据表已经不能反映出数据的内在联系。什么是数据的透视统计？数据透视表是包含数据各行各列的集合，可以提供数据的比较和揭示关系。数据透视表数据透视表:是一种交互式的、交叉制表的Excel报表，用于对多种来源进行汇总和分类。数据透视表的优点：以多种用户友好方式查询大量数据。对数值数据进行分类汇总展开或折叠要关注结果的数据级别将行移动到列或将列移动到行查看源数据的不同汇总。使您能够关注所需的信息(筛选、排序、分组)。创建数据透视表1、选择单元格区域（有列标题）中的一个单元格或将插入点放在一个MicrosoftOfficeExcel表中2、请在“插入”选项卡的“表”组中，单击“数据透视表”，然后单击“数据透视表”。Excel会显示“创建数据透视表”对话框。创建数据透视表3、选择需要分析的数据提示：请考虑使用表名引用而不是单元格区域，因为刷新数据时，添加到表的行会被自动包括到数据透视表中A、单击“选择一个表或区域”B、在“表/区域”框中键入单元格区域或表名引用注：如果在启动向导之前选定了单元格区域中的一个单元格或者插入点位于表中，Excel会在“表/区域”框中显示单元格区域或表名引用创建数据透视表4、通过执行下列操作之一来指定位置：A、新工作表若要将数据透视表放在新工作表中，并以单元格A1为起始位置，请单击“新建工作表”。B、现有工作表要将数据透视表放在现有工作表中，请选择“现有工作表”，然后指定要放置数据透视表的单元格区域的第一个单元格创建数据透视表5、单击“确定”。Excel会将空的数据透视表添加至指定位置并显示数据透视表字段列表，以便您可以添加字段、创建布局以及自定义数据透视表DEMO我的第一个数据透视表。解决问题：每个销售人员的销售额是多少？数据透视表的操作：浏览数据更改窗体布局和字段排列更改列、行和分类汇总的布局更改空白和错误的显示方式浏览数据展开和折叠数据，并显示与值有关的基本明细。对字段和项进行排序、筛选和分组。更改汇总函数，并且添加自定义计算和公式。更改数据透视表字段列表视图字段部分和区域部分堆积这是默认视图，是为少量字段而设计的。字段部分和区域部分并排此视图是为当您在各区域中有四个以上字段时添加和删除字段而设计的。仅字段此视图是为添加和删除多个字段而设计的。仅2x2区域部分此视图只是为重新排列多个字段而设计的。仅1x4区域部分此视图只是为重新排列多个字段而设计的。更改列、行和分类汇总的布局若要进一步优化报表的布局，可以执行影响列、行和分类汇总的更改，如在行上方显示分类汇总或关闭列标题。也可以重新排列一行或一列中的各项。数据透视表的其他操作当求和不能满足要求时，可以使用其他计算方式（计数，平均值、最大值、最小值）数据的动态更新（刷新数据）交换字段可以得到不同的视图显示每个销售人员销售额占总额的百分比美化透视表DEMO所有销售人员年终都有年销售额７％的奖金，今年公司效益好，为年销售额超过50万的员工发放销售额10%的奖金，如何计算，需要重新做表吗？更改空白和错误的显示方式更改错误和空单元格的显示方式。更改不含数据的项和标签的显示方式。显示或隐藏空白行。更改格式对单元格和区域进行手动和有条件格式设置。更改整个数据透视表的格式样式。更改字段的数字格式。数据透视图根据现有的数据透视表创建数据透视图单击所需的数据透视表。在“插入”选项卡上的“图表”组中，单击图表类型。可以使用除XY散点图、气泡图或股价图以外的任意图表类型Excel2007高级函数的使用查找和引用函数INDEX函数MATCH函数OFFSET函数VLOOKUP函数HLOOKUP函数INDEX函数返回表格或数组中的元素值，此元素由行号和列号的索引值给定。INDEX(array,row_num,column_num)Array为单元格区域或数组常量。Row_num数组中某行的行号，函数从该行返回数值。如果省略row_num，则必须有column_num。Column_num数组中某列的列标，函数从该列返回数值。如果省略column_num，则必须有row_num。MATCH函数MATCH函数可在单元格区域中搜索指定项，然后返回该项在单元格区域中的相对位置。语法MATCH(lookup_value,lookup_array,[match_type])lookup_value需要在lookup_array中查找的值。可以为值或对数字、文本或逻辑值的单元格引用。lookup_array必需。要搜索的单元格区域。match_type可选。数字-1、0或11或省略MATCH函数会查找小于或等于lookup_value的最大值0MATCH函数会查找等于lookup_value的第一个值。-1MATCH函数会查找大于或等于lookup_value的最小值。OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用。语法OFFSET(reference,rows,cols,height,width)Reference作为偏移量参照系的引用区域。Rows相对于偏移量参照系的左上角单元格，上（下）偏移的行数。Cols相对于偏移量参照系的左上角单元格，左（右）偏移的列数。Height高度，即所要返回的引用区域的行数。Width宽度，即所要返回的引用区域的列数。VLOOKUP函数VLOOKUP当比较值位于需要查找的数据左边的一列时，可以使用VLOOKUP。语法VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value为需要在表格数组第一列中查找的数值。Table_array为两列或多列数据。Col_index_num为table_array中待返回的匹配值的列序号。Range_lookup为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值统计函数AVERAGEA函数AVERAGEIF函数AVERAGEIFS函数COUNTA函数COUNTIF函数FREQUENCY函数AVERAGEA函数计算参数列表中数值的平均值（算术平均值）。语法AVERAGEA(value1,value2,...)Value1,value2,...为需要计算平均值的1到255个单元格、单元格区域或数值。注解参数可以是下列形式：数值；包含数值的名称、数组或引用；数字的文本表示；或者引用中的逻辑值，例如TRUE和FALSE。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。包含TRUE的参数作为1计算；包含FALSE的参数作为0计算。包含文本的数组或引用参数将作为0（零）计算。空文本()也作为0（零）计算。。如果参数为错误值，将会导致错误。如果要使计算不包括引用中的逻辑值和代表数字的文本，请使用AVERAGE函数。AVERAGEIF函数返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。语法AVERAGEIF(range,criteria,average_range)Range是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。Criteria是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为32、32、32、apples或B4。Average_range是要计算平均值的实际单元格集。如果忽略，则使用range。COUNTA函数COUNTA函数计算区域中不为空的单元格的个数。语法COUNTA(value1,[value2],...)COUNTA函数语法具有下列参数（参数：为操作、事件、方法、属性、函数或过程提供信息的值。）：value1必需。表示要计数的值的第一个参数。value2,...可选。表示要计数的值的其他参数，最多可包含255个参数。注解COUNTA函数可对包含任何类型信息的单元格进行计数，这些信息包括错误值和空文本()。例如，如果区域包含一个返回空字符串的公式，则COUNTA函数会将该值计算在内。COUNTA函数不会对空单元格进行计数。COUNTIF函数计算区域中满足给定条件的单元格的个数。语法COUNTIF(range,criteria)range是一个或多个要计数的单元格，其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。可以在COUNTIF公式中使用通配符“*”、“？”FREQUENCY函数计算数值在某个区域内的出现频率，然后返回一个垂直数组。语法FREQUENCY(data_array,bins_array)Data_array是一个数组或对一组数值的引用，您要为它计算频率。如果