AI를 활용한 데이터 정리 및 변환 방법

데이터 전문가에게 하루 업무 중 가장 많은 시간을 차지하는 것이 무엇인지 묻는다면, 아마도 깊은 한숨과 함께 "데이터 정리(Data cleaning)"라는 대답을 듣게 될 것입니다. 멋진 대시보드를 만들거나, 가치 있는 비즈니스 인사이트를 도출하거나, 복잡한 재무 모델을 실행하기 위해서는 데이터가 정확하고 일관되며 올바른 서식으로 지정되어 있어야 합니다.

과거에는 가공되지 않은 지저분한 데이터를 사용 가능한 형태로 변환하려면 수작업으로 입력하고, 불필요한 공백을 찾기 위해 모니터를 뚫어지게 쳐다보며, 복잡하게 중첩된 수식과 씨름하는 등 수많은 시간을 보내야 했습니다. 하지만 오늘날 인공지능(AI)은 이러한 환경을 완전히 바꿔 놓았습니다. AI 도구와 스마트 어시스턴트를 활용하면 서식 수정을 자동화하고, 일관성 없는 항목을 표준화하며, 순식간에 분석용 데이터 세트를 준비할 수 있습니다.

이 종합 가이드에서는 가장 골치 아픈 데이터 문제를 AI로 해결하는 방법, 그 바탕이 되는 엑셀 수식, 그리고 즉시 적용할 수 있는 실용적인 워크플로우를 살펴봅니다.

데이터 정리가 분석에 필수적인 이유

데이터 과학에는 가비지 인, 가비지 아웃(GIGO, 쓰레기가 들어가면 쓰레기가 나온다)이라는 황금률이 있습니다. 스프레드시트가 오타, 일치하지 않는 날짜 서식, 중복된 레코드로 가득 차 있다면 어떤 분석을 수행하든 근본적으로 오류가 발생할 수밖에 없습니다. 잘못 찍힌 소수점이나 끝에 붙은 공백 하나가 VLOOKUP 및 MATCH 수식을 망가뜨려 잘못된 계산을 초래하고, 결국 잘못된 비즈니스 결정으로 이어질 수 있습니다.

올바른 데이터 변환은 스프레드시트가 단일 진실 공급원(Single source of truth)의 역할을 하도록 보장합니다. 항목이 표준화되면 피벗 테이블이 카테고리를 올바르게 그룹화하고 차트가 현실을 정확히 반영하며, 엑셀에서의 AI 기반 데이터 분석(AI-Powered Data Analysis in Excel)으로 매끄럽게 전환할 수 있습니다. AI는 단순히 정리된 데이터를 분석하는 데만 도움을 주는 것이 아니라, 애초에 데이터를 깨끗하게 만드는 데 가장 강력한 아군이 되었습니다.

AI로 해결할 수 있는 흔한 데이터 문제들

CRM, 회계 소프트웨어 또는 웹 양식에서 원본 데이터를 내보낼 때 데이터가 완벽한 상태인 경우는 드뭅니다. 데이터 실무자가 매일 직면하는 가장 흔한 서식 문제는 다음과 같습니다:

• 일관성 없는 텍스트 대소문자: 같은 열에 "JOHN DOE", "john doe", "John doe"와 같이 이름이 섞여 있는 경우.
• 앞뒤 공백: 텍스트 앞이나 뒤에 보이지 않는 공백이 있어 조회 수식이 일치하는 값을 찾지 못하는 경우.
• 인쇄할 수 없는 문자: 웹 데이터베이스에서 가져온 이상한 기호나 줄 바꿈.
• 일치하지 않는 날짜 서식: 미국식(MM/DD/YYYY)과 유럽식(DD/MM/YYYY) 날짜 구조가 혼합된 경우.
• 표준화되지 않은 숫자: 대시, 마침표, 괄호가 섞여 있거나 숫자만 있는 등 일관성 없는 전화번호.

AI가 등장하기 전에는 이러한 문제를 해결하려면 텍스트 조작 함수에 대한 백과사전 수준의 깊은 지식이 필요했습니다. 이제는 일상적인 언어로 AI에게 문제를 설명하기만 하면 AI가 이를 해결할 정확한 수학적 논리를 생성해 줍니다.

데이터 정리를 위한 전통적인 수식 접근법

AI를 사용해 해결책을 생성할 때도 텍스트 정리를 구동하는 기본적인 엑셀 함수를 이해하는 것은 중요합니다. AI는 수식을 작성할 때 종종 다음과 같은 핵심 함수에 의존하기 때문입니다:

• TRIM: 단어 사이의 단일 공백을 제외한 텍스트의 모든 공백을 제거합니다. 실수로 입력된 이중 공백이나 후행 공백을 제거하는 데 완벽합니다.
• CLEAN: 텍스트에서 인쇄할 수 없는 모든 문자를 제거합니다. 웹에서 가져온 데이터를 처리할 때 필수적입니다.
• PROPER: 각 단어의 첫 글자를 대문자로, 나머지 모든 글자는 소문자로 변환합니다. 이름과 주소를 정리하는 데 이상적입니다.
• UPPER / LOWER: 텍스트를 모두 대문자 또는 모두 소문자로 변환합니다.

매우 손상된 텍스트 문자열을 수동으로 정리하려면 보통 이러한 함수들을 중첩해서 사용합니다. 예를 들어, A2 셀에 " jOhn sMIth "와 같이 엉망인 이름이 있는 경우 결합된 수식은 다음과 같습니다:

=PROPER(TRIM(CLEAN(A2)))

이 수식은 안쪽에서 바깥쪽으로 작동합니다. 인쇄할 수 없는 문자를 벗겨내고, 불필요한 공백을 제거한 다음, 마지막으로 적절한 대소문자를 적용하여 "John Smith"를 반환합니다.

AI가 데이터 준비의 판도를 바꾸는 방법

TRIM과 PROPER를 중첩하는 것은 어느 정도 관리할 수 있지만, 문자열에서 중간 이름을 추출하거나 이메일 주소에서 도메인 이름만 뽑아내야 할 때는 어떻게 될까요? 수식은 놀라울 정도로 복잡해지며 흔히 FIND, LEFT, RIGHT, MID, LEN 등의 함수가 얽히게 됩니다.

이때 AI가 나섭니다. MID 함수를 놓고 20분 동안 시행착오를 겪는 대신, AI 어시스턴트에게 간단한 명령을 내릴 수 있습니다: "B2 셀의 `@` 기호와 `.com` 사이의 텍스트를 추출하는 엑셀 수식을 작성해 줘."

AI는 즉시 올바른 수식을 반환하여 여러분의 시간과 스트레스를 줄여줍니다. 고급 통합 환경으로 발전함에 따라, 엑셀 코파일럿: 스프레드시트의 미래(Excel Copilot: The Future of Spreadsheets)와 같은 도구는 엑셀 인터페이스 내에서 이러한 AI 명령을 직접 실행하고 데이터 세트의 맥락을 분석하여 필요한 정확한 변환을 제안할 수 있게 해 줄 것입니다.

AI 지원을 통한 날짜 및 숫자 표준화

숫자와 날짜는 엑셀이 지역 설정에 따라 잘못 해석하는 경우가 많아 정리하기 까다롭기로 악명이 높습니다. 예를 들어 "04/05/2024"라는 날짜는 4월 5일일 수도 있고 5월 4일일 수도 있습니다.

전화번호 열이 다양한 서식(예: 5551234567, 555-123-4567, (555) 123 4567)으로 뒤죽박죽 섞여 있다면 데이터베이스의 무결성을 위해 이를 표준화하는 것이 필수적입니다. AI의 도움을 받으면 중첩된 SUBSTITUTE 수식을 작성하여 숫자가 아닌 모든 문자를 제거하고 깔끔하게 서식을 다시 지정할 수 있습니다.

AI에게 전화번호 정리를 요청하면 다음과 같은 수식을 생성할 수 있습니다:

=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "-", ""), "(", ""), ")", ""), " ", "") * 1, "(###) ###-####")

이 수식은 순차적으로 하이픈, 괄호, 공백을 아무것도 없는 값으로 치환하여(사실상 제거하여) 텍스트에 1을 곱해 숫자로 변환한 다음, TEXT 함수: 숫자를 텍스트로 서식 지정하기(TEXT Function: Format Numbers as Text)를 사용해 통일된 (###) ###-#### 형태의 시각적 마스크를 적용합니다.

중복 및 일관성 없는 항목 처리하기

데이터 변환에서 또 다른 주요 골칫거리는 카테고리의 표준화입니다. 사용자들이 "Human Resources", "HR", "H.R.", "Human Res." 등 다양하게 입력한 "부서" 열이 있다고 상상해 보세요. 이렇게 일관성 없는 항목들은 여러분이 만들려는 피벗 테이블을 엉망으로 만들 것입니다.

이 문제를 해결하기 위해 AI를 사용하여 매핑 테이블을 구축할 수 있습니다. 먼저 UNIQUE 함수를 사용해 현재 데이터 세트에 존재하는 모든 변형 값을 추출합니다:

=UNIQUE(C2:C1000)

중복 없는 지저분한 목록을 얻고 나면 이를 표준 값(예: 모든 변형을 "HR"로 매핑)으로 매핑할 수 있습니다. 그런 다음 AI의 도움을 받아 완벽한 XLOOKUP 또는 INDEX와 MATCH 수식을 작성하여 새 열에서 지저분한 데이터를 표준화된 데이터로 바꿀 수 있습니다.

앞으로는 애초에 데이터가 지저분해지는 것을 방지하는 것이 데이터를 정리하는 가장 좋은 방법입니다. AI에게 데이터 유효성 검사: 사용자 입력 제어(Data Validation: Control What Users Can Enter)를 위한 사용자 지정 규칙 생성을 요청하여, 향후 입력 항목을 미리 정의된 드롭다운 목록으로 제한할 수 있습니다.

실전 연습: 원본 고객 목록 변환하기

이 모든 것을 실제 시나리오에 적용해 보겠습니다. 서식이 엉망인 웹 양식에서 고객 리드 목록을 내보냈다고 가정해 봅시다. 목표는 이름을 정리하고, 전화번호를 표준화하며, 이메일 도메인을 추출하여 어떤 회사에서 연락을 했는지 파악하는 것입니다.

원본 이름 (A)	원본 전화번호 (B)	원본 이메일 (C)
jAnE dOe	555-987-6543	[email protected]
john SMITH	(555) 123 4567	[email protected]
alice jones	5551112222	[email protected]

1단계: 이름 정리하기
D열(정리된 이름)에서는 전통적인 텍스트 정리 조합을 사용합니다. AI는 =PROPER(TRIM(A2))를 제안할 것입니다. 이 수식은 " jAnE dOe "를 즉시 "Jane Doe"로 변환합니다.

2단계: 전화번호 표준화하기
E열(정리된 전화번호)에서는 앞서 논의한 중첩된 SUBSTITUTE 및 TEXT 수식을 적용합니다. AI는 패턴을 이해하고 =TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"-","")," ",""),"(",""),")","")*1,"(###) ###-####")를 제공합니다. 이제 모든 전화번호가 동일하게 (555) XXX-XXXX 형태로 표시됩니다.

3단계: 도메인 추출하기
F열(회사 도메인)에서는 "@" 기호 뒤의 텍스트를 가져와야 합니다. 복잡한 계산을 직접 알아내는 대신 AI 프롬프트를 통해 =RIGHT(C2, LEN(C2) - FIND("@", C2))를 생성합니다. 이는 "acmecorp.com"과 "globex.com"을 완벽하게 분리해 냅니다.

정리 워크플로우 자동화하기

새로 내보낸 데이터에서 매주 똑같은 정리 수식을 반복해서 실행하고 있다면, 수식만으로는 가장 효율적인 방법이 아닐 수 있습니다. 반복되는 데이터 변환 작업은 자동화된 워크플로우로 발전시켜야 합니다.

엑셀에 내장된 ETL(추출, 변환, 적재) 도구가 이 작업에 제격입니다. AI를 파워 쿼리: 전문가처럼 데이터 가져오기 및 변환하기(Power Query: Import and Transform Data Like a Pro)와 결합하면 기업 수준의 자동화를 실현할 수 있습니다. AI(예: ChatGPT)를 사용해 사용자 지정 "M 코드"(파워 쿼리의 기반 언어)를 작성하여 복잡한 조건부 서식, 열 피벗 해제, 데이터 세트 병합을 자동화할 수 있습니다. 쿼리가 한 번 구축되면 다음 주의 파일을 정리하는 것은 "새로 고침"을 클릭하는 것만큼 간단해집니다.

AI를 활용한 즉각적인 수식 생성

데이터 정리가 꼭 고통스럽고 시간 소모적인 잡일일 필요는 없습니다. 패턴을 인식하고 TRIM, PROPER, SUBSTITUTE, FIND와 같은 표준 텍스트 함수를 이해하면 성공적으로 스프레드시트를 구성할 수 있습니다.

하지만 현대에는 모든 복잡한 추출이나 조건부 치환을 위한 구문을 외울 필요가 없습니다. "이 셀에서 모든 문자를 제거하고 숫자만 남겨줘"와 같이 구체적인 데이터 문제를 일상적인 언어로 설명하기만 하면, GPTExcel를 사용하여 정확한 수식을 즉시 생성할 수 있습니다. GPTExcel는 여러분의 자연어 요청을 작동하는 엑셀 수식으로 변환해 주어 개인 데이터 정리 어시스턴트 역할을 하므로, 여러분은 데이터를 씻어내는 대신 분석하는 데 더 집중할 수 있습니다.

자주 묻는 질문(FAQ)

수식을 작성하지 않아도 AI가 데이터를 자동으로 정리해 줄 수 있나요?

네, 엑셀에는 빠른 채우기(Flash Fill, Ctrl + E)와 같은 내장 AI 기능이 있습니다. 처음 한두 행의 인접한 열에 수정된 버전의 데이터를 입력하면, 빠른 채우기가 기계 학습을 사용해 패턴을 인식하고 명시적인 수식 없이도 나머지 열을 자동으로 채워줍니다.

AI가 생성한 데이터 정리 수식은 항상 완벽한가요?

매우 정확하긴 하지만, AI 수식은 프롬프트의 명확성에 따라 달라집니다. 데이터 세트에 예상치 못한 국가 코드가 포함된 전화번호와 같이 특이한 극단적 사례(Edge cases)가 있는 경우, 기본적인 AI 생성 수식은 해당 행에서 실패할 수 있습니다. 항상 변환된 데이터를 샘플링하여 확인하고, 이상값을 고려해 프롬프트를 세밀하게 조정하세요.

AI로 데이터를 정리하면 원본 데이터를 덮어쓰게 되나요?

수식은 참조하는 셀을 절대 덮어쓰지 않습니다. 깔끔한 데이터를 위한 새로운 "도우미 열(Helper columns)"을 만드는 것(예: "원본 이름" 열 옆에 "정리된 이름" 열 생성)이 가장 좋은 방법입니다. 결과에 만족한다면, 변환을 확정짓기 위해 정리된 열을 복사하여 원본 데이터 위에 "값"으로 붙여넣기 할 수 있습니다.

철자가 약간 다른 중복 항목을 AI가 찾아줄 수 있나요?

네! 이를 "퍼지 매칭(Fuzzy matching)"이라고 합니다. 기본 엑셀 수식은 퍼지 논리를 처리하는 데 어려움을 겪지만, 파워 쿼리에 내장된 유사 항목 병합(Fuzzy Merge) 기능을 사용하거나, 지저분한 데이터 샘플을 AI 챗봇에 붙여넣고 맞춤법이 틀린 변형들을 하나로 그룹화하는 정확한 매핑 테이블을 작성해 달라고 요청할 수 있습니다.