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

[µo°Ý] ½Ð°Ý¤£³sÄòªºÀx¦s®æ°µ¤½¦¡¶ñº¡ªº°Ê§@¡A¥N½X¯à²¤Æ¶Ü

[µo°Ý] ½Ð°Ý¤£³sÄòªºÀx¦s®æ°µ¤½¦¡¶ñº¡ªº°Ê§@¡A¥N½X¯à²¤Æ¶Ü

¥»©«³Ì«á¥Ñ freeffly ©ó 2013-6-19 15:51 ½s¿è

1.¤½¦¡¤£¦P¦p¤U¦pªG¶ñº¡°Ê§@¤@¼Ë¦³¿ìªk²¤Æ¶Ü
2.¤½¦¡¬Û¦P¤£³sÄò¶ñº¡ªº°Ê§@¤@¼Ë¯à²¤Æ¶Ü(v7 v14 v16 v18 ¤½¦¡¬Û¦P)
´¿¸g¤Ñ¯uªº·Q¥Îunion+arrayªº¤è¦¡³B²z
µo²{¤£¦æ¡A¤£ª¾¹D¦³¨S¦³¤H¦³¦n¤èªk²²¤¤U­±ªº¥N½X
  1. Sub ¾P³f¤½¦¡()
  2.     Application.ScreenUpdating = False
  3.     nC = Sheets("¾P³f").Range("A4").End(xlToRight).Offset(, 1).Column
  4.     nR = Sheets("¾P³f").Range("A65536").End(xlUp).Row
  5.     nC1 = Sheets("¾P³f").Range("A4").End(xlToRight).Offset(, 1).Column - 2
  6.     x = [iv1].End(xlToLeft).Column - 21
  7.    

  8.    
  9.     With Sheets("¥þ³¡")
  10.         With .Range("V2")
  11.         .FormulaR1C1 = _
  12.         "=GETPIVOTDATA(""¥»¹ô¾P³fª÷ÃB"",¾P³f!R3C1,""¤ë¥÷"",R1C)"
  13.         .AutoFill .Resize(1, x)
  14.         End With
  15.         With .Range("V9")
  16.         .FormulaR1C1 = _
  17.         "=SUMPRODUCT((¾P³f!R5C" & nC & ":R" & nR & "C" & nC & "=""Àç·~½Ò"")*(¾P³f!R4C3:R4C" & nC1 & "=R1C),¾P³f!R5C3:R" & nR & "C" & nC1 & ")"
  18.         .AutoFill .Resize(1, x)
  19.         End With
  20.         
  21.         With .Range("V4")
  22.         .FormulaR1C1 = "=GETPIVOTDATA(""¦X­p¥¼¦¬±b´Ú"",±bÄÖ!R1C1,""¤ë¥÷"",R1C)"
  23.         .AutoFill .Resize(1, x)
  24.         End With
  25.         With .Range("V7")
  26.         .FormulaR1C1 = "=VLOOKUP(R1C20,³æÀY¸ê®Æ!C1:C45,45,0)"
  27.         .AutoFill .Resize(1, x)
  28.         End With
  29.         
  30.         With .Range("V14")
  31.             .FormulaR1C1 = "=VLOOKUP(R1C20,³æÀY¸ê®Æ!C1:C45,45,0)"
  32.             .AutoFill .Resize(1, x)
  33.         End With
  34.         
  35.         With .Range("V16")
  36.             .FormulaR1C1 = "=R[-14]C-R[-7]C"
  37.             .AutoFill .Resize(1, x)
  38.         End With
  39.         With .Range("V18")
  40.             .FormulaR1C1 = "=R[-14]C-R[-7]C"
  41.             .AutoFill .Resize(1, x)
  42.         End With
  43.         With .Range("V21")
  44.             .FormulaR1C1 = "=VLOOKUP(R1C20,³æÀY¸ê®Æ!C1:C45,45,0)"
  45.             .AutoFill .Resize(1, x)
  46.         End With
  47. end with
  48. End Sub
½Æ»s¥N½X
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

2.¤½¦¡¬Û¦P¤£³sÄò¶ñº¡ªº°Ê§@¤@¼Ë¯à²¤Æ¶Ü(v7 v14 v16 v18 ¤½¦¡¬Û¦P) ...
freeffly µoªí©ó 2013-6-19 15:49


v7 v14 v16 v18 ¤½¦¡¤£¦Pªü?
§Aªº¥N½X¶]§¹µ²ªG¬O¥¿½Tªº¶Ü?

TOP

¦^´_ 1# freeffly
  1. Sub ¾P³f¤½¦¡()
  2.     Application.ScreenUpdating = False
  3.     nC = Sheets("¾P³f").Range("A4").End(xlToRight).Offset(, 1).Column
  4.     nR = Sheets("¾P³f").Range("A65536").End(xlUp).Row
  5.     nC1 = Sheets("¾P³f").Range("A4").End(xlToRight).Offset(, 1).Column - 2
  6.     x = [iv1].End(xlToLeft).Column - 21
  7.    
  8.     With Sheets("¥þ³¡")
  9.         .Range("V2").Resize(1, x).FormulaR1C1 = "=GETPIVOTDATA(""¥»¹ô¾P³fª÷ÃB"",¾P³f!R3C1,""¤ë¥÷"",R1C)"
  10.         .Range("V9").Resize(1, x).FormulaR1C1 = "=SUMPRODUCT((¾P³f!R5C" & nC & ":R" & nR & "C" & nC & "=""Àç·~½Ò"")*(¾P³f!R4C3:R4C" & nC1 & "=R1C),¾P³f!R5C3:R" & nR & "C" & nC1 & ")"
  11.         .Range("V4").Resize(1, x).FormulaR1C1 = "=GETPIVOTDATA(""¦X­p¥¼¦¬±b´Ú"",±bÄÖ!R1C1,""¤ë¥÷"",R1C)"
  12.         
  13.         For Each area In .Range("V7,V14,V21").Areas
  14.             area.Resize(, x).FormulaR1C1 = "=VLOOKUP(R1C20,³æÀY¸ê®Æ!C1:C45,45,0)"
  15.         Next
  16.         For Each area In .Range("V16,V18").Areas
  17.             area.Resize(, x).FormulaR1C1 = "=R[-14]C-R[-7]C"
  18.         Next
  19.     End With
  20. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# stillfish00


    ¥ý¦¬¤U¬Ý¬Ý¡AÁÂÁ¤F
   ©ú¤Ñ´ú¸Õ§¹¦A¦^Âе²ªG
   ¤£¹L¥Îfor each¦n¹³«ÜºC
   ¦]¬°§Ú­è­è¦³¥Îfor each ¤Î for nextªº¤è¦¡¥h¶]§Oªº¤ñ¸û°_¨Ó
   for each¦n¹³°Ò¾é¨Ç
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 3# stillfish00


    ­è¥J²Ó¬Ý¤@¤U
   À³¸Ó¤£·|ªá«Ü¤[®É¶¡
   ´ú¸Õµ²ªG¥i¥H
   ³oºØ¸Ñ¨M¤è¦¡¤£¿ù
   ¾Ç¨ì¤@ºØ
   ¬Û·í·PÁÂ
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD