excel操作技巧分享PPT学习教案

1、会计学1excel操作技巧分享操作技巧分享刚刚开始接触新手基本掌握常用功能初级水平熟练使用常用功能+数据透视表+公式中级水平+数组公式+VBA简单编程高级水平Excel技能+专业知识+行业经验专家2第1页/共90页3第2页/共90页 遇到问题的时候，如果知道应该使用什么功能，但是对这个功能不太会用，此时最好的办法是单击F1键调出Excel的联机帮助，集中精力学习这个需要掌握的功能。 如果遇到的问题不知从何下手，甚至不能确定Excel能否提供解决方法，可以求助于他人，如果没这样的受助机会，可以上网搜索解决方法，或者到某些Excel网站上去寻求帮助，如“Excel Home最佳学习方法”（网址：/

2、 当利用各种资源资源解决了自己的问题卓越，一定很有成就感，此时千万不要停止探索的脚步，争取把解决方法理解得更透彻，能做到举一反三。 暂时不用着的函数不必深究，但一定要了解，而不是简单的忽略。说不定哪天就需要用到的某个功能，Excel里面明明有，可是自己却不知道，以至于影响到寻找答案的速度。4第3页/共90页 学习Excel，阅读与实践必须并重。阅读来的东西，只有亲自在电脑上实践几次，才能把别人的知识真正转化为自己的知识。通过实践，还能够举一反三，即围绕一个知识点，做各种假设来测试，以验证自己的理解是否正解和完整。5第4页/共90页6第5页/共90页7第6页/共90页8第7页/共90页9第8页/

3、共90页 （1）双击任意一个选项卡即可隐藏功能区。当需要使用功能区时，只需单击任意选项卡，功能区就又出现了，完成当前操作后功能区又自动隐藏起来。 （2）单击【快速访问工具栏】右侧的下拉按钮，在弹出中的扩展菜单中单击【功能区最小化】命令。 （3）使用快捷键来显示或隐藏功能区。10第9页/共90页 （1）双击任意一个选项卡即可隐藏功能区。当需要使用功能区时，只需单击任意选项卡，功能区就又出现了，完成当前操作后功能区又自动隐藏起来。 （2）单击【快速访问工具栏】右侧的下拉按钮，在弹出中的扩展菜单中单击【功能区最小化】命令。 （3）使用快捷键来显示或隐藏功能区。11第10页/共90页12第11页/共9

4、0页123413第12页/共90页132414第13页/共90页1234515第14页/共90页213456716第15页/共90页121217第16页/共90页123418第17页/共90页12319第18页/共90页12320第19页/共90页2314 要取消工作表的冻结窗格状态，可以在Excel功能区上再次单击【视图】选项卡中的【冻结窗格】下拉菜单，在其扩展菜单中选择【取消冻结窗格】命令，窗口状态即恢复到冻结前的状态。21第20页/共90页 使用【拆分】命令同样可以做到标题行（或列）始终可见。但与【冻结窗格】不同的是，用鼠标可以拖动拆分柱以调整不同区域的大小。把拆分柱拖动屏幕的边缘，拆分

5、柱会消失，拆分窗口也随之减少。再次单击【拆分】命令将会取消当前的拆分效果。 在使用了【拆分】功能的工作表中，如果单击【冻结拆分窗格】命令，拆分柱会转换成相应的冻结线。而在使用了【冻结拆分窗格】功能的工作表中，单击【拆分】命令，则相当于取消冻结命令。22第21页/共90页1223第22页/共90页1224第23页/共90页25第24页/共90页26第25页/共90页27第26页/共90页1234528第27页/共90页29第28页/共90页30第29页/共90页31第30页/共90页创建图表后，您可以轻松地向此图表添加新元素，如图表标题或新布局（点击【设计】【图表布局】）。32第31页/共90页

6、33第32页/共90页34第33页/共90页第五部分第五部分 数据透视表介绍数据透视表介绍35第34页/共90页如上图中的表格属于交叉表，不太适合依据此表创建数据透视表（不是不能使用数据透视表，只是使用上表创建数据透视表某些功能无法体现）。因为其月份被分为 12 个字段，互相比较起来比较麻烦。最好将其改为如下结构：36第35页/共90页上表只使用一个“月份”字段，而 12 个月作为月份字段的值，这样互相比较起来比较容易。使用此结构的表格，通过数据透视表，很容易创建上图所示的交叉表格，但反之则很麻烦。因此，创建数据透视表之前，要注意表格的结构问题。越简单越好，就类似数据库的存储方式。或者，能纵向

7、排列的表格就不要横向排列。37第36页/共90页创建数据透视表创建数据透视表1、将光标点在表格数据源中任意有内容的单元格，或者将整个数据区域选中。2、选择“插入”选项卡，单击“数据透视表”命令。3、在弹出的“创建数据透视表”对话框中，“请选择要分析的数据”一项已经自动选中了光标所处位置的整个连续数据区域，也可以在此对话框中重新选择想要分析的数据区域（还可以使用外部数据源，请参阅后面内容）。“选择放置数据透视表位置”项，可以在新的工作表中创建数据透视表，也可以将数据透视表放置在当前的某个工作表中。 38第37页/共90页4、单击确定。Excel 自动创建了一个空的数据透视表。 上图中左边为数据透

8、视表的报表生成区域，会随着选择的字段不同而自动更新；右侧为数据透视表字段列表。创建数据透视表后，可以使用数据透视表字段列表来添加字段。如果要更改数据透视表，可以使用该字段列表来重新排列和删除字段。默认情况下，数据透视表字段列表显示两部分：上方的字段部分用于添加和删除字段，下方的布局部分用于重新排列和重新定位字段。可以将数据透视表字段列表停靠在窗口的任意一侧，然后沿水平方向调整其大小；也可以取消停靠数据透视表字段列表，此时既可以沿垂直方向也可以沿水平方向调整其大小。 右下方为数据透视表的 4 个区域，其中“报表筛选”、“列标签”、“行标签”区域用于放置分类字段，“数值”区域放置数据汇总字段。当将

9、字段拖动到数据透视表区域中时，左侧会自动生成数据透视表报表。39第38页/共90页数据透视表字段的使用数据透视表字段的使用 将字段拖动到“行标签”区域，则此字段中的每类项目会成为一行；我们可以将希望按行显示的字段拖动到此区域。 将字段拖动到“列字段”区域，则此字段种的每类项目会成为列；我们可以将希望按列显示的字段拖动到此区域。 将字段拖动到“数值”区域，则会自动计算此字段的汇总信息（如求和、计数、平均值、方差等等）；我们可以将任何希望汇总的字段拖动到此区域。 将字段拖动到“报表筛选”区域，则可以根据此字段对报表实现筛选，可以显示每类项目相关的报表。我们可以将较大范围的分类拖动到此区域，以实现报

10、表筛选。 使用行、列标签区域： 例如在数据透视表字段列表中选中“部门”、“性别”和“年龄 ”字段。这时候“产品名称”字段自动出现在“行标签”区域；由于“年龄”字段是“数字”型数据，自动出现在数据透视表的“数值”区域。数值区域默认是求和模式，因我们要统计平均年龄，则在数值下拉菜单处选“值字段设置”，计算类型选“平均值”，再点“数字格式”，设置小数点位数。如下图：40第39页/共90页结果如下图：41第40页/共90页 在 Excel 2007 的数据透视表中，如果勾选的字段是文本类型，字段默认自动出现在行标签中，如果勾选的字段是数值类型的，字段默认自动出现在数值区域中。我们也可以将关注的字段直接

11、拖动到相应的区域中。 结果如图：42第41页/共90页报表筛选字段的使用报表筛选字段的使用 例如将“部门”字段拖动到“报表筛选”区域，将“性别”字段拖动到“列标签”区域，将“所在项目” 字段拖动到“行标签”区域，将“年龄”拖动到“数值”区域，则可以按部门查看每个部门项目部男女平均年龄。 在“报表筛选”区域，可以对报表实现筛选，查看所关注的特定地区的详细信息。直接单击“报表筛选”区域中“地区”字段右边的下拉键头，即可对数据透视表实现筛选。43第42页/共90页第六部分第六部分 常用函数介绍常用函数介绍44第43页/共90页根据对指定的条件计算结果为 TRUE 或 FALSE，返回不同的结果。可以

12、使用 IF 对数值和公式执行条件检测。Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算某单元格区域内某个文本字符串或数字出现的次数，则可使用 COUNTIF 和 COUNTIFS 电子表格函数。若要计算基于某区域内一个文本字符串或一个数值的总和，可使用 SUMIF 和 SUMIFS 工作表函数。45第44页/共90页所有参数的计算结果为 TRUE 时，返回 TRUE；只要有一个参数的计算结果为 FALSE，即返回 FALSE。通过 AND 函数可以检验多个不同的条件，而不仅仅是一个条件。=AND(=AND(logical1logical1, ,

13、logical2logical2, , .) )注意：注意：u参数的计算结果必须是逻辑值（如参数的计算结果必须是逻辑值（如 TRUE TRUE 或或 FALSEFALSE），而参数必须是包含逻辑值的数组），而参数必须是包含逻辑值的数组u如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。u如果指定的单元格区域未包含逻辑值，则如果指定的单元格区域未包含逻辑值，则 函数将返回错误值函数将返回错误值 #VALUE!#VALUE!。例：假如有人上街，每个人买零食或者买衣服花费了一定的金额，现在要筛选出既买了衣服，又买了零食的人，那么可

14、以用AND函数进行如下操作：46第45页/共90页=OR(=OR(logical1logical1, ,logical2logical2, ,.) )在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。注意：注意：u参数的计算结果必须是逻辑值（如参数的计算结果必须是逻辑值（如 TRUE TRUE 或或 FALSEFALSE），而参数必须是包含逻辑值的数组），而参数必须是包含逻辑值的数组u如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。u如果指定的单元格区域

15、未包含逻辑值，则如果指定的单元格区域未包含逻辑值，则 函数将返回错误值函数将返回错误值 #VALUE!#VALUE!。例：如要求下表中营业收入和合同均大于10000的，则在D2输入公式=or(B2=10000,C210000)，D列结果为FALSE为不满足条件。47第46页/共90页=LARGE=LARGE(arrayarray,k k)返回数据集中第 k 个最大值。使用此函数可以根据相对标准来选择数值。Array 为需要从中选择第 k 个最大值的数组或数据区域。K 为返回值在数组或数据单元格区域中的位置（从大到小排）。注解注解u如果数组为空，函数如果数组为空，函数 LARGE LARGE 返

16、回错误值返回错误值 #NUM!#NUM!。u如果如果 k 0 k 0 或或 k k 大于数据点的个数，函数大于数据点的个数，函数 LARGE LARGE 返回错误值返回错误值 #NUM!#NUM!。u如果区域中数据点的个数为如果区域中数据点的个数为 n n，则函数，则函数 LARGE(array,1) LARGE(array,1) 返回最大值，函数返回最大值，函数 LARGE(LARGE(array,narray,n) ) 返回最小返回最小值。值。=SMALL(=SMALL(arrayarray, ,k k) ) 返回数据集中第 k 个最小值。48第47页/共90页=SMALL(=SMALL(

17、arrayarray, ,k k) ) 返回数据集中第 k 个最小值。49第48页/共90页=MAX(=MAX(number1number1, ,number2number2, ,.) )返回一组值中的最大值。注解u参数可以是数字或者是包含数字的名称、数组或引用。 u逻辑值和直接键入到参数列表中代表数字的文本被计算在内。 u如果参数为数组或引用，则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。u如果参数不包含数字，函数 MAX 返回 0（零）。u如果参数为错误值或为不能转换为数字的文本，将会导致错误。 u如果要使计算包括引用中的逻辑值和代表数字的文本，请使用 MA

18、XA 函数。=MIN(=MIN(number1number1, ,number2number2, ,.) ) 返回一组值中的最小值。=MEDIAN(=MEDIAN(number1number1, ,number2number2, ,.) ) 返回给定数值的中值。中值是在一组数值中居于中间的数值。=MODE(=MODE(number1number1, ,number2number2, ,.) ) 返回在某一数组或数据区域中出现频率最多（众数）的数值。50第49页/共90页=AVERAGE(=AVERAGE(number1number1, , number2number2, ,.) )返回参数的平

19、均值（算术平均值）。注释：u参数可以是数字或者是包含数字的名称、单元格区域或单元格引用。u逻辑值和直接键入到参数列表中代表数字的文本被计逻辑值和直接键入到参数列表中代表数字的文本被计算在内。算在内。u如果区域或单元格引用参数包含文本、逻辑值或空单如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。元格，则这些值将被忽略；但包含零值的单元格将被计算在内。u如果参数为错误值或为不能转换为数字的文本，将会导致错误。u若要在计算中包含引用中的逻辑值和代表数字的文本，请使用 AVERAGEA 函数。u若要只对符合某些条件的值计算平均值，请使用 AVERA

20、GEIF 函数或 AVERAGEIFS 函数。=AVERAGE(A2:A6,5),即(A2+A3+A4+A5+A6+5)/6的结果51第50页/共90页=AVERAGEIF(=AVERAGEIF(rangerange, ,criteriacriteria, ,average_rangeaverage_range) )注解：u忽略区域中包含 TRUE 或 FALSE 的单元格。 u如果 average_range 中的单元格为空单元格，AVERAGEIF 将忽略它。u如果 range 为空值或文本值，则 AVERAGEIF 会返回 #DIV0! 错误值。u如果条件中的单元格为空单元格，AVERA

21、GEIF 就会将其视为 0 值。u如果区域中没有满足条件的单元格，则 AVERAGEIF 会返回 #DIV/0! 错误值。u您可以在条件中使用通配符，即问号 (?) 和星号 (*)。uAverage_rangeAverage_range 不必与不必与 range range 的大小和形状相同。求平均值的实际单元格是通过使用的大小和形状相同。求平均值的实际单元格是通过使用 average_rangeaverage_range 中左上方的单中左上方的单元格作为起始单元格，然后加入与元格作为起始单元格，然后加入与 range range 的大小和形状相对应的单元格确定的。的大小和形状相对应的单元格确

22、定的。range 是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。criteria 是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为 32、32、32、苹果 或 B4。 average_range 是要计算平均值的实际单元格集。如果忽略，则使用 range。52第51页/共90页53第52页/共90页54第53页/共90页=SUM(=SUM(number1number1, ,number2number2, , .) )说明u直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，请参阅下面的示例一和示例二。u如果

23、参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。请参阅下面的第三个示例。u如果参数为错误值或为不能转换为数字的文本，将会导致错误。55第54页/共90页56第55页/共90页=COUNT(=COUNT(value1value1, , value2value2, ,.) )注解u如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如 1），则将被计算在内。u逻辑值和直接键入到参数列表中代表数字的文本被计算在内。u如果参数为错误值或不能转换为数字的文本，则不会被计算在内。u如果参数为数组或引用，则只计算数组或引用中数字的个数。不会计算数组或引用

24、中的空单元格、逻辑值、文本或错误值。 u若要计算逻辑值、文本值或错误值的个数，请使用 COUNTA 函数。u若要只计算符合某一条件的数字的个数，请使用 COUNTIF 函数或 COUNTIFS 函数。=COUNTIF(=COUNTIF(rangerange, ,criteriacriteria) ) 计算某个区域中满足给定条件的单元格数。例如：=COUNTIF(A2:A7,1”重复”“)。二、用出生年月来计算年龄公式：=TRUNC(DAYS360(H6”2009/8/30FALSE)/3600)。三、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E274)”/”M

25、ID(E2112)”/”MID(E2132)。四、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)=15IF(MOD(MID(C2151)2)=1”男”女”)IF(MOD(MID(C2171)2)=1”男”女”)公式内的“C2”代表的是输入身份证号码的单元格。五、求和： =SUM(K2:K56) 对K2到K56这一区域进行求和； 六、平均数： =AVERAGE(K2:K56) 对K2 K56这一区域求平均数； 七、排名： =RANK(K2，K$2:K$56) 对55名学生的成绩进行排名； 八、等级： =IF(K2=85”优”IF(K2=74”良”IF(K2=60

26、”及格”不及格”)九、学期总评： =K2*0.3+M2*0.3+N2*0.4 假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 十、最高分： =MAX(K2:K56) 求K2到K56区域(55名学生)的最高分； 十一、最低分： =MIN(K2:K56) 求K2到K56区域(55名学生)的最低分；9 9、常用函数公式（简）、常用函数公式（简）88第87页/共90页十二、分数段人数统计： (1) =COUNTIF(K2:K56”100) 求K2到K56区域100分的人数;假设把结果存放于K57单元格； (2) =COUNTIF(K2:K56”=95)-K57 求K2到K56区域9599.5分的人数;假设把结果存放于K58单元格； (3)=COUNTIF(K2:K56”=90)-SUM(K57:K58) 求K2到K56区域9094.5分的人数;假设把结果存放于K59单元格； (4)=COUNTIF(K2:K56”=85)-SUM(K57:K59) 求K2到K56区域8589.5分的人数;假设把结果存放于K60单元格； (5)=COUNTIF(K2:K56”=70)-SUM(K57:K60) 求K2到