返回列表 上一主題 發帖

[發問] replace missing value (求救)

[發問] replace missing value (求救)

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

Dear 各位先進大大:

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

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

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

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

感激
[attach]16110[/attach]

回復 1# jj369963

CP2=SUM(I2,K2,X2,AC2,AF2,AR2,AV2,AZ2)/COUNT(I2,K2,X2,AC2,AF2,AR2,AV2,AZ2)*5/3
以此類推
學海無涯_不恥下問

TOP

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

但是我不是要求cp2的值喔

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

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

列如

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

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

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

再麻煩指教

TOP

回復 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 三鍵輸入公式

TOP

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

TOP

回復 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 三鍵輸入公式

TOP

回復 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
複製代碼
學海無涯_不恥下問

TOP

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

TOP

回復 8# jj369963

VBA的作用必須CP:CU欄位都有輸入公式
空格才會對應該列的公式參照填入
學海無涯_不恥下問

TOP

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

回復 9# Hsieh

謝謝版主的回覆:

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

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

感激

再煩請指教

謝謝

[attach]16195[/attach]

TOP

        靜思自在 : 為人處世要小心細心,但不要「小心眼」。
返回列表 上一主題