메뉴
실무 위키›응용 공식›엑셀 일련번호 목록에서 빠진 번호 찾는 공식

엑셀 일련번호 목록에서 빠진 번호 찾는 공식

2021
지원 버전 자세히 보기
엑셀 2021엑셀 2024M365웹 엑셀

시작 번호부터 종료 번호까지 일련번호 목록에 빠진 번호를 오름차순으로 표시합니다.

예제파일 다운로드
엑셀 일련번호 목록에서 빠진 번호 찾는 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=LET ( n, SEQUENCE ( 종료 번호 - 시작 번호 + 1, 1, 시작 번호, 1 ), IFERROR ( FILTER ( n, COUNTIF ( 일련번호 목록, n ) = 0 ), "누락 없음" ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
종료 번호필수누락 검사를 마칠 번호가 입력된 셀입니다. 시작 번호보다 크거나 같은 값이어야 하는 종료값입니다.
시작 번호필수누락 검사를 시작할 번호가 입력된 셀입니다. 목록의 첫 번호보다 앞선 값도 확인할 수 있는 시작값입니다.
일련번호 목록필수검사할 접수 번호가 입력된 범위입니다. 정렬되지 않았거나 같은 번호가 중복되어도 누락 판정에 영향을 주지 않는 목록입니다.
"누락 없음"필수검사 범위의 모든 번호가 목록에 있을 때 반환할 표시이며, 이 공식에서 그대로 사용하는 고정값입니다.
B2=LET(n,SEQUENCE($E$3-$E$2+1,1,$E$2,1),IFERROR(FILTER(n,COUNTIF($A$2:$A$8,n)=0),"누락 없음"))
A
B
C
D
E
F
1
접수 일련번호
빠진 번호
검사 기준
값
2
12045
12043
시작 번호
12041
3
12041
12046
종료 번호
12050
4
12049
12047
5
12042
6
12048
7
12044
8
12050
9
시작 번호와 종료 번호를 포함한 전체 범위에서 접수 일련번호 목록에 없는 값만 오름차순으로 표시합니다. 목록이 정렬되지 않았거나 같은 번호가 중복되어도 누락 판정에는 영향을 주지 않습니다.
이 공식이 사용하는 함수
LET 함수 SEQUENCE 함수 IFERROR 함수 FILTER 함수 COUNTIF 함수

동작 원리

SEQUENCE 함수로 시작 번호부터 종료 번호까지 전체 번호를 만들고 COUNTIF 함수로 각 번호의 등장 횟수를 센 뒤, FILTER 함수는 한 번도 나타나지 않은 번호만 남기며 IFERROR 함수는 누락 번호가 없을 때 안내 문구를 반환합니다.

예제 시트에서 빠진 번호 목록이 시작되는 B2 셀의 계산 과정을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

검사할 전체 번호를 만듭니다

시작 번호 12041부터 종료 번호 12050까지 열 개의 번호를 1씩 증가시켜 만듭니다.

=SEQUENCE ( $E$3 - $E$2 + 1, 1, $E$2, 1 )
= {12041; 12042; 12043; 12044; 12045; 12046; 12047; 12048; 12049; 12050}
2

목록에 있는 횟수를 셉니다

COUNTIF 함수는 각 후보 번호가 접수 일련번호 범위에 몇 번 있는지 셉니다. 12043, 12046, 12047은 등장 횟수가 0입니다.

=COUNTIF ( $A$2:$A$8, {12041; 12042; 12043; 12044; 12045; 12046; 12047; 12048; 12049; 12050} )
= {1; 1; 0; 1; 1; 0; 0; 1; 1; 1} 0인 위치가 목록에서 빠진 번호입니다.
3

한 번도 나타나지 않은 번호만 남깁니다

FILTER 함수가 등장 횟수가 0인 위치만 남겨 세 번호를 오름차순으로 반환합니다. 누락 번호가 없으면 IFERROR 함수가 누락 없음으로 표시합니다.

=LET ( n, SEQUENCE ( $E$3 - $E$2 + 1, 1, $E$2, 1 ), IFERROR ( FILTER ( n, COUNTIF ( $A$2:$A$8, n ) = 0 ), "누락 없음" ) )
= {12043; 12046; 12047} B2:B4 범위에 세 행으로 표시됩니다.
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료