[發問] replace missing value (求救)

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

3^#

發表於 2013-9-19 23:42 | 只看該作者

回復 2# Hsieh
謝謝版大回覆：

但是我不是要求cp2的值喔

我是想請問
1.如何在sheet中多筆數值，找出空白(未填答)
2.用公式自動填入原本的空白

但是每題的 missing value 要去填的數值計算有些不一樣

列如

學號1000093，第1題未填答
就把
average(第1題,第10題,第14題,第17題,第26題,第34題,第44題,第46題) 並填入原本未填答的第1題部分

1.因為資料很多筆，用人工方式去找未填答位置，眼睛快花掉
2.要把原本沒有填答的題目用公式幫他填進數值

不好意思，可能我寫得意思，讓人看不懂

再麻煩指教

版主 Rank: 7 Rank: 7 Rank: 7

ML089

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 2013-9-21 15:53 | 只看該作者

回復 3# jj369963
>>學號1000093，第1題有遺漏值，
>>平均(第1題,第10題,第14題,第17題,第26題,第34題,第44題,第46題) ，遺漏值不算
>>
>>average(第1題,第10題,第14題,第17題,第26題,第34題,第44題,第46題)=3.333333
>>四捨五入=3
>>
>>這1格填 3

第1題=空格,第10題=2,第14題=2,第17題=2,第26題=2,第34題=2,第44題=空格,第46題2

疑問1，既然第一題空格要用其他格來求值，就不能放到average中
疑問2，用看的就知道average不會大於2。average(第1題,第10題,第14題,第17題,第26題,第34題,第44題,第46題)=3.333333，看出來你怎樣計算出來的?
疑問3，average項目看不出規律

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

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

5^#

發表於 2013-9-22 12:40 | 只看該作者

本帖最後由 Hsieh 於 2013-11-15 08:10 編輯

回復 4# ML089

謝謝ML089的回覆：

謝謝您指出的問題，都切中要點
1.確實如果第一題=missing value 就無法放入
2.average(第1題,第10題,第14題,第17題,第26題,第34題,第44題,第46題)=3.333333，確實我算錯了
3.average 的規則(確實沒有一定規則)

MEAN(第4題,第6題,第19題,第24題,第27題,第39題,第43題,第47題)
MEAN(第5題,第7題,第15題,第20題,第22題,第29題,第35題,第40題)
MEAN(第2題,第9題,第12題,第18題,第23題,第28題,第32題,第36題)
MEAN(第3題,第11題,第25題,第30題,第33題,第37題,第41題,第45題)
MEAN(第8題,第13題,第16題,第21題,第31題,第38題,第42題,第48題)
MEAN(第1題,第10題,第14題,第17題,第26題,第34題,第44題,第46題)
MEAN(第74題,第76題,第78題,第80題,第82題)
MEAN(第75題,第77題,第79題,第81題,第83題)
MEAN(第84題,第85題,第86題,第87題,第88題)
MEAN(第52題,第60題,第64題)
MEAN(第57題,第65題,第68題)
MEAN(第56題,第66題,第73題)

問題：在我的檔案裡有漏答的情形，所以想以平均值來取代空值，但是麻煩的是空值不是用1-88題的平均取代，而是依上面規則如第1題是空值，則平均 10,14,17,26,34,44,46，取代第1題空值，如第10題為空值，則平均1,14,17,26,34,44,46，取代第10題空值，如果第56題為空值，則平均 66,73題，取代第56題

本來有想用spss跑，但是spss只提供smean，寫語法一直出錯，無法處理
所以想問各為大大是否excel可以處理呢? 1.找missing value 2.依公式取代missing value
或是有建議的軟體可以處理這樣的情形

感激

如有敘述不清，請見諒
[attach]16123[/attach]

版主 Rank: 7 Rank: 7 Rank: 7

ML089

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2013-9-22 16:33 | 只看該作者

回復 5# jj369963
最後要求的還是CP2~CU2，直接用公式如下求解，不必大費周章回填空格，這樣反而會使原資料喪失原貌

CP2=(SUM(I2,K2,X2,AC2,AF2,AR2,AV2,AZ2)+ROUND(AVERAGE(I2,K2,X2,AC2,AF2,AR2,AV2,AZ2),0)*(8-COUNT(I2,K2,X2,AC2,AF2,AR2,AV2,AZ2)))/8
CQ2=(SUM(J2,L2,T2,Y2,AA2,AH2,AN2,AS2)+ROUND(AVERAGE(J2,L2,T2,Y2,AA2,AH2,AN2,AS2),0)*(8-COUNT(J2,L2,T2,Y2,AA2,AH2,AN2,AS2)))/8
CR2=(SUM(G2,N2,Q2,W2,AB2,AG2,AK2,AO2)+ROUND(AVERAGE(G2,N2,Q2,W2,AB2,AG2,AK2,AO2),0)*(8-COUNT(G2,N2,Q2,W2,AB2,AG2,AK2,AO2)))/8
CS2=(SUM(H2,P2,AD2,AI2,AL2,AP2,AT2,AX2)+ROUND(AVERAGE(H2,P2,AD2,AI2,AL2,AP2,AT2,AX2),0)*(8-COUNT(H2,P2,AD2,AI2,AL2,AP2,AT2,AX2)))/8
CT2=(SUM(M2,R2,U2,Z2,AJ2,AQ2,AU2,BA2)+ROUND(AVERAGE(M2,R2,U2,Z2,AJ2,AQ2,AU2,BA2),0)*(8-COUNT(M2,R2,U2,Z2,AJ2,AQ2,AU2,BA2)))/8
CU2=(SUM(F2,O2,S2,V2,AE2,AM2,AW2,AY2)+ROUND(AVERAGE(F2,O2,S2,V2,AE2,AM2,AW2,AY2),0)*(8-COUNT(F2,O2,S2,V2,AE2,AM2,AW2,AY2)))/8

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

7^#

發表於 2013-9-22 23:38 | 只看該作者

回復 5# jj369963
基本上因為CP:CU欄位的公式參照到F:CO欄位
要在F:CO欄的空格內填入CP:CU所得的值，只能對照後填入數值
不可以利用公式填入空格內，因為這會造成循環參照
利用VBA幫助你填入數值才可達成

Sub ex()
Dim Rng As Range, A As Range, C As Range
r = 2
Do Until Cells(r, 1) = ""
Set Rng = Cells(r, 6).Resize(, 88)
If Application.CountA(Rng) < Rng.Count Then
For Each A In Rng.SpecialCells(xlCellTypeBlanks)
For Each C In Range(Cells(r, "CP"), Cells(r, "CU"))
If InStr(C.Formula, A.Address(0, 0)) > 0 Then
A.Value = Round(C, 0)
End If
Next
Next
End If
r = r + 1
Loop
End Sub

複製代碼

學海無涯_不恥下問

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

8^#

發表於 2013-9-25 16:26 | 只看該作者

本帖最後由 Hsieh 於 2013-11-15 08:11 編輯

回復 7# Hsieh

感謝版主：
您真是瞭解我的心意

這就是我想要它運作的結果，但是不好意思，我不會VBA，所以指令有些看不懂

所以不好意思，因為我有80份像這樣的excel要去run，所以想請問是否可以先搜尋空值

因為像 reply_replace missing value_2 檔案的 F80 就沒有被取代，所以.....真是sorry 是否我要一個一個去定位blank的儲存格

另外 missing value 的取代規則如下:
MEAN(第4題,第6題,第19題,第24題,第27題,第39題,第43題,第47題)
MEAN(第5題,第7題,第15題,第20題,第22題,第29題,第35題,第40題)
MEAN(第2題,第9題,第12題,第18題,第23題,第28題,第32題,第36題)
MEAN(第3題,第11題,第25題,第30題,第33題,第37題,第41題,第45題)
MEAN(第8題,第13題,第16題,第21題,第31題,第38題,第42題,第48題)
MEAN(第1題,第10題,第14題,第17題,第26題,第34題,第44題,第46題)
MEAN(第74題,第76題,第78題,第80題,第82題)
MEAN(第75題,第77題,第79題,第81題,第83題)
MEAN(第84題,第85題,第86題,第87題,第88題)
MEAN(第52題,第60題,第64題)
MEAN(第57題,第65題,第68題)
MEAN(第56題,第66題,第73題)

在我的檔案裡有漏答的情形，所以想以平均值來取代空值，但是麻煩的是空值不是用1-88題的平均取代，而是依上面規則如第1題是空值，則平均 10,14,17,26,34,44,46題，取代第1題空值，如第10題為空值，則平均1,14,17,26,34,44,46題，取代第10題空值，如果第56題為空值，則平均 66,73題，取代第56題

所以想問是否excel可以處理呢?
1.找missing value
2.用vba建立常模取代 missing value

另外sorry這樣這個帖是否要放在vba比較適當呢

感激回應

[attach]16162[/attach]

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

9^#

發表於 2013-9-25 17:36 | 只看該作者

回復 8# jj369963

VBA的作用必須CP:CU欄位都有輸入公式
空格才會對應該列的公式參照填入

學海無涯_不恥下問

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

10^#

發表於 2013-9-28 17:45 | 只看該作者

本帖最後由 Hsieh 於 2013-11-15 08:11 編輯

回復 9# Hsieh

謝謝版主的回覆：

真是愚昧的我，想再請教一個問題，已經在CP:CU欄位都有輸入公式，但是還是沒有取代 missing value 的空格?

另外我用lookup對照帳密，請問這部分是否有辦法可以寫入VBA呢?

感激

再煩請指教

謝謝

[attach]16195[/attach]