我無法找到一種從串列框中獲取平均值然后在用戶表單上顯示該平均值的方法。我知道您應該使用陣列，但目前我很困惑如何在串列框中排列第二列。下面是一個例子，文本框中的數字需要平均然后顯示在我的圓圈中
uj5u.com熱心網友回復：
這可能有助于澄清，它向 ListBox 添加了一個 Click 事件方法......所以單擊 ListBox 中的一個條目，訊息會告訴你他們對它的了解
Private Sub ListBox1_Click()
MsgBox Me.ListBox1.Value
MsgBox Me.ListBox1.ListIndex
MsgBox Me.ListBox1.List(Me.ListBox1.ListIndex, 0) ' The Value of Column 1
MsgBox Me.ListBox1.List(Me.ListBox1.ListIndex, 1) ' The Value of Column 2
End Sub
或者，如果您想知道總體平均值，只需將所有第 2 列條目相加并除以條目數即可
Private Sub ListBox1_Click()
Dim i As Long
Dim iMax As Long
Dim SubTot As Double
SubTot = 0
iMax = UBound(Me.ListBox1.List)
For i = 0 To iMax
SubTot = SubTot Me.ListBox1.List(i, 1)
Next i
MsgBox "The Average of ALL people is " & Round((SubTot / (iMax 1)), 2)
End Sub
uj5u.com熱心網友回復：
從串列框列中獲取平均值
- 此示例在作業表上使用 Active-X 串列框和標簽。當然，您會設法將其應用到您的用戶表單案例中。
- 它使用回圈將串列框第二列的數字寫入陣列，并
Application.Average從陣列中檢索平均值并將其寫入標簽。
Sub ListBoxAverage()
Dim Players() As Variant: Players = VBA.Array("Tim", "Jon", "Sue")
Dim Numbers() As Variant: Numbers = VBA.Array(1, 2, 4)
Dim rUpper As Long: rUpper = UBound(Players)
Dim Arr() As Double ' Array to Hold the Numbers
Dim r As Long ' List Box Row Counter
Dim n As Long ' Array Element Counter
With Sheet1.ListBox1
' Populate.
.Clear
.ColumnCount = 2
For r = 0 To rUpper
.AddItem
.List(r, 0) = Players(r) ' 1st column
.List(r, 1) = Numbers(r) ' 2nd column
Next r
' Write the values from the 2nd column to a Double array.
ReDim Arr(0 To rUpper)
Dim rValue As Variant
For r = 0 To rUpper ' .ListCount - 1
rValue = .List(r, 1) ' 2nd column
If IsNumeric(rValue) Then ' it's a number
Arr(n) = CDbl(rValue)
n = n 1
'Else ' it's not a number; do nothing
End If
Next r
End With
With Sheet1.Label1
' Write the average to the label.
If n = 0 Then
.Caption = "No average."
Else
If n < r Then
ReDim Preserve Arr(0 To n - 1)
End If
.Caption = Format(Application.Average(Arr), "0.0")
End If
End With
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/536507.html
標籤：擅长VBAexcel公式
上一篇：需要FTP檔案而不存盤解釋器檔案通過Python保存在本地
下一篇：跳轉到檔案中找到的文本