美文网首页
掌握投资测算表中常用的EXCEL函数

掌握投资测算表中常用的EXCEL函数

作者: 牧诗520 | 来源:发表于2019-01-08 14:48 被阅读0次

    文|牧诗

    图片来源|网络

    在投资测算表中,常常会看到一些复杂的函数,这些函数对于我们理解表格之间的逻辑关系起到了至关重要的作用,同时掌握这些函数的基本用法也可以大大提升我们的工作效率,节省大量时间。

    这篇文章我将根据前段时间小伙伴的反馈,对测算表中常见的Excel函数进行了梳理,方便大家后续运用。

    1:VLOOKUP/HLOOKUP函数

    关于LOOKUP函数,可以说是投资测算表excel函数公式中最为重要、最常用的函数了。

    HLOOKUP函数和VLOOKUP函数是一类的函数,具体区别在于VLOOKUP函数是纵向查找函数,HLOOKUP函数是横向查找函数。

    为了便于理解,我们直接通过案例的方式进行讲解。

    大家平时在写报告中想必都遇到过这样的情况,需要单独去统计部分业态的货值。比如项目的规划指标表及销售预测表如下:

    假设现在领导让你统计超豪别墅、多层洋房、中高层洋房、附属商业、公寓这五个业态的预估总货值。

    常规的做法是怎样的呢?从规划指标表中去找出对应业态的可售面积,再从销售预测表中找出对应业态的售价,再做乘积求和算出对应部分业态的总货值。这种人工方法在遇到项目业态繁多的时候,费时费力,且极容易出错。

    而采用VLOOKUP函数便可帮助我们轻松解决这个问题:

    (1)通过VLOOKUP查找出对应业态的可售面积。

    在H3空白格输入公式: =VLOOKUP(G3,A2:E16,5,0)。这里面有4个参数,分别是:

    第1个参数:查询条件(即:G列中的业态)。

    第2个参数:查询区域(即:在哪个区域内进行查找)。从查找条件对应列(A列)开始选择,要求包含被查找信息所在列(即:A2:E16),需要注意的是,选取区域时多选几列是可以的,但不能少选。

    第3个参数:被查询的信息在被查询区域的第几列(即:可售面积在第5列我们输入5)。

    第4个参数:0或FALSE,0表示精确查找。

    (2)同理,通过VLOOKUP查找出对应业态的售价。

    (3)简单的乘积求和变得到我们想要的结果。

    如果查询的列很多,我们写函数公式时,没必要每一个查询都写一次,修改一下参数的引用能大大提高我们的效率。

    可售面积我们始终要在A2:E16区域内查询,所以可以锁定第2个参数的列(A2:E16);售价我们始终要在A19:B33区域内查询,所以可以锁定第2个参数的列(A19:B33)。

    当然如果我们始终要引用G3的业态,可以锁定第1个参数(G3)。

    我们还可以结合其他函数来组合使用,进一步提高工作效率。这里推荐使用COLUMN函数。COLUMN函数的作用是提取单元格的列数(即:第几列),那么A列的列数是1,B列的列数是2,C列的列数就是3,以此类推。我们这样就省去了修改第3个参数的操作。

    HLOOKUP函数与VLOOKUP函数用法类似,同理参照即可,在此不再赘述。

    HLOOKUP与VLOOKUP函数在测算表中运用非常广泛,比如成本表、土增税表可用于查找链接各个业态计容面积,增值税表可用于查找链接销售预测表中每月的楼款收入,利润表中各业态收入、成本可用于查找链接对应的收入、成本数据,等等。

    2:SUMPRODUCT函数

    SUMPRODUCT函数为在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

    该函数可以一键生成两列数组对应元素的乘积之和。接5.4.1案例中,我们要最终求得总货值,除了按常规方法先求乘积再求和之外,可直接通SUMPRODUCT函数一键实现:

    3:IFERROR函数

    IFERROR(value, value_if_error)表示判断value的正确性,如果value正确则返回正确结果,否则返回value_if_error。其中value的错误格式有#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL等。

    比如,我们在测算表中经常看到这样的公式:

    =IFERROR(VLOOKUP(G18,$A$2:$E$16,COLUMN(E$2),0),value_if_error)

    它表示如果公式

    “VLOOKUP(G18,$A$2:$E$16,COLUMN(E$2),0)”查找的结果存在则返回该结果,如果不存在则报错。

    当然,也可以将“value_if_error”替换为具体的数,比如 “0”。

    4:MATCH函数

    MATCH函数可用于返回指定内容所在的位置。举个例子,在单元格中输入:=MATCH("中高层洋房",G13:G18,0),回车可以看到"中高层洋房"所对应的行数为3:

    第1个参数:表示要在区域或数组中查找的值(“中高层洋房”)。

    第2个参数:表示可能包含所要查找的数值的连续单元格区域(G13:G18)。

    第3个参数:表示查找方式,用于指定精确查找或模糊查找,取值为-1、1、0 ,其中0为精确查找。

    5:INDEX函数

    INDEX函数可用于返回指定位置中的内容,在单元格中输入:=IDEX(G12:J18,3,3),回车后可以看到G12:J18区域中3行3列交叉对应的值,即12000:

    第1个参数:表示查找区域(G12:J18)。

    第2个参数:指定区域中的行数。

    第3个参数:指定区域中的列数。

    好了,今天的分享就到这里,欢迎大家留言交流。

    相关文章

      网友评论

          本文标题:掌握投资测算表中常用的EXCEL函数

          本文链接:https://www.haomeiwen.com/subject/gmovrqtx.html