網友想要根據一個 Excel 的資料表(如下圖左),在輸入一個代碼後查得資料表中的價格,該如何處理?

主要是要根據代碼中的「a」和「b」來查詢價格。

Excel-同時利用二個代碼查詢(SUBSTITUTE,MID,SUMPRODUCT)

 

【公式設計與解析】

首先,要將「b欄位」的資料重建在二欄之中(如下圖)。

Excel-同時利用二個代碼查詢(SUBSTITUTE,MID,SUMPRODUCT)

 

(1) 公式:=SUBSTITUTE(F2,"-",REPT(" ",20))

結果:XYZ         2       61       5       10

將儲存格F2中的「-」字元以20個空格取代。

(2) 公式:=SUBSTITUTE(MID(SUBSTITUTE(F2,"-",REPT(" ",20)),20,20)," ","")*1

結果:2

利用 MID 函數由第20個字取20個字元,再將空格以空字串取代。

「*1」作用乃為將MID函數傳回的文字數字轉換為數值數字。

(3) 公式:=SUBSTITUTE(MID(SUBSTITUTE(F2,"-",REPT(" ",20)),40,20)," ","")*1

結果:61

利用 MID 函數由第40個字取20個字元,再將空格以空字串取代。

「*1」作用乃為將MID函數傳回的文字數字轉換為數值數字。

(4) 公式:

=SUMPRODUCT((B11=A2:A5)*(B13>=B2:B5)*(B13<=C2:C5)*ROW(D2:D5))-1

結果:4

在 SUMPRODUCT 函數中利用三個條件取出完全符合者的列號:

(B11=A2:A5)*(B13>=B2:B5)*(B13<=C2:C5)

其中「*」運算,相當於執行邏輯AND運算。

學不完.教不停.用不盡文章列表

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