엑셀 빈 셀 제외 순번 매기기 & 새 데이터마다 새 번호 매기기
회사에서 엑셀로 데이터를 관리할 때에는, 체계적인 관리를 위해 표의 첫 열에 일련번호를 작성하는 경우가 많습니다. 만약 데이터에 일련번호가 작성되지 않아 새롭게 일련번호를 추가해야 할 때에는, 데이터 구조에 따라 번호를 매기는 방법도 다양한데요.
오늘은 표 구조에 따라 일련번호를 손쉽게 매기는 방법을 실전 예제와 함께 알아보겠습니다.
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [함수 공식] 엑셀 빈 셀 제외 순번 매기기예제파일
공식 요약
비어 있는 셀 제외 순번 구하기 공식 =IF(셀="","",COUNTA($셀:셀)인수 설명 셀 값이 비어있는지 확인할 셀입니다.
오빠두Tip : $셀:셀은 확장범위입니다. 만약 B4셀이라면, $B$4:B4 로 작성합니다.보너스 공식!
Q - 만약 비어있는 셀을 제외하고 1부터 증가하는 순번을 매겨야 할 땐, 이렇게 사용해보세요!
=IF(셀="","",COUNTIF($셀:셀,셀)
빈 셀을 제외하고 1부터 새롭게 증가하는 순번을 매깁니다 빈 셀은 제외하고 순번 매기기
예제파일을 실행하면 각 지역별 고객의 매출현황이 정리되어 있습니다. 표를 잘 살펴보니, 각 지역별 매출 합계를 계산하면서, 표 중간에 빈셀이 포함되어 있는데요!!😱 이럴 경우, IF 함수와 COUNTA 함수를 사용하면 빈셀을 제외한 순번을 매길 수 있습니다.
IF 함수와 COUNTA 함수를 사용하면 빈 셀을 제외한 순번을 매길 수 있습니다. 오빠두Tip : 만약 그룹별로 구분된 데이터라면 아래 1분 영상강의에 소개해드린 방법을 사용해보세요! 매우 편리하게 그룹별 순번을 매길 수 있습니다!
- 예제파일의 1번 시트로 이동 후, 번호가 시작되는 셀인 A4셀에 아래와 같이 함수를 입력합니다.
=IF(B4="","",COUNTA($B$4:B4)함수 설명
① IF 함수는 '지역(B4)'셀이 비어 있다면 빈 셀로 두고 그렇지 않을 시, COUNTA 함수로 셀 개수를 구합니다.
② COUNTA 함수의 $B$4:B4는 "확장범위"입니다. $B$4셀을 고정하고, 수식이 아래로 자동채우기 되면 $B$4:B5, $B$4:B6,.. 로 범위가 한 칸씩 확장됩니다.
IF 함수와 COUNTA 함수를 작성합니다. - B4셀에 수식을 입력 후, 수식을 아래로 자동채우기 합니다. 만약 자동채우기한 범위의 서식이 변경되었다면, 자동채우기 옵션 단추를 클릭한 후 '서식 없이 채우기'를 선택하면 기존 서식을 유지한 상태로 자동채우기 됩니다.
수식을 입력하면 빈 셀을 제외하고 순번이 매겨집니다. 오빠두Tip : 자동채우기는 실무자가 꼭 알아야 하는 엑셀 필수 기능입니다!😉 자동채우기에 대한 자세한 설명은 아래 5분 영상 강의를 참고해주세요!
새 데이터마다 1부터 증가하는 순번 매기기
또는 새로운 데이터마다 1부터 새롭게 증가하는 순번을 매겨야 할 때가 있습니다. 그럴 때는 IF 함수 하나만 사용해서 간단하게 문제를 해결할 수 있습니다!👍
IF 함수 하나만 사용하면 새로운 데이터마다 1씩 증가하는 순번을 매길 수 있습니다 - 예제파일의 '2'번 시트로 이동한 후, 번호가 시작될 셀인 A4셀에 IF함수를 아래와 같이 작성합니다.
=IF(B4=B3,A3+1,1)함수 설명
윗셀에 작성된 이전 지역과 현재 지역이 같으면 이전 숫자에 1을 더하고, 그렇지 않으면 숫자 1을 반환합니다.
새로운 데이터마다 1씩 증가하는 순번을 매기기 위한 IF 함수를 작성합니다. - 자동채우기 핸들을 표의 맨 마지막 셀까지 드래그해서 자동채우기하면, 각 지역별로 1씩 증가하는 순번이 작성됩니다.
수식을 자동채우기 하면 새로운 데이터마다 1씩 증가하는 순번이 매겨집니다.
- 예제파일의 1번 시트로 이동 후, 번호가 시작되는 셀인 A4셀에 아래와 같이 함수를 입력합니다.
