贊助廠商

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

搜尋本部落格文章資料

網友提問:在 Excel 中有一個資料清單(如下圖左),如何能由日期欄位篩選出某一月份中所有特定項目的資料?

在下圖中的原始資料有:日期、項目、敘述和金額共四個欄位,要根據月份和項目二個條件來篩選出敘述和金額這二個欄位的內容,該如何處理?

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

 

【公式設計與解析】

選取原始資料清單中日期和項目二個欄位中的所有資料,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、項目。

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

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

複製儲存格F5,貼至儲存格F5:F13。

(1) IF((MONTH(日期)=$G$1)*(項目=$G$2),ROW(日期),"")

在陣列公式中,根據二個條件:

條件一:MONTH(日期)=$G$1

MONTH 函數可以取出一個日期的月號。

判斷日期陣列中每個日期的月份是否和儲存格G1內容相符,傳回 TRUE/FALSE 陣列。

條件二:項目=$G$2

判斷日期陣列中每個項目是否和儲存格G2內容相符,傳回 TRUE/FALSE 陣列。

公式中的『*』運算子,相當於執行邏輯 AND 運算。當二個條件都符合者,藉由 ROW函數傳回日期列號的陣列。否則,傳回空字串。

(2)SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數,在傳回的列號陣列中,由小至大依序取出列號。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

(3)OFFSET($C$1,第(2)式-1,0)

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

(4) IFERROR(第(3)式,"")

利用 IFERROR 函數在第(3)式的結果傳回錯誤訊息(錯誤來自第(2)式),則顯示空字串(空白)。

同理:

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

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

複製儲存格G5,貼至儲存格G5:G13。

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • Jimmy Ku
  • 非常感謝, 謝謝作者, 此篇文章, 完全解決我的問題!
  • 謝謝你光臨我的部落格。

    vincent 於 2017/01/19 23:07 回覆

  • Yu
  • 請問如果一個儲存格內有 a2b1c3a1b3,表示有2個a、1個b、3個c、1個a、3個b,下一格為a1b2a1b3,第三格為a2c1b1,想要統計出每個儲存格有多少a、b、c的話,要怎麼寫公式呢? 比方說第一格顯示a3b4c3,第二格顯示a2b5,第三格顯示a2b1c1,謝謝!
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼