2375 |
Excel 資料抓取-SUMPRODUCT、LARGE、OFFSET、ROW、IFERROR函數 |
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)。
部落格相關範例
2018.12.16 |
|
2019.10.24 |
|
2017.04.06 |
|
2020.06.26 |
|
2018.07.09 |
|
2019.05.29 |
|
2018.11.19 |
|
2017.12.28 |
|
2017.05.25 |
|
2019.09.24 |
|
2019.07.29 |
|
2019.10.26 |
|
2017.04.20 |
|
2020.10.25 |
|
2020.02.29 |
|
2020.02.13 |
|
2020.05.15 |
|
2019.08.21 |
|
2018.11.26 |
|
2020.02.11 |
|
2019.12.19 |
|
2019.12.19 |
|
2012.01.30 |
留言列表