当前位置：文档之家 › Excel函数应用集

Excel函数应用集

  • 格式：xls
  • 大小：4.11 MB
  • 文档页数：19

vlookup应用实例主页

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value为需要在Table_array第一列中查找的数值。

可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列查找文本时，文本不区分大小写；可以使用通配符“*”、“?”。

Table_array为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。对区域引用时，可以引用整列,excel会自动判断使用区域。该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列

Col_index_num为table_array中待返回的匹配值的列序号。如为1时，返回table_array第一列中的数值；为2，返回table_array第二列中如果col_index_num小于1，函数 VLOOKUP 返回错误值值 #VALUE!；如果col_index_num大于table_array的列数，函数 VLOOKUP 返回错误值 #REF

Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则近似匹配查询一般用于数值的查询，table_array的第一列必须如果range_value为FALSE（或0），函数VLOOKUP将返回精确匹配值。此时，table_array不必进行排序。如果找不到，则返回错误值#

例1精确匹配查询之基本运用例8在同一单元格按不同例2数值近似匹配查询之基本运用例9返回多个符合条件的例3文本近似匹配查询之基本运用例10有两个并列条件（不例4区别查询结果的空白与0值例11使用数组常量的查询例5依次序返回同一查询结果的多列内容例12从右向左查询例6返回同一查询结果的多列（不按原次序）的内容##处理查找错误例7按部分内容模糊查询

以上举例是vlookup的基本运用，再结合其它函数，可以发挥其更多作用。处理查找错误vlookup使用中一般会产生以下错误：#VALUE!和 #REF!是由于col_index_num设置不当造成的，虽然可以使用ISERROR剔除，但建议不有时col_index_num参数使用column()函数生成，当设置不当造成的错误用ISE#N/A是常见的错误信息，需要针对不同情况处理：精确匹配查找时出现，是由于未找到完全匹配的值。近似匹配查找时出现，是由于查找值小于数据区的最小值。如果vlookup的参数产生错误，vlooukp也返回相同错误，那不在本讨论范围内。

精确匹配查找时出现#N/A错误首先检查引用范围是否正确，查询值是否在数据区的第一列。

当确定引用正确、不应出现#N/A（即应该查找到值）时，检查是否存在空格或其它字符、格式表一张三丰100对于空格或其他不可见字符，可以使用len函数测试文本长度来确李四军 200B50单元格长度4张三丰 #N/A该错误由于查询字符“张三丰 ”后含空格，可能看不出，但在精

100对于lookup_value中的空格，将其中的空格替换掉，或使用TRIM

李四军#N/A该错误是由于查询表表四中“李四军”后含空格，在精确匹配查

200对于Table_array中的空格，也可以使用trim。

这个公式中的Table_array已不是直接引用单元格区域，而是引用因此，这是一个数组公式，需要按Ctrl+Shift+Enter输入如果数据区比较大，数组公式会很慢；不如直接使用替换将数据

李四军 #N/A该错误由于查询字符“李四军”后含不可见字符（非空格），使

由网页复制、其他程序转出的表格，经常会出现这类字符，CODE对于这类不可见字符，可以先复制该字符，然后替换该字符为空

表二100A查找内容格式不符，也会造成＃N/A错误200B可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值

100#N/A这两个错误都是由于格式不一致造成，一个是按数值查文本，一

200#N/A对于此类错误，可以修改查找值、数据区的格式，使之统一；也

A这个公式将数值100用&连接空字符串""，强制转为文本

B这个公式将文本200用*1运算，强制转为数值

要注意的是采用修改格式的方法，仅仅将单元格格式更改还不行例如对于包含数值的常规单元格将格式改为文本后单元格很多时，采用逐个激活的方法肯定会累死。可以采用分列

当某列数据需要全部转换时，采用分列是个好办法。它可以将文分列位于菜单栏——数据——分列由文本转数值也可以复制一空白单元格，选定需转换的数据区，选择性

当确定应该出现#N/A（即查找值不存在）时，如果不要显示错误，可以使用条件格式或直接在表三A100注意使用条件格式处理后单元格内的值仍是#N/A，其他单元格引C200当C55选B时，显然应该返回错误（数据区没有B）。B#N/A这个单元格使用条件格式处理，条件格式公式＝isna(D55)，并设使用ISNA测试vlookup函数是否返回#N/A，如vlookup函数返回#N精确查找时，也可以直接判断查找值是否存在来去除

近似匹配查找时出现#N/A错误近似匹配查找时，除了前述的格式等原因，查找值小于数据区的最小值将返回#N/A。对于此类错误，只要数据区设计合理就可避免（建立可能的最小值）,当然也可以使用ISNA去除

精确匹配查询之基本运用

精确查找是vlookup最基本也是最常用的功能，对于数据量大的查找，其速度比菜单中的查找还快。设置vlookup第四个精确查找适用于文本，也适用于数值；但对数值查找时须注意格式一致，否则会出错

表一姓名工号性别籍贯出生年月张三丰KT001男北京1970年8月

数据含空格等

数据格式不一致

处理错误返回李四光KT002女天津1980年9月王麻子KT003男河北1975年3月赵六儿KT004女河南1985年12月

姓名籍贯王麻子河北根据姓名在$B$9:$F$12中查找籍贯，$B$9:$F$12区域最好使用绝对引用（可在查找的姓名必须位于$B$6:$F$9的第一列；籍贯在$B$6:$F$9位于第四列，因此姓名性别李四光女根据姓名在【表一】中查找籍贯【表一】是定义的单元格区域$B$9:$F$12的名称，在名称框（编辑栏左边）可以工号籍贯KT002天津根据工号在$C$8:$E$12中查找籍贯注意范围已改为$C$8:$E$12，确保工号在第一列；由于范围的改变，籍贯位于该工号出生年月KT00125781根据工号在$C$8:$F$12中查找出生年月，但此时返回的是时间序列值（即格式没1970年8月需要重新设定单元格格式1970年8月或在公式中使用text设定格式

数值近似匹配查询之基本运用近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略，或为true，或为非0数值。（EXCEL中0等同FALS这是一张个所税税率表，对于不确定的收入，可以利用近似匹配查找税率及速扣数表二级数 应税所得超过且不超过税率(%)速算扣除数 15005025002000102532000500015125450002000020375520000400002513756400006000030337576000080000356375880000100000401037591000004515375

应税所得税率速算扣除数 税款5360203756975360在表中未列出，因此EXCEL查找小于5360的最大值即5000，并返回对应的税率等。注意公式中第四个参数省略，或为true，或为非0数值。并且查找的第一列必须升序排列，否则不会返回期望的结果（需要注意的是此时不一定返回错误）。试着输入5000，你会发现税率为20%，应是15%（由条件指定），虽然对最后计算结果没有影响。以下公式可以解决这个

应税所得税率速算扣除数 税款500015125625

文本近似匹配查询之基本运用表三对于文本，一般不使用近似匹配查找（非模糊查找）。吖A并非文本不能用于近似查找，主要是没有太多用途。除了下面这八B嚓C这个例子利用文本近似匹配查找汉字的第一个拼音字母咑D由于EXCEL中汉字字符是按拼音排序的，因此汉字字符比较时的大鵽E左边的表是根据汉字拼音首字母排列，并取各字母的临界点汉字

返回

返回发F猤G汉字字符拼音首字母铪H家J夻J咔K通过将“家”与表中字符比较后，找到比“家”小的

垃L这个例子一般用于姓名的缩写，而上面的公式只找第一个字符，嘸M旀N汉字字符拼音首字母噢O普利卡PLK妑P七Q这个公式取得前三个汉字字符的拼音字母。使用MID依囕R仨S使用该例关键是【表三】的建立；同理，如果要查找汉字的完整他T但这个方法并不一直正确，由于多音字及文字大小排序与其拼音屲W夕X丫Y帀Z

区别查询结果的空白与0值表四姓名内容张三丰0vlookup对于查找到的0值会返回0；张三丰0李四光0对于空白单元格（非空字符串）也会李四光王麻子对空字符串仍返回空字符串。王麻子赵六儿赵六儿张三丰0如要对0值和空白单元格区别，可以直李四光如不需要0值，可直接判断＝0，或在

依次序返回同一查询结果的多列内容按姓名在【表一】中查询全部内容姓名工号性别籍贯出生年月李四光KT002女天津1980年9月王麻子KT003男河北1975年3月

当查询某个结果的多列内容，并要依次取出，可使用column()函数作为index_num参数column()函数返回公式所在列的列号，当公式复制时，会随单元格的移动相应改lookup_value参数使用混合引用，如$B96。即对列标绝对引用（列标前加$）、上面公式只要输入C96中的公式，然后向右、向下复制就可以。

返回同一查询结果的多列（不按原次序）的内容按姓名及不同项目（不依次序）在【表一】中查询姓名性别籍贯出生年月工号先由match函数确定项目内容在B8:F8赵六儿女河南1985年12月KT004注意公式中Match函数的王麻子男河北1975年3月KT003

按部分内容模糊查询

返回返回返回返回

合集下载

日常EXCEL函数应用实例

日常EXCEL函数应用实例


EXCEL操作界面及设置
右键单击工具栏→“自定义 快速访问工具栏〞→弹出自 定义快速访问工具栏选项卡， 可以设置自定义访问工具栏。
Where is it？
1 EXCEL操作界面及设置 2 函数与公式通用技巧 3 常用函数
函数与公式通用技巧
符号
说明
实例
优先顺序
：
=SUM(A1:B10) 区域运算符
=MIN(number1,number2,...) =MEDIAN(number1,number2,...) 中居于中间的数值。 =MODE(number1,number2,...) 多〔众数〕的数值。
返回一组值中的最小值。 返回给定数值的中值。中值是在一组数值
返回在某一数组或数据区域中出现频率最
常用函数——AVERAGE
因为excel将数值按照15位计算精 度带入计算所致。如图，在excel选项 中勾选“将精度设为所显示的精度〞， 可以解决上述情况的发生，但这样也降 低了工作薄中所有工作表的计算精度， 但不影响其他工作薄。
此外，也可以用ROUND函数对数值 进展修约后再求和。
函数与公式通用技巧——分布查看公式计算结果
常用函数——SUM
=SUM(number1,number2, ...)
说明 直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，请参阅下面的例如一和例如二。 假如参数是一个数组或引用，那么只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。请参 阅下面的第三个例如。 假如参数为错误值或为不能转换为数字的文本，将会导致错误。
常用函数——SUBSTITUTE
=SUBSTITUTE(text,old_text,new_text,instance_num)
Excel函数应用教程

Excel函数应用教程

Excel函数应用教程：统计函数来源：CFAN 发布时间：2009-07-21 [评论Error! Invalid Template Key.条][an error occurred while processing this directive]41.MAX用途：返回数据集中的最大数值。

语法：MAX(number1，number2，...)参数：Number1，number2，...是需要找出最大数值的1至30个数值。

实例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，则公式“=MAX(A1:A7)”返回96。

42.MAXA用途：返回数据集中的最大数值。

它与MAX的区别在于文本值和逻辑值(如TRUE 和FALSE)作为数字参与计算。

语法：MAXA(value1，value2，...)参数：value1，value2，...为需要从中查找最大数值的1到30个参数。

实例：如果A1:A5包含0、0.2、0.5、0.4和TRUE，则:MAXA(A1:A5)返回1。

43.MEDIAN用途：返回给定数值集合的中位数(它是在一组数据中居于中间的数。

换句话说，在这组数据中，有一半的数据比它大，有一半的数据比它小)。

语法：MEDIAN(number1，number2，...)参数：Number1，number2，...是需要找出中位数的1到30个数字参数。

实例：MEDIAN(11，12，13，14，15)返回13;MEDIAN(1，2，3，4，5，6)返回3.5，即3与4的平均值。

44.MIN用途：返回给定参数表中的最小值。

语法：MIN(number1，number2，...)。

参数：Number1，number2，...是要从中找出最小值的1到30个数字参数。

实例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，则公式“=MIN(A1:A7)”返回49;而=MIN(A1:A5，0，-8)返回-8。

Excel函数实践应用——出车补贴

Excel函数实践应用——出车补贴

Excel函数实践应用——出车补贴最近，有同学问了两个Excel的实际应用问题，问题内容与司机出车补贴有关，具体如下：问题一：按小时计算补贴司机出车时间10小时以下，补贴15元一个小时；10个小时以上包括10个小时，一次性补贴150元问题二：按时间段计算补贴早上6点前，晚上22：30后补贴50以上两个问题看着复杂，其实用IF函数就能轻松解决，下面进行实操演示。

按小时计算补贴1.问题分析问题一：司机出车时间10小时以下，补贴15元一个小时；10个小时以上包括10个小时，一次性补贴150元。

根据问题分析，问题中前后两种情况是不共存的，那么可以理解为，其中一种情况存在时，另外一种情况的值为零。

2.分解问题建立公式根据问题，我们可以用IF函数，分别为问题中的两种情况建立公式，分别得出以下两条公式：IF(参考数<10,15,0)*参考数IF(参考数>=10,150,0)3.公式整合一个单元格中，不能填写两条公式，我们只能将两条公式结合，根据我们的分析，只要将两条公式相加就可以了，因为其中一种情况存在时，另一种情况的值为零，因此相加并不会影响到对方的结果，最终结果如下图。

按时间段计算补贴1.问题分析问题：早上6点前，晚上22：30后补贴50元这个问题反应的情况是：时间大于6:00，并小于22:00，没有补贴；时间小于6:00，或大于22:30有补贴2.尝试列公式在这里我们继续使用IF函数，我们可以用时间小于6:00，或大于22:30有补贴这种设定来列公式，获得公式如下：IF(参考值<=6:00,50,0)IF(参考是>=22:30,50,0)3.公式整理参照上一个问题的解题思路，我们尝试将两个公式相加，但是，最后得到的却是Excel的报错提示，如下图4.改正错误为什么Excel会报错呢？原因是公式中不能直接使用时间，我们需要把时间转换为数值方可使用，方法如下图，得到时间6:00对应的值是0.25，时间22:30对应的值是0.93755.正确填写公式根据上一步所得数值，最终公式应为：=IF(B2<=0.25,50,0)+IF(B2>=0.9375,50,0)最终结果如下图6.方法补充：使用IF函数和OR函数结合，公式为：=IF(OR(B2<=0.25,B2>=0.9375),50,0)如果用时间大于6:00，并小于22:00时，没有补贴，否则有补贴的思路，公式为：=IF((B2>0.25)*(B2<0.9375),0,50)。

Excel函数应用之统计函数

Excel函数应用之统计函数

Excel函数应用之统计函数Excel的统计工作表函数用于对数据区域进行统计分析。

例如，统计工作表函数能够用来统计样本的方差、数据区间的频率散布等。

是不是感觉仿佛是很专业范围的东西？是的，统计工作表函数中提供了很多属于统计学范围的函数，但也有些函数其实在你我的日常生活中是很经常使用的，比如求班级平均成绩，排名等。

在本文中，要紧介绍一些常见的统计函数，而属于统计学范围的函数不在此赘述，详细的利用方式能够参考Excel帮忙及相关的书籍。

在介绍统计函数之前，请大伙儿先看一下附表中的函数名称。

是不是发觉有些函数是很类似的，只是在名称中多了一个字母A？比如，A VERAGE与A VERAGEA；COUNT与COUNTA。

大体上，名称中带A的函数在统计时不仅统计数字，而且文本和逻辑值(如TRUE 和FALSE)也将计算在内。

在下文中笔者将要紧介绍不带A的几种常见函数的用法。

一、用于求平均值的统计函数A VERAGE、TRIMMEAN一、求参数的算术平均值函数A VERAGE语法形式为A VERAGE(number1,number2, ...)其中Number1, number2, ...为要计算平均值的1～30个参数。

这些参数能够是数字，或是涉及数字的名称、数组或引用。

若是数组或单元格引用参数中有文字、逻辑值或空单元格，那么忽略其值。

可是，若是单元格包括零值那么计算在内。

二、求数据集的内部平均值TRIMMEAN函数TRIMMEAN先从数据集的头部和尾部除去必然百分比的数据点，然后再求平均值。

当希望在分析中剔除一部份数据的计算时，能够利用此函数。

比如，咱们在计算选手平均分数中经常使用去掉一个最高分，去掉一个最低分，XX号选手的最后得分，就能够够利用该函数来计算。

语法形式为TRIMMEAN(array,percent)其中Array为需要进行挑选并求平均值的数组或数据区域。

Percent为计算时所要除去的数据点的比例，例如，若是percent = ，在20个数据点的集合中，就要除去4个数据点(20 x ，头部除去2个，尾部除去2个。