[發問] 請問動態資料中，如何匯總相同值的數字

帖子: 35
主題: 18
精華: 0
積分: 60
點名: 0
作業系統: Windows7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2011-9-21
最後登錄: 2022-10-2

10^#

發表於 2011-10-4 22:07 | 只看該作者

回復 8# Hsieh

超感謝版大的
這問題困擾我超久了
這個禮拜都在研讀Execl
總算看的懂一些版大的公式了
我把它拆解成很多個公式
一個一個去研究
終於大部分看懂了
真佩服版大能夠瞭解這麼複雜的公式
還能活用它
您真神阿
我現在複製您的公式
就能查詢其他欄位的資料
我想
等我多練習幾次您的公式
應該會獲益良多～
總之
萬分感謝您！！！:D

帖子: 35
主題: 18
精華: 0
積分: 60
點名: 0
作業系統: Windows7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2011-9-21
最後登錄: 2022-10-2

9^#

發表於 2011-10-2 10:07 | 只看該作者

回復 8# Hsieh
感謝前輩的回覆
不過我還是沒完全懂
我現在正在努力自修中
等過幾天
再來好好研究前輩的公式
我想到那時候
應該就比較能提出不懂的地方來請教前輩了
現在是太多不懂了... >.<

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

8^#

發表於 2011-9-30 16:16 | 只看該作者

本帖最後由 Hsieh 於 2011-9-30 18:08 編輯

回復 7# hsien33
刪除儲存格會造成定義參照錯誤改用INDIRECT指定範圍
A=INDEX(INDIRECT("資料!$A:$A"),MATCH(統計!$B$2,INDIRECT("資料!$A:$A"),0)+3,1) 找到該姓名的第一列資料位置
B=OFFSET(A,MATCH("小計",A:INDIRECT("資料!$A$65536"),0)-2,) 該姓名資料範圍第一列資料位置向下找到第一個"小計"向上1格(資料最後一筆位置)
P=MATCH("保費小計",OFFSET(A,-2,,,256),0) 該姓名資料範圍標題為"保費小計"的位置
Rng=OFFSET(A,,,ROW(B)-ROW(A)+1,) A欄資料範圍
Rng1=OFFSET(A,,P-1,ROW(B)-ROW(A)+1,) "保費小計"欄位資料範圍

不過我認為要將資料整理成如下圖的資料庫再來計算會比較好

A=INDEX(INDIRECT("資料!$A:$A"),MATCH(工作表1!$A3,INDIRECT("資料!$A:$A"),0)+3,1) 找到該姓名的第一列資料位置
B=OFFSET(A,MATCH("小計",A:INDIRECT("資料!$A$65536"),0)-2,) 該姓名資料範圍第一列資料位置向下找到第一個"小計"向上1格(資料最後一筆位置)
P=MATCH(工作表1!C$1,OFFSET(A,-2,,,256),0)+IF(工作表1!C$1="換算保費小計",0,MOD(COLUMN()-3,3)) 該姓名資料範圍標題為"保費小計"的位置
Rng=OFFSET(A,,,ROW(B)-ROW(A)+1,) A欄資料範圍
Rng1=OFFSET(A,,P-1,ROW(B)-ROW(A)+1,) "保費小計"欄位資料範圍

分區加總.rar (11.89 KB)

學海無涯_不恥下問

帖子: 35
主題: 18
精華: 0
積分: 60
點名: 0
作業系統: Windows7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2011-9-21
最後登錄: 2022-10-2

7^#

發表於 2011-9-30 00:07 | 只看該作者

回復 6# Hsieh

按照您的作法
真的可以使用耶～
可是當我在"資料"工作表中貼入新的姓名C資料時
又不行了
我猜是因為姓名A、姓名B我都把許多欄位刪除後才把資料放上來
姓名C就直接貼上
所以才不行
可以請前輩再麻煩一下
為我大約解釋一下
A=INDEX(資料!$A$A,MATCH(統計!$B$2,資料!$A$A,0)+3,1)
B=OFFSET(A,MATCH("小計",A:資料!$A$65536,0)-2,)
P=MATCH("保費小計",OFFSET(A,-2,,,256),0)
Rng=OFFSET(A,,,ROW(B)-ROW(A)+1,)
Rng1=OFFSET(A,,P-1,ROW(B)-ROW(A)+1,)
=SUMPRODUCT((Rng=B4)*Rng1)
的意思
我知道這樣的請求很不妥
但是我看了一個小時
還是看不出個所以然
只好提出這無理的要求
麻煩前輩了

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

6^#

發表於 2011-9-29 22:43 | 只看該作者

本帖最後由 Hsieh 於 2011-9-29 22:44 編輯

回復 5# hsien33
定義下列名稱
A=INDEX(資料!$A:$A,MATCH(統計!$B$2,資料!$A:$A,0)+3,1)
B=OFFSET(A,MATCH("小計",A:資料!$A$65536,0)-2,)
P=MATCH("保費小計",OFFSET(A,-2,,,256),0)
Rng=OFFSET(A,,,ROW(B)-ROW(A)+1,)
Rng1=OFFSET(A,,P-1,ROW(B)-ROW(A)+1,)

下列儲存格輸入公式
統計!B2用來輸入姓名
統計!B4以下輸入銀行名稱
統計!C4=SUMPRODUCT((Rng=B4)*Rng1) 向下複製

分區加總.rar (9 KB)

學海無涯_不恥下問

帖子: 35
主題: 18
精華: 0
積分: 60
點名: 0
作業系統: Windows7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2011-9-21
最後登錄: 2022-10-2

5^#

發表於 2011-9-29 22:21 | 只看該作者

回復 4# Hsieh

感謝Hsieh 前輩的回覆
針對您說的第一種方式
由於我是從系統中拉出的報表取得資料
不只有姓名A、姓名B兩個
而是有20來個
所以如果一一改表格
我想會非常耗時間
至於您提到的第二種方式
我覺得相當可行
只是多一道功夫而已
但是小弟我剛開始接觸函數
第二種方式可以請前輩指點我該如何寫這個函數嗎？
重點也是卡在
動態範圍中我該如何取得每個姓名裡
客戶的保費小計欄位資料
再次謝過前輩了

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

4^#

發表於 2011-9-29 22:04 | 只看該作者

回復 3# hsien33

此種表格已經不能成為單一資料庫使用
解決方法有2
1.將欄位設置成相同欄位(如下圖)

2.將每個客戶分開計算，再將各個計算結果加總(如下圖)

學海無涯_不恥下問

帖子: 35
主題: 18
精華: 0
積分: 60
點名: 0
作業系統: Windows7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2011-9-21
最後登錄: 2022-10-2

3^#

發表於 2011-9-29 20:18 | 只看該作者

回復 2# ANGELA

是的
抱歉，我想了很久還是不知該如何表達出我的意思
重點就是
"資料"工作表中的資料是會變動的
我如何每次都能在"統計"工作表中擷取到"資料"工作表中我想要的數值呢？
我知道可以先建立名稱定義
再利用 INDIRECT(A) INDIRECT(B)
來回傳我想要的資料
但問題是
1. 姓名A 的資料包含了A2～M52
   姓名B 的資料  包含了A55～J100
   這樣一來我就無法定義名稱了
2."資料"工作表中的資料
   欄位常會變動
   有賣的商品名稱才會出現
   沒有賣的商品名稱欄位就不會出現
   這樣一來
   我就更不知要如何取得格內？的資料了
所以想跟各位前輩求救
是否能請各位幫忙
將？內填上公式
教導小弟如何用函數得到？內的資料
再次拜託各位了～

版主 Rank: 7 Rank: 7 Rank: 7

ANGELA

帖子: 812
主題: 7
精華: 0
積分: 862
點名: 0
作業系統: XP
軟體版本: 2007
閱讀權限: 100
性別: 男
來自: 台中
註冊時間: 2010-5-13
最後登錄: 2019-10-10

論壇貢獻勳章

2^#

發表於 2011-9-29 09:36 | 只看該作者

外行人看不出你要的是什麼?提問要讓人一看就懂,所以要在???的地方填上手工的答案.並解釋如何來的.