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