錦子老師

如果在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欄)來複製公式。