下载

0下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 excel表格函数

excel表格函数.doc

excel表格函数

当时间把相爱写成相爱过时
2017-10-19 0人阅读 举报 0 0 0 暂无简介

简介:本文档为《excel表格函数doc》,可适用于IT/计算机领域

excel表格函数EXCEL中常见函数应用与条件功能函数:LARGE与SMALL。LARGE返回数据集里第k个最大值。使用此函数可以根据相对标准来选择数值。语法LARGE(array,k)Array:为需要从中选择第k个最大值的数组或数据区域。K:为返回值在数组或数据单元格区域里的位置(从大到小排)。说明、如果数组为空函数LARGE返回错误值#NUM!。、如果k<或k大于数据点的数目函数LARGE返回错误值#NUM!。、如果区域中数据点的数目为n则函数LARGE(array,)返回最大值函数LARGE(array,n)返回最小值。SMALL返回数据集中第k个最小值。使用此函数可以返回数据集中特定位置上的数值。语法SMALL(array,k)Array:为需要找到第k个最小值的数组或数字型数据区域。K:为返回的数据在数组或数据区域里的位置(从小到大)。说明、如果array为空函数SMALL返回错误值#NUM!。、如果k<或k超过了数据点数函数SMALL返回错误值#NUM!。、如果n为数组中的数据点数则SMALL(array,)等于最小数值SMALL(array,n)等于最大数值。实际应用的实例:求学生成绩中的前名的成绩和后名成绩。文档说明:A:A存放位学生的成绩在B:B中录入在C中设定公式=LARGE(A:A,B)(第一名的成绩)然后填充到C和C格在D中设定公式=SMALL(A:A,B)(倒数第一名的成绩)然后填充到D和D格。变通与扩展:、当寻找到成绩后可用Match函数寻找成绩所在的位置。、当找到位置后还可以用OFFSET偏移找到其它相应的内容如姓名。VLOOKUP(lookupvalue,tablearray,colindexnum,rangelookup)假如,你要在表A列里填个姓名,B列就自动查找表里的这个姓名对应的身份证号(表,是人事资料),可以这样表表ABABC姓名身份证号姓名性别身份证号张三张三X在表的B输入=VLookUP(A,表!A:F,,)'在表的A:F范转内查找第一个姓名为张三的,再返回第三列的值""是精确查找,SORRY!还有其他参数请自己查帮助吧,这里只我怎么用而已^^LOOKUP从单行或单列区域或者从一个数组(数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式数组常量是用作参数的一组常量。)返回值。LOOKUP函数具有两种语法形式:(向量LOOKUP的向量形式在单行区域或单列区域(称为“向量”)中查找值然后返回第二个单行区域或单列区域中相同位置的值。当要查询的值列表较大或者值可能会随时间而改变时使用该向量形式。数组LOOKUP的数组形式在数组的第一行或第一列中查找指定的值然后返回数组的最后一行或最后一列中相同位置的值。当要查询的值列表较小或者值在一段时间内保持不变时使用该数组形式。注释对于详细的测试或七种以上条件的测试还可以使用LOOKUP函数作为IF函数的替代函数。请参阅数组形式的示例。)向量形式向量是只含一行或一列的区域。LOOKUP的向量形式在单行区域或单列区域(称为“向量”)中查找值然后返回第二个单行区域或单列区域中相同位置的值。当要指定的区域包含要匹配的值时请使用LOOKUP函数的这种形式。LOOKUP的另一种形式自动在第一行或第一列中查找。LOOKUP(lookupvalue,lookupvector,resultvector)LookupvalueLOOKUP在第一个向量中搜索的值。Lookupvalue可以是数字、文本、逻辑值、名称或对值的引用。Lookupvector只包含一行或一列的区域。lookupvector中的值可以是文本、数字或逻辑值。要点lookupvector中的值必须以升序顺序放置:,,,,,,AZFALSE,TRUE。否则LOOKUP可能无法提供正确的值。大写文本和小写文本是等同的。Resultvector只包含一行或一列的区域。它必须与lookupvector大小相同。评论如果LOOKUP找不到lookupvalue则它与lookupvector中小于或等于lookupvalue的最大值匹配。如果lookupvalue小于lookupvector中的最小值则LOOKUP会提供#NA错误值。示例如果将示例复制到一个空白工作表中示例将更易于理解。方法创建一个空白工作簿或工作表。在“帮助”主题中选择示例。请不要选择行或列标题。从“帮助”中选择示例按CtrlC。在工作表中选择单元格A然后按CtrlV。要在查看结果和查看返回结果的公式之间切换请按Ctrl`(重音符)或者在“工具”菜单上指向“公式审核”然后单击“公式审核模式”。AB频率颜色红色橙色黄色绿色蓝色公式说明(结果)=LOOKUP(,A:A,B:B)在列A中查找然后返回列B中同一行内的值(橙色)=LOOKUP(,A:A,B:B)在列A中查找与接近它的最小值()匹配然后返回列B中同一行内的值(橙色)=LOOKUP(,A:A,B:B)在列A中查找与接近它的最小值()匹配然后返回列B中同一行内的值(蓝色)=LOOKUP(,A:A,B:B)在列A中查找并返回错误因为小于lookupvectorA:A中的最小值(#NA)数组形式LOOKUP的数组形式在数组(数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式数组常量是用作参数的一组常量。)的第一行或第一列中查找指定的值并返回数组最后一行或最后一列内同一位置的值。当要匹配的值位于数组的第一行或第一列中时使用LOOKUP的这种形式。当要指定列或行的位置时使用LOOKUP的另一种形式。提示一般而言最好使用HLOOKUP或VLOOKUP函数而不是LOOKUP的数组形式。LOOKUP的这种形式是为了与其他电子表格程序兼容而提供的。LOOKUP(lookupvalue,array)LookupvalueLOOKUP在数组中搜索的值。Lookupvalue可以是数字、文本、逻辑值、名称或对值的引用。如果LOOKUP找不到lookupvalue它会使用数组中小于或等于lookupvalue的最大值。如果lookupvalue小于第一行或第一列中的最小值(取决于数组维度)LOOKUP会返回#NA错误值。数组包含要与lookupvalue进行比较的文本、数字或逻辑值的单元格区域。LOOKUP的数组形式与HLOOKUP和VLOOKUP函数非常相似。区别在于:HLOOKUP在第一行中搜索lookupvalueVLOOKUP在第一列中搜索而LOOKUP根据数组维度进行搜索。如果数组包含宽度比高度大的区域(列数多于行数)LOOKUP会在第一行中搜索lookupvalue。如果数组是正方的或者高度大于宽度(行数多于列数)则LOOKUP在第一列中进行搜索。使用HLOOKUP和VLOOKUP可以通过索引以向下或遍历的方式搜索但是LOOKUP始终选择行或列中的最后一个值。要点数组中的值必须以升序顺序放置:,,,,,,AZFALSE,TRUE。否则LOOKUP无法提供正确的值。大写文本和小写文本是等同的。示例如果将示例复制到一个空白工作表中示例将更易于理解。方法创建一个空白工作簿或工作表。在“帮助”主题中选择示例。请不要选择行或列标题。从“帮助”中选择示例按CtrlC。在工作表中选择单元格A然后按CtrlV。要在查看结果和查看返回结果的公式之间切换请按Ctrl`(重音符)或者在“工具”菜单上指向“公式审核”然后单击“公式审核模式”。AB公式说明(结果)=LOOKUP("C",{"a","b","c","d",,,})在数组的第一行中查找“C”查找小于或等于它(“c”)的最大值然后返回最后一行中同一列内的值()=LOOKUP("bump",{"a","b","c",})在数组的第一行中查找“bump”查找小于或等于它(“b”)的最大值然后返回最后一列中同一行内的值()示例如果将示例复制到一个空白工作表中示例将更易于理解。方法创建一个空白工作簿或工作表。在“帮助”主题中选择示例。请不要选择行或列标题。从“帮助”中选择示例按CtrlC。在工作表中选择单元格A然后按CtrlV。要在查看结果和查看返回结果的公式之间切换请按Ctrl`(重音符)或者在“工具”菜单上指向“公式审核”然后单击“公式审核模式”。下面的示例使用一个数字数组为测试分数指定字母等级。A分数公式说明(结果)=LOOKUP(A,{,,,,},{"F","D","C","B","A"})在数组的第一行中的A()中查找值查找小于或等于它()的最大值然后返回数组最后一行中同一列内的值(F)=LOOKUP(A,{,,,,},{"F","D","C","B","A"})在数组的第一行中的A()中查找值查找小于或等于它()的最大值然后返回数组最后一行中同一列内的值(A)=LOOKUP(A,{,,,,},{"F","D","C","B","A"})在数组的第一行中的A()中查找值查找小于或等于它()的最大值然后返回数组最后一行中同一列内的值(C)=LOOKUP(A,{,,,,,,,,,,,,},{"F","D","D","D","C","C","C","B","B","B","A","A","A"})在数组的第一行中的A()中查找值查找小于或等于它()的最大值然后返回数组最后一行中同一列内的值(F)=LOOKUP(A,{,,,,,,,,,,,,},{"F","D","D","D","C","C","C","B","B","B","A","A","A"})在数组的第一行中的A()中查找值查找小于或等于它()的最大值然后返回最后一行中同一列内的值(A)=LOOKUP(A,{,,,,,,,,,,,,},{"F","D","D","D","C","C","C","B","B","B","A","A","A"})在数组的第一行中的A()中查找值查找小于或等于它()的最大值然后返回最后一行中同一列内的值(C)注意:VLookUP要求查找的目标必须在查找范围的第一列(那么这个例子表的A列就是要查的姓名的列,如果B列才是姓名,那就要改成B:F了),返回值也是以设定范围内第一列为,所以本例的身份证号是列还得注意的就是要返回的内容必须在查找范围的右边,按本例来说,就是身份证号不能在姓名的前面了,这函数是有点局限性,^O^,但我还是觉得不错的一、条件求和。、单条件求和:统计C公司施工的工程总建筑面积并将结果放在E单元格中我们只要在E单元格中输入公式“=SUMIF(D:D,"C公司",E:E)”即完成这一统计。友情提醒:如果对EXCEL的函数不太熟悉在单元格中直接输入公式有困难我们可以用“插入函数”命令(或直接按工具栏上的“粘贴函数”命令按钮)选中你需要的函数后按其提示操作即可完成公式的输入。、多条件求和:统计C公司施工的质量等级为“合格”的工程总建筑面积并将结果放在E单元格中我们用“条件求和”功能来实现:选“工具向导条件求和”命令(若没有此命令选项可以用“加载宏”的方式来增加这一命令选项)在弹出的对话框中按右下带“―”号的按钮(此时对话框变成类似工具条的窗口形式浮于桌面上)用鼠标选定D:I区域并按窗口右边带红色箭头的按钮(恢复对话框状态)。按“下一步”在弹出的对话框中按“求和列”右边的下拉按钮选中“建筑面积”项再分别按“条件列、运算符、比较值”右边的下拉按钮依次选中“施工单位”、“=”(默认)、“C公司”选项最后按“添加条件”按钮。重复前述操作将“条件列、运算符、比较值”设置为“质量等级”、“=”、“合格”并按“添加条件”按钮。两次点击“下一步”在弹出的对话框中按右下带“―”号的按钮用鼠标选定E单元格并按窗口右边带红色箭头的按钮。按“完成”按钮此时符合条件的汇总结果将自动、准确地显示在E单元格中。友情提醒:上述操作实际上是输入了一个数组公式我们也可以先在E单元格中直接输入公式:=SUM(IF(D:D="C公司",IF(I:I="合格",E:E)))然后在按住CtrlShift键(非常关键~~~)的同时按下Enter键也可以达到上述多条件求和之目的。二、条件计数。统计质量等级为“合格”工程的数目并将结果存放在I单元格中在I单元格中输入公式:=COUNTIF(I:I,"合格")当按下确定按钮后统计结果――数字即自动在I单元格中显示出来。三、条件格式。将工程造价在万元(含万元)以上的工程造价数值以红颜色显示出来:选中F至F单元格用“格式条件格式”命令打开“条件格式”对话框(如图)按第二个方框旁的下拉按钮选中“大于或等于”选项再在后面的方框中输入数字按上述对话框中的“格式”按钮打开“单元格格式”对话框(如图)在“文字”卡片下按“颜色”旁的下拉按钮将文字颜色设置成红色后按“确定”按钮关闭“单元格格式”对话框回到“条件格式”对话框中按“确定”按钮。此时造价大于万元的工程其造价数值将以红色显示在单元格中。友情提醒:继续按上述“条件格式”对话框中的“添加”按钮可以设置多级“条件”如“工程造价大于万元以红色显示大于万元以蓝色显示”等(可以对一个单元格设置三个条件)。四、条件函数。我们在对竣工工程观感质量进行评定后当观感得分率超过该工程质量等级可以评定为“优良”。在I单元格中输入公式:=IF(H>=,"优良",IF(H>,"合格","未竣工"))选中I单元格将鼠标移至该单元格右下角成细十字线时(我们称之为“填充柄”)按住左键向下拖拉将上公式复制到I列下面的单元格中以后当工程竣工后我们将该工程的观感得分率填入H列内相应的单元格中则质量等级自动显示在相应工程的I列单元格中(若没有填观感得分率则I列相应单元格中显示“未竣工”字样)。五、条件查找。当工程基本情况表中登记的工程非常之多时如果我们用普通浏览的方式查找某个具体的工程是非常困难的此时我们可以用“查找”功能来快速定位:用“编辑查找”命令打开“查找”对话框在“查找内容”下面的方框中输入该工程的关键词对“搜索方式、搜索范围”作适当设置后连续按“查找下一个”按钮符合模糊条件的单元格依次被选中即可快速查找到某个具体的工程。友情提醒:在“查找内容”下面的方框中输入的内容不一定非得与某一单元格数值完成相同的文本如查找“C公司”的某个工程只要输入“C”即可以进行模糊查找了。六、条件筛选。如果要打印C公司施工的工程基本情况一览表我们可以用“自动筛选”功能来实现:选中表格中的任一单元格用“数据筛选自动筛选”命令启动“自动筛选”功能(列标题旁出现一个下拉按钮如图)按“施工单位”旁的下拉按钮选中C公司则表格中只显示C公司所施工的工程接上打印机装上打印纸按“打印”按钮即可打印出C公司施工的工程基本情况一览表打印结束后重复第的操作关闭“自动筛选”功能则全部工程又重新显示在表格中。友情提醒:此处的“条件筛选”可以进行“多级”筛选如再上述筛选的基础上再按“进度”旁的下拉按钮选中“在建”选项则表格中只剩下“C公司”施工的所有“在建”工程。

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

评分:

/12

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利