錦子老師

袁小依：錦子老師您好，工作表1的G欄下了IF(COUNTIFS(.......函數，參照D、E欄有相同值顯示”重複品項”。

請問若不用篩選，如何下函數將G欄有顯示重複品項的列至工作表2如圖依序顯示呢？

錦子老師：這個問題，我個人解法如下：

步驟1：點取工作表1的G2儲存格輸入公式：

=IF(SUMPRODUCT(($D$2:$D$13=D2)*($E$2:$E$13=E2))>1,"重複品項","")

再將公式複製到G3:G13儲存格。

步驟2：點取工作表2的A1儲存格輸入公式：

=IFERROR(OFFSET(工作表1!A$1,SUMPRODUCT(SMALL((工作表1!$G$2:$G$13="重複品項")*ROW(工作表1!$G$2:$G$13),COUNTBLANK(工作表1!$G$2:$G$13)+ROW()))-1,,),"")

再將公式複製到A1:G2儲存格。

【公式說明】

=IFERROR(OFFSET(工作表1!A$1,SUMPRODUCT(SMALL((工作表1!$G$2:$G$13="重複品項")*ROW(工作表1!$G$2:$G$13),COUNTBLANK(工作表1!$G$2:$G$13)+ROW()))-1,,),"")

公式1：COUNTBLANK(工作表1!$G$2:$G$13)

統計G2:G13空白儲存格數量。

公式2：SMALL((工作表1!$G$2:$G$13="重複品項")*ROW(工作表1!$G$2:$G$13),公式1+ ROW())

傳回工作表1G2:G13儲存格內容等於重複品項的列號第N(公式1運算結果加目前列號)小值。

公式3：SUMPRODUCT(公式2)

傳回公式2運算結果的總和。

公式4：OFFSET(工作表1!A$1,公式3-1,,)

傳回從工作表1的A1儲存格開始移動N(公式3運算結果減1)列的儲存格內容。

公式5：IFERROR(公式5,"")

如果公式5運算結果出現錯誤訊息，則不填入資料，否則填入公式5運算結果。

大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

更多相關影片教學：請點我

更多相關文章：請點我