Jeff는 C 열에 상당한 양의 텍스트가 포함 된 워크 시트를 가지고 있습니다. 그는 C 열 내에서 특정 문자열의 발생 횟수를 계산하려고하지만 데이터의 홀수 행 (1, 3, 5 등)에 대해서만 계산하려고합니다. 그가 찾고있는 문자열은 전체 셀 내용이 아닐 수 있으며 특정 셀 내에서 여러 번 나타날 수 있습니다. (셀에서 2 ~ 3 회 발생하면 2 ~ 3 회 발생하는 것으로 간주됩니다.) 그는 매크로를 사용하지 않고이를 수행하는 공식적인 방법이 있는지 궁금합니다.

이에 접근하는 한 가지 방법은 도우미 열을 사용하는 것입니다. 예를 들어, D 열을 도우미 열로 사용할 수 있고 데이터를 포함하는 첫 번째 셀이 C2 셀이라고 가정 해 보겠습니다. (아마도 C1 셀에는 열 머리글이 있습니다.) D2 셀에서 다음 수식을 사용할 수 있습니다.

=IF(MOD(ROW(),2)=1,(LEN(C2)-LEN(SUBSTITUTE(C2,"my text","")))/LEN("my text"),"")

당신이해야 할 일은 검색 문자열 ( “my text”)을 당신이 찾고있는 것으로 바꾸는 것입니다. LEN 함수는 두 번 사용됩니다. 먼저 C2 셀에있는 길이를 찾은 다음 여기에서 “my text”의 모든 인스턴스가 제거 된 텍스트 길이를 뺍니다. 그런 다음이 값을 검색하려는 항목의 길이로 나누어 셀에 검색 텍스트의 인스턴스 수를 나타냅니다. IF 함수는 행이 홀수 행인 경우에만 숫자 값이 반환되도록합니다.

이 수식을 필요한만큼 행 아래로 복사 한 다음 열을 합산 할 수 있습니다. 결과는 문자열이 홀수 행에 나타나는 횟수입니다.

워크 시트 레이아웃에서 도우미 열을 사용할 수없는 경우 데이터 배열에서 작동하는 수식을 사용할 수 있습니다.

트릭을 수행하는 사람은 다음과 같습니다.

=SUMPRODUCT((LEN(C:C)-LEN(SUBSTITUTE(C:C,"my text","")))/LEN("my text")*ISODD(ROW(C:C)))

이 공식은 SUMPRODUCT 함수가 C 열의 각 셀에 대해 내부적으로 계산을 수행한다는 점을 제외하고는 기본적으로 이전 공식과 동일합니다. 공식이 C 열을 모두 검사하므로 검색 텍스트 ( “my text “)가 열의 데이터가 아닌 셀 (예 : 열 머리글) 내에서 발생하면 반환 된 합계에도 포함됩니다.

매크로를 사용하기로 결정한 경우 셀 범위를 검사하고 개수를 결정하는 사용자 정의 함수를 쉽게 만들 수 있습니다. 다음은 사용할 수있는 매크로 유형의 예입니다.

Function CountInst(rSource As Range, sSearch As String, bCaseInsensitive As Boolean) As Integer     Dim c As Range     Dim iCount As Integer     Dim sTemp1 As String     Dim sTemp2 As String

sTemp2 = sSearch     If bCaseInsensitive Then sTemp2 = LCase(sTemp2)

iCount = 0

For Each c In rSource         If c.Row Mod 2 = 1 Then             sTemp1 = c.Text             If bCaseInsensitive Then sTemp1 = LCase(sTemp1)



iCount = iCount + (Len(sTemp1) - _               Len(Replace(sTemp1, sTemp2, ""))) / Len(sTemp2)

End If     Next c

CountInst = iCount End Function

이를 사용하려면 범위, 검색하려는 항목 및 일치 항목이 대소 문자를 구분하지 않을지 여부를 제공하기 만하면됩니다. 예를 들어 “my text”를 검색하고 대소 문자를 구분하지 않으려면 다음을 사용합니다.

=CountInst(C2:C99,"my text",TRUE)

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (1514)은 Microsoft Excel 2007, 2010, 2013 및 2016에 적용됩니다.