ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] replace missing value (¨D±Ï)

[µo°Ý] replace missing value (¨D±Ï)

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-11-15 08:09 ½s¿è

Dear ¦U¦ì¥ý¶i¤j¤j¡G

¦b³B²z¸ê®Æ¹J¨ì»Ý­n¨ú¥N  missing value

°ÝÃD1¡G¦p¦ó§ä¨ì  missing value ´N¬Oblank
°ÝÃD2¡G¦p¦ó¨ú¥N[attach]16110[/attach]

¥»¨Ó¬O·Q¥Îspss¨Ó°µ¡A¦ýspss¥u¤º«Ø5ºØ¤èªk¡A¦ý³£¤£¬O§Ú­nªº­pºâ¤è¦¡

©Ò¥H½Ð¦U¦ì°ª­º¥ý½ú
¦h¦h«ü±Ð¤£¤~ªº§Ú

·P¿E
[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
¥H¦¹Ãþ±À
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh
ÁÂÁª©¤j¦^ÂСG

¦ý¬O§Ú¤£¬O­n¨Dcp2ªº­È³á

§Ú¬O·Q½Ð°Ý
1.¦p¦ó¦bsheet¤¤¦hµ§¼Æ­È¡A§ä¥XªÅ¥Õ(¥¼¶ñµª)
2.¥Î¤½¦¡¦Û°Ê¶ñ¤J ­ì¥»ªºªÅ¥Õ

¦ý¬O¨CÃDªº missing value ­n¥h¶ñªº¼Æ­È­pºâ¦³¨Ç¤£¤@¼Ë

¦C¦p

¾Ç¸¹1000093¡A²Ä1ÃD¥¼¶ñµª
´N§â
average(²Ä1ÃD,²Ä10ÃD,²Ä14ÃD,²Ä17ÃD,²Ä26ÃD,²Ä34ÃD,²Ä44ÃD,²Ä46ÃD) ¨Ã¶ñ¤J­ì¥»¥¼¶ñµªªº²Ä1ÃD³¡¤À

1.¦]¬°¸ê®Æ«Ü¦hµ§¡A¥Î¤H¤u¤è¦¡¥h§ä¥¼¶ñµª¦ì¸m¡A²´·ú§Öªá±¼
2.­n§â­ì¥»¨S¦³¶ñµªªºÃD¥Ø¥Î¤½¦¡À°¥L¶ñ¶i¼Æ­È

¤£¦n·N«ä¡A¥i¯à§Ú¼g±o·N«ä¡AÅý¤H¬Ý¤£À´

¦A³Â·Ð«ü±Ð

TOP

¦^´_ 3# jj369963
>>¾Ç¸¹1000093¡A²Ä1ÃD¦³¿òº|­È¡A
>>¥­§¡(²Ä1ÃD,²Ä10ÃD,²Ä14ÃD,²Ä17ÃD,²Ä26ÃD,²Ä34ÃD,²Ä44ÃD,²Ä46ÃD) ¡A¿òº|­È¤£ºâ
>>
>>average(²Ä1ÃD,²Ä10ÃD,²Ä14ÃD,²Ä17ÃD,²Ä26ÃD,²Ä34ÃD,²Ä44ÃD,²Ä46ÃD)=3.333333
>>¥|±Ë¤­¤J=3
>>
>>³o1®æ¶ñ 3

²Ä1ÃD=ªÅ®æ,²Ä10ÃD=2,²Ä14ÃD=2,²Ä17ÃD=2,²Ä26ÃD=2,²Ä34ÃD=2,²Ä44ÃD=ªÅ®æ,²Ä46ÃD2

ºÃ°Ý1¡A¬JµM²Ä¤@ÃDªÅ®æ­n¥Î¨ä¥L®æ¨Ó¨D­È¡A´N¤£¯à©ñ¨ìaverage¤¤
ºÃ°Ý2¡A¥Î¬Ýªº´Nª¾¹Daverage¤£·|¤j©ó2¡Caverage(²Ä1ÃD,²Ä10ÃD,²Ä14ÃD,²Ä17ÃD,²Ä26ÃD,²Ä34ÃD,²Ä44ÃD,²Ä46ÃD)=3.333333¡A¬Ý¥X¨Ó§A«ç¼Ë­pºâ¥X¨Óªº?
ºÃ°Ý3¡Aaverage¶µ¥Ø¬Ý¤£¥X³W«ß
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-11-15 08:10 ½s¿è

¦^´_ 4# ML089

ÁÂÁÂML089ªº¦^ÂСG

ÁÂÁ±z«ü¥Xªº°ÝÃD¡A³£¤Á¤¤­nÂI
1.½T¹ê¦pªG ²Ä¤@ÃD=missing value ´NµLªk©ñ¤J
2.average(²Ä1ÃD,²Ä10ÃD,²Ä14ÃD,²Ä17ÃD,²Ä26ÃD,²Ä34ÃD,²Ä44ÃD,²Ä46ÃD)=3.333333¡A½T¹ê§Úºâ¿ù¤F
3.average ªº³W«h(½T¹ê¨S¦³¤@©w³W«h)

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

°ÝÃD¡G¦b§ÚªºÀɮ׸̦³º|µªªº±¡§Î¡A©Ò¥H·Q¥H¥­§¡­È¨Ó¨ú¥NªÅ­È¡A¦ý¬O³Â·Ðªº¬OªÅ­È¤£¬O¥Î1-88ÃDªº¥­§¡¨ú¥N¡A¦Ó¬O¨Ì¤W­±³W«h ¦p²Ä1ÃD¬OªÅ­È¡A«h¥­§¡ 10,14,17,26,34,44,46¡A¨ú¥N²Ä1ÃDªÅ­È¡A¦p²Ä10ÃD¬°ªÅ­È¡A«h¥­§¡1,14,17,26,34,44,46¡A¨ú¥N²Ä10ÃDªÅ­È¡A¦pªG²Ä56ÃD¬°ªÅ­È¡A«h¥­§¡ 66,73ÃD¡A¨ú¥N²Ä56ÃD

¥»¨Ó¦³·Q¥Îspss¶]¡A¦ý¬Ospss¥u´£¨Ñsmean¡A¼g»yªk¤@ª½¥X¿ù¡AµLªk³B²z
©Ò¥H·Q°Ý¦U¬°¤j¤j¬O§_excel¥i¥H³B²z©O? 1.§ämissing value 2.¨Ì¤½¦¡¨ú¥Nmissing value
©Î¬O¦³«Øijªº³nÅé¥i¥H³B²z³o¼Ëªº±¡§Î

·P¿E

¦p¦³±Ô­z¤£²M¡A½Ð¨£½Ì
[attach]16123[/attach]

TOP

¦^´_ 5# jj369963
³Ì«á­n¨DªºÁÙ¬OCP2~CU2¡Aª½±µ¥Î¤½¦¡¦p¤U¨D¸Ñ¡A¤£¥²¤j¶O©P³¹¦^¶ñªÅ®æ¡A³o¼Ë¤Ï¦Ó·|¨Ï­ì¸ê®Æ³à¥¢­ì»ª

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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# jj369963
°ò¥»¤W¦]¬°CP:CUÄæ¦ìªº¤½¦¡°Ñ·Ó¨ìF:COÄæ¦ì
­n¦bF:COÄ檺ªÅ®æ¤º¶ñ¤JCP:CU©Ò±oªº­È¡A¥u¯à¹ï·Ó«á¶ñ¤J¼Æ­È
¤£¥i¥H§Q¥Î¤½¦¡¶ñ¤JªÅ®æ¤º¡A¦]¬°³o·|³y¦¨´`Àô°Ñ·Ó
§Q¥ÎVBAÀ°§U§A¶ñ¤J¼Æ­È¤~¥i¹F¦¨
  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
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-11-15 08:11 ½s¿è

¦^´_ 7# Hsieh


    ·PÁª©¥D¡G
   ±z¯u¬OÁA¸Ñ§Úªº¤ß·N

   ³o´N¬O§Ú·Q­n¥¦¹B§@ªºµ²ªG¡A¦ý¬O¤£¦n·N«ä¡A§Ú¤£·|VBA¡A©Ò¥H«ü¥O¦³¨Ç¬Ý¤£À´

  ©Ò¥H¤£¦n·N«ä¡A¦]¬°§Ú¦³80¥÷¹³³o¼Ëªºexcel­n¥hrun¡A©Ò¥H·Q½Ð°Ý¬O§_¥i¥H¥ý·j´MªÅ­È

   ¦]¬° ¹³  reply_replace missing value_2 Àɮתº F80 ´N¨S¦³ ³Q¨ú¥N¡A©Ò¥H.....¯u¬Osorry ¬O§_§Ú­n¤@­Ó¤@­Ó¥h©w¦ìblankªºÀx¦s®æ

¥t¥~ missing value ªº¨ú¥N³W«h¦p¤U:
MEAN(²Ä4ÃD,²Ä6ÃD,²Ä19ÃD,²Ä24ÃD,²Ä27ÃD,²Ä39ÃD,²Ä43ÃD,²Ä47ÃD)
MEAN(²Ä5ÃD,²Ä7ÃD,²Ä15ÃD,²Ä20ÃD,²Ä22ÃD,²Ä29ÃD,²Ä35ÃD,²Ä40ÃD)
MEAN(²Ä2ÃD,²Ä9ÃD,²Ä12ÃD,²Ä18ÃD,²Ä23ÃD,²Ä28ÃD,²Ä32ÃD,²Ä36ÃD)
MEAN(²Ä3ÃD,²Ä11ÃD,²Ä25ÃD,²Ä30ÃD,²Ä33ÃD,²Ä37ÃD,²Ä41ÃD,²Ä45ÃD)
MEAN(²Ä8ÃD,²Ä13ÃD,²Ä16ÃD,²Ä21ÃD,²Ä31ÃD,²Ä38ÃD,²Ä42ÃD,²Ä48ÃD)
MEAN(²Ä1ÃD,²Ä10ÃD,²Ä14ÃD,²Ä17ÃD,²Ä26ÃD,²Ä34ÃD,²Ä44ÃD,²Ä46ÃD)
MEAN(²Ä74ÃD,²Ä76ÃD,²Ä78ÃD,²Ä80ÃD,²Ä82ÃD)
MEAN(²Ä75ÃD,²Ä77ÃD,²Ä79ÃD,²Ä81ÃD,²Ä83ÃD)
MEAN(²Ä84ÃD,²Ä85ÃD,²Ä86ÃD,²Ä87ÃD,²Ä88ÃD)
MEAN(²Ä52ÃD,²Ä60ÃD,²Ä64ÃD)
MEAN(²Ä57ÃD,²Ä65ÃD,²Ä68ÃD)
MEAN(²Ä56ÃD,²Ä66ÃD,²Ä73ÃD)

¦b§ÚªºÀɮ׸̦³º|µªªº±¡§Î¡A©Ò¥H·Q¥H¥­§¡­È¨Ó¨ú¥NªÅ­È¡A¦ý¬O³Â·Ðªº¬OªÅ­È¤£¬O¥Î1-88ÃDªº¥­§¡¨ú¥N¡A¦Ó¬O¨Ì¤W­±³W«h ¦p²Ä1ÃD¬OªÅ­È¡A«h¥­§¡ 10,14,17,26,34,44,46ÃD¡A¨ú¥N²Ä1ÃDªÅ­È¡A¦p²Ä10ÃD¬°ªÅ­È¡A«h¥­§¡1,14,17,26,34,44,46ÃD¡A¨ú¥N²Ä10ÃDªÅ­È¡A¦pªG²Ä56ÃD¬°ªÅ­È¡A«h¥­§¡ 66,73ÃD¡A¨ú¥N²Ä56ÃD

©Ò¥H·Q°Ý¬O§_excel¥i¥H³B²z©O?
1.§ämissing value
2.¥Îvba«Ø¥ß±`¼Ò¨ú¥N missing value

¥t¥~sorry³o¼Ë³o­Ó©«¬O§_­n©ñ¦bvba¤ñ¸û¾A·í©O

·P¿E¦^À³


[attach]16162[/attach]

TOP

¦^´_ 8# jj369963

VBAªº§@¥Î¥²¶·CP:CUÄæ¦ì³£¦³¿é¤J¤½¦¡
ªÅ®æ¤~·|¹ïÀ³¸Ó¦Cªº¤½¦¡°Ñ·Ó¶ñ¤J
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-11-15 08:11 ½s¿è

¦^´_ 9# Hsieh

ÁÂÁª©¥Dªº¦^ÂСG

¯u¬O·M¬Nªº§Ú¡A·Q¦A½Ð±Ð¤@­Ó°ÝÃD¡A¤w¸g¦bCP:CUÄæ¦ì³£¦³¿é¤J¤½¦¡¡A¦ý¬OÁÙ¬O¨S¦³¨ú¥N missing value ªºªÅ®æ?
   

¥t¥~§Ú¥Îlookup¹ï·Ó±b±K¡A½Ð°Ý³o³¡¤À¬O§_¦³¿ìªk¥i¥H¼g¤JVBA©O?

·P¿E

¦A·Ð½Ð«ü±Ð

ÁÂÁÂ

[attach]16195[/attach]

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD