- ?
EXCEL 2016只需30秒更换数据源进行其他表数据透视分析
浑惜珊
展开
一、已经完成的数据透视表,表名为TS001,移动复制,建立副本打√,选中TS001,如下图:
二、新建数据透视表表名为TS001(2),分析-更改数据源-选择一个表或区域,由于我是选定的002的表名,所有的数据表都是统一的列内容,告诉大家个技巧直接把'001'!$A:$R手动输入更改'002'!$A:$R,确定后,马上生成数据透视内容,不用再进去重新选择行列了。如下图:
- ?
Excel函数公式:含金量超高的Excel2016新增函数IFS等实用技巧
鸽子
展开
经常使用Excel的同学们都知道,Excel的功能非常的强大,如果用一句话来概括,那就是:只有你想不到的,没有她办不到的。但在实际的使用中,80%的人对于函数公式以及数据的掌握少之又少,所以,Excel的威力大打折扣,但是Excel2016新增的下述函数,不仅好用,而且还非常的方便,简单。
一、IFS函数:检测是否满足一个或多个条件条件并返回与第一个TRUE条件对应的值。
语法结构:=IFS(条件1,值1,条件2,值2……条件N,值N)。
目的:根据成绩判断等级。
1、IF函数嵌套法。
方法:
在目标单元格中输入公式:=IF(D3=100,"满分",IF(D3>=95,"优秀",IF(D3>=80,"良好",IF(D3>=60,"及格","不及格"))))
2、IFS函数法。
方法:
在目标单元格中输入公式:=IFS(D3=100,"满分",D3>=95,"优秀",D3>=80,"良好",D3>=60,"及格",D3<60,"不及格")。
解读:
从IF函数嵌套和IFS的公式对比中,我们可以看出,IFS实现起来非常的简单,只需要条件和值成对出现就可以了。而IF嵌套就括号是不是看着很头晕呢?如果条件非常的所,是不是IFS的优势就更加明显了。
二、MINIFS函数:返回指定条件下的最小值。
语法结构:=MINIFS(返回值所在的区域,条件1区域,条件1,条件2区域,条件2……条件N区域,条件N)。
目的1:求男、女同学的最低分。
方法:
在目标单元格中输入公式:=MINIFS(D3:D9,C3:C9,F4)。
目的2:求及格男、女同学中的最低分 。
方法:
在目标单元格中输入公式:=MINIFS(D3:D9,C3:C9,F4,D3:D9,">=60")。
解读:
1、从上述两个示例中我们可以看出,MINIFS函数的条件个数是可以增加的,所以应用起来非常的方便。
2、求目的1的最低分可以用公式:=IF(C3:C9=F4,MIN(D3:D9))实现。而目的2的可以用公式:=IF((C3:C9=F4)*(D3:D9>=60),D3:D9)来实现。但需注意的是必须用Ctrl+Shift+Enter填充,而不是用Ctrl+Enter来填充。
三、MAXIFS函数:返回指定条件下的最大值。
语法结构:=MAXIFS(返回值区域,条件1区域,条件1,条件2区域,条件2……条件N区域,条件N)。
目的1:求男、女同学的最高分。
方法:
在目标单元格中输入公式:=MAXIFS(D3:D9,C3:C9,F4)。
目的2:求不及格男、女同学的最高分。
方法:
在目标单元格中输入公式:=MAXIFS(D3:D9,C3:C9,F4,D3:D9,"<60")。
解读:
1、从示例中可以看出,MAXIFS的条件个数也是可以增加的,这就大大的提高了应用的便利性。
2、求目的1的最高分可以用公式:=IF((C3:C9=F4),MAX(D3:D9))来实现,目的2的最高分可以用公式:=IF((C3:C9=F4)*(D3:D9<60),D3:D9)来实现。但需注意的是必须用Ctrl+Shift+Enter填充,而不是用Ctrl+Enter来填充。
四、CONCAT函数:将多个区域或或字符串中的文本引用组合起来。
语法结构:=CONCAT(字符串或单元格区域的引用)。
方法:
在目标单元格中输入公式:=CONCAT(B3:F5)。
解读:
1、用公式:=B3&C3&D3&E3&F3&B4&C4&D4&E4&F4&B5&C5&D5&E5&F5也可以实现单元格内容的合并,但是非常繁琐。
2、用CONCAT合并单元格内容只需填写范围即可,非常的方便。
结束语:
从上述的的示例当中,我们可以看出,Excel 2016的新增函数使用起来非常的方便,虽然功能用以前的版本也可以实现,但是效率不高,容易出错。
如果有问题请在留言区留言讨论哦!
- ?
excel 2016下拉菜单关联这样做
傲芙
展开
“在excel2016中设置联动下拉菜单”的操作步骤是:
1、以Excel 2016为例,打开Excel工作表;
2、在F:H列以一级菜单为标题,二级菜单纵向排列的方式建表;
3、选择一级菜单设置区域,在“数据”选项下的“数据验证”中,“设置”选项选择“序列”,来源选择F1:H1单元格,确定;
4、选择F:H列,在“公式”选项下的“根据所选内容创建”,在“选定区域创建名称”对话框中,勾选“首行”,按“确定”,完成创建名称;
5、选择二级菜单设置区域,在“数据”选项下的“数据有效性”中,“设置”选项选择“序列”,来源输入=INDIRECT(A2) 确定;
6、在一级菜单设置区域,点击下拉选项按钮,选择相应的菜单内容;
7、在二级菜单设置区域,点击选项按钮,会弹出与一级菜单相对应的二级联动菜单,供选择。
8、整个设置过程中,按照“一级菜单为标题,二级菜单纵向排列的方式”建立表格格式是关键,方便根据所选内容创建名称,并通过INDIRECT引用创建的名称,生成二级联动菜单。
(本文内容由百度知道网友AHYNLWY贡献)
- ?
Excel 2016直方图使用指南
lecn625
展开
图/文 | 安伟星
直方图是用于展示数据分布情况的图表,经常用于数据统计中。
它可清晰地展示出数据的分类情况和各类别之间的差异,为分析和判断数据提供依据。
01创建直方图
创建直方图,只用一列数据即可,直方图的分类标签是根据数值的大小进行自动分组。比如对一组测试数据,我们想要考察测试分数的分布情况。
①选择数据,选中上图所示的数据区域;
②单击“插入”>“插入统计信息图表”>“直方图”。
默认生成的直方图是个样子的。到这里,一定会有几个疑问:
①这些柱子的个数是怎么决定的?
②横坐标轴代表的含义是什么?
③这个图能够说明什么问题?
带着这些问题,我们继续学习。
02配置直方图箱
右键单击图表的水平坐标轴,单击“设置坐标轴格式”,然后单击“坐标轴选项”。
直方图的核心就在“横坐标轴”选项中,这里官方的说法是“箱”,关于“箱”的设置,有六个选项。
这六个选项的使用方法,总结如下表所示,Excel默认生成的直方图是自动模式,此时箱宽度通过使用 Scott 正态引用规则计算(1)。
我们通过自定义箱宽度的方式,设置每个箱子的宽度。其实这个宽度指的是数值的分组跨度,输入20,那么这组数据将按照20的间隔进行分组。
数据分组的间距越窄,图表拆分的箱型就越多,分析的区间就越精准。但是这个区间不是无限度的细分的,否则就失去了统计的意义了。
从图中可以明显看出,分数处于在(60,80]之间的最多,(80,100]之间的最少。通过成绩的分布,可以分析出整体水平、测试题目的难易度等等,从而为我们的决策做依据。这就是直方图的最典型的意义。
03帕累托图
帕累托图(Pareto chart),以意大利经济学家V.Pareto的名字而命名,在反映质量问题、展现质量改进项目等领域有广泛应用。
它是按照发生频率大小顺序绘制的直方图,表示有多少结果是由已确认类型或范畴的原因所造成。
从帕累托图的定义中,可以知道,将直方图进行排序,基本上就变成了帕累托图,同时帕累托图还有一条表示累计百分比的折线。因此可以说,帕累托图是直方图的增强版。
举例说明
▼
比如有六项因素能够影响测验成绩,选中此数据表。
单击“插入”>“插入统计信息图表”>然后选择“直方图”右侧的“排列图”。
然后,你看Excel 2016竟然可以一键生成帕累托图,而且还可以自动对项目按照值的大小进行排序,自动计算累计百分比。
①图中的柱形代表每个因素的频数(出现的次数);
②折线图是累计百分比,最终值一定要落在100%上;
与帕累托图紧密相关的是二八法则,在这里体现在“影响结果80%的因素,集中在所有因素的20%里面”,所以集中力量解决这20%的因素,就能将成绩(或者品质)提升80%。
▌注:(1)用于在 Excel 2016 中创建直方图的公式,自动选项(Scott 正态引用规则)
·END·
- ?
使用Excel2016才发现瀑布图如此简单
甄丑
展开
瀑布图是形似瀑布的图表,它是用来展示一系列增加值或减少值对初始值的影响,可以直观的反映数据增减变化的图表,这个图表在职场中很多场景都可以应用,Excel2016版本开始,图表中增加了瀑布图,可以不用原来稍微复杂的表格和图表操作了,我们可以很简单的生成瀑布图。
STEP 1:准备数据
如下图,B5单元格公式为=SUM(B2:B4),B9单元格公式为=SUM(B5:B9)。
STEP 2:生成图表
选中A1:B9数据区域,【插入】选项卡选择【瀑布图】,初步生成的图表下图可见。
STEP 3:美化图表
生成的图表,图例是自动显示三项的,分别为"增加、减少、汇总",其中正数自动设置为增加,负数自动设置为减少,汇总需要我们去指定。
在"实发工资"数据点两次单击,注意不是双击,然后再右键单击,选择"设置为汇总"。
同样道理将"实发工资"数据点设置为汇总项,完成后如下图。
再进行美化,最终效果如下:
默认的瀑布图是带连接符线条的,我们右键单击数据点,设置数据系列格式,可以将其去除。
- ?
Excel 2016的逆天功能,好用到飞起来!
穆菲音
展开
今天介绍Excel里面两个强大到逆天但很多人还不知道的功能,不信你看看?
一、二维表转一维表
如下图所示,如果你在工作中遇到这种表格转换需求,会怎么做呢?
思考10秒钟......
我见过最多的办法就是......复制粘贴,没错,大量重复繁琐操作最后搞的人头晕眼花脑发胀!
看完下面这招,不花1分钟你就可以轻松搞定。
步骤1:单击表格内任意区域,单击【数据】-【从表格】
步骤2:选中最左列,单击【转换】-【逆透视其他列】
步骤3:这时你会惊奇的发现,Excel变魔术一样的得到了如下图所示的表格!
这时只需单击【开始】-【关闭并上载】即可完成。
步骤4:得到如下所示的表格,正是你想要的。
整个过程的操作演示动画,请点击如下gif观看
你没看过,平时那么繁重的工作,用Excel 2016就这么快捷!
如果你觉得Excel 2016只有这点本事的话,那你可太小看它啦,下面来个更逆天的,让你大开眼界!
二、拆分复杂数据列并整理为规范表格
如果要你把下图左侧的表格变成右边的样子,你该怎么办呢?
按空格分列然后一个个粘贴?数组公式?VBA编程?
别想啦,有了Excel 2016,分分钟搞定它,看步骤吧。
步骤1:选中表格中任意单元格,单击【数据】-【从表格】
步骤2:选中需要拆分的列,单击【拆分列】-【按分隔符】
步骤3:选择按空格拆分,单击确定按钮。
拆分后的样子如下图所示
步骤4:选中作者所在的多列,单击【转换】-【逆透视列】
Excel大变魔术,瞬间得到如下图所示的表格
步骤5:这时只需将没用的列删除即可
得到想要的表格后,单击【开始】-【关闭并上载】,大功告成
得到想要的表格
整个操作过程其实很简单,30秒足以搞定,点击以下gif动态图片即可查看
Excel是不是比你想象中更强大,这些技巧既简单又实用!
- ?
excel2016怎么做折线图
阿鹏
展开
一、工具
电脑一台
Excel 2016
二、方法/步骤
1、选中数据区域——点击插入——选择所需图表类型,然后进入图表美化。
2、点击图表工具——格式——垂直(值)轴——设置所选内容格式。
3、设置坐标轴格式——坐标轴选项。
主要刻度线数目=(最大值-最小值)/主要刻度单位值(小于6-7个为最佳)
如图所示,最小值为0,最大值为100,单位为20,刻度数目为5.
4、垂直(值)轴主要网格线:
设置主要网格线格式——线条(实线)——颜色(黑色,淡色35%)——短划线类型(短划线)
网格线装饰(一般为虚线 ,在柱子上有数据标签时删除,或者数据较少时)
5、绘图区
选中(右击)——添加数据标签
设置数据标签格式——纯色填充,可以根据需要自行设置。
6、水平(类别)轴
设置坐标轴格式——刻度线标记——主要类型(内部)。
这里数据项较多,要有刻度线标记,使图表分类项更容易分辨。如果数据项较少,去掉刻度线,使图表跟简介美观。
设置坐标轴格式——线条(实线)——颜色(黑色)。
设置坐标轴格式——对齐方式——文字方向——堆积。
注意:
折线图的使用范围:未分组或者分组数量过多的情况;在相等时间间隔下数据的发展趋势。
(本文内容由百度知道网友彼岸的暗夜贡献)
- ?
使用Excel-2016你应该知道的小技巧,完美简单实用!
巴拉科阿
展开
今天分享一个不用函数不用公式也不用VBA,可以直接生成工资条的小技巧,我们来看一个例子。
将上图工资表生成下图的工资条,如何实现的呢。
具体操作做步骤:
1、将第一行复制到7至11行,如图:
2、对E列进行操作,分别在E2至E6单元格和E7至E11单元格内填充数字1-5,如图:
3、选中A1到E11区域,点击“数据”选项卡下的“排序”按钮
4、打开“排序”窗口,“主要关键字”选择(列E),“排序依据”选择“数值”,“次序”选择升序
点击确定后,回到这个结果,如下图:
5、删除E列 和11行,这样工资条就做好了。
其实,关键在于Excel“筛选”功能的运用,只要E列的数据输入正确,就可以生成完美的工资条。
- ?
怎么将excel2016设置为默认程序
虞楷瑞
展开
“将excel2016设置为默认程序”的操作步骤是:
1、打开安装有Office 2016办公软件的电脑;
2、在Excel文档上,点击鼠标右键,点“打开方式”;
3、如果Excel 2016已经是默认方式,那么将出现在“打开方式”列表中;
4、如果Excel 2016不是默认的打开方式,那么点击“选择默认程序”;
5、在“打开方式”中,选择“浏览”;
6、在C:\Program Files\Microsoft Office\Office16路径下找到“Excel”程序,选择“打开”;
7、勾选“始终使用选择的程序打开这种文件”,确定,即可完成以Excel 2016作为默认程序打开Excel文档。
(本文内容由百度知道网友AHYNLWY贡献)
- ?
Excel函数公式:Excel2016新增逆天功能,非常的好用
吉靖琪
展开
大年三十了,给大家拜年了,新的一年里万事如意,同时感谢大家一年来的支持和厚爱。祝大家新年快乐,狗年大吉,财源滚滚……
再难的问题,也会有解决的办法,达到最终的目的。但是,解决的方法,思路不同,达到目的过程也会不同。例如,Excel2016新增的下属功能,为我们的工作带来了极大的方便。
一、快速将二维表转换为一维表。
方法:
1、选定表格中的任意单元格。
2、【数据】-【从表格】,单击【表数据的来源】右侧的箭头,选定需要转换的范围(不能包括表头),【确定】。
3、在弹出的新窗口中单击【转换】-【逆透视其他列】-【开始】-【关闭并上载】。
4、调节结构并美化。
二、利用逆透视快速拆分数据。
方法:
1、选定表格中的任意单元格。
2、【数据】-【从表格】,单击【表数据的来源】右侧的箭头,选定需要转换的范围(不能包括表头),【确定】。
3、选中“内容”列,单击【拆分列】-【按分隔符】,在【选择或输入分隔符】下拉列表中选择【空格】-【确定】。
4、按Ctrl键选中所有“内容”列,【转换】-【逆透视列】。
5、删除“属性”列,美化表格。