返回列表 上一主題 發帖

[發問] 資料處理問題,求助各位先進們的賜教

本帖最後由 Hsieh 於 2012-4-12 00:31 編輯

回復 1# jj369963

這問題重點在於找出範圍
首先要先把你的代公式工作表第一列的資料做一下刪除不可見字元
1.選取第一列
2.編輯/尋找取代
3.在尋找目標中以alt+數字鍵32輸入為尋找目標
4.按下全部取代
接下來定義4個名稱
x=MATCH("a. Dependent Variable: "&代公式!A$1,原始資料!$A:$A,0)
y=MATCH("b. Dependent Variable: "&代公式!A$1,原始資料!$A:$A,0)
k=x-y+1
rng=OFFSET(原始資料!$A$1,y+4,1,k-6,3)
完成設置這4個名稱後
代公式!C2公式
=VLOOKUP($B2,rng,2,0)
向下向右複製
非標準化!D4公式
=IF(ISERROR(MATCH($C4,代公式!$1:$1,0)),"",INDEX(代公式!$A$1:$AI$91,MATCH(非標準化!D$2,代公式!$B:$B,0),MATCH($C4,代公式!$1:$1,0)))
向下向右複製
學海無涯_不恥下問

TOP

回復 3# jj369963


   在定義4個名稱時作用儲存格必須選取公式化!C2儲存格
學海無涯_不恥下問

TOP

本帖最後由 Hsieh 於 2012-4-11 23:48 編輯

回復 5# jj369963

算條件平均值,2003版本公式要長一點
代公式!C2公式
=SUMPRODUCT((INDEX(rng,,1)=$B2)*(INDEX(rng,,2)))/SUMPRODUCT((INDEX(rng,,1)=$B2)*1)

example0409更新.rar (817.71 KB)
學海無涯_不恥下問

TOP

回復 7# jj369963

代公式!C2
=SUMIF(INDEX(rng,,1),$B2,INDEX(rng,,2))
非標準化!D4(樓上錯誤已更正)
=IF(ISERROR(MATCH($C4,代公式!$1:$1,0)),"",INDEX(代公式!$A$1:$AI$91,MATCH(非標準化!D$2,代公式!$B:$B,0),MATCH($C4,代公式!$1:$1,0)))
學海無涯_不恥下問

TOP

回復 9# jj369963


    選取代公式!C1新增
定義名稱t
=SMALL(IF(ISNUMBER(FIND("a. Dependent Variable: ",原始資料!$A$1:$A$16854)),ROW(代公式!$1:$16854),""),COLUMN(代公式!A$1))
定義名稱s
=SUMPRODUCT(ISNUMBER(FIND("a. Dependent Variable: ",原始資料!$A$1:$A$16854))*1)
定義名稱x
=MATCH("a. Dependent Variable: "&代公式!C$1,原始資料!$A:$A,0)
定義名稱y
=MATCH("b. Dependent Variable: "&代公式!C$1,原始資料!$A:$A,0)
定義名稱k
=x-y+1
定義名稱rng
=OFFSET(原始資料!$A$1,y+4,1,k-6,3)

******以下為公式******

代公式!C1公式
=IF(COLUMN(A$1)>s,"",SUBSTITUTE(INDIRECT("原始資料!A"&t),"a. Dependent Variable: ",""))
向右複製

代公式!C2公式
=SUMIF(INDEX(rng,,1),$B2,INDEX(rng,,2))
向右向下複製

非標準化!D4公式
=IF(ISERROR(MATCH($C4,代公式!$1:$1,0)),"",INDEX(代公式!$A$1:$AI$91,MATCH(非標準化!D$2,代公式!$B:$B,0),MATCH($C4,代公式!$1:$1,0)))
向右向下複製
example0412-1更新.zip (1.9 MB)
學海無涯_不恥下問

TOP

本帖最後由 Hsieh 於 2012-4-13 22:27 編輯

回復 11# jj369963

16854是因為你原來的資料只到A16854
若要解決資料量變動可增加一個判斷資料尾的名稱
定義名稱時作用儲存格的位置會影響到相對參照的欄列位
所以要特別注意所在儲存格位置
h名稱(取得原始資料的A欄資料尾)
=LOOKUP("龘",原始資料!$A$2:$A$65536,ROW(原始資料!$2:$65536))
rng_1名稱(取得原始資料範圍)
=INDIRECT("原始資料!A1:A"&h)
s名稱(計算原始資料!A欄內有幾個代號)
=SUMPRODUCT(ISNUMBER(FIND("a. Dependent Variable: ",rng_1))*1)
*********
選取代公式!B1(因為名稱t要從B欄向右從1開始遞增,所以SMALL的第2引數使用COLUMN(代公式!A$1)圍欄相對、列絕對參照,故作用儲存格必須位於B欄)
t名稱(取得第幾個代碼所在位置)
=SMALL(IF(ISNUMBER(FIND("a. Dependent Variable: ",rng_1)),ROW(rng_1),""),COLUMN(代公式!A$1))
x名稱(代碼資料結束點)
=MATCH("a. Dependent Variable: "&代公式!B$1,原始資料!$A:$A,0)
y名稱(代碼資料起點)
=MATCH("b. Dependent Variable: "&代公式!B$1,原始資料!$A:$A,0)
k名稱(代碼範圍列述)
=x-y+1
rng名稱(代碼實際資料範圍)
=OFFSET(原始資料!$A$1,y+4,1,k-6,3)
******************
代公式!B2公式
=SUMIF(INDEX(rng,,1),$A2,INDEX(rng,,2))
學海無涯_不恥下問

TOP

        靜思自在 : 人的心地是一畦田,土地沒有播下好種子,也長不出好的果實。 -
返回列表 上一主題