在Excel中，如果想要建立一個每年每個月星期幾數量的統計表(如下圖)，該如何處理呢？
首先建立一個微調按鈕控制項，其設定如下：
(表單控制項的操作可參考：http://isvincent.blogspot.com/2010/05/excel_7085.html)
接著輸入公式：
儲存格B3：=DATE($B$1,A3,1)
將儲存格數值格式設定為「月/日」，複製儲存格B3到儲存格B3:B14。
儲存格C3：=EOMONTH(DATE($B$1,A3,1),0)
利用EOMONTH函數，求得各月的最後一天。
(相關用法請參考：http://isvincent.blogspot.com/2010/04/excel_1137.html)
將儲存格數值格式設定為「月/日」，複製儲存格C3到儲存格C3:C14。
儲存格D3：=SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)=
COLUMN(D3)-3)*1)
複製儲存格D3到儲存格D3:I14。
其原理為：
INDIRECT($B3&":"&$C3)：以B3和C3儲存格內容(數值)轉換為儲存格位址。
ROW(INDIRECT($B3&":"&$C3))：儲存格位址轉為列數。
WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)：將列數代入WEEKDAY函數，求得星期幾的陣列。
WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)=COLUMN(D3)-3)：判斷其值是否為1,2,3…7。
WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)=COLUMN(D3)-3)*1：將邏輯判斷值轉為數字(1或0)
SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)=
COLUMN(D3)-3)*1)
將邏輯判斷值轉為的數字加總。
|
WEEKDAY：傳回符合日期的星期。給定的日預設為介於 1 (星期日) 到 7 (星期六) 之間的整數。 語法：WEEKDAY(serial_number,[return_type]) Serial_number：必要參數。要找的日期的代表序列值。日期必須使用 DATE 函數、其他公式或函數的結果來輸入。 Return_type：選用參數。決定傳回值類型的數字。 Return_type 傳回的數字 : |
儲存格K3：=SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)>5)*1)
複製儲存格B3到儲存格K3:K14。
如果判斷邏輯值轉換後的數字大於5，即表示為星期六(6)或星期日(7)，加總的結果可得週休日數。
使用微調按鈕，可以立即顯示各年度的星期幾日數。
留言列表