贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

(讀者提問)在 Excel 的工作表中有個材質和價格的清單,其中的價格依最小值和最大值之間的範圍而定。如何依數值位於的範圍內對應的材質以求得價格?

以下圖為例,材質「ZD」的數值17是位於 6.1~25.0 之間,所以對應的價格為 260。

Excel-在一段範圍中比對符合的資料(SUMPRODUCT,OFFSET)

 

【公式設計與解析】

選取儲存格A1:D13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:

材質、最小值、最大值、價格。

儲存格H2:=OFFSET(D1,SUMPRODUCT((材質=F2)*((G2>=最小值)*
(G2<=最大值))*ROW(價格))-1,0)

條件一:(材質=F2)

SUMPRODUCT 函數中,判斷「材質」儲存格範圍和儲存格F2比對的結果,傳回 TRUE/FALSE 陣列。

條件二:(G2>=最小值)*(G2<=最大值)

SUMPRODUCT 函數中,判斷儲存格G2的內容是否介於最大值儲存格範圍和最小值儲存格範圍之間,傳回 TRUE/FALSE 陣列。

其中 (材質=F2)*((G2>=最小值)*(G2<=最大值)) 之間的『*』運算子,相當於執行邏輯 AND 運算。

SUMPRODUCT 函數中的(材質=F2)*((G2>=最小值)*(G2<=最大值))*ROW(價格),其中ROW(價格)會傳回價格儲存格範圍的每個儲存格列號。而最後一個『*』運算子,乃是執行乘法運算。運算時的 TRUE/FALSE 陣列會轉換為 1/0 陣列,公式最後會傳回符合條件者的列號。

Excel-在一段範圍中比對符合的資料(SUMPRODUCT,OFFSET)

將上式代入 OFFSET 函數,即可查得符合條件者的價格了。

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

(網友提問)在 Excel 的工作表中有一個產品編號和特賣價的對照表,如果選取一個「抽籤折扣(共有三種:95折、90折、85折)」,再依購買數量來計算結帳價,該如何處理?

Excel-依抽籤折扣數計算價錢(VLOOKUP,CHOOSE,MATCH)

 

【公式設計與解析】

抽籤折扣欄位的下拉式清單是利用資料驗證功能來製作:

Excel-依抽籤折扣數計算價錢(VLOOKUP,CHOOSE,MATCH)

 

儲存格D2:=VLOOKUP(A2,$G$2:$H$10,2,FALSE)*CHOOSE(MATCH(C2,
{"95折","90折","85折"},0),0.95,0.9,0.85)

(1) 找出特賣價:VLOOKUP(A2,$G$2:$H$10,2,FALSE)

根據儲存格A2的內容(產品編號),利用 VLOOKUP 函數查詢對應的特賣價。

 

(2) 計算結帳價:CHOOSE(MATCH(C2,{"95折","90折","85折"},0),0.95,0.9,0.85)

MATCH(C2,{"95折","90折","85折"},0):根據儲存格C2的內容(抽籤折扣)查詢第幾種特價。

根據 MATCH 函數的傳回值,於 CHOOSE 函數對應傳回一個折扣數值。其中「95折、90折、85折」對照折扣「0.95、0.9、0.85」。

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 的工作表中有一個數值清單,如何求正數最小值和負數最大值?

Excel-求正數最小值和負數最大值(陣列公式)

 

【公式設計與解析】

選取儲存格A1:A21,按 Ctrl+Shift+F3 鍵,定義名稱:數值。

1. 正數最小值

公式:{=MIN(IF(數值>0,數值,""))}

以上為陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{{}」。

 

2. 負數最大值

公式:{=MAX(IF(數值<0,數值,""))}

以上為陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{{}」。

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼