[µo°Ý] ¬O§_¦³¤ñ¸û§Öªº¤èªk©O?

celina710828

½Ð°Ý¤j¤j­Ì¡A¦pªG¸ê®Æ¦³A1~A300 ¡A¨C15µ§¸ê®Æ B1=AVERAGE(A1:A15)ªº¥­§¡­È
¡A¦ý¤U¤@­Ó¬OB2=AVERAGE(A16:A30)
B3=AVERAGE(A31:A45)¥H¦¹Ãþ±À¡A
¦Ó¤£¬Oª½±µ¤U©ÔB1=AVERAGE(A1:A15)
                            B2=AVERAGE(A2:A16)
                            B3=AVERAGE(A3:A17)
¬O§_¦³¤ñ¸û§Öªº¤èªk¥h°µ©O?

Hsieh

B1=AVERAGE(OFFSET($A$1,(ROW(A1)-1)*15,,15,))¦V¤U½Æ»s

celina710828

·PÁÂHsieh¡A §Ú§â¥¦§ï¨ì¤u§@ªí2    B1=AVERAGE(OFFSET(¤u§@ªí1!$A$1,(ROW(A1)-1)*15,,15,))
¥i¬O§ÚÁÙ¬O·Qª¾¹D»¡©ú¡A¬O§_¥i¥H©O?

ginbow

­è­è·|¿ù·N  

³o¬O·sªº¸Õ¸Õ¬Ý

Sub gd()


Dim i As Integer
Dim s As Integer
Dim frow As Integer
Dim d As Integer


s = Worksheets("sheet1").Cells(65536, 1).End(xlUp).Row

Z = s / 15

w = Int(Z)

For x = 1 To w

If frow = 0 Then frow = 1 Else: frow = d + 1


d = frow + 14

Worksheets("sheet1").Range("B" & x).FormulaArray = "=AVERAGE(A" & frow & " : A" & d & ")"


Next x



End Sub

Hsieh

¦^´_ 3# celina710828


    B1=AVERAGE(OFFSET(¤u§@ªí1!$A$1,(ROW(A1)-1)*15,,15,))
OFFSET(¤u§@ªí1!$A$1,(ROW(A1)-1)*15,,15,)´N¬O­n±o¨ì¥­§¡½d³ò
¨ä¤¤¤u§@ªí1!$A$1·í§@¸ê®Æªº°_©l¦ì¸m
·íB1¤½¦¡¤¤(ROW(A1)-1)*15´N¬O­n±qA1¦ì²¾´X¦C?
(ROW(A1)-1)=0¡A0*15ÁÙ¬O0¡A©Ò¥HB1­nºâªº½d³ò¬O±qA1¤U²¾0¦C
¨ì¤FB2¤½¦¡·|Åܦ¨(ROW(A2)-1)*15
´N·|¬O¦ì²¾15¦Cªº¦ì¸m°µ°_ÂI´N¬OA16
,,15,))·í¤¤ªº15¬O«ü¡A°_ÂI¦ì¸m¦V¤UÂX®i15¦Cªº½d³ò

celina710828

·PÁÂginbow ªºÀ°¦£¡A¦ý¥¨¶°µLªk¨Ï¥Î¤]¡A§Ú¥¨¶°¤w§ï¬°§C¤F¡A¤]¬O¤£¯à¥Î¡AÁÙ¬O«Ü·PÁ±z
Hsieh ¤j¤j±z¦n:¤£¦n·N«ä§Ú¤£ª¾¹D­þ¸Ì¥X¤F°ÝÃD¥­§¡­È¤£¤@¼Ë¤]¡AÀ°À°¤p¤k§a

yanto913

A2=AVERAGE(OFFSET('15¬°¤@²Õ'!$A$2,(ROW(A2)-2)*15,,15,))
A3=AVERAGE(OFFSET('15¬°¤@²Õ'!$A$2,(ROW(A3)-2)*15,,15,))

linsurvey2005

¦^´_ 5# Hsieh


§Ú³ßÅw³o¼Ëªº¤¬°Ê¤è¦¡
¦n¤Í¾Ç²ßªºªÅ¶¡~I like

Hsieh

A2=AVERAGE(OFFSET('15¬°¤@²Õ'!$A$2,(ROW(A1)-1)*15,,15,))
¦V¤U½Æ»s

celina710828

ÁÂÁ¤T¦ì¤j¦Ñ­Ì¡A§Ú³£¦³¸Õ³£¥i¥H§¹¦¨¤]¡A

¦Ü©ó§Úªº¬O2011ª©§ä¤£¨ì
½s¿è>>¨ì>>¯S®í>>¿ïªÅ®æ>>½T©w>>¥kÁä>>§R°£>>Àx¦s®æ¤W²¾>>½T©w>>§¹¤u

CTRL+`(ESCÁä¤U¨º¤@ºJ¬Ý¤½¦¡¤§ÅܤÆ)

¿ï¦³¤½¦¡¤§³sÄòÀx¦s®æ>>°Å¤U>>¤Á¦Ü¨ä¥L¤u§@ªí>>¶K¤W
¦ýÁÙ¬O²Ä¤@¦¸ªº¤èªk¤]¬O¥i¥Hªº¡AÁÂÁÂ