데이터 정리는 데이터 분석과 계산을 수행하기 위해 반드시 거쳐야 하는 핵심 과정이며, 데이터 준비 및 포맷팅의 첫 번째 단계입니다. 데이터 정리란 불일치, 오류, 원치 않는 서식을 제거하는 작업을 의미합니다. 마이크로소프트 엑셀의 파워 쿼리(Power Query)와 구글 스프레드시트의 REGEX 함수는 모두 고급 데이터 정리에 강력한 도구입니다. 이 글에서는 두 도구를 비교하면서 각각을 활용해 고급 데이터 정리를 수행하는 방법을 소개합니다.
엑셀의 파워 쿼리(Power Query)
파워 쿼리는 엑셀에 내장된 기능으로, 직관적인 인터페이스를 통해 데이터 변환과 정리 작업을 손쉽게 수행할 수 있도록 지원합니다. 복잡한 수식 없이도 데이터를 정리하고 형식을 지정할 수 있으며, 다양한 데이터 소스에서 데이터를 가져올 수 있는 강력한 연결성을 제공합니다. 또한 유연한 기능들을 활용해 복잡한 데이터 정리 작업도 효율적으로 처리할 수 있습니다.
구글 스프레드시트의 REGEX 함수
구글 스프레드시트의 REGEX 함수는 정규 표현식(regular expression)을 사용해 텍스트 데이터 내 패턴을 찾아내므로, 텍스트 항목의 구문 분석, 서식 지정, 유효성 검사에 특히 적합합니다.
- REGEXREPLACE: 정규 표현식과 일치하는 텍스트를 지정한 텍스트로 대체합니다.
- =REGEXREPLACE(text, regular_expression, replacement)
- REGEXMATCH: 텍스트 문자열이 지정된 정규 표현식과 일치하는지 확인합니다.
- =REGEXMATCH(text, regular_expression)
- REGEXEXTRACT: 정규 표현식을 기준으로 텍스트에서 일치하는 부분 문자열을 추출합니다.
- =REGEXEXTRACT(text, regular_expression)
이러한 함수들은 데이터 정리, 패턴 인식, 동적 텍스트 조작에 매우 유용하게 활용됩니다.
이제 잘못된 서식, 불필요한 문자, 오류, 빈칸 등이 섞여 있는 원본 데이터 세트를 예로 들어 보겠습니다. 엑셀의 파워 쿼리와 구글 스프레드시트의 REGEX 함수를 사용해 고급 데이터 정리를 수행하면서 두 도구를 비교해 보겠습니다.
불필요한 문자 제거하기
괄호, 하이픈, 공백 같은 불필요한 문자가 포함된 전화번호 데이터 세트를 정리해 보겠습니다.
파워 쿼리
- 데이터 범위를 선택합니다.
- 데이터 탭으로 이동 >> 테이블/범위에서(From Table/Range)를 선택합니다.
- 파워 쿼리 편집기가 열리면 열을 선택합니다. 여기서는 전화번호 열을 선택했습니다.
- 변환 탭으로 이동 >> 값 바꾸기(Replace Values)를 선택합니다.
- 값 바꾸기 대화 상자가 나타나면:
- 찾을 값 입력란: 제거할 문자((, ), - 등)를 하나씩 입력합니다.
- 바꿀 값 입력란: 비워 둡니다. 다른 값으로 교체하려면 해당 값을 입력하면 됩니다.
- 확인을 클릭합니다.
- 닫기 및 로드(Close & Load)를 선택해 정리된 데이터를 엑셀로 다시 가져옵니다.
구글 스프레드시트 REGEXREPLACE 함수
REGEXREPLACE 함수를 사용해 구글 스프레드시트에서 데이터와 서식을 함께 정리해 보겠습니다. 셀 G2에 아래 수식을 입력합니다.
=ARRAYFORMULA(IF(LEN(REGEXREPLACE(D2:D6, "[^0-9]", ""))=10,
"(" & MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 1, 3) & ") " &
MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 4, 3) & "-" &
MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 7, 4),
"Invalid"))
이 수식은 숫자가 아닌 모든 문자를 제거한 후, 정리된 번호가 정확히 10자리인지 확인합니다. 유효한 경우 (XXX) XXX-XXXX 형식으로 표시하고, 그렇지 않으면 "Invalid"를 반환합니다.
특수 문자 정리하기
다음 수식을 사용하면 불필요한 특수 문자를 한 번에 정리할 수 있습니다.
수식:
=REGEXREPLACE(D2, "[^a-zA-Z0-9]", "")
[^a-zA-Z0-9] 패턴은 문자와 숫자가 아닌 모든 문자를 제거합니다.
정규 표현식에 익숙하다면 REGEXREPLACE 함수를 활용해 단 하나의 수식으로 문자와 숫자를 제외한 모든 문자를 손쉽게 제거할 수 있습니다.
텍스트 대소문자 표준화
데이터 세트에 대소문자가 뒤섞인 텍스트가 있을 수 있습니다. 이 경우 모든 텍스트를 소문자 또는 각 단어의 첫 글자만 대문자인 형태(Title Case)로 변환할 수 있습니다.
파워 쿼리
데이터 범위를 선택한 후 데이터 탭 >> 테이블/범위에서를 선택해 파워 쿼리를 엽니다.
- 변환할 텍스트가 있는 열을 선택합니다.
- 변환 탭으로 이동 >> 단어 첫 글자를 대문자로(Capitalize Each Word)를 선택합니다.
구글 스프레드시트 PROPER 함수
구글 스프레드시트의 REGEXREPLACE는 정규 표현식 패턴이나 대체 문자열 내에서 UPPER, LOWER 함수를 직접 지원하지 않기 때문에, 정규 표현식만으로 각 단어의 첫 글자를 대문자로 바꾸는 것은 불가능합니다. 대신 PROPER, UPPER, LOWER 같은 외부 함수를 활용해야 합니다.
PROPER 함수를 사용하면 각 단어의 첫 글자는 대문자로, 나머지 글자는 소문자로 자동 변환됩니다.
구분 기호로 데이터 분할하기
구분 기호를 기준으로 결합된 텍스트나 이름을 별도의 열로 분할할 수 있습니다. 파워 쿼리와 구글 스프레드시트의 REGEX 함수를 사용해 데이터를 정리해 보겠습니다.
파워 쿼리
데이터 범위를 선택한 후 데이터 탭 >> 테이블/범위에서를 선택해 파워 쿼리를 엽니다.
- 이름이 포함된 열을 선택합니다.
- 홈 탭으로 이동 >> 열 분할(Split Column)에서 구분 기호 기준(By Delimiter)을 선택합니다.
- 구분 기호 기준 열 분할 대화 상자에서:
- 구분 기호 선택 및 입력: 공백(Space)을 선택합니다.
- 확인을 클릭합니다.
- 생성된 열의 이름을 "First Name"(이름)과 "Last Name"(성)으로 변경합니다.
구글 스프레드시트 REGEXEXTRACT 함수
REGEXEXTRACT 함수를 사용하면 구분 기호를 기준으로 데이터를 분할할 수 있습니다. 원하는 셀에 아래 수식을 입력합니다.
=REGEXEXTRACT(E9, "^([^ ]+) (.+)$")
이 수식은 성과 이름을 두 개의 셀로 분리합니다. 필요에 따라 추가 열을 활용하면 됩니다.
파워 쿼리 vs 구글 스프레드시트 REGEX, 언제 무엇을 사용할까?
파워 쿼리는 기초부터 고급 수준까지의 데이터 정리에 가장 유용합니다. 직관적인 인터페이스를 갖추고 있어 대규모의 구조화된 데이터 변환에 이상적이며, 복잡한 데이터 세트 작업, 구조화된 조인(join), 데이터 병합 시에도 뛰어난 효율을 발휘합니다.
반면 구글 스프레드시트의 REGEX 함수는 빠른 텍스트 기반 작업과 개별 셀 내 특정 텍스트 추출에 적합합니다. 정규 표현식 문법에 익숙하다면 REGEX 함수가 특히 큰 도움이 됩니다.
결론
파워 쿼리와 구글 스프레드시트의 REGEX 함수는 상호 보완적인 데이터 정리 도구입니다. 파워 쿼리는 대용량 데이터 세트에 대한 일괄 변환이 필요한 엑셀 사용자에게 이상적이며, 구글 스프레드시트의 REGEX 함수는 소규모 데이터 세트에서 텍스트 기반 작업에 유연성을 제공합니다. 두 도구를 함께 활용하면 더욱 강력하고 효율적인 데이터 정리가 가능합니다! 두 도구를 모두 이해하고 있다면 엑셀이든 구글 스프레드시트든 대부분의 데이터 정리 작업을 손쉽게 처리할 수 있습니다!