Office办公软件培训资料

,WeBuildYourTrustinSolar,办公软件培训教材,IT部/舒进良2012-6-14,Excel使用技巧,Office办公软件,Excel函数运用,Excel快捷键,快捷键,在工作表中移动到当前数据区域的边缘：Ctrl+箭头键移动到行首：Home移动到工作表的开头：Ctrl+Home移动到工作表的最后一个单元格。：Ctrl+End向下移动一屏：PageDown向上移动一屏：PageUp向右移动一屏：Alt+PageDown向左移动一屏：Alt+PageUp移动到工作簿中下一个工作表：Ctrl+PageDown移动到工作簿中前一个工作表：Ctrl+PageUpF4键可以重复前一次操作，在很多情况下起作用，比如在工作表内加入或删除一行，然后移动插入点并按下F4键以加入或删除另一行，根本不需要使用菜单。,Excel-使用技巧,在输入数值时如果开头是0如何让它显示出来当我们在输入数值时，如编号如果开头是0的话，往往是显示不出来的。例如：输入009527显示出来是9527,0是显示不出来的。这里只要在前面加上一个单引号“”就可以显示出来（如身份证号码也是相同办法）,如果在一个Excel文件中含有多个工作表，如何将多个工作表一次设置成同样的页眉和页脚？把鼠标移到工作表的名称处（若你没有特别设置的话，Excel自动设置的名称是“sheet1、sheet2、sheet3.”），然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时你的所有操作都是针对全部选中的工作表，不管是设置页眉和页脚还是打印你的工作表。,Excel-使用技巧,在不同的单元格连续输入多个值我们有时需要在不同单元格内连续输入多个数据，但每次输入一个数据后按回车键，活动单元格均默认下移一个单元格，非常不便。方法:单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定其他单元格按回车键，光标自动停留在第一个选中单元格，再输入数据，敲回车键。光标会自动移动到下一个选中单元格。,Excel-使用技巧,快速换行我们在使用Excel制作表格时经常会遇到需要在一个单元格输入一行或几行文字的情况，如果输入一行后敲回车键就会移到下一单元格，而不是换行。简便实用的操作方法可以实现换行:在选定单元格输入第一行内容后，在换行处按“Alt+回车键”，即可输入第二行内容，再按“Alt+回车键”输入第三行以此类推。,Excel-使用技巧,通过一次按键创建一个Excel图表这是一个非常老套的Excel窍门。要想使用键盘快速创建一个图表，选择你需要绘制的数据并按下F11键。Excel将自动为你创建图表。另外，在选好单元格后按下“Alt+F1”，你将得到相同的结果。但是按下“F11”和“Alt+F1”键，区别如下：F11键生成图表存放在新的工作表中Alt+F1生成的图表直接存放在当前工作表中,Excel-使用技巧,禁止复制隐藏行或列中的数据如果你复制了包含隐藏列(或行)的一个数据区域，然后把它粘贴到一个新的工作表，那么Excel把隐藏列也粘贴过来了。要想避免这种情况，可以选取你要复制的数据区域，然后选择“编辑定位”命令(ctrl+G)，单击“定位条件”按钮，出现“定位条件”对话框，选中“可见单元格”选项，再复制和粘贴这个选定区域就会得到你所希望的结果。,Excel-使用技巧,快速给数字加上单位有时我们需要给输入的数值加上单位（如“立方米”等），少量的我们可以直接输入而大量的如果一个一个地输入就显得太慢了。下面的方法来实现单位的自动输入:先将数值输入相应的单元格中（注意:仅限于数值），然后在按住Ctrl键的同时选取需要加同一单位的单元格，单击“格式单元格”命令，打开“单元格格式”对话框（），在“数字”标签中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入#立方米，按下确定键后，单位（立方米）即一次性加到相应数值的后面。,Excel-使用技巧,按笔划排序,在默认情况下，Excel对中文字的排序方式是按照“字母”顺序的，以中文姓名为例，字母顺序即按姓的拼音的首字母在26个英文字母中出现的顺序进行排列，如果同姓，则依次计算名的第二、第三字。图中显示的表格包含了按字母顺序排列的姓名数据。,然而，在我们的习惯中，常常是按照“笔划”的顺序来排列姓名的。这种排序的规则是：按姓字的划数多少排列，同划数内的姓字按起笔顺序排列（横、竖、撇、捺、折），划数和笔形都相同的字，按字形结构排列，先左右、再上下，最后整体字。如果姓字相同，则依次看名第二、三字，规则同姓字。,1、单击数据区域中任意单元格，如A2。2、单击菜单“数据”“排序”，出现“排序”对话框。3、在“排序”对话框中，选择“主要关键字”为“姓名”，排序方式为升序。4、单击“排序”对话框中的“选项”按钮，在出现的“排序选项”对话框中，5、单击方法区域中的“笔划排序”单选按钮，如图所示，单击“确定”按钮6、单击“确定”按钮，关闭“排序”对话框。,Excel-使用技巧,在工作日历中突显周休日,很多用户喜欢使用Excel制作工作日历，如图所示的是公司人力资源部的工作日历的一部分。如果能把所有的周休日都醒目地标识出来，就能够避免一些时间安排上的问题了。,方法如下：1、单击A2并拖动光标到A17以选定区域A2:A17，单击菜单“格式”“条件格式”。在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：=WEEKDAY(A2,2)52、单击“格式”按钮，在“单元格格式”对话框的“字体”选项卡中选择“字形”为“加粗”，颜色为红色，单击“确定”按钮。单击“条件格式”对话框的“确定”按钮，如图所示。,上述公式使用了函数WEEKDAY计算活动单元格的星期，如果大于5即是周六或周日。,Excel-使用技巧,自动醒目的小计,在实际工作，常常需要在表格中使用小计行或小计列来汇总某类数据，如图所示。使用条件格式功能，能够快速地为所有小计行与小计列设置醒目的格式。,1、单击A1并拖动光标到H11以选定区域A1:H11，单击菜单“格式”“条件格式”。2、在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：=($A1=小计)+(A$1=小计)03、单击“格式”按钮，在“单元格格式”对话框的“字体”选项卡中选择“字形”为“加粗”，颜色为蓝色，单击“确定”按钮。4、单击“条件格式”对话框的“确定”按钮,上述的公式中使用的是相对引用，目的是判断A列与第1行中是否会出现“小计”字样，如果出现，则在相同的行、列中应用醒目的格式。根据逻辑值与数值的转换关系，以及逻辑值计算的原理，上述公式也可以写为=($A1=小计)+(A$1=小计)或者=OR($A1=小计),(A$1=小计),Excel-使用技巧,快速比较不同区域的数值（一）,Excel-使用技巧,Excel的多窗口特性能够帮助用户比较不同区域的数据，但是当需要比较的区域较大时，人工比较不但费时而且准确率不高。此时如果利用条件格式功能，则能快速而又准确地完成对比工作。在如下图所示的工作表中，源数据和校验数据分别位于A2B21和D2E21，如果希望标记出与源数据不匹配的校验数据，方法如下：,1、单击D2并拖动光标到E21以选定区域D2:E21，单击菜单“格式”“条件格式”。2、在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“单元格数值”项，运算符选择“不等于”，在右边的文本框中输入：=A23、单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮。4、单击“条件格式”对话框的“确定”按钮，关闭对话框。,快速比较不同区域的数值（二）,Excel-使用技巧,上页中介绍了利用条件格式快速对比不同区域中的数值的一种方法。然而在实际工作中，需要对比的数据可能并不是位置一一对应的。例如，如图所示的表格中，需要把与账号二不匹配的账号一标记出来，但两列账号的排列顺序并不相同，对于这种情况，条件格式的设置要相对复杂一些。,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函数的使用必须执行“工具加载宏分析工具库”方能使用,Excel-函数运用,函数参数中的单元格引用,根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用（如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列）,Excel-函数运用,函数参数中标志的使用方法,由于Excel工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而B1单元格已经输入了“物理”字样，则求物理平均分的公式可以写成“=AVERAGE(物理)”。需要特别说明的是，公式引用“列标志”时的限制较多，它只能在当前数据列的下方引用，不能跨越工作表引用。从本质上讲，名称和标志都是单元格引用的一种方式。因为它们不是文本，使用时名称和标志都不能添加引号。,Excel-函数运用,EXCEL函数的结构,Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。,Excel函数结构:,也有一些函数是没有参数的,如ROW(),左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用半角逗号进行分隔,Excel-函数运用,函数参数常用符号或表示方法,函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入东南汽车或“东南汽车”连接符:A2:B7；1:1；1:5；F:F；A：N,Excel-函数运用,Rank函数,用途：返回一个数值在一组数值中的排位(如果数据清单已经排过序了，则数值的排位就是它当前的位置)。语法：RANK(需要计算其排位的数字，存放要比较的数字的区域，排序方式)参数：排序方式为一数字，指明排位的方式。如果排序方式为0或省略，则按降序排列的数据清单进行排位。如果排序方式不为零，当作按升序排列的数据清单进行排位。注意：函数RANK对重复数值的排位相同。但重复数的存在将影响后续数值的排位。如在一列整数中，若整数60出现两次，其排位为5，则61的排位为7(没有排位为6的数值)。RANK(number,ref,order)=RANK(B7,$B$7:$B$19,1),Excel-函数运用,Rank的案例,对汽车厂商8月份销量进行排名：,Excel-函数运用,SUM：求和函数,用途：返回某一单元格区域中所有数字之和。语法：SUM(number1，number2，.)。语法格式：Number1，number2，.为1到30个需要求和的数值(包括逻辑值及文本表达式)、区域或引用。注意：参数表中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1、文本被转换为数字。如果参数为数组或引用，只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。参数太多的突破方法：巧用括号法,Excel-函数运用,AVERAGE:求算术平均值,用途：计算所有参数的算术平均值。语法：AVERAGE(number1，number2，.)。参数：Number1、number2、.是要计算平均值的130个参数。实例：如果A1：A5区域命名为分数，其中的数值分别为100、70、92、47和82，则公式“=AVERAGE(分数)”返回78.2。,Excel-函数运用,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。,Excel-函数运用,INT:无条件取整,用途：将任意实数向下取整为最接近的整数。语法：INT(实数)参数：Number为需要处理的任意一个实数。实例：如果A1=16.24、A2=-28.389，则公式“=INT(A1)”返回16，=INT(A2)返回-29。,Excel-函数运用,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。,Excel-函数运用,EXACT:比较两个字符串是否相同,用途：测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则返回FALSE。EXACT函数能区分大小写，但忽略格式上的差异。语法：EXACT(text1，text2)。参数：Text1是待比较的第一个字符串，Text2是待比较的第二个字符串。实例：如果A1=得利卡、A2=富利卡、A3=戈蓝，则公式“=EXACT(A1，A2)”返回FALSE，=EXACT(A1，A3)返回FALSE，=EXACT(word，word)返回TRUE。,Excel-函数运用,CONCATENATE,用途：将若干文字串合并到一个文字串中，其功能与性别为女者,称谓为XX女士,Excel-函数运用,案例二:依下述税率表,利用if函数计算个人所得税(课后练习),注意：因IF仅可嵌套七层，所以无法计算月薪超过102000者个人所得税,假如A3为存放税前工资的单元格,个人所得税计算公式如下:=IF(A382000,(A3-2000)*40%-10375,IF(A362000,(A3-2000)*35%-6375,IF(A342000,(A3-2000)*30%-3375,IF(A322000,(A3-2000)*25%-1375,IF(A37000,(A3-2000)*20%-375,IF(A34000,(A3-2000)*15%-125,IF(A32500,(A3-2000)*10%-25,IF(A32000,(A3-2000)*5%,0),Excel-函数运用,Sumif:条件求和函数,用途：根据指定条件对若干单元格、区域或引用求和。语法：SUMIF(条件区域，条件，需求和的区域)参数：条件是由数字、逻辑表达式等组成的判定条件。,Excel-函数运用,Sumif案例,请统计人资组同仁的工资总额:Sumif(A:A,人资组,C:C),Excel-函数运用,Countif：条件计数函数。,用途：计算区域中满足给定条件的单元格的个数。语法：COUNTIF(统计区域，条件)参数：“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。“条件”为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。,Excel-函数运用,Countif案例,以上述样表为据,完成以下案例:例1：依“师员”字段，统计师员级的人数countif(C:C,师)；countif(C:C,员)例2:统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数countif(I:I,=3)；countif(I:I,=3)-countif(I:I,=5)例3:检测到职编号是否有重复值countif(A:A,A2),Excel-函数运用,Counta函数,用途：返回参数组中非空值的数目。利用函数COUNTA可以计算数组或单元格区域中数据项的个数。语法：COUNTA(单元格区域1,单元格区域2)说明：参数的个数为130个。,Excel-函数运用,Counta的思考案例,例1：如果A1=6.28、A2=3.74，其余单元格为空，则公式“=COUNTA(A1:A7)”的计算结果等于？。例2：统计C列的非空白单元格的表示方法为：？;若整张EXCEL表的每个单元格都有数据,前述函数公式的结果为？,Excel-函数运用,MOD：取余函数,用途：返回两数相除的余数，其结果的正负号与除数相同。语法：MOD(被除数，除数)参数：除数不能为零。实例：公式“=MOD(14，4)”返回？2；“=MOD(5，-2)”返回？-1。,Excel-函数运用,Vlookup函数,返回表格或数组当前行中指定列处的数值。语法：VLOOKUP(索引值，数据区域，列序号，查找方式)参数：索引值为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。列序号指数据区域中待返回的匹配值的列序号。它等于1时，返回数据区域第一列中的数值；它等于2时，返回数据区域第二列中的数值，以此类推。查找方式为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值的最大数值；如果为0，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。,Excel-函数运用,Vlookup案例,案例一:利用vlookup抓取另一张表中的资料。,Excel-函数运用,Left/Right：截取字符串函数,用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。语法：LEFT(文本,返回的字符数)Right(文本,返回的字符数)参数：返回的字符数必须大于或等于0。实例：如果A1=电脑爱好者，则LEFT(A1，2)返回?;Right(A1,3)返回?,Excel-函数运用,Left与right的综合运用,如何利用Left及Right从身份证号中提取出生年份。,提示：假如A2存放身份证号，提取年份的做法：=RIGHT(LEFT(A2,10),4),Excel-函数运用,Mid：截取字符串函数,用途：MID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：MID(文本串,开始位置,返回字符的个数)参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。实例1：如果a1=东南(福建)汽车工业有限公司，则公式“=MID(A1，4，2)”返回“福建”。案例:利用MID从身份证号中提取出生年份。思考：MID函数与Left与right的综合运用的区别,Excel-函数运用,LEN：计算字符长度。,用途：LEN返回文本串的字符数。语法：LEN(text)。参数：Text待要查找其长度的文本。注意：此函数用于双字节字符，且空格也将作为字符进行统计。实例：如果A1=电脑爱好者，则公式“=LEN(A1)”返回？,Excel-函数运用,If、Len及mid函数综合运用,如果A列里CM后没有“00”的话，就把“00“加到CM后,但是如果CM后有