EXCLE使用技巧讲解

1、九洲集团（香港）控股有限公司久和新科技（深圳）有限公司EXCEL简单技巧的使用编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司目录OFFICE的定义学习EXCEL意义及目的EXCEL目录的应用EXCEL常用函数EXCEL其他技巧总结编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司官方定义：MicrosoftOffice是微软公司开发的一套基于Windows操作系统的办公软件套装。常用组件有Word、Excel、Powerpoint等。民间定义：方便办公的应用软件，性质相当于铁锹。能替代纸质化办公、提高数据处理能力、提高视觉效果。俗话说：工欲善其事，必先利其器。熟练的

2、掌握常用的技巧，能提高工作效率。本次学习目的：1、掌握常用的方法和技巧；2、掌握学习不常用的方法和技巧的学习方法；OFFICE的定义学习意义及目的编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司目录的应用目录的制作就是使用EXCEL内部的超链接功能。快捷键：Ctrl + k。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司目录的应用常用的目录使用基本是两种：第一种是EXCEL的外部链接；第二种是EXCEL的内部链接。1、在当前文件夹内新建一个文本类型的文件（所谓的.txt文件）。2、打开文件输入“DIR /S /B 1.TXT”； DIR是DOS命令符意思为生产目录

3、； /S /B 是生成该文件夹内所有文件及子文件。如果只用/B只生成该文件夹下的目录，不生成子文件； 1.txt是生成的目录输出在名为1的文本中。3、保存该文件，把文件后缀名从.txt改为.bat。双击运行，这时系统就会新建一个名字为1的文本文件。4、打开文本文件如左图目录已生成；编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司目录的应用1、把txt文档内的数据全部复制到新建的Excel表格内，发现内容在A列内，需要把目录的内容分开输出到各个列，方便管理；2、选择A列，点击数据的选项卡，选择分列；3、点击出现选项卡的下一步，在第二页中分隔符号选择其他，在里面输入“”后点击下一步。

4、点击完成。就出现最有侧的效果了。4、把不需要的存储文件名删除。例如：A列、B列中的数据可以进行删除；5、优化格式。EXCEL的外部链接编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司目录的应用按左图的编号顺序进行建立超链接。1、选择想建立超链接的内容；2、Ctrl + k打开超链接编辑器；3、选择“现有文件或网页” 的选项卡；4、选择“当前文件夹（最近使用过的文件夹）” 选项卡；5、选择需要建立链接的文件夹；6、选择需要建立链接的文件；7、确定；8、测试。建立目录的时候最好把目录名称与链接的名称弄成一致。EXCEL的外部链接编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有

5、限公司EXCEL的内部链接目录的应用内部链接适用与一个表格内有多个工作薄（比如说有几十个，用鼠标选择工作量大还容易出错）。按左图的编号顺序进行建立超链接。1、选择想建立超链接的内容；2、Ctrl + k打开超链接编辑器；3、选择“本文当中的位置” 的选项卡；4、选择需要建立链接的文件；5、确定；6、测试。外部链接与内部链接如果需要批量实现链接需要使用Excel的宏功能，用VBA编程实现。虽然碰上目录内容或工作簿比较多的表格此方法依然笨重，但一次建立好后可以一劳永逸。尤其经常用的表格不需要每次在找文件所在位置或工作薄的位置。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司EXCEL

6、常用函数巧SUM及其相关应用数组的应用COUNT及其相关的应用ROUND的应用判断语句（IF语句）VLOOKUP的应用编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司SUM及其相关应用SUM是Excel最常用的函数，没有之一。基本用过Excel的人可能别的不会用，但这个公式一定会用。SUM公式与E15+F15+O15不同的是，SUM公式的意义是把其括号内的参数进行求和，包括数组。应用最直接的方式就是把想进行求和的内容放到括号里，像上图想计算E15至O15连续的和用“：”连接两个参数就可以，如果想跳着计算用SUM(E15，G15，I15，O15)用“，”隔开。编辑课件九洲集团（香港

7、）控股有限公司久和新科技（深圳）有限公司SUM及其相关应用关于SUMIF与SUMIFS的应用1、SUMIF与SUMIFS都是求和函数，与SUM的区别是参数新增了判断语句。表示如果*符合*那么求和2、SUMIF（ range，criteria，sum_range ）： SUMIF（条件区域，求和条件，实际求和区域），第二个求和条件参数在第一个条件区域里。3、SUMIFS（ sum_range， range1，criteria1 ， range10，criteria10）： SUMIFS（实际求和区域，条件区域1，求和条件1，条件区域10，求和条件10 ），求和条件参数在第一个条件区域里。 4、注

8、意SUMIF与SUMIFS参数顺序正好相反。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司SUM及其相关应用关于SUMIF与SUMIFS的应用注意事项：1、 criteria求和条件参数在第一个range条件区域里；2、参数criteria如果是文本，要加引号。且引号为英文状态下输入。3、参数criteria还可以使用通配符*。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司关于SUMPRODUCT的应用SUM及其相关应用SUMPRODUCT实际上是乘积求和运算，SUMPRODUCT（array1,array2,array3, .）Array为数组。当sumpro

9、duct函数中的参数只有一个数组时，即对数组1;2;3;4;5;6;7进行求和，1+2+3+4+5+6+7=28。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司关于SUMPRODUCT的应用SUM及其相关应用当sumproduct函数中参数为两个数组时，两个数组的所有元素对应相乘。公式=sumproduct（A2:A8,B2:B8）可转化为=sumproduct（数组1，数组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。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有

10、限公司关于SUMPRODUCT的应用SUM及其相关应用当sumproduct函数中参数为三个数组时，三个数组的所有元素对应相乘。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司关于SUMPRODUCT的应用SUM及其相关应用单条件求和统计九洲集团后的数字。=sumproduct(A:A=“九洲集团)*1，(B:B)，这种写法好像与之前看的函数参数不一样，之前的参数传的全是数字，这个里面穿的是文本。其实A:A=“九洲集团”是一个判断语句，A列中的所有数据如果是对的则结果为TRUE,如果是错的结果为FALSE。在计算机语言中TRUE的结果为1，FALSE的结果为0。我们这个表格中如果

11、用数组表示则可以表示为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列的数组后想加。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司关于

12、SUMPRODUCT的应用SUM及其相关应用多条件求和一=sumproduct(A:A=“九洲集团”)*1，(C:C=“残友”)*1，(B:B)。其实了解单条件之后，多条件的也就是在加多个数组。注意：多条件求和这种写法是需要满足所有条件才能求和，如果多条件中有一个条件不满足，那么该条件对应的数据就不会作为计算值。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司关于SUMPRODUCT的应用SUM及其相关应用多条件求和二=sumproduct(A:A=“九洲集团”)+(C:C=“残友”)，(B:B)。这种写法是先把(A:A=“九洲集团”)的数组与(C:C=“残友”)数组想加，也就是

13、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,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的应用SUM及其

14、相关应用排名：=SUMPRODUCT(D2$D$2:$D$8)*1)+1数组公式D2$D$2:$D$8这里是由TRUE和FALSE组成的逻辑数组FALSE；TRUE；TRUE；FALSE；TRUE；FALSE；TRUE转化为1和0的数组0；1；1；0；1；0；1。这里利用的是该成绩和所以成绩进行比较，通过测算高于该成绩的成绩，每次累加1。效果等同于RANK(criteria,VALUE1)函数注意：因为SUMPRODUCT内部参数是数组，所以要求行与列的维度一样。数组的长度要求相同。排名函数RANK:= RANK(criteria,VALUE1)criteria表示需要排名的条件，VALUE1

15、表示排名的值。其中排名区域用$符号表示锁定，下拉公式时公式中的参数不会因单元格的改变而改变，快捷键是F4。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司数组的应用数组的表示数组的民间定义：一组数的集合。EXCEL中基本数组的表示方式一行列的数组用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是一个

16、数组，所以公式输入完成后用Ctrl + Shift + Enter确认，表示为数组求和。如果不用Ctrl + Shift + Enter组合键直接用Enter那么结果为左侧的结果，只输出数组中的第一个数，也就是1。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司COUNT及其相关的应用关于COUNT、COUNTA和COUNTBLANK的应用COUNT是计数的函数=COUNT(VALUE1，VALUE2,)COUNT表示计算区域中包含数字的单元格的个数。VALUE1参数就要统计的范围，VALUE2参数与VALUE1相同，可有可无。如有需求COUNT参数可以是多个。COUNTA表示计

17、算区域中非空单元格的个数，用法与COUNT相同。COUNTBLANK表示计算某个区域中空单元格的个数，用法与COUNT相同。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司数组的应用数组应用的实例正常计算公式为=12000*(1+0.05)0+12000*(1+0.05)1+12000*(1+0.05)19EXCEL上不用数组表示出每年的房租，再进行求和。用数组就不需要列出每一列数，只需要把该数组表示出来用组合键确认就可以。该公式的设计是要找出数组的规律，从左侧的正常计算公式可以看出数组的规律是(1+0.05)0,1,2,19。但ROW系数不能为0，那么可以表示为1+(1+0.0

18、5) 1,2,19。Excel表示N次方的函数为POWER(需计算的参数，N次方)。所以用POWER(1.05,ROW(1：19)*12000表示该数组，再用SUM函数进行求和，最后再加上第一年的房租。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司COUNT及其相关的应用关于COUNTIF与COUNTIFS的应用countif（range，criteria）参数：range 要计算其中非空单元格数目的区域参数：criteria 以数字、表达式或文本形式定义的条件countifs(range1,criteria1, range2,criteria2,)参数：range 要计算其中

19、非空单元格数目的区域参数：criteria 以数字、表达式或文本形式定义的条件。countifs后面的括号可以加多个条件，但每个条件都需要有两个参数，一个单元格选取，另外一个就是判断条件。表示同时满足的个数。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司ROUND的应用Round(number,num_digits)的参数：number是需要进行四舍五入的数值。num_digits是需要进行四舍五入的位数。number可以是其他函数。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司判断语句（IF语句）IF函数有三个参数，语法如下：=IF(条件判断, 结果为真返回值

20、, 结果为假返回值)编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司判断语句（IF语句）上表为IF语句的实例应用，其中结合了count，round两个函数。该例子的设计思路就是，如果排名小于总共人数的个数*等级分配的比例则为A等等。属于4重IF嵌套。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司VLOOKUP的应用VLOOKUP查找与引用函数：=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)表述就是VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）。从左图可以看出表二中

21、没有袁总与俞总的信息，那么表一匹配不到就显示#N/A。查找列数从引用的第一列开始计算。如左图中想查找籍贯，那么从姓名开始算为1，籍贯列就为2。那么index_num参数的值就为2。 range_lookup表示查找精度，我们一般都输入0，表示精确查找凡是有#N/A显示的第一说明需要引用的表中没有这个人，第二可能是因为名字有误（包括名字输入差异，格式差异等）。与VLOOKUP相关的函数还有HLOOKUP与LOOKUP，用法类似。VLOOKUP是查找列，HLOOKUP是查找行。编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司函数应用的总结1、所谓复杂的公式就是多个函数的反复调用；2、没有必要花大量的时间研究函数，把上述函数的使用方法和参数弄透记住基本可以满足现阶段的所用的应用。3、遇到无法实现的功能直接百度，有了上述函数的基础，不论遇到什么函数都可以快速的套用编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司EXCEL其他技巧查找相同项，为相同数据标注颜色编辑课件九洲集团（香港）控股有限公司久和新科技（深圳）有限公司EXCEL其他技巧到日期提前三天提醒，用AND(TO