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

jj369963

Dear大大：

來尋求協助與學習。我有一些資料不知如何處理，想請教大家。不好意思，我的邏輯與表達能力不好，如有說明不佳，歡迎回覆，謝謝。
使用任何方法皆可(因為我目前不知如何整理)，但希望整理成"非標準化"sheet,相關說明，參考附檔，不知是否資料多，所以開啟excel要等一些時間。

我先從"原始資料sheet"一個一個慢慢找出變相(列1)表格的位置,再用手動方式，帶vlookup對應數值，但是這樣很慢，因為要慢慢用肉眼找表格。
對應完後，整理成像上面一樣，之後再慢慢用肉眼找出變相位置，用轉置方法貼到"非標準化"sheet
因為有200多個這樣的檔案，用肉眼尋找，又減下貼上，易出錯
所以想請教是否可以快速的把資料整理成"非標準化"sheet

目前的問題是如快速定位出原始資料所需要的表格
我只需要Coefficientsa表格裡的Unstandardized Coefficients，我把幾個用黃底標出來，並知道它的條目名稱，像是A11_1_0，並知道其底下的次變相的非表準化係數是多少，整理出"非標準化"的sheet，是我所需(不限定任何方法)。

忘記說明如果在同一條目搜尋到，多個相同變相，其分數須做平均

因為是EXCEl新手，希望求助大大們，也可以順便學習，提升自己，再次拜謝。

Hsieh

回復 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)))
向下向右複製

jj369963

回復 2# Hsieh


    不好意思大大：

可以請您示範嗎？

目前不懂的地方有
2.定義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)
因為我不知道所謂的定義名稱用途，及從哪個功能列去做，要在哪一個儲存格做呢？

所以可以示範，或是解釋嗎？不好意思再多麻煩，不過也希望從多發問及多操作上可以讓自己更進步，再次感激。

附檔為經過超版的見解所做，但是本人可能較笨，所以做出來似乎不一樣，所以想請問是哪步驟出錯呢？再煩請回答，謝謝

3.

Hsieh

回復 3# jj369963


   在定義4個名稱時作用儲存格必須選取公式化!C2儲存格

jj369963

回復 4# Hsieh


    謝謝回答，我再試試。另外因為忘記說明

忘記說明如果在同一表格裡對應的，條目裡搜尋到，多個相同變相，其分數須做平均，這要怎樣結合vlookup及average呢？我也不確定如果搜到vlookup是否可以搜到2個相同值，因為變相─S_17解決複雜問題，有2個值→所以須搜尋對應的的2個在做平均

在麻煩大大，謝謝

Hsieh

回復 5# jj369963

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

example0409更新.rar (817.71 KB)

下載次數: 4

2012-4-11 23:48

jj369963

回復 6# Hsieh

Dear 超版大大:

    不好意思，謝謝您的回答。不過因為討論過後，變成要算(確定這是最後分析策略了，汗顏)

在同一表格裡對應的，條目裡搜尋到，多個相同變相，其分數須做"加總"，變相─S_17解決複雜問題，有2個值→所以須搜尋對應的的2個在做"加總" 所以借助參考超版公式，改成 代公式!C2公式=SUMPRODUCT((INDEX(rng,,1)=$B2)*(INDEX(rng,,2)))

另外有個疑問，如附檔非標準化sheet裡
A11_1_0、A11_2_0、A11_3_2、A12_1_1、A12_5_2、A12_5_3、A12_5_7、A12_5_8、A11_1、A11_2是錯誤值，從M欄 A11_3開始才有值，但是M欄的值應該是屬於A欄 A11_1_0，也就是它跳掉10個欄位，但我不知道為什麼。我猜是在rng上的設定，但我看不懂其意義(汗顏我自己要多加油)

另外請問大大是否可以給予相關建議關於學習"定義名稱"這部份，深覺其功能實在強大，本以為只是作名稱範圍上的命名，但用於公式卻千變萬化。

此外如果可能的話，如果我想從參考資料裡的表格，抓出我的條目A11_1_0、A11_2_0、A11_3_2、A12_1_1、A12_5_2、A12_5_3、A12_5_7、A12_5_8、A11_1、A11_2，放置代公式sheet的列1，而不是如檔案中用手key，是否有辦法呢？就事先定位出條目在表格的位置，再抓出其文字。
再次拜謝及感激。
麻煩了

Hsieh

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

jj369963

回復 8# Hsieh

Dear 大大：

不好意思。我說錯了，真是抱歉。是在代公式sheet有錯誤

如附檔代公式sheet
A11_1_0、A11_2_0、A11_3_2、A12_1_1、A12_5_2、A12_5_3、A12_5_7、A12_5_8、A11_1、A11_2是錯誤值，從M欄 A11_3開始才有值，但是M欄的值應該是屬於A欄 A11_1_0，也就是它跳掉10個欄位。


此外如果可能的話，如果我想從參考資料裡的表格，抓出我的條目A11_1_0、A11_2_0、A11_3_2、A12_1_1、A12_5_2、A12_5_3、A12_5_7、A12_5_8、A11_1、A11_2，放置代公式sheet的列1，而不是如檔案中用手key，是否有辦法呢？就是先定位出條目在表格的位置，再抓出其文字條目。
再次拜謝及感激。
麻煩了

Hsieh

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

下載次數: 2

2012-4-12 08:56