一、问题
有个朋友接了三星电脑的仓储外包服务,三星电脑每天从仓库里进进出出,他雇了一帮人,每天从卡车上卸货、装货。又雇了人专门统计每天操作的电脑台数,他就按照每天操作的电脑台数来向三星收钱。比如某天装卸了100台电脑,每操作一台电脑5块钱(我们叫它单位价格吧),那这一天就应该向三星收500块钱(营业额),如此类推,按月向三星结账。
当然每天装卸的数量是不一样的,而且不一样的装卸数量,其单位价格是不一样的。操作的台数越多,单位价格越高。
他问我,如何在Excel中快速计算每天的营业额。如下表:
日期 | 台数 | 营业额 |
2011/8/1 | 90000 | |
2011/8/2 | 120000 | |
2011/8/3 | 97959 | |
2011/8/4 | 166231 |
点击下载文件: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 |
点击下载文件: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 |
点击下载文件:Report.xls (25.50 kb)
三、新解决方案的优点
怎么样?这种方案是不是更具有扩展性呢?
- 不用嵌套,因此即使数量分段再多,公式也不用变;
- 数量分段改变、增多、减少了,或者同一数量分段的价格变化了,都不用修改公式,只需在单价表里作相应的修改即可;
- 低藕合度:公式与数据源分离了!
四、VLookUp()函数解析
请Google、百度搜寻吧!这里需要提及的只是,公式(=B2*VLOOKUP(B2,单价!$A:$C,3,TRUE))的最后一个参数使用了TRUE,即为模糊匹配,所以可以用来查找数量区间的单位价格。因为这里的操作台数每天变化,而只要在一个区间内的数量,单价都一样,所以在这里使用模糊匹配方式很重要!
如果不了解VLookUp()函数的精确匹配与模糊匹配方式,则还是先Google、百度一下吧!
五、相关文件下载
示例Excel表格:Report.xls (25.50 kb)
[donate: www.zizhujy.com]