[µo°Ý] ¦p¦ó±N²£¥Íªº¹Ïªí¦s©ñ¦b«ü©w¤u§@ªíªº¦ì¸m¤W©O¡H

dechiuan999

¦U¦ì¤j¤j¦n¡G

   ¤p§Ì¦n¤[¨S¦³¤W¨Ó¦V¦U
¦ì¤j¤j½Ð¦w¡CÁٽЦU¦ì¤j¤j¨£½Ì¡C
²{¤p§Ì¦³¤@°ÝÃD·Q½Ð¦U¦ì
¤j¤j¬Û§U¡C°ÝÃD¦p¤U¡G

   ¤p§Ì·Q­×§ï¤U¦C»yªk¡A
·í«ö¤USHEET1¤u§@ªí¤Wªº«ö¶s®É¡A
±N²£¥Íªº¹Ïªí¦s©ñ¦b«ü©w¦ì¸m¡C
¨Ò¦pH1:L12ªº¦ì¸m

½Ð°Ý¦p¦ó­×§ï©O¡H
Private Sub CommandButton1_Click()
   
    Dim mRng As Range
    Dim mRng1 As Range
    Dim mRng2 As Range
    Dim mRng3 As Range
    Dim mTotal%
    Dim oldMonth
    Dim mSht As Worksheet
    Dim mRow As Integer
   
   
    Set mSht = Worksheets(1)
    With mSht
        mRow = .Range("a1").End(xlDown).Row
        Set mRng = .Range("g1:l" & mRow)
        Set mRng1 = .Range("a2:a12")
        Set mRng2 = .Range("f2:f12")
        mTotal = Application.WorksheetFunction.Max(mRng2)
        Set mRng3 = Union(mRng1, mRng2)
    End With
   
    oldMonth = Month(Date) - 1
    Application.ScreenUpdating = False
    Charts.Add
   
    Select Case mTotal
    Case 1 To 100
        mTotal = "100"
    Case 101 To 200
        mTotal = "200"
    Case 201 To 300
        mTotal = "300"
    Case 301 To 400
        mTotal = "400"
    Case 401 To 500
         mTotal = "500"
    Case esle
        mTotal = mTotal
    End Select
   
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .SetSourceData Source:=mRng3, PlotBy:=xlColumns
        .HasTitle = True
        .ChartType = xlColumnClustered
        .HasLegend = False
        .ApplyDataLabels xlDataLabelsShowValue
        '.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
        .ChartTitle.Characters.Text = oldMonth & " ¤ë¥÷²Î­pªí"
        .ChartTitle.Font.Bold = False
        .ChartTitle.Font.Size = 12
        .PlotArea.Top = 16
        .PlotArea.Height = 160
        .Axes(xlValue).MaximumScale = mTotal
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
        .ChartArea.Font.Size = 8
        .ChartTitle.Font.Size = 10
    End With
   
   
    Application.ScreenUpdating = True '
   
End Sub

register313

¦^´_ 1# dechiuan999

1. Private Sub CommandButton1_Click()
   
2. Dim mRng As Range
   
3.     Dim mRng1 As Range
   
4.     Dim mRng2 As Range
   
5.     Dim mRng3 As Range
   
6.     Dim mTotal%
   
7.     Dim oldMonth
   
8.     Dim mSht As Worksheet
   
9.     Dim mRow As Integer
   
10.         
    
11.     Set mSht = Worksheets(1)
    
12.     With mSht
    
13.         mRow = .Range("a1").End(xlDown).Row
    
14.         Set mRng = .Range("g1:l" & mRow)
    
15.         Set mRng1 = .Range("a2:a12")
    
16.         Set mRng2 = .Range("f2:f12")
    
17.         mTotal = Application.WorksheetFunction.Max(mRng2)
    
18.         Set mRng3 = Union(mRng1, mRng2)
    
19.     End With
    
20.    
    
21.     oldMonth = Month(Date) - 1
    
22.     Application.ScreenUpdating = False
    
23.     '·s¼W¹Ïªí¤§X¦ì¸m Y¦ì¸m ¼e °ª
    
24.     Set mychart = Sheet1.ChartObjects.Add(350, 0, 400, 200)
    
25.    
    
26.     Select Case mTotal
    
27.     Case 1 To 100
    
28.         mTotal = "100"
    
29.     Case 101 To 200
    
30.         mTotal = "200"
    
31.     Case 201 To 300
    
32.         mTotal = "300"
    
33.     Case 301 To 400
    
34.         mTotal = "400"
    
35.     Case 401 To 500
    
36.          mTotal = "500"
    
37.     Case esle
    
38.         mTotal = mTotal
    
39.     End Select
    
40.    
    
41.     With mychart.Chart
    
42.         .SetSourceData Source:=mRng3, PlotBy:=xlColumns
    
43.         .HasTitle = True
    
44.         .ChartType = xlColumnClustered
    
45.         .HasLegend = False
    
46.         .ApplyDataLabels xlDataLabelsShowValue
    
47.         '.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
    
48.         .ChartTitle.Characters.Text = oldMonth & " ¤ë¥÷²Î­pªí"
    
49.         .ChartTitle.Font.Bold = False
    
50.         .ChartTitle.Font.Size = 12
    
51.         .PlotArea.Top = 16
    
52.         .PlotArea.Height = 160
    
53.         .Axes(xlValue).MaximumScale = mTotal
    
54.         .Axes(xlCategory, xlPrimary).HasTitle = False
    
55.         .Axes(xlValue, xlPrimary).HasTitle = False
    
56.         .ChartArea.Font.Size = 8
    
57.         .ChartTitle.Font.Size = 10
    
58.      End With
    
59.    
    
60.     Application.ScreenUpdating = True
    
61. 
    
62. End Sub

½Æ»s¥N½X

TEST¤G.rar (13.05 KB)

¤U¸ü¦¸¼Æ: 8

2012-2-28 17:51

GBKEE

¦^´_ 2# register313
Set Rng = Range("H1:L12")    ' ¼Ó¥D«ü©w¹Ïªíªº¦ì¸m
Set mychart = Sheet1.ChartObjects.Add(Rng(1).Left, Rng(1).Top, Rng.Width, Rng.Height)

dechiuan999

ÁÂÁ¤G¦ì¤j¤j¡C
¤p§Ì¤Þ¥Î¤G¦ì¤j¤j´£
¨Ñªº¤èªk´ú¸Õ«Ü¦¨¥\¡C

¥t·Q½Ð敎ª©¥D¤j¤j
¦b»yªk¤¤¦³¥yrng(1)
©Ò¥Nªí¥Î·N¬°¦ó
¤S¦p¦óÀ³¥Î©O¡H

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

register313

¦^´_ 4# dechiuan999


Set Rng = Range("H1:L12")   
«hRng(1)=Range("H1")
   Rng(2)=Range("I1")
   Rng(6)=Range("H2")
...¥ý¾î¦Aª½ ¤§±Æ¦C¤è¦¡
¬°Àx¦s®æ¤§¥t¤@ºØªí¥Üªk

¤U¸ü (95.68 KB)

2012-2-28 23:08

dechiuan999

ÁÂÁ¤G¦ì¤j¤j¬Û§U¡C

·P®¦¤j¤j¡I