[µo°Ý] ¦p¦ó±N¤G­Ó¤£¦P¦ì¸mªº¸ê®Æ²£¥Í¤@­Ó¹Ïªí©O¡H

dechiuan999

¦U¦ì¤j¤j¦n¡G
  
   ¤p§Ì­n±N¤@¤u§@ªíªº¤£¦P¦ì¸mªº¸ê®Æ
¦P®É²£¥Í¤@­Ó¹Ïªí¡A¦ýµLªk±N¤G­Ó¤£¦P¦ì¸mªº¸ê®Æ²£
¥Í¤@­Ó¹Ïªí¡C
   ½Ð°Ý¦U¦ì¤j¤j¦¹°ÝÃDÀ³¦p¦ó§JªA©O¡H

»¡©ú¦p¤U¡G
¹Ïªí°Ï¶ô¬°
­n±N¦¹¤G­Ó°Ï¶ôRANGE("B2:T22")¤ÎRANE("B25:F29")¦X¨Ö¨Ã
²£¥Í¤@­Ó¹Ïªí

¹Ïªí¦ì¸m¡G
RANGE("A1").RESIZE(14,20)

¤U¦C¬°¤p§Ì¤§»yªkÀ³¦p¦ó­×¥¿©O
Sub aa()
   
     '³fª«¦s©ñ³B²Î­p¹Ïªí
   
    Dim mRng As Range
    Dim mRng1 As Range
    Dim mRng2 As Range
    Dim mRng3 As Range
    Dim mRng4 As Range
    Dim mTotal%, mRow$
    Dim oldmonth
    Dim mSht1 As Worksheet
    Dim mChart As ChartObject
    Dim mCol%, sumTotal As Long
   
    Application.ScreenUpdating = False
        
    Set mSht1 = Worksheets("·í¤ë²Î­pªí¤Î¹Ïªí")
    With mSht1
        mCol = .Range("a16").End(xlToRight).Column
        Set mRng = .Range("a17").Resize(5, mCol)
        Set mRng1 = .Range("b25").Resize(5, .Range("b25").End(xlToRight).Column - 1)
        Set mRng2 = Union(mRng, mRng1)
        Set mRng3 = .Range("a1").Resize(14, mCol)
    End With
   
    oldmonth = Month(Date)
    If oldmonth = "1" Then
        oldmonth = "12"
    Else
        oldmonth = oldmonth - 1
    End If
    Call changeMonth(oldmonth)
    Application.ScreenUpdating = False
   
    Set mChart = mSht1.ChartObjects.Add(mRng3.Left, mRng3.Top, mRng3.Width, mRng3.Height)
    With mChart
        .Name = "¦UÃö°Ï³fª«¦s©ñ³B©Ò²Î­pªí"
    End With
   
    mTotal = Application.WorksheetFunction.Max(mRng2)
    Select Case mTotal
   
    Case 1 To 50
        mTotal = "50"
     Case 51 To 100
        mTotal = "100"
    Case 101 To 150
        mTotal = "150"
    Case 151 To 200
        mTotal = "200"
    Case 201 To 250
        mTotal = "250"
    Case 251 To 300
        mTotal = "300"
    Case 301 To 350
        mTotal = "350"
    Case 351 To 400
        mTotal = "400"
    Case 401 To 450
        mTotal = "450"
    Case 451 To 500
        mTotal = "500"
    End Select
   
    Set mRng4 = mSht1.Rows(24).Find(what:="Á`­p ¡G", after:=mSht1.Range("a24"), LookIn:=xlValues, SearchDirection:=xlPrevious)
    If Not mRng4 Is Nothing Then
        sumTotal = mRng4.Offset(6).Value
    End If
   
    With mChart.Chart
        .SetSourceData Source:=mRng2, PlotBy:=xlRows
        .HasTitle = True
        .ChartType = xlColumnClustered
        .HasLegend = True
        .ApplyDataLabels xlDataLabelsShowValue
        .Axes(xlCategory).TickLabels.Orientation = xlHorizontal
        .ChartTitle.Characters.Text = "  " & oldmonth & " ¤ë¥÷¦UÃö°Ï³fª«¦s©ñ³B©Ò²Î­pªí (" & sumTotal & " ¥÷)"
        .ChartTitle.Font.Bold = False
        .ChartTitle.Font.Size = 12
        '.PlotArea.Top = 16
        '.PlotArea.Height = 160
        With .Axes(Type:=xlValue)
            .HasTitle = True
            .AxisTitle.Text = "³fª«¦s©ñ³B©Ò"
             .AxisTitle.Orientation = xlVertical
             .MaximumScale = mTotal
        End With
        '.Axes(xlCategory, xlPrimary).HasTitle = False  ''false
        '.Axes(xlValue, xlPrimary).HasTitle = False   ''false
        .ChartArea.Font.Size = 8
        .ChartTitle.Font.Size = 10
     End With
     
    Application.ScreenUpdating = True
   
    Set mRng = Nothing
    Set mRng1 = Nothing
    Set mRng2 = Nothing
    Set mRng3 = Nothing
    Set mChart = Nothing
End Sub


ÁÂÁ¦U¦ì¤j¤j¡I

dechiuan999

ÁÂÁ¤G¦ìª©¥D¤j¤jªº¬Û§U¡C
´ú¸Õ³£«Ü¦¨¥\¡C
¤p§Ì·|¥ý¦¬¤U
¦^¥h¦n¦n¬ã¨s¡C

·P®¦¤G¦ì¤j¤j¡I

c_c_lai

ÆZ¤£¿ùªº¥Ó½×ÃD§÷¡A¹ï§Ú­Ó¤H¨Ó»¡¤]¬O¤@µf¦ÒÅç¡A
ÁÂÁ¨â¦ì¤j¤jªº«ü¾É¡I

GBKEE

¦^´_ 5# dechiuan999
Do Until .SeriesCollection.Count = 4        '°õ¦æ°j°é:  Until ( ¤@ª½¨ì)  ±ø¥ó¦¨¥ß(¼Æ¦CªºÁ`¼Æ=4 ) ®É ¤£°õ¦æ°j°é
.SeriesCollection(5).Delete                          '§R°£²Ä5­Ó¼Æ¦C¸ê®Æ
Loop

1. Option Explicit
   
2. Sub Ex()
   
3.      Dim Rng(2) As Range, xl As Integer
   
4.      With Worksheets("·í¤ë²Î­pªí¤Î¹Ïªí")
   
5.         .ChartObjects.Delete                            '§R°£©Ò¦³¹Ïªí
   
6.         Set Rng(0) = .Range("a1").Resize(.[a1].End(xlDown).Row - 1, .[a1].End(xlDown).End(xlToRight).Column)        '¹Ïªí©ñ¸m½d³ò
   
7.         Set Rng(1) = .[B17:T21]                         '°ò¶©,¥x¥_´ä,¤»°ô,«n®rªøºa,®ç¶é µ¥Ãö¼Æ¾Ú
   
8.         Set Rng(2) = .[B25:F29]                         '¤­°ôÃö¼Æ¾Ú
   
9.         With .ChartObjects.Add(Rng(0)(1).Left, Rng(0)(1).Top, Rng(0).Width, Rng(0).Height).Chart
   
10.            .Parent.Name = "¦UÃö°Ï³fª«¦s©ñ³B©Ò²Î­pªí"
    
11.             For xl = 2 To Rng(1).Rows.Count
    
12.                 With .SeriesCollection.NewSeries        '«Ø¥ß·s¼Æ¦C
    
13.                     .Name = Rng(1).Cells(xl, 0)         '¦C¦WºÙ
    
14.                     .Values = "=" & Rng(1).Rows(xl).Address(, , xlR1C1, 1) & "," & Rng(2).Rows(xl).Address(, , xlR1C1, 1)
    
15.                                                         '.Values:  ·s¼Æ¦Cªº­È-> ³s±µ Rng(1),Rng(2) ªº R1C1¦¡ ¦ì§}
    
16.                     .XValues = "=" & Rng(1).Rows(1).Address(, , xlR1C1, 1) & "," & Rng(2).Rows(1).Address(, , xlR1C1, 1)
    
17.                                                         ' .XValues: ·s¼Æ¦C X¶b¼ÐÅÒ
    
18.                 End With
    
19.             Next
    
20.             .HasTitle = True                            '·s¼W¹Ïªí«á ¨S¦³«ü©w¹Ïªí¸ê®Æ½d³ò ©T¶·¦b  ¸ê®Æ½d³ò«ü©w«á
    
21.            .ChartTitle.Characters.Text = Format(DateAdd("M", -1, Date), "oooo") & " ¥÷¦UÃö°Ï³fª«¦s©ñ³B©Ò²Î­pªí ( " & Application.Sum(Rng(1), Rng(2)) & " ¥÷)"            
    
22.            .ApplyDataLabels AutoText:=True             '¦Û°ÊÅã¥Ü¾A·íªº¼Æ­È
    
23.         End With
    
24.     End With
    
25. End Sub

½Æ»s¥N½X

dechiuan999

ÁÂÁª©¥D¤j¤j¡C
¤w´ú¸Õ¦¨¥\¡C
¤U¦C»yªk¯à½Ðª©¥D¤j¤jµy°µ»¡©ú¨ä¥Î·N¶Ü¡H
Do Until .SeriesCollection.Count = 4
.SeriesCollection(5).Delete
Loop

·P®¦¤j¤j¡I

Hsieh

¦^´_ 3# dechiuan999
¸Õ¸Õ¬Ý

1. Sub aa()
   
2.    
   
3.      '³fª«¦s©ñ³B²Î­p¹Ïªí
   
4.    
   
5.     Dim mRng As Range
   
6.     Dim mRng1 As Range
   
7.     Dim mRng2 As Range
   
8.     Dim mRng3 As Range
   
9.     Dim mRng4 As Range
   
10.     Dim mTotal%, mRow$
    
11.     Dim oldmonth
    
12.     Dim mSht1 As Worksheet
    
13.     Dim mChart As ChartObject
    
14.     Dim mCol%, sumTotal As Long
    
15.    
    
16.     Application.ScreenUpdating = False
    
17.         
    
18.     Set mSht1 = Worksheets("·í¤ë²Î­pªí¤Î¹Ïªí")
    
19.     With mSht1
    
20.         mCol = .Range("a16").End(xlToRight).Column
    
21.         Set mRng = .Range("a17").Resize(5, mCol)
    
22.         Set mRng1 = .Range("b25").Resize(5, .Range("b25").End(xlToRight).Column - 1)
    
23.         Set mRng2 = Union(mRng, mRng1)
    
24.         Set mRng3 = .Range("a1").Resize(14, mCol)
    
25.     End With
    
26.    
    
27.     oldmonth = Month(Date)
    
28.     If oldmonth = "1" Then
    
29.         oldmonth = "12"
    
30.     Else
    
31.         oldmonth = oldmonth - 1
    
32.     End If
    
33.     Call changeMonth(oldmonth)
    
34.     Application.ScreenUpdating = False
    
35.    
    
36.     Set mChart = mSht1.ChartObjects.Add(mRng3.Left, mRng3.Top, mRng3.Width, mRng3.Height)
    
37.     With mChart
    
38.         .Name = "¦UÃö°Ï³fª«¦s©ñ³B©Ò²Î­pªí"
    
39.     End With
    
40.    
    
41.     mTotal = Application.WorksheetFunction.Max(mRng2)
    
42.     Select Case mTotal
    
43.    
    
44.     Case 1 To 50
    
45.         mTotal = "50"
    
46.      Case 51 To 100
    
47.         mTotal = "100"
    
48.     Case 101 To 150
    
49.         mTotal = "150"
    
50.     Case 151 To 200
    
51.         mTotal = "200"
    
52.     Case 201 To 250
    
53.         mTotal = "250"
    
54.     Case 251 To 300
    
55.         mTotal = "300"
    
56.     Case 301 To 350
    
57.         mTotal = "350"
    
58.     Case 351 To 400
    
59.         mTotal = "400"
    
60.     Case 401 To 450
    
61.         mTotal = "450"
    
62.     Case 451 To 500
    
63.         mTotal = "500"
    
64.     End Select
    
65.    
    
66.     Set mRng4 = mSht1.Rows(24).Find(what:="Á`­p ¡G", after:=mSht1.Range("a24"), LookIn:=xlValues, SearchDirection:=xlPrevious)
    
67.     If Not mRng4 Is Nothing Then
    
68.         sumTotal = mRng4.Offset(6).Value
    
69.     End If
    
70.    
    
71.     With mChart.Chart
    
72.         .SetSourceData Source:=mRng2, PlotBy:=xlRows
    
73.         '­Ó§O³]¸m¼Æ¦C½d³ò
    
74.     .SeriesCollection(1).XValues = _
    
75.         "=(·í¤ë²Î­pªí¤Î¹Ïªí!R17C2:R17C20,·í¤ë²Î­pªí¤Î¹Ïªí!R25C2:R25C6)"
    
76.     .SeriesCollection(1).Values = _
    
77.         "=(·í¤ë²Î­pªí¤Î¹Ïªí!R18C2:R18C20,·í¤ë²Î­pªí¤Î¹Ïªí!R26C2:R26C6)"
    
78.     .SeriesCollection(1).Name = "=·í¤ë²Î­pªí¤Î¹Ïªí!R18C1"
    
79.     .SeriesCollection(2).XValues = _
    
80.         "=(·í¤ë²Î­pªí¤Î¹Ïªí!R17C2:R17C20,·í¤ë²Î­pªí¤Î¹Ïªí!R25C2:R25C6)"
    
81.     .SeriesCollection(2).Values = _
    
82.         "=(·í¤ë²Î­pªí¤Î¹Ïªí!R19C2:R19C20,·í¤ë²Î­pªí¤Î¹Ïªí!R27C2:R27C6)"
    
83.     .SeriesCollection(2).Name = "=·í¤ë²Î­pªí¤Î¹Ïªí!R19C1"
    
84.     .SeriesCollection(3).XValues = _
    
85.         "=(·í¤ë²Î­pªí¤Î¹Ïªí!R17C2:R17C20,·í¤ë²Î­pªí¤Î¹Ïªí!R25C2:R25C6)"
    
86.     .SeriesCollection(3).Values = _
    
87.         "=(·í¤ë²Î­pªí¤Î¹Ïªí!R20C2:R20C20,·í¤ë²Î­pªí¤Î¹Ïªí!R28C2:R28C6)"
    
88.     .SeriesCollection(3).Name = "=·í¤ë²Î­pªí¤Î¹Ïªí!R20C1"
    
89.     .SeriesCollection(4).XValues = _
    
90.         "=(·í¤ë²Î­pªí¤Î¹Ïªí!R17C2:R17C20,·í¤ë²Î­pªí¤Î¹Ïªí!R25C2:R25C6)"
    
91.     .SeriesCollection(4).Values = _
    
92.         "=(·í¤ë²Î­pªí¤Î¹Ïªí!R21C2:R21C20,·í¤ë²Î­pªí¤Î¹Ïªí!R29C2:R29C6)"
    
93.     .SeriesCollection(4).Name = "=·í¤ë²Î­pªí¤Î¹Ïªí!R21C1"
    
94.     '§R°£¿ù»~¼Æ¦C
    
95.     Do Until .SeriesCollection.Count = 4
    
96.     .SeriesCollection(5).Delete
    
97.     Loop
    
98.     .Location Where:=xlLocationAsObject, Name:="·í¤ë²Î­pªí¤Î¹Ïªí"
    
99.         .HasTitle = True
    
100.         .ChartType = xlColumnClustered
     
101.         .HasLegend = True
     
102.         .ApplyDataLabels xlDataLabelsShowValue
     
103.         .Axes(xlCategory).TickLabels.Orientation = xlHorizontal
     
104.         .ChartTitle.Characters.Text = "  " & oldmonth & " ¤ë¥÷¦UÃö°Ï³fª«¦s©ñ³B©Ò²Î­pªí (" & sumTotal & " ¥÷)"
     
105.         .ChartTitle.Font.Bold = False
     
106.         .ChartTitle.Font.Size = 12
     
107.         With .Axes(Type:=xlValue)
     
108.             .HasTitle = True
     
109.             .AxisTitle.Text = "³fª«¦s©ñ³B©Ò"
     
110.              .AxisTitle.Orientation = xlVertical
     
111.              .MaximumScale = mTotal
     
112.         End With
     
113.         .ChartArea.Font.Size = 8
     
114.         .ChartTitle.Font.Size = 10
     
115.      End With
     
116.      
     
117.     Application.ScreenUpdating = True
     
118.    
     
119.     Set mRng = Nothing
     
120.     Set mRng1 = Nothing
     
121.     Set mRng2 = Nothing
     
122.     Set mRng3 = Nothing
     
123.     Set mChart = Nothing
     
124. End Sub

½Æ»s¥N½X

dechiuan999

ÁÂÁª©¥D¤j¤j¡C
¬Ý°_¨Ó¤w±µªñ¤p§Ìªº»Ý¨D¡A
¦ý¤p§Ì¤´·Q¹F¦¨¦¹½d¨Òªº
¹Ïªí¼Ë¦¡¡C
(¤p§Ì¤w­«·s¤W¶ÇÀÉ®×)
¤U¦C¬O¤p§Ì­«·s½Õ¾ãªº¹Ïªí
¥u¬O¤Ö¤F½d³òRANGE("B25:F29")
ªº¦ì¸m¡C

»yªk¦p¤U¡G
Private Sub CommandButton1_Click()   
         
    Dim mRng As Range
    Dim mRng1 As Range
    Dim mRng2 As Range
    Dim mRng3 As Range
    Dim mTotal%, mRow$
    Dim oldmonth
    Dim mSht1 As Worksheet
    Dim mChart As ChartObject
    Dim mCol%, sumTotal As Long
   
    Application.ScreenUpdating = False
   
        
    Set mSht1 = Worksheets("·í¤ë²Î­pªí¤Î¹Ïªí")
    With mSht1
        mCol = .Range("a16").End(xlToRight).Column
        Set mRng = .Range("a1").Resize(14, mCol)
        
        Set mRng1 = .Range("a17").Resize(5, mCol)
        Set mRng2 = .Range("b26").Resize(5, 5)
        Set mRng3 = Union(mRng1, mRng2)       'mRng3¬O±N¤G­Ó°Ï¶ô¦X¨Ö¨Ã¾É¤J.SetSourceData Source:=mRng3, PlotBy:=xlRows «h·|¥¢±Ñ¦¬³õ
      
    End With
   
    oldmonth = Month(Date)
   
    If oldmonth = "1" Then
        oldmonth = "12"
    Else
        oldmonth = oldmonth - 1
    End If
   
    Call changeMonth(oldmonth)
    Application.ScreenUpdating = False
    Set mChart = mSht1.ChartObjects.Add(mRng.Left, mRng.Top, mRng.Width, mRng.Height)
    With mChart
        .Name = "¦UÃö°Ï³fª«¦s©ñ³B©Ò²Î­pªí"
    End With
    mTotal = Application.WorksheetFunction.Max(mRng3)
    Select Case mTotal
    Case 1 To 50
        mTotal = "50"
     Case 51 To 100
        mTotal = "100"
    Case 101 To 150
        mTotal = "150"
    Case 151 To 200
        mTotal = "200"
    Case 201 To 250
        mTotal = "250"
    Case 251 To 300
        mTotal = "300"
    Case 301 To 350
        mTotal = "350"
    Case 351 To 400
        mTotal = "400"
    Case 401 To 450
        mTotal = "450"
    Case 451 To 500
        mTotal = "500"
    End Select
    Set mRng3 = mSht1.Rows(24).Find(what:="Á`­p ¡G", after:=mSht1.Range("a24"), LookIn:=xlValues, SearchDirection:=xlPrevious)
    If Not mRng3 Is Nothing Then
        sumTotal = mRng3.Offset(6).Value
    End If
   
    With mChart.Chart
        .SetSourceData Source:=mRng1, PlotBy:=xlRows    '¥Ø«e°õ¦æmrng1 ¦Ó¤Ö¤Fmrng2ªº°Ï¶ô¦ì¸m¡A¦ý¦pªG§ï¬°mRng3®É«oµLªk¹F¦¨¤G­Ó°Ï¶ô
        .HasTitle = True
        .ChartType = xlColumnClustered
        .HasLegend = True
        .ApplyDataLabels xlDataLabelsShowValue
        .Axes(xlCategory).TickLabels.Orientation = xlHorizontal
        .ChartTitle.Characters.Text = "  " & oldmonth & " ¤ë¥÷¦UÃö°Ï³fª«¦s©ñ³B©Ò²Î­pªí (" & sumTotal & " ¥÷)"
        .ChartTitle.Font.Bold = False
        .ChartTitle.Font.Size = 12
        With .Axes(Type:=xlValue)
            .HasTitle = True
            .AxisTitle.Text = "³fª«¦s©ñ³B©Ò"
             .AxisTitle.Orientation = xlVertical
             .MaximumScale = mTotal
        End With
        .ChartArea.Font.Size = 8
        .ChartTitle.Font.Size = 10
     End With     
    Application.ScreenUpdating = True   
    Set mRng = Nothing
    Set mRng1 = Nothing
    Set mRng2 = Nothing
    Set mRng3 = Nothing
    Set mChart = Nothing
End Sub

Hsieh

¦^´_ 1# dechiuan999

¬O¤£¬O³o¼Ë?
TESTCHART.rar (11.75 KB)

¤U¸ü¦¸¼Æ: 17

2012-4-4 19:23