贊助廠商

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

搜尋本部落格文章資料

(網友提問)在 Excel 的工作表中,假如要依分組人數給予分組編號,該如何處理?

參考下圖,第一列是分組數,要自動產生分組號碼。

Excel-將數列依分組人數自動給予編號(INT,ROW)

 

【公式設計與解析】

儲存格C2:=INT((ROW(1:1)-1)/C$1)+1

先利用 ROW 函數來產生 1, 2, 3, 4, … 數列。

ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ROW(4:4)=1→...。

再利用 INT 函數取得除以分組數的『商數』。

注意其中:「ROW(1:1)-1」和最後的「+1」的小技巧。

文章標籤

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

(網友提問)如果想要根據在 Excel 工作表中的日期清單,將每個日期延後2天,但是必須跳過星期六、日,該如何處理?

這個問題很常見,例如:要計算不包含六、日的 2 個工作天的日期等。(參考下圖)

Excel-計算隔2天日期並跳過星期六日(WEEKDAY)

 

【公式設計與解析】

儲存格D2:=A2+2+(WEEKDAY(A2+2,2)>5)*(8-WEEKDAY(A2+2,2))

(1) A2+2

用以計算 2 天後的日期。

(2) WEEKDAY(A2+2,2)>5

利用 WEEKDAY 函數來找出日期對應是星期幾,在本例中使用參數 2,表示傳回值 1~7 對應至星期一~星期日。所以如果傳回值大於 5,表示該日期是星期六或星期日。公式會傳回 TRUE/FALSE

Excel-計算隔2天日期並跳過星期六日(WEEKDAY)

(3) 8-WEEKDAY(A2+2,2)

WEEKDAY(A2+2,2) 的傳回值為 1~7,8-WEEKDAY(A2+2,2)的傳回值是 7~1。

(4) (WEEKDAY(A2+2,2)>5)*(8-WEEKDAY(A2+2,2))

該公式在儲存格A2日期 2 天後為星期六時加 2 天,若為星期日則加 1 天。

文章標籤

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

(網友提問)如何在 Excel 的工作中,利用輸入年和月後,自動產生該月的月曆(標示星期幾),並且能將星期六、日加以標示,該如何處理?

參考下圖,當年輸入2019、月輸入10後,自動產生該月各日是星期幾,並且將所有的星期六日儲存格用不同色彩標示。

Excel-自動產生月曆並且標示星期六日(TEXT)


【公式設計與解析】

1. 依年月日產生星期幾

儲存格B4:=RIGHT(TEXT(DATE($A$2,$B$2,B3),"[$-zh-TW]aaa;@"),1)

(1) DATE($A$2,$B$2,B3)

利用 DATE 函數取得年月日的日期。

(2) TEXT(DATE($A$2,$B$2,B3),"[$-zh-TW]aaa;@")

將日期代入 TEXT 函數,並依『[$-zh-TW]aaa;@』格式顯示。(週一、週二、…)

(3) RIGHT(TEXT(DATE($A$2,$B$2,B3),"[$-zh-TW]aaa;@"),1

利用 RIGHT 函數取得最右邊一個字,即為一、二、…、六、日。


2. 設定星期六日的格式

如圖,選取儲存格B5:AF14(所有黃色儲存格),設定格式化的條件的規則:

規則類型:使用公式來決定要格式哪些儲存格。

規則說明:=WEEKDAY(DATE($A$2,$B$2,B$3),2)>5

Excel-自動產生月曆並且標示星期六日(TEXT)

WEEKDAY 函數中使用參數 2,星期一~星期日對應傳回值 1~7,所以傳回值 6 和 7 為假日(>5)。

Excel-自動產生月曆並且標示星期六日(TEXT)


【延伸學習】

在依年月日產生星期幾的公式中使用參數「[$-zh-TW]aaa;@」,是如何產生的呢?你可以由以下管道取得。

先選取一個日期,再進入「儲存格格式」設定中的日期部分,然後選取「日期/週三」。

image

再切換至「自訂」,即可取得參數設定。

image

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼