编按:哈喽,大家好!看到标题相信大家都会纳闷吧。复制粘贴不是excel中最简单,最基础的操作吗?怎么可能90%的人都不会呢?今天咱们要说的可不是普通的复制粘贴,而是将数据复制粘贴到筛选后的区域中。说到这里可能又有小伙伴们疑惑了,“小编,你可别逗我,数据是不能直接复制粘贴到筛选后的区域中的,这可是常识!”别着急,跟着小编继续往下看,你就知道了!
************
如果我对你说,我可以将一个连续区域中的值,快速粘贴到筛选后不连续的区域中,你信吗?例如要将下图右侧连续的“目标值”列粘贴到筛选过后不连续的“目标粘贴区域”列中。
刚好今天呢,我在公司做了一个实验,问了同事们上图这样的问题,同事们给我的回答都是出奇的一致,好像是商量好似的,都说“怎么可能啊,谁都知道筛选后的区域,是无法直接再复制粘贴数据过去的”,我:“额…一个白眼,自行体会”。
相信这也是大多数人的看法,不过方法还是有的。
是不是有点小好奇,这是怎么做到的?想学的话,就继续往下看吧。
为了有对比性,我们先来试一下大家都会的CTRL+C、CTRL+V,直接复制粘贴后的效果:
什么鬼?只显示了一个数字,那其他数字呢?让我们把筛选结果全部展开来看一下。(如下图所示)
哦,原来隐藏掉的部分也会被复制粘贴啊!
试验结论:此路不通。
看来还真没有那么容易,要好好动动脑筋才行。所谓思路才是王道,有时候思路比方法更重要,让我们一起来好好分析一下吧:
我们的目标:需要把一列连续的数据,直接复制粘贴到筛选后的区域中。其中被筛选掉的部分不需要被复制粘贴到。
目前的障碍:无法直接进行复制粘贴,被筛选掉的数据也会被复制粘贴到。
想办法解决:既然不能直接复制粘贴,那我们不妨换一种思路,从复制粘贴的定式思维里跳出来,重新审视这个问题。仔细分析,其实这也是个“查找”的问题啊,我们直接把那几个数查找过来不就成了!
等一下,什么,查找?这路子一下子就变宽了。查找有一系列给力的函数,比如(V)LOOKUP、INDEX等,其中VLOOKUP不能直接使用,因为不满足它的查找条件,除非使用辅助列在两个区域左边各加一列相同字段,如1/2/3等,就可以实现。但是太麻烦,不能满足我们想立马粘贴的需求。
那看看INDEX吧,哎呀哈,貌似还可以。INDEX的语法是:INDEX(array,row_num,column_num) ,用于在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。关于INDEX详细的使用方法,小伙伴们可以参考之前的教程《INDEX:函数中的精确制导导弹,最强大的瘸子》,此处不做赘述了。
我们来应用一下,得出了第一个数,但还不能直接往下拉得到后面的结果。
这该怎么解决呢?我们先把这个函数剥开,会发现查找区域$C$12:$C$14是固定的,列数也是固定的1列,只有行数是不固定的,所以只要把行数搞定就可以了。
现在开始专攻行数。行数是个变量,本例中的行数分别为1、2、3,如果我能用一个函数,让它直接返回行数1、2、3,而且可以忽略隐藏行不计,那就好了。
真有这样的函数吗?答案是,当然有!
那就是大名鼎鼎的SUBTOTAL函数。为什么说它大名鼎鼎,因为SUBTOTAL函数是excel中唯一一个能忽略隐藏单元格,统计用户可见单元格的函数,具有不可替代性。正好是我们今天所需要的。
SUBTOTAL的语法是SUBTOTAL(function_num,ref1,ref2, ...),function_num所对应的功能参数有很多,整理后如下图所示。关于SUBTOTAL详细的使用方法,小伙伴们可以参考之前的教程《以一敌十的SUBTOTAL函数,你怎能错过?》,在此就不细说了。
因为是统计筛选后的结果,所以选择参数103或者3都是可以的。若不是统计筛选后的结果而是统计隐藏行后的结果,就只能用103了。接下来本例会用参数103来进行演示和说明。
我们将上面的INDEX公式中,代表行数的“1”用SUBTOTAL替换,就是=INDEX($C$12:$C$14,SUBTOTAL(103,$B$3:B3),1),结果如下,大功告成。
为了方便大家更好的理解,带大家来操作一下,可以跟着一起做哦,请看下方动图演示:
以上就是今天的内容,我们将看似不可能的事情变成了可能,最重要的就是学会动脑筋,然后运用excel解决问题。小伙伴们赶紧打开excel,来练习一下吧。