excel分段趋势线

一、结果1、点击“开始画趋势线”2、任意选取两点3、任意再选取两点4、任意再选取两点二、实现方法：1,module1DimmyClassModuleAsNewcl_ChartEventsPublicMyStartPointSelectedAsBooleanSubInitializechart()Application.ScreenUpdating=TrueApplication.EnableEvents=TrueMyStartPointSelected=FalseOnErrorGoTos0IfActiveChartIsNothingThenSetmyClassModule.myChartClass=ActiveSheet.ChartObjects(1).ChartElseSetmyClassModule.myChartClass=ActiveChartEndIfExitSubs0:MsgBoxNoChartinthissheet!...EndSubSubInitializechart1()Application.ScreenUpdating=TrueApplication.EnableEvents=TrueOnErrorGoTos0IfActiveChartIsNothingThenSetmyClassModule.myChartClass=ActiveSheet.ChartObjects(1).ChartElseSetmyClassModule.myChartClass=ActiveChartEndIfFori=myClassModule.myChartClass.FullSeriesCollection.CountTo2Step-1myClassModule.myChartClass.FullSeriesCollection(i).DeleteNextiExitSubs0:EndSub2,ClassModulescl_ChartEventsPublicWithEventsmyChartClassAsChartPublicstrStartAsStringPrivateSubmyChartClass_Deactivate()'Application.SendKeys({ESC})EndSubPrivateSubmyChartClass_MouseDown(ByValButtonAsLong,ByValShiftAsLong,ByValxAsLong,ByValyAsLong)OnErrorResumeNextApplication.ScreenUpdating=FalseDimlElementIDAsLongDimlArg1AsLongDimlArg2AsLongmyChartClass.GetChartElementx,y,lElementID,lArg1,lArg2'MyStartPointSelected=FalseIflElementID=xlSeriesAndButton=1ThenIflArg20Then'***marktheselectedpointmyChartClass.SeriesCollection(lArg1).SelectWithSelection.MarkerBackgroundColorIndex=45.MarkerForegroundColorIndex=3EndWithmyChartClass.SeriesCollection(lArg1).Points(lArg2).SelectWithSelection.MarkerBackgroundColorIndex=5.MarkerForegroundColorIndex=3EndWith'****displaytheselectedpointdataSetrange1=Sheets(sheet1).Range($A$2:$A$16).Cells.SpecialCells(xlCellTypeVisible)ForEachcInrange1j=j+1Ifj=lArg2Thenstr1=myChartClass.SeriesCollection(lArg1).XValuesstr2=myChartClass.SeriesCollection(lArg1).Valuesstr3=str1(lArg2)str4=str2(lArg2)IfMyStartPointSelected=FalseThenstrStart=c.AddressMyStartPointSelected=TrueElseMyLineStart=Split(strStart,$)MyLineEnd=Split(c.Address,$)WithActiveChart.SeriesCollection.NewSeries'.Name=ActiveSheet.Range(G3).Values=Sheets(sheet1).Range(B&MyLineStart(2)&:B&MyLineEnd(2)).XValues=Sheets(sheet1).Range(A&MyLineStart(2)&:A&MyLineEnd(2)).Format.Line.Visible=msoFalse'.Format.ForeColor.RGB=RGB(204,0,0).MarkerStyle=3.MarkerSize=3.Trendlines.Add.Trendlines(ActiveChart.FullSeriesCollection.Trendlines.Count).SelectMyStartPointSelected=FalseEndWithmyChartClass.SeriesCollection(ActiveChart.FullSeriesCollection.Count).Trendlines(1).SelectSelection.DisplayEquation=TrueSelection.DisplayRSquared=TrueEndIfExitForEndIfNextEndIfEndIfEndSub3,分别为“开始画趋势线”、“清除所有趋势线”指定宏Initializechart、Initializechart1