大貓閒聊--excel中如何快速提取兩列中的相同數據

要处理的问题类型,如图1所示:

图1


图1中有两列数据,如何快速识别出两列的相同项,并提取出来。


下边猫哥就教你们怎么装×


同样,高阶的装×行为需要高阶的技能,此处就需要利用数组,能否熟练应用数组,是一个excel猎手进1阶的标志。


此次共要达到如图2所示的3种效果:

图2


第1种:提取出左列独有的项目

第2种:提取出右列独有的项目

第3种:提取出双边都有的项目


第1种解答:提取出左列独有的项目


在E3单元格中输入公式(同时按Ctrl+shift+enter键,然后下拉)

=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)=0,ROW($B$3:$B$22),1000), ROW(A1)))&""


公式解析:

先拆:

第1层:index(),也是最外边的一层

第2层:small()

第3层:if()

第4层:countif()最里边的一层


从里到外:


countif函数,COUNTIF(C:C,$B$3:$B$22)=0,这里即用到数组,即c:c是备查找的区域,$B$3:$B$22是要查找的目标值组合,此处用数组代替以前你们常用的单个单元格的值,即判断数组$B$3:$B$22中的每一个单元格的值在区域C:C中是否有数,即如果都没有,则返回false,因为false参与计算是值为0。


注意,看黑板,重点来了


数组的一个特性就是逐一判断,比如上边提到的这个公式:

COUNTIF(C:C,$B$3:$B$22)=0,即是先判断b3单元格1猫在c列中是否有对应的值,如果有则判断一次,同时if函数也判断一次,返回值集合见图3:


图3


因为1猫在c列中不存在,故countif函数结果为0,if函数返回ROW($B$3:$B$22),对应位置的数组值为3,同理推敲至b4值2猫,在c列中有对应的值,则countif函数结果不为0,则if函数返回值为1000,如上图所示,依次类推。



if函数,这个就简单了,如果COUNTIF(C:C,$B$3:$B$22)=0成立,则返回数组ROW($B$3:$B$22),否则返回值1000(这个1000是随便设定的,只要大于数组的元素数即可,比如数组ROW($B$3:$B$22)的元素个数是20,1000大于20了)。


此处仍然有一个数组ROW($B$3:$B$22),这个数组返回值为如图4所示:

图4


如何理解呢?建议去单独学习一下数组,这里简单介绍一下,数组无法在单元格中单独全部显示,单元格只能显示出一个元素值,如果要全部显示数组的值,需要根据数组的维度,选择对应的区域,同时按Ctrl+shift+enter键,完成输入,之后你会看到函数中会出现{}这个大括号,即为数组形式,手动敲一下就明白了。


small函数:

语法small(数组,第n个最小值)

small函数是专用于数组计算的,即返回数组中的第n个最小值


row(a1),是辅助用于生成small函数中的n,用以参与计算数组元素的取值



但是此例子中,参与small函数判断的是数组

IF(COUNTIF(C:C,$B$3:$B$22)=0,ROW($B$3:$B$22),1000), ROW(A1)),该数组的值见下图5所示:

图5


small(上述数组,row(a1)),返回值为3,因为

row(a1)=1,所以返回数组中第1个最小值为3。

接着判断small(上述数组,row(a2),因为row(a2)=2,则返回上述数组中的第二个最小值,级为13,依次类推。最后生成的数组为下图所示:


index函数:

index()返回目标区域的目标位置的值,small函数生成的值为3,则返回在目标区域中的位置3,即第3行,即1猫


最后公式后边&"",是为了将0转化为空值,美化视图,如果不加这个,空单元格的返回值是0,不加这个符合也无所谓。



第2种解答:提取出右列独有的项目

在如图所示f3单元格输入((同时按Ctrl+shift+enter键,然后下拉)

=INDEX(C:C,SMALL(IF(COUNTIF(B:B,$C$3:$C$12)=0,ROW($B$3:$B$12),1111),ROW(A1)))&""


具体逻辑同理第一种方法,只是将查找区域与查找值调换个位置,比如index函数的查找区域有b:b变为右列的c:c,同理countif函数中的查找值、查找区域一样调换一下,仔细比较一下即可,此处不做详细讲解。





第3种解答:提取出双边都有的项目


这个与上述两种方法变动有点大。大体逻辑也是一样的。


按照此例子excel模板图6所示:

图6


在g3单元格输入(同时按Ctrl+shift+enter键,然后下拉)

=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)>0,ROW($B$3:$B$22),1000), ROW(A1)))&""


此公式中,将countif函数改为>0,而不是等于0,即改为判断目标值数组$B$3:$B$22中的元素是否在目标区域中存在,存在则>0成立,返回对应的数组值,继续重复方法1中的逻辑。


相關文章

深入理解Excel的vlookup函數

2021-07-10

對於Excel的使用,可以分為幾個層次:I只涉及到簡單公式的數據處理,包括各種篩選的使用;II涉及到一些複雜公式,包括if、vlookup等函數的使用;III數據透視表的使用;IVVBA的使用。其中,vlookup的使用還是有一定的複雜度的。lookup自然

VLOOKUP很難理解?或許你就差這一個神器

2021-07-13

來源:數據STUDIO作者:雲朵君一說到Excel查找函數,你一定會想到VLOOKUP函數,雖然它是最基礎實用的函數,但每次一看就會,一用就忘。接下來給大家分享一個VLOOKUP函數動態圖解,記得收藏它哦,在每次使用VLOOKUP函數時,把它拿出來一看就會用,不用

COUNTIF函數用法大全

2021-08-19

COUNTIF函數用於統計滿足某個條件的單元格的數量,其基本用法為:COUNTIF(統計區域,指定的條件)如下圖所示,要統計A列有幾個5,可以使用以下公式:=COUNTIF(A2:A10,5)咱們把COUNTIF函數的第二參數稍加改動,就可以衍生出很多高效用法,例如

Excel公式技巧107:將表數據轉換成列數據(續)

2021-09-13

學習Excel技術,關注微信公眾號:excelperfect在《Excel公式技巧106:將表數據轉換成列數據》中,詳細解析了一位網友問我的問題的解答過程。然而,事情並沒有完。上次提供的示例數據太完美了,所以實現起來相對簡單。在上次的解答之後,該名網友又提出了一個比

關於Countif,不單是單條件計數,還有這3個高能用法

2021-09-14

關於函數Countif,大多數的親和小編一樣,都認為它是計數函數,其實這個認識沒錯,Countif函數確實是計數函數,但其衍生出來的3個高能用法,才是精髓。一、Countif——基本用法功能:計算指定區域中滿足指定條件的單元格個數。語法結構:=Countif(條件範

Excel|一個小實例(從文本中提取數字)瞭解數組及數組公式

2021-09-21

Excel工作表可以理解為一個由行、列組成的二維數組。Excel公式支持數組和數組公式。數組可以是常量數組,也可以是單元格區域的引用。使用數組和數組公式時,可以逐數組元素或逐單元格處理後返回結果。下面直接從一個實例入手,再去討論相關知識點。1實例(從文本里提取數字

Excel公式技巧97:多條件查找

2021-10-20

學習Excel技術,關注微信公眾號:excelperfect有時候,我們需要根據多個條件在數據表中查找值,此時,就需要使用一些公式技巧了。本文的示例使用INDEX函數/MATCH函數組合的數組公式來實現多條件查找。示例1:滿足兩個條件如下圖1所示,需要查找指定汽車制

查找!查找!永恆的查找!

2021-10-21

學習Excel技術,關注微信公眾號:excelperfect查找,永恆的主題。從一大堆數據中找出我們需要的數據,這是Excel中常用的操作。如果使用公式來進行查找,那麼Excel提供了豐富的函數,讓你隨條件不同獲取想要的數據。其中,最常見或我們最熟悉的就是VLOOK

這10個Excel函數公式,職場必備,辦公不求人

2021-11-14

在職場辦公中,對數據的統計分析應用最多的還是Excel,如果不掌握一定量的技巧,那在辦公的過程中,肯定會求助於別人,今天,小編給大家帶來10個Excel函數公式,讓你在職場辦公中不在求人。一、Excel函數公式:自動計算年齡。目的:根據員工的【出生日期】自動計算年齡

這個公式查找,有點難

2021-11-14

學習Excel技術,關注微信公眾號:excelperfect標籤:Excel公式練習在《判斷兩個區域是否具有相同的值》中,我們比較了兩個相同大小的區域,看看這兩個區域中的值是否完全相同。下面更進一步,查找一組數據在另一個表中出現的位置。如下圖1所示,單元格區域B4:

大小奇偶質合陰陽?懂的都懂!

2021-11-16

看了標題就懂的,肯定是“深受其害”的。這個行業讓很多人是又愛又恨,又充滿希望又每天都失望。不過我目前並沒有加入到這個行業中來,哪怕是前段時間樓下每天路過的小店開出了800多萬,我也沒有動搖。當然,這完全是個人喜好,無關對錯。適度的情況下,我認為是合理的。具體是哪個行

提取非重複值,重複值只保留一個

2021-12-08

一、案例如下圖所示,B2:B10為一份名單,其中部分姓名出現多次。要求提取非重複值,重複出現的姓名只提取一次,結果如D2:D8所示。二、操作步驟方法一:刪除重複值選中B1:B10單元格區域,單擊【數據】-【刪除重複值】,打開【刪除重複值】對話框。如下圖所示:由於B1

用Reduce函數求最值

2021-12-15

Hi,大家早上好,我是偏愛函數公式,愛用Excel圖表管理倉庫的大叔Mr趙~咱繼續囉嗦REDUCE函數~❶求最大值。如下圖,求出B列的最大值。在D2單元格輸入公式:=REDUCE(,B2:B14,LAMBDA(x,y,IF(x>y,x,y)))公式計算

Excel如何按照月份彙總銷售量

2022-01-21

如下圖是某公司銷售表,現在我們想要快速統計出各月份的總銷量。在E2單元格輸入公式=SUMPRODUCT((MONTH($A$2:$A$13)=D2)*$B$2:$B$13)將E2單元格公式下拉到底即可完成下面跟大家簡單介紹一下這個公式,首先是MONTH函數,它的作用