動態範圍內加總方式

vvcvc

我定義名稱取得我所需要的動態範圍
想要在該動態範圍(不同分頁)內取得範圍內第5、105、205、305...列的加總

另一個名稱取得之動態範圍會隨公式日期變大
例如1/1是2*300、1/2是3*300、1/3是4*300的範圍
我想取得第5、105、205、305...列全部的加總
類似SUM(Sheet2!$D5:G5)+SUM(Sheet2!$D105:G105)+SUM(Sheet2!$D205:G205)+...

請問以上2種狀況公式該如何撰寫?

Hsieh

我定義名稱取得我所需要的動態範圍
想要在該動態範圍(不同分頁)內取得範圍內第5、105、205、305...列的加總 ...
vvcvc 發表於 2013-1-22 15:42

1/1是2*300、1/2是3*300、1/3是4*300的範圍
這句是甚麼意思?
假設名稱為Rng
要加總其中列號為5、105，205....
=SUMPRODUCT(Rng*(MOD(ROW(Rng),100)=5))
如果是加總範圍內的第5、105、205....列(當此名稱指定的起始列位並非第一列)
=SUMPRODUCT(Rng*(MOD(ROW(Rng),100)=ROW(INDEX(Rng,1,1))+4))

vvcvc

1/1是2*300、1/2是3*300、1/3是4*300的範圍
是指1/1時動態範圍的大小是2欄*300列、1/2為3欄*300列、1/3為4欄*300列
定義名稱的公式如下:
OFFSET(量產網版!$A$1,MATCH(總表!D$3,量產!$A:$A,0),3,MATCH(總表!E$3,量產!$A:$A,0)-MATCH(總表!D$3,量產網版!$A:$A,0)-12,DAY(總表!B47+1))
offset的寬我並非定值

謝謝Hsieh版主的回答
我會研究看看是不是可以成功套到我需要的地方
文件為公司內部的東西，不方便直接上傳
且我希望學到方法而不是只是得到大家協助(問題解決了會有惰性，反而沒學到東西)

vvcvc

我的說明好像有點難看懂
重新簡單說明如下:
第一個問題是1欄*N列，使用以下公式可得到解決
=SUMPRODUCT(Rng*(MOD(ROW(Rng),100)=5))
第二個問題是N欄*M列，使用以下公式可得到解決
=SUMPRODUCT(Rng*(MOD(ROW(Rng),100)=ROW(INDEX(Rng,1,1))+4))

但我想再請教Hsieh版主
第二個問題
為什麼要寫成ROW(INDEX(Rng,1,1))+4
因為我試著使用=SUMPRODUCT(Rng*(MOD(ROW(Rng),100)=5))亦可得解
使用F9發現ROW(INDEX(Rng,1,1))+4所得的結果是一個陣列，裡面只有一個數字
這段公式的用意實在想不透，可否請您再次解說?

Hsieh

回復 4# vvcvc


    ROW(INDEX(Rng,1,1))是取得該範圍第一個儲存格的列位
加4則是該範圍的第5個列號
這是配合Row(Rng)傳回的列位陣列，每個數值加上ROW(INDEX(Rng,1,1))+4所得的值成為新的陣列

vvcvc

我的動態位置取得的資料包含文字類
在相乘後會回傳#value
這種問題使用is函數可以解決嗎?

目前的問題已排除，是自己表格中誤key
想問這問題是針對未來若有相同情況發生
該資訊又一定要key在動態範圍內的欄位時的解決之道