[µo°Ý] ·Q¨DA20:A200³Ì«á10µ§ªº¥­§¡¼Æ¤½¦¡¦p¦ó¼g?

t8899

·Q¨DA20:A200³Ì«á10µ§ªº¥­§¡¼Æ¤½¦¡¦p¦ó¼g?
¦pªG¤£¨ì10µ§¡A´N¨D³Ì«á´Xµ§ªº¥­§¡
¦pªG
1.¤¤¶¡¦³ªÅ¥Õ,·Óºâ,ºâ0 ¤½¦¡¬O¦p¦ó?
1.¤¤¶¡¦³ªÅ¥Õ,¤£ºâ,¦A©¹¤W§ì¨ì10µ§,¤½¦¡¤S¬O¦p¦ó?

gong

2¡B­pºâ®Éªº­­¨î       
¼Æ¦ìºë«×        15 ¦ì
³æ®æ¤¤¥iÁä¤Jªº³Ì¤j¼Æ­È        9.99999999999999E+307
³Ì¤j¥¿¼Æ        1.79769313486231E308
³Ì¤p­t¼Æ        -2.2250738585072E-308
³Ì¤p¥¿¼Æ        2.229E-308
³Ì¤j­t¼Æ        -2.2250738585073E-308
¤½¦¡¤º®eªºªø«×        1,024 ­Ó¦r¤¸
叠¥N¦¸¼Æ        32767
¤u§@ªí°}¦C­Ó¼Æ        ¨ü¥i¥Î°O¾ÐÅé­­¨î¡C¥t¥~¡A°}¦C¤£¯à¤Þ¥Î¾ã­Ó¦C¡C¨Ò¦p¡A°}¦C¤£¯à¤Þ¥Î¾ã­Ó C:C ¦C©Î C1:C65536 °Ï°ì¡CµM¦Ó¡A°}¦C¥i¥H¤Þ¥Î°Ï°ì C1:D65535¡A¦]爲¦¹°Ï°ì¤ñ³Ì¤j¤u§@ªíªº¤j¤p¤Ö¤@¦æ¡A¥B¥¼¥]¬A¾ã­Ó C ©Î D ¦C¡C
¿ï©w°Ï°ì­Ó¼Æ        2,048
¨ç¼Æªº°Ñ¼Æ­Ó¼Æ        30
¨ç¼Æªº´O®M¼h¼Æ        7
¥i¥Î¤u§@ªí¨ç¼Æªº­Ó¼Æ        329
­pºâ¤¹³\ªº³Ì¦­¤é´Á        1900 ¦~ 1 ¤ë 1 ¤é¡]¦pªG¨Ï¥Î 1904 ¦~¤é´Á¨t²Î¡A«h爲 1904 ¦~ 1 ¤ë 1 ¤é¡^

t8899

A20:A200³Ì«á10µ§ªº¥­§¡¼Æ¤½¦¡

¦pªG¤£¨ì10µ§¡A´N¨D³Ì«á´Xµ§ªº¥­§¡

=AVERAGE(OFFSET( ...
JBY µoªí©ó 2012-10-28 11:49

½Ð°Ý 9.99E+307 ¬O¤°·N«ä??

gong

=SUM(INDIRECT("r"&MAX(1,MATCH(9.99E+307,A:A,1)-9)&":r"&MATCH(9.99E+307,A:A,1)&"c1",0))/MIN(10,MATCH(9.99E+307,A:A,1))
ªÅ¥Õ¥H"0"­pºâ¤§¥­§¡

gong

=AVERAGE(INDIRECT("r"&MAX(1,MATCH(9.99E+307,A:A,1)-9)&":r"&MATCH(9.99E+307,A:A,1)&"c1",0))
³Ì«á10µ§§tªÅ¥Õ¤§¥­§¡

JBY

¦^´_ 3# t8899

A20:A200³Ì«á10µ§ªº¥­§¡¼Æ¤½¦¡

¦pªG¤£¨ì10µ§¡A´N¨D³Ì«á´Xµ§ªº¥­§¡

¤¤¶¡¦³ªÅ¥Õ,¤£ºâ,¦A©¹¤W§ì¨ì10µ§

阵¦C¤½¦¡ :

{=AVERAGE(IF(ISNUMBER(MATCH(ROW(A20:A200),LARGE(IF(ISNUMBER(A20:A200),ROW(A20:A200)),ROW(INDIRECT("1:10"))),0)),A20:A200))}

JBY

¦^´_ 3# t8899

A20:A200³Ì«á10µ§ªº¥­§¡¼Æ¤½¦¡

¦pªG¤£¨ì10µ§¡A´N¨D³Ì«á´Xµ§ªº¥­§¡

=AVERAGE(OFFSET(INDEX(A20:A200,MATCH(9.99E+307,A20:A200)),0,0,-MIN(10,COUNT(A20:A200))))

t8899

¦^´_  t8899
GBKEE µoªí©ó 2012-10-25 08:50

ÁÂÁÂ!
§Ú¬O·Q¥Î¤½¦¡ªº¤è¦¡ª½±µ¦bÀx¦s®æºâ¥X
¤£ª¾¦³µL¿ìªk?

GBKEE

¦^´_ 1# t8899

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim i As Integer, xSum As Long, xi As Integer, x As Integer
   
4.     x = 10
   
5.     For i = 200 To 20 Step -1
   
6.         If xi > x Then Exit For
   
7.         If Cells(i, "a") <> "" Then
   
8.             xSum = xSum + Cells(i, "a")
   
9.             xi = xi + 1
   
10.         End If
    
11.     Next
    
12.     MsgBox xSum / x
    
13. End Sub

½Æ»s¥N½X