一、问题

有个朋友接了三星电脑的仓储外包服务,三星电脑每天从仓库里进进出出,他雇了一帮人,每天从卡车上卸货、装货。又雇了人专门统计每天操作的电脑台数,他就按照每天操作的电脑台数来向三星收钱。比如某天装卸了100台电脑,每操作一台电脑5块钱(我们叫它单位价格吧),那这一天就应该向三星收500块钱(营业额),如此类推,按月向三星结账。

当然每天装卸的数量是不一样的,而且不一样的装卸数量,其单位价格是不一样的。操作的台数越多,单位价格越高。

他问我,如何在Excel中快速计算每天的营业额。如下表:

日期 台数 营业额
2011/8/1 90000  
2011/8/2 120000  
2011/8/3 97959  
2011/8/4 166231  

 

VlookUp()函数的妙用

点击下载文件:Report.xls (25.50 kb)

他是这样问的,这个IF()函数该如何写?

我听完他的不同台数对应的不同单位价格,了解到他是想写这样的IF()函数:

=IF(B2>=100000,0.85, IF(B2>=10000,0.84,0.83))*B2

然而,这样的公式非常危险,因为以后一旦单位价格有变化(单位价格变动,或者数量区间变动、增加、减少等),该公式就需要重新写一遍,非常麻烦,而且IF()函数只能嵌套七层,如果数量区间多于7个,就难以用此公式了。

二、解决方案

1. 先新建一张工作表(WorkSheet),命名为单价。按照他的数量分段对应的单位价格,整理出如下的单价表。一目了然:

台数 单价
1 9999 0.83
10000 99999 0.84
100000 -- 0.85

 

VLookUp()函数的妙用

点击下载文件:Report.xls (25.50 kb)

2. 在第一张表(报表)的营业额里输入如下公式:

=B2*VLOOKUP(B2,单价!$A:$C,3,TRUE)

再将公式复制下来,即可

日期 台数 营业额
2011/8/1 90000 ¥75,600
2011/8/2 120000 ¥102,000
2011/8/3 97959 ¥82,286
2011/8/4 166231 ¥141,296
2011/8/5 120071 ¥102,060

VLookUp()函数的妙用

点击下载文件:Report.xls (25.50 kb)

三、新解决方案的优点

怎么样?这种方案是不是更具有扩展性呢?

  1. 不用嵌套,因此即使数量分段再多,公式也不用变;
  2. 数量分段改变、增多、减少了,或者同一数量分段的价格变化了,都不用修改公式,只需在单价表里作相应的修改即可;
  3. 低藕合度:公式与数据源分离了!

四、VLookUp()函数解析

请Google、百度搜寻吧!这里需要提及的只是,公式(=B2*VLOOKUP(B2,单价!$A:$C,3,TRUE))的最后一个参数使用了TRUE,即为模糊匹配,所以可以用来查找数量区间的单位价格。因为这里的操作台数每天变化,而只要在一个区间内的数量,单价都一样,所以在这里使用模糊匹配方式很重要!

如果不了解VLookUp()函数的精确匹配与模糊匹配方式,则还是先Google、百度一下吧!

五、相关文件下载 

示例Excel表格:Report.xls (25.50 kb)

[donate: www.zizhujy.com]