826 |
EXCEL如何取二張工作表交集資料 |
||||
示範檔 |
無 |
範例檔 |
826.XLSX |
結果檔 |
826F.XLSX |
如果在Excel活頁簿中有二張工作表,工作表2存放各個項目的數值,如下圖所示。
工作表1存放的是各項目中文與英文交集的數值,如下圖所示。
要如何才可以讓工作表1存放工作表2的項目數值?
方法1:以VLOOKUP函數作業
1.在工作表2的B欄插入一個空白欄,抓取A欄中文字,以B2儲存格為例輸入公式「LEFT(A2,1)」後,按Enter鍵完成輸入,如下圖所示。
2.點取「常用 > 編輯 > 排序與篩選」倒三角鈕,選擇「自訂排序」項目,如下圖所示。
3.在「排序」對話方塊,點取「排序方式」右方倒三角鈕,選擇「中文字」欄位。
4.點取「排序方式」列「順序」欄位倒三角鈕,選擇「自訂清單」項目,如下圖所示。
5.在「自訂清單」對話方塊,點取「自訂清單」列示方塊中「甲,乙,丙,丁…」項目,如下圖所示。
6.點取「確定」鈕回到「排序」對話方塊,如下圖所示。
7.點取「新增層級」鈕,點取「次要排序方式」右方倒三角鈕,選擇「英文字母」欄位。
8.點取「次要排序方式」列「順序」欄位倒三角鈕,選擇「A到Z」項目。
9.點取「確定」鈕回到工作表,排序結果如下圖所示。
【說明】
這邊使用排序功能整體看起來是多餘,其實不然,若不排序則後面使用VLOOKUP函數會找錯數值資料或找不到數值資料。
10.點取工作表1的B2儲存格輸入公式「=IF(ISNA(VLOOKUP($A2&B$1,工作表2!$A$1:$D$253,3,FALSE)),"",VLOOKUP($A2&B$1,工作表2!$A$1:$D$253,3,FALSE))」後,按Enter鍵完成輸入,如下圖所示。
其他儲存格都可以用「B2儲存格複製-貼上」的方式或者用「黑十字拖拉」的方式(例:先拉B2..B11,再往右拉到AA欄)來複製公式,如下圖所示。
方法2:以SUMPRODUCT&TEXT函數作業
這種方法前面9個步驟都與VLOOKUP函數方法一樣,不同的是在步驟10的B2儲存格輸入公式「=TEXT(SUMPRODUCT(--($A2=工作表2!$B$2:$B$253)*--(B$1=工作表2!$D$2:$D$253)*工作表2!$C$2:$C$253),"0;;""""")」後,按Enter鍵完成輸入,如下圖所示。
【公式說明】
--($A2=工作表2!$B$2:$B$253)是在判斷在工作表2的B2到B253儲存格中是否有A2儲存格內容,若有為TRUE,若用數值來看則為1,沒有則為FALSE,若用數值來看則為0。
"0;;"""""是在設定算出來的結果數值格式,第一個;號的左邊表示若為數值,則顯示數值,右邊拉著第二個;號表示若為負數則不填入資料,第二個;號右邊表示若為0則空格。
其他儲存格都可以用「B2儲存格複製-貼上」的方式或者用「黑十字拖拉」的方式(例:先拉B2..B11,再往右拉到AA欄)來複製公式。
留言列表