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

Excel没有未来2018-11-04 15:20:57

章二. 函数/公式

此二者非常容易混淆, 画张图比较容易说清楚:




只要在上方公式区输入的, 等号之后的内容, 都属于公式(绿色). 
而函数(红色), 则是后面带一对括号的那些内容.

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



其中比较普世的是以下四类:



数量不多, 经常去 [该网站因为政策法规不予展示] 上搜一下, 就知道用法了. 
这个常用函数里面有一个人气堪比AKB48的, 那就是VLOOKUP (以及他的妹妹HLOOKUP)

VLOOKUP其实就是建立两个表的关联, 将B表的内容, 自动导入到A表:




以下是用法详解, 实在看不懂老衲的字迹的, 可私信:





-----------------------断见取见----------------------
中级要点是如何将这么多函数做成一个复杂的公式
复杂的公式, 核心就是函数的嵌套, 函数里面套函数, 招中有招, 直至八八六十四招. 如长江大河, 滔滔不绝. 函数的嵌套最多可以套64层(Excel 2013, 从前Excel 2003-2007为最多套7层). 函数的总字数长度可达恒河沙数 (老衲记不清具体数量的时候就暂时说恒河沙数).

下图这个例子就是一个简单的多层嵌套, 主要是if逻辑上的嵌套.



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

臣子恨, 何时雪?
怎么办? 中间列!

这回老衲举一个实用的例子, 个税计算:

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



写成公式是这样的:




把公式摘出来给各位欣赏一下:

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

看到这个公式是不是感到口干舌燥, 头晕目眩, 前列腺紧张?

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



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

然后该说说模拟运算了.

公式可以拖动, 其中参考的单元格在拖动的时候位置也会变动. 下图就是老衲正在拖数据的瞬间:



一松手:



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

例如不同利率, 不同年限下房贷的问题(这真是一个令人悲伤的例子, 施主请看破红尘吧):



普通公式也可以做到, 但是就是需要向右拖N次, 或者向下拖M次. 等你拖好, 妹子已经下班, 和别人一起吃麻辣烫去了. 
模拟运算则可以一下子把这个6*6的结果全算出来.

操作很繁琐, 接下来的内容请点赞, 给施主增加信心:

先在左上角放一个本息合计公式:



然后选中所有的可变利率及可变年限:



然后选择"模拟运算表"



点击确定之后就可以Duang了:



唉, 这果然是一个令人伤心的例子.

-------------------阿耨多罗三藐三菩提--------------------

高级要点是如何自定义一个函数.

刚才的例子, 为了计算日期对应的季度, 使用了一个漫长的公式. 现在看看这个, 一个函数就直接命中靶心, 赏心悦目~



这个Quarter函数, 少侠的Excel里面是找不到的, 因为这是老衲自创. 它的真实面目是这样的(感谢

@黄老邪

的提醒):





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

-------------------六道轮回分割线-----------------------

来源:知乎,作者:靳伟


Copyright © 日本动漫音乐虚拟社区@2017