1、《Excel 2010 函数与公式》 人民邮电出版社 胡建学编著 2、《EXCEL 2010从入门到精通》 科学出版社 杰创文化编著

Presentation on theme: "1、《Excel 2010 函数与公式》 人民邮电出版社 胡建学编著 2、《EXCEL 2010从入门到精通》 科学出版社 杰创文化编著"— Presentation transcript:

1 1、《Excel 2010 函数与公式》 人民邮电出版社 胡建学编著 2、《EXCEL 2010从入门到精通》 科学出版社 杰创文化编著
课程名称：office 软件应用技术 推荐参考书： 1、《Excel 2010 函数与公式》 人民邮电出版社 胡建学编著 2、《EXCEL 2010从入门到精通》 科学出版社 杰创文化编著 教 师：刘振章 电 话: (622149) 教学资料：网络课堂

2 Excel 2000基础篇 1 数据输入 文本数据、日期数据、数值数据、自动填充 2 工作表管理 3 公式与函数
插入、删除、复制、移动和重命名 3 公式与函数 使用方法、绝对引用与相对应用、常用函数介绍 4 数据隐藏与保护 常用数据隐藏于保护策略 5 使用技巧 工作表的拆分与冻结、记忆式输入、自动切换输入法

3 一、数据的输入 1、可以在单元格输入数据，也可以在编辑框内输入数据；
2、输入结束后按ENTER、TAB、箭头键或用鼠标单击编辑栏上的“√”，可确认输入；按ESC或单击编辑栏上的“×”可以取消输入； 3、单元格数据的输入：可以输入常量和公式，可以输入的常量数据类型为：文本型、数值型、逻辑类型和日期时间型

4 日期数据 日期使用斜线或连字符“-”输入，格式比较自由，例如： “ ”、“13-9-5”均可，也可以 “13/9/5”。

5 数值数据 1、可以使用科学计数法表示数字，如123000可写成1.23E5。
2、数值数字右对齐，数值输入超长，以科学记数法显示， 最多15位有效数字 。 3、如果数字太长无法在单元格中显示，单元格将以“###”显示，此时需更改列宽。 4、数字前加“￥”或“$”则以货币形式显示，例如： ￥ 123，456。 5、数字也可用百分比形式表示。如45% 【例】以下数据都是合法的数字输入形式： 2.780；1.789%；50E2；0 4/5；$7.6；￥8.3

6 数据自动填充 使用填充柄自动填充一批有规律的数据，例如等差、等比数列和预定义的序列。 以等差趋势填充一个序列 以等比趋势填充一个序列
至少选定两个含有趋势初始值的单元格。拖动填充柄向需要升序或降序的方向移动。 例如，如果在单元格 C1:D1 中的初始值为 3、5 ，则向右拖动填充柄将以升序填充，向左拖动将以降序填充。 以等比趋势填充一个序列 至少选定两个含有趋势初始值的单元格。按住鼠标右键，拖动填充柄向需要升序或降序的方向移动。 例如，如果在单元格 C1:D1 中的初始值为 2 和 4，则向右拖动填充柄将以升序填充，向左拖动将以降序填充。 释放鼠标右键，再单击快捷菜单上的“等比序列”。 填充柄的使用 单个单元格内容为纯字符、纯数字、或是公式，填充相当于复制 单个单元格内容为文字数字混合体，填充时文字不变，最右边的数字递增，例如：数组填充

7 数据自动填充 使用填充柄自动填充一批有规律的数据，例如等差、等比数列和预定义的序列。 使用工具栏填充 自动填充序列
系统定义序列：单个单元格内容为Excel预设的自动填充序列中的一员，按预设序列填充，例如：初始值为一月，自动填充为二月、三月等。 自定义序列：文件－选项－高级－常规-编辑自定义系列

8 二、工作表管理 1. 选取工作表 单个、连续多个、不连续多个。 2. 删除工作表 选取要删除的工作表 编辑/删除工作表 3. 插入工作表
选取要删除的工作表 编辑/删除工作表 3. 插入工作表 选取插入工作表的位置 插入/工作表 4. 重命名工作表 双击要重命名的工作表 输入新名字

9 5. 工作簿之间的复制或移动 6. 工作簿内的复制或移动 复制: Ctrl+拖曳 打开源工作簿 和目标工作簿 选取源工作表
编辑/移动或复制工作表 选中“建立副本”复选框为复制，否则为移动。 6. 工作簿内的复制或移动 复制: Ctrl+拖曳 移动: 拖曳

10 三、公式与函数 公式形式：＝操作数、运算符和函数 操作数 ：单元格、数字、字符、区域 运算符 ：算术 ＋、－、*、／、、（） 字符 ＆
电子表格可以在单元格中输入公式和函数，可以对表中数据进行总计、 平均、汇总以及其它更为复杂的运算，数据修改后公式的计算结果 也自动更新 1. 使用公式 公式形式：＝操作数、运算符和函数 操作数　：单元格、数字、字符、区域 运算符　：算术　＋、－、*、／、、（） 字符　＆ 关系 =、>、>=、<=、<、<> 公式例 ：=C5*1.15+D =C5+Sheet2!C5+C6

11 2.使用函数 在Excel中提供了9类函数共几百个函数(数学、日期、文字等)。 函数形式：函数名（参数表） 输入函数方法: (1)直接输入:
例：求C5:C8单元格的平均值，结果存放在C10单元格中。 在编辑框直接输入：=AVERAGE(C5:C8) (2)使用函数向导

12 (1) 概念 (3) 相互转换 3.单元格的引用 相对引用：公式中引用的单元格地址在公式复制、移动时自行调整
绝对引用：公式中引用的单元格地址在公式复制、移动时不会改变 (2)表示 相对引用：列坐标行坐标　　例B6， A4 , C5:F8 绝对引用：$列坐标$行坐标　例$B$6,　$A$4 , $C$5:$F$8 混合引用：列坐标$行坐标 例 B$6, A$4, C$5:F$8 $列坐标行坐标 例 $B6, $A4, $C5:$F8 引用同一工作簿中其它工作表中的单元格：Sheet2!B6+Sheet1!A4 (3) 相互转换 反复按 F4 键 选定单元格中的引用部分

13 4.常用函数(一) ABS 求出参数的绝对值。 数据计算
　　AND “与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。 条件判断 　　AVERAGE 求出所有参数的算术平均值。 数据计算 　　COLUMN 显示所引用单元格的列标号值。 显示位置 　　CONCATENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。 字符合并 　　COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。 条件统计 　　DATE 给出指定数值的日期。 显示日期 　　DATEDIF 计算返回两个日期参数的差值。 计算天数 　　DAY 计算参数中指定日期或引用单元格中的日期天数。 计算天数 　　DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。 条件统计 　　FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。 概率计算　　

14 4.常用函数(二) FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。 概率计算
IF 根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果。 条件计算 INDEX 返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。 数据定位 INT 将数值向下取整为最接近的整数。 数据计算 ISERROR 用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回 FALSE。 逻辑判断 LEFT 从一个文本字符串的第一个字符开始，截取指定数目的字符。 截取数据 LEN 统计文本字符串中字符数目。 字符统计 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。 匹配位置 MAX 求出一组数中的最大值。 数据计算 MID 从一个文本字符串的指定位置开始，截取指定数目的字符。 字符截取 MIN 求出一组数中的最小值。 数据计算 MOD 求出两数相除的余数。 数据计算 MONTH 求出指定日期或引用单元格中的日期的月份。 日期计算 NOW 给出当前系统日期和时间。 显示日期时间

15 4.常用函数(三) OR 仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返 回逻辑“真（TRUE）”。 逻辑判断 RANK 返回某一数值在一列数值中的相对于其他数值的排位。 数据排序 RIGHT 从一个文本字符串的最后一个字符开始，截取指定数目的字符。 字符截取 SUBTOTAL 返回列表或数据库中的分类汇总。 分类汇总 SUM 求出一组数值的和。 数据计算 SUMIF 计算符合指定条件的单元格区域内的数值和。 条件数据计算 TEXT 根据指定的数值格式将相应的数字转换为文本形式 数值文本转换 TODAY 给出系统日期 显示日期 VALUE 将一个代表数值的文本型字符串转换为数值型。 文本数值转换 VLOOKUP 在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值条件定位 WEEKDAY 给出指定日期的对应的星期数。 星期计算

16 5. Excel错误信息 1、＃＃＃＃＃!: 单元格中公式所产生的结果太长，容纳不下；
2、＃DIV/O! :除数为0，或者除数指向了一个空单元格。 3、＃VALUE! 在需要数字或逻辑值时却输入了文本的原因； 4、＃name! 使用了Excel不能识别的文本而产生的错误；删除了公式中使用的共同名称或使用了不存在以及拼写错误的名称所致。 5、＃N/A ! ，在函数或公式中没有可用数值时产生的错误信息。例如： 6、＃REF! 单元格引用无效。比如，我们删除了有其他公式引用的单元格。 7、＃NUM! 公式或函数中某个数字有问题。例如，在需要数字参数的函数中使用了不能接受的参数，或者公式产生的数字太大或太小等。 LOG(,10)

17 四. 数据保护与隐藏 1.数据保护 保护工作簿和工作表，可以限制修改数据。如想修改数据必须 取消保护。 (1)工作簿保护:审核-保护工作簿。
1.数据保护　 保护工作簿和工作表，可以限制修改数据。如想修改数据必须 取消保护。 (1)工作簿保护:审核-保护工作簿。 选中“结构”复选框：工作簿中的工作表不能进行移动、删除、隐藏、取消隐藏或重新命名，也不能插入新的工作表。 选中“窗口”复选框：打开工作簿时保持窗口的固定位置和大小。 为防止他人取消工作簿保护，可以键入密码。 撤消保护:工具-保护-撤消工作簿保护，如有密码保护必须输入密码。

18 (2)工作表保护:保护工作表中的数据不被修改。
审核-保护工作表。 为防止他人取消工作表保护，可以键入密码。 (锁定选项必须有效) 撤消保护:审核-撤消工作表保护，如有密码保护必须输入密码。

19 2.数据隐藏 隐藏工作簿的一部分，数据将从视图中消失，但并没有从工作 簿中删除。如果保存并关闭了工作簿，下次打开它时隐藏的数
据仍然会是隐藏的。打印工作簿时，Microsoft Excel不会打 印隐藏部分。 (1)工作簿隐藏:视图-隐藏； 取消隐藏：视图-取消隐藏

20 2.数据隐藏 (2)工作表隐藏: 选定工作表，开始-格式-可见性-隐藏和取消隐藏 结合工作簿的保护实现真正隐藏。

21 2.数据隐藏 (3)行或列隐藏: 选定行或列，开始-格式-可见性-隐藏和取消隐藏 取消隐藏：选择区域,开始-格式-可见性-隐藏和取消隐藏
结合工作表保护功能，实现真正隐藏。

22 (4)单元格隐藏: 选定单元格，开始-格式-单元格格式，数字-自定义类型;;;(在单元格中不可见）; 开始-格式-单元格格式，保护-隐藏; 审核-保护工作表（在编辑框中不可见）. 取消隐藏：撤消对工作表的保护，恢复数值类型 (5)区域隐藏:选定区域，隐藏方法与单元格隐藏方法一样 取消隐藏：选择区域,方法与单元格取消隐藏方法一样 (6)公式隐藏: 选定单元格或区域，格式-单元格-保护-隐藏 工具-保护-工作表 取消隐藏：撤消对工作表的保

23 部分数据保护 保护工作表后允许部分数据修改。

24 3.常用数据保护方案 (1).为工作簿设置密码： 单击“文件”菜单中的“另存为”命令。 单击“工具”菜单上的“常规选项”。
在“打开权限密码”或“修改权限密码”编辑框中，选定代表原密码的符号。 如果要更改密码，请键入新密码，然后单击“确定”按钮。 密码是区分大小写的。 如果要取消密码，按 DELETE 键，然后单击“确定”按钮。

25 (2) 隐藏工作表并使用密码保护工作簿 工作簿可以读写，但是某些工作表通过密码才能看得见。 隐藏工作表； 使用密码保护工作簿；

26 (3).公式的保护 隐藏公式,用口令保护工作表 (4).行列或单元格的保护 隐藏行列或单元格,用口令保护工作表

27 口令设置原则 口令要足够长，应该不少于8个字符 。 同时包含多种类型的字符，如大写字母、小写字母、数字、标点符号、不可见的ASCII字符等。
不要选取显而易见的信息作口令，例如单词、姓名缩写 ，证件编码、著名商标等。 不要在不同系统上使用同一口令。 不要将口令写下来，也不要将口令存于电脑文件中。 不要让别人知道 ，在输入口令时应确认无人在身边 定期改变口令，至少6个月要改变一次.

28 双核心PC暴力破解 最简单的数字密码－－六位（例如银行密码）瞬间搞定，八位348分钟，十位163天
普通大小写字母－－六位33分钟，八位62天 数字＋大小写字母－－六位一个半小时，八位253天 数字＋大小写字母＋标点－－六位22小时，八位23年

29 五.常用技巧介绍 1.工作表的拆分与冻结　 (1)工作表的拆分：把工作表窗口拆分为几个窗格。每个窗格中都包含了工作表的内容，便于对工作表中感兴趣部分对比观看。 可以进行水平拆分、垂直拆分、水平和垂直拆分

30 (2)工作表窗口的冻结 冻结窗格使您得以选择滚动工作表时始终保持可见的数据。在 滚动时保持行和列标志可见。 选择冻结位置
选择“窗口”菜单中的“冻结窗口”命令。

31 2.快速给数字加单位 有时我们需要给输入的数值加上单位（如“立方米”等），少量的我们可以直接输入，而大量的如果一个一个地输入就显得太慢了。
2.快速给数字加单位　 有时我们需要给输入的数值加上单位（如“立方米”等），少量的我们可以直接输入，而大量的如果一个一个地输入就显得太慢了。 选取需要加同一单位的单元格; 格式→单元格，在“数字”标签中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入“#”“立”“方”“米”，按下确定键后，单位（立方米）即一次性加到相应数值的后面。

32 3.数据有效性* 限制数据类型 限制数据范围 限制数据长度
限制重复数据：在A列中没有重复数据，选择“自定义”，在“公式”栏中输入=countif(a:a,a1)=1 设置输入提示信息 设置错误信息 圈释无效数据

33 自动切换输入法 ---- 在一张工作表中，往往是既有数据，又有文字，这样在输入时就需要来回在中英文之间反复切换输入法，非常麻烦，你可以用以下方法实现自动切换。方法是： (1)打开所用的中文输入法，再选中需要输入中文的单元格区域； (2)打开“有效数据”对话框，选中“IME模式”选项卡，在“模式”框中选择打开，单击“确定”按钮； (3)选中输入数字的单元格区域，在“有效数据”对话框中，单击“IME模式”选项卡，在“模式”框中选择关闭（英文模式）； (4)单击“确定”按钮。这样，用鼠标分别在刚才设定的两列中选中单元格，五笔和英文输入方式就可以相互切换了。

34 4.用下拉列表快速输入数据 如果某些单元格区域中要输入的数据很有规律，如学历(小学、初
中、高中、中专、大专、本科、硕士、博士)、职称(技术员、助理 工程师、工程师、高级工程师)等，你希望减少手工录入的工作量， 这时我们就可以设置下拉列表实现选择输入。 选取需要设置下拉列表的单元格区域; “数据→有效性”命令，从“数据有效性”对话框中选择“设置”选项卡，在“允许”下拉列表中选择“序列”，在“来源”框中输入我们设置下拉列表所需的数据序列，如“技术员,助理工程师,工程师,高级工程师”; 选中复选框“提供下拉箭头”被选中，单击“确定”按钮即可。 这样在输入数据的时候，就可以单击单元格右侧的下拉箭头选择输入数据，从而加快了输入速度。

35 5.单元格内换行(ALT + Enter) 6.多个单元格中输入相同的数据或公式 要在一个单元格中输入几行文字，如果输入一行后敲回车键就
会移到下一单元格，而不是换行。 在输入第一行内容后，在换行处按“Alt+回车键”，即可输入 第二行内容，再按“Alt+回车键”输入第三行以此类推 6.多个单元格中输入相同的数据或公式 选取需要输入相同数据区域; 输入数据或公式 Ctrl+Enter

36 7.快速输入自定义短语 使用该功能可以把经常使用的文字定义为一条短语，当输入该条短语时，“自动更正”便会将它更换成所定义的文字。
定义“自动更正”项目的方法: 单击“文件→选项→校对”命令; 在弹出的“自动更正”对话框中的“替换”框中键入短语，如“qy”，在“替换为”框中键入要替换的内容，如“湖北汽车工业学院”，单击“添加”按钮，将该项目添加到项目列表中，单击“确定”退出。以后只要输入“qy”，则“湖北汽车工业学院”这个短语就会输到表格中。

37 8.将复制的单元格安全地插入到现有单元格之间
如果你想要将一块复制的单元格插入到其他行或列之间而不是 覆盖这些行或列,可以通过下面这个简单的操作来完成: 选择你将要复制的单元格 单击编辑菜单上的复制 在工作表上选择将要放置被复制单元格的区域 按下“Ctrl+Shift+ +” 在插入对话框中选择周围单元格的移动方向，然后单击“确定”。现在，复制的单元格将插入到合适的位置，而你无需担心它们覆盖原有的信息。

38 9.输入15位以上的数字编码 (1) 用’引导 (2) 设置单元格格式为文本
10. 显示公式和公式求值 （1）显示公式 如果工作表中的数据多数是由公式生成的，如果想要快速知道每个单 元格中的公式形式，可以这样做： “公式”- “显示公式”。 （2）公式求值： “公式”- “公式求值” （3）错误检查

39 11.自动设置小数点 如果有一大批小于1的数字要录入到Excel工作表中，如果录入前先设置自 动调整小数点，将会使输入速度成倍提高。 单击“文件”-“选项”-“高级”选项卡，选中“自动设置小数点”复选 框，在“位数”微调编辑框中键入需要显示在小数点右面的位数。在此， 我们键入“2”单击“确定”按钮。 此时如果你在单元格中键入的是“8888”，则在你结束输入之后，该单元 格的数字自动变为“88.88”。

40 数据管理 1、数据获取 数据来源：ACCESS，网页，文本，数据库 2、数据分列 3、合并计算 4、数据筛选 5、数据透视表与数据透视图 插入-数据透视表