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

[µo°Ý] ªí³æ¦C¦L¶W¹L¤@­¶½d³ò,¥i§_¦b¦C¦L®É³æ­¶¥[Á`¼Æ­È?

[µo°Ý] ªí³æ¦C¦L¶W¹L¤@­¶½d³ò,¥i§_¦b¦C¦L®É³æ­¶¥[Á`¼Æ­È?

½Ð°Ý¤j¤j¡A
excel¸ê®Æ¦b¹wÄý¦C¦L®É¶W¹L¤@­¶ªº½d³ò¡A¥i¥H¦b¦L¥Xªº¨C­¶­¶§À°µ¼Æ­Èªº¥[Á`¶Ü?

¨Ò¦p¡G
AÄæ     BÄæ
¤j©ú   123
.......
¦Ñ³¯   325
¤pªá   521
¤p¤ý   456

¸ê®Æ¹wÄý¦C¦L®É¡A²Ä¤@­¶ªº³Ì«á¤@µ§¸ê®Æ¥u¯à¦C¦L¨ì¦Ñ³¯¡A·Q­n¥[Á`BÄæ¼Æ­È¡A¨Ã¦b²Ä¤@­¶­¶§À¨q¥X¡A
²Ä¤G­¶¤]¬O¥[Á`BÄæ¡A¨Ã¦b¤U¤è¦A¥[Á`²Ä¤@­¶»P²Ä¤G­¶ªºBÄæ¡A½Ð°Ýexcel¥i¥H³o¼Ë³]©w¶Ü?

¦^´_ 1# zhiling


    play.gif
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ register313 ©ó 2012-6-3 23:17 ½s¿è

¦^´_ 1# zhiling
¤À­¶¤p­p²Ö­p.rar (14.23 KB)
  1. Private Sub CommandButton1_Click()
  2. N = 10
  3. CommandButton2_Click
  4. ResetAllPageBreaks
  5. r = [A65536].End(xlUp).Row
  6. If r Mod N <> 0 Then m = r \ N + 1
  7. For i = m To 1 Step -1
  8.   k = i * N + 2
  9.   Rows(k & ":" & k + 1).Insert Shift:=xlDown
  10.   Cells(k, 1) = "¤p­p"
  11.   Cells(k + 1, 1) = "²Ö­p"
  12.   Cells(k, 2) = Application.Sum(Range(Cells(k - N, 2), Cells(k - 1, 2)))
  13.   Cells(k + 1, 2) = Application.Sum(Range(Cells(3, 2), Cells(k - 1, 2)))
  14.   HPageBreaks.Add Before:=Range("A" & k + 2)
  15. Next i
  16. r = [A65536].End(xlUp).Row
  17. PageSetup.PrintTitleRows = Rows("1:2").Address
  18. PageSetup.PrintArea = "$A$2:$B$" & r
  19. End Sub

  20. Private Sub CommandButton2_Click()
  21. Dim Rng As Range
  22. r = [A65536].End(xlUp).Row
  23. For i = r To 1 Step -1
  24.   If Cells(i, 1) = "¤p­p" Or Cells(i, 1) = "²Ö­p" Then
  25.     If Rng Is Nothing Then
  26.       Set Rng = Rows(i)
  27.     Else
  28.       Set Rng = Union(Rng, Rows(i))
  29.     End If
  30.   End If
  31. Next i
  32. If Not Rng Is Nothing Then Rng.Delete
  33. End Sub
½Æ»s¥N½X
  1. Private Sub CommandButton1_Click()               '¥[¤J¤À­¶¤p­p²Ö­p
  2. N = 10                                           '³]©w10µ§¸ê®Æ¤@­¶
  3. CommandButton2_Click
  4. ResetAllPageBreaks                               '­«³]©Ò¦³¤À­¶½u
  5. r = [A65536].End(xlUp).Row
  6. If r Mod N <> 0 Then m = r \ N + 1               '¦@¦³´X­Ó20ªº­¿¼Æ
  7. For i = m To 1 Step -1
  8.   k = i * N + 2
  9.   Rows(k & ":" & k + 1).Insert Shift:=xlDown     '¥[¤J2¦CªÅ¥Õ¦C
  10.   Cells(k, 1) = "¤p­p"
  11.   Cells(k + 1, 1) = "²Ö­p"
  12.   Cells(k, 2) = Application.Sum(Range(Cells(k - N, 2), Cells(k - 1, 2)))  '¤p­p
  13.   Cells(k + 1, 2) = Application.Sum(Range(Cells(3, 2), Cells(k - 1, 2)))  '²Ö­p
  14.   HPageBreaks.Add Before:=Range("A" & k + 2)     '¤ô¥­¤À­¶½u
  15. Next i
  16. r = [A65536].End(xlUp).Row
  17. PageSetup.PrintTitleRows = Rows("1:2").Address   '³]©w¼ÐÃD¦C
  18. PageSetup.PrintArea = "$A$2:$B$" & r             '³]©w¦C¦L½d³ò
  19. End Sub

  20. Private Sub CommandButton2_Click()                 '²¾°£¤À­¶¤p­p²Ö­p
  21. Dim Rng As Range
  22. r = [A65536].End(xlUp).Row
  23. For i = r To 1 Step -1
  24.   If Cells(i, 1) = "¤p­p" Or Cells(i, 1) = "²Ö­p" Then
  25.     If Rng Is Nothing Then
  26.       Set Rng = Rows(i)
  27.     Else
  28.       Set Rng = Union(Rng, Rows(i))
  29.     End If
  30.   End If
  31. Next i
  32. If Not Rng Is Nothing Then Rng.Delete
  33. End Sub
½Æ»s¥N½X

TOP

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2012-6-3 22:15): ½ÐÁ¿ÀÉ®×À£ÁY¤W¶Ç»¡©ú»Ý¨D

·PÁ¤j¤jªº¦^ÂСA¤S¾Ç¤F¤@©Û¡A

¤£¦n·N«ä¡AÀ³¸Ó¬O§Úªí¹Fªº¤£°÷²M·¡¡A
©Ò¥HÁÙ¬O¦³°ÝÃD­n¦A½Ð±Ð¡A

1¡B§Ú­n¥[Á`¤p­pªº¼Æ­È¦@¦³40µ§¡A¹wÄý¦C¦L¤@­¶¥i¥H¬Ý¨ì25µ§¡A¥i¥H¦b³o¤@­¶(«e25µ§¼Æ­È)¤U¤è¤p­p¡A
        ¦b²Ä¤G­¶¤U¤èªº¤p­p¥t¥~«á­±15µ§¼Æ­È¡A¨Ã¦b²Ä¤G­¶¤p­p¤U¤è¦A¥[Á`²Ä¤@­¶»P²Ä¤G­¶ªº¤p­p¶Ü?

2¡B§Ú¦b¤@­ÓexcelÀɮפ¤¥Î¤F¦n´X­Óvlookup(¤j¬ù7.8­Ó)¡Aµ²ªG¦sÀɮɡAÀÉ®×­n¶]¤ñ¸û¤[¡A¦³¿ìªk§ïµ½¶Ü?
       (¨ä¤¤¦³5­Óvlookup¥u¬O¬°¤F§â¥t¤@­Ósheetªº¼Æ­È¾ã­Ó·h¨ì¥t¤@­Ósheet¡A¦ý¬O­n¤ñ¹ï¦WºÙ¡A
        ¦]¬°¤£ª¾¹D¦³¨S¦³¨ä¥L¤ñ¸û§Öªº¤è¦¡¡A¥Ø«e¥u·|¨Ï¥Îvlookup¤@­Ó¤@­Ó¤ñ¹ï)


¤£¦n·N«ä¡A³Ìªñ¤~¶}©l¸ÕµÛ§âexcelªº¨ç¼ÆÀ³¥Î¦b¤u§@¤W¡A¤§«e¥u·|¥[Á`ªº¨ç¼Æ¦Ó¤w¡A©Ò¥H¦pªG°ÝÃD¤ñ¸û²Â¡A
½Ð¦U¦ì¤j¤j¦h¦h¥]²[¡AÁÂÁÂ!!

TOP

¦]¬°¨C¥x¦Lªí¾÷³]©w¤@­¶¦³´X­Ó Rows ¬O¤£¤@©w
©Ò¥H´Nºâ¥ý¦b²{¦b°µ¦n¤F¡A¤U¤@¥x¦Lªí¾÷ªº³]©w¤]¤£¤@©w¥i¥Î¡C

¦pªG­n¶]ªº¤ñ¸û§Ö
1.´«¹q¸£
2.¤½¦¡¤Æ¬°­È

TOP

·PÁ½Ѧì¤j¤jªº¦^ÂлP»¡©ú¡A§Ú¦A¸Õ¬Ý¬Ý¡A
½T¹ê´«¤@¥x¹q¸£Àx¦s³t«×´N¦n¦h¤F¡AÁÂÁÂ!!

TOP

¦^´_ 6# zhiling
¦]¬°¨C¥x¦Lªí¾÷³]©w¤@­¶¦³´X­Ó Rows ¬O¤£¤@©w
³o¸Ì¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Msg As String, xi As Integer
  4.     With ActiveSheet
  5.         If Application.CountA(.Range("a1").CurrentRegion) = 0 Then
  6.             MsgBox "½d³ò¤º¨S¦³¸ê®Æ¥i¦L¦C"
  7.             Exit Sub
  8.         End If
  9.         ActiveWindow.View = xlPageBreakPreview
  10.         .PageSetup.PrintArea = .Range("a1").CurrentRegion.Address
  11.         .PageSetup.PrintTitleRows = "$1:$1"
  12.         .PageSetup.LeftFooter = "&14&N ­¶-  &P"
  13.         '­¶§À¥kÃä:   &14-> ¦rÅéSize =14, &N->Á`­¶¼Æ,  &P->²Ä´X­¶
  14.         For xi = 0 To .HPageBreaks.Count
  15.             Msg = "&14 ¤p­p: "                   '&14:  ¦rÅéSize =14
  16.             If xi = 0 Then
  17.                 Msg = Msg & Application.Sum(.Range([b2], .HPageBreaks(1).Location.Cells(0, 2)))
  18.             ElseIf xi > 0 And xi < .HPageBreaks.Count Then
  19.                 Msg = Msg & Application.Sum(.Range(.HPageBreaks(xi).Location.Cells(1, 2), .HPageBreaks(xi + 1).Location.Cells(0, 2)))
  20.                 Msg = Msg & Chr(10) & "²Ö­p: " & Application.Sum(.Range([b2], .HPageBreaks(xi + 1).Location.Cells(0, 2)))
  21.             Else
  22.                 Msg = Msg & Application.Sum(.Range(.HPageBreaks(.HPageBreaks.Count).Location, .[b2].End(xlDown)))
  23.                 Msg = Msg & Chr(10) & "²Ö­p: " & Application.Sum(.Range("B:B"))
  24.             End If
  25.         .PageSetup.RightFooter = Msg                        '­¶§À¥ªÃä:
  26.          .PrintOut From:=xi + 1, To:=xi + 1, Copies:=1      '¤@­¶¤@­¶¦L¦C
  27.         Next
  28.         .DisplayPageBreaks = False                          '¨ú®ø¤À­¶½u
  29.     End With
  30.     ActiveWindow.View = xlNormalView
  31.     MsgBox "¦L¦C §¹¦¨"
  32. End Sub
½Æ»s¥N½X
À³¥Î¦b 3# ÀÉ®× ¦p¹Ïªº¸ê®Æ¤¤

TOP

¦p¦ó¯àÅã¥Ü¥X ¤d¤À¦ì¼Æ­È®æ¦¡?

TOP

¦^´_ 8# ccp

¤@¯ë¾Þ§@:
®æ¦¡/Àx¦s®æ ¼Æ­È ¤Ä¿ï¤d¤À¦ì

¤u§@ªí¨ç¼Æ:
=TEXT(¼Æ­È,"0,000")

VBA:
Format(¼Æ­È, "0,000")

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD