網友想要:在 Excel 的工作表中,找出每一列中第一個負數出現的日期,該如何處理?

參考下圖,第一列是日期列,在A欄中希望能將每一列中第一個負數出現時,查詢第一列的日期列出來。

找出每一列中第一個負數出現的日期

 

【公式設計與解析】

儲存格A2:{=IFERROR(OFFSET($B$1,0,SMALL(IF(B2:M2<0,COLUMN(B2:M2),
" "),1)-2),"")}

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

複製儲存格A2,貼至儲存格A2:A16。

COLUMN函數:傳回儲存格欄的編號。

SMALL函數:傳回資料集中第 K 個最小值。

OFFSET函數:傳回依所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。

IFERROR函數:當公式評估為錯誤時,傳回指定的值;否則,傳回公式的結果。

(1) IF(B2:M2<0,COLUMN(B2:M2)," ")

在陣列公式中,找出儲存格B2:M2中小於0者,再利用 COLUMN(B2:M2) 取得其欄的編號。如果不是小於0者,給予一個空格。

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

利用 SMALL 函數,將第(1)傳回的儲存格陣列取其中最小者(欄的編號)。

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

將第(2)式傳回欄的編號代入 OFFSET 函數中,求得第一列對應的欄位內容。

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

當同一列中如果全部的數字都沒有負數,則會顯示錯誤訊息(這是第(2)式傳回空格造成的)。再利用 IFFERROR 函數,將錯誤訊息轉換為空字串顯示。

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

arrow
arrow
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

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