从列中挑选出值到图表中

发布于 2024-12-21 06:01:51 字数 632 浏览 2 评论 0原文

我在 VBA 中编写了这段代码，用于根据单个工作表页面的值创建饼图（这些值是从学生列表数据库导入的），我想要转换为饼图的值与性别列相关，此列上的值可以是“M”或“F”，这是我尝试过的代码：

Sub CriarGrafico()
Set graficos = Sheets(3).ChartObjects
On Error Resume Next
If Not graficos Is Nothing Then graficos.Delete
'elimina os graficos existentes na folha 8
Set Grafico = graficos.Add(0, 100, 500, 250)
Grafico.Chart.ChartWizard _
Source:=Sheets(2).Range("E7:E13"), _
Gallery:=xlPie, _
CategoryLabels:=1, _
SeriesLabels:=1, _
HasLegend:=True

我这里有两个问题，首先，我的范围似乎不起作用，我猜这是因为我我只使用一个值其次，如果我确实尝试包含第二列，则图表不会聚集 M 和 F 值（因此，如果我有 3 名男学生和 2 名女学生，则图表显示 5 个值）

我希望我的图表只需显示 2 个结果，一个为 M 值和 F 值。

原文

I've made this code in VBA to create a pie chart from the values of a single sheet page (these values were imported from a students list database), the values I want to convert into a pie chart are related to the sex column, the values on this column can either be 'M' or 'F', this is the code I tried:

Sub CriarGrafico()
Set graficos = Sheets(3).ChartObjects
On Error Resume Next
If Not graficos Is Nothing Then graficos.Delete
'elimina os graficos existentes na folha 8
Set Grafico = graficos.Add(0, 100, 500, 250)
Grafico.Chart.ChartWizard _
Source:=Sheets(2).Range("E7:E13"), _
Gallery:=xlPie, _
CategoryLabels:=1, _
SeriesLabels:=1, _
HasLegend:=True

I have two problems here, first of all, my range doesn't seem to be working, I'm guessing it's because I'm only using values from one single collumn, secondly, if I do attempt to include a second column, the chart is not agglomerating the M and F values (so if I have 3 tudents that are male and 2 female students, the chart displays 5 values)

I want my chart to simply display 2 results, one for the M value and F value.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眼角的笑意。 2024-12-28 06:01:51

我假设您的输入如下所示：

Student1    M
Student2    F
Student3    F
Student4    F
Student5    M
Student6    M
Student7    F

并且您想要这样的内容：

在此处输入图像描述

图表不会“聚集M和F”，即它不会为您计算M和F的总数。您必须自己执行此操作，例如使用 =COUNTIF($E$7:$E$13,"=M") 和 =COUNTIF($E$7:$E$13,"= F")，这将导致如下所示的结果：

M    3
F    4

您可以将其绘制为饼图，如上面的饼图。

如果没有看到你的工作表，我无法告诉你为什么你的“范围不起作用”，但这段代码绝对适合我：

Dim Grafico As ChartObject
Dim s As Series
Set Grafico = Sheets(3).ChartObjects.Add(0, 100, 500, 250)
With Grafico.Chart
    .ChartType = xlPie
    .SetSourceData Source:=Sheets("Sheet3").Range("G8:H9")
    .Location Where:=xlLocationAsObject, Name:=Sheets(3).Name
End With

I'm assuming your input looks like this:

Student1    M
Student2    F
Student3    F
Student4    F
Student5    M
Student6    M
Student7    F

and you want something like this:

enter image description here

The chart will not "agglomerate M and F", i.e. it won't calculate the total number of M's and F's for you. You have to do that yourself, for example using =COUNTIF($E$7:$E$13,"=M") and =COUNTIF($E$7:$E$13,"=F"), which would result in something like this:

M    3
F    4

This you can plot as a pie chart like the one above.

Without seeing your sheet I can't tell you why your "range doesn't work", but this code definitely works for me:

Dim Grafico As ChartObject
Dim s As Series
Set Grafico = Sheets(3).ChartObjects.Add(0, 100, 500, 250)
With Grafico.Chart
    .ChartType = xlPie
    .SetSourceData Source:=Sheets("Sheet3").Range("G8:H9")
    .Location Where:=xlLocationAsObject, Name:=Sheets(3).Name
End With

回复收藏 0 原文

~没有更多了~