■ 목차
1. 일련번호 만들기 - 자동 채우기
2. 일련번호 - 간단한 수식
3. 일련번호 만들기 - Row 함수 사용
4. 일련번호 만들기 - Aggregate 함수 (숨기기, 필터, 삭제 시도 유지)
** 핵심 요약
1. 일련번호 만들기 - 자동 채우기
- 가장 간단하게 만들 수 있는 방법입니다.
- 엑셀은 입력된 패턴을 읽고 자동으로 채워주는 기능이 있습니다. 이게 순수한 숫자랑 숫자가 포함된 문자의 경우가 조금 다른데
- 숫자의 경우 우선 최소 2개 정도의 패턴을 만들어 주고 "자동 채우기"를 해주셔야 합니다.
- 1, 2, 3, 4, … 와 같은 연속된 숫자를 입력하는 경우입니다.
- ① 먼저 1, 2 숫자를 각각의 셀에 입력을 하고 마우스로 2개의 셀을 모두 드래그해서 선택을 합니다.
- ② 우측 하단의 "채우기 핸들"이라고 네모난 작은 사각형이 보입니다.
- ③ "채우기 핸들"을 마우스로 클릭한 채 아래로 원하는 만큼 드래그해줍니다.
- 마우스를 놓게되면 연속된 일련번호가 만들어집니다.
- 이렇게 입력한 일련번호는 직접 키보드로 1 ~ 5까지 입력한 것과 동일하게 입력이 됩니다.
- 그래서 중간에 행을 삭제하거나 하게되면 일련번호가 어긋나게 되고 다시금 일련번호를 만들어 줘야 합니다.
- 하지만 가장 간단하고 손쉽게 만들 수 있는 방법입니다.
2. 일련번호 - 간단한 수식
- 일련번호를 간단한 수식을 이용해서 채우는 방법입니다.
- 첫 번째 행에 숫자 1을 입력하고 두 번째 행은 "첫번째 행 + 1"의 수식을 입력하고 이 수식을 원하는 행 만큼 복사를 해주는 방법입니다.
- 간단하고도 많이 사용하는 방법이고 중간에 행이 삭제가 되어도 연속된 일련번호가 유지가 됩니다.
3. 일련번호 만들기 - Row 함수 사용
- Row 함수는 지정된 셀의 행 번호나 지정된 셀이 없는 경우에는 현재 셀의 행 번호 숫자를 반환하는 함수입니다.
- 행 번호는 행을 삭제하는 경우에도 항상 일련번호 형식이 유지가 되기 때문에 일련번호를 만드는 용도로 많이 사용하고 있습니다.
- 사용하는 방법은 Row 함수에 시작하는 행번호가 될 수 있도록 적절한 숫자를 더하거나 빼주고 이를 자동 채우기로 수식을 복사해서 사용합니다.
- 장점은 간단한 수식으로 일련번호를 바로 만드는 것이 가능하고 간혹 행이 삭제가 되더라도 일련번호를 잘 유지해 줍니다.
- 다만 "행 숨기기"나 "자동 필터" 숨기기의 경우 일련번호가 보이는 셀로 만들어 지는 것이 아니라 숨겨진 셀을 포함하여 만들어지게 됩니다.
- 이 점은 사용자의 의도에 따라서 별 문제가 안 될 수도 있고 단점이 될 수도 있습니다.
- 사용자가 숨겨진 행을 제외하고 현재 보이는 셀만으로 일련번호를 만들고 싶다면 사용할 수 없는 방법이 되게 됩니다.
4. 일련번호 만들기 - Aggregate 함수 (숨기기, 필터, 삭제 시도 유지)
- 수식을 좀 사용해줘야 하지만 일련 번호의 끝판 왕입니다.
- 행의 삭제, 숨기기, 필터링에도 항상 보이는 셀만을 기준으로 일련번호를 만들 수 있는 방법입니다.
- Aggregate 함수는 Subtotal 함수의 상위호환 버전 쯤 되는 함수입니다. 여러 가지 함수계산을 숨겨진 행, 오류 값 등을 무시하고 계산을 할 수 있게 해주는 함수입니다.
- Aggregate 함수는 계산하고자 하는 계산식의 종류를 선택하고 숨겨진 행 등의 포함여부를 옵션으로 지정한 후에 셀 범위를 지정하게 되면
- 해당 범위의 숫자 데이터를 지정한 계산식으로 계산을 해주는 함수입니다.
- 이 함수의 "숨겨진 행 무시"라는 옵션을 활용하여 일련번호를 만들어 주면 항상 보이는 셀을 기준으로 일련번호를 유지할 수 있습니다.
- 수식 : =AGGREGATE(3, 5, $B$2:B2)
- 첫 번째 인수는 계산할 함수를 지정해야 한는데 3번은 "Counta 함수"입니다.
- 두 번째 인수는 계산 시에 무시할 값을 지정하는 인수로 5번은 "숨겨진 행 무시"입니다.
- 그리고 Counta 함수로 계산을 해줄 셀의 범위를 "$B$2:B2"와 같이 한 칸 위의 셀을 지정하는데 첫번째 셀은 절대참조로 합니다. (단축키 : F4 사용)
- 이렇게 입력을 하고 "채우기 핸들"을 이용하여 원하는 만큼 쭉 수식을 복사를 해주게 되면 아래와 같이 절대참조:상대참조 형식의 셀주가 변하게 되게 됩니다.
- "절대참조:상대참조" 형식의 셀 주소 입력에 주의해 주시기 바랍니다.
- 주소 형태가 시작셀은 절대참조로 고정이 되어 있고 끝나는 셀은 상대참조로 수식을 복사하면 선택 범위가 하나씩 늘어나도록 되어 있습니다.
- 그래서 Counta 함수로 계산을 하게 되면 일련번호처럼 숫자가 하나씩 늘어나는 구조입니다.
- 행 삭제, 행 숨기기, 자동 필터를 적용해도 항상 보이는 셀을 기준으로 일련번호가 유지됨을 확인할 수 있습니다.
- Aggregate 함수와 유사한 함수로 Subtotal 함수가 있지만 Subtotal 함수의 경우 기본 옵션으로 Subtotal 함수로 계산된 수식을 무시를 하기 때문에 금번과 같은 경우에는 사용을 할 수가 없습니다.
- 간단한 팁으로 필요하신 경우에 많이들 활용해 보시기 바랍니다.
- 수고하셨습니다.
** 핵심 요약 : 일련번호 만들기 - 행 숨기기, 필터, 삭제해도 유지되는 번호 만들기
1. 일련번호 만들기 - 자동 채우기
- 최소 2개 정도의 숫자 패턴을 입력하고 "자동 채우기"를 드래그 해준다.
- 간단히 사용 가능 하지만 행이 삭제되는 경우 일련번호를 다시 입력해줘야 한다.
2. 일련번호 - 간단한 수식
- 첫 번째 행은 숫자 1 다음 행부터는 수식으로 "바로 위의 행 + 1"를 입력해 준다.
3. 일련번호 만들기 - Row 함수 사용
- Row 함수는 지정된 셀의 행 번호나 지정된 셀이 없는 경우에는 현재 셀의 행 번호 숫자를 반환하는 함수이다.
- Row 함수에 시작하는 행 번호가 될 수 있도록 적절한 숫자를 더하거나 빼주고 이를 자동 채우기로 수식을 복사해서 사용한다.
- 간단하고 행이 삭제가 되어도 일련번호를 유지하지만 행 숨기기, 자동 필터의 경우 행이 없어지는 것이 아니기 때문에 보이는 셀만의 일련번호를 만들 수는 없다.
4. 일련번호 만들기 - Aggregate 함수 (숨기기, 필터, 삭제 시도 유지)
- 행의 삭제, 숨기기, 필터일에도 항상 보이는 셀만을 기준으로 일련번호를 만들 수 있는 방법이다.
- 수식 : =AGGREGATE(3, 5, $B$2:B2)
- 3번 "Counta 함수"를 사용해서 5번 "숨겨진 행 무시" 옵션으로 시작셀은 절대참조, 끝나는 셀은 상대참조로 입력하여 수식을 복사하여 사용한다.
- 행 삭제, 행 숨기기, 자동 필터를 적용해도 항상 보이는 셀을 기준으로 일련번호가 유지된다.
[세상의 모든 엑셀 (엑셀팁, 질문)] - 999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
[세상의 모든 엑셀 (엑셀팁, 질문)] - 001. (엑셀 팁, 질문) 누적 합계 구하기 - 절대참조/상대참조, 빠른 분석 도구 [세상의 모든 엑셀 (엑셀팁, 질문)] - 002. (엑셀 팁, 질문) 날짜 칸 나누어 입력하기
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글