|
是不是每次面對Excel里海量的數據，想要精準提取特定信息時就頭疼不已？手動篩選、逐條核對，不僅效率低得可憐，還特別容易出錯，一不小心就前功盡棄。別愁啦，表姐這就教你幾招，用Excel函數輕松搞定多對多查詢等難題，讓你秒變表格高手！ 合并多表名單：UNIQUE與TOCOL的強強聯合 當有多個工作表記錄著不同時間段的數據，比如1~4月的員工考勤記錄分別存放在不同工作表中，每個月都可能有新入職以及離職人員，此時需要從這四個表中提取出不重復的員工名單，UNIQUE函數和TOCOL函數就能輕松搞定。
在“匯總表”的A1單元格輸入公式： =UNIQUE(TOCOL('1月:4月'!A:A,1)) TOCOL函數第一參數使用多工作表引用方式'1月:4月'!A:A，表示要處理的數據范圍為“1月”至“4月”工作表的A列；第二參數使用1，表示忽略空白單元格。
TOCOL函數將四個工作表的A列以忽略空白單元格的形式合并為一列，再使用UNIQUE函數提取出不重復的員工名單，這樣就能快速得到合并后的不重復名單了。 多條件精準查詢：FILTER函數大顯身手 在日常工作中，經常需要根據多個條件從數據表中提取符合要求的記錄。比如，要提取出品牌為“松下”，并且庫存數大于20的所有記錄。這時，FILTER函數就能派上大用場。 在目標單元格，如E5單元格中輸入公式：↓
=FILTER(A2:A13,(B2:B13=E2)*(C2:C13>20)) 這里，要返回內容的單元格區域是A2:A13，指定的條件是(B2:B13=E2)*(C2:C13>20)。兩個條件之間用乘號連接，意味著要同時滿足這兩個條件。當兩組邏輯值的對應位置都是TRUE時，相乘后的結果為1，表示這兩個條件同時符合，否則返回0。 FILTER函數會檢查相乘后的結果，如果不等于0，就返回A2:A13單元格區域中對應位置的整行記錄，這樣就能精準提取出符合條件的數據了。 提取未售出商品：FILTER與COUNTIF的完美配合 有時候，需要根據已有的商品列表和已售商品列表，提取出全部未售出的商品。這時，FILTER函數和COUNTIF函數可以協同工作。 在E2單元格輸入公式： =FILTER(A2:A13,COUNTIF(C2:C5,A2:A13)=0)
先使用COUNTIF函數統計A2:A13單元格中的每個元素在C2:C5中出現的次數，得到一組由1和0構成的內存數組。接著判斷內存數組中的各個元素是否等于0，返回由TRUE或FALSE構成的內存數組。FILTER函數最終返回A2:A13單元格區域中與TRUE對應的整行記錄，也就是未售出的商品信息。 帶錯誤值的篩選匯總：AGGREGATE函數的妙用 在對數據進行篩選和匯總時，可能會遇到數據中存在錯誤值的情況。比如，已經對B列的部門進行了篩選，而且F列的金額計算結果有錯誤值，此時需要對F列的金額進行匯總，AGGREGATE函數就能發揮作用。 =AGGREGATE(9,7,F2:F14)
AGGREGATE函數第一參數使用9，表示匯總方式為求和；第二參數使用7，表示忽略隱藏行和錯誤值。這樣，在計算F2:F14單元格區域的和時，就會自動忽略其中的錯誤值，得到正確的匯總結果。 知識擴展 除了上述提到的函數，Excel中還有很多實用的函數可以用于數據處理。比如，SUMIFS函數可以根據多個條件對數據進行求和；VLOOKUP函數可以在表格或區域中按行查找數據。在實際應用中，可以根據具體需求選擇合適的函數組合，以實現更復雜的數據處理任務。 同時，掌握函數的嵌套使用技巧，能夠進一步提高數據處理效率。另外，定期學習和了解Excel的新函數和功能，也能讓我們在面對不斷變化的數據處理需求時更加得心應手。 總結 本文介紹了Excel中FILTER、UNIQUE、TOCOL和AGGREGATE這四個函數在多對多查詢等數據處理場景中的應用。FILTER函數可以方便地根據多個條件提取符合要求的記錄；FILTER與COUNTIF函數配合能提取未售出商品；UNIQUE與TOCOL函數組合可合并多表名單并提取不重復數據；AGGREGATE函數能對帶錯誤值的數據進行篩選匯總。掌握這些函數的使用方法，能夠讓我們在處理Excel數據時更加高效、準確，輕松應對各種復雜的數據處理需求，從Excel小白逆襲成為表格高手。 |
|
|
