單條數據查詢,你會。
同時查多條重複數據,你也會嗎?
大家都知道,由于vlookup本身的局限性,隻能查非重複的單條數據。如果要同時查多條重複數據,vlookup無法實現。
要同時查多條重複數據,很多人的解決方法是:
- 利用篩選功能。但效率很低,每次都要重新設置篩選條件。
- 利用高級篩選功能。對新手來說,有一些複雜。
- 先對數據進行排序,再每次利用Ctrl f手動查找。
其實vlookup也可以進行重複數據查詢。下面分享幾種利用Vlookip進行重複數據查詢的方法。
案例。如下圖數據,我們要求查詢所有采購部門的年薪,性别和姓名。
第一種方案。Vlookup If
- 插入輔助行1. 在部門前面插入一行,用函數If(),給每條條數編号。在B2輸入=IF(C2<>C1,1,B1 1), 然後向下複制。此函數的目的是,如果兩行數據的部門,不一緻,就編号1,如果相同,就加1.
2. 插入輔助行2. 在輔助行1的前面再插入一行。用連接函數&,創建唯一數據(部門&編号)。在B2輸入=D2&C2 , 然後向下複制。&的功能是将兩個數據連接起來。
3.創建唯一查詢條件。在K2輸入“采購部1”, 然後向下拖拉,就創建了多條唯一數據。
4.用Vlookup設置公式。
在L2輸入公式=VLOOKUP(K2,B:D,3,0)。并向下拖拉複制。
同理,在M2輸入公式=VLOOKUP(K2,B:E,4,0);
在N2輸入=VLOOKUP(K2,B:F,5,0);
在O2輸入=VLOOKUP(K2,B:G,6,0)。
第二種方案。Vlookup countif
同第一種方案,在部門前插入兩行輔助行,輔助行1,輔助行2.
- 輔助行1. 在C2輸入=COUNTIF($D$2:D2,D2)。 Countif的功能返回區域内重複值的個數,語法格式為countif(區域,條件)。注意$D$2:D2中的第一個D2必須要用絕對引用。這樣就給每一個重複值取了個編碼。
- 輔助行2. 在B2輸入=D2&C2 。 函數&的功能是将兩個數據連接。這樣就給每一個重複值取了個唯一值。
3.同第一種方案,創建唯一查詢條件。在K2輸入“采購部1”, 然後向下拖拉,就創建了多條唯一數據。
4.用Vlookup設置公式。
- 在L2輸入公式=VLOOKUP(K2,B:D,3,0)。并向下拖拉複制。
- 同理,在M2輸入公式=VLOOKUP(K2,B:E,4,0);
- 在N2輸入=VLOOKUP(K2,B:F,5,0);
- 在O2輸入=VLOOKUP(K2,B:G,6,0)。
第三種方案。Vlookup countif row
同第二種方案,在部門前插入兩行輔助行,輔助行1,輔助行2.
- 輔助行1. 在C2輸入=COUNTIF($D$2:D2,D2)。 Countif的功能返回區域内重複值的個數,語法格式為countif(區域,條件)。注意$D$2:D2中的第一個D2必須要用絕對引用。這樣就給每一個重複值取了個編碼。
- 輔助行2. 在B2輸入=D2&C2 。 函數&的功能是将兩個數據連接。這樣就給每一個重複值取了個唯一值。
3.在K2輸入“采購部”。
4.用Vlookup row設置公式。
Row()是返回引用單元格的行号。我們的目的是利用部門和行号創建一個唯一值。
- 在L2輸入公式=VLOOKUP($K$2&ROW(A1),B:D,3,0)。并向下拖拉複制。注意,K2要用絕對引用,因為K2是條件“采購部”所在單元格,此位置是不變的。Row(A1)返回值為1, $K$2&ROW(A1)返回值是“采購部1”.
- 同理,在M2輸入公式=VLOOKUP($K$2&ROW(A1),B:E,4,0)
- 在N2輸入=VLOOKUP($K$2&ROW(A1),B:F,5,0)
- 在O2輸入=VLOOKUP($K$2&ROW(A1),B:G,6,0)
但我們把K2數據變成生産部時,查詢結果會自動更新。
大家還有其他方案嗎?
以後會用得上,值得收藏,點贊,關注。
,