網友根據先前的這篇文章:Excel-多條件的查詢(INDEX+SMALL+陣列),發現他使用的 Excel 版本無法使用 IFERROR 函數,該如何修改公式?

在下圖中,其實是要根據三個條件(編號、外形、尺寸)求得對應的售價,而且當查無資料時以空白顯示,該如何處理?

Excel-多條件的查詢(OFFSET,SUMPRODUCT)

 

【公式設計與說明】

選取儲存格A1:C19,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號、外形、尺寸。

公式A:SUMPRODUCT((編號=F4)*(外形=$G$1)*(尺寸=$G$2)*ROW(外形))

(編號=F4)*(外形=$G$1)*(尺寸=$G$2):在 SUMPRODUCT 函數中傳回同時符合三個條件的 TRUE/FALSE 結果,其中「*」運算,相當於執行邏輯 AND 運算,並會在運算後將 TRUE/FALSE 陣列轉換為 1/0 陣列。

ROW(外形):用以取得符合條件的「列號」,例如:ROW(2:2)=2、ROW(8:8)=8。

儲存格G4:=IF(公式A=0,"",OFFSET($D$1,公式A-1,))

若公式A傳回 0,表示找不到相符的列號,就以空白顯示,若有傳回列號,則以 OFFSET 函數求得對應的售價。

原始公式→儲存格G4:
=IF(SUMPRODUCT((編號=F4)*(外形=$G$1)*(尺寸=$G$2)*ROW(外形))=0,"",
OFFSET($D$1,SUMPRODUCT((編號=F4)*(外形=$G$1)*(尺寸=$G$2)*ROW(外形))
-1,))

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

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