☰

Robert Mundigl 技术 - 摘要

tag-icon • tag-icon microsoft-excel-2010 charts
Robert Mundigl 技术 - 摘要

有没有办法配置 Excel 2010 以在散点图中的选定点上显示横线？默认行为是将鼠标悬停在光标上时显示带有点值的工具提示。单击该点时，最好显示横线（延伸到边框，使刻度更容易读取）。

答案1

如果我正确理解了这个问题，当您单击散点图中的数据点时，您正在寻找类似这样的内容？

在此处输入图片描述

Robert Mundigl 描述这个怎么做在他的博客 Clearly and Simply 上，改编了乔恩 珀耳帖 技术。效果确实很好。

阅读博客了解详细信息。下面我只是总结了关键步骤。

Robert Mundigl 技术 - 摘要

首先设置以下命名范围

在此处输入图片描述

然后打开误差线

现在我们使用标准 Excel 功能（功能区“图表工具”|“图表和布局”选项卡|“误差线”）向图表的数据系列添加水平和垂直误差线。在“设置误差线格式”对话框中，我们选择“自定义”误差量，并使用名为公式的误差线量指定值（myEB_X_Pos 等）

在此处输入图片描述

添加 VBA 代码

如果你以前从未使用过 VBA，请阅读此内容VBA 入门指南。

在 Visual Basic 编辑器（按Alt+F11访问）中，插入一个名为modApp事件。

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         modAppEvent
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Turn application events on and off
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Public my_objSheet As clsAppEvent

Sub AppEventsOn()
    On Error Resume Next
    Set my_objSheet = New clsAppEvent
    Set my_objSheet.xlApp = Application
End Sub

Sub AppEventsOff()
    On Error Resume Next
    Set my_objSheet.xlApp = Nothing
End Sub

添加另一个模块modChart事件

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         modChartEvent
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Setting and resetting chart events
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit
Option Base 1

Public myCharts() As New clsChartEvent

Sub Set_All_Charts()
Dim obj_cht As ChartObject
Dim int_chartnum As Integer

    On Error Resume Next
    If ActiveSheet.ChartObjects.Count > 0 Then
        ReDim myCharts(ActiveSheet.ChartObjects.Count)
        int_chartnum = 1
        For Each obj_cht In ActiveSheet.ChartObjects
            Set myCharts(int_chartnum).myEmbeddedChart = obj_cht.Chart
            int_chartnum = int_chartnum + 1
        Next
    End If

End Sub

Sub Reset_All_Charts()
Dim int_chartnum As Integer

    On Error Resume Next
    int_chartnum = UBound(myCharts)
    For int_chartnum = 1 To UBound(myCharts)
        Set myCharts(int_chartnum).myEmbeddedChart = Nothing
    Next

End Sub

Sub ActivateSheet(ByVal Sh As Object)
    Set_All_Charts
End Sub

第三个叫修改DropLines

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         modDropDownLines
'   Author:         Robert Mundigl
'   Copyright:      © 2012 by Robert Mundigl, www.clearlyandsimply.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Change the value of the defined named range based on the data point the user clicked on
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Sub DropLines(lngDataPoint As Long)
' Update the named range after user clicked on a data point
Dim rngCurrentCell As Range

    ' Store the active cell
    Set rngCurrentCell = ActiveCell
    ' Update the selected data point
    ActiveWorkbook.Names("myDataPoint").Value = lngDataPoint
    ' Go back to the cell (prevent Excel from activating the data series)
    rngCurrentCell.Select

End Sub

然后添加一个名为clsApp事件

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         clsAppEvent
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Application Event Class Module
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Public WithEvents xlApp As Excel.Application

Private Sub xlApp_SheetActivate(ByVal obj_Sh As Object)
    ActivateSheet obj_Sh
End Sub

Private Sub xlApp_SheetDeactivate(ByVal obj_Sh As Object)
    Reset_All_Charts
End Sub

另一个叫clsChart事件

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         clsChartEvent
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Edited by:      Robert Mundigl
'   Last edit:      27-October-2012
'   Purpose:        Handle clicks on a data point of an embedded chart
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Public WithEvents myEmbeddedChart As Chart

Private Sub myEmbeddedChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
Dim lng_Element As Long
Dim lng_Argument1 As Long
Dim lng_Argument2 As Long

    If Button = xlPrimaryButton Then
        myEmbeddedChart.GetChartElement X, Y, lng_Element, lng_Argument1, lng_Argument2
        If lng_Element = xlSeries And lng_Argument2 > 0 Then
            DropLines lng_Argument2
        End If
    End If

End Sub

最后在本工作簿模块添加

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         Workbook code
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Initialize and clean up when opening or closing the workbook
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Private Sub Workbook_Open()
    AppEventsOn
    Set_All_Charts
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    AppEventsOff
End Sub

下载 Zip

Robert 添加了下载包含各种示例的 zip 文件这将帮助您入门（并且比复制和粘贴所有这些 VBA 更容易）。

相关内容