EXCEL常用公式
来源:百度文库 编辑:神马文学网 时间:2024/03/28 19:00:37
Excel是集表格、计算和数据库为一身的优秀软件,其数据处理的核心是函数和数据库。本文介绍Excel XP函数的新增功能、常用函数和数据库
的使用方法,以及Excel XP的网络集成特性。
一、Excel XP函数新增功能
1.公式错误检查
公式是Excel处理数据的主要工具,由于它的运算符和参数比较复杂,用户构造公式时极易发生错误。 Excel XP提供了公式错误检查功能。当单元格中的公式出现错误时,其左侧会出现智能标记按钮。单击该按钮可以打开一个智能标记菜单,其上端显示出错误的名称(如“无效名称错误”),能帮助用户迅速发现错误原因。
2.函数工具提示
用户直接输入公式的函数名和参数时,不能看到函数格式和参数的提示,这是公式发生错误的一个重要原因。为此,Excel XP增加了函数工具提示功能。以公式“=SUMIF(A1:A8)”为例,当你输入=SUMIF()”后,公式下方会显示黄色的函数工具提示条,提供了有关函数和参数的语法信息。你只要单击其中的函数或参数名称,就可以打开帮助获得更多信息。
3.用自然语言搜索函数
Excel拥有数百个函数,寻找适用的函数是初级用户面临的难题。为此,Excel XP在“插入函数”对话框中增加了“搜索函数”功能。假如你要完成数据排序任务,可以单击工具栏中的“插入函数”按钮打开对话框,在其中的“搜索函数”框内输入“排序”,然后单击“转到”按钮,对话框下面的“选择函数”框中显示“RANK”等排序函数。
4.监视窗口
如果一个工作表的不同单元格或者多个工作表设置了公式,要想查看其内容和计算结果,必须在不同单元格或工作表中转换。为此,Excel XP增加了一个名为监视窗口的工具。其使用方法是:选中含有公式的待监视单元格,再用鼠标右键单击工具栏选择“监视窗口”。然后单击“监视窗口”中的“添加监视”按钮,被监视的公式及其计算结果就会显示在监视窗口中。
5.公式审核 Excel XP新增了一个“公式审核”工具栏,它提供了几个新的数据审查工具,例如“错误检查”、“追踪引用单元格”和“公式求值”。“错误检查”与语法检查程序类似,它用特定的规则检查公式中存在的问题,可以查找并发现常见错误,你可以在“选项”对话框的“错误检查”选项卡中启用或关闭这些规则。“追踪引用单元格”可以用蓝色箭头等标出公式引用的所有单元格,追踪结束后可以使用“移去单元格追踪箭头”按钮将标记去掉。“公式求值”可以打开一个对话框,用逐步执行方式查看公式计算顺序和结果,能够清楚了解复杂公式的计算过程。
二、Excel 函数快速上手
1.求和函数SUM
语法:SUM(number1,number2,...)。
参数:number1、number2...为1到30个数值(包括逻辑值和文本表达式)、区域或引用,各参数之间必须用逗号加以分隔。
注意:参数中的数字、逻辑值及数字的文本表达式可以参与计算,其中逻辑值被转换为1,文本则被转换为数字。如果参数为数组或引用,只有其中的数字参与计算,数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。
应用实例一:跨表求和
使用SUM函数在同一工作表中求和比较简单,如果需要对不同工作表的多个区域进行求和,可以采用以下方法:选中Excel XP“插入函数”对话框中的函数,“确定”后打开“函数参数”对话框。切换至第一个工作表,鼠标单击“number1”框后选中需要求和的区域。如果同一工作表中的其他区域需要参与计算,可以单击“number2”框,再次选中工作表中要计算的其他区域。上述操作完成后切换至第二个工作表,重复上述操作即可完成输入。“确定”后公式所在单元格将显示计算结果。
应用实例二:SUM函数中的加减混合运算
财务统计需要进行加减混合运算,例如扣除现金流量表中的若干支出项目。按照规定,工作表中的这些项目没有输入负号。这时可以构造“=SUM(B2:B6,C2:C9,-D2,-E2)”这样的公式。其中B2:B6,C2:C9引用是收入,而D2、E2为支出。由于Excel不允许在单元格引用前面加负号,所以应在表示支出的单元格前加负号,这样即可计算出正确结果。即使支出数据所在的单元格连续,也必须用逗号将它们逐个隔开,写成“=SUM(B2:B6,C2:C9,-D2,-D3,D4)”这样的形式。
应用实例三:及格人数统计
假如B1:B50区域存放学生性别,C1:C50单元格存放某班学生的考试成绩,要想统计考试成绩及格的女生人数。可以使用公式“=SUM(IF(B1:B50=″女″,IF(C1:C50>=60,1,0)))”,由于它是一个数组公式,输入结束后必须按住Ctrl+Shift键回车。公式两边会自动添加上大括号,在编辑栏显示为“{=SUM(IF(B1:B50=″女″,IF(C1:C50>=60,1,0)))}”,这是使用数组公式必不可少的步骤。
2.平均值函数AVERAGE
语法:AVERAGE(number1,number2,...)。
参数:number1、number2...是需要计算平均值的1~30个参数。
注意:参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻辑值或空白单元格将被忽略,但单元格中的零则参与计算。如果需要将参数中的零排除在外,则要使用特殊设计的公式,下面的介绍。
应用实例一:跨表计算平均值
标签名为“一班”、“二班”和“三班”的工作表存放各班学生的成绩,则它们的总平均分计算公式为“=AVERAGE(一班!C1:C36,三班!C1:C32,三班!C1:C45)”。式中的引用输入方法与SUM跨表求和时相同。
应用实例二:忽略零的平均值计算公式
假设A1:A200随机存放包括零在内的48个数值,在AVERAGE参数中去掉零引用很麻烦,这种情况可以使用公式“=AVERAGE(IF(A1:A200<>0,A1:A200,″″)”。公式输入结束后按住Ctrl+Shift回车,即可对A1:A200中的数值(不包括零)计算平均值。
3.逻辑函数IF
语法:IF(logical_test,value_if_true,value_if_false)。
参数:logical_test是结果为true(真)或false(假)的数值或表达式;value_if_true是logical_test为true时函数的返回值,如果logical_test为ture并且省略了value_if_true,则返回true。而且value_if_true可以是一个表达式;value_if_false是logical_test为false时函数的返回值。如果logical_test为false并且省略value_if_false,则返回false。Value_if_false也可以是一个表达式。
应用实例一:个人收入调节税计算
假设个人收入调节税的稽征办法是:工资在1000元以下的免征调节税,工资1000元以上至1500元的超过部分按5%的税率征收,1500元以上至2000元的超过部分按8%的税率征收,高于2000元的超过部分按30%的税率征收。
假如工作表的B列存放职工姓名,C列存放工资,选中D列的空白单元格输入公式“=IF(C2<=1000,″″,IF((C2-1000)<=1500,(C2-1000)*0.05,IF(C2-E2<=1500,(C2-1500)*0.08,IF(C2>2000,(C2-2000)*0.3))))”,回车后即可计算出C2工资应缴纳的收入调节税。
公式中的IF语句是逐次计算的,如果第一个逻辑判断C2<=1000成立,则公式所在单元格被填入空格;如果第一个逻辑判断式不成立,则计算第二个IF语句;直至计算结束。如果税率征收标准发生了变化,只须改变逻辑和计算式中的值,如1000、1500和2000等即可。
应用实例二:消除无意义的零
用SUM函数计算工资总额等问题时,若引用的全部参数均为空白单元格,公式仍然会计算出一个“0”。这样打印出来的报表不仅很不美观。为此可将计算公式设计为“=IF(SUM(A1:B1,D2:E8)<>0,SUM(A1:B1,D2:E8),″″)”,即是当SUM(A1:B1,D2:E8)计算结果不等于零时,公式所在单元格显示SUM(A1:B1,D2:E8)的计算结果,否则显示一个空格。
上面这个问题在财务计算中依然存在,如公式“=A1-A6-A9”有可能为零或显示负数。为了避免打印零或负数,可以设计公式“=IF(A2-A6-A9=0,″″,IF(A2-A6-A9<0,RMB(A2-A6-A9),A2-A6-A9))”。当A2-A6-A9=0时在单元格中填写零,否则进行下一个逻辑判断。如果A2-A6-A9<0则在结果前添加一个“¥”符号,否则进行正常运算直至结束。
应用实例三:多条件求和
假设C1:C460区域内存放着职工的职称,D1:D460区域存放着职工的性别。如果要统计具有高级职称的男性职工总数,可以使用公式“=SUM(IF(C1:C460=″高级″,IF(D1:D460=″男″,1,0)))”。这是一个数组公式,输入结束后按住Ctrl+Shift回车即可计算出结果。
4.快捷方式或链接创建函数HYPERLINK
语法:HYPERLINK(link_location,friendly_name)。
参数:link_location是文件的路径和文件名,它可以指向文档中的某个具体位置。如Excel工作表中的特定单元格或区域,或某个Word文档中的书签,也可以指向硬盘中的文件或是Internet或Intranet的URL。Friendly_name为单元格中显示的链接文字或数字,它用蓝色显示并带有下划线。如果省略了friendly_name,单元格就将link_location显示为链接。
应用实例:管理文档或网站地址
如果你拥有大量文档或收集了许多网站地址,管理起来一定有困难。如果能够将其中的文档名、URL等与文档等对象链接起来,不仅管理方便,还可以直接打开文档或访问站点。具体操作方法是:首先根据文档类型建立管理工作表,其中的文件名或网站名必须使用以下方法输入:选中一个空白单元格,单击Excel XP工具栏中的“粘贴函数”按钮,打开“粘贴函数”对话框,在“函数分类”框下选中“查找与引用”,然后在“函数名”框内找到HYPERLINK函数。单击对话框中的“确定”按钮,弹出“HYPERLINK”函数向导。在“link_location”框中输入文件的完整路径和名称(包括扩展名),如“C:\my documents\IT网站集锦.doc”,然后在“friendly_name”框中输入文件名(如“IT网站集锦”)。
确认输入无误单击“确定”按钮,所选单元格即出现带下划线的紫色文件名“IT网站集锦”。
此后你就可以从工作表中打开文档,方法是:打开工作表,在其中找到需要打开的文件。箭头光标指向文件名会变成手形,若停留片刻则会显示该文件的完整路径和名称,单击则会调用关联程序将文件打开。
如果你收集了许多网站的URL,还可以用此法建立一个大型“收藏夹”。既可以用Excel的强大功能进行管理,又可以从工作表中直接访问Web站点。
5.计数函数COUNT
语法:COUNT(value1,value2,...)。
参数:value1,value2...是包含或引用各类数据的1~30个参数。
注意:COUNT函数计数时数字、日期或文本表示的数字会参与计数,错误值或其他无法转换成数字的文字被忽略。如果参数是一个数组或引用,那么只有数组或引用中的数字参与计数;其中的空白单元格、逻辑值、文字或错误值均被忽略。
应用实例:及格率统计
假如C1:G42存放着42名学生的考试成绩,在一个空白单元格内输入公式“=COUNTIF(C1:C42,″>=60″)/COUNTA(C1:C42)”回车,即可计算出该列成绩的及格率(即分数为60及以上的人数占总人数的百分比)。
6.最大值函数MAX、最小值函数MIN
语法:MAX(number1,number2,...),MIN(number1,number2,...)。
参数:number1,number2...是需要找出最大值(最小值)的1至30个数值、数组或引用。
注意:函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式,如果参数是不能转换为数字的内容将导致错误。如果参数为数组或引用,则只有数组或引用中的数字参与计算,空白单元格、逻辑值或文本则被忽略。
应用实例:查询最高分(最低分)
假如C1:G42存放着42名学生的考试成绩,则选中一个空白单元格,在编辑栏输入公式“=MAX(C1:C42)”,回车后即可计算出其中的最高分是多少。
如果将上述公式中的函数名改为MIN,其他不变,就可以计算出C1:G42区域中的最低分。
7.条件求和函数SUMIF
语法:SUMIF(range,criteria,sum_range)。
参数:range是用于条件判断的单元格区域,criteria是由数字、逻辑表达式等组成的判定条件,sum_range为需要求和的单元格、区域或引用。
应用实例:及格平均分统计
假如A1:A36单元格存放某班学生的考试成绩,若要计算及格学生的平均分,可以使用公式“=SUMIF(A1:A36,″>=60″,A1:A36)/COUNTIF(A1:A36,″>=60″)。公式中的“=SUMIF(A1:A36,″>=60″,A1:A36)”计算及格学生的总分,式中的“A1:A36”为提供逻辑判断依据的单元格引用,“>=60”为判断条件,不符合条件的数据不参与求和,A1:A36则是逻辑判断和求和的对象。公式中的COUNTIF(A1:A36,″>=60″)用来统计及格学生的人数。
8.贷款偿还计算函数PMT
语法:PMT(rate,nper,pv,fv,type).
参数:如今贷款买房子或车子的人越来越多,计算某一贷款的月偿还金额是考虑贷款的重要依据,Excel XP提供的PMT函数是完成这一任务的好工具。语法中的rate是贷款利率;nper为贷款偿还期限;pv是贷款本金;fv为最后一次付款后剩余的贷款金额,如果省略fv,则认为它的值为零;type为0或1,用来指定付款时间是在月初还是月末。如果省略type,则假设其值为零。
应用实例:购房还款金额
假如你为购房贷款十万元,如果年利率为7%,每月末还款。采用十年还清方式时,月还款额计算公式为“=PMT(7%/12,120,-100000)”。其结果为¥-1,161.08,就是你每月须偿还贷款1161.08元。
9.样本的标准偏差函数STDEV
语法:STDEV(number1,number2,...)。
参数:number1,number2,...为对应于总体样本的1到30个参数,它们可以是数值、引用或数组。
注意:STDEV函数的参数是总体中的样本,并忽略参数中的逻辑值(true或false)和文本。如果需要用全部数据计算标准偏差,则应使用STDEVP函数。如果参数中的逻辑值和文本不能忽略,请使用STDEVA函数。
应用实例一:成绩离散度估计
假设某班共有36名学生参加考试,随机抽取的五个分数为A1=78、A2=45、A3=90、A4=12和A5=85。如果要估算本次考试成绩相对平均分的离散程度,即学生的考试成绩偏离平均分的多少,可以使用公式“=STDEV(A1:A5)”。其计算结果为33.00757489,标准偏差的数值越大成绩越分散。
应用实例二:质量波动估计
质量控制等场合也能用到STDEV函数,如从一批钢丝绳中随机抽出若干进行试验,分别测出它们的抗拉强度。根据STDEV函数的计算结果即可判断钢丝绳的抗拉强度是否分散,如果计算的标准偏差比较小,说明抗拉强度的一致性好,质量比较稳定。反之说明钢丝绳的质量波动较大,抗拉强度不够一致。
10.排序函数RANK
语法:RANK(number,ref,order)。
参数:number是需要计算其排位的一个数字;ref是包含一组数字的数组或引用(其中的非数值型参数将被忽略);order是用来说明排序方式的数字(如果order为零或省略,则以降序方式给出结果,反之按升序方式)。
应用实例:产值排序
假如E2、E3、E4单元格存放一季度的总产值。计算各车间产值排名的方法是:在F2单元格内输入公式“=RANK(E2,$E$2:$E$4)”,敲回车即可计算出铸造车间的产值排名是2。再将F2中的公式复制到剪贴板,选中F3、F4单元格按Ctrl+V,就能计算出其余两个车间的产值排名3和1。如果B1单元格中输入的公式为“=RANK(E2,$E$2:$E$4,1)”,则计算出的序数按升序方式排列,即2、1和3。
需要注意的是:相同数值用RANK函数计算得到的序数(名次)相同,但会导致后续数字的序数空缺。假如上例中F2单元格存放的数值与F3相同,则按本法计算出的排名分别是3、3和1(降序时)。即176.7出现两次时,铸造和维修车间的产值排名均为3,后续金工车间的排名就是1(没有2)。
11.四舍五入函数
语法:ROUND(number,num_digits)。
参数:number是需要四舍五入的数字;num_digits为指定的位数,number将按此位数进行四舍五入。
注意:如果num_digits大于0,则四舍五入到指定的小数位;如果 num_digits 等于 0,则四舍五入到最接近的整数;如果 num_digits 小于 0
,则在小数点左侧按指定位数四舍五入。
应用实例:消除计算误差
假设Excel工作表中有D2=356.68、E2=128.12,需要将D2与E2之和乘以0.1,将计算结果四舍五入取整数,再将这个结果乘以1.36(取两位小数)得到最终结果。
一般用户的做法是选中某个单元格(如F2),使用“单元格”命令将它的小数位数设为零,然后在其中输入公式“F2=(D1+E1)*0.1”。再将G2单元格的小数位数设成两位,最后把F2*1.36的结果存入其中就可以了。从表面上看,上述方法没有什么问题。因为(D1+E1)*0.1在F2单元格显示48(注意:是显示48),如果F2单元格的小数位数为零,(D1+E1)*0.1经四舍五入后的结果就是48。接下去却出了问题,因为F2*1.36的计算结果是65.90,根本不是48*1.36的正确结果65.28,其中65.90是(D2+E2)*0.1未经四舍五入直接乘以1.36的结果。
以上计算结果说明:“单元格格式”、“数字”选项卡设置的“小数位数”,只能将单元格数值的显示结果进行四舍五入,并不能对所存放的数值进行四舍五入。换句话说,单元格数值的显示结果与实际存放结果并不完全一致,如果不注意这个问题,计算工资等敏感数据就会出现错误。例如在上例中,F2单元格内的数值虽然显示为48,但实际存放的却是48.45,自然得出了48.45*1.36=65.90的结果(按计算要求应为65.28)。
要解决这个问题并不难,你只须在G2单元格内输入公式“=(ROUND((D2+E2)*0.1,0))*1.36”,就可以按要求计算出正确结果65.28。式中的ROUND函数按指定位数对“(D2+E2)*0.1”进行四舍五入,函数中的参数0将“(D2+E2)*0.1”四舍五入到最接近的整数。
12.条件计数函数COUNTIF
语法:COUNTIF(range,criteria)。
参数:range为需要统计的符合条件的单元格区域;criteria为参与计算的单元格条件,其形式可以为数字、表达式或文本(如36、″>160″和″男″等)。条件中的数字可以直接写入,表达式和文本必须加引号。
应用实例:男女职工人数统计
假设 A1:A58区域内存放着员工的性别,则公式“=COUNTIF(A1:A58,″女″)”统计其中的女职工数量,“=COUNTIF(A1:A58,″男″)”统计其中的男职工数量。
COUNTIF函数还可以统计优秀或及格成绩的数量,假如C1:G42存放着42名学生的考试成绩,则公式“=COUNTIF(C2:G2,″>=85″)”可以计算出其中高于等于85分的成绩数目。如将公式改为“=COUNTIF(C2:G2,″>=60″)”,则可以计算出及格分数的个数。
13.百分排位预测函数PERCENTILE
语法:PERCENTILE(array,k)。
参数:array为定义相对位置的数值数组或数值区域,k为数组中需要得到其百分排位的值。
注意:如果array所在单元格为空白或数据个数超过8191,则返回#NUM!错误。如果k<0或k>1,则返回#NUM!错误。如果k不是1/(n-1)的倍数,该函数使用插值法确定其百分排位。
应用实例:利润排行预测
假设C1:C60区域存放着几十个公司的利润总额。如果你想知道某公司的利润达到多少,才能进入排名前10%的行列,可以使用公式“=PERCENTILE(C1:C60,0.9)”。如果计算结果为9867万元,说明利润排名要想进入前10%,则该公司的利润至少应当达到9867万元。
14.数值探测函数ISNUMBER
语法:ISNUMBER(value)。
参数:如果value为数值时函数返回ture(真),否则返回false(假)。
应用实例一:无错误除法公式 如果工作表为C1设计了公式“=A1/B1”,一旦B1单元格没有输入除数,就会在C1中显示错误信息“#DIV/0!”。这不仅看起来很不美观,一旦作为报表打印还可能引起误会。为避免上面提到的问题出现,可将C1单元格中的公式设计成“=IF(ISNUMBER(B1),A1/B1,″″)”。式中的ISNUMBER函数对B1单元格进行探测,当B1被填入数值时返回true(真),反之返回false(假)。为真时IF函数执行A1/B1的运算,为假时在C1单元格中填入空格。
应用实例二:消除无意义的零
使用SUM函数计算工资总额时,若引用的单元格区域没有数据,Excel仍然会计算出一个结果“0”。这样打印出来的报表不符合财务规定,为此可将公式设计成“=IF(ISNUMBER(A1:B1),SUM(A1:B1),″″)”。
式中ISNUMBER函数测试SUM 函数所引用的单元格区域是否全部为空,当不为空时返回true(真),反之返回fales(假)。为真时IF函数执行SUM(A1:B1),为假时在存放计算结果的F1单元格中填入空格,这样即可避免在F1单元格中出现“0”。
15.零存整取收益函数PV
语法:PV(rate,nper,pmt,fv,type)。
参数:rate为存款利率;nper为总的存款时间,对于三年期零存整取存款来说共有3*12=36个月;pmt为每月存款金额,如果忽略pmt则公式必须包含参数fv;fv为最后一次存款后希望得到的现金总额,如果省略了fv则公式中必须包含pmt参数;type为数字0或1,它指定存款时间是月初还是月末。
应用实例:零存整取收益函数PV
假如你每月初向银行存入现金500元,如果年利2.15%(按月计息,即月息2.15%/12)。如果你想知道5年后的存款总额是多少,可以使用公式“=FV(2.15%/12,60,-500,0,1)”计算,其结果为¥31,698.67。
式中的2.15%/12为月息;60为总的付款时间,在按月储蓄的情况下为储蓄月份的总和;-500为每月支出的储蓄金额(-表示支出);0表示储蓄开始时账户上的金额,如果账户上没有一分钱,则为0否则应该输入基础金额;1表示存款时间是月初还是月末,1为月初0或忽略为月末。
16.内部平均值函数TRIMMEAN
语法:TRIMMEAN(array,percent)。
参数:array为需要去掉若干数据然后求平均值的数组或数据区域;percent为计算时需要除去的数据的比例,如果 percent = 0.2,说明在20个数据中除去 4 个,即头部除去 2 个尾部除去 2 个。如果percent=0.1,则30个数据点的10%等于3,函数TRIMMEAN将在数据首尾各去掉一个数据。
应用实例:评比打分统计
歌唱比赛采用打分的方法进行评价,为了防止个别人的极端行为,一般计算平均分数要去掉若干最高分和最低分。假如B1:B10区域存放某位歌手的比赛得分,则去掉一个最高分和一个最低分后的平均分计算公式为“=TRIMMEAN(B1:B10,0.2)”。公式中的0.2表示10个数据中去掉2个(10×0.2),即一个最高分和一个最低分。
17.日期年份函数YEAR
语法:YEAR(serial_number)。
参数:serial_number为待计算年份的日期。
应用实例:“虚工龄”计算
所谓“虚工龄”就是从参加工作算起,每过一年就增加一年工龄,利用YEAR函数计算工龄的公式是“=YEAR(A1)-YEAR(B1)”。公式中的A1和B1分别存放工龄的起止日期,YEAR(A1)和YEAR(B1)分别计算出两个日期对应的年份,相减后得出虚工龄。
18.起止天数函数DAYS360
语法:DAYS360(start_date,end_date,method)。
参数:start_date和end_date是用于计算期间天数的起止日期,可以使用带引号的文本串(如"1998/01/30")、系列数和嵌套函数的结果。如果start_date在end_date之后,则DAYS360将返回一个负数。
19.取整函数TRUNC
语法:TRUNC(number,num_digits)。
参数:number是需要截去小数部分的数字,num_digits则指定保留到几位小数。
应用实例:“实工龄”计算
实际工作满一年算一年的工龄称为“实工龄”,如1998年6月1日至2001年12月31日的工龄为3年。计算“实工龄”的公式是“= TRUNC((DAYS360(″1998/6/1″,″2001/12/31″))/360,0)”,公式中的DAYS360(″1998/6/1″,″2001/12/31″)计算两个日期相差的天数,除以360后算出日期相差的年份(小数)。最后TRUNC函数将(DAYS360(A1,B1)/360的计算结果截去小数部分,从而得出“实工龄”。如果计算结果需要保留一位小数,只须将公式修改为“= TRUNC((DAYS360(″1998/6/1″,″2001/12/31″))/360,1)”即可。
如果你要计算参加工作到系统当前时间的实工龄,可以将公式修改为“= TRUNC((DAYS360(″1998/6/1″,NOW()))/360,0)”。其中NOW()函数返回当前的系统日期和时间。
20.字符提取函数MID
语法:MID(text,start_num,num_chars)或MIDB(text,start_num,num_bytes)。
参数:text是含有要待提取字符的文本,start_num是要提取的第一个字符的位置(其中第一个字符的start_num为1以此类推),num_chars 指定MID从文本中提取的字符个数,Num_bytes指定MIDB从文本中提取的字符个数(按字节计算)。
应用实例:姓氏提取和统计
假如工作表C列存放着职工的姓名,如果想知道某一姓氏的职工人数,可以采用以下方法。首先使用公式“=MID(C1,1,1)”或“=MIDB(C1,1,2),从C1中提取出职工的姓氏,采用拖动或复制的方法即可提取其他单元格中的职工姓氏。然后使用公式“=COUNTIF(D1:D58,″张″)”统计出姓张的职工数量,随后改变公式中的姓氏就可以统计出其他姓氏职工的数量。
三、Excel XP数据库速成
Excel XP具有强大的数据库功能,对包含大量数据的表格进行排序、筛选等处理易如反掌,下面介绍常用的数据库操作。
1.数据排序
Excel XP第一行的单元格具有特殊作用,其中的文字相当于数据库字段名,可作为关键字参与排序、记录单等各种操作。
(1)文本排序选举等场合需要按姓氏笔划为文本排序,Excel提供了比较好的解决办法。如果你要将图1数据表按车间名称的笔划排序,可以使用以下方法:
选中排序关键字所在列(或行)的首个单元格,单击Excel“数据”菜单下的“排序”命令打开对话框,再单击其中的“选项”按钮。选中“排序选项”对话框“方法”下的“笔画排序”,再根据数据排列方向选择“按行排序”或“按列排序”,“确定”后回到“排序”对话框。如果你的数据带有标题行(如“单位”之类),则应选中“有标题行”(反之不选),然后打开“主要关键字”下拉列表,选择其中的“单位”,
选中排序方式(“升序”或“降序”)后“确定”,表中的所有数据就会据此重新排列。此法稍加变通即可用于“第一名”、“第二名”等文本的排序,请大家自行摸索。
(2)自定义排序
如果你要求Excel按照“金工车间”、“铸造车间”和“维修车间”的特定顺序重排工作表数据,前面介绍的几种方法就无能为力了。这类问题可以用定义排序规则的方法解决:首先单击Excel“工具”菜单下的“选项”命令,打开“选项”对话框中的“自定义序列”选项卡。选中左边“自定义序列”下的“新序列”,光标就会在右边的“输入序列”框内闪动,你就可以输入“金工车间”、“铸造车间”等自定义序列了,输入的每个序列之间要用英文逗号分隔,或者每输入一个序列就敲回车。如果序列已经存在于工作表,可以选中序列所在的单元格区域单击“导入”,这些序列就会被自动进入“输入序列”框。无论采用以上那种方法,单击“添加”按钮即可将序列放入“自定义序列”中备用。
使用排序规则排序的具体方法与笔划排序很相似,只是你要打开“排序选项”对话框中的“自定义排序次序”下拉列表,选中前面定义的排序规则,其他选项保持不动。回到“排序”对话框后根据需要选择“升序”或“降序”,“确定”后即可完成数据的自定义排序。
2.数据检索
如果Excel XP工作表中的数据字段很多时,查看一条或几条记录需要左右滚动屏幕,此时可以采用以下方法检索并查看:单击“数据”菜单下的“记录单”命令打开对话框(图2)。
再次单击其中的“条件”按钮。对话框中的字段就会变成空白等待输入,并且“条件”按钮变为“表单”按钮。在对话框的相应字段位置输入条件,如果在“序号”字段框内输入12回车,则序号为12的数据就会显示在对话框中。当然,你输入的检索条件可以使用>、<、>=、<=、<>逻辑符号。例如单击“条件”按钮后在“总分”框内输入“>170”,则表示检索总分大于170的记录,回车后第一条大于170的数据就会显示在对话框中。继续单击“上一条”或“下一条”按钮,可以查看检索出来的其他数据。
3.数据筛选
上面介绍的方法可以检索满足某一条件的记录,但是检索条件比较复杂时(如检索总分大于170和小于120的纪录),这种检索方法就无能为力了。此时可以使用Excel XP提供的自定义筛选功能,操作方法是:单击“数据”、“筛选”子菜单下的“自动筛选”命令,每个字段名的右侧会出现一个下拉按钮。根据你要筛选的字段单击下拉按钮,在下拉菜单中选择“自定义”命令打开对话框(图3)。
单击第一行左侧的下拉按钮,选择其中的“大于”,然后在其右边输入170;按相同方法在第二行中选择“小于”,并在右边输入120,选中两行中间的“或”后单击“确定”按钮,工作表会自动显示所有符合筛选条件的记录。筛选出来的记录可以执行复制等操作,如果你要清除筛选结果,只要将“数据”、“筛选”子菜单下的“自动筛选”前的选中标记去掉即可。
4.数据汇总
小计、合计是应用非常广泛的数据统计方式,Excel XP分类汇总是此类操作的高手。你只要将光标放在工作表的任一单元格中,单击“数据”菜单下的“分类汇总”命令打开对话框。如果要对某个班级的数学、语文成绩进行汇总,则应在“分类字段”中选中“班级”,在“汇总方式”中选择“求和”,在“选择汇总项”中选中“数学”和“语文”。如果选中“汇总结果显示在数据下放”,单击“确定”按钮后汇总结果会显示在工作表数据的下面,反之则会显示在工作表数据上方。如果你想清除工作表中的分类汇总结果,只要打开“分类汇总”对话框,单击其中的“全部删除”按钮就可以了。
5.数据透视表
数据透视表是Excel XP数据库功能的重要组成部分,它能够非常灵活的汇总数据。如果你要对一个学生成绩工作表进行统计,计算所有学生的数学成绩总分,采用数据透视表可以快速完成任务。
单击“数据”菜单下的“数据透视表和数据透视图”命令打开向导,首先选择数据源类型和创建的报表类型,完成后单击“下一步”按钮。这时要选择建立数据透视表的数据区域,你只要用鼠标在工作表中拖动即可将引用的数据区域放入对话框。接着选择数据透视表的位置,可以选择“新建工作表”或“现有工作表”。单击“完成”按钮,将“数据透视表字段列表”中的“数学”拖入工作表的指定位置,Excel XP会立刻计算出所有学生的数学成绩合计。
6.数据查找
数据筛选的对象是工作表中的某条记录,这里介绍的查找对象则是单元格中的数据,Excel XP可以执行的查找有以下几种类型:
(1)普通查找
它利用编辑菜单中的查找命令查找特定的数据,操作方法如下:单击“编辑”菜单中的“查找”命令,打开“查找”对话框。在“查找内容”后输入要查找的内容,单击“查找下一个”按钮,光标就会停留在首先发现数据的单元格中。如果没有发现要查找的数据,Excel XP会弹出对话框告知用户。
(2)特殊查找
特殊查找可以找出包含相同内容(如文本)或格式的所有单元格,或者存在内容差异的单元格,其操作步骤如下:如果你要找出工作表中含有文本的所有单元格,请选中含有文本任意一个单元格,然后单击“编辑”菜单中的“定位”命令。再单击对话框中的“定位条件”按钮,选中“定位条件”对话框中的“常量”,再选中复选项中的“文本”。单击“确定”按钮后,所有符合条件的所有单元格就会选中。
如果你要找出数据行或列中与活动单元格不匹配的部分,可以打开“定位条件”对话框。如果需要找出特定行中与活动单元格不同的单元格,可以选择“行内容差异单元格”;若要找出列中与活动单元格不同的单元格,则应选中“列内容差异单元格”,单击“确定”按钮即可完成查找。
四、Excel XP网络集成
1.Web数据共享
Excel XP可以从网页中导入数据进行分析。你只须选择“数据”、“导入外部数据”子菜单下的“导入数据”,在“选取数据源”对话框中找到Web页,单击“打开”按钮就可以将其导入Excel。当你进行过一次数据导入后,用鼠标右键单击打开的网页,可以看到快捷菜单中增添了“导出到Microsoft Excel”命令。如果希望从远程数据源导入数据,则应使用“数据连接向导”,它可以从Microsoft SQL Server、开放式数据库连接(ODBC)以及联机分析处理(OLAP)等数据源中查找并导入数据。
此外,Excel XP可以直接粘贴Word XP表格,从而共享其中的数据,进一步扩大了两者协同工作的范围。
2.E-mail功能
Excel的“常用”工具栏带有一个“电子邮件”按钮。单击它可以在当前窗口显示一个邮件发送工具栏,其界面与Outlook Express很相似,你只要输入收件人的E-mail地址和主题等,就可以将文件以附件或正文的形式发送出去。作为正文发送时,只须填写对方的E-mail地址,并在“主题”栏内进行简单介绍,然后单击“发送该工作表”按钮(如果你选中了工作表中的部分区域,该按钮变成“发送所选区域”)即可。当其他用户收到邮件后,可以直接对正文进行编辑修改,处理完毕后用同样方法将文件发给你。单击“附加文件按钮”,可以打开“插入附件”对话框,像Outlook Express那样插入并发送附件。
3.网页保存和预览
Excel XP“文件”菜单下有一条“保存为Web页”命令,用户可以选择将工作簿或当前工作表保存为Web页(HTML、XML),还可以在工作表中添加交互功能。
Excel XP内建了网页浏览功能,如果你想将工作表发布到网上,只要单击“文件”菜单下的“Web页预览”命令,Excel XP就会自动调用IE打开工作表。
类别:office园地 | |添加到搜藏 |分享到i贴吧 | 浏览(12) |评论 (0)
上一篇:一句话,让你豁然开朗 下一篇:加密Excel文件
的使用方法,以及Excel XP的网络集成特性。
一、Excel XP函数新增功能
1.公式错误检查
公式是Excel处理数据的主要工具,由于它的运算符和参数比较复杂,用户构造公式时极易发生错误。 Excel XP提供了公式错误检查功能。当单元格中的公式出现错误时,其左侧会出现智能标记按钮。单击该按钮可以打开一个智能标记菜单,其上端显示出错误的名称(如“无效名称错误”),能帮助用户迅速发现错误原因。
2.函数工具提示
用户直接输入公式的函数名和参数时,不能看到函数格式和参数的提示,这是公式发生错误的一个重要原因。为此,Excel XP增加了函数工具提示功能。以公式“=SUMIF(A1:A8)”为例,当你输入=SUMIF()”后,公式下方会显示黄色的函数工具提示条,提供了有关函数和参数的语法信息。你只要单击其中的函数或参数名称,就可以打开帮助获得更多信息。
3.用自然语言搜索函数
Excel拥有数百个函数,寻找适用的函数是初级用户面临的难题。为此,Excel XP在“插入函数”对话框中增加了“搜索函数”功能。假如你要完成数据排序任务,可以单击工具栏中的“插入函数”按钮打开对话框,在其中的“搜索函数”框内输入“排序”,然后单击“转到”按钮,对话框下面的“选择函数”框中显示“RANK”等排序函数。
4.监视窗口
如果一个工作表的不同单元格或者多个工作表设置了公式,要想查看其内容和计算结果,必须在不同单元格或工作表中转换。为此,Excel XP增加了一个名为监视窗口的工具。其使用方法是:选中含有公式的待监视单元格,再用鼠标右键单击工具栏选择“监视窗口”。然后单击“监视窗口”中的“添加监视”按钮,被监视的公式及其计算结果就会显示在监视窗口中。
5.公式审核 Excel XP新增了一个“公式审核”工具栏,它提供了几个新的数据审查工具,例如“错误检查”、“追踪引用单元格”和“公式求值”。“错误检查”与语法检查程序类似,它用特定的规则检查公式中存在的问题,可以查找并发现常见错误,你可以在“选项”对话框的“错误检查”选项卡中启用或关闭这些规则。“追踪引用单元格”可以用蓝色箭头等标出公式引用的所有单元格,追踪结束后可以使用“移去单元格追踪箭头”按钮将标记去掉。“公式求值”可以打开一个对话框,用逐步执行方式查看公式计算顺序和结果,能够清楚了解复杂公式的计算过程。
二、Excel 函数快速上手
1.求和函数SUM
语法:SUM(number1,number2,...)。
参数:number1、number2...为1到30个数值(包括逻辑值和文本表达式)、区域或引用,各参数之间必须用逗号加以分隔。
注意:参数中的数字、逻辑值及数字的文本表达式可以参与计算,其中逻辑值被转换为1,文本则被转换为数字。如果参数为数组或引用,只有其中的数字参与计算,数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。
应用实例一:跨表求和
使用SUM函数在同一工作表中求和比较简单,如果需要对不同工作表的多个区域进行求和,可以采用以下方法:选中Excel XP“插入函数”对话框中的函数,“确定”后打开“函数参数”对话框。切换至第一个工作表,鼠标单击“number1”框后选中需要求和的区域。如果同一工作表中的其他区域需要参与计算,可以单击“number2”框,再次选中工作表中要计算的其他区域。上述操作完成后切换至第二个工作表,重复上述操作即可完成输入。“确定”后公式所在单元格将显示计算结果。
应用实例二:SUM函数中的加减混合运算
财务统计需要进行加减混合运算,例如扣除现金流量表中的若干支出项目。按照规定,工作表中的这些项目没有输入负号。这时可以构造“=SUM(B2:B6,C2:C9,-D2,-E2)”这样的公式。其中B2:B6,C2:C9引用是收入,而D2、E2为支出。由于Excel不允许在单元格引用前面加负号,所以应在表示支出的单元格前加负号,这样即可计算出正确结果。即使支出数据所在的单元格连续,也必须用逗号将它们逐个隔开,写成“=SUM(B2:B6,C2:C9,-D2,-D3,D4)”这样的形式。
应用实例三:及格人数统计
假如B1:B50区域存放学生性别,C1:C50单元格存放某班学生的考试成绩,要想统计考试成绩及格的女生人数。可以使用公式“=SUM(IF(B1:B50=″女″,IF(C1:C50>=60,1,0)))”,由于它是一个数组公式,输入结束后必须按住Ctrl+Shift键回车。公式两边会自动添加上大括号,在编辑栏显示为“{=SUM(IF(B1:B50=″女″,IF(C1:C50>=60,1,0)))}”,这是使用数组公式必不可少的步骤。
2.平均值函数AVERAGE
语法:AVERAGE(number1,number2,...)。
参数:number1、number2...是需要计算平均值的1~30个参数。
注意:参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻辑值或空白单元格将被忽略,但单元格中的零则参与计算。如果需要将参数中的零排除在外,则要使用特殊设计的公式,下面的介绍。
应用实例一:跨表计算平均值
标签名为“一班”、“二班”和“三班”的工作表存放各班学生的成绩,则它们的总平均分计算公式为“=AVERAGE(一班!C1:C36,三班!C1:C32,三班!C1:C45)”。式中的引用输入方法与SUM跨表求和时相同。
应用实例二:忽略零的平均值计算公式
假设A1:A200随机存放包括零在内的48个数值,在AVERAGE参数中去掉零引用很麻烦,这种情况可以使用公式“=AVERAGE(IF(A1:A200<>0,A1:A200,″″)”。公式输入结束后按住Ctrl+Shift回车,即可对A1:A200中的数值(不包括零)计算平均值。
3.逻辑函数IF
语法:IF(logical_test,value_if_true,value_if_false)。
参数:logical_test是结果为true(真)或false(假)的数值或表达式;value_if_true是logical_test为true时函数的返回值,如果logical_test为ture并且省略了value_if_true,则返回true。而且value_if_true可以是一个表达式;value_if_false是logical_test为false时函数的返回值。如果logical_test为false并且省略value_if_false,则返回false。Value_if_false也可以是一个表达式。
应用实例一:个人收入调节税计算
假设个人收入调节税的稽征办法是:工资在1000元以下的免征调节税,工资1000元以上至1500元的超过部分按5%的税率征收,1500元以上至2000元的超过部分按8%的税率征收,高于2000元的超过部分按30%的税率征收。
假如工作表的B列存放职工姓名,C列存放工资,选中D列的空白单元格输入公式“=IF(C2<=1000,″″,IF((C2-1000)<=1500,(C2-1000)*0.05,IF(C2-E2<=1500,(C2-1500)*0.08,IF(C2>2000,(C2-2000)*0.3))))”,回车后即可计算出C2工资应缴纳的收入调节税。
公式中的IF语句是逐次计算的,如果第一个逻辑判断C2<=1000成立,则公式所在单元格被填入空格;如果第一个逻辑判断式不成立,则计算第二个IF语句;直至计算结束。如果税率征收标准发生了变化,只须改变逻辑和计算式中的值,如1000、1500和2000等即可。
应用实例二:消除无意义的零
用SUM函数计算工资总额等问题时,若引用的全部参数均为空白单元格,公式仍然会计算出一个“0”。这样打印出来的报表不仅很不美观。为此可将计算公式设计为“=IF(SUM(A1:B1,D2:E8)<>0,SUM(A1:B1,D2:E8),″″)”,即是当SUM(A1:B1,D2:E8)计算结果不等于零时,公式所在单元格显示SUM(A1:B1,D2:E8)的计算结果,否则显示一个空格。
上面这个问题在财务计算中依然存在,如公式“=A1-A6-A9”有可能为零或显示负数。为了避免打印零或负数,可以设计公式“=IF(A2-A6-A9=0,″″,IF(A2-A6-A9<0,RMB(A2-A6-A9),A2-A6-A9))”。当A2-A6-A9=0时在单元格中填写零,否则进行下一个逻辑判断。如果A2-A6-A9<0则在结果前添加一个“¥”符号,否则进行正常运算直至结束。
应用实例三:多条件求和
假设C1:C460区域内存放着职工的职称,D1:D460区域存放着职工的性别。如果要统计具有高级职称的男性职工总数,可以使用公式“=SUM(IF(C1:C460=″高级″,IF(D1:D460=″男″,1,0)))”。这是一个数组公式,输入结束后按住Ctrl+Shift回车即可计算出结果。
4.快捷方式或链接创建函数HYPERLINK
语法:HYPERLINK(link_location,friendly_name)。
参数:link_location是文件的路径和文件名,它可以指向文档中的某个具体位置。如Excel工作表中的特定单元格或区域,或某个Word文档中的书签,也可以指向硬盘中的文件或是Internet或Intranet的URL。Friendly_name为单元格中显示的链接文字或数字,它用蓝色显示并带有下划线。如果省略了friendly_name,单元格就将link_location显示为链接。
应用实例:管理文档或网站地址
如果你拥有大量文档或收集了许多网站地址,管理起来一定有困难。如果能够将其中的文档名、URL等与文档等对象链接起来,不仅管理方便,还可以直接打开文档或访问站点。具体操作方法是:首先根据文档类型建立管理工作表,其中的文件名或网站名必须使用以下方法输入:选中一个空白单元格,单击Excel XP工具栏中的“粘贴函数”按钮,打开“粘贴函数”对话框,在“函数分类”框下选中“查找与引用”,然后在“函数名”框内找到HYPERLINK函数。单击对话框中的“确定”按钮,弹出“HYPERLINK”函数向导。在“link_location”框中输入文件的完整路径和名称(包括扩展名),如“C:\my documents\IT网站集锦.doc”,然后在“friendly_name”框中输入文件名(如“IT网站集锦”)。
确认输入无误单击“确定”按钮,所选单元格即出现带下划线的紫色文件名“IT网站集锦”。
此后你就可以从工作表中打开文档,方法是:打开工作表,在其中找到需要打开的文件。箭头光标指向文件名会变成手形,若停留片刻则会显示该文件的完整路径和名称,单击则会调用关联程序将文件打开。
如果你收集了许多网站的URL,还可以用此法建立一个大型“收藏夹”。既可以用Excel的强大功能进行管理,又可以从工作表中直接访问Web站点。
5.计数函数COUNT
语法:COUNT(value1,value2,...)。
参数:value1,value2...是包含或引用各类数据的1~30个参数。
注意:COUNT函数计数时数字、日期或文本表示的数字会参与计数,错误值或其他无法转换成数字的文字被忽略。如果参数是一个数组或引用,那么只有数组或引用中的数字参与计数;其中的空白单元格、逻辑值、文字或错误值均被忽略。
应用实例:及格率统计
假如C1:G42存放着42名学生的考试成绩,在一个空白单元格内输入公式“=COUNTIF(C1:C42,″>=60″)/COUNTA(C1:C42)”回车,即可计算出该列成绩的及格率(即分数为60及以上的人数占总人数的百分比)。
6.最大值函数MAX、最小值函数MIN
语法:MAX(number1,number2,...),MIN(number1,number2,...)。
参数:number1,number2...是需要找出最大值(最小值)的1至30个数值、数组或引用。
注意:函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式,如果参数是不能转换为数字的内容将导致错误。如果参数为数组或引用,则只有数组或引用中的数字参与计算,空白单元格、逻辑值或文本则被忽略。
应用实例:查询最高分(最低分)
假如C1:G42存放着42名学生的考试成绩,则选中一个空白单元格,在编辑栏输入公式“=MAX(C1:C42)”,回车后即可计算出其中的最高分是多少。
如果将上述公式中的函数名改为MIN,其他不变,就可以计算出C1:G42区域中的最低分。
7.条件求和函数SUMIF
语法:SUMIF(range,criteria,sum_range)。
参数:range是用于条件判断的单元格区域,criteria是由数字、逻辑表达式等组成的判定条件,sum_range为需要求和的单元格、区域或引用。
应用实例:及格平均分统计
假如A1:A36单元格存放某班学生的考试成绩,若要计算及格学生的平均分,可以使用公式“=SUMIF(A1:A36,″>=60″,A1:A36)/COUNTIF(A1:A36,″>=60″)。公式中的“=SUMIF(A1:A36,″>=60″,A1:A36)”计算及格学生的总分,式中的“A1:A36”为提供逻辑判断依据的单元格引用,“>=60”为判断条件,不符合条件的数据不参与求和,A1:A36则是逻辑判断和求和的对象。公式中的COUNTIF(A1:A36,″>=60″)用来统计及格学生的人数。
8.贷款偿还计算函数PMT
语法:PMT(rate,nper,pv,fv,type).
参数:如今贷款买房子或车子的人越来越多,计算某一贷款的月偿还金额是考虑贷款的重要依据,Excel XP提供的PMT函数是完成这一任务的好工具。语法中的rate是贷款利率;nper为贷款偿还期限;pv是贷款本金;fv为最后一次付款后剩余的贷款金额,如果省略fv,则认为它的值为零;type为0或1,用来指定付款时间是在月初还是月末。如果省略type,则假设其值为零。
应用实例:购房还款金额
假如你为购房贷款十万元,如果年利率为7%,每月末还款。采用十年还清方式时,月还款额计算公式为“=PMT(7%/12,120,-100000)”。其结果为¥-1,161.08,就是你每月须偿还贷款1161.08元。
9.样本的标准偏差函数STDEV
语法:STDEV(number1,number2,...)。
参数:number1,number2,...为对应于总体样本的1到30个参数,它们可以是数值、引用或数组。
注意:STDEV函数的参数是总体中的样本,并忽略参数中的逻辑值(true或false)和文本。如果需要用全部数据计算标准偏差,则应使用STDEVP函数。如果参数中的逻辑值和文本不能忽略,请使用STDEVA函数。
应用实例一:成绩离散度估计
假设某班共有36名学生参加考试,随机抽取的五个分数为A1=78、A2=45、A3=90、A4=12和A5=85。如果要估算本次考试成绩相对平均分的离散程度,即学生的考试成绩偏离平均分的多少,可以使用公式“=STDEV(A1:A5)”。其计算结果为33.00757489,标准偏差的数值越大成绩越分散。
应用实例二:质量波动估计
质量控制等场合也能用到STDEV函数,如从一批钢丝绳中随机抽出若干进行试验,分别测出它们的抗拉强度。根据STDEV函数的计算结果即可判断钢丝绳的抗拉强度是否分散,如果计算的标准偏差比较小,说明抗拉强度的一致性好,质量比较稳定。反之说明钢丝绳的质量波动较大,抗拉强度不够一致。
10.排序函数RANK
语法:RANK(number,ref,order)。
参数:number是需要计算其排位的一个数字;ref是包含一组数字的数组或引用(其中的非数值型参数将被忽略);order是用来说明排序方式的数字(如果order为零或省略,则以降序方式给出结果,反之按升序方式)。
应用实例:产值排序
假如E2、E3、E4单元格存放一季度的总产值。计算各车间产值排名的方法是:在F2单元格内输入公式“=RANK(E2,$E$2:$E$4)”,敲回车即可计算出铸造车间的产值排名是2。再将F2中的公式复制到剪贴板,选中F3、F4单元格按Ctrl+V,就能计算出其余两个车间的产值排名3和1。如果B1单元格中输入的公式为“=RANK(E2,$E$2:$E$4,1)”,则计算出的序数按升序方式排列,即2、1和3。
需要注意的是:相同数值用RANK函数计算得到的序数(名次)相同,但会导致后续数字的序数空缺。假如上例中F2单元格存放的数值与F3相同,则按本法计算出的排名分别是3、3和1(降序时)。即176.7出现两次时,铸造和维修车间的产值排名均为3,后续金工车间的排名就是1(没有2)。
11.四舍五入函数
语法:ROUND(number,num_digits)。
参数:number是需要四舍五入的数字;num_digits为指定的位数,number将按此位数进行四舍五入。
注意:如果num_digits大于0,则四舍五入到指定的小数位;如果 num_digits 等于 0,则四舍五入到最接近的整数;如果 num_digits 小于 0
,则在小数点左侧按指定位数四舍五入。
应用实例:消除计算误差
假设Excel工作表中有D2=356.68、E2=128.12,需要将D2与E2之和乘以0.1,将计算结果四舍五入取整数,再将这个结果乘以1.36(取两位小数)得到最终结果。
一般用户的做法是选中某个单元格(如F2),使用“单元格”命令将它的小数位数设为零,然后在其中输入公式“F2=(D1+E1)*0.1”。再将G2单元格的小数位数设成两位,最后把F2*1.36的结果存入其中就可以了。从表面上看,上述方法没有什么问题。因为(D1+E1)*0.1在F2单元格显示48(注意:是显示48),如果F2单元格的小数位数为零,(D1+E1)*0.1经四舍五入后的结果就是48。接下去却出了问题,因为F2*1.36的计算结果是65.90,根本不是48*1.36的正确结果65.28,其中65.90是(D2+E2)*0.1未经四舍五入直接乘以1.36的结果。
以上计算结果说明:“单元格格式”、“数字”选项卡设置的“小数位数”,只能将单元格数值的显示结果进行四舍五入,并不能对所存放的数值进行四舍五入。换句话说,单元格数值的显示结果与实际存放结果并不完全一致,如果不注意这个问题,计算工资等敏感数据就会出现错误。例如在上例中,F2单元格内的数值虽然显示为48,但实际存放的却是48.45,自然得出了48.45*1.36=65.90的结果(按计算要求应为65.28)。
要解决这个问题并不难,你只须在G2单元格内输入公式“=(ROUND((D2+E2)*0.1,0))*1.36”,就可以按要求计算出正确结果65.28。式中的ROUND函数按指定位数对“(D2+E2)*0.1”进行四舍五入,函数中的参数0将“(D2+E2)*0.1”四舍五入到最接近的整数。
12.条件计数函数COUNTIF
语法:COUNTIF(range,criteria)。
参数:range为需要统计的符合条件的单元格区域;criteria为参与计算的单元格条件,其形式可以为数字、表达式或文本(如36、″>160″和″男″等)。条件中的数字可以直接写入,表达式和文本必须加引号。
应用实例:男女职工人数统计
假设 A1:A58区域内存放着员工的性别,则公式“=COUNTIF(A1:A58,″女″)”统计其中的女职工数量,“=COUNTIF(A1:A58,″男″)”统计其中的男职工数量。
COUNTIF函数还可以统计优秀或及格成绩的数量,假如C1:G42存放着42名学生的考试成绩,则公式“=COUNTIF(C2:G2,″>=85″)”可以计算出其中高于等于85分的成绩数目。如将公式改为“=COUNTIF(C2:G2,″>=60″)”,则可以计算出及格分数的个数。
13.百分排位预测函数PERCENTILE
语法:PERCENTILE(array,k)。
参数:array为定义相对位置的数值数组或数值区域,k为数组中需要得到其百分排位的值。
注意:如果array所在单元格为空白或数据个数超过8191,则返回#NUM!错误。如果k<0或k>1,则返回#NUM!错误。如果k不是1/(n-1)的倍数,该函数使用插值法确定其百分排位。
应用实例:利润排行预测
假设C1:C60区域存放着几十个公司的利润总额。如果你想知道某公司的利润达到多少,才能进入排名前10%的行列,可以使用公式“=PERCENTILE(C1:C60,0.9)”。如果计算结果为9867万元,说明利润排名要想进入前10%,则该公司的利润至少应当达到9867万元。
14.数值探测函数ISNUMBER
语法:ISNUMBER(value)。
参数:如果value为数值时函数返回ture(真),否则返回false(假)。
应用实例一:无错误除法公式 如果工作表为C1设计了公式“=A1/B1”,一旦B1单元格没有输入除数,就会在C1中显示错误信息“#DIV/0!”。这不仅看起来很不美观,一旦作为报表打印还可能引起误会。为避免上面提到的问题出现,可将C1单元格中的公式设计成“=IF(ISNUMBER(B1),A1/B1,″″)”。式中的ISNUMBER函数对B1单元格进行探测,当B1被填入数值时返回true(真),反之返回false(假)。为真时IF函数执行A1/B1的运算,为假时在C1单元格中填入空格。
应用实例二:消除无意义的零
使用SUM函数计算工资总额时,若引用的单元格区域没有数据,Excel仍然会计算出一个结果“0”。这样打印出来的报表不符合财务规定,为此可将公式设计成“=IF(ISNUMBER(A1:B1),SUM(A1:B1),″″)”。
式中ISNUMBER函数测试SUM 函数所引用的单元格区域是否全部为空,当不为空时返回true(真),反之返回fales(假)。为真时IF函数执行SUM(A1:B1),为假时在存放计算结果的F1单元格中填入空格,这样即可避免在F1单元格中出现“0”。
15.零存整取收益函数PV
语法:PV(rate,nper,pmt,fv,type)。
参数:rate为存款利率;nper为总的存款时间,对于三年期零存整取存款来说共有3*12=36个月;pmt为每月存款金额,如果忽略pmt则公式必须包含参数fv;fv为最后一次存款后希望得到的现金总额,如果省略了fv则公式中必须包含pmt参数;type为数字0或1,它指定存款时间是月初还是月末。
应用实例:零存整取收益函数PV
假如你每月初向银行存入现金500元,如果年利2.15%(按月计息,即月息2.15%/12)。如果你想知道5年后的存款总额是多少,可以使用公式“=FV(2.15%/12,60,-500,0,1)”计算,其结果为¥31,698.67。
式中的2.15%/12为月息;60为总的付款时间,在按月储蓄的情况下为储蓄月份的总和;-500为每月支出的储蓄金额(-表示支出);0表示储蓄开始时账户上的金额,如果账户上没有一分钱,则为0否则应该输入基础金额;1表示存款时间是月初还是月末,1为月初0或忽略为月末。
16.内部平均值函数TRIMMEAN
语法:TRIMMEAN(array,percent)。
参数:array为需要去掉若干数据然后求平均值的数组或数据区域;percent为计算时需要除去的数据的比例,如果 percent = 0.2,说明在20个数据中除去 4 个,即头部除去 2 个尾部除去 2 个。如果percent=0.1,则30个数据点的10%等于3,函数TRIMMEAN将在数据首尾各去掉一个数据。
应用实例:评比打分统计
歌唱比赛采用打分的方法进行评价,为了防止个别人的极端行为,一般计算平均分数要去掉若干最高分和最低分。假如B1:B10区域存放某位歌手的比赛得分,则去掉一个最高分和一个最低分后的平均分计算公式为“=TRIMMEAN(B1:B10,0.2)”。公式中的0.2表示10个数据中去掉2个(10×0.2),即一个最高分和一个最低分。
17.日期年份函数YEAR
语法:YEAR(serial_number)。
参数:serial_number为待计算年份的日期。
应用实例:“虚工龄”计算
所谓“虚工龄”就是从参加工作算起,每过一年就增加一年工龄,利用YEAR函数计算工龄的公式是“=YEAR(A1)-YEAR(B1)”。公式中的A1和B1分别存放工龄的起止日期,YEAR(A1)和YEAR(B1)分别计算出两个日期对应的年份,相减后得出虚工龄。
18.起止天数函数DAYS360
语法:DAYS360(start_date,end_date,method)。
参数:start_date和end_date是用于计算期间天数的起止日期,可以使用带引号的文本串(如"1998/01/30")、系列数和嵌套函数的结果。如果start_date在end_date之后,则DAYS360将返回一个负数。
19.取整函数TRUNC
语法:TRUNC(number,num_digits)。
参数:number是需要截去小数部分的数字,num_digits则指定保留到几位小数。
应用实例:“实工龄”计算
实际工作满一年算一年的工龄称为“实工龄”,如1998年6月1日至2001年12月31日的工龄为3年。计算“实工龄”的公式是“= TRUNC((DAYS360(″1998/6/1″,″2001/12/31″))/360,0)”,公式中的DAYS360(″1998/6/1″,″2001/12/31″)计算两个日期相差的天数,除以360后算出日期相差的年份(小数)。最后TRUNC函数将(DAYS360(A1,B1)/360的计算结果截去小数部分,从而得出“实工龄”。如果计算结果需要保留一位小数,只须将公式修改为“= TRUNC((DAYS360(″1998/6/1″,″2001/12/31″))/360,1)”即可。
如果你要计算参加工作到系统当前时间的实工龄,可以将公式修改为“= TRUNC((DAYS360(″1998/6/1″,NOW()))/360,0)”。其中NOW()函数返回当前的系统日期和时间。
20.字符提取函数MID
语法:MID(text,start_num,num_chars)或MIDB(text,start_num,num_bytes)。
参数:text是含有要待提取字符的文本,start_num是要提取的第一个字符的位置(其中第一个字符的start_num为1以此类推),num_chars 指定MID从文本中提取的字符个数,Num_bytes指定MIDB从文本中提取的字符个数(按字节计算)。
应用实例:姓氏提取和统计
假如工作表C列存放着职工的姓名,如果想知道某一姓氏的职工人数,可以采用以下方法。首先使用公式“=MID(C1,1,1)”或“=MIDB(C1,1,2),从C1中提取出职工的姓氏,采用拖动或复制的方法即可提取其他单元格中的职工姓氏。然后使用公式“=COUNTIF(D1:D58,″张″)”统计出姓张的职工数量,随后改变公式中的姓氏就可以统计出其他姓氏职工的数量。
三、Excel XP数据库速成
Excel XP具有强大的数据库功能,对包含大量数据的表格进行排序、筛选等处理易如反掌,下面介绍常用的数据库操作。
1.数据排序
Excel XP第一行的单元格具有特殊作用,其中的文字相当于数据库字段名,可作为关键字参与排序、记录单等各种操作。
(1)文本排序选举等场合需要按姓氏笔划为文本排序,Excel提供了比较好的解决办法。如果你要将图1数据表按车间名称的笔划排序,可以使用以下方法:
选中排序关键字所在列(或行)的首个单元格,单击Excel“数据”菜单下的“排序”命令打开对话框,再单击其中的“选项”按钮。选中“排序选项”对话框“方法”下的“笔画排序”,再根据数据排列方向选择“按行排序”或“按列排序”,“确定”后回到“排序”对话框。如果你的数据带有标题行(如“单位”之类),则应选中“有标题行”(反之不选),然后打开“主要关键字”下拉列表,选择其中的“单位”,
选中排序方式(“升序”或“降序”)后“确定”,表中的所有数据就会据此重新排列。此法稍加变通即可用于“第一名”、“第二名”等文本的排序,请大家自行摸索。
(2)自定义排序
如果你要求Excel按照“金工车间”、“铸造车间”和“维修车间”的特定顺序重排工作表数据,前面介绍的几种方法就无能为力了。这类问题可以用定义排序规则的方法解决:首先单击Excel“工具”菜单下的“选项”命令,打开“选项”对话框中的“自定义序列”选项卡。选中左边“自定义序列”下的“新序列”,光标就会在右边的“输入序列”框内闪动,你就可以输入“金工车间”、“铸造车间”等自定义序列了,输入的每个序列之间要用英文逗号分隔,或者每输入一个序列就敲回车。如果序列已经存在于工作表,可以选中序列所在的单元格区域单击“导入”,这些序列就会被自动进入“输入序列”框。无论采用以上那种方法,单击“添加”按钮即可将序列放入“自定义序列”中备用。
使用排序规则排序的具体方法与笔划排序很相似,只是你要打开“排序选项”对话框中的“自定义排序次序”下拉列表,选中前面定义的排序规则,其他选项保持不动。回到“排序”对话框后根据需要选择“升序”或“降序”,“确定”后即可完成数据的自定义排序。
2.数据检索
如果Excel XP工作表中的数据字段很多时,查看一条或几条记录需要左右滚动屏幕,此时可以采用以下方法检索并查看:单击“数据”菜单下的“记录单”命令打开对话框(图2)。
再次单击其中的“条件”按钮。对话框中的字段就会变成空白等待输入,并且“条件”按钮变为“表单”按钮。在对话框的相应字段位置输入条件,如果在“序号”字段框内输入12回车,则序号为12的数据就会显示在对话框中。当然,你输入的检索条件可以使用>、<、>=、<=、<>逻辑符号。例如单击“条件”按钮后在“总分”框内输入“>170”,则表示检索总分大于170的记录,回车后第一条大于170的数据就会显示在对话框中。继续单击“上一条”或“下一条”按钮,可以查看检索出来的其他数据。
3.数据筛选
上面介绍的方法可以检索满足某一条件的记录,但是检索条件比较复杂时(如检索总分大于170和小于120的纪录),这种检索方法就无能为力了。此时可以使用Excel XP提供的自定义筛选功能,操作方法是:单击“数据”、“筛选”子菜单下的“自动筛选”命令,每个字段名的右侧会出现一个下拉按钮。根据你要筛选的字段单击下拉按钮,在下拉菜单中选择“自定义”命令打开对话框(图3)。
单击第一行左侧的下拉按钮,选择其中的“大于”,然后在其右边输入170;按相同方法在第二行中选择“小于”,并在右边输入120,选中两行中间的“或”后单击“确定”按钮,工作表会自动显示所有符合筛选条件的记录。筛选出来的记录可以执行复制等操作,如果你要清除筛选结果,只要将“数据”、“筛选”子菜单下的“自动筛选”前的选中标记去掉即可。
4.数据汇总
小计、合计是应用非常广泛的数据统计方式,Excel XP分类汇总是此类操作的高手。你只要将光标放在工作表的任一单元格中,单击“数据”菜单下的“分类汇总”命令打开对话框。如果要对某个班级的数学、语文成绩进行汇总,则应在“分类字段”中选中“班级”,在“汇总方式”中选择“求和”,在“选择汇总项”中选中“数学”和“语文”。如果选中“汇总结果显示在数据下放”,单击“确定”按钮后汇总结果会显示在工作表数据的下面,反之则会显示在工作表数据上方。如果你想清除工作表中的分类汇总结果,只要打开“分类汇总”对话框,单击其中的“全部删除”按钮就可以了。
5.数据透视表
数据透视表是Excel XP数据库功能的重要组成部分,它能够非常灵活的汇总数据。如果你要对一个学生成绩工作表进行统计,计算所有学生的数学成绩总分,采用数据透视表可以快速完成任务。
单击“数据”菜单下的“数据透视表和数据透视图”命令打开向导,首先选择数据源类型和创建的报表类型,完成后单击“下一步”按钮。这时要选择建立数据透视表的数据区域,你只要用鼠标在工作表中拖动即可将引用的数据区域放入对话框。接着选择数据透视表的位置,可以选择“新建工作表”或“现有工作表”。单击“完成”按钮,将“数据透视表字段列表”中的“数学”拖入工作表的指定位置,Excel XP会立刻计算出所有学生的数学成绩合计。
6.数据查找
数据筛选的对象是工作表中的某条记录,这里介绍的查找对象则是单元格中的数据,Excel XP可以执行的查找有以下几种类型:
(1)普通查找
它利用编辑菜单中的查找命令查找特定的数据,操作方法如下:单击“编辑”菜单中的“查找”命令,打开“查找”对话框。在“查找内容”后输入要查找的内容,单击“查找下一个”按钮,光标就会停留在首先发现数据的单元格中。如果没有发现要查找的数据,Excel XP会弹出对话框告知用户。
(2)特殊查找
特殊查找可以找出包含相同内容(如文本)或格式的所有单元格,或者存在内容差异的单元格,其操作步骤如下:如果你要找出工作表中含有文本的所有单元格,请选中含有文本任意一个单元格,然后单击“编辑”菜单中的“定位”命令。再单击对话框中的“定位条件”按钮,选中“定位条件”对话框中的“常量”,再选中复选项中的“文本”。单击“确定”按钮后,所有符合条件的所有单元格就会选中。
如果你要找出数据行或列中与活动单元格不匹配的部分,可以打开“定位条件”对话框。如果需要找出特定行中与活动单元格不同的单元格,可以选择“行内容差异单元格”;若要找出列中与活动单元格不同的单元格,则应选中“列内容差异单元格”,单击“确定”按钮即可完成查找。
四、Excel XP网络集成
1.Web数据共享
Excel XP可以从网页中导入数据进行分析。你只须选择“数据”、“导入外部数据”子菜单下的“导入数据”,在“选取数据源”对话框中找到Web页,单击“打开”按钮就可以将其导入Excel。当你进行过一次数据导入后,用鼠标右键单击打开的网页,可以看到快捷菜单中增添了“导出到Microsoft Excel”命令。如果希望从远程数据源导入数据,则应使用“数据连接向导”,它可以从Microsoft SQL Server、开放式数据库连接(ODBC)以及联机分析处理(OLAP)等数据源中查找并导入数据。
此外,Excel XP可以直接粘贴Word XP表格,从而共享其中的数据,进一步扩大了两者协同工作的范围。
2.E-mail功能
Excel的“常用”工具栏带有一个“电子邮件”按钮。单击它可以在当前窗口显示一个邮件发送工具栏,其界面与Outlook Express很相似,你只要输入收件人的E-mail地址和主题等,就可以将文件以附件或正文的形式发送出去。作为正文发送时,只须填写对方的E-mail地址,并在“主题”栏内进行简单介绍,然后单击“发送该工作表”按钮(如果你选中了工作表中的部分区域,该按钮变成“发送所选区域”)即可。当其他用户收到邮件后,可以直接对正文进行编辑修改,处理完毕后用同样方法将文件发给你。单击“附加文件按钮”,可以打开“插入附件”对话框,像Outlook Express那样插入并发送附件。
3.网页保存和预览
Excel XP“文件”菜单下有一条“保存为Web页”命令,用户可以选择将工作簿或当前工作表保存为Web页(HTML、XML),还可以在工作表中添加交互功能。
Excel XP内建了网页浏览功能,如果你想将工作表发布到网上,只要单击“文件”菜单下的“Web页预览”命令,Excel XP就会自动调用IE打开工作表。
类别:office园地 | |添加到搜藏 |分享到i贴吧 | 浏览(12) |评论 (0)
上一篇:一句话,让你豁然开朗 下一篇:加密Excel文件
EXCEL常用公式222222
EXCEL常用公式
EXCEL常用公式23
常用excel电子表格公式
EXCEL常用公式
excel常用电子表格公式
EXCEL常用公式0
Excel中常用函数公式
EXCEL中常用公式集合
excel常用公式工业会计常用公式(二)
EXCEL常用公式以及小技巧
常用电子表格公式 Excel操作技巧教程
EXCEL中常用公式集合 ,excel 输入数据的方法
excel公式
excel公式
excel公式
(一)EXCEL中常用 公式集合 ,excel 输入数据的方法技巧总结
EXCEL中常用公式集合 ,excel 输入数据的方法技巧总结
(一)EXCEL中常用公式集合 ,excel 输入数据的方法技巧总结
(一)EXCEL中常用公式集合 ,excel 输入数据的方法技巧总结1
常用公式
常用公式
excel公式应用大全
excel 公式应用大全