精品久久久久久久久水蜜桃|丁香花五月|新中文字幕麻豆视频|美女下面粉嫩粉嫩冒白浆高清|日本黄h兄妹h动漫一区二区三区|床片激情免费视频|羞羞动漫在线入口免费阅读

  1. 首頁 > 生活百科 > >

不用輔助列一對多查詢

生活經(jīng)驗不用

舉個例子，左邊是公司各部門的員工名單數(shù)據(jù)，現(xiàn)在我們需要根據(jù)部門 ， 來查找匹配出該部門下的所有員工姓名 ， 如下所示：

不用輔助列一對多查詢

文章插圖
方法一：Vlookup+輔助列
首先我們來看下添加輔助列的情況，我們在A列前面插入一列，命名為輔助列，然后在輔助列輸入的公式是：
=C2&COUNTIFS($C$2:C2,C2)
不用輔助列一對多查詢

文章插圖
counifs是計數(shù)公式 ， 我們第一個C2進(jìn)行固定引用，當(dāng)我們將公式下拉填充的時候 ， 起始數(shù)據(jù)區(qū)域還是C2，結(jié)束匹配是C6，也就是從C2到C6里面找 ， 有多少個市場部的，這個時候已經(jīng)是第3個市場部了，所以結(jié)果是市場3
不用輔助列一對多查詢

文章插圖
通過輔助列的構(gòu)建，我們得到了唯一值
所以我們需要查找各個部門的第1個員工的話 ， 就把查找值改成F2&1，既市場1的員工
=VLOOKUP(F2&1,$A:$D,4,0)
不用輔助列一對多查詢

文章插圖
如果想查找第2個員工的話，就是市場2，第3個就是市場3對應(yīng)的員工 ， 為了讓我們公式能夠向右填充，所以我們使用Column(A1)公式來替代數(shù)字1，Column()公式是返回單元格在第幾列,A1在第1列，所以結(jié)果是1，
然后E1單元格要按3下F4進(jìn)行固定引用列的位置，IFERROR公式用來屏蔽錯誤值，所以最后我們使用的公式是：
=IFERROR(VLOOKUP($F2&COLUMN(A1),$A:$D,4,0),"")
不用輔助列一對多查詢

文章插圖
2、Vlookup公式不用輔助列
如果說我們想不用輔助列，要用VLOOUP實現(xiàn)一對多查詢的話，直接使用公式：
=IFERROR(VLOOKUP($E2&COLUMN(A1),IF({1,0},$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$E2),$C$1:$C$100),2,0),"")
不用輔助列一對多查詢

文章插圖


接下來是理解過程：
其實和插入輔助列一樣 ， 首先要構(gòu)建一個虛擬數(shù)組 ， 也就是說累計計數(shù)虛擬存在，但是不體現(xiàn)在單元格中，我們用虛擬數(shù)組構(gòu)建的公式是：
$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$E2)，為了方便理解，我們把這個公式放在單元格 ， 就得到了如下的一個虛擬數(shù)組
不用輔助列一對多查詢

文章插圖
以上只得到了查找列，我們還要把結(jié)果列放進(jìn)行，所以我們用IF({1,0})來構(gòu)建 ， 公式為：
=IF({1,0},$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$G2),$C$1:$C$100)
不用輔助列一對多查詢

文章插圖
【不用輔助列一對多查詢】最關(guān)鍵的就是解決了第二參數(shù)，虛擬數(shù)據(jù)源的構(gòu)建，最后就是我們只需要用VLOOKUP，對查找值+COLUMN公式，就能得到我們想要的結(jié)果了
=IFERROR(VLOOKUP(查找值&COLUMN(),虛擬數(shù)組,2,0),"")
3、FILTER+TRANSPOSE
如果你的EXCEL版本夠高，我們就可以用FILTER+TRANSPOSE公式，快速查找，我們輸入的公式是：
=TRANSPOSE(FILTER(C:C,B:B=E2))
不用輔助列一對多查詢

文章插圖
關(guān)于這個技巧，你學(xué)會了么？動手試試吧！