Ronald는 여러 신호 수준 측정을 일련의 값으로 Excel로 가져옵니다. 그는 특정 임계 값 아래로 떨어지는이 시리즈에 존재하는 연속적인 값 그룹 수를 계산해야합니다. 예를 들어, 그는 다음 측정 값을 가질 수 있습니다.

27, 22, 22, 30, 32, 18, 22, 23, 28, 39, 24, 27, 35, 25, 21

해당 그룹의 구성원이 26 미만인 그룹의 수를 알고 싶다면 답은 4가됩니다. 이것은 26 미만의 개별 값의 수가 아니라 26 미만의 연속 값의 그룹입니다. 따라서 여기에서 경우, 네 그룹은 다음에서 대괄호로 표시됩니다.

27, [22, 22], 30, 32, [18, 22, 23], 28, 39, [24], 27, 35, [25, 21]

Ronald는 자신이 지정할 수있는 임의의 임계 값 아래로 떨어지는 그룹의 수를 알아 내기 위해 어떤 종류의 공식을 사용할 수 있는지 궁금합니다.

실제로 이에 접근 할 수있는 여러 가지 방법이 있습니다. 첫 번째는 임계 값 및 시퀀스 그룹화의 변화를 본질적으로 기록하는 “결과 열”을 사용하는 것입니다. 예를 들어 워크 시트의 A 열에 위의 값 (A2 셀에서 시작)과 E1 셀에 임계 값이있는 경우 A 열의 값 오른쪽에있는 모든 셀에 다음 수식을 사용할 수 있습니다.

=IF(A2>=$E$1,B1,IF(A1<$E$1,B1,B1+1))

공식은 임계 값 미만의 그룹의 누적 합계를 유지합니다. B 열의 최대 값 (또는 마지막 값)은 임계 값 아래의 총 그룹 수를 제공합니다. 이 수식은 A 열에서 바로 왼쪽에있는 값이 임계 값보다 높거나 낮은 지 여부를 확인합니다.

위이거나 그렇지 않고 열 A의 이전 값도 아래에있는 경우 누계를 증가시키지 않습니다. 그렇지 않으면 새 그룹이 시작되기 때문에 증가합니다.

계수를 수행하는 관련 방법은 대신이 공식을 B 열에 사용하는 것입니다.

=IF(A2>=$E$1,0,IF(A1<$E$1,0,1))

이로 인해 일련의 0 또는 1 값이 포함 된 B 열이 생성됩니다. 1 값이 발생하는 유일한 시간은 임계 값 미만인 시리즈가 시작될 때입니다. 이렇게하면 그룹 수를 제공하는 B 열의 모든 값을 쉽게 합산 할 수 있습니다.

결과 열을 사용하지 않으려면 배열 수식을 사용하여 개수를 알아낼 수 있습니다. 다음 수식은 분석 할 값이 A2에서 시작하는 A 열에 있고 임계 값이 E1 셀에 있다고 가정합니다. 또한 Ctrl + Shift + Enter를 눌러 배열 수식을 입력한다는 점을 기억하십시오.

=SUM(IF((A2:A16<$E$1)((A2:A16((A1:A15<$E$1)ISNUMBER(A1:A15))),1))

공식은 기본적으로 이전 결과 열 공식이 수행 한 작업을 수행 한 다음 (임계 값 미만 그룹이 시작되는지 여부에 따라 0 또는 1을 결정) 해당 값을 합산합니다.

물론 이러한 유형의 비교를 많이 수행하는 경우 그룹 수를 계산하기 위해 사용자 정의 함수 (매크로)를 개발할 수 있습니다. 다음은 이러한 기능의 예입니다.

Function CountGroups(ByVal MyRange As Range, Threshold As Single)

Dim Cell As Range     Dim bInGroup As Boolean     Dim iCount As Integer

Application.Volatile     iCount = 0     bInGroup = False     For Each Cell In MyRange         If Application.IsNumber(Cell) Then             If Cell < Threshold Then 'Less than the threshold?

If Not bInGroup Then  'Only count if starting new group                     iCount = iCount + 1                     bInGroup = True     'Mark as being in group                 End If             Else                 bInGroup = False        'No longer in a group             End If         End If     Next     CountGroups = iCount End Function

이 함수는 범위의 각 셀을 살펴보고 이것이 새로운 임계 값 미만 그룹의 시작인지 여부를 계산합니다. 워크 시트에서 다음과 같은 수식을 사용하여 함수를 사용합니다.

=CountGroups(A2:A16,E1)

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

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

이 팁 (3171)은 Microsoft Excel 97, 2000, 2002 및 2003에 적용됩니다. 여기에서 Excel (Excel 2007 이상)의 리본 인터페이스에 대한이 팁 버전을 찾을 수 있습니다.

link : / excelribbon-Counting_Groupings_Below_a_Threshold [임계 값 이하의 그룹 계산].