EXCEL培训42110实用教案

1、 EXCEL是日常工作中应用最普遍的工具。对于审计人员来说，是日常工作中应用最普遍的工具。对于审计人员来说，不但不但(bdn)要掌握基本的操作，还要学会一些操作技巧以及数要掌握基本的操作，还要学会一些操作技巧以及数据分析、函数、图表等部分高级功能，提升据分析、函数、图表等部分高级功能，提升EXCEL操作水平，操作水平，提高工作效率。提高工作效率。引言(ynyn)第1页/共59页第一页，共60页。EXCEL 功能(gngnng)分类2函数1操作3分析5VBA4图表第2页/共59页第二页，共60页。基础基础(jch)操作操作 is the1rd 第3页/共59页第三页，共60页。页面设置和打印页面

2、设置和打印(d (d yn)yn) 设置页面设置页面(y min)位置位置 打印顶端标题行打印顶端标题行 设置页眉页脚设置页眉页脚 设置分页符设置分页符 使用使用“照相机照相机”第4页/共59页第四页，共60页。 2设置(shzh)页边距 设置居中设置居中(jzhng)方式方式第5页/共59页第五页，共60页。3设置设置(shzh)页眉页眉/页脚页脚页眉页眉/ /页脚设置页脚设置(shzh)(shzh)对话框对话框第6页/共59页第六页，共60页。图图4-136 自定义页眉对话框自定义页眉对话框页眉页眉/ /页脚设置页脚设置(shzh)(shzh)对话框对话框第7页/共59页第七页，共60页。

3、 4工作(gngzu)表 设置顶端设置顶端(dngdun)标题行标题行第8页/共59页第八页，共60页。worldsis thetrailing only常用常用(chn yn)快捷键快捷键CTRL+CCTRL+C：复制CTRL+ZCTRL+Z：剪切CTRL+VCTRL+V：粘贴CTRL+Y(F4)CTRL+Y(F4)：重复上一步操作在行或列间双击鼠标左键，行或列自动宽度自动适应F8F8SHIFT+SHIFT+鼠标左键：移动(ydng)(ydng)列或区块输入身份证：在单元格中，先打个逗号，然后输入数字第9页/共59页第九页，共60页。worldsis thetrailing only常用常用

4、(chn yn)快捷键快捷键插入空白单元格插入多行多列转换行列次序反转表格：复制(fzh)表格右键选择性粘贴选择“转置”第10页/共59页第十页，共60页。 excel中如何设单元格为下拉选择框，从而使其他的单元格的数值与本单元格的选择相对应的变化？ 点菜单 数据-有效性-设置选项卡-允许下拉选单中，选择序列-在下面的来源中输入所有可选的内容用逗号隔开，如“1，2，3”，或者点右边的选择按钮到文件里面选择一个区域，如果希望输入的数据不受这个条件的限制，比如我想输入“4”而软件不报错的话，就切换到 出错(ch cu)警告 选项卡，去掉 输入无效数据

5、时显示出错(ch cu)警告 前面的钩，确定之后，看一看是不是有了下拉选择框。 其它的单元格数值需要随之变化的话，需要设置公式，建议使用IF公式的嵌套，这个公式比较简单，容易掌握。第11页/共59页第十一页，共60页。 隐藏工作表的方法如下隐藏工作表的方法如下(rxi)(rxi)： （1 1）选择要隐藏的工作表。）选择要隐藏的工作表。 （2 2）单击）单击“格式格式”菜单中菜单中“工作表工作表”命令，在弹出的级联菜单中单击命令，在弹出的级联菜单中单击“隐藏隐藏”命令。命令。 取消隐藏工作表的方法如下取消隐藏工作表的方法如下(rxi)(rxi)： （1 1）单击）单击“格式格式”菜单中的菜单中的

6、“工作表工作表”命令，在弹出的子菜单中单击命令，在弹出的子菜单中单击“取消取消隐藏隐藏”命令。命令。 （2 2）在弹出的）在弹出的“取消隐藏取消隐藏”对话框中选择要取消隐藏的工作表。对话框中选择要取消隐藏的工作表。隐藏隐藏/取消工作取消工作(gngzu)表的表的方法方法第12页/共59页第十二页，共60页。 1 1工作表窗口的拆分工作表窗口的拆分 拆分可分为三种：水平拆分、垂直拆分、水平和垂直同时拆分。拆分可分为三种：水平拆分、垂直拆分、水平和垂直同时拆分。 2 2工作表窗口的冻结工作表窗口的冻结 窗口冻结操作方法：窗口冻结操作方法： （1 1）选择冻结位置）选择冻结位置(wi zhi) (w

7、i zhi) （2 2）选择）选择“窗口窗口”菜单中的菜单中的“冻结窗口冻结窗口”命令。命令。工作工作(gngzu)表窗口拆分表窗口拆分第13页/共59页第十三页，共60页。图图4-36 工作表窗口工作表窗口(chungku)拆分示例拆分示例第14页/共59页第十四页，共60页。图图4-37 窗口窗口(chungku)冻结示例冻结示例第15页/共59页第十五页，共60页。 （1 1）格式复制）格式复制 方法一：方法一： 单击单击“编辑编辑(binj)”(binj)”菜单中的菜单中的“复制复制”命令。选择目标区域，单击命令。选择目标区域，单击“编辑编辑(binj)”(binj)”菜单中的菜单中的

8、“选择性粘贴选择性粘贴”命令，在打开的选择性粘贴对话框中选命令，在打开的选择性粘贴对话框中选中中“格式格式”按钮。按钮。 方法二：选方法二：选“格式刷格式刷”按钮。按钮。 （2 2）格式删除）格式删除 选择要删除格式的区域。选择选择要删除格式的区域。选择“编辑编辑(binj)”(binj)”菜单中的菜单中的“清除清除”命令，在命令，在弹出的子菜单中选择弹出的子菜单中选择“格式格式”子命令。子命令。 工作表格式工作表格式(g shi)复制复制/删除删除第16页/共59页第十六页，共60页。largest economyis the2rd 函函 数数 第17页/共59页第十七页，共60页。Exce

9、lExcel中的公式中的公式(gngsh)(gngsh)及函及函数数公式是公式是ExcelExcel中数值关系的具体体现，构成了中数值关系的具体体现，构成了ExcelExcel的核心，正确地使用任何一个公式必须明的核心，正确地使用任何一个公式必须明确地把握下面三个因素：确地把握下面三个因素：1. 1. 公式中的参数：公式中需要哪些参数，自公式中的参数：公式中需要哪些参数，自变量来源于何外，公式如何引用其他区域或单变量来源于何外，公式如何引用其他区域或单元格元格2. 2. 公式的运算：公式的运算实质性机理是什公式的运算：公式的运算实质性机理是什么，有什么约束条件么，有什么约束条件3. 3. 公式

10、运算的结果表现公式运算的结果表现(bioxin)(bioxin)：公式：公式运算的结果表现运算的结果表现(bioxin)(bioxin)为什么，其格式为什么，其格式如何如何第18页/共59页第十八页，共60页。ExcelExcel中公式中公式(gngsh)(gngsh)的输入的输入ExcelExcel中的公式通常都是以中的公式通常都是以“=”“=”开头的一开头的一个运算式或函数式，可以将个运算式或函数式，可以将“=”“=”引导符看引导符看成是成是ExcelExcel公式的标志。公式的标志。例如：例如：G5G5单元单元(dnyun)(dnyun)中有如下内中有如下内容容=G1+G3+G4=G1+

11、G3+G4* *2 2又如：又如：N9N9单元单元(dnyun)(dnyun)中有如下内中有如下内容容=SUM=SUM（A9:M9A9:M9） 这些都是这些都是ExcelExcel中最简单的公式示例中最简单的公式示例第19页/共59页第十九页，共60页。ExcelExcel公式公式(gngsh)(gngsh)中的参数中的参数ExcelExcel中公式中的参数有下列三种情况：中公式中的参数有下列三种情况：1. 1. 常数：公式中使用某些常数来参与运常数：公式中使用某些常数来参与运算，或由常数指明公式的运算方式算，或由常数指明公式的运算方式2. 2. 系统变量：公式的某些变量来源于计系统变量：公式

12、的某些变量来源于计算机系统的自身参数，如机器时间等算机系统的自身参数，如机器时间等3. 3. 地址引用：对其他工作溥、工作表、地址引用：对其他工作溥、工作表、区域或单元格的内容的引用，这种引用在区域或单元格的内容的引用，这种引用在是根据被引用对象的位置进行是根据被引用对象的位置进行(jnxng)(jnxng)的，所以的，所以ExcelExcel中又称之为地址引用中又称之为地址引用第20页/共59页第二十页，共60页。ExcelExcel公式中对地址公式中对地址(dzh)(dzh)的引用的引用方式方式在在ExcelExcel中公式对其他地址的引用分为三种情况：中公式对其他地址的引用分为三种情况：

13、1. 1. 相对引用：即对另一单元的地址引用是按与当前相对引用：即对另一单元的地址引用是按与当前单元的特定距来确定的，这种引用在该公式被复制时单元的特定距来确定的，这种引用在该公式被复制时会自动调整会自动调整2. 2. 绝对引用：即对被引用单元采用绝对的行列号来绝对引用：即对被引用单元采用绝对的行列号来引用，公式不会自动调整引用，公式不会自动调整3. 3. 混合引用：行或列之一采用绝对引用混合引用：行或列之一采用绝对引用如果如果(rgu)(rgu)有区域或单元格命名存在，还可以按有区域或单元格命名存在，还可以按名引用名引用第21页/共59页第二十一页，共60页。ExcelExcel公式相对与绝

14、对公式相对与绝对(judu)(judu)地址操作地址操作在在ExcelExcel中输入公式中输入公式(gngsh)(gngsh)时，相对地址与时，相对地址与绝对地址可以进行转换操作：绝对地址可以进行转换操作： 凡行或列地址前出现有凡行或列地址前出现有“$”“$”标志的，该行或标志的，该行或列的地址即为绝对地址引用，否则为相对地址引用列的地址即为绝对地址引用，否则为相对地址引用 在地址输入时，相对地址和绝对地址间可以在地址输入时，相对地址和绝对地址间可以用用F4F4键进行循环转换键进行循环转换G3F4$G$3F4G$3F4$G3第22页/共59页第二十二页，共60页。ExcelExcel公式中对

15、地址的操作公式中对地址的操作(cozu)(cozu)方式方式在在ExcelExcel中输入公式需要进行地址引用时中输入公式需要进行地址引用时可以采用的操作方式为：可以采用的操作方式为：1. 1. 直接输入式：直接输入被引用对象的直接输入式：直接输入被引用对象的行列地址行列地址2. 2. 交互指定式：即用鼠标或光标键直接交互指定式：即用鼠标或光标键直接交互式指出被引用对象的地址交互式指出被引用对象的地址3. 3. 名称粘贴式：如果名称粘贴式：如果(rgu)(rgu)引用的引用的对象已有命名，可以采用对象已有命名，可以采用F3F3键来粘贴该对键来粘贴该对象的名称于公式之中象的名称于公式之中第23页

16、/共59页第二十三页，共60页。MS ExcelMS Excel公式公式(gngsh)(gngsh)的自动调整的自动调整在在ExcelExcel中公式的相对此用部分会，在该公式自中公式的相对此用部分会，在该公式自身的位置发生变化，或在其引用身的位置发生变化，或在其引用(ynyng)(ynyng)对对象的位置发生变化时，会自动进行相应的调整，象的位置发生变化时，会自动进行相应的调整，但应注意一些细节：但应注意一些细节： 当公式被复制到它处时，会自动调整当公式被复制到它处时，会自动调整 当公式被移动到它处时，不会自动调当公式被移动到它处时，不会自动调整整 如被引用如被引用(ynyng)(ynyng

17、)对象为一区域，对象为一区域，则在区域中插入或删除行列时，就注意引用则在区域中插入或删除行列时，就注意引用(ynyng)(ynyng)这一对象的公式调整情况这一对象的公式调整情况第24页/共59页第二十四页，共60页。ExcelExcel公式公式(gngsh)(gngsh)中中的函数的函数函数是函数是ExcelExcel中完成复杂运行的一种程序片断，它通中完成复杂运行的一种程序片断，它通常是常是ExcelExcel自身内含的，必须时也可以由用户通过自身内含的，必须时也可以由用户通过VBAVBA扩充。扩充。由于函数是由于函数是ExcelExcel复杂运行的核心，而电子表格又是复杂运行的核心，而电

18、子表格又是以运算作为主要功能的，所以一个电子表格软件，其以运算作为主要功能的，所以一个电子表格软件，其内含的函数数量基本上可以直接表征内含的函数数量基本上可以直接表征(bio zhn(bio zhn) )这个软件的功能广度和深度。这个软件的功能广度和深度。ExcelExcel内部蕴含有丰富内部蕴含有丰富的积各类函数。的积各类函数。第25页/共59页第二十五页，共60页。1使用使用(shyng)公式公式 公式中的运算符有以下公式中的运算符有以下(yxi)(yxi)四类：四类： （1 1）算术运算符）算术运算符 （2 2）比较运算符）比较运算符 （3 3）文本运算符（）文本运算符（& &） （4

19、4）引用运算符（）引用运算符（: :） 函函 数数第26页/共59页第二十六页，共60页。输入输入(shr)(shr)公式公式第27页/共59页第二十七页，共60页。拖拽鼠标复制拖拽鼠标复制(fzh)(fzh)公式公式第28页/共59页第二十八页，共60页。公式公式(gngsh)(gngsh)和函数和函数/ /使用函使用函数数ExcelExcel有丰富的内部函数，其类型包括： 财务函数、日期与时间函数、数学与三角函数、统计函数、查找与引用(ynyng)(ynyng)函数、数据库函数、文本函数、逻辑函数、信息函数（1 1）函数的输入（2 2）自动求和（3 3）自动计算（4 4）函数复制 第29页

20、/共59页第二十九页，共60页。ExcelExcel常用常用(chn(chn yn yn) )函数简介函数简介函数名功能说明基本语法SUM求和SUM(数1,数2，)AVERAGE求均值AVERAGE(数1,数2，)IF条件操作IF(条件表达式,为真时取值,为假时取值)COUNT数值单元计数COUNT(数1,数2，)MAX求最大值MAX(数1,数2，)SIN正弦函数SIN(数)SUMIF条件求和SUM(比较区域，条件表达式，求和区域）PMT分期偿还额PMT(利率，总额，现值，未来值，期初/期末)STDEV样本标准差STDEV(数1,数2，)第30页/共59页第三十页，共60页。 （1）函数的输入

21、 函数的输入有两种方法(fngf)：一为直接输入，一为粘贴函数法（即使用函数向导）。 图图4-16 粘贴粘贴(zhnti)函数对话框函数对话框输入输入(shr)公式公式第31页/共59页第三十一页，共60页。自动自动(zdng)求和求和自动自动(zdng)求和求和第32页/共59页第三十二页，共60页。选择选择(xunz)的的区域区域自动自动(zdng)计算计算结果显示区结果显示区第33页/共59页第三十三页，共60页。 1 1相对引用相对引用 2 2绝对引用绝对引用 3 3三维地址三维地址(dzh)(dzh)引用引用 5 5VlookupVlookup的使用的使用单元格引用单元格引用(yny

22、ng)第34页/共59页第三十四页，共60页。largest economy3rd 分析分析(fnx) 第35页/共59页第三十五页，共60页。筛选筛选(shixun)数据表数据表 1 1自动自动(zdng)(zdng)筛选筛选 2 2自定义筛选自定义筛选 3 3高级筛选高级筛选第36页/共59页第三十六页，共60页。下拉箭头按扭的选择下拉箭头按扭的选择(xunz)第37页/共59页第三十七页，共60页。自动筛选之后自动筛选之后(zhhu)的清单的清单第38页/共59页第三十八页，共60页。多条件筛选多条件筛选(shixun)的筛选的筛选(shixun)结果结果第39页/共59页第三十九页，共

23、60页。图图4-81 设置筛选设置筛选(shixun)条件条件 筛选筛选(shixun)结果结果第40页/共59页第四十页，共60页。2自定义筛选自定义筛选(shixun)“自定义自动自定义自动(zdng)筛选方式筛选方式”对话框对话框第41页/共59页第四十一页，共60页。筛选筛选(shixun)(shixun)结果结果第42页/共59页第四十二页，共60页。高级高级(goj)筛选筛选 如果用户要使用高级筛选，一定要先建立一个条件区域。条件区域用来指定如果用户要使用高级筛选，一定要先建立一个条件区域。条件区域用来指定筛选的数据必须筛选的数据必须(bx)(bx)满足的条件。满足的条件。 空二行

24、空二行条件条件(tiojin)区域区域图图4-85 条件区域条件区域第43页/共59页第四十三页，共60页。高级高级(goj)(goj)筛选对话框筛选对话框第44页/共59页第四十四页，共60页。筛选筛选(shixun)结果显示数据表格结果显示数据表格第45页/共59页第四十五页，共60页。复制复制(fzh)后的筛选结果后的筛选结果复制复制(fzh)的筛选结的筛选结果果第46页/共59页第四十六页，共60页。数据数据(shj)排序排序 1 1默认排序默认排序(pi x)(pi x) 2 2按一列排序按一列排序(pi x)(pi x) 3 3按多列排序按多列排序(pi x)(pi x)第47页/

25、共59页第四十七页，共60页。默认默认(mrn)排序排序 ExcelExcel在默认排序时是根据单元格中的数据进行在默认排序时是根据单元格中的数据进行(jnxng)(jnxng)排序的。在按升序排序时，排序的。在按升序排序时，ExcelExcel使使用如下顺序：用如下顺序： （1 1）数值从最小的负数到最大的正数排序。）数值从最小的负数到最大的正数排序。 （2 2）文本和数字的文本按从）文本和数字的文本按从0909，azaz，AZAZ的顺的顺序排列。序排列。 （3 3）逻辑值）逻辑值False False 排在排在True True 之前。之前。 （4 4）所有错误值的优先级相同。）所有错误值

26、的优先级相同。 （5 5）空格排在最后。）空格排在最后。第48页/共59页第四十八页，共60页。按一列按一列(y li)(y li)排序排序设置设置(shzh)排序方排序方式式 主关键字排列(pili)顺序第49页/共59页第四十九页，共60页。 按按“笔试笔试”字段降序排列字段降序排列(pili)(pili)之后的结果之后的结果 第50页/共59页第五十页，共60页。以机试降序排列以机试降序排列(pili)(pili)的结果的结果第51页/共59页第五十一页，共60页。3按多列排序按多列排序(pi x)设置设置(shzh)多列排序对话框多列排序对话框以笔试为主要关键字，机试为以笔试为主要关键字，机试为次要关键字的排序次要关键字的排序(pi x)结果结果第52页/共59页第五十二页，共60页。分类分类(fn li)汇总汇总 1 1简单的分类汇总简单的分类汇总(huzng)(huzng) 2 2创建多级分类汇总创建多级分类汇总(huzng) (huzng) 3 3分级显示数据分级显示数据 4 4清除分类汇总清除分类汇总(huzng)(huzng)第53页/共59页第五十三页，共60页。1简单简单(jindn)的分类汇总的分类汇总分类汇总分类