엑셀에서 축구경기 결과를 입력하면 자동으로 팀별 승/무/패/승점 데이터를 자동으로 만들어주는 수식을 만들어보도록 하겠다. 이전 글에서 FILTER와 ROWS 함수를 이용하여 팀별 경기수를 자동으로 카운트해주는 예제를 만들었는데, 여기에 추가로 승/무/패/승점을 자동으로 계산해주는 수식을 만들어보자.
엑셀을 이용하여 축구 승무패 자동 카운트
아래와 같이 축구 리그에서 각 팀별 승/무/패 횟수 및 승점을 자동으로 카운트하는 수식을 만들어보자.
축구 리그 경기결과를 통해 승/무/패/승점 자동 카운트
축구리그 팀별 승/무/패/승점을 자동으로 계산하도록 하였다. 오른쪽의 “경기 결과” 테이블은 경기를 진행하거나 예정된 경기 리스트 테이블이며, 왼쪽의 “팀별 점수” 테이블이 경기결과에 따라 자동으로 팀별 점수가 업데이트되는 테이블이다. 경기 결과 테이블에 경기가 완료되어 점수를 입력해주면 자동으로 팀별 점수 테이블에 경기수/승/무/패/승점이 각 국가별로 계산되어 입력되도록 수식이 만들어져 있다.
승/무/패 카운트 조건
경기결과에서 승/무/패를 카운트하는 조건을 먼저 정리해보자.
승(승리) 카운트 조건
- A열의 국가명이 경기결과 테이블의 H열에 있고 J열의 점수가 K열의 점수보다 크다.
- A열의 국가명이 경기결과 테이블의 I열에 있고, K열의 점수가 J열의 점수보다 크다.
- J열, K열의 점수가 모두 숫자가 입력되어야 한다.
무(무승부) 카운트 조건
- A열의 국가명이 경기결과 테이블의 H열에 있고, J열의 점수와 K열의 점수가 같다.
- A열의 국가명이 경기결과 테이블의 I열에 있고, J열의 점수와 K열의 점수가 같다.
패(패배) 카운트 조건
- A열의 국가명이 경기결과 테이블의 H열에 있고 J열의 점수가 K열의 점수보다 작다.
- A열의 국가명이 경기결과 테이블의 I열에 있고, K열의 점수가 J열의 점수보다 작다.
- J열, K열의 점수가 모두 숫자가 입력되어야 한다.
승점 계산법
- 1승마다 3점이 추가된다.
- 1무마다 1점이 추가된다.
- 패는 승점에 추가되지 않는다.
위의 조건을 만족하는 행을 A열의 각 국가별로 경기결과 테이블인 H:K 범위에서 찾아서 승/무/패 각각를 카운트하여 표기하여 준다. 그러면 이런 수식을 어떻게 만들 수 있는지를 알아보자.
필요한 함수 FILTER, ROWS
이때 필요한 함수는 FILTER, ROWS, IFERROR, ISNUMBER 이다. 여기에서 FILTER와 ROWS 함수가 핵심역할을 한다. FILTER 함수를 통해 조건에 맞는 행을 필터링하여 ROWS 함수로 행의 개수를 세면 된다.
각 함수에 대해 간단히 설명하면 아래와 같다.
| 함수 | 설명 |
|---|---|
| FILTER | 지정된 범위에서 원하는 조건으로 테이블을 필터링하여 반환하는 함수이다. 경기 결과 테이블에서 조건에 맞게 행을 필터링하기 위해 사용한다. |
| ROWS | 행의 수를 반환하는 함수이다. FILTER를 통해 조건에 맞게 반환된 행의 개수를 세기 위해 사용한다. |
| IFERROR | 수식에서 에러가 발생한 경우 지정된 값을 출력할 수 있는 기능을 하는 함수이다. FILTER 함수는 조건에 맞는 행이 없으면 에러를 발생한다. 따라서 조건에 맞는 행이 없을 경우 0으로 표시해주기 위해 IFERROR를 사용한다. |
| ISNUMBER | 값이 숫자인지를 판단하는 함수이다. 경기결과에서 점수값이 숫자로 입력되었는지를 판단하여 경기를 치뤘는지를 판단하기 위해 사용한다. |
승/무/패/승점 수식
이제 위에서 정리한 승/무/패/승점 계산 조건과 필요한 함수를 가지고 수식을 만들어보자.
승(승리) 카운트 수식
=IFERROR(ROWS(FILTER(H:K, (((H:H=A3) * (J:J > K:K)) + ((I:I=A3) * (J:J < K:K))) * ISNUMBER(J:J) * ISNUMBER(K:K) ) ), 0)
복잡해보이므로 수식을 분해해서 하나씩 순서대로 알아보자. 먼저 가장 핵심이 되는 FILTER 함수의 동작을 알아보자.
FILTER(H:K, (((H:H=A3) * (J:J > K:K)) + ((I:I=A3) * (J:J < K:K))) * ISNUMBER(J:J) * ISNUMBER(K:K) )
FILTER 함수는 (범위, 조건) 을 인자로 넣어주면 된다. 첫번째 인자로 경기결과 테이블을 나타내는 H:K 범위를 넣어주었고, 두번째 인자로 필터링할 조건을 넣어주었다. 이 조건 부분이 가장 핵심인 부분이라고 보면 되겠다.
조건문에서 +는 OR를 의미하고, *는 AND를 의미한다. 이것을 잘 기억하고 조건문을 보면 아래와 같이 해석된다.
(
(
(H:H=A3) * (J:J > K:K) ) + ( (I:I=A3) * (J:J < K:K) )
) *
ISNUMBER(J:J) *
ISNUMBER(K:K)
)- (H:H=A3) * (J:J > K:K) ) + ( (I:I=A3) * (J:J < K:K) ) : H열에 국가명이 일치하고 J열의 점수가 K열의 점수보다 크거나 또는(OR) I열의 국가명이 일치하고 K열의 점수가 J열의 점수보다 크다. (승리조건)
- ISNUMBER(J:J) * ISNUMBER(K:K) : J열이 숫자 AND K열이 숫자 (점수입력조건)
- 승리조건 AND 점수입력조건
경기 결과에서 각 팀별로 위의 조건을 만족하는 행을 모두 구하면 승리한 경기결과만 남게 되는 것이다.
ROWS( FILTER함수구문 )
FILTER에서는 조건에 맞는 행들이 모두 반환되므로, 우리는 필터링된 행을 모두 출력하는 것이 아니라 행의 개수만 필요하다. 따라서 FILTER 함수의 결과에 ROWS 함수를 사용하여 행의 개수를 카운트하여 반환하게 만든다.
IFERROR( ROWS( FILTER함수구문 ), 0 )
FILTER 함수에서 조건에 맞는 행이 없으면 에러가 반환된다. 따라서 경기결과 테이블에서 조건에 해당하는 행이 하나도 없다면 FILTER 함수에서 에러가 발생한다. 이 에러를 컨트롤 하기 위해 IFERROR 함수를 사용한다. IFERROR 함수는 (수식, 수식이 에러일 경우 값) 이렇게 2개의 인수를 사용한다. 따라서 첫번째 인자에 위에서 승리 회수를 구하는 수식을 넣어주고, 2번째 인자에 에러일 경우 0이 출력될 수 있도록 0을 넣어주면 된다.
이렇게하면 승리 횟수가 한번도 없는 국가의 경우 승리수를 0으로 표시될 수 있다.
무(무승부) 카운트 수식
=IFERROR(ROWS(FILTER(H:K, (((H:H=A3) * (J:J=K:K)) + ((I:I=A3) * (J:J=K:K))) * ISNUMBER(J:J) * ISNUMBER(K:K) )), 0)
수식의 기본틀은 위의 승리 수식과 동일하고, FILTER 함수의 조건문만 다르다. 경기결과의 점수열인 J열과 K열이 같다는 조건으로만 바꾸어 주면 된다.
(
(
(H:H=A3) * (J:J = K:K) ) + ( (I:I=A3) * (J:J = K:K) )
) *
ISNUMBER(J:J) *
ISNUMBER(K:K)
)패(패배) 카운트 수식
=IFERROR(ROWS(FILTER(H:K, (((H:H=A3) * (J:J < K:K)) + ((I:I=A3) * (J:J > K:K))) * ISNUMBER(J:J) * ISNUMBER(K:K) )), 0)
수식의 기본틀은 위의 승리 수식과 동일하고, FILTER 함수의 조건문만 다르다. 경기결과의 점수열만 승리 조건과 반대로 해주면 된다.
(
(
(H:H=A3) * (J:J < K:K) ) + ( (I:I=A3) * (J:J > K:K) )
) *
ISNUMBER(J:J) *
ISNUMBER(K:K)
)승점 카운트 수식
=C3*3 + D3
승점 계산법은 아주 간단하다. 승리시 3점을 획득하고, 무승부시 1점을 획득하면 되므로 아래와 계산이 된다.
(승리 횟수 * 3) + (무승부 횟수 * 1)
이것을 그대로 엑셀 수식으로만 넣어주면 된다.
엑셀 예제 실제로 사용해보기
아래는 Microsoft Office 365 웹을 사용하여 만든 실제 엑셀 예제이다. 아래의 예제를 마음껏 수정하면서 테스트를 해보도록 하자. 경기결과에서 점수를 추가하거나 수정해보면 팀별 승/무/패/승점이 자동으로 계산되는 것을 확인할 수 있다.
경기수 자동 카운트 만들기
경기수 자동 카운트에 대한 방법은 아래 글을 참고하도록 하자.
골득실 자동 카운트 만들기
다음으로는 아래 글을 통해 골득실을 자동으로 계산해주는 방법을 알아보자.
