1044發票資料轉付款單作業-SUMPRODUCT函數

如果在「發票」工作表有如下圖的資料。

我想把同一個 INVOICE 的記錄，輸入到 H3 儲存格中，使用函數方式擷取到「付款單」工作表中，請問要如何操作。

操作方式如下：

步驟1：框選發票工作表中所在資料(A2:F11)依您的資料大小調整，不含第一列。

步驟2：點取「公式 > 從選取範圍建立」圖示。

步驟 3 ：在「以選取範圍建立名稱」對話方塊，點取「頂端列」核取方塊，使其打勾。

步驟4：點取「確定」鈕。

步驟5：點取「付款單」工作表標籤。

步驟6：點取A3儲存格輸入公式「=IFERROR(OFFSET(發票!$A$1,SUMPRODUCT(LARGE ((INVOICE =發票!$H$3)*ROW(INVOICE),1))-1,COLUMN()-1),"")」後，按ENTER鍵完成輸入。

步驟7：點取A4儲存格輸入公式「=IFERROR(OFFSET(發票!$A$1,SUMPRODUCT(LARGE ((INVOICE =發票!$H$3)*ROW(INVOICE),2))-1,COLUMN()-1),"")」後，按ENTER鍵完成輸入。

步驟8：點取A5儲存格輸入公式「=IFERROR(OFFSET(發票!$A$1,SUMPRODUCT(LARGE ((INVOICE =發票!$H$3)*ROW(INVOICE),3))-1,COLUMN()-1),"")」後，按ENTER鍵完成輸入。

步驟9：點取A6儲存格輸入公式「=IFERROR(OFFSET(發票!$A$1,SUMPRODUCT(LARGE ((INVOICE =發票!$H$3)*ROW(INVOICE),4))-1,COLUMN()-1),"")」後，按ENTER鍵完成輸入。

步驟10：點取A7儲存格輸入公式「=IFERROR(OFFSET(發票!$A$1,SUMPRODUCT(LARGE ((INVOICE =發票!$H$3)*ROW(INVOICE),5))-1,COLUMN()-1),"")」後，按ENTER鍵完成輸入。

這是預估一張INVOICE最多五筆記錄，若您的INVOICE一張含有更多筆記錄則視需求增加公式。

步驟11：將A3:A7儲存格框選起來，複製到B3:F7儲存格範圍。

【公式說明】

1. LARGE ((INVOICE =發票!$H$3)*ROW(INVOICE),1) 傳回INVOICE編號=H3儲存格內容的記錄，其中列號為最大值的那筆記錄列號。

2. SUMPRODUCT(LARGE ((INVOICE =發票!$H$3)*ROW(INVOICE),1)) 擷取1.的列號值。

3. OFFSET(發票!$A$1,SUMPRODUCT(LARGE ((INVOICE =發票!$H$3)*ROW (INVOICE),1))-1,COLUMN()-1) 傳回從A1儲存格向下移動2.傳回的列數值，及欄數。

4. IFERROR(OFFSET(發票!$A$1,SUMPRODUCT(LARGE ((INVOICE =發票!$H$3)*ROW ( INVOICE),2))-1,COLUMN()-1),"") 如果3.傳回錯誤值，則儲存格不要填入資料。