konantw

³o­Ó¤]¬O§Ú¤@ª½·Q¤F¸Ñªº, ¬Ý¬Ý¤j®a¬O¦p¦ó¸Ñ¨Mªº

c_c_lai

¦^´_ 17# alexliou
    ActiveSheet.Shapes(chartname).Left = Cells(xRow + cHeight, yCol).Left  
    ActiveSheet.Shapes(chartname).Top = Cells(xRow + cHeight, yCol).Top
³o¼Ëªº»Î±µ¤è¦¡ÆZ¤£¿ùªº¡A¤£¶È±K¦X¤S¬üÆ[¡A¦A¥[¤W
        .Axes(xlValue, xlPrimary).MinimumScale = Int(sMin / 10) * 10
        .Axes(xlValue, xlPrimary).MaximumScale = Int(sMax / 10) * 10
        .Axes(xlValue, xlSecondary).MinimumScale = .Axes(xlValue, xlPrimary).MinimumScale
        .Axes(xlValue, xlSecondary).MaximumScale = .Axes(xlValue, xlPrimary).MaximumScale
ªº¥ª¥k¹ïºÙ¡A¹ê¦b¤Ó·PÁ±z¦³³o¼Ëªº Idea¡I

alexliou

ªÑ²¼¹Ïªí.zip (29.56 KB)

¤U¸ü¦¸¼Æ: 105

2012-4-26 16:23

§Ú¹Á¸Õ§â¨â­Ó¹Ïªí³s¦b¤@°_

c_c_lai

¦^´_ 13# GBKEE
¦^´_ 14# alexliou

ÁÂÁ¨â¦ìªº«ü¾É»PÀ°¦£¡A¨ì¥Ø«e¬°¤î¡A¹ï©ó¹Ï§Îªºªí¹F¤]¦³§ó²`¼h¦aÅé·|¡A
¨Æ«á§Ú·|¦A¤ÏÂнm²ß¤Î®»ºNÅé·|¡A¦A¦¸¦V¨â¦ì»¡Án "·P¿E¨â¦ì¥ý¶i"¡I

alexliou

¦^´_ 11# c_c_lai
1.  ¥ý´£¿ô¤@¤U
.SeriesCollection.Add Source:=Range("²Î­p¹Ïªí!$H$1:²Î­p¹Ïªí!$J$" & totalRows)
.SeriesCollection.Add Source:=Range("²Î­p¹Ïªí!$G$1:²Î­p¹Ïªí!$G$" & totalRows)
³o¨â¦æªº$H$1©M$G$1 ­n§ï¬° $H$2©M$G$2, ­n¤£µM·|³y¦¨©MªÑ»ù¸ê®Æªº¤£Match
C2·|¹ïÀ³¨ìH1©MG1
³o¬O¦]¬°¥Î  .SeriesCollection.Add ¤èªk®É, ¥¦¤£·|À°§A¦Ò¼{¨ì¼ÐÃD¦C

2.  ¦b³o­Ó°ÝÃD¸Ì, Á`¦@¦³¤T­Ó ChartGroups
²Ä¤@­Ó¬OªÑ»ù¶}°ª¦¬§Cªº º¦¶^¹Ï (¨ä¹ê¤]¬O§é½u¹Ïªº©µ¦ù)
²Ä¤G­Ó¬O5MA, 20MA, 60MAªº§é½u¹Ï
²Ä¤T­Ó¬O¦¨¥æ¶qªºª½±ø¹Ï
(¦b¦¨¥æ¶qª½±ø¹Ï¥[¤J«á, ª½±ø¹ÏÅÜchartgroups(1), º¦¶^¹ÏÅÜchartgroups(2), §é½u¹ÏÅÜchartgroups(3),
§Ú²q´ú Excelµ¹¤£¦Pªº¹Ï§ÎType«ü©w¤£¦PªºÀu¥ý¶¶§Ç)
       
¦pªGº¦¶^¹Ï©M§é½u¹Ï¦pªG¯à¦@¥Î¤@­Ó¼Æ­È®y¼Ð¶b, ¦Ó¦¨¥æ¶q¥Î°Æ®y¼Ð¶b, ¨º³o­Ó°ÝÃD´Nºâ§¹¬ü¸Ñ¨M¤F       
¦ý§Ú¸Õ¤F´X¦¸, º¦¶^¹Ï²Õ¤@©w­n¿W¥Î¤@­Ó¼Æ­È®y¼Ð¶b (§â§Oªº¹Ï²Õ«ü©w¨Ï¥Îº¦¶^¹Ï²Õªº®y¼Ð¶b³£·|µo¥Í¿ù»~°T®§)       
©Ò¥H§¡½u¹Ï²Õ¥u¯à©M¦¨¥æ¶q¹Ï²Õ¦@¥Î¤@­Ó®y¼Ð¶b
¦ý³o¨â²Õ¸ê®Æªº¼Æ­È¤j¤p®t²§«Ü¤j
¦Ó¥B§¡½u¹Ï²Õªº®y¼Ð¶b¨è«×ÁÙ­n©Mº¦¶^¹Ï²Õªº®y¼Ð¶b¨è«×¤@¼Ë (¬Ý°_¨Ó¤~¦³·N¸q)
       
¦b³o¨Ç­­¨î±ø¥ó¤U, ³æ¥ÎEXCELªºchart¥\¯à, ¥Ø«e§Ú§ä¤£¥X§¹¬üªº¸Ñ¨M¤èªk
       
3. ¥t¤@­Ó¤èªk¬O§â¥¦©î¦¨¨â­Ó¹Ï, º¦¶^©M§¡½u¦b¤@­Ó¹Ï¤W, ¦¨¥æ¶q¦b¤@­Ó¹Ï¤W, µM«á§â¨â­Ó¹Ï¤W¤U³s¦b¤@°_

GBKEE

¦^´_ 12# c_c_lai
¦b¹Ïªí¤¤ ¼Æ¦C¼Æ¤j©ó2¤~¥i¥H«ü©w°Æ®y¼Ð¶b( Y®y¼Ð)
AxisGroup ÄÝ©ÊXlAxisGroup ¥i¥H¬O³o¨Ç XlAxisGroup ±`¼Æ¤§¤@¡C
xlPrimary             1  ( X®y¼Ð )
xlSecondary        2  ( Y®y¼Ð)

1. For Each E In ActiveSheet.Shapes
   
2.                 If InStr(E.Name, "Option Button") Then
   
3.                     If E.OLEFormat.Object.Value = 1 Then °Æ®y¼Ð = E.TopLeftCell(1, 0)
   
4.                 End If
   
5.             Next
   
6.          ' ¥H¤Wµ{¦¡½Xªº¥Øªº¦b´M§ä «ü©w¬° °Æ®y¼Ð ¼Æ¦C ªº¦WºÙ
   
7.             For xi = 1 To .SeriesCollection.Count
   
8.                 With .SeriesCollection(xi)
   
9.                    ' If .Name = "¦¨¥æ¶q" And .Parent.SeriesCollection.Count > 1 Then .AxisGroup = 2
   
10.                     If .Name = °Æ®y¼Ð And .Parent.SeriesCollection.Count > 1 Then .AxisGroup = 2
    
11.                     .Border.ColorIndex = Int(55 * Rnd) + 1  '¶Ã¼Æ²£¥Í¼Æ¦C©³¦â
    
12.                     .Border.Weight = xlThin
    
13.                     .Border.LineStyle = xlContinuous
    
14.                     .MarkerStyle = xlNone
    
15.                     .MarkerBackgroundColorIndex = xlNone
    
16.                     .MarkerForegroundColorIndex = xlNone
    
17.                     .MarkerStyle = xlNone
    
18.                     .Smooth = False
    
19.                     .MarkerSize = 2
    
20.                     .Shadow = False
    
21.                 End With
    
22.             Next

½Æ»s¥N½X

c_c_lai

¦^´_ 10# GBKEE
³o¨ÇÆ[©À»¡©ú¹ï§Úªº½T«D±`¦³À°§U¡A
¤WÀY§Ú¦³¤@¨Ç¼¶¼g»{ª¾¤Wªº²~ÀV¡A½Ð¦AÀ°§Ú¸Ñ´b§a¡I
ÁÂÁ±z¡I

c_c_lai

¦^´_ 9# alexliou
§Ú·Q­nªí¹Fªº¬O ( '**************************************************************************************** )¡G
(1)  Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$C$1:²Î­p¹Ïªí!$F$" & totalRows)  
       ¬O«ü¦V "®É¶¡¶b¡B¥H¤Î  ¶}½L»ù¡B³Ì°ª»ù¡B³Ì§C»ù¡B¦¬½L»ù" µ¥¡C   
(2) Range("²Î­p¹Ïªí!$H$1:²Î­p¹Ïªí!$J$" & totalRows)
       ¬O¤À§O¬° "5MA¡B20MA¡B¥H¤Î 60MA" µ¥¡C
(3) Range("²Î­p¹Ïªí!$G$1:²Î­p¹Ïªí!$G$" & totalRows)
      ¬O¬°  ¦¨¥æ¶q¡C

¥Ø«e§Ú·Q±N  (1) ©M (2) ³]¦¨  ¥D®y¼Ð¶b¡A (3) ·Q±N¥¦³]©w¬° °Æ®y¼Ð¶b¡C
¦b¦¹½d¨Ò¤¤¡AÀ³¦p¦ó¥¿½T¥h³]©w  .SeriesCollection(#).AxisGroup = 1 or 2 ©O¡H
¥H¤ÎÀ³¸Ó¦ì¦b¦ó³B«Å§i©l¥¿½T±o©y¡H
·q½Ð«ü±Ð¡AÁÂÁ±z¡I

1.     With ActiveChart
   
2.         .SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$C$1:²Î­p¹Ïªí!$F$" & totalRows)
   
3.         '.SetSourceData Source:=Range(" ²Î­p¹Ïªí!$C$1:²Î­p¹Ïªí!$F$" & totalRows)
   
4.         ' .ChartType = xlStockVOHLC              ' ªÑ²¼¹Ï   ( ·|³y¦¨ °O¾ÐÅ餣¨¬ )
   
5.         .ChartType = xlStockOHLC                 ' ªÑ²¼¹Ï
   
6.         .SeriesCollection(1).AxisGroup = 1  ' ****************************************************************************************
   
7.                           
   
8.         .SeriesCollection.Add Source:=Range("²Î­p¹Ïªí!$H$1:²Î­p¹Ïªí!$J$" & totalRows)
   
9.         .SeriesCollection(5).Name = "=²Î­p¹Ïªí!$H$1"
   
10.         .SeriesCollection(5).ChartType = xlLine
    
11.         .SeriesCollection(6).Name = "=²Î­p¹Ïªí!$I$1"
    
12.         .SeriesCollection(6).ChartType = xlLine
    
13.         .SeriesCollection(7).Name = "=²Î­p¹Ïªí!$J$1"
    
14.         .SeriesCollection(7).ChartType = xlLine
    
15.         ' .SeriesCollection(2).AxisGroup = 1 '****************************************************************************************
    
16.         
    
17.         With .ChartGroups(1)
    
18.              .UpBars.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
    
19.              .DownBars.Format.Fill.ForeColor.RGB = RGB(0, 32, 96)
    
20.         End With
    
21.         
    
22.         
    
23.         .SeriesCollection.Add Source:=Range("²Î­p¹Ïªí!$G$1:²Î­p¹Ïªí!$G$" & totalRows)
    
24.         .SeriesCollection(8).Name = "=²Î­p¹Ïªí!$G$1"
    
25.         .SeriesCollection(8).ChartType = xlColumnClustered
    
26.         .SeriesCollection(8).AxisGroup = 2 '****************************************************************************************
    
27.         
    
28.         ' With .ChartGroups(2)      'ª`·N, ChartGroup¦b´¡¤J¦¨¥æ¶qª½±ø«áÅܦ¨ 2 ¤F
    
29.         '     .UpBars.Interior.Color = RGB(255, 0, 0)
    
30.         '     .DownBars.Interior.Color = RGB(0, 32, 96)
    
31.         ' End With
    
32.    
    
33.                              
    
34.         .Axes(xlCategory).CategoryType = xlCategoryScale
    
35.         .Axes(xlCategory).TickLabels.NumberFormatLocal = "hh:mm"
    
36.         .Axes(xlCategory).MajorTickMark = xlNone
    
37.         .Axes(xlCategory).TickLabelPosition = xlLow
    
38.             
    
39.         .Axes(xlValue).TickLabels.NumberFormatLocal = "0_ "                     ' ¥Nªíª«¥óªº®æ¦¡¥N½X¡C
    
40.    
    
41.     End With

½Æ»s¥N½X

°õ¦æ«á¡A¥Ø«e¹Ï§ÎÅܦ¨¦p¤U¤§ª¬ºA¡G

¤U¸ü (34.38 KB)

2012-4-24 21:12

GBKEE

¦^´_ 8# c_c_lai
(1)   E «üªº¬O D1 ©¹¥k¨ì³Ì«á¦³¸ê®Æªº½d³ò ,  [d1].End(xlToRight)  ¦p¤u§@ªí¤W«ö¤U Ctrl+-> ¥k¤è¦VÁä

(2)  Rng(1).Columns(Application.Match(Ar(xi), Rng(1).Rows(1), 0)) «üªº¬O¬Æ»ò¡H   Rng(1).Columns(§ä¨ìªºÄæ)
Application.Match(Ar(xi), Rng(1).Rows(1), 0)->  ¤u§@ªíMatch¨ç¼Æ ¦b Rng(1).Rows(1)-> Rng(1)ªº²Ä¤@¦C  ´M§ä Ar(xi)

(3)   .ChartType = xlColumnClustered    ¦³¨ÇÄÝ©Ê ¨Ã¤£¬O©Ò¦³ªº¹Ï§Î  ³£¥i¥H¥Îªº

(4) ·í¹Ïªí¤£¦s¦b®É¡Aµ{¦¡ (¨î©w¸ê®Æ°Ï) °õ¦æ¨ì .ChartObjects.Delete ·|²£¥Í "°õ¦æ¶¥¬q¿ù»~ '1004'"
2003ª© ¤£·|¦³ ¬O§Aªºª©¥»¸ûÄYÂÔ

(5) ±²¶b ¥iÁYµu¸ê®Æ¼Æ, ª½±µ¥´¤W¼Æ­È¤]¥i¥HÁYµu¸ê®Æ¼Æ.

alexliou

¦^´_ 5# c_c_lai
­n¼W¥[5MA, 20MA, 50MA´N©M§â¦¨¥æ¶q¥[¤W¥h¤@¼Ë
¥i§Q¥Î SeriesCollection.Add ¤èªk
°²³]5MA, 20MA, 50MA¤À§O¦bH, I, J Äæ

ActiveChart.SeriesCollection.Add Source:=Range("H2:J" & totalrows)
ActiveChart.SeriesCollection(6).ChartType = xlLine
ActiveChart.SeriesCollection(7).ChartType = xlLine
ActiveChart.SeriesCollection(8).ChartType = xlLine

¸ê®Æ§Ç¦C6,7,8 (§Y5MA, 20MA, 50MA) ·|»P¸ê®Æ§Ç¦C5(¦¨¥æ¶q)¦@¥Î°Æ®y¼Ð¶b
¦ý5MA, 20MA, 50MAªº¨è«×À³»PªÑ»ù¤@­P
©Ò¥H»Ý±N°Æ®y¼Ð¶bªº¨è«×»P¥D®y¼Ð¶bªº¨è«×³]¬°¬Û¦P  
¯ÊÂI¬O·í¦¨¥æ¶q»PªÑ»ùªº³W¼Ò®t¤Ó¦h®É  
¹Ï·|¬Ý°_¨Ó«Ü©_©Ç (©Ò¥H¦¨¥æ¶q­n¨ú¾A·íªº³æ¦ì, Åý¥¦ªº­È©MªÑ»ù¤£·|®t¤Ó¦h)
¦ý§ÚÁ٧䤣¥X¤èªkÅý5MA, 20MA, 50MA»PªÑ»ùº¦¶^¹Ï¦@¥Î¦P¤@®y¼Ð¶b