|
|
Excel 有哪些和 vlookup 一样重要的函数或功能？
可以实现什么功能？用法？请举例。
关注者
9,376
被浏览
1,170,734
132 个回答
函数是excel中最重要的分析工具，面对400多个excel函数新手应该从哪里入手呢？下面是实际工作中最常用的15个（组）函数，学会后工作中的excel难题基本上都能解决了。
1、IF函数
用途：根据条件进行判断
用法：
=IF(判断条件,条件成立返回的值,条件不成立返回的值）
=IF(AND(条件1,条件2), 两个条件同时成立返回的值,不成立返回的值)
=IF(OR(条件1,条件2), 两个条件之一成立返回的值,都不成立返回的值)
如图：当A列值小于500且B列值显示未到期，在C列显示补款，否则显示空白。
=IF(AND(A2<500,B2="未到期"),"补款",””)
2、Round函数、INT函数
用途：数值四舍五入和取整函数
用法：=Round(数值,保留的小数位数） 四舍五入；=INT(数值) 数字取整
如图：分别对A1的小数进行取整和四舍五入保留两位小数B4公式 =INT(A1)；B5公式 =Round(A1,2)
3、Vlookup函数
用途：数据查找、表格核对、表格合并
用法：=vlookup（查找的值，查找区域，返回值所在列数，精确还是模糊查找）
如图：根据姓名查找职位。
4、Sumif和Countif函数
用途：按条件求和，按条件计数，很多复杂的数据核对也需要用到这2个函数。
用法：=Sumif(判断区域,条件，求和区域)；=Counif(判断区域,条件）
如图：要求在F2统计A产品的总金额
5、Sumifs和Countifs函数
用途：多条件求和、多条件计数，数据分类汇总利器
用法：
=Sumifs(求和区域,判断区域1，条件1，判断区域2，条件2…..
=Countifs(判断区域1，条件1，判断区域2，条件2.....）
如图：统计郑州所有电视机的销量之和=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
说明：在sumifs中可以使用通配符*
6、Left、Right和Mid函数
用途：字符串的截取
用法：
=Left(字符串,从左边截取的位数)
=Right(字符串,从右边截取的位数)
=Mid(字符串,从第几位开始截,截多少个字符）
=left("abcde",2) 结果为 ab
=right("abcde",3) 结果为 cde
=mid("abcde", 2,3) 结果为 bcd
7、Datedif函数
用途：日期的间隔计算。
用法：
=Datedif(开始日期,结束日期."y") 间隔的年数
=Datedif(开始日期,结束日期."M") 间隔的月份
=Datedif(开始日期,结束日期."D") 间隔的天数
如图：B列为入职日期， 要求计算入职多少月？
8、最值计算函数
用途：计算最大值，最小值
用法：
=MAX(区域) 返回最大值
=MIN(区域) 返回最小值
=Large(区域,n）返回第n大值
=Small(区域,n）返回第n小值
如图：对D列的数字计算最大值，最小值，第2大值，第2小值。
9、IFERROR函数
用途：把公式返回的错误值转换为提定的值。如果没有返回错误值则正常返回结
用法：
=IFERROR(公式表达式,错误值转换后的值）
如图：要求计算完成率
10、INDEX+MATCH函数
用途：match查找到行数列数，然后用index根据位置从另一行/列中提取相对应位置的值
用法：=INDEX(区域,match(查找的值，一行或一列,0) )
如图：要求根据产品名称，查找编号。
11、FREQUENCY函数
统计年龄在30-40岁之间的员工个数。
12、AVERAGEIFS函数
按多条件统计平均值。
13、SUMPRODUCT函数
统计不重复的总人数，用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。
14、PHONETIC函数
Phonetic函数只能对字符型内容合并，数字不可以。
15、SUBSTITUTE函数
如图，可以将手机号码的中间四位换成星号。
全文22706个字，阅读大约10分钟，干货预警，作为Excel的深度使用者，跟VLOOKUP一样重要的功能，如果要选择一个，甚至说Excel只能学习一个功能，那么肯定是——数据透视表，数据透视表有多强大呢？借着这个话题分享给大家，希望对大家有帮助。
本文首发于公众号： 芒种学院 ，未经允许，禁止转载。
90%的Excel使用场景都在“数据分析”，而“数据透视表”作为Excel中最强大的数据分析工具，其重要程度不言而喻，在我们组建的数千人的学习社群中，总结了大量的透视表使用技巧和学习路线，借着这个话题分享给大家，因为篇幅问题，整理好的教程都放在链接中了~
课程传送门（学完点个5星即可）
Excel就该这样用，数据透视表篇 - 网易云课堂
01 数据透视表是什么？
可能大部分人都不知道透视表是什么？简单来介绍下透视表是什么？能做什么？如何提升我们的工作效率，为什么要使用透视表？先来简单看一个视频：
透视表能做什么？
https://www.zhihu.com/video/1097976429505069056
除了代替复杂函数，制作交互性图表，还能规范数据，一个GIF的时间完成一项工作：
一键规范数据
https://www.zhihu.com/video/1097976564406403072
透视表（Pivot Table）是一种交互性的表，可以用来进行计算，例如：求和、筛选、排序等等，并且计算的结果跟透视表中的排列有关，之所以成为数据透视表，是因为它可以动态地改变透视表的版面布局，可以非常方便地从不同角度分析数据，并且这里还有一个词，叫“交互”，跟传统的表格不同，我们可以跟表格之间做一些人机交互，更方便地集中展示我们想要的数据。
先来看下透视表能做哪些高级的操作吧~
1.1 数据的快速分组
在Excel传统用法中，如果要对数据进行分组，要写非常复杂的混合函数，效率非常的低，使用透视表就非常的便捷，右击「 创建组 」，然后「 月份 」，点击「 确定 」即可。动画演示：
当然除了日期，还可以对数值、文本进行分组，也是同样的操作，非常方便。
1.2 相同标签的快速合并
合并相同标签也是一个非常使用的操作，如果使用合并单元格去合并的话，效率非常的低，并且会修改数据的结构，使用透视表就可以非常便捷地完成这个操作。
右击「 数据透视表选项 」，勾选「 合并且居中标签 」，点击「 确定 」即可。
除了能快速合并相同标签，也可以选择将相同的标签快速进行重复录入，在透视表选项中也是非常便捷就能完成的一个操作。
1.3 数据一键排名
传统的表格中，使用排名要使用RANK函数，复杂一点的会使用SUM和COUNTIF函数配合数组，非常麻烦，其实在透视表中只需要重新插入一个重复字段，右击「 值显示方式 」-「 降序排序 」，然后右击「 排序 」-「 升序 」即可完成快速排名，不用函数繁琐计算，非常方便。
1.4 一键批量创建工作表
在使用Excel的时候，经常需要对某些数据进行拆分单元格，例如：不同地区的数据放到不同的工作表。手动筛选然后复制粘贴的效率实在是太低了，这个时候就可以利用透视表快速拆分数据。
将需要拆分的「 字段 」拖动的「 筛选区域 」，然后点击「 分析 」选项卡，选择「 显示报表筛选页 」，点击「 确定 」即可。
1.5 一键合并所有文档
透视表除了能拆分文档，还能合并文档，对于相同结构的多份文档，如果使用手动合并，效率非常低下，使用透视表30秒就可以搞定。
依次按「 Alt+D+P 」，选择「 多重合并计算 」，然后将「 每一张表的数据一一添加 」，点击「 确定 」即可。
合并前的12份文档：
合并结果：
1.6 一键计算所占百分比
在Excel中需要统计每个数据所占的百分比，利用SUM函数也可以完成，不过在透视表中只需要通过修改值显示方式即可搞定。
右击「 值显示方式 」，选择「 总计的百分比 」即可完成。
1.7 一键规范数据
都说「 数字不规范，亲人两行泪 」，在办公中会经常碰到各种不规范的数据，例如将一月、二月、三月或者销售员的名字做成字段名。
这种表格对我们的统计分析来说会非常麻烦，如果需要手动去处理统计，非常吃力，然而利用透视表就可以大大提升我们的一个效率，一分钟做出一份规范的数据。
依次按「 Alt+D+P 」，选择「 多重合并计算区域 」，然后「 创建单页字段 」，点击「 确定 」，在新建的透视表中，拖动到「 右下角的汇总单元格 」，双击，就可以看到规范的数据了。
例如下图：
动画演示：
1.8 60秒制作高级交互图表
传统的图表要想制作出交互功能，只有通过插入组件或者使用数据验证制作下拉菜单进行交互，而利用数据透视表，可以便捷的插入切片器、动态透视图、日程等等，可以制作出非常强大的交互效果，如下图：
1.9 一键修改数据格式
修改数据的显示格式也是一个常用的技巧，如果使用手动去修改会非常麻烦，例如将10000修改成10,000这种千分位的展示形式，利用「 数字格式 」可以一键完成修改。
右击「 数字格式 」，选择「 数值 」，勾选「 千分位 」，小数点后位数为「 0 」，点击「 确定 」即可。
1.10 快速分析数据
作为Excel里数一数二的数据分析工具，透视表用得最多的就是数据分析，可以快速进行交叉分析、对比分析、结构分析、汇总分析等等。
例如按照年度、季度、月份、天数对数据进行分组统计，利用数据透视表内置的数据分组，可以快速实现：
快速对比两列数据，例如快速对比其他月份与1月的销售额差异百分比，利用透视表鼠标拖拽也非常方便：
快速创建数据上下级，实现数据按照不同分类进行汇总：
02 如何使用透视表
将透视表划分成10个部分来系统学习，如： 软件基础操作（基本术语、重整布局、数据源、字段重命名、延迟布局更新等）、数据刷新、值字段的单元格格式、排序与筛选、透视表分组计算、透视表虚拟字段、透视表动态查询、透视表布局与样式、动态透视图 。
对于透视表的学习，最重要的是多动手，多实践，才能快速学会数据统计分析的技巧，找到数据中隐藏的规律，碰到问题才能快速解决。
先来简单看下学习大纲（点击查看大图）：
01 透视表基础知识
熟悉数据透视表的基本术语、4大区域、常用操作对于后期深入学习数据透视表是非常重要的，这些基础知识看起来会比较枯燥，但是确实整个学习中最重要的部分。
1.1 数据源的基本要求
数据源是创建透视表的基础，并非所有的数据都适合创建透视表，它是具有一定要求的，判断数据是否规范，总共有5个方面：
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
1.2 创建数据透视表
数据透视表的创建方法有多种，并且数据源也有不同的来源，并且每种创建方法的表现形式都存在差异，学会利用数据缓存来创建数据透视表，能让所有的透视表都共享缓存，文档会变得更小，运行速度会变得更快。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
1.3 基本术语和4大区域
了解透视表中的项、字段、行区域、列区域、值区域、报表筛选区域：
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
1.4 透视表的基本操作
选择透视表的单元格、字段、项、整张表，重命名透视表，复制/移动/删除透视表是透视表的基本操作，这些是使用透视表的一个前提：
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
1.5 透视表字段布局重命名
字段布局是调整字段在4大区域中的位置，通过鼠标拖拽的方式来进行快速数据分析，重命名会帮我们更直观观测透视表。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
1.6 延迟布局更新
延迟布局更新在处理大量数据的时候效率会非常的高，使用延迟布局更新，可以在调整好字段布局之后才统一更新数据透视表，会大大减少我们等待的时间。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
02 数据源的刷新
2.1 手动刷新数据源
透视表默认使用的都是缓存数据，当数据源更新的时候，透视表是不会自动更新的，需要我们提供过手动来刷新数据，通常数据源的改动有：数据改动、数据区域改动。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
2.2 自动刷新数据源
除了手动刷新之外，可以设置文件打开的时候自动刷新数据，也可以通过vba，插入一个按钮，通过点击按钮就可以刷新数据。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
2.3 刷新时的注意事项
在数据刷新后，往往会出现很多 垃圾条目 或者刷新后， 单元格的样式、列宽全部改变了 ，这个时候可以通过数据透视表选项窗口来设置，知识点也不多，但是非常实用。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
03 值字段数字格式
3.1 数字格式设置
在透视表中也可以修改单元格的格式，在不更改数据的情况下可以改变透视表的显示方式。例如添加 前缀、后缀、千分位等， 让计算类型变成： 求和、计数、平均、最大、最小 等等。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
3.2 空值和错误值处理方式
透视表中难免会碰上错误值或者空值，如果不处理的话，会让报表看起来比较怪异。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
04 透视表筛选和排序
4.1 自动和手动排序
在透视表中，如果对显示的顺序不满意的话，可以通过透视表的排序功能来对数据进行排序，例如：手动排序、自动排、自定义排序、依据其他字段排序等。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
4.2 筛选字段布局与排序
除了通过列筛选、行筛选来筛选数据，透视表还提供了报表筛选区域，更加简洁专业地让我们来筛选数据，在筛选区域中，数值也是可以进行排序的，并且可以改变分布的方式。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
05 透视表数据分组
5.1 数值和文本分组
在透视表中，我们可以将某类具有相同规律的数值分成一组，例如20~30岁的可以分成一组，同个姓氏的可以分成一组，这里就涉及到数值和文本分组了。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
5.2 日期数据快速分组
日期数据也可以进行快速分组，不过需要满足是规范日期的条件，日期数据可以划分为：秒、分、小时、天、月、季度、年份等。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
06 透视表中的虚拟字段
6.1 常用的值显示方式
在透视表中，除了常规的无计算选项之外，还可以设置值的计算方式，例如显示成百分比、差异、差异百分比、父级百分比等，每种百分比都有不同的展示形式。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
6.2 计算字段的使用
在透视表中，如果当前的字段没办法满足我们的需求，这个字段又可以通过其他字段计算得到，这个时候我们就可以利用透视表提供的计算字段功能。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
6.3 计算项使用基础
计算项和计算字段的区别是，一个仅仅是一条数据，另外一个是一个字段，如果我们想在透视表中新增一条数据的话，就可以使用计算项。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
07 动态查询数据
7.1 切片器实现交互式筛选
切片器是Excel2010提供的一个新功能，它提供了一个图形化交互筛选的方式，比筛选报表使用起来更加流畅舒服。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
7.2 多表联动筛选小技巧
在透视表中，一个切片器是可以关联多个透视表的，这样的话，我们就可以实现，一次筛选，多次展示的效果。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
7.3 切片器样式修改
在Excel中，系统内置了几十种切片器的样式，我们可以快速来修改切片器的样式，也可以通过自定义的方式来修改样式。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
08 透视表布局与样式
8.1 透视表的3种布局
默认的透视表使用压缩布局，我们可以更改为大纲布局或者表格布局，让显示看起来更加宽松一些，同时还有项目标签的处理方式，可以通过合并单元格的形式来合并项目标签。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
8.2 透视表布局样式的快速修改
在Excel中，总共有85种内置的透视表样式，分为浅色、中浅深色、深色。除了使用内置的方式来修改，也可以自定义透视表的样式。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
09 动态透视图
9.1 创建/删除/移动/修改数据透视图
在Excel中，透视图是另外一个神器，我们可以通过3种方式来创建透视图，并且删除我们不想要的透视图，或者修改透视图的图表类型等。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
9.2 切片器联动透视图&迷你图的使用
在Excel中，如何制作这种具有动态交互功能的图表呢？包含：迷你图、动态图、展示数据、切片器等。
这就要用到多个组件之间的相互联动了。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
9.3 数据透视图的使用限制
当然透视图并不是万能的，还是存在着一些限制的，例如透视图有3种不能创建的图表类型。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
10 实战案例
10.1 在财务数据中的应用
可以利用透视表的多重合并区域来将多份表单进行汇总，从而达到按照月份、季度、上下半年、年度的方式快速汇总。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
10.2 在销售数据中的使用
如何利用透视表去挖掘销售数据中隐藏的信息呢？通过鼠标简单的拖一拖拽一拽就可以完成非常高级的仪表盘数据，并且还具备交互功能。
学习传送门： Excel就该这样用，数据透视表篇 - 网易云课堂
暂时分享这么多，想要学习【数据透视表】的也可以关注下我哦~
本文首发于公众号： 芒种学院 ，未经允许，禁止转载。