贊助廠商

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

搜尋本部落格文章資料

廣告贊助

回答網友提問:在下圖中左有一個 Excel 的資料表,如何取出指定日期的所有資料?

在下圖中的「預製日」欄位是一個日期清單,現在我們要在儲存格G1中輸入一個日期,在下圖右列出預製日是該日期的所有資料清單,該如何處理?

這類題目,已是我接觸的問題中,最常被問及的。可見需求很高,所以再次不厭其煩的解說,希望能對網友有幫助。

Excel-取出合於條件的資料(OFFSET,ROW,SMALL,陣列公式)

【公式設計與解析】

首先,為了讓公式易讀好懂,先選取儲存格B1:B31,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:預製日。

接著,輸入公式,儲存格H2:

{=IFERROR(OFFSET($A$1,SMALL(IF(預製日=$G$1,ROW(預製日),""),ROW(1:1))-1,COLUMN(A:A)),"")}

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

複製儲存格H2,貼至儲存格H2:K2,複製儲存格H2:K2,並往下各列貼上。

(1) IF(預製日=$G$1,ROW(預製日),"")

在陣列公式中,判斷預製日陣列如果和儲存格G1相同者,傳回其儲存格的列號,否則傳回空字串("")。ROW 函數可以傳回儲存格的列號,而當條件不成立時傳回空字串的用意是為了讓之後的公式傳回一個錯誤訊息。

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

利用 SMALL 函數由小到大取出第 1, 2, 3, ... 較小值。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3。當公式向下複製時,若 SMALL 函數發生錯誤時,會傳回錯誤訊息。

(3) OFFSET($A$1,第(2)式-1,COLUMN(A:A))

利用第(2)式的傳回值,代入 OFFSET 函數,以儲存格A1為起點,傳回對應的儲存格內容。公式中的「第(2)式-1」是因為 OFFSET 函數中的 row 參數是以 0 為起始。

其中 COLUMN(A:A)=1,當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4。

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

若公式有傳回錯誤息時,利用 IFERROR 函數將其顯示為空字串。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • Doris Hung
  • 請問大師:
    假設多個儲存格內容分別有A1,A1,A1,B1,B0.5,C1,C1...
    我要把A,B,C各別相加總,
    例如..
    A1,A1,A1-->A3
    B1,B0.5-->B1.5
    要怎麼設定公式呢??
    求救~~
  • 請參考:http://isvincent.pixnet.net/blog/post/47263020

    vincent 於 2017/05/20 23:11 回覆

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼