excel2007制作柏拉图

我用VBA在EXCEL2010下做了段程序，可自动生成柏拉图。

以下是程序源代码

'数据区域的左上角位置常量

Const UpperLeftPos = "B4"

Const UpperPos = "B"

Const LeftPos = 4

Sub 绘制标准柏拉图()

Dim Range1 As Range

'选择数据区域，Range("B2")这个格子里放的是有效数据的行数，即分类的总个数

Set Range1 = Range(UpperLeftPos &":" &Chr(Asc(UpperPos) + 1) &(LeftPos + Range("B2")) &"," &Chr(Asc(UpperPos) + 2) &LeftPos &":" &Chr(Asc(UpperPos) + 2) &(LeftPos + Range("B2") + 1))

'插入柱形图

ActiveSheet.Shapes.AddChart.Select

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=Range1

'把百分比数据系列的图形格式变为“带数据值的折线图”，并画在由次要横坐标和次要纵坐标里

ActiveChart.SeriesCollection(2).Select

ActiveChart.SeriesCollection(2).AxisGroup = 2

ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers

ActiveChart.SetElement (msoElementSecondaryCategoryAxisWithoutLabels)

ActiveChart.SetElement (msoElementSecondaryCategoryAxisShow)

'把次要横坐标的值显示到刻度上，而不是刻度中间，并隐藏次要横坐标的显示

ActiveChart.Axes(xlCategory, xlSecondary).Select

Selection.MajorTickMark = xlNone

Selection.TickLabelPosition = xlNone

ActiveChart.Axes(xlCategory, xlSecondary).AxisBetweenCategories = False

'调整柱形图的显示，去掉相邻柱子的间距，并给每个柱子加外框

ActiveChart.SeriesCollection(1).Select

ActiveChart.ChartGroups(1).GapWidth = 0

With Selection.Format.Line

.Visible = msoTrue

.ForeColor.ObjectThemeColor = msoThemeColorText1

.ForeColor.TintAndShade = 0

.ForeColor.Brightness = 0

End With

With Selection.Format.Line

.Visible = msoTrue

.Weight = 1

End With

'修改主要纵坐标，满值为累加和

ActiveChart.Axes(xlValue).Select

ActiveChart.Axes(xlValue).CrossesAt = 0

Selection.MajorTickMark = xlInside

ActiveChart.Axes(xlValue).MinimumScale = 0

ActiveChart.Axes(xlValue).MaximumScale = Range(Chr(Asc(UpperPos) + 3) &(LeftPos + Range("B2") + 1))

'修改次要纵坐标，满值为100%

ActiveChart.Axes(xlValue, xlSecondary).Select

ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 1

ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = 0

Selection.MajorTickMark = xlInside

ActiveChart.Axes(xlValue, xlSecondary).CrossesAt = 1

Selection.TickLabels.NumberFormat = "0%"

'让百分比折线上的点显示出具体的数值

ActiveChart.SeriesCollection(2).Select

ActiveChart.SeriesCollection(2).ApplyDataLabels

ActiveChart.SeriesCollection(2).DataLabels.Select

Selection.Position = xlLabelPositionRight

'隐去横向的主要网格线

ActiveChart.Axes(xlValue).Select

ActiveChart.Axes(xlValue).MajorGridlines.Select

Selection.Format.Line.Visible = msoFalse

'为图表添加标题，标题的内容放在Range("B1")格子里

ActiveChart.SetElement (msoElementChartTitleAboveChart)

ActiveChart.ChartTitle.Select

ActiveChart.ChartTitle.Text = Range("B1")

End Sub

上网查了一下柏拉图分布，其中α,β>0为二正数。matlab好像没有直接生成这个分布的函数。

不过任何一个分布都可以由均匀分布表示出来。

假设有一个均匀分布p

则有p=得到的x用p表示的函数就是你要的分布

得到x=(a/(1-p))^(1/b)     根据x>a有0<p<1

我试着画了a=1,b=2的图像，此时f(x)=2/(x^3)

就画这个频率图为

然后用程序

p=unifrnd(0,1,1,10^7)

a=1

b=2

x=(a./(1-p)).^(1/b)

[m,n]=hist(x,30)

bar(n,m/sum(m))

画出来的频率直方图为

---