怎样才算是精通Excel?(二)

函数 公式  这一节的初级要点是熟悉excel现有的函数库。  其中比较普世的是“逻辑”“文本”“日期和时间”“查找与引用”这四类。  这个常用函数




函数/公式


  这一节的初级要点是熟悉excel现有的函数库。


  其中比较普世的是“逻辑”“文本”“日期和时间”“查找与引用”这四类。






  这个常用函数里面有个人气堪比AKB48的,那就是vlookup(以及它的妹妹hlookup)。


  vlookup其实就是建立两个表的关联,将B表的内容,直接导入到A表:



要点:

1.vlookup第一参数是连接两个表的USB接口,只有数值一致才能成功。(上表中,我们需要填的是王二的职务,王二是B1,且王二是两个表共有的,则第一参数填B1.)

2.第二参数是一个范围,从能连接上的“USB接口”列为第一列。(能连接上的“USB接口”的范围是A8:B10,则第二参数填A8:B10。)

3.第三参数是列序号从“USB接口”为1,依次向后数,数列需要的数值列。(我们需要第二参数选定范围中的第n列,这个数字就填n。例如上表中,我们需要的是职务,职务在A8:B10范围中的第二列,则填2)

4.第四参数是匹配模式。(大多数时候填0就行,即false,代表精确匹配)


  中级要点是如何将这么多复杂的函数做成一个公式。

  复杂的公式,核心就是函数的嵌套。函数里面套函数,招中有招,直至八八六十四招。如长江大河,滔滔不绝。函数的嵌套最多可以套64层(Excel 2013,从前Excel2003-2007为最多套7层)。


  不过心有五蕴,人有三昧,简言之就是地球人还没有进化成三体星人的体态,嵌套的数量一多,就会令人心毒盛起,头晕目眩,前列腺紧张,根本看不清楚自己在写什么。


  臣子恨,何时雪?


  怎么办,中间列!


  现在我们举个栗子,个税计算:

  正确的个税计算算法是这样的:



  写成公式是这样的:

=IF([月工资]-3500<=0,0,IF([月工资]-3500<=1500,([月工资]-3500)*0.03,IF([月工资]-3500& lt;=4500,([月工资]-3500)*0.1-105,IF([月工资]-3500<=9000,([月工资]- 3500)*0.2-555,IF([月工资]-3500<=35000,([月工资]-3500*0.25-1005,IF([月工资]-3500<=80000,([月工资]-3500*0.35-5505,IF([月工资]-3500>80000,([月工资]-3500*0.45-13505,0))))))))


  看到这个公式是不是感到口干舌燥,头晕目眩?


  但如果使用中间列,将公式拆解,每个子部分做成一列,就会立刻神清气爽:



最后将不需要的列隐藏起来, Mission complete~

然后该说说模拟运算了.
公式可以拖动, 其中参考的单元格在拖动的时候位置也会变动. 下图就是joy正在拖数据的瞬间:


一松手:
  这种拖数据, 虽然很简便, 但也有一个问题, 就是只能向着一个方向拖, 或上或下, 或左或右。假设现在有一个数据要求, 有两个变量, 相当于让你同时向下向右拖动怎么办?

  例如不同利率, 不同年限下房贷的问题(这真是一个令人悲伤的例子, 施主请看破红尘吧):
  普通公式也可以做到, 但是就是需要向右拖N次, 或者向下拖M次. 等你拖好, 妹子已经下班, 和别人一起吃麻辣烫去了.
  模拟运算则可以一下子把这个6*6的结果全算出来.

  操作很繁琐, 接下来的内容请点赞, 给施主增加信心:
  先在左上角放一个本息合计公式:
  然后选中所有的可变利率及可变年限:
  然后选择"模拟运算表"
  点击确定之后就可以Duang了:
  唉, 这果然是一个令人伤心的例子.


  高级要点是如何自定义一个函数.
  刚才的例子, 为了计算日期对应的季度, 使用了一个漫长的公式. 现在看看这个, 一个函数就直接命中靶心, 赏心悦目~
  这个Quarter函数, 少侠的Excel里面是找不到的, 因为这是joy自创的。它的真实面目是这样的:

  没错, 这就是第五层心法乾坤大挪移第一级, 也就是VBA.



文章来源:知乎@靳伟



作者: 京东大学
发布日期:2015-12-25 13:10:32  原文链接:点此查看原文