register313

¦^´_ 1# av8d

¬¡°Ê¼s§i:
1.µo°ÝªÌ½Ð¤W¶ÇEXCELÀ£ÁYÀÉ(¤p¾Ç¥Í¤]¥i¥H)
2.°t¦XEXCELÀɽЧâ¥\¯à»¡©ú²M·¡

¤U¸ü (170.57 KB)

2012-4-11 14:15

M2=IF(OR(D2="",ROW()>ROUNDUP((COUNTA($G:$G)-1)/2,0)+1),"",OFFSET($D$2,ROW()-2,COLUMN()-13))
Q2=IF(OR(OFFSET($D$2,ROW()-2+ROUNDUP((COUNTA($G:$G)-1)/2,0),COLUMN()-17)="",ROW()>ROUNDUP((COUNTA($G:$G)-1)/2,0)+1),"",OFFSET($D$2,ROW()-2+ROUNDUP((COUNTA($G:$G)-1)/2,0),COLUMN()-17))

register313

¦^´_ 3# av8d

1. 2# ªº2­Ó¤½¦¡§Y¬Oµª®×,½Ð¨Ì¹ê»ÚÀx¦s®æ¤§¤À§G±¡§Î¥h­×¥¿¤½¦¡

register313

¦^´_ 5# av8d

1. Sub printx()
   
2. LR = Sheets("¤u§@°Ï").[A65536].End(xlUp).Row - 1
   
3. R = Sheets("¦C¦L³]©w").[A2]
   
4. C = Sheets("¦C¦L³]©w").[B2]
   
5. N = Application.RoundUp(LR / R, 0)
   
6. Sheets("¦C¦L").Cells = ""
   
7. CX = 0
   
8. With Sheets("¤u§@°Ï")
   
9.   For NX = 1 To N
   
10.     RX = Application.RoundUp(NX / C, 0) - 1
    
11.     If CX = C Then CX = 0
    
12.     .[A1:C1].Copy Sheets("¦C¦L").[A1].Offset((R + 1) * RX, 3 * CX)
    
13.     .[A2].Resize(R, 3).Offset(R * (NX - 1), 0).Copy
    
14.     Sheets("¦C¦L").[A2].Offset((R + 1) * RX, 3 * CX).PasteSpecial Paste:=xlPasteValues
    
15.     CX = CX + 1
    
16.   Next NX
    
17. End With
    
18. End Sub

½Æ»s¥N½X

register313

¦^´_ 5# av8d

"¦C¦L"¤u§@ªí¤§
A2=IF(ROUNDUP(COLUMN()/3,0)>¦C¦L³]©w!$B$2,"",OFFSET(¤u§@°Ï!$A$2,MOD(ROW()-2,¦C¦L³]©w!$A$2)+(ROUNDUP(COLUMN()/3,0)-1)*¦C¦L³]©w!$A$2+(ROUNDUP((ROW()-1)/¦C¦L³]©w!$A$2,0)-1)*¦C¦L³]©w!$A$2*¦C¦L³]©w!$B$2,MOD(COLUMN()-1,3),,))
¦V¥k½Æ»s,¦V¤U½Æ»s

register313

¦^´_ 9# av8d

§Aªþ¥ó¸Ìªº¤½¦¡¨S¿ù
¦ý©ÒÅã¥Ü¤§µª®×¬°¦ó¤£¹ï

ÃöÁä¦b©ó
      COUNTA($H:$H)  HÄ榳¤½¦¡199­Ó,³y¦¨­p¼Æ­Ó¼Æ¬°199 (¿ù»~)
­Y§ï¬°COUNTA($C:$C)   CÄ榳­È7­Ó,­p¼Æ­Ó¼Æ¬°7 (¥¿½T)

=>2­Ó¤½¦¡¸ÌªºCOUNTA($H:$H)¥þ§ï¬°COUNTA($C:$C),§Y¥i¥¿½TÅã¥Ü