|
|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
大家都知道EXCEL是微软公司的OFFICE产品，EXCEL一经推出就得到很多经常处理大量数据的人士的喜爱，主要是因为它的快捷和自动计算的功能，特别是他提供了大量的函数，让我们能够十分方便的使用！
例如：VLOOKUP函数就是一个十分好的应用函数，它主要是用来计算如奖金分配等工作的，为我们减少了很多的麻烦和一些不必要的错误，只要您的条件值是正确的，他保证能够让您得到准确无误的值，今后只要您的条件值有所改动，VLOOKUP函数马上就会更新您的所有值。好了，言归正传！
VLOOKUP函数 语法VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value 为需要在数据表第一列中查找的数值。
Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。
Col_index_num 为table_array中待返回的匹配值的列序号。
Range_lookup 为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值。
首先，我们看看下面的这个表（见表1），这是一个编号和奖金分配的表，本例中奖金是随着编号的固定数值的不同而改变，而且任何不在此编号内的数据都将视为不合格产品，不能给奖金！如20和25这两个值，奖金分别为100和60，如表（1）
编号 奖金
5 50
10 110
15 120
20 100
25 60
表（1）
如果编号是21、22、23、24那么就不能得到奖金！
第一步我做了一个VLOOKUP函数，让奖金与编号挂钩，首先，看看我们的工资表是如何使用VLOOKUP函数的，见表（2）这是一个EXCEL数据表，它VLOOKUP需要一个主表[表（2）]和一个条件表[表（1）]，将他们放在一张表内即可，例如SHEET1内的不同列中即可，我将主表放在A1:E7中，将条件表[表（1）]放在H和I列内，一切准备就绪后，我们就可以将VLOOKUP函数放在相应的单元格中了，即C列中从C2到C7，首先，选择单元格C2，然后我们点击工具条中的 按钮，在"查找与引用"里找到"VLOOKUP"函数，点击确定即可，进入对话框后在：
lookup_value内输入：B2
table_array内输入：H:I
col_index_num内输入：2
range_lookup内输入：暂时不输入（空值）即近似匹配值，将在以下详细介绍。
确定后，单元格C2得到的公式为："=VLOOKUP(B2,H:I,2)"，直接在单元格中输入也是可以的！
然后，使用EXCEL的"自动填充"功能来填入下面5个数据，填充的结果如[表（2）]，只要你改变"条件表" [表（1）]的值，[表（2）]数值将马上进行改变。这样就实现了表格的自动化，但是有一点你可以看到这个表格有两个很大的缺陷，首先就是它出现了错误值#N/A，这个错误值代表的意思是："除以了0"；其次"=VLOOKUP(B2,H:I,2)"这个公式是一个近似匹配值，即20和25之间的任意值奖金都为100，如本例的单元格B3它的值为：21，就得到奖金100（参看[表（1）]）。而本例的要求是：不在编号内的数据，都将视为不合格产品，且不能给奖金！即C3的值必需为"0"，不应该是"100"，否则将导致合计数据为230而不是130元，产生错误！怎样才能改正这两个错误的发生呢？
这就是我要做的第二步，选用另两个函数，ISERROR和IF函数，ISERROR函数是一个测试错误的函数，它的语法是：
ISERROR 值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）。如果您的测试值为错误的时候，当前得到的值为"TRUE"，否则将为"FALSE"。
举例：如果有一个单元格"B9"是一个公式为："＝2/0"回车后，它将成为一个错误值即"#DIV/0!"，用以告诉我们任何值不可以除零！在单元格"A9"内输入公式"=ISERROR(B9)"回车后"A9"的值为："TRUE"，表示测试结果是"真"，如果再次改变"B9"的公式为："＝2/2"回车后给公式变为"1"，我们会发现同时"A9"的值也发生了变化，变为："FALSE"。
在本例中公式"VLOOKUP(B2,H:I,2)"相当于上例中的"B9"单元格，现在我们看看如下两个公式：
①"=ISERROR(VLOOKUP(B2,H:I,2))" ←近似匹配值②"=ISERROR(VLOOKUP(B2,H:I,2,FALSE))" ←精确匹配值上述两个公式，得到的值是不同的，即①得到的两个值（20和25）之间的值如21得到的是FLASE，这就与我们的特定值[表（1）]规定的"任何不在此编号内的数据都将视为不合格产品，不能给奖金！"产生了冲突，所以只能强制让公式得TRUE，即只能用②这个公式，让VLOOKUP函数精确匹配。这样C2和C3的值都为"TRUE"我们的目的就达到了！
最后一步就是使用IF函数，它显然是一个条件函数，语法
IF(logical_test,value_if_true,value_if_false)Logical_test 计算结果为TRUE或FALSE的任何数值或表达式。
Value_if_true Logical_test为TRUE时函数的返回值。
Value_if_false Logical_test为FALSE时函数的返回值。
"Logical_test"的值就是在第二步中，说的②精确匹配公式"Value_if_true"这个值添入：" "0" "，即值公式②的值等于TRUE时。
"Value_if_false"这个值添入："VLOOKUP(B2,H:I,2) "，即值公式①的值等于FALSE时。
OK单元格"C2"最终的公式得到了，如下：
"=IF(ISERROR(VLOOKUP(B2,H:I,2,FALSE)),"0",VLOOKUP(B2,H:I,2))"最后使用"自动填充"功能，向下拖动即可得到相应的数值，见[表（3）]
姓名 编号 正确奖金 错误奖金 基本工资 错误合计 正确合计
张一 3 0 #N/A 100 #N/A 100
李二 21 0 100 130 230 130
王五 10 110 110 130 240 240
大侠 15 120 120 150 270 270
小虾 20 100 100 160 260 260
老板 25 60 60 250 310 310
表（3）
通过这个公式我们能够认识到EXCEL的强大数据处理能力，并由此让您对EXCEL的函数有进一步的了解，在实际工作中充分利用它的内置函数方便自己的工作！ |
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2010-9-25 17:15
楼主
