麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 求公式,不知如何下標題? [打印本頁]

作者: PJChen 時間: 2013-2-23 16:45 標題: 求公式,不知如何下標題?

請教函數高手,
附上檔案,請大家幫忙! [attach]14240[/attach]

條件1 資料固定從第13列開始
條件2 F欄時間都固定相隔5分鐘
條件3 每個日期的時間符合0:00:00~23:55:00時,開始從1計算,不符合條件則為0.
要求1 如何使符合條件的A145~A1872從1數到1728 ?
例: 2013/2/1及2013/2/8 不符合條件3的規範,A欄就標示為0
要求2 排除不符合條件的2/1和2/8,其餘依不同日期從1數到最後(288),不符合條件則為0.
例: 日期2/2符合條件的B145~B432從1數到288 ?
日期2/3符合條件的B433~B720從1數到288 ?....依此類推
要求3 H欄位為F欄位的時間計算,在格式為"通用格式"下,計算F欄時間的相減值
例: H13為F13-F12=0,H14為F14-F13=5....依此類推

作者: PJChen 時間: 2013-2-28 16:14

回復 1# PJChen
提問了好多天,都不見有人回覆,不知是否無法使用函數公式呢?

作者: Hsieh 時間: 2013-2-28 17:37

回復 2# PJChen

是這樣的結果嗎?
[attach]14274[/attach]

作者: PJChen 時間: 2013-2-28 22:43

回復 3# Hsieh

版主太帥了!
這就是我要的,我可以繼續下個步驟了,若有不懂的再請教您!!...感謝您.

作者: PJChen 時間: 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開始數
[attach]14299[/attach]

作者: Hsieh 時間: 2013-3-7 19:59

回復 5# PJChen
試試看
[attach]14303[/attach]

作者: PJChen 時間: 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個?可否幫忙解說?

[attach]14311[/attach]

作者: Hsieh 時間: 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))  '同日期的最後列號(可刪除)

作者: PJChen 時間: 2013-3-11 11:39

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

作者: PJChen 時間: 2013-3-13 11:00

回復 8# Hsieh
版大好,

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

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

作者: Hsieh 時間: 2013-3-13 14:47

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

作者: PJChen 時間: 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….依此類推,其公式應該怎麼下?

作者: Hsieh 時間: 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)

[attach]14356[/attach]

作者: PJChen 時間: 2013-3-13 23:07

回復 13# Hsieh

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

作者: PJChen 時間: 2013-3-31 13:14

回復 13# Hsieh

版大好,
原12樓所發問的問題,本想要自行解答,奈何腦袋實在不開竅,到現在還是未參透也未解決!!

我已經被別人催得要著火了,只好再求版大幫忙....
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….依此類推,其公式應該怎麼下?

[attach]14496[/attach]

作者: Hsieh 時間: 2013-3-31 16:55

回復 15# PJChen

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

作者: PJChen 時間: 2013-3-31 17:57

回復 16# Hsieh

感謝大大,

三番二次麻煩您!!這階段執行沒問題了,我得進行下個步驟了,週三次做不完,我就要斷頭了.... 感謝幫了大忙了!!

作者: PJChen 時間: 2013-3-31 19:49

回復 16# Hsieh

大大, 不好意思...

之前的R3檔有錯如下,已依您的指點作了修改....到現階段:將rng定義日期範圍不包刮標題列,會出錯,請看現在的R4檔(之前的不理會)
10F的問題: 我的表格依作業稍作了些變動,不知為何B欄的2/2的值某些會出現#VALUE!
11F時大大建議將: rng定義日期範圍不包刮標題列即可解決

[attach]14499[/attach]

作者: Hsieh 時間: 2013-3-31 22:05

回復 18# PJChen

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

[attach]14501[/attach]

作者: PJChen 時間: 2013-3-31 23:11

回復 19# Hsieh

謝謝版大,

工作太趕,囫圇吞棗,消化不良....
只能事後多補強.

作者: PJChen 時間: 2013-4-2 21:26

回復 19# Hsieh

大大,

趁現在檔案可以打開,我把自定義的資料,全部copy出來放在L:S欄,但檢查後發現A:B/ N:P欄出現很多的#N/A,可否請大大撥出一些時間幫忙檢查,是否要修改部份公式?
不好意思!!因為沒有修改它,我在公司開啟幾乎是當掉的,我就沒法作其他事了!!
[attach]14519[/attach]

作者: Hsieh 時間: 2013-4-2 22:05

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

作者: Bodhidharma 時間: 2013-4-3 13:11

回復 22# Hsieh

這邊的檔案我都是一開啟就當機，把公式自動運算改成手動，也是一重算就當機
有點詭異…因為這台的性能應該沒有問題(Intel Core i5 2450M, 8G memory)
不過換台電腦就沒問題了(Intel Core i3-2120, 4G memory)

作者: PJChen 時間: 2013-4-3 14:03

回復 23# Bodhidharma

我也很苦惱,所以才想改為不要自定義,但裡面公式太複雜,我自己實在改不出來,若有能人熟悉函數公式的,可否幫忙修改?當然我知道自定義公式可以省略些公式的格式,但權衡檔案跑不動的情況,我是寧願多幾行公式,但求開檔存檔順遂....
附件的公式已改為手動,所以至少可以開檔,自定義我也還保留,但改為"非自定義公式"我就不行了.....
求...熟悉函數公式的能人,可以幫助修改,將檔案中的"自定義公式"全部改掉??這只是一個範例檔,資料會一直增加,最好不要使用陣列公式.....

[attach]14524[/attach]

作者: Hsieh 時間: 2013-4-3 22:17

回復 24# PJChen

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

作者: PJChen 時間: 2013-4-3 23:32

回復 25# Hsieh

大大,
我看到您已經將自定義名稱改成輔助公式,我太開心了,真多謝您!!

作者: PJChen 時間: 2013-4-12 21:15

回復 25# Hsieh

大大,

請教:2/4之後的日期,H欄值大於0.1,B欄要從1開始數,但不知為何從卻出現2? [attach]14649[/attach]

作者: Hsieh 時間: 2013-4-14 10:09

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

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

作者: PJChen 時間: 2013-4-14 12:31

回復 28# Hsieh

大大,

請問B2公式中,紅色及藍色的字,我看不懂它在公式中代表什麼?請幫忙解說?謝謝!!
B2=(D2<>0)*(SUMPRODUCT(ISNUMBER(1/((INDIRECT("R"&MATCH(M2,M:M,0)&"C13:RC13",0)=M2)*((INDIRECT("R"&MATCH(M2,M:M,0)&"C4:RC4",0)<>0)*(INDIRECT("R"&MATCH(M2,M:M,0)-1&"C4:R[-1]C4",0)=0))))*1)+P2)

作者: Hsieh 時間: 2013-4-14 12:43

回復 29# PJChen

INDIRECT函數第一個參數是儲存格位址字串，第2個參數是字串的形式
第2個參數為1或省略則是以A1形式判讀第一個參數
第2個參數為0則以R1C1形式判讀第一個參數
R後面的數字表示第幾列，C後面數字表示第幾欄
數字已[](中括號)包圍，則是以公式所在位置為原點，位移的量
RC表示公式所在位置
R1C1為A1儲存格
R[1]C[1]為公式所在位置向下1列，向右1欄的位置
R[-1]C[-1]為公式所在位置向上1列，向左1欄的位置

作者: PJChen 時間: 2013-4-14 15:31

回復 30# Hsieh

請問..

"R"&MATCH(M5,M:M,0) > R是表示R1 (1省略)& MATCH(M5,M:M,0)表示Cxx (第幾個column?)

至於 "C13:RC13" 我還是解讀不出

不過依照你修改的公式,已可執行.

作者: Hsieh 時間: 2013-4-14 15:41

回復 31# PJChen

"R"&MATCH(M5,M:M,0)會指向日期出現的第一個位置
如果以其中2013/2/2出現在M欄的第一位置就會是"R2"
"R2"這字串結合後段&"C13:RC13"
整個字串就等於"R2C13:RC13"
"C13"代表工作表的第13欄，也就是M欄
假如這公式是寫在O15
那麼INDIRECT("R2C13:RC13"0)就會是指向M2:M15

作者: PJChen 時間: 2013-4-14 19:01

回復 32# Hsieh

謝謝版大清楚的解說,我再自行理解一下..

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)