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

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

14^# 跳轉到 »

發表於 2013-3-13 23:07 | 只看該作者

回復 13# Hsieh

感謝版大耐心的回覆,
這些公式對我來說很需要時間來消化,請容我先研究.....

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

13^#

發表於 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)

學海無涯_不恥下問

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

12^#

發表於 2013-3-13 16:40 | 只看該作者

回復 11# Hsieh

版大,

1) 我不懂這個公式,能否詳解? y=INDIRECT("H"&'KWH(New)'!x&":H"&ROW())..............請問紅色的"x"代表的意義是什麼?
2) 前面的問題解決了,但發現 KWH_R3的檔,B欄還出現一些之前未發現的錯誤,例:B301:B319應該全為1,但卻只有B301是1(只舉其中一例)

3) 以 KWH_R3為例,A欄若要仿照B欄的作法,時間符合0:00:00~23:55:00且H欄位大於0.1,為有效區間,但不區分日期,例如:符合0:00:00~23:55:00的條件,從2/2 6:05~6:55是第一個有效區間,則標示為1(A86:A96),其同日第2個有效區間為11:10~12:50則標示為2(A147:A167),A301:A319標示為5….依此類推,其公式應該怎麼下?

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

11^#

發表於 2013-3-13 14:47 | 只看該作者

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

KWH_R3.rar (136.43 KB)

學海無涯_不恥下問

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

10^#

發表於 2013-3-13 11:00 | 只看該作者

回復 8# Hsieh
版大好,

我的表格依作業稍作了些變動,不知為何B欄的2/2的值某些會出現#VALUE!
我已查了整整一天,但找不出原因為何,請版大幫幫忙!!

另外這個公式 =INDIRECT("H"&'KWH(New)'!x&":H"&ROW())..............請問紅色的"x"代表的意義是什麼?

KWH_R3.zip (139.83 KB)

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

9^#

發表於 2013-3-11 11:39 | 只看該作者

回復 8# Hsieh
感謝版大的解說,目前運作的結果ok,不過公式我還得細細研究,有問題再請教!! 感謝!!

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

8^#

發表於 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))  '同日期的最後列號(可刪除)

學海無涯_不恥下問

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

7^#

發表於 2013-3-8 12:38 | 只看該作者

回復 6# Hsieh

請教版大,
I13=IF((B13>0)*(G13>0.1),SUMPRODUCT((y>0.1)*(OFFSET(y,-1,)<0.1)*1)+n,0)
例如這個公式,這是自定義名稱?比較複雜的情形,沒有"自定義名稱"是否無法運作?
上面的公式用了y/ n二個定義,但為何我去看名稱管理員的時候,其中的自定義有8個?可否幫忙解說?

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

6^#

發表於 2013-3-7 19:59 | 只看該作者

回復 5# PJChen
試試看

KWH_R2.zip (196.98 KB)

學海無涯_不恥下問

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

5^#

發表於 2013-3-7 15:25 | 只看該作者

回復 3# Hsieh

版大您好,
昨天作業中又發現有新的難題,遂再提問,但今日查看不知為何PO上的問題自動不見了,只好再重來一次!!
我的工作表因為資料會不斷增加,是否這種情形不適合用陣列?想請問何種公式可以符合我的需求?
問題如下:
每個日期的時間符合0:00:00~23:55:00且G欄位大於0.1,為有效區間,例如:2/2 符合0:00:00~23:55:00的條件,從2/2 6:05~6:55是第一個有效區間,則標示為1,其同日第2個有效區間為11:10~12:50則標示為2….依此類推,每日的有效區間都從1開始數

KWH_R2.zip (173.3 KB)

TOP

[發問] 求公式,不知如何下標題?

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]