關於weeknum使用限制?

帖子: 144
主題: 33
精華: 0
積分: 178
點名: 0
作業系統: XP
軟體版本: Office 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-9-26
最後登錄: 2023-6-23

15^#

發表於 2012-4-7 00:23 | 只看該作者

本帖最後由 vvcvc 於 2012-4-7 00:24 編輯

在這個問題的檔案中其實還有一個分頁用來輸入raw data
在每一天的資料統計格子裡用的公式如下
=SUMPRODUCT((登記表!$I$6:$I$9999=$B97)*(登記表!$N$6:$N$9999=--(LEFT(E$16,1))))
$I$6:$I$9999是raw data中日期欄位，用來和計算的分頁比對日期以計算次數
今天發現一個問題
如果我$I$6:$I$9999的日期因同一天要登記多筆記錄
於是未一格一格填入日期而是直接將資料向下拉
使用上方的公式就會認不出下拉的欄位
若一格一格輸入則沒有問題

這是什麼原因?
原本以為在$I$6:$I$9999前加一個datevalue把那些儲存格的值強迫轉成日期來跟B欄的日期比對
但似乎是因為datevalue不是陣列公式而失敗
有什麼方法可以解決嗎?

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 247
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-14

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

14^#

發表於 2012-2-23 08:21 | 只看該作者

回復 13# vvcvc

$J$2-WEEKDAY($J$2)推算出第一周的起始日期本年度起算日
$J$2:$J$367-($J$2-WEEKDAY($J$2))當日日期減去本年度起算日
($J$2:$J$367-($J$2-WEEKDAY($J$2))-1)從起算日至當日經過幾天，為了與7整除會得到0所以加1
INT(($J$2:$J$367-($J$2-WEEKDAY($J$2))-1)/7)與7相除後取整數
整數在第一周會為0，由此推算加1以後才是正確WEEKNUM

學海無涯_不恥下問

帖子: 144
主題: 33
精華: 0
積分: 178
點名: 0
作業系統: XP
軟體版本: Office 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-9-26
最後登錄: 2023-6-23

13^#

發表於 2012-2-23 00:10 | 只看該作者

不太懂為什麼要先減1再除7
之後卻又要再加1
可否解釋?

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 247
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-14

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

12^#

發表於 2012-2-22 10:17 | 只看該作者

回復 9# gong

這個方法並無法取得真正WEEKNUM
只是從當年1/1日起算每7天為一周的算法
試試
B18=SUMPRODUCT(((INT(($J$2:$J$367-($J$2-WEEKDAY($J$2))-1)/7)+1)=ROW($A1))*(K$2:K$367))

學海無涯_不恥下問

帖子: 144
主題: 33
精華: 0
積分: 178
點名: 0
作業系統: XP
軟體版本: Office 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-9-26
最後登錄: 2023-6-23

11^#

發表於 2012-2-22 01:08 | 只看該作者

gong版主B18的答案
今年剛好1/1在W01的第一天，所以W01有7天
明年W01只有6天
這樣明年是不是就要重新修改計算式?

帖子: 144
主題: 33
精華: 0
積分: 178
點名: 0
作業系統: XP
軟體版本: Office 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-9-26
最後登錄: 2023-6-23

10^#

發表於 2012-2-22 00:12 | 只看該作者

本帖最後由 vvcvc 於 2012-2-22 01:06 編輯

又多一招，二種方式試過都成功
但最後版面做成一橫一直
先來研究二位的解答再自行修改成自己需要的
感謝指導

gong版主的公式中要小小修改一個數字，一季只有3個月，應該要除以3才對
b2
=SUMPRODUCT((ROUNDUP(TEXT($J$2:$J$367,"m")/3,0)=(--RIGHT($A2,1)))*K$2:K$367)

版主 Rank: 7 Rank: 7 Rank: 7

gong

帖子: 455
主題: 8
精華: 1
積分: 480
點名: 0
作業系統: WINS home
軟體版本: OFFICE XP
閱讀權限: 100
性別: 男
來自: 平鎮
註冊時間: 2010-5-1
最後登錄: 2015-1-22

論壇貢獻勳章

9^#

發表於 2012-2-21 23:10 | 只看該作者

b18
=SUMPRODUCT((--(INT(($J$2:$J$367-DATE(2012,1,1))/7)+1)=(--(RIGHT($A18,2))))*(K$2:K$367))

知之為知之，不知為不知，誠實也！

版主 Rank: 7 Rank: 7 Rank: 7

gong

帖子: 455
主題: 8
精華: 1
積分: 480
點名: 0
作業系統: WINS home
軟體版本: OFFICE XP
閱讀權限: 100
性別: 男
來自: 平鎮
註冊時間: 2010-5-1
最後登錄: 2015-1-22

論壇貢獻勳章

8^#

發表於 2012-2-21 22:56 | 只看該作者

b2
=SUMPRODUCT((ROUNDUP(TEXT($J$2:$J$367,"m")/4,0)=(--RIGHT($A2,1)))*K$2:K$367)

b6
=SUMPRODUCT((TEXT($J$2:$J$367,"mmm")=$A6)*K$2:K$367)

知之為知之，不知為不知，誠實也！

register313

　頭銜: 麻辣路人甲

大學生 Rank: 4

帖子: 967
主題: 0
精華: 0
積分: 1001
點名: 0
作業系統: WIN XP
軟體版本: OFFICE 2003
閱讀權限: 50
性別: 男
來自: 台北
註冊時間: 2010-11-29
最後登錄: 2022-5-17

2012龍年全勤獎章熱烈參與勳章

7^#

發表於 2012-2-21 18:40 | 只看該作者

回復 6# vvcvc

季,月的公式前面已說明過了

改直式後修正如下
B2=SUM(INDIRECT(ADDRESS(ROW()*3,COLUMN())&":"&ADDRESS(ROW()*3+2,COLUMN()),1))
B6=SUMPRODUCT((MONTH($J$2:$J$400)=ROW()-5)*K$2:K$400)