2강. 데이터 정제와 가공 요청하기 | Claude로 엑셀 & PPT 업무 자동화 20강 · 입문 — NUGUNA

🎯 학습 목표

이 섹션에서는 이번 강의에서 여러분이 무엇을 얻어갈 수 있는지 먼저 살펴보겠습니다.

이 강을 마치면 다음을 할 수 있습니다.

현실 데이터에서 자주 발생하는 오염 유형(공백, 특수문자, 날짜 형식 불일치, 중복)을 인식할 수 있습니다.
클로드에게 데이터 정제 작업을 구체적으로 요청해 정확한 엑셀 수식을 받을 수 있습니다.
TRIM, CLEAN, SUBSTITUTE, TEXT, COUNTIF 등 정제에 필수적인 수식의 원리를 이해하고 응용할 수 있습니다.
텍스트를 분리하거나 합치는 수식을 상황에 맞게 요청하고 적용할 수 있습니다.

데이터 정제는 엑셀 업무에서 가장 시간이 많이 걸리는 작업 중 하나입니다. 클로드를 활용하면 어떤 오염 패턴에도 빠르게 수식을 만들어 적용할 수 있습니다.

💡 현실 데이터는 왜 항상 지저분한가

이 섹션에서는 업무 현장에서 데이터가 지저분해지는 원인과, 정제하지 않으면 어떤 문제가 생기는지 살펴보겠습니다.

실무에서 다루는 데이터는 교과서처럼 깔끔하지 않습니다. ERP 시스템에서 내보낸 CSV, 여러 담당자가 입력한 구글 스프레드시트, 외부 기관에서 받은 엑셀 파일—이 데이터들에는 의도치 않은 오염이 가득합니다. 가장 흔한 문제는 다음과 같습니다.

첫째, 불필요한 공백입니다. 셀에 "홍길동 " 처럼 이름 뒤에 보이지 않는 공백이 붙어 있는 경우입니다. 눈에는 보이지 않지만, VLOOKUP 같은 함수로 데이터를 조회할 때 "홍길동"과 "홍길동 "은 다른 값으로 인식되어 조회에 실패합니다.

둘째, 날짜 형식 불일치입니다. 같은 날짜를 누군가는 "2024-01-15"로, 누군가는 "2024.01.15"로, 또 다른 누군가는 "2024년 1월 15일"로 입력합니다. 이렇게 되면 날짜 정렬이나 기간 계산이 제대로 되지 않습니다.

셋째, 중복 데이터입니다. 거래처 목록, 고객 DB 등에서 같은 데이터가 두 번 이상 입력된 경우입니다. 중복이 있으면 집계가 왜곡되고 보고서의 신뢰성이 떨어집니다.

넷째, 붙어 있어야 할 데이터가 분리되거나, 분리되어야 할 데이터가 합쳐진 경우입니다. "홍길동/영업팀"처럼 이름과 부서가 한 셀에 들어와 있거나, 반대로 성과 이름이 다른 셀에 분리되어 있어 합쳐야 하는 경우입니다.

이런 오염 데이터를 정제하지 않고 분석하면 잘못된 결론이 나옵니다. 예를 들어 중복이 있는 채로 매출을 집계하면 실제보다 높게 나오고, 날짜 형식이 섞인 채로 기간별 필터를 걸면 일부 데이터가 빠집니다. 클로드를 활용하면 이런 문제를 빠르게 해결하는 수식을 얻을 수 있습니다.

🧹 공백과 특수문자 제거하기

이 섹션에서는 데이터에 섞인 불필요한 공백과 특수문자를 제거하는 수식을 클로드에게 어떻게 요청하는지 살펴보겠습니다.

공백 문제는 크게 세 가지로 나뉩니다. ①앞뒤에 붙은 공백(선행·후행 공백), ②단어 사이의 과도한 공백(예: "홍 길동"), ③줄바꿈 문자나 탭 같은 눈에 보이지 않는 특수 공백입니다. 각각 다른 방식으로 처리해야 합니다.

클로드에게 공백 제거를 요청하는 방법

가장 효과적인 요청 방식은 어떤 종류의 공백인지를 설명하는 것입니다. 예를 들어 이렇게 요청할 수 있습니다.

"A열에 고객 이름이 입력되어 있는데, 앞뒤에 불필요한 공백이 붙어 있고 중간에도 공백이 2개 이상 들어간 경우가 있습니다. 이를 정리해서 B열에 깔끔한 이름만 나오게 하는 수식을 만들어주세요."

클로드는 이 요청에 =TRIM(A2) 수식을 제안합니다. TRIM은 텍스트의 앞뒤 공백을 제거하고, 단어 사이의 공백도 하나만 남기는 엑셀 내장 함수입니다. 사용법은 간단합니다. B2 셀에 =TRIM(A2)를 입력하고 아래 행으로 복사하면, A열 전체의 공백이 정리된 결과가 B열에 나타납니다.

그런데 TRIM으로도 제거되지 않는 특수 문자가 있습니다. 시스템에서 자동 생성된 파일이나 웹에서 복사한 데이터에는 출력 불가능한 제어 문자(ASCII 코드 0~31 영역)가 숨어 있는 경우가 있습니다. 이럴 때는 CLEAN 함수를 함께 사용합니다. 클로드에게 "TRIM으로 제거되지 않는 특수 문자도 있는 것 같아요"라고 추가로 말하면 =TRIM(CLEAN(A2))를 제안해 줍니다. CLEAN은 TRIM이 처리하지 못하는 제어 문자를 먼저 제거하고, TRIM이 남은 공백을 정리하는 식으로 두 함수를 조합해 사용합니다.

특정 특수문자(예: 하이픈, 괄호, 슬래시)만 선택적으로 제거하고 싶을 때는 SUBSTITUTE 함수를 활용합니다. 예를 들어 전화번호에서 하이픈을 제거하고 싶다면, 클로드에게 "B열 전화번호에서 하이픈(-)을 모두 제거해서 숫자만 남기고 싶어요"라고 말하면 =SUBSTITUTE(B2,"-","")를 제안합니다. 하이픈을 빈 문자열로 바꾸는 원리입니다. 하이픈과 괄호를 동시에 제거하고 싶다면 SUBSTITUTE를 중첩해서 사용합니다.

정제 후에는 원본 데이터를 남겨두고, 정제된 결과를 새 열에 넣은 뒤 검토하는 것이 좋습니다. 직접 원본을 덮어쓰면 수식을 잘못 작성했을 때 되돌리기 어렵습니다. 검토 후 이상이 없으면 정제된 열을 복사해 "값으로 붙여넣기"(Ctrl+Shift+V 또는 마우스 우클릭 → 값 붙여넣기)를 한 뒤 수식 열을 삭제하면 됩니다.

📅 날짜 형식 통일하기

이 섹션에서는 다양하게 입력된 날짜를 하나의 형식으로 통일하는 방법과 클로드 요청 패턴을 살펴보겠습니다.

날짜 형식 문제는 엑셀에서 특히 까다롭습니다. 엑셀이 날짜를 인식하는 방식과 사람이 입력하는 방식이 다르기 때문입니다. 엑셀 내부에서 날짜는 숫자(시리얼 번호)로 저장됩니다. 예를 들어 2024년 1월 15일은 내부적으로 45306이라는 숫자로 저장되고, 화면에는 날짜 형식으로 표시됩니다. 문제는 "2024.01.15"나 "2024년 1월 15일"처럼 텍스트로 입력된 경우입니다. 이 경우 엑셀은 그것을 날짜가 아닌 텍스트로 인식하므로, 날짜 계산이나 정렬이 작동하지 않습니다.

텍스트 날짜를 엑셀 날짜로 변환하기

클로드에게 이렇게 요청할 수 있습니다.

"A열에 날짜가 '2024.01.15' 형식으로 텍스트로 입력되어 있습니다. 이것을 엑셀이 날짜로 인식하도록 변환하는 수식을 만들어주세요."

클로드는 =DATEVALUE(SUBSTITUTE(A2,".","-"))를 제안할 수 있습니다. SUBSTITUTE로 점(.)을 하이픈(-)으로 바꾼 뒤, DATEVALUE로 텍스트를 날짜 값으로 변환하는 방식입니다. 변환 후에는 셀 서식을 날짜 형식으로 바꿔줘야 날짜처럼 보입니다. 클로드에게 "변환 후에 셀 서식도 어떻게 해야 하나요?"라고 추가로 물어보면 셀 서식 설정 방법도 안내해 줍니다.

이미 엑셀 날짜인 값을 특정 텍스트 형식으로 변환하기

반대로 엑셀의 날짜 값을 "2024-01-15" 형식의 텍스트로 바꿔야 하는 경우도 있습니다. 예를 들어 보고서나 코드에서 특정 형식의 날짜 문자열이 필요할 때입니다. 이때는 TEXT 함수를 사용합니다.

"B열에 엑셀 날짜 값이 있습니다. 이것을 'YYYY-MM-DD' 형식의 텍스트로 변환하는 수식을 만들어주세요."

클로드는 =TEXT(B2,"YYYY-MM-DD")를 제안합니다. TEXT 함수의 두 번째 인수는 원하는 날짜 형식입니다. "YYYY"는 연도 4자리, "MM"은 월 2자리, "DD"는 일 2자리를 의미합니다. "YYYY년 MM월 DD일" 형식으로 바꾸고 싶다면 =TEXT(B2,"YYYY년 MM월 DD일")처럼 변경하면 됩니다.

날짜 형식 작업에서 중요한 점은 요청할 때 현재 데이터가 텍스트인지, 이미 엑셀 날짜 값인지를 구분해서 알려주는 것입니다. 클로드에게 "A열 셀을 클릭하면 수식 입력창에 날짜가 숫자로 보입니다"(엑셀 날짜 값) 또는 "A열 셀을 클릭하면 입력한 그대로 '2024.01.15'가 보입니다"(텍스트) 중 어느 경우인지 알려주면 훨씬 정확한 수식을 받을 수 있습니다.

🔁 중복 데이터 찾기와 처리하기

이 섹션에서는 데이터에 섞인 중복을 효율적으로 찾아내고 처리하는 방법을 살펴보겠습니다.

중복 처리는 두 단계로 나뉩니다. ①중복 여부를 확인하는 단계, ②중복을 제거하는 단계입니다.

COUNTIF로 중복 여부 확인하기

클로드에게 이렇게 요청합니다.

"A열에 거래처 이름이 있습니다. 각 이름이 몇 번 중복되었는지 확인하고, 2번 이상 등장하는 행에 표시하는 수식을 만들어주세요."

클로드는 =COUNTIF($A$2:$A$100,A2)를 제안합니다. COUNTIF는 범위($A$2:$A$100) 안에서 기준값(A2)과 일치하는 셀 개수를 셉니다. 이 수식을 B열에 넣으면 각 행의 이름이 전체 범위에서 몇 번 등장하는지 숫자로 표시됩니다. 1이면 유일한 값, 2 이상이면 중복입니다.

중복 행에 색상으로 표시하고 싶다면, 조건부 서식과 COUNTIF를 결합하면 됩니다. 클로드에게 "중복인 셀을 빨간색으로 자동 표시하는 조건부 서식 설정 방법을 알려주세요"라고 요청하면, 조건부 서식의 수식 입력칸에 넣을 값과 설정 단계를 알려줍니다.

엑셀 내장 기능으로 중복 제거하기

중복을 확인한 뒤 제거하는 가장 간단한 방법은 엑셀의 데이터 → 중복 항목 제거 기능입니다. 클로드에게 "확인된 중복을 어떻게 제거하면 되나요?"라고 물어보면 이 기능을 단계별로 안내해 줍니다.

다만 이 내장 기능에는 주의사항이 있습니다. 중복 항목 제거는 첫 번째 등장하는 행을 남기고 나머지를 삭제합니다. 따라서 중요한 데이터를 무분별하게 삭제하지 않으려면, 실행 전에 반드시 원본을 백업하거나 별도 시트에 복사해두세요. 클로드에게 "중복 제거 전에 어떤 점을 주의해야 하나요?"라고 물어보면 이런 주의사항도 함께 알려줍니다.

특정 열 기준으로만 중복을 판단해야 하는 경우도 있습니다. 예를 들어 주문 번호가 같아도 상품 종류가 다르면 중복이 아닌 경우입니다. 이럴 때 클로드에게 "주문 번호(A열)와 상품 코드(B열)가 모두 같은 경우만 중복으로 처리하는 방법을 알려주세요"처럼 복합 조건을 설명하면 더 정교한 처리 방법을 안내받을 수 있습니다.

✂️ 텍스트 분리와 합치기

이 섹션에서는 한 셀에 합쳐진 텍스트를 나누거나, 여러 셀의 텍스트를 하나로 합치는 수식 요청 방법을 살펴보겠습니다.

텍스트 분리·합치기는 실무에서 매우 자주 마주치는 작업입니다. "홍길동 (영업팀)"을 이름과 팀명으로 나눠야 한다거나, 반대로 성(姓)과 이름이 다른 열에 있어 하나로 합쳐야 하는 경우입니다.

텍스트 합치기

가장 간단한 텍스트 합치기는 & 연산자를 사용하는 방법입니다. A열에 성, B열에 이름이 있다면 =A2&B2로 합칩니다. 두 값 사이에 공백을 넣고 싶다면 =A2&" "&B2처럼 작성합니다.

"A열에 이름, B열에 부서, C열에 직급이 있습니다. 이 세 값을 '이름(부서/직급)' 형식으로 합치는 수식을 만들어주세요."

클로드는 =A2&"("&B2&"/"&C2&")"를 제안합니다. 이처럼 고정 텍스트(괄호, 슬래시)와 셀 값을 &로 이어 붙이면 됩니다. Excel 2016 이상에서는 구분자를 지정해 여러 값을 한 번에 합치는 TEXTJOIN 함수도 사용할 수 있습니다. 클로드에게 사용 중인 엑셀 버전을 알려주면 버전에 맞는 함수를 제안합니다.

텍스트 분리하기

텍스트 분리는 합치기보다 수식이 복잡해집니다. 구분자(공백, 슬래시, 쉼표 등)의 위치를 찾아 그 앞뒤로 텍스트를 잘라야 하기 때문입니다.

"A열에 '홍길동/영업팀' 형식으로 이름과 팀명이 슬래시(/)로 구분되어 있습니다. 슬래시 앞은 B열에, 슬래시 뒤는 C열에 넣는 수식을 각각 만들어주세요."

클로드는 슬래시 앞 부분(이름)은 =LEFT(A2,FIND("/",A2)-1), 슬래시 뒤 부분(팀명)은 =MID(A2,FIND("/",A2)+1,LEN(A2))를 제안합니다. 이 수식들의 원리를 간단히 설명하면, FIND는 슬래시(/)가 몇 번째 위치에 있는지 찾아주고, LEFT는 그 위치 앞까지의 문자를 잘라내며, MID는 그 위치 다음부터 끝까지의 문자를 잘라냅니다.

Microsoft 365 구독자이거나 Excel 2021 이상 버전을 사용한다면 TEXTSPLIT 함수를 사용할 수 있습니다. TEXTSPLIT은 구분자를 기준으로 텍스트를 한 번에 여러 셀로 분리해주는 최신 함수입니다. 클로드에게 "사용 중인 버전은 Microsoft 365입니다"라고 알려주면 TEXTSPLIT을 활용한 더 간단한 수식을 제안해 줄 것입니다.

분리 작업 후에는 결과를 꼭 확인하세요. 특히 이름 중에 슬래시가 2개 이상 있는 경우나, 슬래시가 없는 예외 케이스가 있으면 수식이 오류를 반환합니다. 클로드에게 "슬래시가 없는 행도 있을 수 있는데, 이 경우에도 오류 없이 처리하게 하려면 어떻게 해야 하나요?"라고 물어보면 IFERROR로 감싸는 방법을 알려줍니다.

⚠️ 데이터 정제 시 자주 하는 실수

이 섹션에서는 데이터 정제 과정에서 주의해야 할 실수와 함정을 살펴보겠습니다.

실수 1 — 원본 데이터를 바로 덮어쓰기

정제 수식을 원본 데이터가 있는 열에 직접 적용하면 안 됩니다. 수식이 잘못되었을 때 되돌릴 방법이 없어집니다. 반드시 빈 열에 결과를 먼저 만들고, 검토 후 이상이 없으면 "값으로 붙여넣기"로 교체하세요. 클로드에게 "정제 후 원본 열에 덮어쓰는 방법을 알려줘"라고 요청하기보다는 "새 열에 정제 결과를 만드는 방법"을 요청하는 것이 안전합니다.

실수 2 — 클로드가 제안한 수식을 검증 없이 사용하기

클로드가 제안한 수식이 모든 데이터에 완벽하게 작동하지 않을 수 있습니다. 특히 예외 케이스(공백이 없는 행, 날짜 형식이 다른 행, 구분자가 없는 행)에서 오류가 발생할 수 있습니다. 수식을 적용하기 전에 10~20개 샘플 행에 먼저 테스트하고, 오류(#VALUE!, #N/A 등)가 없는지 확인하세요. 오류가 있으면 클로드에게 "이런 오류가 발생했어요. 어떻게 수정하면 되나요?"라고 바로 물어보면 됩니다.

실수 3 — 오염 유형을 정확히 설명하지 않음

클로드에게 "데이터가 지저분해요, 정리해줘"처럼 막연하게 요청하면 어떤 종류의 오염인지 클로드가 파악할 수 없습니다. 샘플 데이터를 붙여넣거나, 오염 유형을 구체적으로 설명(앞에 공백이 있다, 특정 문자가 섞여 있다, 날짜 형식이 X처럼 들어와 있다)해야 정확한 수식을 받을 수 있습니다.

실수 4 — 수식 결과와 원본 수식을 함께 유지하지 않음

정제된 결과를 최종본으로 사용하려면 수식을 "값으로 붙여넣기"해서 고정해야 합니다. 수식 상태로 두면 원본 데이터가 변경될 때 결과도 달라지고, 파일을 다른 곳으로 이동하면 참조가 깨질 수 있습니다. 최종 결과를 확정한 뒤에는 복사 → 값으로 붙여넣기 순서를 꼭 지키세요.

실수 5 — 숫자처럼 생긴 텍스트와 실제 숫자 혼동

ERP 시스템에서 내보낸 파일에서는 숫자가 텍스트 형식으로 저장되어 있는 경우가 많습니다. 셀 왼쪽 위에 작은 녹색 삼각형이 표시된다면 텍스트 형식 숫자일 가능성이 큽니다. 이 상태에서는 SUM이나 AVERAGE 같은 함수가 해당 값을 무시합니다. 클로드에게 "숫자처럼 생긴 텍스트를 진짜 숫자로 바꾸는 방법을 알려줘"라고 요청하면 해결 방법을 알려줍니다.

📝 핵심 요약 + 다음 강 예고

이 강에서 배운 핵심 내용을 정리합니다.

정제 작업	주요 수식	클로드 요청 포인트
앞뒤 공백 제거	TRIM	공백 위치(앞뒤/중간) 명시
제어 문자 제거	CLEAN + TRIM 조합	TRIM으로 해결 안 될 때 언급
특정 문자 제거	SUBSTITUTE	제거할 문자를 정확히 명시
텍스트 → 날짜	DATEVALUE + SUBSTITUTE	현재 날짜 형식 정확히 설명
날짜 → 텍스트	TEXT	원하는 출력 형식 명시
중복 확인	COUNTIF	중복 기준 열과 범위 설명
텍스트 합치기	& 연산자, TEXTJOIN	구분자와 조합 형식 설명
텍스트 분리	LEFT/MID + FIND/LEN	구분자 종류와 위치 설명

다음 강(3강)에서는 VLOOKUP, INDEX/MATCH, 배열 수식처럼 초보자에게 어렵게 느껴지는 복잡한 수식을 클로드와 함께 빠르게 만드는 방법을 배웁니다. "수식이 너무 복잡해서 못 쓰겠다"는 고민을 클로드로 해결하는 방법을 알아보겠습니다.

데이터 정제와 가공 요청하기