2013年7月10日 星期三

Excel-指定重覆出現次數對應的日期(SUMPRODUCT,INDEX)

有網友問到:在一個 Excel 的資料表中(參考下圖左),含有「日期、單號、數量」三個欄位,要如何找出第 3 個單號 25 (由上而下)所對應的日期呢?

【準備工作】

建立一個輔導欄位,在儲存格D2輸入公式「=COUNTIF($B$2:B2,B2)」,複製儲存格D2,往下各列貼上。該公式用以計算由第一個儲存格B2開始,每個單號出現的次數。

選取儲存格B2:D16,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:單號、輔助。

【輸入公式】

儲存格G3:=INDEX(日期,SUMPRODUCT((單號=F3)*(輔助=3)*ROW(單號))-1,)

SUMPRODUCT((單號=F3)*(輔助=3)*ROW(單號)):找出符合儲存格F3的單號並且為輔助欄位為 3 (第 3 個)是位於「單號」陣列中的第幾個。

再透過 INDEX 函數以查表方式查出對於的日期。

複製儲存格G3,往下各列貼上。

 

【補充資料】

相關之詳細函數說明,請參閱微軟網站:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

沒有留言:

張貼留言

好康東東