close

2351

Excel查表-比對-判斷與傳回對應的值-IFSUMIFS

顯捷:「這是我們自己設計的Excel,會依照所選擇的產品自動判斷是否符合批量的價格,但是本人腦筋轉不過來,兜不出公式,請教錦子老師幫忙….

問題是,我現在想依照我選擇的產品,找出對應的單價與批量價格,當我key入客戶的購買數量後(Quantity),會依照這個數量判斷,如果客戶購買數量低於批量,在儲存格(Unit Price)內會顯示單件價格,如果超過批量,則會顯示批量價格。

如果可以的話,希望不要另外建一張表來比對或改變價格/批量的排列方式!!!

錦子:「這只要使用IFSUMIFS函數結合即可完成妳的需求。

點取G3儲存格輸入公式:

=IF(F3<SUMIFS($H$10:$H$13,A$10:$A$13,A3,$C$10:$C$13,C3),SUMIFS($G$10:$G$13,A$10:$A$13,A3,$C$10:$C$13,C3),SUMIFS($I$10:$I$13,A$10:$A$13,A3,$C$10:$C$13,C3))

再將公式複製到G4:G6儲存格。」

【公式說明】

SUMIFS(實際計算範圍,條件1範圍,條件1,條件2範圍,條件2….條件127範圍,條件127)

計算符合各個條件所在列對應到實際計算範圍同一列數值總和。

SUMIFS($H$10:$H$13,A$10:$A$13,A3,$C$10:$C$13,C3) 傳回符合A10:A13儲存格內含有A3儲存格內容且C10:C13儲存格內含有C3儲存格內容對應到H10:H13同列數值總和(批量)

SUMIFS($G$10:$G$13, A$10:$A$13,A3,$C$10:$C$13,C3) 傳回符合A10:A13儲存格內含有A3儲存格內容且C10:C13儲存格內含有C3儲存格內容對應到G10:G13同列數值總和(未達批量價格)

SUMIFS($I$10:$I$13,A$10:$A$13,A3,$C$10:$C$13,C3) 傳回符合A10:A13儲存格內含有A3儲存格內容且C10:C13儲存格內含有C3儲存格內容對應到I10:I13同列數值總和(己達批量價格)

IF(F3<SUMIFS($H$10:$H$13,A$10:$A$13,A3,$C$10:$C$13,C3),SUMIFS($G$10:$G$13,A$10:$A$13,A3,$C$10:$C$13,C3),SUMIFS($I$10:$I$13,A$10:$A$13,A3,$C$10:$C$13,C3)) 如果F3內容小於SUMIFS($H$10:$H$13,A$10:$A$13,A3,$C$10:$C$13,C3)傳回的值,則執行SUMIFS($G$10:$G$13, A$10:$A$13,A3,$C$10:$C$13,C3),否則執行SUMIFS($I$10:$I$13,A$10:$A$13,A3,$C$10:$C$13,C3)

部落格相關範例

2017.06.24

318ExcelSumif函數和Sumifs函數進行條件求和的異同

 

2017.06.03

320Sumif函數的幾種常見用法

 

2017.05.25

368SUMIF的高級用法:跨多工作表依條件求和

 

2017.07.05

315SUMIF函數同時加總兩種情況下的資料之和

 

2019.10.24

2272SUMIF函數跨工作表的問題

 

2017.07.07

316SUMIFS函數的公式語法及使用方法實例

 

2014.08.15

SUMIFS

 

2017.01.07

220 使用SUMIF函數實現查找引用功能的方法

 

2019.08.30

2194跨工作表資料合計-SUMIF

 

2017.04.04

497SUMIF函數基礎語法解析

 

2017.04.12

348SUMIF函數的4種特殊用法

 

2017.01.02

212 SUMIF函數基礎語法解析

 

2017.01.04

214 使用SUMIF函數統計多條件求和的方法

 

2017.08.10

499使用SUMIF函數統計單字段單條件求和的方法

 

2017.07.06

317SUMIF函數的公式語法及使用方法實例

 

2017.09.11

377SUMIF函數的一個神奇用法詳解

 

2019.06.12

2109-18個條件求和公式沒用過,別說你會Excel

 

2019.08.29

2194跨工作表資料合計-SUMIFSIF

 

2017.01.09

223 使用SUMIF函數進行多欄區域條件求和的方法

 

 


arrow
arrow
    創作者介紹

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