網友提問,根據這篇:

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。

公式原理相同。

【延伸閱讀-FILTER函數篩選應用】

Excel-下拉式清單選取月份列出該月日期

圖片1 Excel-根據單條件和雙條件篩選資料(FILTER)

圖片1 Excel-列出指定星期幾的日期

圖片1 Excel-單一欄位篩選與跨欄位篩選

圖片1 Excel-計算分組最大值

圖片1 Excel-FILTER和OFFSET的動態陣列

圖片1 Excel-篩選資料並轉置資料

圖片1 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

圖片1 Excel-由資料清單中篩選一組

圖片1 Excel-利用FILTER函數模糊篩選

圖片1 Excel-從日期清單中區別平日和假日計算總和

圖片1 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

圖片1 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

圖片1 Excel-FILTER函數與進階篩選

圖片1 Excel-2021版新增函數的使用

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

arrow
arrow

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