錦子老師

如下圖左邊表格所示，我們要將該表格依照日期及項目交互統計其數量。

步驟1.點取「公式 > 己定義之名稱 > 定義名稱」圖示。

步驟2.在「編輯名稱」對話方塊，點取「名稱」欄位輸入「日期」字串。

步驟3.點取「參照到」欄位輸入「=OFFSET(SUMPRODUCT!$A$1,1,0,COUNT(SUMPRODUCT! $A:$A),1)」字串，如下圖所示。

OFFSET函數解析：

OFFSET(Reference,Rows,Cols,Height,Width) 根據指定的參照位址取得列數及欄數範圍。

Reference：參照位址，用以計算位移結果的起始位置。

Rows：指定要向上(負值)或向下(正值)位移的列數。

Cols：指定要向左(負值)或向右(正值)位移的欄數。

Height：欲傳回的儲存格列數。

Width：欲傳回的儲存格欄數。

COUNT函數解析：

COUNT(Value1,Value2,…) 計算範圍中包含數值的儲存格數目。

Value1,Value2,…：為1到255個參數，可以包含不同類型的資料，但只有數值資料會被統計。

公式解析

COUNT(SUMPRODUCT! $A:$A) 傳回SUMPRODUCT工作表中A欄數值儲存格數量。

OFFSET(SUMPRODUCT!$A$1,1,0,COUNT(SUMPRODUCT! $A:$A),1) 以SUMPRODUCT工作表中A1儲存格為起點，向下一列，選取1欄與COUNT函數計算出的列數。

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

步驟5.點取「公式 > 己定義之名稱 > 定義名稱」圖示。

步驟6.在「編輯名稱」對話方塊，點取「名稱」欄位輸入「項目」字串。

步驟7.點取「參照到」欄位輸入「=OFFSET(SUMPRODUCT!$B$1,1,0,COUNT (SUMPRODUCT! $A:$A),1)」字串，如下圖所示。

步驟8.點取「確定」鈕。

步驟9.點取「公式 > 己定義之名稱 > 定義名稱」圖示。

步驟10.在「編輯名稱」對話方塊，點取「名稱」欄位輸入「lvok 」字串。

步驟11.點取「參照到」欄位輸入「=OFFSET(SUMPRODUCT!$C$1,1,0,COUNT(SUMPRODUCT! $A:$A),1)」字串，如下圖所示。

步驟12.點取「確定」鈕。

步驟13.點取F2儲存格輸入「=SUMPRODUCT(($A$2:$A$17=$E2)*($B$2:$B$17=F$1)*$C$2: $C$17)」後，按ENTER鍵完成輸入。

步驟14.將滑鼠指標移到F2儲存格右下角，直到遊標為「+」時，按住滑鼠左鍵不放向下拉至F5儲存格，再向右拉至H5儲存格，如下圖所示。

如果說記錄持續增加，要如何不改變公式而能得到正確結果呢？

步驟15.點取F2儲存格輸入「=SUMPRODUCT((日期=$E2)*(項目=F$1)*數量)」後，按Enter鍵完成輸入。

步驟16.將滑鼠指標移到F2儲存格右下角，直到遊標為「+」時，按住滑鼠左鍵不放向下拉至F5儲存格，再向右拉至H5儲存格，如下圖所示。