[發問] replace missing value (求救)

jj369963

Dear 各位先進大大：

在處理資料遇到需要取代  missing value

問題1：如何找到  missing value 就是blank
問題2：如何取代[attach]16110[/attach]

本來是想用spss來做，但spss只內建5種方法，但都不是我要的計算方式

所以請各位高首先輩
多多指教不才的我

感激
[attach]16110[/attach]

Hsieh

回復 1# jj369963

CP2=SUM(I2,K2,X2,AC2,AF2,AR2,AV2,AZ2)/COUNT(I2,K2,X2,AC2,AF2,AR2,AV2,AZ2)*5/3
以此類推

jj369963

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

但是我不是要求cp2的值喔

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

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

列如

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

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

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

再麻煩指教

ML089

回復 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項目看不出規律

jj369963

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

ML089

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

Hsieh

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

1. Sub ex()
   
2. Dim Rng As Range, A As Range, C As Range
   
3. r = 2
   
4. Do Until Cells(r, 1) = ""
   
5.   Set Rng = Cells(r, 6).Resize(, 88)
   
6.   If Application.CountA(Rng) < Rng.Count Then
   
7.   For Each A In Rng.SpecialCells(xlCellTypeBlanks)
   
8.       For Each C In Range(Cells(r, "CP"), Cells(r, "CU"))
   
9.           If InStr(C.Formula, A.Address(0, 0)) > 0 Then
   
10.              A.Value = Round(C, 0)
    
11.           End If
    
12.       Next
    
13.   Next
    
14.   End If
    
15. r = r + 1
    
16. Loop
    
17. End Sub

複製代碼

jj369963

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

回復 8# jj369963

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

jj369963

回復 9# Hsieh

謝謝版主的回覆：

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

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

感激

再煩請指教

謝謝

[attach]16195[/attach]