excel使用技巧PPT课件

Office-excel软件应用,姓名：李东明电话培训目标,2,Office-excel软件应用,Excel使用技巧,Excel函数运用,数据透析表及图表,4,单元格自定义类型,在“开始”菜单栏下的“数字”区域，有设定好的一些格式，比如百分数，时间格式，分数啊，会计格式啊等等，需要使用的话，直接单击就可以了另外，在看看“自定义”设置，这就需要讲下通配符：一般我们常用到的通配符有“0”和“#”，它代表的就是一个字符。注意哦：通配符要在英文半角情况下输入,5,快速给数字加上单位,有时我们需要给输入的数值加上单位（如“立方米”等），少量的我们可以直接输入而大量的如果一个一个地输入就显得太慢了。下面的方法来实现单位的自动输入：先将数值输入相应的单元格中（注意:仅限于数值），然后在按住Ctrl键的同时选取需要加同一单位的单元格，单击“开始单元格-格式”命令，打开“单元格格式”对话框（），在“数字”标签中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入#立方米，按下确定键后，单位（立方米）即一次性加到相应数值的后面。,6,数值开头的零如何显示,在输入数值时如果开头是0如何让它显示出来？当我们在输入数值时，如编号如果开头是0的话，往往是显示不出来的。例如：输入009527显示出来是9527,0是显示不出来的。这里只要在前面加上一个单引号“”（英文格式下）就可以显示出来（如身份证号码也是相同办法）,7,在不同的单元格连续输入多个值,我们有时需要在不同单元格内连续输入多个数据，但每次输入一个数据后按回车键，活动单元格均默认下移一个单元格，非常不便。方法:单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定其他单元格按回车键，光标自动停留在第一个选中单元格，再输入数据，敲回车键。光标会自动移动到下一个选中单元格。,8,数据有效性,数据有效性在我们日常操作Excel数据时，可以借助数据有效性的功能来验证我们输入数据的有效性。1、单击功能区“数据”，单击“数据有效性”根据我们实际需要进行设置。如图,动手操作,9,使用数据有效性，限制单元格只能输入18位身份证号码。,文本快速换行,我们在使用Excel制作表格时经常会遇到需要在一个单元格输入一行或几行文字的情况，如果输入一行后敲回车键就会移到下一单元格，而不是换行。简便实用的操作方法可以实现换行:在选定单元格输入第一行内容后，在换行处按“Alt+回车键”，即可输入第二行内容，再按“Alt+回车键”输入第三行以此类推。,11,禁止复制隐藏行或列中的数据,如果你复制了包含隐藏列(或行)的一个数据区域，然后把它粘贴到一个新的工作表，那么Excel把隐藏列也粘贴过来了。要想避免这种情况，可以选取你要复制的数据区域，然后选择“开始-编辑查找和选择-定位”命令(ctrl+G)，单击“定位条件”按钮，出现“定位条件”对话框，选中“可见单元格”选项，再复制和粘贴这个选定区域就会得到你所希望的结果。,12,按笔画排序,在默认情况下，Excel对中文字的排序方式是按照“字母”顺序的，以中文姓名为例，字母顺序即按姓的拼音的首字母在26个英文字母中出现的顺序进行排列，如果同姓，则依次计算名的第二、第三字。图中显示的表格包含了按字母顺序排列的姓名数据。然而，在我们的习惯中，有时常常是按照“笔划”的顺序来排列姓名的。这种排序的规则是：按姓字的划数多少排列，同划数内的姓字按起笔顺序排列（横、竖、撇、捺、折），划数和笔形都相同的字，按字形结构排列，先左右、再上下，最后整体字。如果姓字相同，则依次看名第二、三字，规则同姓字。1、单击数据区域中任意单元格，如A2。2、单击菜单“数据”“排序”，出现“排序”对话框。3、在“排序”对话框中，选择“主要关键字”为“姓名”，排序方式为升序。4、单击“排序”对话框中的“选项”按钮，在出现的“排序选项”对话框中，5、单击方法区域中的“笔划排序”单选按钮，如图所示，单击“确定”按钮6、单击“确定”按钮，关闭“排序”对话框。,13,在工作日历中突显周休日,很多用户喜欢使用Excel制作工作日历，如图所示的是公司人力资源部的工作日历的一部分。如果能把所有的周休日都醒目地标识出来，就能够避免一些时间安排上的问题了。方法如下：1、单击A2并拖动光标到A17以选定区域A2:A17，单击菜单“开始”“条件格式”。在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：=WEEKDAY(A2,2)52、单击“格式”按钮，在“单元格格式”对话框的“字体”选项卡中选择“字形”为“加粗”，颜色为红色，单击“确定”按钮。单击“条件格式”对话框的“确定”按钮，如图所示。,14,多个表统一页眉页脚,如果在一个Excel文件中含有多个工作表，如何将多个工作表一次设置成同样的页眉和页脚？把鼠标移到工作表的名称处（若你没有特别设置的话，Excel自动设置的名称是“sheet1、sheet2、sheet3.”），然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时你的所有操作都是针对全部选中的工作表，不管是设置页眉和页脚还是打印你的工作表。,15,自动醒目的小计,在实际工作，常常需要在表格中使用小计行或小计列来汇总某类数据，如图所示。使用条件格式功能，能够快速地为所有小计行与小计列设置醒目的格式。1、单击A1并拖动光标到H11以选定区域A1:H11，单击菜单“格式”“条件格式”。2、在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：=($A1=小计)+(A$1=小计)03、单击“格式”按钮，在“单元格格式”对话框的“字体”选项卡中选择“字形”为“加粗”，颜色为蓝色，单击“确定”按钮。4、单击“条件格式”对话框的“确定”按钮,16,快速比较不同区域的数值（一）,Excel的多窗口特性能够帮助用户比较不同区域的数据，但是当需要比较的区域较大时，人工比较不但费时而且准确率不高。此时如果利用条件格式功能，则能快速而又准确地完成对比工作。在如下图所示的工作表中，源数据和校验数据分别位于A2B21和D2E21，如果希望标记出与源数据不匹配的校验数据，方法如下：1、单击D2并拖动光标到E21以选定区域D2:E21，单击菜单“格式”“条件格式”。2、在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“单元格数值”项，运算符选择“不等于”，在右边的文本框中输入：=A23、单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮。4、单击“条件格式”对话框的“确定”按钮，关闭对话框。,17,快速比较不同区域的数值（二）,上页中介绍了利用条件格式快速对比不同区域中的数值的一种方法。然而在实际工作中，需要对比的数据可能并不是位置一一对应的。例如，如图所示的表格中，需要把与账号二不匹配的账号一标记出来，但两列账号的排列顺序并不相同，对于这种情况，条件格式的设置要相对复杂一些。1、单击A2并拖动光标到A11以选定区域A2:A11，单击菜单“格式”“条件格式”。2、在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：=OR(EXACT(A2,B$2:B$11)=FALSE3、单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮。4、单击“条件格式”对话框的“确定”按钮。,在上述公式中，EXACT函数用于比较两个文本字符串是否完全相同，如完全相同则返回TRUE，否则返回FALSE。使用以下3个公式，也能够实现同样的功能：=ISNA(MATCH(A2,B$2:B$11,)=ISNA(VLOOKUP(A2,B$2:B$11,1,)=NOT(OR(A2=B$2:B$11)如果使用公式=COUNTIF(B$3:B$12,A3)，会得到错误的结果。因为当字符型数字的长度超过15位时，COUNTIF函数会把从第16位开始的数字都当作0计算。可以按下面的方法来创建公式：=COUNTIF(B$3:B$12,A3average扩展函数：必须通过单击“工具加载宏”菜单命令加载，然后才能像内置函数那样使用。当你发现你某些函数不可用时，请执行这一步骤。如：iseven函数的使用必须执行“工具加载宏分析工具库”方能使用,23,如何插入函数,在菜单栏公式里面插入函数，如下左图所示。在单元格输入“=”输入函数名称插入公式，如下右图所示。,24,函数参数中的单元格引用,根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用（如A2）、绝对引用（$A$2）和混合引用三种类型混合引用有“绝对列和相对行”（如$A3:$E3）；或是“绝对行和相对列”（如A$3:E$3）两种形式。前者不论公式怎么复制,列都不变。后者不论公式怎么复制，行号不变。绝对符号的添加:用F4放在公式中的单元格引用中进行切换几种单元格引用表示法：A2:B7（某一单元格区域）；1:1（第一行）；1:5(1至5行)；F:F（F列）；A：N（A到N列）,25,函数参数中标志,由于Excel工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而B1单元格已经输入了“物理”字样，则求物理平均分的公式可以写成“=AVERAGE(物理)”。需要特别说明的是，公式引用“列标志”时的限制较多，它只能在当前数据列的下方引用，不能跨越工作表引用。从本质上讲，名称和标志都是单元格引用的一种方式。因为它们不是文本，使用时名称和标志都不能添加引号。,函数参数中标志的使用方法：,26,EXCEL函数的结构,Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。,Excel函数结构:,也有一些函数是没有参数的,如ROW(),左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用半角逗号进行分隔,27,函数参数常用符号或表示方法,函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入东南汽车或“东南汽车”连接符:A2:B7；1:1；1:5；F:F；A：N,28,Rank函数,用途：返回一个数值在一组数值中的排位(如果数据清单已经排过序了，则数值的排位就是它当前的位置)。语法：RANK(需要计算其排位的数字，存放要比较的数字的区域，排序方式)参数：排序方式为一数字，指明排位的方式。如果排序方式为0或省略，则按降序排列的数据清单进行排位。如果排序方式不为零，当作按升序排列的数据清单进行排位。注意：函数RANK对重复数值的排位相同。但重复数的存在将影响后续数值的排位。如在一列整数中，若整数60出现两次，其排位为5，则61的排位为7(没有排位为6的数值)。RANK(number,ref,order)=RANK(B7,$B$7:$B$19,1),29,Sum求和函数,用途：返回某一单元格区域中所有数字之和。语法：SUM(number1，number2，.)。语法格式：Number1，number2，.为1到30个需要求和的数值(包括逻辑值及文本表达式)、区域或引用。注意：参数表中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1、文本被转换为数字。如果参数为数组或引用，只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。参数太多的突破方法：巧用括号法,30,AVERAGE函数,用途：计算所有参数的算术平均值。语法：AVERAGE(number1，number2，.)。参数：Number1、number2、.是要计算平均值的130个参数。实例：如果A1：A5区域命名为分数，其中的数值分别为100、70、92、47和82，则公式“=AVERAGE(分数)”返回78.2。,31,ROUND对数值进取四舍五入,用途：按指定位数四舍五入某个数字。语法：ROUND(需四舍五入的数字，保留的小数位)注意：如果保留的小数位大于0，则四舍五入到指定的小数位；如果等于0，则四舍五入到最接近的整数；如果小于0，则在小数点左侧按指定位数四舍五入。实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3；=ROUND(82.149，2)返回82.15；=ROUND(21.5，-1)返回20；Round(A1,0)返回65。,32,INT无条件取整,用途：将任意实数向下取整为最接近的整数。语法：INT(实数)参数：Number为需要处理的任意一个实数。实例：如果A1=16.24、A2=-28.389，则公式“=INT(A1)”返回16，=INT(A2)返回-29。,33,TRUNC函数,用途：将数字的小数部分依需截去（不作四舍五入），返回实数。语法：TRUNC(需要截去小数部分的数字，保留小数的位数)注意：TRUNC函数可以按需要截取数字的小数部分，而INT函数则将数字向下舍入到最接近的整数。INT和TRUNC函数在处理负数时有所不同：TRUNC(-4.3)返回-4，而INT(-4.3)返回-5。实例：如果A1=78.652，则公式“=TRUNC(A1，1)”返回78.6，=TRUNC(A1，2)返回78.65，=TRUNC(-8.963，2)返回8.96。,34,比较两个字符串是否相同,用途：测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则返回FALSE。EXACT函数能区分大小写，但忽略格式上的差异。语法：EXACT(text1，text2)。参数：Text1是待比较的第一个字符串，Text2是待比较的第二个字符串。实例：如果A1=得利卡、A2=富利卡、A3=戈蓝，则公式“=EXACT(A1，A2)”返回FALSE，=EXACT(A1，A3)返回FALSE，=EXACT(word，word)返回TRUE。,EXACT:比较两个字符串是否相同,35,CONCATENATE文本合并,用途：将若干文字串合并到一个文字串中，其功能与性别为女者,称谓为XX女士,=IF(A10,负,)若整张EXCEL表的每个单元格都有数据,前述函数公式的结果为？,Counta的思考案例,49,MOD：取余函数,用途：返回两数相除的余数，其结果的正负号与除数相同。语法：MOD(被除数，除数)参数：除数不能为零。实例：公式“=MOD(14，4)”返回？2；“=MOD(5，-2)”返回？-1。,MOD：取余函数,50,Vlookup函数,返回表格或数组当前行中指定列处的数值。语法：VLOOKUP(索引值，数据区域，列序号，查找方式)参数：索引值为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。列序号指数据区域中待返回的匹配值的列序号。它等于1时，返回数据区域第一列中的数值；它等于2时，返回数据区域第二列中的数值，以此类推。查找方式为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值的最大数值；如果为0，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。,Vlookup,51,截取字符串函数,用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。语法：LEFT(文本,返回的字符数)Right(文本,返回的字符数)参数：返回的字符数必须大于或等于0。实例：如果A1=电脑爱好者，则LEFT(A1，2)返回?;Right(A1,3)返回?,Left/Right：截取字符串函数,52,Left与right的综合运用,如何利用Left及Right从身份证号中提取出生年份。,Left与right的综合运用,53,Mid：截取字符串函数,用途：MID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：MID(文本串,开始位置,返回字符的个数)参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。实例1：如果a1=东南(福建)汽车工业有限公司，则公式“=MID(A1，4，2)”返回“福建”。案例:利用MID从身份证号中提取出生年份。思考：MID函数与Left与right的综合运用的区别,Mid：,54,计算字符长度,用途：LEN返回文本串的字符数。语法：LEN(text)。参数：Text待要查找其长度的文本。注意：此函数用于双字节字符，且空格也将作为字符进行统计。实例：如果A1=电脑爱好者，则公式“=LEN(A1)”返回？,LEN：,55,If、Len及mid函数综合运用,如果A列里CM后没有“00”的话，就把“00“加到CM后,但是如果CM后有”00“话，就不加了（产生的效果如B列所示），怎么做？,公式提示：=IF(MID(A1,3,2)00,CM00&MID(A1,3,LEN(A1)-2),A1),56,替换指定文本,用途：在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定的文本，可以使用函数SUBSTITUTE；如果需要在某一文字串中替换指定位置处的任意文本，就应当使用函数REPLACE。语法：SUBSTITUTE(需要替换其中字符的文本，需被替换的旧文本，用於替换的文本，第几次出现的旧文本)。参数：第几次出现的旧文本为一数值，用来指定以新的文本替换第几次出现的旧文本；如果指定了该参数，则只有满足要求的旧文本被替换；否则将用新文本替换所有出现的旧文本。,SUBSTITUTE：替换指定文本,57,替换指定文本,SUBSTITUTE：替换指定文本,58,Rept：补位函数,用途：按照给定的次数重复显示文本。可以通过REPT函数对单元格进行重复填充。语法：REPT(需重复显示的文本，重复显示的次数)。参数：重复显示的次数需为正数。注意：REPT函数的结果不能多于255个字符。实例：公式“=REPT(很好，2)”返回“很好很好”。,Rept：,59,Rept函数与len函数的综合运用,60,Index函数,用途：返回表格或区域中的数值。语法：INDEX(单元格区域，行序号，列序号)返回数组中指定的单元格或单元格数组的数值。案例：,题一：=INDEX（A1：C5，2，2）的返回值是？256题二：=INDEX（A1：A5，5，1）的返回值是？100,Index函数,61,打印工资条案例,工资数据原表（存放在sheet1中）：,希望能在另一个工作表中将上述工资表转换为以下样式：,提示：=IF(MOD(ROW(),2)0,Sheet1!A$1,INDEX(Sheet1!$A:$N,ROW()/2+1,COLUMN(