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

hsien33

請教一下各位前輩
如果每次貼入資料表的欄位都不同
只有有賣這個商品時
欄位才會出現
所以可能今天貼入的資料表中
我要的欄位（六六大順保費）是D4
明天貼入的資料表中
我要的欄位(六六大順保費)變成是E5
像這樣的資料中
只有名稱相同
我該如何擷取資料呢？

我可能表達的不是很清楚
在附件 Book1.rar (6.25 KB)

BOOK1
下載次數: 8

2011-9-28 21:19

中我有提出兩問題（用？來表示想知道的欄位公式）
可以請各位大大幫我看看嗎？
先謝過各位前輩了

附件如右：

ANGELA

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

hsien33

回復 2# ANGELA

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

Hsieh

回復 3# hsien33

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

下載 (45.16 KB)

2011-9-29 22:02

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

下載 (14.45 KB)

2011-9-29 22:03

hsien33

回復 4# Hsieh

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

Hsieh

回復 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)

下載次數: 10

2011-9-29 22:42

hsien33

回復 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

回復 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,)   "保費小計"欄位資料範圍

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

下載 (73.85 KB)

2011-9-30 18:05

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)

下載次數: 6

2011-9-30 18:08

hsien33

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

hsien33

回復 8# Hsieh

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