加载中…10个Excel核心技巧，轻松应对80%的工作需求
(2017-01-12 11:28:52) 10个Excel核心技巧，轻松应对80%的工作需求
文/罂粟姐姐
本文系欢乐PPT(huanleppt)授权“清南”发布
又到一年年终时，各种报表疯狂袭来，小伙伴们的问题也是此起彼伏层出不穷，所以罂粟姐姐使出洪荒之力，结合大家常见的问题总结了Excel的10个核心技巧，希望能够拯救正在数据海洋里挣扎的你们。（文末有重量级福利）
第一部分：数据输入篇
规范性的数据输入可以减少后期数据整理的工作量，也可以极大地提高数据统计工作的效率。
1、自定义单元格格式
利用Excel单元格的自定义格式我们可以实现一些看起来非常神奇和有用的效果。
操作步骤：点击单元格-单击右键-设置单元格格式-自定义-输入根据数据规律填入的数据类型（或者使用快捷键Ctrl+1调出设置单元格格式）
主要规则：“#”，数字占位符，一个#代表一个数字；“@”，文本占位符；“，”，千位分隔符；“.”，小数点；“%”，百分比。
案例1：将数字以千分位格式输入（###,###）
千分位格式输入
案例2：输入员工的工号("ABC"###)
输入工号
案例3：地名或者公司名称全称的快速输入（"中国"@）
输入名称全称
案例4：做多条件判断，90分以上为优秀，60分以上及格，60分以下不及格（[>=90]优秀；[>=60]及格；不及格）
多条件判断
2、数据有效性
数据有效性是对单元格或单元格区域输入的数据从内容到数量上的限制，这样就可以依靠系统检查数据是否正确有效。
案例1：设置一级下拉列表
操作步骤：“数据-数据验证-设置-允许-序列-选择数据来源（也可手动输入数据，用逗号分隔）
设置一级下拉列表
案例2：设置二级下拉列表
操作步骤：设置一级下拉列表-定义名称-设置二级下拉列表（=INDIRECT($B2)）
设置一级下拉列表及自定义名称
设置二级下拉列表
注意：因为B2单元格最开始为空，所以提示“源当前包含错误”，直接点击确定即可。
第二部分：数据整理篇
将数据整理为规范的数据不仅可以使图表更加整齐美观，符合公司或者个人常用习惯，也能够极大地方便后期的数据统计分析。
3、分列
案例1：删除不需要的内容
操作步骤：数据-分列-固定宽度（分隔符号）-选择“不导入此列”
删除不需要的内容
案例2：按关键字拆分
操作步骤：数据-分列-分隔符号-其它-输入拆分的关键字（使用方法等同于其它分隔符号）
按关键字拆分
案例3：文本或数字转换为日期
操作步骤：数据-分列-分隔符号-列数据格式-日期
文本或数字转换为日期
4、自定义排序
当排序规则首先定好，并且升序和降序都无法实现时，需要使用自定义排序工具。
操作步骤：文件-选项-高级-常规-编辑自定义列表-自定义序列-从单元格中导入序列-导入-确定，选择排序数据-开始-排序与筛选-自定义排序-次序-自定义序列。
导入排序规则
第三部分：数据计算篇
Excel的核心功能就是数据计算，目前Excel所达到的计算能力已经能够满足觉得大多数用户的需求，目前计算方法有三种，一是工具栏上的自带计算工具（中级），二是函数计算（高级），三是VBA（大神级）。
5、合并计算
案例1：单数据源的合并计算
操作步骤：点击“一级区域”-数据-合并计算-输入数据源-添加-选择“首行”-选择“最左列”-确定
单数据源的合并计算
案例2：多数据源相同字段合并计算
操作步骤：点击“一级区域”-数据-合并计算-分别输入多个数据源-添加-选择“首行”-选择“最左列”-确定
多数据源的合并计算
案例3：多数据源不同字段合并计算
操作步骤：点击“一级区域”-数据-合并计算-分别输入多个数据源-添加-选择“首行”-选择“最左列”-确定
多数据源不同字段合并计算
案例4：使用通配符的合并计算
操作步骤：利用通配符构建原始表格区域-单击表格区域-数据-合并计算-输入数据源-添加-选择“首行”-选择“最左列”-确定。
使用通配符的合并计算
6、VLOOKUP函数
题外话：罂粟姐姐最近被问的最多的问题就是“我应该哪个V哪个？”
VLOOKUP函数是Excel工具里公认的大神级函数，8组Excel函数，帮您解决工作中80%的难题这篇文章里有基本用法介绍，各位小伙伴一定要掌握！今天给大家分享3个进阶应用。
英文语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
中文语法：VLOOKUP（找谁，在什么范围找，在范围里的第几列，精确查找或模糊查找)
案例1：多列查找时公式一次性输入
第三个参数在范围里的第几列有两种最常用的表达方法：
（1）1=COLUMN(A1)；2=COLUMN(B1)……
（2）1=MATCH(I6,$C$1:$F$1,0)；2=MATCH(J6,$C$1:$F$1,0)……
最终公式有两种：
（1）=VLOOKUP($H7,$B$1:$F$18,COLUMN(B2),0)
（2）=VLOOKUP($H7,$B$1:$F$18,MATCH(I$6,$B$1:$F$1,0),0)
多列查找时公式一次性输入
案例2：多条件查找
公式=VLOOKUP(E3&F3,IF({1,0},A1:A13&B1:B13,C1:C13),2,0)
E3&F3：将多条件合并
IF({1,0},A1:A13&B1:B13,C1:C13)：利用if函数第一个参数的数组化特征，将多条件合并后的内容与查询的列结合起来，组成新的两列数据。
输入公式结束后同时按Ctrl+Shift+Enter三键出来结果。
多条件查找
案例3：模糊查找
注意：引用的数字区域必须由小到大排列，输出结果是和查找值最接近但比它小的那个值。
公式=VLOOKUP(D4,$A$1:$B$10,2,1)
模糊查找
7、数据透视表
Excel数据透视表，10个不得不说的秘密文章中详细介绍了数据透视表的十大进阶应用，这里再给大家强调一下数据透视表的基础应用。
操作步骤：插入-数据透视表-选择数据源-创建透视表
数据透视表
第四部分：数据图表篇
8、迷你图
迷你图是放入单个单元格中的小型图，每个迷你图代表所选内容中的一行数据。
操作步骤：插入-迷你图-选择迷你图类型
迷你图
9、利用辅助数据作图
作为一个工作多年的职场老司机，最头疼的就是年终报告，而年终报告里最让人头疼的就是数据图表。
曾经，我的数据图表里柱状图是这样的：
直接插入“柱形图”
现在是这样的：
区别主次坐标轴的柱状图
传说中的两极图
根据数据大小变化填充颜色的柱形图
不同季度不同颜色的柱状图
10、动态可视化图表
动态可视化图表可以让数据更生动，更能体现数据的变化规律。
曾经，我的折线图是这样的：
直接插入折线图
现在我的折线图是这样的：
动态折线图
好了，今天罂粟姐姐的教程就到这里啦，连续3个晚上12点后睡觉才写出来，不容易啊！
图表制作部分主要是供大家参考，感兴趣的亲们放心，罂粟姐姐已经细心地为大家准备好了源文件链接，大家下载练习就行，还不会的小伙伴，关注我以后的教程哦。
好辛苦啊，快凌晨1点了，我要休息了，晚安！
来源邀稿：罂粟姐姐（简书）
作者简介：罂粟姐姐，简书作者，主业数据分析，兼职高校讲师，写有温度又有态度的文字。
微博：罂粟姐姐-
微信公众号欢乐PPT(ID:huanleppt)授权发布
http://www.cwbgj.com/advance/ueditor/php/upload/20170112/1484191706740.jpg
http://www.jianshu.com/p/8d3384d1466f#
喜欢
0
赠金笔