在本文中，我们将学习如何在Microsoft Excel中通过VBA计算和显示总计。

让我们通过一个简单的练习来了解如何通过Microsoft Excel中的VBA计算和显示总计的方式和位置。在工作表1中有数据，其中A列包含类别，B列包含竞争对手ID，C列包含状态。

现在，我们要在同一工作簿中的Sheet2中检索一个报告，其中包含分类为合格和不合格候选人的数据。

image 1

image 2

请遵循以下给定步骤：-

  • 按Alt + F11键打开VBE页面以编写宏。

  • 然后转到插入选项卡并插入一个模块。

  • 在页面中写下以下提到的代码。

Sub CountStatus()

Dim Lastrow As Long, Countpass1 As Long, countfail1 As Long

Dim erow As Long, Countpass2 As Long, CountFail2 As Long

Lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Countpass1 = 0

countfail1 = 0

Countpass2 = 0

CountFail2 = 0

For i = 2 To Lastrow

If Sheet1.Cells(i, 1) = "CTY1" And Sheet1.Cells(i, 3) = "Pass" Then

Countpass1 = Countpass1 + 1

ElseIf Sheet1.Cells(i, 1) = "CTY1" And Sheet1.Cells(i, 3) = "Fail" Then

countfail1 = countfail1 + 1

ElseIf Sheet1.Cells(i, 1) = "CTY2" And Sheet1.Cells(i, 3) = "Pass" Then

Countpass2 = Countpass2 + 1

ElseIf Sheet1.Cells(i, 1) = "CTY2" And Sheet1.Cells(i, 3) = "Fail" Then

CountFail2 = CountFail2 + 1

End If

Next i

'Msgbox "Pass count of CTY1," & " " & Countpass1 & " " & "Fail Count of CTY1," & " " & countfail1 & vbCrLf & "Pass count of CTY2," & " " & Countpass2 & " " & "Fail Count of CTY2," & " " & CountFail2

Sheet2.Range("A2:C500").Clear

Sheet2.Cells(erow, 1) = "CTY1"

Sheet2.Cells(erow, 2) = Countpass1

Sheet2.Cells(erow, 3) = countfail1

erow = erow + 1

Sheet2.Cells(erow, 1) = "CTY2"

Sheet2.Cells(erow, 2) = Countpass2

Sheet2.Cells(erow, 3) = CountFail2

End Sub

•要运行宏，请按F5键。

•所有详细信息将根据要求在Sheet2中更新。

image 3

这是通过Microsoft Excel中的VBA计数和显示总计的方法。

image 48

如果您喜欢我们的博客，请在Facebook上与您的朋友分享。另外，您也可以在Twitter上关注我们。__我们很高兴收到您的来信，请告知我们如何改进，补充或创新我们的工作，并为您做得更好。写信给我们[email protected]