사번을 부서코드, 입사연도, 일련번호로 나누는 공식
2021지원 버전 자세히 보기
고정 길이 사번을 부서코드, 입사연도, 일련번호로 나눕니다.
인수 설명
01수식 하나로 사번 전체 나누기 (엑셀 2021/M365)
=MID ( 사번, XLOOKUP ( 결과 머리글, 구간 항목, 시작 위치 ), XLOOKUP ( 결과 머리글, 구간 항목, 글자 수 ) )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
수식을 입력한 셀자동으로 채워진 범위
02구간표에 따라 사번 항목 나누기 (엑셀 2021 이후)
=MID ( 사번, XLOOKUP ( INDEX ( 결과 머리글 범위, 1, COLUMNS ( $B$1:B$1 ) ), 구간 항목 범위, 시작 위치 범위 ), XLOOKUP ( INDEX ( 결과 머리글 범위, 1, COLUMNS ( $B$1:B$1 ) ), 구간 항목 범위, 글자 수 범위 ) )인수 설명 자세히 보기
동작 원리
01수식 하나로 사번 전체 나누기 (엑셀 2021/M365)
두 XLOOKUP 함수는 항목별 시작 위치와 글자 수를 구합니다. MID 함수는 이 값으로 사번 5개에서 세 항목을 한 번에 잘라냅니다.
예제 시트에서 사번 5개를 한 번에 나누는 B2 셀의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
XLOOKUP 함수가 머리글 순서로 시작 위치 배열을 반환합니다
결과 머리글 B1:D1의 세 항목을 구간 항목 F2:F4에서 찾아 시작 위치 G2:G4의 값을 한 번에 가져옵니다. 부서코드·입사연도·일련번호 순서로 {1, 3, 7}을 반환합니다.
=XLOOKUP ( 결과 머리글, 구간 항목, 시작 위치 )
= {1, 3, 7}
XLOOKUP 함수가 머리글 순서로 글자 수 배열을 반환합니다
같은 방식으로 글자 수 H2:H4의 값을 한 번에 가져옵니다. 부서코드 2자·입사연도 4자·일련번호 4자 순서로 {2, 4, 4}를 반환합니다.
=XLOOKUP ( 결과 머리글, 구간 항목, 글자 수 )
= {2, 4, 4}
MID 함수가 사번 범위 전체에서 항목을 한 번에 잘라냅니다
두 XLOOKUP 함수가 반환한 배열을 시작 위치와 글자 수로 넣으면 MID 함수가 5개 사번 전부에서 세 항목을 동시에 추출합니다. B2 셀 하나의 수식으로 5행 3열 결과가 펼쳐집니다.
=MID ( A2:A6, {1, 3, 7}, {2, 4, 4} )
= {"HR","2021","1037"; "IT","2019","1224"; "SA","2023","2008"; "FN","2020","1056"; "MK","2024","1111"}
B2:D6 범위에 5행 3열로 펼쳐집니다. 02구간표에 따라 사번 항목 나누기 (엑셀 2021 이후)
COLUMNS 함수와 INDEX 함수는 현재 결과 머리글을 고릅니다. 두 XLOOKUP 함수는 시작 위치와 글자 수를 찾습니다. MID 함수는 사번에서 해당 구간을 잘라냅니다.
예제 시트에서 HR20211037의 부서코드를 나누는 B2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
현재 결과 열의 순번을 구합니다
B열은 결과 머리글 범위의 첫 번째 열입니다. B2 셀에서는 첫 머리글부터 현재 머리글까지 한 열이므로 1을 반환합니다.
=COLUMNS ( $B$1:B$1 )
= 1
순번에 해당하는 결과 머리글을 고릅니다
결과 머리글 범위의 첫 번째 값은 부서코드입니다. 수식을 오른쪽으로 복사하면 열 순번이 2와 3으로 늘어나 입사연도와 일련번호를 차례로 고릅니다.
=INDEX ( $B$1:$D$1, 1, 1 )
= 부서코드
머리글의 시작 위치와 글자 수를 찾습니다
구간표에서 부서코드 행을 찾습니다. 부서코드는 사번의 첫 번째 글자부터 두 글자를 사용하므로 시작 위치와 글자 수로 1과 2를 반환합니다.
=XLOOKUP ( "부서코드", $F$2:$F$4, $G$2:$G$4 )
= 1
=XLOOKUP ( "부서코드", $F$2:$F$4, $H$2:$H$4 )
= 2
찾은 구간만 사번에서 잘라냅니다
HR20211037의 첫 번째 글자부터 두 글자를 가져옵니다. 결과로 부서코드 HR을 반환합니다.
=MID ( $A2, 1, 2 )
= HR

