[Excel] 행이 삭제되거나 숨겨지더라도 행 번호를 자동으로 입력하게 만드는 방법

 정보

• 업무명     : 행이 삭제되거나 숨겨지더라도 행 번호 자동 입력 방법
• 작성자     : 박진만
• 작성일     : 2020-02-18
• 설   명      :
• 수정이력 :

 

 내용

[특징]

• 특정 행 삭제 및 숨김 이후에도 행 번호를 연속적으로 입력하는 방법 소개

 

[기능]

• SUBTOTAL 함수를 사용하여 자동 필터 이후에도 열 번호 자동 입력하기
• 행 삭제 및 숨겨진 행이 있어도 열 번호 자동 입력하기
  • SUBTOTAL 함수가 행에 미치는 기능
  • SUBTOTAL 함수를 사용하여 행 번호를 자동으로 입력시키기
•  자동 필터로 추출한 행에 행 번호 입력 여부 확인
• 특정 행을 숨긴 이후에도 행 번호 입력 여부 확인
• 특정 행을 삭제한 이후에도 행 번호 입력 여부 확인
• SUBTOTAL 함수의 또다른 기능 (링크)
• 요점

 

[사용법]

• 없음

 

[사용 OS]

• Window 10

 

[사용 언어]

• Excel v2010

 

 세부 내용

• 엑셀에서 표를 만들 때는 대체로 "No" 또는 "참조 번호" 등의 열을 만들고,행 번호를 입력하는 경우가 많다.
• 그러나 손으로 ​​1,2,3을 차례로 입력하는 것은 오타의 위험도 있고 많은 열을 입력하는 경우 시간이 걸릴 수 있다.
• 이럴 때는 현재의 행수를 취득 할 수 있는 row 함수를 사용하여 번호를 직접 입력하지 않는 경우가 일반적이다. 
• 그러나 row 함수를 사용하더라도 중간의 행이 삭제 된 경우 또는 숨겨진 경우에 대해서 올바르게 표시되지 않을 수 있다.
• 따라서 이번에는 행의 상태에 관계없이 올바른 행 번호를 자동 입력 해주는 SUBTOTAL 함수를 소개하고자 한다.
• 이 함수를 사용하면 행의 상태가 바뀌어도 행 번호 매기기를 다시 할 필요가 없기 때문에, 참고할만 하다.

 

[SUBTOTAL 함수를 사용하여 자동 필터 이후에도 열 번호 자동 입력하기]

• 아래의 엑셀 그림에 나타나는 열은 SUBTOTAL 함수가 입력되어 있고, 자동 필터에서 성별이 '여자'의 행만 추출하고 있다.
• 이 경우에도 선택 된 행에 올바른 행 번호가 자동으로 입력되어있는 것을 확인할 수 있다.

 

 

[행 삭제 및 숨겨진 행이 있어도 열 번호 자동 입력하기]

• SUBTOTAL 사용하기 전에 어떤 상태일때 행 번호를 자동으로 입력할 수 있는지 알아보자.

 

• SUBTOTAL 함수가 행에 미치는 기능
  • SUBTOTAL 함수는 행이 다음 상태일 때 제대로 된 행 번호를 입력 할 수 있다.
    • 자동 필터로 추출된 행
    • 도중에 숨겨진 행이 포함된 행
    • 중간 줄을 삭제했을 때
  • 즉 "현재 보이는 행" 에 행 번호를 입력 할 수 있다.

 

• SUBTOTAL 함수를 사용하여 행 번호를 자동 입력시키기
  • 이제  SUBTOTAL 함수를 사용하여 행 번호를 자동으로 입력해보자.
  • 행 번호를 입력하는 첫 번째 셀 (B4)에 다음 SUBTOTAL 함수의 식을 입력한다.
    • = SUBTOTAL (103, $C $3:C4 ) - 1

 

• 해당 함수에 대한 상세 설명

▶ SUBTOTAL 함수의 첫 번째 인수의 "103"은 "COUNTA 계산" 을 의미하며, 두 번째 인수는 계산 대상 범위 $C$3 ~ C4 (C3-C4)을 지정하는 것이다. 결국 여기에 입력 한 수식의 의미는 "셀 $C$3에서 수식을 입력 한 셀 (C4)까지 셀의 개수" 를 의미하는 것. ▶ 즉, 위 수식에서 계산된 셀 개수는 제목에 해당하는 셀과 수식을 입력 한 셀인 총 2 개이므로 "2 - 1"의 "1"이 리턴되는 것이다. ▶ 제목 셀(C3) 까지 포함하여 셀을 계산하는 이유는 SUBTOTAL 함수를 사용한 자동 필터 문제 (마지막 줄이 필터의 대상에서 분리되는 현상) 을 피하기 위함이다.

 

• 다음 작업으로는 입력 한 SUBTOTAL 함수의 식을 마지막 행까지 드래그 하여 확장시키면 된다.

• 이제 마지막 행까지 행 번호가 입력 될 것이다.
• 여기까지 한 후에는 자동 필터링을 하던 숨겨진 행을 만들던 제대로 행 번호가 입력 될 것이다.

 

[특정 행을 숨긴 이후에도 행 번호 입력 여부 확인]

• 「성별」에서 '남자'를 필터링 한 경우이다.
• 제대로 행 번호가 입력되는것을 확인할 수 있다.

 

[특정 행을 숨긴 이후에도 행 번호 입력 여부 확인]

• 중간 행 (4~6번 행)을 숨겼지만 일련번호가 재대로 입력된다.

 

[특정 행을 삭제한 이후에도 행 번호 입력 여부 확인]

• 마찬가지로, 중간 행을 삭제해도 올바른 행 번호가 나타난다.

 

[SUBTOTAL 함수의 또다른 기능 (링크)]

• SUBTOTAL 함수를 이용하여 부분합을 쉽게 구할 수 있다.

https://shlee1990.tistory.com/497

[Excel] 엑셀에서 부분합을 쉽게 계산하기

 

• 또한 동일한 함수를 이용하여 행 필터링 및 숨기기 후 나타나는 행의 합계만을 계산할 수 있다.

https://shlee1990.tistory.com/499

[Excel] 엑셀에서 필터링 후 남은 행의 합계 구하기

 

요점

• 행 상태에 관계없이 보이는 행의 행 번호를 자동 입력하고 싶은 경우 SUBTOTAL 함수를 사용한다.

 

 참고 문헌

[논문]

• 없음

[보고서]

• 없음

[URL]

• 없음

 

 문의사항

[기상학/프로그래밍 언어]

• sangho.lee.1990@gmail.com

[해양학/천문학/빅데이터]

• saimang0804@gmail.com