close

826

EXCEL如何取二張工作表交集資料

示範檔

範例檔

826.XLSX

結果檔

826F.XLSX

如果在Excel活頁簿中有二張工作表,工作表2存放各個項目的數值,如下圖所示。

工作表1存放的是各項目中文與英文交集的數值,如下圖所示。

要如何才可以讓工作表1存放工作表2的項目數值?

方法1:以VLOOKUP函數作業

1.在工作表2B欄插入一個空白欄,抓取A欄中文字,以B2儲存格為例輸入公式「LEFT(A2,1)」後,按Enter鍵完成輸入,如下圖所示。

2.點取「常用 > 編輯 > 排序與篩選」倒三角鈕,選擇「自訂排序」項目,如下圖所示。

3.在「排序」對話方塊,點取「排序方式」右方倒三角鈕,選擇「中文字」欄位。

4.點取「排序方式」列「順序」欄位倒三角鈕,選擇「自訂清單」項目,如下圖所示。

5.在「自訂清單」對話方塊,點取「自訂清單」列示方塊中「,,,」項目,如下圖所示。

6.點取「確定」鈕回到「排序」對話方塊,如下圖所示。

7.點取「新增層級」鈕,點取「次要排序方式」右方倒三角鈕,選擇「英文字母」欄位。

8.點取「次要排序方式」列「順序」欄位倒三角鈕,選擇「AZ」項目。

9.點取「確定」鈕回到工作表,排序結果如下圖所示。

【說明】

這邊使用排序功能整體看起來是多餘,其實不然,若不排序則後面使用VLOOKUP函數會找錯數值資料或找不到數值資料。

10.點取工作表1B2儲存格輸入公式「=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函數方法一樣,不同的是在步驟10B2儲存格輸入公式「=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)是在判斷在工作表2B2B253儲存格中是否有A2儲存格內容,若有為TRUE,若用數值來看則為1,沒有則為FALSE,若用數值來看則為0

"0;;"""""是在設定算出來的結果數值格式,第一個;號的左邊表示若為數值,則顯示數值,右邊拉著第二個;號表示若為負數則不填入資料,第二個;號右邊表示若為0則空格。

其他儲存格都可以用「B2儲存格複製-貼上」的方式或者用「黑十字拖拉」的方式(例:先拉B2..B11,再往右拉到AA)來複製公式。

 

 


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()