贊助廠商

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

搜尋本部落格文章資料

網友根據先前的這篇文章: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,))

創作者介紹

學不完.教不停.用不盡

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


留言列表 (2)

發表留言
  • 初心者
  • SUMPRODUCT((編號=F4)*(外形=$J$1)*(尺寸=$J$2)*ROW(外形))
    圖片看不到J1、J2的欄位,是打錯了嗎?
  • 已更正。原J1和J2,要以G1和G2取代。謝謝你的提醒!

    vincent 於 2016/08/06 23:30 回覆

  • 訪客
  • 抱歉,有個問題想請教
    SUMPRODUCT((編號=F4)*(外形=$G$1)*(尺寸=$G$2)*ROW(外形)) 此定義為公式A 的公式是要放在哪個儲存格?不太懂 ,

    儲存格G4:=IF(公式A=0,"",OFFSET($D$1,公式A-1,))
    G4的查詢條件前提是要判斷公式A的成立與否,但我不懂G4儲存格要如何"判斷"公式A是否成立
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼