Gráfico de dispersão do Excel para mostrar a barra transversal no ponto selecionado

Question

Existe uma maneira de configurar o Excel 2010 para mostrar uma barra transversal no ponto selecionado em um gráfico de dispersão? O comportamento padrão é mostrar uma dica de ferramenta com o valor do ponto ao passar o cursor do mouse. Quando clicar no ponto, seria bom mostrar a barra (que se estende até a borda, facilitando a leitura da escala).

    

Answer 1

Se eu entendi a pergunta corretamente, você está procurando por algo assim quando clica em um ponto de dados em um gráfico de dispersão?

RobertMundigldescreve como fazer isso em seu blog Clearly and Simply, adaptando um Jon Peltier técnica . Funciona muito bem.

Leia o blog para todos os detalhes. Eu apenas resumi os principais passos abaixo.

Técnica Robert Mundigl - Resumo

Primeiro defina os seguintes intervalos nomeados

Emseguida,alterneasbarrasdeerroem

NowweaddhorizontalandverticalerrorbarstothedataseriesofthechartusingthestandardExcelfunctionality(ribbonChartTools|TabChartandLayout|ErrorBars.IntheFormatErrorBarsdialogue,weselectErrorAmount“Custom”andspecifythevaluesusingtheerrorbaramountnamedformulas(myEB_X_Pos,etc.)

Adicione o código VBA

Se você nunca usou o VBA antes, leia este Guia de introdução do VBA .

No Editor do Visual Basic (pressione Alt + F11 para acessar isso), insira um módulo chamado modAppEvent .

' ----------------------------------------------------------------------------------------------------------------------------------
'   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

adicione outro módulo chamado modChartEvent

' ----------------------------------------------------------------------------------------------------------------------------------
'   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

e um terceiro chamado modDropLines

' ----------------------------------------------------------------------------------------------------------------------------------
'   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

Em seguida, adicione um módulo de classe chamado clsAppEvent

' ----------------------------------------------------------------------------------------------------------------------------------
'   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

e outro chamado clsChartEvent

' ----------------------------------------------------------------------------------------------------------------------------------
'   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

Finalmente, no módulo ThisWorkbook , adicione

' ----------------------------------------------------------------------------------------------------------------------------------
'   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

Download do Zip

Robert adicionou um zip de download contendo vários exemplos que o ajudarão a começar mais fácil do que copiar e colar todo esse VBA).