엑셀 실무 필수 기법: 데이터 합치기와 분리 완벽 가이드
엑셀로 업무를 하다 보면 데이터를 합치거나 분리해야 하는 상황이 자주 발생합니다. 이름과 성을 합치거나, 주소를 구분해서 나누거나, 여러 열의 데이터를 하나로 통합하는 등의 작업은 일상적입니다. 오늘은 실무에서 가장 많이 사용되는 데이터 합치기와 분리 방법들을 체계적으로 정리해보겠습니다.
데이터 합치기 방법들
1. 연결 연산자(&) 사용하기
가장 기본적이면서도 직관적인 방법입니다.
=A1&B1
=A1&" "&B1 // 공백 추가
=A1&"-"&B1 // 하이픈으로 연결사용 예시:
- 성(A1: "김")과 이름(B1: "철수")을 합치기:
=A1&B1→ "김철수" - 공백 포함해서 합치기:
=A1&" "&B1→ "김 철수"
2. CONCATENATE 함수
여러 셀의 값을 합칠 때 사용하는 전용 함수입니다.
=CONCATENATE(A1,B1,C1)
=CONCATENATE(A1," ",B1," ",C1) // 공백 포함장점: 명확한 함수명으로 가독성이 좋음
단점: & 연산자보다 입력이 번거로움
3. CONCAT 함수 (엑셀 2016 이후)
CONCATENATE의 개선된 버전으로, 범위 지정이 가능합니다.
=CONCAT(A1:C1)
=CONCAT(A1," - ",B1," - ",C1)4. TEXTJOIN 함수 (엑셀 2016 이후)
실무에서 가장 유용한 함수 중 하나입니다.
=TEXTJOIN(구분자, 빈셀무시여부, 텍스트1, 텍스트2, ...)
=TEXTJOIN(" ",TRUE,A1:E1) // 공백으로 연결, 빈 셀 무시
=TEXTJOIN(",",FALSE,A1:A5) // 쉼표로 연결, 빈 셀 포함활용 예시:
- 여러 부서명을 쉼표로 연결:
=TEXTJOIN(",",TRUE,A1:A10) - 이름을 하이픈으로 연결:
=TEXTJOIN("-",TRUE,B2:D2)
5. 셀 서식을 이용한 합치기
숫자 데이터를 특정 형태로 합칠 때 사용자 지정 서식을 활용할 수 있습니다.
예시: 년-월-일 형태로 표시
- 셀 서식 → 사용자 지정 →
YYYY"-"MM"-"DD
데이터 분리 방법들
1. 구분 기호로 분리 (텍스트 나누기)
엑셀 기본 기능 활용:
- 데이터 선택 → 데이터 탭 → 텍스트 나누기
- 구분 기호 지정 (쉼표, 공백, 탭 등)
- 열 너비 조정 후 완료
장점: 대량 데이터 한 번에 처리 가능 단점: 함수가 아니므로 자동 업데이트 안됨
2. LEFT, RIGHT, MID 함수 활용
특정 위치의 문자를 추출하는 함수들입니다.
=LEFT(A1,3) // 왼쪽에서 3글자
=RIGHT(A1,2) // 오른쪽에서 2글자
=MID(A1,2,4) // 2번째부터 4글자실무 예시:
- 사원번호 "EMP001"에서 번호만 추출:
=RIGHT(A1,3)→ "001" - 휴대폰번호 "010-1234-5678"에서 앞자리:
=LEFT(A1,3)→ "010"
3. FIND/SEARCH와 조합한 동적 분리
구분자 위치를 찾아서 동적으로 분리하는 방법입니다.
// 첫 번째 공백 앞의 문자 추출
=LEFT(A1,FIND(" ",A1)-1)
// 첫 번째 공백 뒤의 문자 추출
=MID(A1,FIND(" ",A1)+1,LEN(A1))
// 마지막 공백 뒤의 문자 추출
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))4. SPLIT 함수 (Office 365/Excel 2021)
최신 버전에서 가장 강력한 분리 함수:
=SPLIT(A1," ") // 공백으로 분리
=SPLIT(A1,",") // 쉼표로 분리
=SPLIT(A1,"-",2) // 하이픈으로 분리, 2개 열로만5. TEXTSPLIT 함수 (최신 Office 365)
SPLIT보다 더 유연한 분리가 가능합니다.
=TEXTSPLIT(A1," ") // 공백으로 분리
=TEXTSPLIT(A1,{" ","-"}) // 공백 또는 하이픈으로 분리실무 활용 팁과 주의사항
합치기 실무 팁
- 빈 셀 처리: TEXTJOIN의 빈셀무시 옵션 적극 활용
- 조건부 합치기: IF 함수와 조합
=IF(B1<>"",A1&" "&B1,A1) // B1이 비어있지 않으면 공백 추가- 대량 데이터: Flash Fill 기능도 고려 (데이터 패턴 자동 인식)
분리 실무 팁
- 일관성 확인: 분리 전 데이터 형태의 일관성 점검
- 예외 처리: IFERROR 함수로 오류 방지
=IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)- 성능 고려: 대량 데이터는 텍스트 나누기 기능이 더 빠름
주의사항
- 데이터 백업: 원본 데이터는 항상 보존
- 버전 확인: 사용하는 엑셀 버전에서 지원하는 함수 확인
- 문자 인코딩: 특수문자나 다국어 처리 시 주의
- 자동 업데이트: 함수 방식은 원본 변경 시 자동 반영, 기능 방식은 수동 재실행 필요
상황별 최적 방법 추천
| 상황 | 합치기 | 분리 |
|---|---|---|
| 소량 데이터 | & 연산자 | LEFT/RIGHT/MID |
| 대량 데이터 | TEXTJOIN | 텍스트 나누기 |
| 복잡한 조건 | IF + TEXTJOIN | TEXTSPLIT |
| 동적 처리 | TEXTJOIN | FIND + MID |
| 최신 버전 | TEXTJOIN | SPLIT/TEXTSPLIT |
마무리
데이터 합치기와 분리는 엑셀 실무의 핵심 기법입니다. 상황에 맞는 적절한 방법을 선택하여 효율적으로 작업하시기 바랍니다. 특히 TEXTJOIN과 SPLIT/TEXTSPLIT 함수는 최신 엑셀에서 매우 강력한 도구이므로, 가능하다면 이 함수들을 우선적으로 활용하시길 추천드립니다.
실무에서는 단순한 기법도 응용하면 강력한 도구가 됩니다. 다양한 상황에서 연습해보시고, 본인만의 노하우를 쌓아가시기 바랍니다!
0 댓글