Sub 宏3()
'
' 宏3 宏
'
'
ActiveWindow.SmallScroll Down:=-12
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
ActiveCell.FormulaR1C1 = "=YEAR(RC[-1])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D3048")
Range("D2:D3048").Select
Windows("300医药.xls").Activate
Windows("300价值.xls").Activate
Windows("300医药.xls").Activate
Windows("300价值.xls").Activate
Windows("300医药.xls").Activate
Windows("300价值.xls").Activate
Windows("300医药.xls").Activate
Windows("300价值.xls").Activate
Windows("300医药.xls").Activate
Windows("300价值.xls").Activate
Windows("300医药.xls").Activate
Windows("300价值.xls").Activate
Windows("300医药.xls").Activate
Windows("300价值.xls").Activate
Windows("300医药.xls").Activate
Windows("300价值.xls").Activate
Windows("300医药.xls").Activate
Range("P1:Y15").Select
Selection.Copy
Windows("300价值.xls").Activate
Range("P1").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Columns("X:X").Select
Columns("X:X").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 8
Range("P15").Select
Sheets("300价值的成分股").Select
Sheets.Add After:=ActiveSheet
Sheets("Sheet1").Select
Application.CutCopyMode = False
Sheets("Sheet1").Move Before:=Sheets(2)
Sheets("300价值的估值历史数据").Select
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:F").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.AutoFilter
Columns("A:A").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A2:C3048")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("A:B").Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A:$B")
ActiveSheet.Shapes("图表 1").IncrementLeft -114.75
ActiveSheet.Shapes("图表 1").IncrementTop -90.75
ActiveSheet.Shapes("图表 1").ScaleWidth 1.5354166667, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("图表 1").IncrementTop -9.75
Range("A:A,C:C").Select
Range("C1").Activate
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A:$A,Sheet1!$C:$C")
ActiveSheet.Shapes("图表 2").IncrementLeft -115.5
ActiveSheet.Shapes("图表 2").IncrementTop 118.5
ActiveSheet.Shapes("图表 2").ScaleWidth 1.5916666667, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("图表 1").IncrementLeft 6.75
ActiveSheet.Shapes("图表 1").IncrementTop 1.5
ActiveSheet.ChartObjects("图表 2").Activate
ActiveSheet.Shapes("图表 2").IncrementLeft 11.25
Range("D2").Select
ActiveCell.FormulaR1C1 = "50%百分位"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=2, Length:=2).Font
.Name = "Arial"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=4, Length:=3).Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("D3").Select
ActiveCell.FormulaR1C1 = "40%百分位"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=2, Length:=2).Font
.Name = "Arial"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=4, Length:=3).Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("D2:D3").Select
Selection.AutoFill Destination:=Range("D2:D6"), Type:=xlFillDefault
Range("D2:D6").Select
Range("E2").Select
Columns("D:D").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = "=PERCENTILE(RC[-2]:R[3046]C[-2],0.5)"
ActiveWindow.SmallScroll Down:=-21
ActiveCell.FormulaR1C1 = "=PERCENTILE(R2C3:R3048C3,0.5)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E6"), Type:=xlFillDefault
Range("E2:E6").Select
Range("E3").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R2C3:R3048C3,0.4)"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R2C3:R3048C3,0.3)"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R2C3:R3048C3,0.2)"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R2C3:R3048C3,0.1)"
Range("E9").Select
ActiveWindow.SmallScroll Down:=-6
Sheets("300价值的估值历史数据").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Columns("N:N").Select
End Sub