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

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2013-1-22 15:42 | 只看該作者

動態範圍內加總方式

我定義名稱取得我所需要的動態範圍
想要在該動態範圍(不同分頁)內取得範圍內第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

超級版主 Rank: 8 Rank: 8

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

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

2^#

發表於 2013-1-22 23:03 | 只看該作者

我定義名稱取得我所需要的動態範圍
想要在該動態範圍(不同分頁)內取得範圍內第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))

學海無涯_不恥下問

TOP

vvcvc

中學生 Rank: 2

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

3^#

發表於 2013-1-23 08:41 | 只看該作者

本帖最後由 Hsieh 於 2013-1-23 10:37 編輯

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

TOP

vvcvc

中學生 Rank: 2

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

4^#

發表於 2013-1-23 10:39 | 只看該作者

我的說明好像有點難看懂
重新簡單說明如下:
第一個問題是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所得的結果是一個陣列，裡面只有一個數字
這段公式的用意實在想不透，可否請您再次解說?

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

5^#

發表於 2013-1-23 14:11 | 只看該作者

回復 4# vvcvc

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

學海無涯_不恥下問

TOP

vvcvc

中學生 Rank: 2

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

6^#

發表於 2013-1-23 15:11 | 只看該作者

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

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

TOP