EXCLE使用技巧讲解ppt课件

1、EXCELEXCEL简单技巧的使用简单技巧的使用;目录OFFICE的定义学习EXCEL意义及目的EXCEL目录的应用EXCEL常用函数EXCEL其他技巧总结;官方定义：Microsoft Office是微软公司开发的一套基于 Windows 操作系统的办公软件套装。常用组件有 Word、Excel、Powerpoint等。民间定义：方便办公的应用软件，性质相当于铁锹。能替代纸质化办公、提高数据处理能力、提高视觉效果。俗话说：工欲善其事，必先利其器。熟练的掌握常用的技巧，能提高工作效率。本次学习目的：1、掌握常用的方法和技巧；2、掌握学习不常用的方法和技巧的学习方法；;目录的制作就是使用EXCE

2、L内部的超链接功能。快捷键：Ctrl + k。;常用的目录使用基本是两种：第一种是EXCEL的外部链接；第二种是EXCEL的内部链接。1、在当前文件夹内新建一个文本类型的文件所谓的.txt文件）。2、打开文件输入“DIR /S /B 1.TXT”； DIR是DOS命令符意思为生产目录； /S /B 是生成该文件夹内所有文件及子文件。如果只用/B只生成该文件夹下的目录，不生成子文件； 1.txt是生成的目录输出在名为1的文本中。3、保存该文件，把文件后缀名从.txt改为.bat。双击运行，这时系统就会新建一个名字为1的文本文件。4、打开文本文件如左图目录已生成；;1、把txt文档内的数据全部复制

3、到新建的Excel表格内，发现内容在A列内，需要把目录的内容分开输出到各个列，方便管理；2、选择A列，点击数据的选项卡，选择分列；3、点击出现选项卡的下一步，在第二页中分隔符号选择其他，在里面输入“”后点击下一步。点击完成。就出现最有侧的效果了。4、把不需要的存储文件名删除。例如：A列、B列中的数据可以进行删除；5、优化格式。EXCEL的外部链接;按左图的编号顺序进行建立超链接。1、选择想建立超链接的内容；2、Ctrl + k打开超链接编辑器；3、选择“现有文件或网页” 的选项卡；4、选择“当前文件夹最近使用过的文件夹）” 选项卡；5、选择需要建立链接的文件夹；6、选择需要建立链接的文件；7、

4、确定；8、测试。建立目录的时候最好把目录名称与链接的名称弄成一致。EXCEL的外部链接;EXCEL的内部链接内部链接适用与一个表格内有多个工作薄比如说有几十个，用鼠标选择工作量大还容易出错）。按左图的编号顺序进行建立超链接。1、选择想建立超链接的内容；2、Ctrl + k打开超链接编辑器；3、选择“本文当中的位置” 的选项卡；4、选择需要建立链接的文件；5、确定；6、测试。外部链接与内部链接如果需要批量实现链接需要使用Excel的宏功能，用VBA编程实现。虽然碰上目录内容或工作簿比较多的表格此方法依然笨重，但一次建立好后可以一劳永逸。尤其经常用的表格不需要每次在找文件所在位置或工作薄的位置。;

5、EXCEL常用函数巧SUM及其相关应用数组的应用COUNT及其相关的应用ROUND的应用判断语句（IF语句）VLOOKUP的应用;SUM是Excel最常用的函数，没有之一。基本用过Excel的人可能别的不会用，但这个公式一定会用。SUM公式与E15+F15+O15不同的是，SUM公式的意义是把其括号内的参数进行求和，包括数组。应用最直接的方式就是把想进行求和的内容放到括号里，像上图想计算E15至O15连续的和用“：”连接两个参数就可以，如果想跳着计算用SUM(E15，G15，I15，O15)用“，”隔开。;关于SUMIF与SUMIFS的应用1、SUMIF与SUMIFS都是求和函数，与SUM的区

6、别是参数新增了判断语句。表示如果*符合*那么求和2、SUMIF（ range，criteria，sum_range ）： SUMIF条件区域，求和条件，实际求和区域），第二个求和条件参数在第一个条件区域里。3、SUMIFS（ sum_range， range1，criteria1 ， range10，criteria10）： SUMIFS实际求和区域，条件区域1，求和条件1，条件区域10，求和条件10 ），求和条件参数在第一个条件区域里。 4、注意SUMIF与SUMIFS参数顺序正好相反。;关于SUMIF与SUMIFS的应用本卷须知：1、 criteria求和条件参数在第一个range条件区域

7、里；2、参数criteria如果是文本，要加引号。且引号为英文状态下输入。3、参数criteria还可以使用通配符*。;关于SUMPRODUCT的应用SUMPRODUCT实际上是乘积求和运算，SUMPRODUCTarray1,array2,array3, .）Array为数组。当sumproduct函数中的参数只有一个数组时，即对数组1;2;3;4;5;6;7进行求和，1+2+3+4+5+6+7=28。;关于SUMPRODUCT的应用当sumproduct函数中参数为两个数组时，两个数组的所有元素对应相乘。公式=sumproductA2:A8,B2:B8可转化为=sumproduct数组1，数

8、组2）=sumproduct（1；2；3；4；5；6；7，1；2；3；4；5；6；7）=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。;关于SUMPRODUCT的应用当sumproduct函数中参数为三个数组时，三个数组的所有元素对应相乘。;关于SUMPRODUCT的应用单条件求和统计九洲集团后的数字。=sumproduct(A:A=“九洲集团)*1，(B:B)，这种写法好像与之前看的函数参数不一样，之前的参数传的全是数字，这个里面穿的是文本。其实A:A=“九洲集团是一个判断语句，A列中的所有数据如果是对的则结果为TRUE,如果是错的结果为FALSE。在计算机语言中TRUE的

9、结果为1，FALSE的结果为0。我们这个表格中如果用数组表示则可以表示为TURE,FALSE, FALSE, FALSE, FALSE, FALSE, TURE,FALSE, FALSE, FALSE, FALSE, FALSE, TURE,FALSE, FALSE, FALSE, FALSE, FALSE , TURE,FALSE, FALSE, FALSE, FALSE, FALSE*1之后转化为1,0,0,0,0,0, 1,0,0,0,0,0, 1,0,0,0,0,0, 1,0,0,0,0,0,。这是就跟上页所说的数组一样了。最终也转换为用B列的数组乘以A列的数组后想加。;关于SUMPR

10、ODUCT的应用多条件求和一=sumproduct(A:A=“九洲集团”)*1，(C:C=“残友”)*1，(B:B)。其实了解单条件之后，多条件的也就是在加多个数组。注意：多条件求和这种写法是需要满足所有条件才能求和，如果多条件中有一个条件不满足，那么该条件对应的数据就不会作为计算值。;关于SUMPRODUCT的应用多条件求和二=sumproduct(A:A=“九洲集团”)+(C:C=“残友”)，(B:B)。这种写法是先把(A:A=“九洲集团”)的数组与(C:C=“残友”)数组想加，也就是1,0,0,0,0,0, 1,0,0,0,0,0, 1,0,0,0,0,0, 1,0,0,0,0,0,+1

11、,0,0,0,1,0,1,0,0,0,1,0,0,0,1,0,1,0,0,0,1,0,0,0=1,0,0,0,1,0, 1,0,0,0,1,0, 1,0,1,0,1,0, 1,0,1,0,0,0。之后在与(D:D)的数组相乘。多条件求和一中的意思就是满足(A:A=“九洲集团”)并且满足 (C:C=“残友”)。多条件求和二中的意思就是满足(A:A=“九洲集团”)或者满足(C:C=“残友”)。;关于SUMPRODUCT的应用排名：=SUMPRODUCT(D2$D$2:$D$8)*1)+1数组公式D2$D$2:$D$8这里是由TRUE和FALSE组成的逻辑数组FALSE；TRUE；TRUE；FALS

12、E；TRUE；FALSE；TRUE转化为1和0的数组0；1；1；0；1；0；1。这里利用的是该成绩和所以成绩进行比较，通过测算高于该成绩的成绩，每次累加1。效果等同于RANK(criteria,VALUE1)函数注意：因为SUMPRODUCT内部参数是数组，所以要求行与列的维度一样。数组的长度要求相同。排名函数RANK:= RANK(criteria,VALUE1)criteria表示需要排名的条件，VALUE1表示排名的值。其中排名区域用$符号表示锁定，下拉公式时公式中的参数不会因单元格的改变而改变，快捷键是F4。;数组的表示数组的民间定义：一组数的集合。EXCEL中基本数组的表示方式一行列

13、的数组用1,2,3,4,5,6,7表示；表示方式二竖列数组用1;2;3;4;6;7表示1,2,3,99,100行列长串数组可以用函数COLUMN(1：100)表示；1;2;3;99;100竖列长串数组可以用函数ROW(1：100)表示。1到100的所有整数求和公式为：=SUM(ROW(1：100)因为是把1到100的所有数相加，1到100是一个数组，所以公式输入完成后用Ctrl + Shift + Enter确认，表示为数组求和。如果不用Ctrl + Shift + Enter组合键直接用Enter那么结果为左侧的结果，只输出数组中的第一个数，也就是1。;关于COUNT、COUNTA和COUN

14、TBLANK的应用COUNT是计数的函数=COUNT(VALUE1，VALUE2,)COUNT表示计算区域中包含数字的单元格的个数。VALUE1参数就要统计的范围，VALUE2参数与VALUE1相同，可有可无。如有需求COUNT参数可以是多个。COUNTA表示计算区域中非空单元格的个数，用法与COUNT相同。COUNTBLANK表示计算某个区域中空单元格的个数，用法与COUNT相同。;数组应用的实例正常计算公式为=12000*(1+0.05)0+12000*(1+0.05)1+12000*(1+0.05)19EXCEL上不用数组表示出每年的房租，再进行求和。用数组就不需要列出每一列数，只需要把

15、该数组表示出来用组合键确认就可以。该公式的设计是要找出数组的规律，从左侧的正常计算公式可以看出数组的规律是(1+0.05)0,1,2,19。但ROW系数不能为0，那么可以表示为1+(1+0.05) 1,2,19。Excel表示N次方的函数为POWER(需计算的参数，N次方)。所以用POWER(1.05,ROW(1：19)*12000表示该数组，再用SUM函数进行求和，最后再加上第一年的房租。;关于COUNTIF与COUNTIFS的应用countifrange，criteria）参数：range 要计算其中非空单元格数目的区域参数：criteria 以数字、表达式或文本形式定义的条件counti

16、fs(range1,criteria1, range2,criteria2,)参数：range 要计算其中非空单元格数目的区域参数：criteria 以数字、表达式或文本形式定义的条件。countifs后面的括号可以加多个条件，但每个条件都需要有两个参数，一个单元格选取，另外一个就是判断条件。表示同时满足的个数。;Round(number,num_digits)的参数：number是需要进行四舍五入的数值。num_digits是需要进行四舍五入的位数。number可以是其他函数。;IF函数有三个参数，语法如下：=IF(条件判断, 结果为真返回值, 结果为假返回值);上表为IF语句的实例应用，其

17、中结合了count，round两个函数。该例子的设计思路就是，如果排名小于总共人数的个数*等级分配的比例则为A等等。属于4重IF嵌套。;VLOOKUP查找与引用函数：=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)表述就是VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）。从左图可以看出表二中没有袁总与俞总的信息，那么表一匹配不到就显示#N/A。查找列数从引用的第一列开始计算。如左图中想查找籍贯，那么从姓名开始算为1，籍贯列就为2。那么index_num参数的值就为2。 range_lookup表示

18、查找精度，我们一般都输入0，表示精确查找凡是有#N/A显示的第一说明需要引用的表中没有这个人，第二可能是因为名字有误包括名字输入差异，格式差异等）。与VLOOKUP相关的函数还有HLOOKUP与LOOKUP，用法类似。VLOOKUP是查找列，HLOOKUP是查找行。;1、所谓复杂的公式就是多个函数的反复调用；2、没有必要花大量的时间研究函数，把上述函数的使用方法和参数弄透记住基本可以满足现阶段的所用的应用。3、遇到无法实现的功能直接百度，有了上述函数的基础，不论遇到什么函数都可以快速的套用;查找相同项，为相同数据标注颜色;到日期提前三天提醒，用AND(TODAY()=D5+3),(TODAY=D5)实现，D5表示实际日期，实际日期提前3天就是加3