'计算区间计数 countVal = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=20 and <40") Range("c2").Value = "大于等于20的数量:" & countVal countVal = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=40 AND <60") Range("c3").Value = "大于等于40的数量:" & countVal countVal = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=60") Range("c4").Value = "大于等于60的数量:" & countVal '计算所占比例 countRatio = countVal / WorksheetFunction.CountA(Range("A1:A" & lastRow)) Range("d2").Value = "大于等于20的数量占比:" & Format(countRatio, "0.00%") countRatio = countVal / WorksheetFunction.CountA(Range("A1:A" & lastRow)) Range("d3").Value = "大于等于40的数量占比:" & Format(countRatio, "0.00%") countRatio = countVal / WorksheetFunction.CountA(Range("A1:A" & lastRow)) Range("d4").Value = "大于等于60的数量占比:" & Format(countRatio, "0.00%")
时间: 2023-07-03 14:08:10 浏览: 53
这段代码使用了Excel VBA的WorksheetFunction函数来计算一个Excel表格中某一列的数值符合特定条件的数量,并计算出所占比例。具体来说,这段代码计算了表格中大于等于20、40、60的数量,以及它们所占的比例,然后将结果输出到表格中的C2、C3、C4、D2、D3、D4单元格中。其中,lastRow是一个变量,代表表格中数据的最后一行。
相关问题
'计算区间计数 countVal = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=20 and <40") Range("c2").Value = "大于等于20的数量:" & countVal countVal = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=40 AND <60") Range("c3").Value = "大于等于40的数量:" & countVal countVal = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=60") Range("c4").Value = "大于等于60的数量:" & countVal '计算所占比例 countRatio = countVal / WorksheetFunction.CountA(Range("A1:A" & lastRow)) Range("d2").Value = "大于等于20的数量占比:" & Format(countRatio, "0.00%") countRatio = countVal / WorksheetFunction.CountA(Range("A1:A" & lastRow)) Range("d3").Value = "大于等于40的数量占比:" & Format(countRatio, "0.00%") countRatio = countVal / WorksheetFunction.CountA(Range("A1:A" & lastRow)) Range("d4").Value = "大于等于60的数量占比:" & Format(countRatio, "0.00%"),统计错误
看起来你在计算区间计数和占比时出现了错误。对于区间计数,你使用了错误的语法。应该使用以下语法:
```
countVal = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=20") - WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=40") + WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=60")
```
这将计算所有大于等于20的数减去所有大于等于40的数加上所有大于等于60的数,从而得到所需的区间计数。
对于占比,你在每个计算中都使用了相同的变量 `countVal`,这是不正确的。应该使用以下语法:
```
countRatio = countVal / WorksheetFunction.CountA(Range("A1:A" & lastRow))
Range("d2").Value = "大于等于20的数量占比:" & Format(countRatio, "0.00%")
countRatio = (countVal - WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=20 AND <40")) / WorksheetFunction.CountA(Range("A1:A" & lastRow))
Range("d3").Value = "大于等于40的数量占比:" & Format(countRatio, "0.00%")
countRatio = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=60") / WorksheetFunction.CountA(Range("A1:A" & lastRow))
Range("d4").Value = "大于等于60的数量占比:" & Format(countRatio, "0.00%")
```
这将计算每个区间的占比,并将其放置于正确的单元格中。
希望这能帮助你解决问题!
Sub Statistics() Dim lastRow As Long Dim maxVal As Double Dim minVal As Double Dim avgVal As Double Dim countVal As Long Dim countRatio As Double lastRow = Cells(Rows.Count, "A").End(xlUp).Row '获取数据的最后一行 '计算最大值 maxVal = WorksheetFunction.Max(Range("A1:A" & lastRow)) Range("B1").Value = maxVal '计算最小值 minVal = WorksheetFunction.Min(Range("A1:A" & lastRow)) Range("B2").Value = minVal '计算平均值 avgVal = WorksheetFunction.Average(Range("A1:A" & lastRow)) Range("B3").Value = avgVal '计算区间计数 countVal = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=60") Range("B4").Value = countVal '计算所占比例 countRatio = countVal / WorksheetFunction.CountA(Range("A1:A" & lastRow)) Range("B5").Value = countRatio End Sub,最后输出结果加中文
Sub 统计数据()
Dim lastRow As Long
Dim maxVal As Double
Dim minVal As Double
Dim avgVal As Double
Dim countVal As Long
Dim countRatio As Double
lastRow = Cells(Rows.Count, "A").End(xlUp).Row '获取数据的最后一行
'计算最大值
maxVal = WorksheetFunction.Max(Range("A1:A" & lastRow))
Range("B1").Value = "最大值:" & maxVal
'计算最小值
minVal = WorksheetFunction.Min(Range("A1:A" & lastRow))
Range("B2").Value = "最小值:" & minVal
'计算平均值
avgVal = WorksheetFunction.Average(Range("A1:A" & lastRow))
Range("B3").Value = "平均值:" & avgVal
'计算区间计数
countVal = WorksheetFunction.CountIf(Range("A1:A" & lastRow), ">=60")
Range("B4").Value = "大于等于60的数量:" & countVal
'计算所占比例
countRatio = countVal / WorksheetFunction.CountA(Range("A1:A" & lastRow))
Range("B5").Value = "大于等于60的数量占比:" & Format(countRatio, "0.00%")
End Sub
相关推荐
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![pptx](https://img-home.csdnimg.cn/images/20210720083543.png)