麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 資料處理問題，求助各位先進們的賜教 [打印本頁]

作者: jj369963 時間: 2012-4-9 22:08 標題: 資料處理問題，求助各位先進們的賜教

本帖最後由 jj369963 於 2012-4-10 09:23 編輯

Dear大大：

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

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

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

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

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

作者: Hsieh 時間: 2012-4-9 23:45

本帖最後由 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)))
向下向右複製

作者: jj369963 時間: 2012-4-10 00:32

本帖最後由 jj369963 於 2012-4-10 01:09 編輯

回復 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 時間: 2012-4-10 09:19

回復 3# jj369963

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

作者: jj369963 時間: 2012-4-10 09:46

回復 4# Hsieh

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

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

在麻煩大大，謝謝

作者: Hsieh 時間: 2012-4-11 23:47

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

回復 5# jj369963

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

[attach]10409[/attach]

作者: jj369963 時間: 2012-4-12 00:16

本帖最後由 jj369963 於 2012-4-12 00:38 編輯

回復 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 時間: 2012-4-12 00:34

回復 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 時間: 2012-4-12 00:50

回復 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 時間: 2012-4-12 08:57

回復 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)))
向右向下複製
[attach]10412[/attach]

作者: jj369963 時間: 2012-4-13 20:33

回復 10# Hsieh

Dear 大大：

謝謝您回答，不過因為我想多了解您的公式(讓自己多學習)，所以又做測試，將資料修改如附檔，一樣代入大大提供的公式見解。
但發現會出現錯誤值。所以想問如果修改成附檔，那又該如何改公式呢？
目前不懂此
t=SMALL(IF(ISNUMBER(FIND("a. Dependent Variable: ",原始資料!$A$1:$A$16854)),ROW(代公式!$1:$16854),""),COLUMN(代公式!A$1))

而且位為何是原始資料!$A$1:$A$16854)),ROW(代公式!$1:$16854 不懂為什麼到 16854呢？

再次拜謝與賜教

作者: Hsieh 時間: 2012-4-13 22:25

本帖最後由 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))

作者: jj369963 時間: 2012-4-16 22:02

回復 12# Hsieh

Dear 大大：

非常感謝你的詳細講解，會再努力研究相關應用。

再次拜謝與感恩

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)