怎样才算精通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.

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

章三: 图/表各位善男子善女人久等. 老衲今天为各位解说五轮真经的第三层, 又称无上正等正觉图形图表经. 如是我闻:

第一级: 表格

主要入口在这里:

也可以使用这个:

表格创建完成后, 点击表格中的内容, 会出现一个新的密法空间:

这些东西都是干嘛的呢?

首先是表名称. 子曰:“名不正,则言不顺; 言不顺,则事不成; 事不成, 则饮西北风". 达尔文在加拉帕戈斯群岛发现的奇行种生物程序猿, 对名称就非常关注, 程序猿对名称的关注主要是认为能方便后续使用. 更直观, 也不容易出错.

以上图出现过的公式距离

=VLOOKUP([对应级数],个税速算表,4,FALSE)

"个税速算表"就是一个表格的名字, 这样的话选择范围就不是一个类似于 =D30:F37 这样很难记忆的字符串, 而变成一个非常容易理解的对象. 而[对应级数]这种列名也一目了然, 如果不加命名, 就得换成=F22:F26, 还要考虑绝对地址和相对地址, 非常麻烦.

起个好名字就成功了一半哦~

切片器: 切片器诞生于2010年. 其实就是一种更美观的筛选.

那 筛选又是什么:

筛选一共只有两路18式. 属于最简易的功夫, 但是日常防身非常实用, 希望各位有时间能多多操演, 不过没时间的话, 老板也会逼着你天天操演, 所以这个不必多解释.

此外再说一下表格的另外一个好处: 生成透视表特别方便.

如果是普通数据, 如果想要生成数据表, 必须全部选中:

但如果是表格的话, 随便选中表中任意一个单元格, 即可开始操作:

既然事已至此, 老衲顺道说一下条件格式这个惠而不费的功能, 自己使用还是给他人展示都非常美观:

下面做个集大成的演示(哎呀呀, 老衲狗眼被晃瞎了, 看来只能明日再叙了)

-------------------阿赖耶识分割线-----------------------

图表经 第二级 图表

图表主练手少阳三焦经, 内力不深, 招式繁多. 以老衲愚见, 这级偏向华而不实. 不过既然至此, 老衲依旧为诸位善男子善女人逐一解说.

图表共有十式, 常用者六, 不常用者四. 看起来招式并不多对吧?

但其中每一式下可能有若干变招, 故而常见的总数是: 52式 (自定义式未计入)

常用图式, 可通过Excel上面的tips来理解 (将鼠标hover在某图式上就可以看到):

后面不太常用的四式可以稍微详细点说说.

-----------------------诸行无常分割线------------------------散点图散点图是展示两变量关系强弱的图形.

老衲举个蒸栗(正例). 一个国家的人均寿命和该国家的人均GDP有没有关系呢?

找了一份2014年的公开数据(不一定完全正确哦)

Excel是一个非常强大的办公软件，我记得之前有人问过我Excel都能做什么？我的回答有三个：

第一、能用Excel去编写一些游戏；

第二、能用Excel做数据处理、图像可视化、分析数据提炼等工作；

第三、还可以辅助财务部门、人事等多个部门处理很多的数据及处理日常的工作；

那怎么才算精通excel呢？

我认为最好的回答就是：遇到Excel问题能够迅速的解决掉！因为无论你的函数应用的再好，技巧会的再多，问题解决不了也是无济于事！

本身我每天会在我的头条号中分享办公软件的应用技巧！我的希望也是成为Excel大神级人物；但这个过程还是很漫长的。

举个实战例子吧，让大家掌握比应用小技巧更值得掌握的日常高手必备知识！

场景再现：我朋友再整理发票的时候，只知道总金额是1150.元。但不知道是哪几张发票组成的了，需要从很多的发票列表中找出。

如下图例子所示：

尽管只有18张发票，但是学过高中数学的同学都知道，如果一单一单去凑数，就是一个排列组合问题，如果运气好的话，可能很快就凑出来了，但如果运气不好呢？更何况这只是例子，如果有成千上万的数字，怎么可能挨个去揍呢？

操作办法：

查找一下自己的excel，“数据”中有没有“规划求解”这项！如图：

第一步、如果没有显示“规划求解”功能，需要添加：点击“文件”——“选项”——“加载项”找到“规划求解加载项”，鼠标点击转到即可。

第二步、需要添加一个辅助列。再运算结果处D2输入=SUMPRODUCT(B2:B19,C2:C19)如图：

第三步、点击“数据”——“规划求解”。

设置目标：为D2单元格

目标值：就是总和1150

设置可变单元格为辅助列C2：C15

然后依次添加可变单元格的3个约束条件（其原理就是在辅助列中不断用1和0去测试，在F2中返回结果），如下图所示：

备注：其中三个约束条件；第一个条件：辅助列< =1;第二个条件：辅助列=整数（切记这个地方选择int）；第三个条件：辅助列》=0。

点击“求解”后出现：

最后，点击确定；结果就出来了！标准“1”的就是要找的两张发票！

关注我的头条号，欢迎随时交流！每天分享办公软件应用技巧！

关注我的微信公众号：IT微技能加油站（点击微信添加朋友——公众号——IT微技能加油站），定期分享你想要的职场生存技能及自媒体相关知识！

也欢迎关注：睡前读书十分钟（点击微信添加朋友——公众号——睡前读书十分钟），定期分享如何把控心态及工作生方式的文章；及时了解世界、读懂人生！

就用它作图(注意老衲的手法, 千万注意, 否则图是画不出来的):

Duang:

能看出来点规律吗? 似乎不是很容易对吧. 喝! 目下才是真正显示手腕的时刻!

再看一遍:

看来钱可通神这四个字果然是有些道理.

刚才的例子也表明, 真正控制图表的, 不在图上, 而是在属性格式里.

那老衲再举一个例子, 民主指数和人均GDP(购买力平价法)之间的关系:

咦, 这个相关性就要差一些了. 有很多有钱的一点也不民主, 但民主的基本还算有钱.

散点图在老衲看来只是一个半成品, 它不能显示数据所属的"系列", 结果就是上面这一大片圆点, 你是无法直观识别每个点是属于谁的.

这个时候就必须配合第三方标签工具来完成: 例如 XY Chart Labeler (下载地址:The XY Chart Labeler Add-in）

顺道再说一句, 如果施主打算自己也写这么个XY Chart Labeler, 功力需要达到第五层心法的第二级, 也就是VSTO. 掌握了VSTO, Excel世界的大门就算正式打开了, 理论上就没有什么能够阻止施主了.

泡泡图

这是散点图的一种变招, 散点图只能选取两列, 而泡泡图必须选取三列, 第三列就是用来计算泡泡面积的, 继续用上面的数据做例子, 我们不但要看看民主指数和人均GDP的关系, 还得看看这个国家的总体量, 省得被一群小国忽悠:

这三列全都选中, 然后选择泡泡图:

结果如何, 各位不妨亲自试试^_^

-------------------四无量心分割线------------------

雷达图雷达图主要是对两个(多个)对象的多个属性进行综合比较的时候使用. 一般来说越圆, 各项指标就越均衡; 圈的面积越大, 综合实力就越高.

注意事项: 属性值作为行(hang), 对象作为列. 这样默认就能输出正确的雷达图. (样例数据来自汽车之家, 老衲不是车托)

好, 出图了:

如果数据写成了这样:

那默认出图就会是这样:

但其实也没啥问题. 这时候需要右键点击图片: 选择数据

然后切换一下行列就OK了

选择数据是非常基本而关键的知识点. 万望各位施主对此能提高重视.

------------------------------------------

曲面图曲面图主要是表现什么呢.......老衲个人理解是.......... 这个主要表现 z = f''( f(x), f'(y) ) 这样的计算式 (这完全不是中文好吗?! 老衲也知道啊, 但是就是不知道中文怎么讲啊!!)

曲面图和"模拟运算表" 可谓是天生的一对, 地造的一双. 什么? 你已经忘了什么叫"模拟运算表"? 那其实也没啥关系, 只要你不是科研/教学人员, 那曲面图和模拟运算表在实际工作中, 遭遇到的概率小于万分之一.

不过行文至此, 图还是要上的(背后的数据就是使用模拟运算表得来的).

是不是很酷炫? 还能3D旋转哦. 但这个图究竟能干啥呢? 让老衲吃包辣条再思考一下.......

------------------------------------------股价图

顾名思义, 就是做出股价分析的图表. 但老衲生平从不炒股(因为没钱), 都不知道这个东西怎么看.....

画一张还是可以的:

这张图对应的数据是这样的:

最多5列, 分别是交易量, 开盘价, 高点, 低点, 收盘价. 顺序不能随便改哦.

预祝炒股的各位同学今年好收成.

------------------以下是干货分割线----------------关于图表的干货终于要来了! 那就是怎么把图表变漂亮~ 追求美乃是人之天性, 老衲但做浅说.

请看老衲标记的部分, 再加上空白的大背景, 一般而言能控制的便是这些. 想要变漂亮就要从每一个标记点上做道场:

一旦开始对图表进行操作, 千万不要触碰快速布局和图表样式模板哦, 否则:

下场请参见: 第二次衝擊

然后就要确定一个所谓美观的范例. 如果不知道猫长什么样, 笔下又怎么可能画出一只猫呢? 不知道漂亮的Excel图表是什么样子, 那即便对所有的操作无比精熟, 又怎么可能做出一张漂亮的Excel图表呢?

(A同学默默地交给老衲一张图: 我觉得这张图很帅, 就要这样的吧)

...................

...................

...................

老衲花了半天功夫, 终于找到了一个合适的示例: 电影票房火箭飞升, 虽然立意不高, 但是配色看着还挺喜庆~

老衲这里没有原始数据, 所以绘图不能100%复刻. 先把已有的数据列出来.

然后标记上对应的颜色(需要使用第三方取色器, 老衲使用的是QQ截图......)

来画个八维太极圆环图吧(饼图的变招)

好像哪里不对, 这和原图没有任何相似之处啊.

切换一下行列(忘了如何切换行列的请往前翻):

好像有点意思了, 不过这也太丑了. 丑得让老衲又开始思考苦海无边, 回头是岸的道理了.....

但这就是Excel自带模板的真面目......

好, 图表美化经中真正的大招来了: 天罡地火 辅助列!!!

为了弥补Excel自带模板的先天不足, 制图时, 很可能需要添加一些数据, 来实现美观的效果.

原有数据(提前切换了行列)添加辅助列之后变成这样:

至于辅助列里面的数据怎么来的, 很遗憾地告诉大家, 是老衲随意编造的. 这个其实需要一些尝试和反复修改.

请看大图!

似乎更丑了.......

吃包辣条冷静了一下, 老衲觉得问题主要是图中每个环都瘦比飞燕, 如果变成杨玉环想必会漂亮得多. 那就来试试吧:

于是乎:

眼尖的同学是不是可以看出一点眉目了?

接下来就简单了:

1. 去掉每个环区的边框, 使其彼此紧凑. 2. 扇区起始角度顺时针移动30度. 3.将辅助列的部分改成"无填充"