回覆列表
-
1 # excel函式教程
-
2 # 精進Excel
多列日期表常見有以下兩種情況,我從簡單到複雜的情況依次講解。
一、如果每行資料從左到右是按日期從小到大排列的
那麼每行最後更新的日期就是最大的那個日期,這個問題就可以轉換為求一行最大值。
直接使用MAX函式即可得到結果:
=MAX(C2:XFD2)公式解釋:取第二行,從第三列到最後一列的最大值。如果後續日期更新,公式會自動重新計算。
二、如果僅有標題是日期格式
那麼該問題轉化為求每一行最後一個非空單元格的位置,並以該位置為基準,偏移至第一行,以取得日期值。
這種情況可以使用lookup函式來實現:
=LOOKUP(1,0/(C2:XFD2<>""),$C$1:$XFD$1)公式解釋:
(1)C2:XFD2<>""判斷該行的資料是否為空,得到一個由TRUE和FALSE構成的陣列
(2)0/(C2:XFD2<>"")可以將上面得到的陣列轉換為如下形式:#DIV/0! , 0 , #DIV/0! , 0 , #DIV/0! ……
其中“0/”的作用是把符合條件的轉換為0,不符合條件的轉換為錯誤#DIV/0!
(3)查詢值為1,是由於lookup函式具有自動忽略錯誤值和向下匹配的特徵;忽略錯誤使得只保留所有0,向下匹配就是如果沒有找到結果,則返回小於查詢結果的最大值,本例中返回陣列中最後一個0所對應的單元格。
(4)$C$1:$XFD$1則充分利用了lookup最後一個引數可以偏移返回其它單元格區域的值的特點,直接返回第一行中對應的日期。
結語:
關於求某行/列的第一個/最後一個非空單元格,除了使用lookup函式還可以根據具體情況使用index+match或者sumproduct等函式來實現,除了使用函式還可以使用PowerQuery或者VBA等功能實現,應多從不同角度靈活分析,找到最適合自己的方法。
如下圖,提取每列中最後一行的日期。
在I3單元格輸入公式:=INDEX(D:D,COUNT(D:D)+1) 往右邊拖拉填充即可。
公式說明:使用index函式給定要返回的區域,再返回指定的行列單元格值,用COUNT函式計算D列中有多少個數值(日期本質就是數值)結果返回9個,標題是文字,所以不會計算在內,所以需要在後面+1才能返回最後一行的日期。