close

2375

Excel 資料抓取-SUMPRODUCTLARGEOFFSETROWIFERROR函數

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

image

錦子老師:「這要用的函數有點多,有IFERRORSUMPRODUCTLARGEOFFSET

點取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儲存格。

image

【公式解說】

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

CONCAT

2019.10.24

2272SUMIF函數跨工作表的問題

2017.04.06

358使用Excel函數公式統計文字出現次數的方法

2020.06.26

1059EXCEL儲存格內容合併

2018.07.09

835不會這5Excel函數別說您熟練使用Excel-欄位斷字與合併函數

2019.05.29

Excel之中使用CONCATENATE時怎麼換行?

2018.11.19

1050Excel加總-EVALUATE函數

2017.12.28

606Excel中根據中國身份證字號自動生成生日和性別的方法

2017.05.25

397Excel函數實現排序與篩選的方法

2019.09.24

Q128Excel 多欄合併成一欄(參數間以空白鍵隔開)

2019.07.29

Q42EXCEL 數字型儲存格合併,並保留前置 0

2019.10.26

829EXCEL計算機

2017.04.20

415Office365裡新增的幾個Excel函數

2020.10.25

2061EXCEL 如何讓函數裡的欄位自動加1

2020.02.29

908符合條件的合併儲存格對應內容合併

2020.02.13

909這樣的Excel儲存格合併,聽說會的人只有1%-高級篇

2020.05.15

1007Excel儲存格非重覆資料合併

2019.08.21

F03函數綜合應用大雜燴-文字函數

2018.11.26

1053Excel 文字數值加總

2020.02.11

909這樣的Excel儲存格合併,聽說會的人只有1%-基礎篇

2019.12.19

Y2018西元2018年相關範例

2019.12.19

Y2019西元2019年相關範例

2012.01.30

EXCEL 各函數功能說明

 


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()