贊助廠商

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

網友問到一個 Excel 的問題:

根據前一篇文章:Excel-依據日期區間列出各月人員清單(OFFSET,ROW,COLUMN,陣列公式),如果日期有跨年時,該如何處理?

本例以2015年7月~12月、2016年1月~6為週期,來列出各月學員的清單。

Excel-依據日期區間列出各月(跨年)人員清單(OFFSET,ROW,COLUMN,陣列公式)

 

【公式設計與解析】

先選取儲存格A1:C23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學員、開始、結束。

儲存格E3:

{=IFERROR(OFFSET($A$1,SMALL(IF((DATE(YEAR(開始),MONTH(開始),1)<=
DATE(2015,COLUMN(G:G),1))*(DATE(YEAR(結束),MONTH(結束),1)>=
DATE(2015,COLUMN(G:G),1)),ROW(學員),""),ROW(1:1))-1,0,1,1),"")}

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

條件一:DATE(YEAR(開始),MONTH(開始),1)<=DATE(2015,COLUMN(G:G),1)

DATE(YEAR(開始),MONTH(開始),1):利用 DATE 函數找出開始的日期陣列中各月的第1天。

DATE(2015,COLUMN(G:G),1):利用 DATE 函數找出 2015 年7月的第1天。

COLUMN(G:G)=7,向右複製公式時,COLUMN(G:G)=7→COLUMN(H:H)=8→...COLUMN(L:L)=12。

條件二:DATE(YEAR(結束),MONTH(結束),1)>=DATE(2015,COLUMN(G:G),1)

複製儲存格E3,貼至儲存格E3:J24。

 

儲存格K3:

{=IFERROR(OFFSET($A$1,SMALL(IF((DATE(YEAR(開始),MONTH(開始),1)<=
DATE(2016,COLUMN(A:A),1))*(DATE(YEAR(結束),MONTH(結束),1)>=
DATE(2016,COLUMN(A:A),1)),ROW(學員),""),ROW(1:1))-1,0,1,1),"")}

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

條件一:DATE(YEAR(開始),MONTH(開始),1)<=DATE(2016,COLUMN(A:A),1)

DATE(2016,COLUMN(A:A),1):利用 DATE 函數找出 2016 年1月的第1天。

COLUMN(A:A)=1,向右複製公式時,COLUMN(A:A)=1→COLUMN(B:B)=2→...COLUMN(F:F)=6。

條件二:DATE(YEAR(結束),MONTH(結束),1)>=DATE(2016,COLUMN(A:A),1)

複製儲存格E3,貼至儲存格K3:P24。

詳細說明,可參考前一篇文章:Excel-依據日期區間列出各月人員清單(OFFSET,ROW,COLUMN,陣列公式)

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

網友問到 Excel 的問題:如何將 2015/05/25 轉換為 2015年05月25日?

參考下圖,使用者在輸入日期時,可能會輸入數值格式的 2015/05/25,其數值置於D欄(本例為:42149),也可能輸入的是文字格式的 2015/05/25 字串。相同公式不一定都適用於數值和文字格式的日期格式。

Excel-將西元年日期加上年月日(TEXT,YEAR,MONTH,DAY)

【公式設計與解析】

儲存格B2:

=YEAR(A2)&"年"&TEXT(MONTH(A2),"00")&"月"&TEXT(DAY(A2),"00")&"日"

(1) YEAR(A2)&"年"

利用 YEAR 函數取出儲存格A2中 4 碼的年數。

(2) TEXT(MONTH(A2),"00")&"月"

利用 MONTH 函數取出儲存格A2中的月數,因為函數傳回的數值,所以 02 會轉換為 2,因此利用 TEXT 函數透過參數『"00"』,將其顯示為 2 碼的月數。

(3) TEXT(DAY(A2),"00")&"日"

利用 DAY 函數取出儲存格A2中的日數,再透過 TEXT 函數將其顯示為 2 碼的日數。

這個公式適用於數值格式的日期轉換,也適用於文字格式的日期轉換。

複製儲存格B2,貼至儲存格B2:B10,再貼至儲存格B13:B21。

 

如果,你採用以下的公式。

儲存格C2:=LEFT(A2,4)&"年"&MID(A2,6,2)&"月"&RIGHT(A2,2)&"日"

該公式利用 LEFT、MID、RIGHT 函數取出儲存格年、月、日三個位置的內容,該公式不適用於數值格式的日期格式,因為其會以一個數值(參考D欄)來拆解年、月、日三個位置的內容。(參考上圖)

如果是數值格式的日期格式,你可以使用設定儲存格格式的方式。

自訂數值格式:yyyy"年"mm"月"dd"日"

不用再透過公式設定。

Excel-將西元年日期加上年月日(TEXT,YEAR,MONTH,DAY)

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼