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

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

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

2^#

發表於 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)))
向下向右複製

學海無涯_不恥下問

帖子: 147
主題: 46
精華: 0
積分: 205
點名: 0
作業系統: windows xp
軟體版本: office 2003
閱讀權限: 20
性別: 女
註冊時間: 2012-3-19
最後登錄: 2021-5-21

3^#

發表於 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.

example0409更新.rar (818.2 KB)

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

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

4^#

發表於 2012-4-10 09:19 | 只看該作者

回復 3# jj369963

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

學海無涯_不恥下問

帖子: 147
主題: 46
精華: 0
積分: 205
點名: 0
作業系統: windows xp
軟體版本: office 2003
閱讀權限: 20
性別: 女
註冊時間: 2012-3-19
最後登錄: 2021-5-21

5^#

發表於 2012-4-10 09:46 | 只看該作者

回復 4# Hsieh

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

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

在麻煩大大，謝謝

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

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

6^#

發表於 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)

example0409更新.rar (817.71 KB)

學海無涯_不恥下問

帖子: 147
主題: 46
精華: 0
積分: 205
點名: 0
作業系統: windows xp
軟體版本: office 2003
閱讀權限: 20
性別: 女
註冊時間: 2012-3-19
最後登錄: 2021-5-21

7^#

發表於 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，是否有辦法呢？就事先定位出條目在表格的位置，再抓出其文字。
再次拜謝及感激。
麻煩了

example0412更新.rar (813.78 KB)

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

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

8^#

發表於 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)))

學海無涯_不恥下問

帖子: 147
主題: 46
精華: 0
積分: 205
點名: 0
作業系統: windows xp
軟體版本: office 2003
閱讀權限: 20
性別: 女
註冊時間: 2012-3-19
最後登錄: 2021-5-21

9^#

發表於 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，是否有辦法呢？就是先定位出條目在表格的位置，再抓出其文字條目。
再次拜謝及感激。
麻煩了

example0412-1更新.rar (824.31 KB)

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

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

10^#

發表於 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)))
向右向下複製

example0412-1更新.zip (1.9 MB)

學海無涯_不恥下問