贊助廠商

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

搜尋本部落格文章資料

(網友提問)如何在 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 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (5)

發表留言
  • jijane
  • 您的電腦教學對我們工作的幫助很大,
    真的很謝謝您的無私分享!
    請問我可以求救EXCEL的問題嗎?
    (其實問題很笨但我無法改悄悄話,只好見笑了)
    我在第1個工作表建立基礎資料,
    第2至23個工作表係利用陣列公式OFFSET以及函數VLOOKUP自動抓取第1個工作表的相應內容,
    但存檔時會彈出稍微影響逼真度的警示,
    利用巨集(從網路上找的以及自己錄製的)將工作表各自另存新檔時,
    不是存取失敗就是要我偵錯,
    常常無法完全成功,
    請問我可以怎麼修正?
    (需要檔案mail給您以了解問題所在嗎?)
    感恩~~~
  • vincent.teachergmail .com

    vincent 於 2017/12/07 14:50 回覆

  • Chiner7
  • [$-zh-TW]aaa;@ 這個參數我發現在某些版本好像會有些問題
    [$-804]aaa;@ 所以改用回這樣的參數就比較沒這個問題

    上網查了這問題的資料
    A1/格式/自訂/m/d[$-804] (aaa)亦可,正確應該[$-804]m/d (aaa),/確定,輸入4/4然後下拉。
    [$-404]和[$-804]是中文常見跟常用的自訂類型,後面加上日期(星期)代碼(參考下列網址],頂多再知道[$-409]是英文就夠了,其他如日文[$-411],[$-412]韓文...應該不是很重要....
  • 謝謝你提供寶貴而實用的資訊。

    vincent 於 2018/06/14 21:02 回覆

  • 訪客
  • 您好~感謝你的回覆.那篇製作萬年月曆,之前就有看過了~也有將裡面的公式用在工作報表中,非常好用~
    可是目前遇到一問題, 日期(B3) 能否為自動帶出的 內容是月+日 (如:6/29),但卻是以日的方式呈現(如:29)呢?
  • 請參考:http://isvincent.pixnet.net/blog/post/48004563

    vincent 於 2018/06/30 21:02 回覆

  • 訪客
  • 您好: 能麻煩您指點一下嗎??
    我有二個工作表是相互連結的 A表是記錄表 B表是統計表
    依照記錄表內的資料 1.日期 2.人員 3.數量傳回統計表內
    記錄表內的日期 格式-為日期格式:2018/7/5
    統計表內的日期是用版大的公式帶出的IF(MONTH(DATE($B$1,$E$1,COLUMN(K:K)))=$E$1,DAY(DATE($B$1,$E$1,COLUMN(K:K))),"") 格式-為自訂格式-d : 5
    二個工作表的日期因為格式不同就連結不起來了..
    目前是將統計表的日期格式改以手key的方式 想請問除此之外有沒有其他方式呢??
    還請版大能不厭其煩的指點一下...感激不盡~

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼