[Excel] 연승, 연패 또는 연속된 값의 Count

경기 결과 승무패를 계속 기재한 자료에서 연속으로 승리한 연승이나 연속으로 패배한 연패의 최대수를 계산하는 수식입니다.

 

1. Index함수와 Find, Rept 함수를 이용한 방법

최근에 Excel 2021이나 Microsoft 365는 배열을 지정하면 자동으로 배열함수로 계산을 하지만,
Excel 2019이하 버전은 Ctrl+Shift+Enter로 입력하거나 INDEX함수를 사용해야 합니다.

 

아래는 INDEX함수를 이용하여 배열을 만들어 최대값을 찾아내는 방법입니다.

 

경기 결과가 C4:V4 범위에 기재되어 있을 때의 수식은 아래와 같습니다.

=MAX( INDEX( ISNUMBER( FIND( REPT( W$3, ROW($A$1:$A$20) ), CONCAT($C4:$V4) ) )*ROW($A$1:$A$20), ) )

 

가장 안쪽부터 수식을 보면

CONCAT($C4:$V4) => "승승무무무무무승패패패승승승승패패무승승"
이 부분은 CONCAT함수가 Excel 2019부터 지원이라 그이전의 버전은 아래와 같이 하나씩 지정해 주어야 합니다.

=CONCATENATE($C4,$D4,$E4,$F4,$G4,$H4,$I4,$J4,$K4,$L4,$M4,$N4,$O4,$P4,$Q4,$R4,$S4,$T4,$U4,$V4)

 

REPT( W$3, ROW($A$1:$A$20) )  이 부분은 바깥쪽의 Index의 영향으로 ROW(A1)~ROW(A20)이 적용되어

  => {"승","승승","승승승","승승승승"... "승승승승승승승승승승승승승승승승승승승승"}

 

이걸 결합하면 Index함수 때문에 아래와 같은 수식을 연속으로 작성하게 됩니다.

FIND("승", "승승무무무무무승패패패승승승승패패무승승")

FIND("승승", "승승무무무무무승패패패승승승승패패무승승")

...

FIND( "승승승승승승승승승승승승승승승승승승승승",  "승승무무무무무승패패패승승승승패패무승승")

=> 이 결과는 {1, 1, 12, 12, #Value, #Value,,,,}가 됩니다.

 

ISNUMBER( {1, 1, 12, 12, #Value, #Value,...} ) => {True, True, True, True, False, False,...}

 

ISNUBER( ...) * ROW(A1:A20) 은 {True, True, True, True, False, False,...} * {1,2,3,4,5,...19,20}

=> 이 결과는 {1,2,3,4,0,0,...}

 

이렇게 범위로 설정한 것을 하나씩 대입해서 배열을 만들어 주는게 INDEX()함수입니다.

 

최종적으로 

MAX( {1,2,3,4,0,0,...} ) => 최종 결과 4가 됩니다.

 

2. Frequecy 함수를 이용한 방법

 

우선 첫번째와 두번째 IF문을 풀어 보면 아래 그림과 같이 해당하는 칸의 열번호와 아닌 칸의 열번호 배열이 됩니다.

다음에 사용하는 Frequency 함수는 첫번째 인수의 데이터를 분류하여 두번째 인수의 기준수들의 이하의 빈도수를 순차적으로 반환하며 마지막 요소의 '초과'가 하나 더 추가 된 배열이 나옵니다.

이는 아래 그림을 참고하면 이해가 쉽습니다. 

맨 오른쪽 빨강색이 마지막 요소 [20]의 초과를 상징적으로 보여줍니다.

 

마지막으로 Frequency의 빈도수에서 MAX 값을 구하면 최다 기록이 확인 됩니다.

 

연승.xlsx

0.01MB