網友提問,根據這篇:

Excel-使用兩個條件篩選資料(OFFSET,SMALL,ROW,MONTH,陣列公式)

問到如何延伸為多條件 AND, OR 運算,例如,參考下圖,要找出一月份項目為「食」和「住」者。邏輯運算為:「一月 AND (食 OR 住)」

Excel-使用多條件篩選資料(AND,OR)(OFFSET,SMALL,ROW,MONTH,陣列公式)


【公式設計與解析】

1. 列出敘述

儲存格F6:{=IFERROR(OFFSET($C$1,SMALL(IF((MONTH(日期)=$G$1)*
((項目=$G$2)+(項目=$G$3)),ROW(日期),""),ROW(1:1))-1,0),"")}

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

複製儲存格F6,貼至儲存格F6:F18。

(1) 處理 OR 條件

公式:(項目=$G$2)+(項目=$G$3)

判斷項目陣列中是否和儲存格G2內容相符或是和儲存格G3內容相符,傳回 TRUE/FALSE 陣列。其中運算子「+」為執行 OR 的功能。

(2) 處理 AND 條件

公式:(MONTH(日期)=$G$1)*((項目=$G$2)+(項目=$G$3))

判斷日期陣列中是否和儲存格G1內容相符,傳回 TRUE/FALSE 陣列。其中運算子「*」為執行 AND 的功能。

在陣列公式中如果符合以上二個公式者,傳回日期列號的陣列,否則,傳回空白串。

再利用 SMALL 函數,在傳回的列號陣列中,由小至大依序取出列號。

再藉由傳回的列號,代入 OFFSET 函數,取得對應的儲存格內容。

最後利用 IFERROR 函數在 OFFSET 函數結果傳回錯誤訊息時顯示空字串(空白)。


2. 列出金額

儲存格G6:{=IFERROR(OFFSET($D$1,SMALL(IF((MONTH(日期)=$G$1)*
((項目=$G$2)+(項目=$G$3)),ROW(日期),""),ROW(1:1))-1,0),"")}

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

複製儲存格G6,貼至儲存格GF6:G18。

公式原理相同。

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