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

發票資料轉付款單作業-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.傳回錯誤值,則儲存格不要填入資料。