錦子老師

BLACK：「錦子老師，如下圖，G欄位+H欄位是材料與需求量，請教可以使用什麼函數從A欄位:E欄位找回合適的單價(I欄位)/廠商(J欄位)/適合的MOQ區間(K欄位)? 謝謝。

錦子老師：「這要用的函數有點多，有IFERROR、SUMPRODUCT、LARGE、OFFSET等…

點取I2儲存格輸入公式：

=IFERROR(OFFSET($E$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10<=H2)*ROW($D$2:$D$10),1))-1,0),OFFSET($E$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10 >=H2)*ROW($D$2:$D$10),COUNTIF($A$2:$A$10,G2)))-1,0))

再將公式複製到I3:I10儲存格。

點取J2儲存格輸入公式：

=IFERROR(OFFSET($C$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10<=H2)*ROW($D$2:$D$10),1))-1,0),OFFSET($C$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10 >=H2)*ROW($D$2:$D$10),COUNTIF($A$2:$A$10,G2)))-1,0))

再將公式複製到J3:J10儲存格。

點取K2儲存格輸入公式：

=IFERROR(OFFSET($D$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10<=H2)*ROW($D$2:$D$10),1))-1,0),OFFSET($D$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10 >=H2)*ROW($D$2:$D$10),COUNTIF($A$2:$A$10,G2)))-1,0))

再將公式複製到K3:K10儲存格。

【公式解說】

IFERROR(公式，公式出現錯誤值要執行的動作)

SUMPRODUCT(計算範圍1,計算範圍2….計算範圍255)

OFFSET(開始位置，移動列數，移動欄數，框選列數，框選欄數)

ROW()

COUNTIF(資料範圍,條件)

($A$2:$A$10=G2) 傳回A2:A10儲存格內容是否與G2儲存格內容一樣，一樣為TRUE，否則為FALSE。

($D$2:$D$10<=H2) 傳回A2:A10儲存格內容是否與H2儲存格內容一樣，一樣為TRUE，否則為FALSE。

ROW($D$2:$D$10) 傳回D2:D10列號。

COUNTIF($A$2:$A$10,G2) 統計A2:A10儲存格內容與G2儲存格內容一樣的儲存格數量。

LARGE(($A$2:$A$10=G2)*($D$2:$D$10<=H2)*ROW($D$2:$D$10),1) 傳回($A$2:$A$10=G2)與($D$2:$D$10<=H2)與ROW($D$2:$D$10)三個條件皆成立的儲存格列號，第1大值(求出比數量低的最接近值所在列號)。

LARGE(($A$2:$A$10=G2)*($D$2:$D$10>=H2)*ROW($D$2:$D$10),COUNTIF($A$2:$A$10,G2)) 傳回($A$2:$A$10=G2)與($D$2:$D$10>=H2)與ROW($D$2:$D$10)三個條件皆成立的儲存格列號，由COUNTIF($A$2:$A$10,G2)統計數量(N)之第N大值(求出比數量高的最接近值所在列號)。

OFFSET($D$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10<=H2)*ROW($D$2:$D$10),1))-1,0) 由D1儲存格開始移動SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10 <=H2)*ROW($D$2:$D$10),1))-1計算出的數值減一列數，移動0欄。

IFERROR(OFFSET($D$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10<=H2)*ROW($D$2:$D$10),1))-1,0),OFFSET($D$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10 >=H2)*ROW($D$2:$D$10),COUNTIF($A$2:$A$10,G2)))-1,0)) 如果OFFSET($D$1,SUMPRODUCT (LARGE(($A$2:$A$10=G2)*($D$2:$D$10<=H2)*ROW($D$2:$D$10),1))-1,0)傳回錯誤值，則執行OFFSET($D$1,SUMPRODUCT(LARGE(($A$2:$A$10=G2)*($D$2:$D$10>=H2)*ROW($D$2:$D$ 10),COUNTIF($A$2:$A$10,G2)))-1,0)。

部落格相關範例