都进来看看!这招以一敌五的Excel绝技,连透视表都甘拜下风!
发布时间:2023-10-06 00:50:45
在武侠小说里,经常会有一种情况——带着主角光环的人,总是凭借“一招鲜,吃遍天”的“伟大漏洞”,处处逢凶化吉。都说“职场如战场”,来到了职场,每一位EXCELER,面对来自四面八方的工作压力,也算是“处处逢凶”了。那么能不能也有这么“一招”,让同学们“随时化吉”呢?跟上E图表述的步伐,来学习一招“以不变应万变”的绝技吧。
【正文】
不多废话,切入主题。同学们知道在日常表格中,有一种信息内容叫做“序号”吗?我们今天就来“玩”这个序号,我保证你会“爱”上这个“小操作”的。
添加序号的方法
在日常生活中,我们每个人添加序号的方式可能都不一样,主要归纳起来有下面几种。

这是较常规的几种添加序号的方法,但是也仅限于“常规”。而我们今天要学的内容,就是非常规的序号,而这样的序号可以给我们带来“前所未有且简单”的体验。
1“动态”提取不重复的值并统计

遇到这种情况,大部分同学会使用两种方法解决:
第一种:首先复制粘贴出E列内容,然后在“数据”选项卡中,点击“删除重复项”功能键,再用SUMIF函数求和。
B22单元格函数:
=SUMIF($E$2:$E$16,A22,$F$2:$F$16)

第二种:直接使用数组函数,得到不重复的费用类别,再使用SUMIF函数求和。

A22单元格函数:{=IFERROR(INDEX($E$2:$E$16,SMALL(IF(MATCH($E$2:$E$16,$E$2:$E$16,0)=ROW($1:$15),ROW($1:$15),99^9),ROW(A1))),””)} 输入完成后,按数组函数的结束键CTRL+SHIFT+ENTER三键结束。 你会选择哪种呢?如果说我们需要在源数据更新后,也能实现自动统计的话,肯定是选择函数的做法,但是这个数组函数真的不是初学者能够驾驭的(上面的数组函数不是今天的主题,故不作展开说明),下面我们就用序号的方法来处理这个问题。

步骤1:在数据首列前插入一列“辅助列”,在A2单元格输入函数:=IF(COUNTIF($F$2:F2,F2)=1,MAX($A$1:A1)+1,””),下拉填充得到被引用的序号。这里使用COUNTIF函数结合绝对引用,使区域中的首个单元格固定,末单元格逐步扩大,通过IF函数判断,如果COUNTIF返回1,即为目标值第一次出现,再使用MAX函数结合绝对引用,累加出唯一出现的值所对应的顺次; 此类引用方法在之前的教程中介绍过,小伙伴们可以点击教程《同样是countifs函数,为什么同事却使得比你好?原因在这里!》学习,此处不做赘述了。 步骤2:在B22单元格输入函数:=IFERROR(VLOOKUP(ROW(B1),$A$2:$F$16,6,0),””),下拉填充函数后,引出不重复的费用类别。使用ROW函数得到序号,用VLOOKUP逐步引出这个序号对应的费用类别,当ROW函数的序号在索引区域中没有出现时,用IFERROR函数规避错误值#N/A; 步骤3:在C22单元格输入函数:=IF(B22=””,””,SUMIF($F$2:$F$16,B22,$G$2:$G$16)),当对应的B列内容不为空时,汇总各个费用类别的金额。 这样一来是不是简单了很多?而且是随数据源的更新而变动的哦~

2
“动态”分类提取明细
按照上面的思路,我们再看一个工作中经常会遇到的问题。如下图所示,我们需要按照不同的费用类别,提取出对应的明细数据。

步骤1:同样在数据首列前插入空白列。在A2单元格输入函数:=IF(F2=$G$19,MAX($A$1:A1)+1,””),下拉填充公式,得到满足条件的记录序号。当数据中的费用类别和被统计项G19单元格(注意使用绝对引用)相同时,利用MAX函数标记序号。 步骤2:在B22单元格输入函数:=IFERROR(VLOOKUP(ROW(B1),$A$1:$G$16,MATCH(B$21,$A$1:$G$1,0),0),””),下拉右拉填充公式。这里依然是使用VLOOKUP函数索引ROW函数,再利用MATCH函数,找到表头的顺序号,作为VLOOKUP在索引区域中被索引的列序。当然,这个表格依旧是可以实现动态更新的。

3
“动态”多条件提取明细
同样的思路再来“玩”一个多条件的索引。如下图所示,需要提取出满足多个条件的明细数据。

步骤1:同样在A2单元格输入函数:=IF(AND(C2>=$D$21,C2<=$D$22,E2=$F$21,F2=$F$22),MAX($A$1:A1)+1,””),使用AND函数,使IF函数形成多条件同时满足与否的判断,标记出多条件都满足的明细记录序号。 步骤2:在B25单元格输入函数:=IFERROR(VLOOKUP(ROW(B1),$A$1:$G$16,MATCH(B$24,$A$1:$G$1,0),0),””),此函数同案例2的函数是一样的,我们就不多介绍了。看一下动态的效果吧。

4
“动态”将明细按金额大小排序

给一列数字排名,估计很多同学都知道用RANK函数吧,但是如果有重复的数字,就需要RANK+COUNTIF函数的嵌套使用,起到不重复排名的效果,这个案例就是利用了这个原理。 步骤1:在A3单元格输入函数:=RANK(D3,$D$3:$D$17,IF($H$1=”降序”,0,1))+COUNTIF($D$3:D3,D3)-1,先通过RANK函数得到数值的排名,用IF函数判断H1单元格的数据,如果是“降序”则返为“0”,如果是“升序”或者单元格为空,则默认返回“1”。COUNTIF函数是去重排名的关键,同样是利用绝对引用固定区域中开始单元格的位置,确定数字出现的次数,如果第一次出现,1-1=0,则直接返回RANK函数得到的排名。第二次出现就是2-1=1,则在RANK函数排名的基础上+1。这样就完成了重复排名增加1位次的过程。 步骤2:在F3单元格输入函数:=IFERROR(VLOOKUP(ROW(F1),$A$2:$D$17,MATCH(F$2,$A$2:$D$2,0),0),””),这个函数的原理依然同案例2的索引过程。 通过选择升序降序的选项,做到动态罗列数据的过程,如下:

5
“动态”插入空白行
对于这个需求,现下最常用的方法是“添加序号排序的方法”,如下:

但是这种方式有一个弊端,如果插入的空白行不固定,那就需要频繁的操作,而且序号的粘贴过程也比较麻烦,插入几行就要复制几次序号。所以我们就一起来看一个函数+序号动态插入空白行的案例。

步骤1:在A列给源数据表添加序号,在A3单元格输入函数:=ROW(A1),然后下拉公式填充。 步骤2:在F2单元格输入“辅助”,在F3单元格输入函数:=IF(COUNTIF($F$2:F2,F2)<$I$1+1,MAX(F2:F2),F2+1),依然是利用了COUNTIF结合绝对引用的做法,判断从起始单元格F2到上一个单元格为止的区域中,上一个单元格的值出现了几次,如果值小于间隔行数+1,就显示已经出现的最大序号(用MAX函数引出),否则序号累计1,就是函数中F2+1的运算。然后下拉填充函数,如果数据较多,这里介绍一个利用“名称框”代替下拉填充的过程。

步骤3:在G3单元格输入函数:=IFERROR(IF(AND($F3<>””,$F3<>$F2),VLOOKUP($F3+1,$A$2:$D$17,MATCH(G$2,$A$2:$D$2,0),0),””),””),使用AND函数,使IF函数形成多条件同时满足与否的判断。当F3单元格不为空,且F3单元格不等于上一个单元格的值的时候,使用VLOOKUP+MATCH函数的嵌套引用明细表内容。填充函数后就是下面的效果了。

“辅助列”在原数据中,一般在首列之前,或者末列之后。目的是在不改变原数据结构的基础上,为了解决一些不能通过原数据直接处理的数据分析要求,而添加辅助计算的信息内容。


猜你喜欢
- 在使用word文档时很多小伙伴会发现在文档侧边有一个侧边栏,想要关闭这个侧边栏大家之都如何操作吗?一起来看看word怎么永久关闭侧边栏呢?小
- 身处这个时代 ,我们比以往的任何一个年代都离不开网络。没有了网络很多操作都无法进行,没有了网络不能玩游戏,不能在线看视频。但是最近有朋友表示
- Win7系统关机总卡在正在注销怎么办?最近有用户发现,电脑每次关机的时候都会卡在正在注销的界面,如何解决?请看下文具体解决方法。解决方法:1
- 在公式中添加一些序号也是非常常见的,当然如果是为整个文档中的公式进行编号,就要使用MathType公式编号的功能,但是有时候公式内部会使用一
- 这篇文章主要介绍了电脑升级win11后怎么退回win10?win11退回win10系统操作方法的相关资料,需要的朋友可以参考下本文详细内容。
- 怎么把excel单元格内顿号分的内容提出来?具体该怎么去操作的呢?今天,小编就教大家在Excel中单元格内顿号分内容提出来的操作方法。Exc
- 苹果Mac上如何设置延长屏幕锁屏时间?小编教给大家设置方法,来看看吧!1.在屏幕左上角点击苹果图标,打开“系统偏好设置”。2.在系统偏好设置
- 在电脑中,有时会遇到软件打不开的情况,如在打开QQ等软件时报错,提示应用程序无法启动因为并行配置不正确,应用程序无法启动因为并行配置不正确怎
- 欢迎观看 After Effects 教程,小编带大家学习 After Effects 的基本工具和使用技巧,了解如何在 AE 中使用形状图
- Win8任务管理器经过了大改变,不但可以显示基础信息,还能增加“进程句柄、线程、用户对象”等等,下面教大家如何将其调出来,感兴趣的朋友可以看
- 我们经常用WPS表格完成一些表格上的处理,比如课程表,这些需要用到斜线,那这时我们该怎么办呢?下面小编为大家介绍如何在WPS表格中制作出斜线
- 与win7、win8系统相比,win10系统自带更新功能。包含系统更新、驱动更新及应用更新。win10系统是默认更新的,在默认的情况下会自动
- 打印机驱动是针对打印机运行的程序,在用户更换或者升级打印机后,建议把旧的打印机驱动进行卸载以免影响兼容,那么要怎么卸载才能干净呢?来看小编分
- 通常当我们浏览一些网站时,我会发现我的鼠标右键被锁定,无法复制和部分操作,如何解决呢?以下是具体方法如下:教程/方法第一种方法:1,我们必须
- 在使用Word进行文档编辑的时候使用了页面边框,在编辑的窗口中显示正常,但在打印预览中,页面边框无法显示或者显示不完整,打印出来后也是如此。
- 对称页边距是指设置双面文档的对称页面如图对称页边距中码2所示的页边距。例如书籍或杂志。在这种情况下,左侧页面的页边距是右侧页面页边距的镜像(
- wps是 甚么 意义?wps代表 甚么? 实在 许多人都不 晓得, 实在wps是有两个 意义的,下载吧小编就 来说讲有哪些 意义。一、在软件
- 电脑上不了网,我们一般会检查网络连接设备是否连接上了,是断网了还是什么情况的。在打开网络连接设备的时候,发现“调制解调器已删除”了,都没有去
- Excel中经常需要使用到公式锁定的功能,公式具体该如何进行锁定呢? 今天,小编就教大家在Excel中公式锁定的方法。Excel中公式锁定的
- Excel表格中含有大量的公式,默认情况下公式是隐藏的。方法一:第一步:点击菜单中的【公式】第二步:点击【显示公式】按钮第三步:瞧!公式出现