엑셀 그룹별 순번을 부여하는 2가지 방법 ~별첨. 절대주소 사용방법

업무를 하다보면 그룹별로 순번을 부여해줘야 하는 일이 종종 있습니다.

예를 들어 반별로 학생의 순번을 부여한다거나, 테이블별 컬럼의 순번을 정할때도 사용합니다.

이번 글에서는 조건에 해당하는 값의 개수를 구하는 함수인 COUNTIF 함수 또는 IF 함수를 사용해서 그룹별 순번 부여 방법에 대해 알아보도록 하겠습니다.

엑셀 그룹별 순번 부여 방법

1. IF 함수를 이용한 순번 부여

2. COUNTIF 함수를 이용한 순번 부여

1. IF 함수를 이용한 순번 부여

첫번째 방법은 if 함수를 사용하여 순번을 부여하는 방법입니다.

일단 엑셀 샘플 데이터를 만들어보도록 하겠습니다.

샘플 데이터는 반별 해당 학생의 이름에 순번을 부여하는 방법입니다.

if 함수를 사용해서 그룹별 순번을 부여하기 위해서는 표를 먼저 정렬시켜줘야합니다.

정렬방법은 표를 선택한 후 데이터 > 정렬 아이콘을 클릭해줍니다.

그리고 정렬 팝업이 나타나면 위와 같이 변경 후 확인 버튼을 클릭해줍니다.

그럼 표가 반, 이름순으로 정렬된 것을 보실 수 있습니다.

이제 순번만 부여하면 되겠죠? if 문을 사용해서 순번을 부여해보도록 합시다.

if 문의 사용법입니다.

if( 조건식, 조건의결과가 참(true)일 때 값, 조건의 결과가 거짓(false)일 때 값)

그럼 순번에 식을 작성해 봅시다. 엑셀에서 식을 입력할 때는 "="을 먼저 입력해야합니다.

=IF(B4=B3, D3+1, 1)

식을 풀이해보면 위 그림에서 ②의 값이 ①의 값과 같으면 ③값에 1을 더한 값을 표시하고 아니면 1을 표시한다는 뜻입니다.

식의 표현되로 값이 출력 된다면 D4 셀의 값은 ②(1반)의 값과 ①(반)의 값이 다르니 1의 값이 표시됩니다.

D5 셀의 값은 자동채우기로 인해 IF(B5=B4, D4+1, 1) 로 자동 변환이 됩니다. 

그럼 B5(1반)의 값과 B4(1반)의 값이 같으니 D4(1)의 값에 1을 더한 2가 표시되게 됩니다.

말로 풀어쓰니까 조금 어려워 보이네요. 실제로 값이 표시되는지 확인해보겠습니다.

반별로 순번이 자동으로 채워지시는게 보이시나요?

자동 채우기를 사용해서 변경된 수식을 오른쪽에 표시해봤습니다.

각 수식 계산해 보시면 어떻게 해서 위와 같은 결과가 나타나는지 이해하시기 편하실겁니다.

이 방법을 사용하면 정렬에 의해 순위를 구하는 로직도 구현할 수 있습니다.

다만, 이 방법은 중간에 값이 추가됐을 때 채우기를 다시 해줘야하는 단점이 있습니다.

2. COUNTIF 함수를 이용한 순번 부여

두번째 방법은 COUNTIF 함수와 절대 값을 주소를 이용해서 순번을 부여하는 방법입니다.

IF를 사용하는 방법과는 다르게 이 방법은 정렬이 필요없습니다.

동일한 샘플을 사용해서 순번을 부여하도록 하겠습니다.

COUNTIF 함수는 지정한 범위 내에서 조건에 맞는 셀의 개수를 구하는 함수입니다.

COUNTIF( 셀 범위, 조건 값)

COUNTIF 함수는 Range(값들의 범위), Criteria(조건값) 두개의 값을 인자로 합니다.

조건값에는 부등호가 들어간 문자열값이 들어갑니다.

예를 들어 A1 ~ A10 범위에 5보다 작은 값을 가진 데이터들의 개수를 구하고 싶다면 COUNTIF(A1:A10, "< 5") 처럼 쓰시면 됩니다.

부등호가 제외되면 같은 값을 찾아냅니다.

여기서는 같은 값을 찾아내는 기능을 응용해서 1반 학생의 순번을 구하면 되겠죠?

=COUNTIF($B$3:B3, B3)

식을 풀이해보면 B3(1반)~B3(1반) 범위에서 B3(1반)인 값의 개수를 표시하라는 뜻입니다. 당연히 1이 나오겠죠?

여기서 자동 채우기를 하면 그 다음 셀은 B4(3반)~B4(3반) 범위에서 B4(3반)인 값의 개수를 찾으라는 뜻으로 바뀔까요?

그럼 모든 셀의 값이 1이 나오지 않을까? 라고 생각하시겠지만 함정이 있습니다.

바로 "$B$3" 부분입니다. 엑셀에서 주소를 참조할 때 $표시를 사용하면 자동 채우기를 했을 때 값이 자동으로 변하지 않고 항상 고정되어있습니다.

엑셀에서는 "$" 표시를 두고 절대주소라고 표현 합니다.

절대 주소로 변경하는 방법은 직접 입력해도 되지만 셀을 선택 한 후 "F4"키를 누르면 자동으로 변환됩니다.

B3의 셀에서 "F4"키를 한번 누르면 $B$3의 절대주소로 변경되며, 한번 더 누르면 B$3으로, 한번 더 누르면 $B3으로 변경됩니다.

일부만 열이나 행 참조주소 일부에만 "$"값이 붙은 참조주소의 값을 혼합주소라고 합니다

B$3인 경우에는 아래, 위 방향을 향해 자동채우기를 해도 3의 값은 변하지 않게 때문에 참조되는 값이 항상 같습니다.

기본주소(상대주소)로 표시되었다면 자동채우기를 했을때 1반, 3반, 2반 ... 4반 값이 자동으로 채워져야 하지만 혼합주소를 사용했기 때문에 1반만 표시됩니다.

$B3의 경우에는 왼쪽, 오른쪽 방향을 향해 자동채우기를 해도 B의 값이 변하지 않아 참조되는 값이 항상 같습니다.

마찬가지로 상대주소로 표시되었다면 자동채우기를 통해 1반, 서준, 1 값이 자동으로 채워져야하지만 혼합주소를 사용해 역시 1반만 표시되는 것을 보실 수 있습니다.

절대주소를 이용해서 =COUNTIF($B$3:B3, B3)를 사용한 결과 값을 보겠습니다.

E3셀의 수식을 풀어보면 (1반) 값 중 1반의 개수

E4셀의 수식을 풀어보면 (1반, 3반) 값 중 1반의 개수

E5셀의 수식을 풀어보면 (1반, 3반, 2반) 값 중 1반의 개수

위와같이 범위가 하나씩 늘어나면서 개수를 구하게 됩니다.

결국 같은 반 인원의 순번이 구해지게 됩니다.

이 방법은 IF함수를 사용했을 때와는 다르게 중간에 값이 채워지더라도 수식만 복사해서 넣으면 자동으로 계산이 되는 장점이 있습니다.

맺음말

이번 글에서는 IF함수와 COUNTIF 함수를 사용해서 같은 그룹내에서 순번을 부여하는 방법에 대해서 알아봤습니다.

이 내용은 한번만 이해하시면 다른 곳에도 응용할 곳이 많으니 잘 이해가 안되더라도 몇번은 봐주시면 좋을 듯 합니다.

그럼 이번 글은 여기서 마치도록 하겠습니다. 이상한 부분이 있다면 댓글로 알려주시면 감사하겠습니다.