excel - Excel计算文本值中包含数字1的列数
问题描述
我正在尝试提出一个简单的解决方案来计算包含某些数字的列数。它可以是公式或函数。
单元格将包含数字和字母的混合,即第 1 组、第 11 节、任务 21、Lot1、Item-1 等...
我想做的是只计算所有的 1,然后是 2,等等......显然不是 21 或 11。可能有空间,也可能没有,没有什么是一成不变的。我正在尝试使公式起作用,但我想我需要做一个函数并从我想要结果的单元格中调用它。即“=CountCols(“1”)。
我可以想到一个函数,但它看起来非常笨重,我想我会看看是否有人有更优雅的方式。任何帮助或想法表示赞赏。
解决方案
并感谢大家查看我的问题。
这是由我分享这篇文章的朋友回答的。她创造了这两个功能,它们实现了梦想。
(我只需要在特定工作表中搜索每列的第 6 行。)
Public Function countColumnsWithWord(strToFind As String, sSheet As String) As Long
Dim wks As Worksheet
Dim c As Long
Dim r As Long
Dim countOfFound As Long
Dim iLastCol As Integer
Dim strToSearch As String
Set wks = Application.Worksheets(sSheet)
iLastCol = wks.Cells(6, wks.Columns.Count).End(xlToLeft).Column
'start in col 2
'only row 6
c = 2
r = 6
For c = 1 To iLastCol
If wks.Cells(r, c).Value <> "" And Not IsEmpty(wks.Cells(r, c).Value) Then
strToSearch = CStr(wks.Cells(r, c).Value)
If findInString(strToFind, strToSearch) = True Then
countOfFound = countOfFound + 1
End If
End If
Next c
countColumnsWithWord = countOfFound
End Function
Public Function findInString(strToFind As String, strToSearch As String)
Dim isFound As Boolean
Dim strCurrent As String
Dim i As Long
Dim strToSearchLength As Long
strToSearchLength = Len(strToSearch)
isFound = False
strCurrent = ""
i = 1
While i < strToSearchLength + 1 And isFound = False
If Mid(strToSearch, i, 1) = " " Then
If strToFind = strCurrent Then
isFound = True
Else
strCurrent = ""
End If
Else
strCurrent = strCurrent & Mid(strToSearch, i, 1)
End If
i = i + 1
Wend
If isFound = False And strToFind = strCurrent Then
isFound = True
End If
findInString = isFound
End Function