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

[µo°Ý] Ãþ¦ü²¾°Ê«ü¤Þ½uªº®ÄªG

[µo°Ý] Ãþ¦ü²¾°Ê«ü¤Þ½uªº®ÄªG

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

¤p©f¦b¨Ï¥ÎEXCEL¤ÀªR¸ê®Æ®É¥X²{¦³Ãö¹Ïªí¤¤¸ê®Æ¼ÐÅÒªº§xÃø¡A­º¥ý¡A­Y§Ú­Ì¶È¦³¤@­ÓÅܼƭn¤ÀªRªº¸Ü´N¨S¦³¦¹°ÝÃD¡A¦]¬°·í¹q¸£´å¼Ð«ü¦V»Ý­n¬d¸ß³B«K·|¦Û°ÊÅã¥Ü¸ê®Æ¼ÐÅÒ¡A¦p¤U¹Ï¤@

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

©êºp¤W­±¨S¥´§¹´N«ö¨ì°e¥X¤F¡A¶W¹L¤T¤ÀÄÁ¤£¯à½s¿è><¡A¤U­±¬O§¹¾ãªº¤º®e~

¤p©f¦b¨Ï¥ÎEXCEL¤ÀªR¸ê®Æ®É¥X²{¦³Ãö¹Ïªí¤¤¸ê®Æ¼ÐÅÒªº§xÃø¡A­º¥ý¡A­Y§Ú­Ì¶È¦³¤@­ÓÅܼƭn¤ÀªRªº¸Ü´N¨S¦³¦¹°ÝÃD¡A
¦]¬°·í¹q¸£´å¼Ð«ü¦V»Ý­n¬d¸ß³B«K·|¦Û°ÊÅã¥Ü¸ê®Æ¼ÐÅÒ¡A¦p¤U¹Ï¤@



¦ý¬O·íÅܼƸê®Æ¤@¦hªº®É­Ô¡AEXCEL¶È¯àÅã¥Ü¤@­ÓÅܼƪº¸ê®Æ¼ÐÅÒ¡A¨Ï±o¦bÁͶդÀªR¤W¬Û·í§xÃø¡A¦p¤U¹Ï¤G



EXCEL¤]¦³Åã¥Ü©Ò¦³¸ê®Æ¼ÐÅÒªº¥\¯à¡A¦ý¦]¬°¸ê®ÆÁc¦h¨Ï±o¹Ïªí§¹¥þ¨S¦³¥iŪ©Ê¡A¨£¹Ï¤T¡C



¤p©f¹Á¸Õ«Ü¤[¤]¨S¦³¶ÈÅã¥Ü¤@µ§¸ê®Æ¼ÐÅÒªº¥\¯à¡A¤£ª¾¦U¦ì¤j¤j¬O§_¦bµ{¦¡¤¤¦³¤èªk¥i¥HÅýEXCEL¦³Ãþ¦ü¤U¹Ï¥|¬Ý½L³nÅ餤Ãþ¦ü²¾°Ê¬d»ù½uªº®ÄªG¡A



¤U¹Ï¤­¬O¤p©f§Æ±æ³Ì«áªº¥\¯à¥H¤Îªþ¥ó¡A³Â·Ð¦³³Ò¦U¦ì¤j¤j¡A§Ú«Ü»Ý­n³o­Ó¥\¯à~~~



Index.zip (77.95 KB)

TOP

¦^´_ 2# ®ß®ß¤l
°Ñ¦Ò¥H¤U¸ê°T¬O§_´N¬O©p©Òºc«äªº¼Ò¦¡¡H
K ½uªÑ²¼¹Ï¦p¦ó¯à»P¥D¤O¡B´²¤á¡B¤Î¦¨¥æ¶q½u¦@¦s¡H

TOP

§A¦Û¤v¥i¥H¥h¼g¤@­Ó

GIF2.gif

TOP

¦^´_ 2# ®ß®ß¤l
·s¼Wª«¥óÃþ§O¼Ò²Õ¡A©R¦W EventClassModule
  1. Private WithEvents myChartClass As Chart
  2. Private myVLine As Object
  3. Private myTarget As Range

  4. Public Sub InitializeChart(objChart As Object, rngTarget As Range)
  5.     Set myChartClass = objChart
  6.     Set myTarget = rngTarget
  7.    
  8.     On Error Resume Next
  9.     Set myVLine = myChartClass.Shapes("vline")
  10.     On Error GoTo 0
  11.    
  12.     If myVLine Is Nothing Then
  13.         With myChartClass.PlotArea
  14.             Set myVLine = myChartClass.Shapes.AddLine(.InsideLeft, .InsideTop, .InsideLeft, .InsideTop + .InsideHeight)
  15.             myVLine.Name = "vline"
  16.         End With
  17.     End If
  18. End Sub

  19. Private Sub myChartClass_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
  20.     Dim pt_x As Double, interval As Double, indx As Long
  21.     Dim arValues
  22.    
  23.     pt_x = 75 * x / ActiveWindow.Zoom
  24.     interval = myChartClass.PlotArea.InsideWidth / UBound(myChartClass.SeriesCollection(1).XValues)
  25.     indx = Application.RoundUp((pt_x - myChartClass.PlotArea.InsideLeft) / interval, 0)
  26.     indx = Application.Min(Application.Max(1, indx), UBound(myChartClass.SeriesCollection(1).XValues))
  27.         
  28.     With myChartClass.SeriesCollection
  29.         If .Count = 0 Then Exit Sub
  30.         For i = 1 To .Count
  31.             With .Item(i)
  32.                 .ApplyDataLabels Type:=xlDataLabelsShowNone
  33.                 .Points(indx).ApplyDataLabels Type:=xlDataLabelsShowValue
  34.                 .Points(indx).DataLabel.Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
  35.                 arValues = .Values
  36.                 If i <= myTarget.Cells.Count Then myTarget.Cells(i + 1).Value = arValues(indx)
  37.             End With
  38.         Next
  39.         arValues = .Item(1).XValues
  40.         myTarget.Cells(1).Value = arValues(indx)
  41.     End With
  42.    
  43.     With myChartClass
  44.         If .Axes(xlCategory).AxisBetweenCategories Then
  45.             myVLine.Left = .PlotArea.InsideLeft + (indx - 0.5) * interval
  46.         Else
  47.             myVLine.Left = .PlotArea.InsideLeft + (indx - 1) * interval
  48.         End If
  49.     End With
  50. End Sub
½Æ»s¥N½X
¤@¯ë¼Ò²Õ¡G
  1. Dim myChart As EventClassModule

  2. Sub TriggerChartVLine()
  3.     With Sheets(1)
  4.         Set myChart = New EventClassModule
  5.         myChart.InitializeChart .ChartObjects(1).Chart, .Range("U15:U18")
  6.     End With
  7. End Sub

  8. Sub Auto_Open()
  9.     TriggerChartVLine
  10. End Sub
½Æ»s¥N½X
Index.zip (93.23 KB)
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 3# c_c_lai

C¤j±z¦n¡G

¦]¬°¤p©f¤~­è¥[¤J¨S¦³¦h¤[¡A¿n¤À¤£°÷¤U¸üªþ¥ó¡A

½Ð°Ý¦³¨ä¥L¤èªk¥i¥H¤U¸ü¶Ü¡Ö¡Õ¡AÁÙ¬O¥u¯àµ¥¿n¤À¨ì50¤§«á¤~¯à«ôŪ¤F:'( :'(

TOP

¦^´_ 5# stillfish00

S¤j±z¦n¡G
«D±`·PÁ±z¡A°ÝÃD¸Ñ¨M¤F¡Ö¡Õ¡A¦ýÁÙ¤£¤Ó¼ô±xª«¥ó¼Ò²Õªº¨Ï¥Î¤èªk¡A¤p©f·|¦n¦n¬ã¨sªº¡ã¡ã¡ã

Index.zip (93.02 KB)

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2015-12-17 18:53 ½s¿è

¦^´_ 6# ®ß®ß¤l
¥[ªo¡I¦h¦h°Ñ»P°Q½×¦Û·|¦³©Ò¦¬Ã¬ªº¡C
¥t¥~¦b stillfish00 ¤j¤j ¼Ò²Õ (Module1)
¸Ì¦A¥[¤W  .ChartObjects(1).Activate¡A
¦p¦¹·Æ¹«¥u­n¦b¹Ïªí¤W·Æ°Ê¡A«ü¤Þ½u
§Y¨è¦³Ãþ¦ü²¾°Êªº®ÄªG¡A¥ç§Y¤@¶i¤J
¥»ªí³æ¥ß§Y±Ò°Ê«ü¤Þ½u¦³Ãþ¦ü²¾°Êªº®ÄªG
  1. Sub TriggerChartVLine()
  2.     With Sheets(1)
  3.         Set myChart = New EventClassModule
  4.         myChart.InitializeChart .ChartObjects(1).Chart, .Range("U15:U18")
  5.         .ChartObjects(1).Activate    '  ¤@¶i¤J¥»ªí³æ«ü¤Þ½u§Y¨è¦³Ãþ¦ü²¾°Êªº®ÄªG
  6.     End With
  7. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# stillfish00
S¤j±z¦n¡G
¦b¬ã¨sªº¹Lµ{¤¤¤p©f¤S¹J¨ì¤F¥H¤U°ÝÃDQQ¡A½Ð¤j¤j­ÌÀ°¦£¸Ñ´b ><

1.´N¬O»¡§é½u¹Ï¤¤ªºXValues­È¬O³sÄòªº¡A¤]´N¬OXValues±q2012/10/16~2015/9/16¬O¤£¶¡Â_ªº­È¡A¦ý¦]¬°ªÑ¥«·|¥ð¥«ªºÃö«Y¡A¥æ©ö¤é´Á¬O¤£³sÄòªº¡A¾É­PXValues¤j©óÁ`µ§¼Æ720µ§¡C
   ¦]¦¹indx«ü¦V¤é´Á·|²£¥Í°¾®t¡C¥Ø«e¤p©fªº¸Ñ¨M¤è¦¡¬O±N¥ÎAÄæ¤é´Á©ñ¨ì°}¦C(XValueOutZero)¤¤¡A¦A¥Îindx«ü¦V¦¹°}¦C(XValueOutZero)¡C¤U­±¬O¤p©f¥[ªºµ{¦¡½X
  1. Private Sub myChartClass_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
  2.     Dim pt_x As Double, interval As Double, indx As Long
  3.     Dim arValues, XValueOutZero
  4.    
  5.     XValueOutZero = Range("A2:A721").Value
  6.     pt_x = 75 * x / ActiveWindow.Zoom
  7.     interval = myChartClass.PlotArea.InsideWidth / UBound(myChartClass.SeriesCollection(1).XValues) '¶Ç¦^¦¹°}¦C¤§ºû«×¤§³Ì°ª¥i¥Îµù¼Ð(¯Á¤Þ)
  8.     indx = Application.RoundUp((pt_x - myChartClass.PlotArea.InsideLeft) / interval, 0)
  9.     indx = Application.Min(Application.Max(1, indx), UBound(myChartClass.SeriesCollection(1).XValues))
  10.    
  11.     With myChartClass.SeriesCollection
  12.         If .Count = 0 Then Exit Sub
  13.         For i = 1 To .Count
  14.             With .Item(i)
  15.                 .ApplyDataLabels Type:=xlDataLabelsShowNone
  16.                 .Points(indx).ApplyDataLabels Type:=xlDataLabelsShowValue
  17.                 .Points(indx).DataLabel.Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
  18.                 arValues = .Values
  19.                 If i <= myTarget.Cells.Count Then myTarget.Cells(i + 1).Value = arValues(indx)
  20.             End With
  21.         Next
  22.         'arValues = .Item(1).XValues
  23.         myTarget.Cells(1).Value = XValueOutZero(indx, 1)
  24.     End With
  25.     myVLine.Line.ForeColor.RGB = RGB(0, 0, 0)
  26.     myVLine.Line.Weight = 0.25
  27.     With myChartClass
  28.         If .Axes(xlCategory).AxisBetweenCategories Then
  29.             myVLine.Left = .PlotArea.InsideLeft + (indx - 0.5) * interval
  30.         Else
  31.             myVLine.Left = .PlotArea.InsideLeft + (indx - 1) * interval
  32.         End If
  33.     End With
  34. End Sub
½Æ»s¥N½X
¦ý¬O³o¼Ë¤l¤Sµo²{vlineµLªkºë·Çªº«ü¦V¥¿½Tªº¤é´Á¡A¦p¹Ï1¡Avline«ü¦Vªº¤é´ÁÀ³¸Ó¬O2015/5/16¡A¦ý¬O«oÅã¥Ü2015/5/19ªº¤é´Á:'(

2. ¥t¥~¡AmyChartClass_MouseMove¬O¦b¶i¤J·Æ¹«¦b¹Ïªí¤¤²¾°Ê³o­Ó¨Æ¥óµo¥Í®É©Ò°õ¦æªºµ{¦¡¡A¥¦»Ý­n¦³4­Ó¶Ç¤J­È
  1. myChartClass_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
½Æ»s¥N½X
¡A¦ý¦b·Æ¹«¦b¹Ïªí²¾°Ê®É¨Ã¨S¦³°õ¦æ¨ä¥¦µ{¦¡½X¡A¨º»ò myChartClass_MouseMove ¬O«ç»ò±o¨ì³o¨Ç¶Ç¤J­Èªº©O¡A³o¨Ç¶Ç¤J­È¤S¬O¤°»ò ?

3. ³Ì«á·Q½Ð°Ý¤j¤j¤U­±³o¬qµ{¦¡ªº¥Î·N¬O¤°»ò >< ?
  1.     With myChartClass
  2.         If .Axes(xlCategory).AxisBetweenCategories Then
  3.             myVLine.Left = .PlotArea.InsideLeft + (indx - 0.5) * interval
  4.         Else
  5.             myVLine.Left = .PlotArea.InsideLeft + (indx - 1) * interval
  6.         End If
  7.     End With
  8. End Sub
½Æ»s¥N½X
¥H¤W°ÝÃD¤p©f¦Ê«ä¤£±o¨ä¸Ñ¡A¨D§U¤j¤j­Ì¡A°ÝÃD¦h¤S²Â¤]½Ð¦U¦ì¤j¤j®ü²[:Q :Q

TOP

¦^´_ 9# ®ß®ß¤l
1. ¯àªþÀɮ׬ݬݤñ¸û¦n¡A©Î¬O¸Õ¸Õ§ï¦¨³o¼Ë
  1. Private Sub myChartClass_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
  2.     Dim pt_x As Double, interval As Double, indx As Long
  3.     Dim arValues, targetX
  4.     Dim isAxisBetween As Boolean, dataCount As Long, diffX, xOffset
  5.    
  6.     pt_x = 75 * x / ActiveWindow.Zoom
  7.    
  8.     With myChartClass
  9.         If .SeriesCollection.Count = 0 Then Exit Sub
  10.         isAxisBetween = .Axes(xlCategory).AxisBetweenCategories '®y¼Ð¶b¦ì¸m ¨è«×¶¡:True ¨è«×¤W:False
  11.         arValues = .SeriesCollection(1).XValues '¤é´Á¸ê®Æ
  12.         dataCount = UBound(arValues)    '¸ê®Æ¼Æ¥Ø
  13.         With .Axes(xlCategory)
  14.             diffX = .MaximumScale - .MinimumScale
  15.             xOffset = IIf(isAxisBetween, 0.5 * myChartClass.PlotArea.InsideWidth / (diffX + 1), 0)
  16.             If pt_x < myChartClass.PlotArea.InsideLeft + xOffset Then
  17.                 indx = 1
  18.             Else
  19.                 targetX = .MinimumScale + diffX * (pt_x - myChartClass.PlotArea.InsideLeft - xOffset) / (myChartClass.PlotArea.InsideWidth - 2 * xOffset)
  20.                 indx = Application.Match(targetX, arValues, 1)
  21.                 If indx < UBound(arValues) Then
  22.                     If Abs(targetX - arValues(indx + 1)) < Abs(targetX - arValues(indx)) Then indx = indx + 1   '­×¥¿³Ìªñ¸ê®ÆÂI
  23.                 End If
  24.             End If
  25.             
  26.         End With
  27.         myVLine.Left = .PlotArea.InsideLeft + xOffset + (.PlotArea.InsideWidth - 2 * xOffset) * (arValues(indx) - arValues(1)) / CDbl(diffX)
  28.         myTarget.Cells(1).Value = arValues(indx)
  29.         For i = 1 To .SeriesCollection.Count
  30.             With .SeriesCollection(i)
  31.                 .ApplyDataLabels Type:=xlDataLabelsShowNone
  32.                 .Points(indx).ApplyDataLabels Type:=xlDataLabelsShowValue
  33.                 .Points(indx).DataLabel.Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
  34.                 arValues = .Values
  35.                 If i < myTarget.Cells.Count Then myTarget.Cells(i + 1).Value = arValues(indx)
  36.             End With
  37.         Next
  38.     End With
  39. End Sub
½Æ»s¥N½X
2. MouseMove ¬O Chart(¹Ïªí) ¤º«Øªº¨Æ¥ó¡A·í·Æ¹««ü¼Ð¦b¹Ïªí¤Wªº¦ì¸m§ïÅܮɷ|¦Û°ÊIJµo¦¹¨Æ¥ó¡C
    ¨Æ¥óªº°Ñ¼Æ·|¦Û¤v¶Ç¤J¡A§A¥u­nª¾¹D¶Ç¤JªºªF¦è¨ì©³¬O¥Nªí¤°»ò¡C
    ¦U°Ñ¼Æ»¡©ú¥i¥H¦Û¤vF1¬d MouseMove±oª¾¡C
    Button  : ¨Æ¥óµo¥Í®É¡A·Æ¹««öÁ䪬ºA
    Shift : ¨Æ¥óµo¥Í®É SHIFT¡BCTRL ©M ALT Á䪺ª¬ºA
    x : ·Æ¹««ü¼Ð¦b¹Ïªíª«¥ó¤u§@°Ï®y¼Ð¤¤ªº X ®y¼Ð¡C
    y : ·Æ¹««ü¼Ð¦b¹Ïªíª«¥ó¤u§@°Ï®y¼Ð¤¤ªº Y ®y¼Ð¡C
3. ¬O¦]¬°®y¼Ð¶b®æ¦¡¤¤¡A®y¼Ð¶b¦ì¸m¤À¬°¨è«×¤W©M¨è«×»P¨è«×¶¡¬Û¶Z¨âºØ¡A·|¼vÅT¸ê®ÆÂI¦ì¸m¡C
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD