엑셀에서 일련번호 자동으로 부여하기(ROW함수, COUNTA함수 활용)

엑셀에서 목록을 작성할 때 일련번호를 일일이 부여하는 게 굉장히 번거롭습니다. 1, 2, 3을 입력한 뒤에 드레그해서 입려할 수도 있지만 목록이 계속 업데이트 된다면 또다시 드레그를 해줘야하는 불편함이 생깁니다. 그래서 이때 함수를 이용해서 자동으로 목록 개수에 맞춰 번호가 입력되도록 해보겠습니다.

---

1. 번호에 다음과 같이 함수를 입력합니다.

=IF(LEN(@품목)>0,ROW(A1),"")

LEN함수는 텍스트의 입력된 개수를 확인해주는 함수입니다. “LEN(@품목)>0”의 의미는 폼목에 아무것도 적혀있지 않을 경우를 반환한다는 뜻입니다. IF 함수의 조건과 함께 쓰여 만약 폼목에 아무것도 적혀있지 않을 경우 “” 빈 칸을 반환합니다.

ROW함수는 선택된 셀의 행 번호를 반환해 줍니다. ROW(A1)의 경우 A열의 1행이라는 뜻이므로 숫자 1이 반환됩니다. 만약 ROW(A2) 또는 ROW(B2) 이렇게 입력할 경우 모두 동일하게 숫자 2를 반환할 것입니다.

함수 최종 해석, IF함수와 결합해서 품목에 한 글자라도 적혀 있을 경우 ROW(A1)부터 숫자를 카운팅 합니다. 만약 한 글자도 없을 경우 빈칸으로 놔둡니다. 여기서 ROW함수는 상대 참조이므로 아래로 내려갈수록 ROW(A1, A2, A3, A4,…)이런식으로 번호가 매겨지는 게 원리입니다.

그런데 이 방법으로는 빈칸에 숫자가 카운팅 되어 키위가 6번이 아니라 7번이 됩니다. 이를 해결할 수 있는 다른 방법을 알아보겠습니다.

 

2. 번호에 다음과 같은 함수를 입력합니다.

=IF(LEN(@품목)>0,COUNTA($C$3:C9),"")

1번 식에서 ROW함수가 COUNTA함수로 변경되었습니다. COUNTA함수는 값이 있는 셀의 숫자를 세주는 함수입니다. 따라서 빈칸을 제외하고 품목에 6개가 있다고 판단하여 키위가 6번이 되는 원리인 거죠.

여기서 주의할 점은 COUNTA($C$3:C9)처럼 품목의 첫 행인 C3에 $표시가 붙었다는 것입니다. 여기서 $는 절대참조 표시로 해당 셀을 선택하고 키보드 F4를 누르면 누르는 순서대로 (1)셀 절대 참조, (2)행 절대 참조, (3)열 절대 참조, (4)상대 참조로 바뀝니다. 여기서는 품목의 시작 행부터 카운팅을 해야하므로 셀 절대 참조로 입력합니다.

마지막으로 병합된 셀에서는 어떻게 번호를 입력하는지 알아보겠습니다.

 

3. 여기서는 입력하는 방법만 주의하면 됩니다.

=COUNTA($C$3:C3)

 

합수를 1번이 들어갈 셀에 입력한 뒤 원하는 번호까지 드레그를 해줍니다. 그 뒤에 [Ctrl] + [Enter] 를 눌러 수식을 일괄 입력해 줍니다. 그러면 병합된 셀에도 알맞은 번호가 입력된 것을 확인할 수 있습니다. 2번, 3번 함수는 다음과 같이 입력됩니다.

2번 =COUNTA($C$3:C6)

3번 =COUNTA($C$3:C9)

수식을 복사하는 [Ctrl] + [Enter] 키를 활용하면 병합된 셀에도 원하는 방식으로 번호를 부여할 수 있습니다.