求公式,不知如何下標題? - Excelㄧ般區

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

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

2^#

發表於 2013-3-7 19:59 | 顯示全部帖子

回復 5# PJChen
試試看

KWH_R2.zip (196.98 KB)

學海無涯_不恥下問

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

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

3^#

發表於 2013-3-8 14:54 | 顯示全部帖子

回復 7# PJChen

a=OFFSET('KWH (原始)'!$E$13,,,COUNT('KWH (原始)'!$E:$E),)  'E欄資料範圍
ar=SUMPRODUCT((INT(a)=INT('KWH (原始)'!$E161))*1) '計算與同列日期相同數量
k=MATCH(1,1/(INT(a)=INT('KWH (原始)'!$E161)),0) '找到日期的開始位置
n=SUMPRODUCT((INT(OFFSET(y,,-2))<>INT(OFFSET(y,-1,-2)))*(OFFSET(y,-1,)>0.1)*(y>0.1)*1)  '計算日期變化時，前一日的最後一筆與當日第一筆均超過0.1的數量
rng=OFFSET('KWH (原始)'!$E$12,k,,ar,)  '同日期的範圍
x=ROW(INDEX(rng,1,1)) '同日期第一個位置的列號
y=INDIRECT("G"&x&":G"&ROW())  '同日期第一個位置到本列的範圍
z=LOOKUP(2,1/(INT(a)=INT('KWH (原始)'!$E161)),ROW(a))  '同日期的最後列號(可刪除)

學海無涯_不恥下問

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

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

4^#

發表於 2013-3-13 14:47 | 顯示全部帖子

回復 10# PJChen
出錯的原因在定義名稱n會在第一個日期出現錯誤值
n為計算剛好日期轉換時同為大於0.1的數量
而標題列(文字)經INT處理會變成錯誤值
把rng定義日期範圍不包刮標題列即可解決

KWH_R3.rar (136.43 KB)

學海無涯_不恥下問

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

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

5^#

發表於 2013-3-13 18:02 | 顯示全部帖子

回復 12# PJChen

x=ROW(INDEX('KWH(New)'!rng,1,1))
rng所得到的是同一日期的範圍
INDEX('KWH(New)'!rng,1,1)
就參照到rng的第一個儲存格(同日期的第一筆儲存格)
ROW(INDEX('KWH(New)'!rng,1,1))
傳回該位置的列號
y=INDIRECT("H"&'KWH(New)'!x&":H"&ROW())
就傳回同日期第一位置到公式所在列的H欄位範圍
要解決錯誤值只能在判斷n值時加入判斷
將n的公式改為
=SUMPRODUCT((INT(OFFSET('KWH(New)'!y,,-2))<>INT(IF(ISNUMBER(OFFSET('KWH(New)'!y,-1,-2)),OFFSET('KWH(New)'!y,-1,-2),OFFSET('KWH(New)'!y,,-2))))*(OFFSET('KWH(New)'!y,-1,)>0.1)*('KWH(New)'!y>0.1)*1)

KWH_R3.rar (137.42 KB)

學海無涯_不恥下問

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

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

6^#

發表於 2013-3-31 16:55 | 顯示全部帖子

回復 15# PJChen

計算B欄上下儲存格相異的數量
A13=(B13>0)*SUMPRODUCT(($B$13:B13<>0)*($B$12:B12<>$B$13:B13))

KWH_R3.rar (137.42 KB)

學海無涯_不恥下問

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

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

7^#

發表於 2013-3-31 22:05 | 顯示全部帖子

回復 18# PJChen

每日資料範圍少了第一個位置
定義名稱k減1即可

KWH_R4.rar (162.34 KB)

學海無涯_不恥下問

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

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

8^#

發表於 2013-4-2 22:05 | 顯示全部帖子

回復 21# PJChen
定義名稱中的公式，換成寫在儲存格內會產生不同效果
你在定義名稱內所見的公式都是一般公式
但在儲存格內可能就必須使用陣列公式
開啟速度緩慢這是無法避免的
因為你的需求複雜，要取出每個分類的範圍就已經是個動態，每個儲存格均須重算位置
再加上你的電腦效能太差，根本不適合使用2007以上版本(因為我電腦開啟不須超過1分鐘，你卻要1小時)

學海無涯_不恥下問

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

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

9^#

發表於 2013-4-3 22:17 | 顯示全部帖子

回復 24# PJChen

應該是動態範圍造成的計算重複
加兩欄輔助欄試試看

KWH_R4.rar (197.79 KB)

學海無涯_不恥下問

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

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

10^#

發表於 2013-4-14 10:09 | 顯示全部帖子

本帖最後由 Hsieh 於 2013-4-14 10:12 編輯

回復 27# PJChen
計算兩日交換時都符合規則的數量範圍問題

KWH_R4_Hsieh.rar (190.31 KB)

學海無涯_不恥下問