메뉴
실무 위키›응용 공식›사번을 부서코드, 입사연도, 일련번호로 나누는 공식

사번을 부서코드, 입사연도, 일련번호로 나누는 공식

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

고정 길이 사번을 부서코드, 입사연도, 일련번호로 나눕니다.

예제파일 다운로드
사번을 부서코드, 입사연도, 일련번호로 나누는 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=MID ( 사번, XLOOKUP ( INDEX ( 결과 머리글 범위, 1, COLUMNS ( $B$1:B$1 ) ), 구간 항목 범위, 시작 위치 범위 ), XLOOKUP ( INDEX ( 결과 머리글 범위, 1, COLUMNS ( $B$1:B$1 ) ), 구간 항목 범위, 글자 수 범위 ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
사번필수부서코드, 입사연도, 일련번호의 순서와 길이가 일정한 사번이 적힌 셀입니다.
결과 머리글 범위필수사번에서 나눌 항목 이름이 왼쪽부터 적혀 있고 구간 항목 범위의 이름과 정확히 같은 머리글 범위입니다.
구간 항목 범위필수부서코드, 입사연도, 일련번호처럼 사번에서 나눌 항목 이름이 적힌 범위입니다.
시작 위치 범위필수각 항목의 첫 글자가 사번의 몇 번째에 있는지 적힌 범위입니다. 첫 글자의 위치는 1입니다.
글자 수 범위필수각 항목에서 잘라낼 글자 수가 적혀 있고 시작 위치 범위와 행 순서가 같은 범위입니다.
B2=MID($A2,XLOOKUP(INDEX($B$1:$D$1,1,COLUMNS($B$1:B$1)),$F$2:$F$4,$G$2:$G$4),XLOOKUP(INDEX($B$1:$D$1,1,COLUMNS($B$1:B$1)),$F$2:$F$4,$H$2:$H$4))
A
B
C
D
E
F
G
H
I
1
사번
부서코드
입사연도
일련번호
구간 항목
시작 위치
글자 수
2
HR20211037
HR
2021
1037
부서코드
1
2
3
IT20191224
IT
2019
1224
입사연도
3
4
4
SA20232008
SA
2023
2008
일련번호
7
4
5
FN20201056
FN
2020
1056
6
MK20241111
MK
2024
1111
7
모든 사번의 구간 순서와 길이가 같아야 합니다. B2 셀의 수식을 D열까지 오른쪽으로 복사한 뒤 아래로 복사하면 각 머리글에 맞는 구간을 나눌 수 있습니다.
이 공식이 사용하는 함수
MID 함수 XLOOKUP 함수 INDEX 함수 COLUMNS 함수

동작 원리

COLUMNS 함수와 INDEX 함수가 현재 결과 머리글을 고르고, XLOOKUP 함수가 시작 위치와 글자 수를 찾으면 MID 함수가 사번에서 해당 구간을 잘라냅니다.

예제 시트에서 HR20211037의 부서코드를 나누는 B2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

현재 결과 열의 순번을 구합니다

B열은 결과 머리글 범위의 첫 번째 열입니다. B2 셀에서는 첫 머리글부터 현재 머리글까지 한 열이므로 1을 반환합니다.

=COLUMNS ( $B$1:B$1 )
= 1
2

순번에 해당하는 결과 머리글을 고릅니다

결과 머리글 범위의 첫 번째 값은 부서코드입니다. 수식을 오른쪽으로 복사하면 열 순번이 2와 3으로 늘어나 입사연도와 일련번호를 차례로 고릅니다.

=INDEX ( $B$1:$D$1, 1, 1 )
= 부서코드
3

머리글의 시작 위치와 글자 수를 찾습니다

구간표에서 부서코드 행을 찾습니다. 부서코드는 사번의 첫 번째 글자부터 두 글자를 사용하므로 시작 위치와 글자 수로 1과 2를 반환합니다.

=XLOOKUP ( "부서코드", $F$2:$F$4, $G$2:$G$4 )
= 1
=XLOOKUP ( "부서코드", $F$2:$F$4, $H$2:$H$4 )
= 2
4

찾은 구간만 사번에서 잘라냅니다

HR20211037의 첫 번째 글자부터 두 글자를 가져옵니다. 결과로 부서코드 HR을 반환합니다.

=MID ( $A2, 1, 2 )
= HR
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료