전문가 가이드:고급 데이터 정리를 위한 파워 쿼리와 Google 스프레드시트 REGEX 비교

데이터 정리는 데이터 분석 및 계산을 수행하는 데 중요하며 데이터 준비 및 형식화의 첫 번째 단계입니다. 데이터 정리는 불일치, 오류 및 원치 않는 형식을 제거하는 것을 의미합니다. Microsoft Excel의 파워 쿼리와 Google Sheets의 REGEX 기능은 모두 고급 정리에 강력합니다. 고급 데이터 정리를 위해 파워 쿼리와 Google 스프레드시트의 REGEX 기능을 사용하는 방법을 비교하면서 보여드리겠습니다.

Excel의 파워 쿼리

파워 쿼리는 데이터 변환 및 데이터 정리를 위한 사용자 친화적인 인터페이스를 제공하는 기본 제공 Excel 기능입니다. 복잡한 수식 없이 데이터를 정리하고 형식을 지정합니다. 파워 쿼리는 다양한 원본에서 데이터를 가져오고 강력한 데이터 연결을 제공합니다. 복잡한 데이터 정리 작업을 수행할 수 있는 유연한 기능을 갖추고 있습니다.

Google 스프레드시트의 REGEX 기능

Google 스프레드시트의 REGEX 함수는 정규 표현식을 사용하여 텍스트 데이터 내의 패턴을 찾기 때문에 텍스트 항목의 구문 분석, 서식 지정, 유효성 검사에 이상적입니다.

1. REGEXREPLACE: 정규식과 일치하는 텍스트를 지정된 텍스트로 바꿉니다.
   • =REGEXREPLACE(텍스트, 정규 표현식, 교체)
2. REGEXMATCH: 텍스트 문자열이 지정된 정규식과 일치하는지 확인합니다.
   • =REGEXMATCH(텍스트, 정규 표현식)
3. REGEXEXTRACT: 정규식을 기반으로 텍스트에서 일치하는 하위 문자열을 추출합니다.
   • =REGEXEXTRACT(텍스트, 정규 표현식)

이러한 기능은 데이터 정리, 패턴 인식 및 동적 텍스트 조작에 유용합니다.

잘못된 형식, 원치 않는 문자, 오류, 공백 등이 포함된 원시 데이터세트를 고려해 보겠습니다. Excel의 파워 쿼리와 Google Sheets의 REGEX 기능을 사용하여 서로 비교하면서 고급 데이터 정리 방법을 보여드리겠습니다.

원치 않는 문자 제거

괄호, 대시, 공백 등 원하지 않는 문자가 포함된 전화번호 데이터세트를 정리하세요.

파워 쿼리

• 데이터 범위를 선택하세요.
• 데이터로 이동 탭>> 표/범위에서 선택 .
• 파워 쿼리 편집기가 나타납니다. 열을 선택합니다. 전화번호를 선택했습니다. 열.
• 변환으로 이동 탭>> 값 바꾸기 선택 .
• 값 바꾸기 대화 상자가 나타납니다.
  • 찾을 값 상자:원하지 않는 문자((, ), -, )를 개별적으로 입력하세요.
  • 바꾸기에서 상자:비워두거나 대체할 값이 있는 경우 해당 값을 삽입할 수 있습니다.
  • 확인을 클릭하세요. .

• 닫기 및 로드를 선택합니다. 정리된 데이터를 Excel로 다시 가져옵니다.

Google 스프레드시트의 REGEXREPLACE 함수

REGREPLACE 함수를 사용하여 Google 스프레드시트의 서식과 함께 데이터를 정리해 보겠습니다. 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 형식으로 지정하세요.; 그렇지 않으면 "잘못됨"을 반환합니다.

Google 스프레드시트의 REGREPLACE 기능

다음 공식을 사용하여 불필요한 특수 문자를 지울 수 있습니다.

수식:

=REGEXREPLACE(D2, "[^a-zA-Z0-9]", "")

이 [^a-zA-Z0-9] 패턴은 문자나 숫자가 아닌 모든 문자를 제거합니다.

정규 표현식에 익숙하다면 REGREPLACE 함수를 사용하면 단일 수식 내에서 문자나 숫자를 제외한 모든 문자를 제거할 수 있습니다.

텍스트 대소문자 표준화

데이터 세트에는 혼합 텍스트가 포함될 수 있습니다. 모든 텍스트 항목을 소문자 또는 제목 케이스로 변환할 수 있습니다.

파워 쿼리

데이터 범위를 선택하고 데이터 로 이동하여 파워 쿼리를 엽니다. 탭>> 표/범위에서 선택 .

• 변형할 텍스트가 있는 열을 선택하세요.
• 변환으로 이동 탭>> 각 단어를 대문자로 표시를 선택합니다. .

Google 스프레드시트 적절한 기능

Google 스프레드시트의 REGEXREPLACE는 정규식 패턴이나 교체 내에서 직접 UPPER 및 LOWER 기능을 지원하지 않으므로 정규식만으로 각 단어를 대문자로 표시할 수 있는 방법이 없습니다. PROPER, UPPER, LOWER와 같은 외부 기능을 사용할 수 있습니다.

이 공식을 사용하면 각 단어가 대문자로 표시되고 첫 글자가 아닌 문자는 모두 소문자로 변환됩니다.

구분자로 데이터 분할

병합된 텍스트나 이름을 구분 기호를 사용하여 별도의 열로 분할할 수 있습니다. 파워 쿼리와 Google Sheets의 REGEX 기능을 사용하여 데이터를 정리해 보겠습니다.

파워 쿼리

데이터 범위를 선택하고 데이터 로 이동하여 파워 쿼리를 엽니다. 탭>> 표/범위에서 선택 .

• 이름이 포함된 열을 선택하세요.
• 홈으로 이동 탭>> 열 분할에서> 구분자별 선택 .

• 구분자로 열 분할 대화 상자;
  • 구분 기호 선택 및 입력: 공백( )을 선택하세요. .
  • 확인을 클릭하세요. .

• 결과 열의 이름을 '이름'과 '성'으로 바꿉니다.

Google 스프레드시트 REGEXEXTRACT 함수

REGEXEXTRACT 함수를 사용하여 데이터를 구분 기호로 분할할 수 있습니다. 선택한 셀에 다음 수식을 삽입하세요.

=REGEXEXTRACT(E9, "^([^ ]+) (.+)$")

이 수식은 성과 이름을 두 개의 셀로 분리합니다. 필요에 따라 추가 열을 사용합니다.

파워 쿼리와 Google 스프레드시트 REGEX를 사용해야 하는 경우

파워 쿼리는 기본부터 고급 데이터 정리에 가장 유용합니다. 사용자 친화적인 인터페이스를 통해 보다 광범위하고 구조화된 데이터 변환에 이상적입니다. 복잡한 데이터세트, 구조화된 조인, 데이터 병합 작업을 할 때 효율적입니다.

Google Sheets REGEX 기능은 빠른 텍스트 기반 작업과 개별 셀 내의 특정 텍스트 추출에 적합합니다. REGEX 함수는 정규식 구문에 익숙한 경우 특히 유용합니다.

결론

파워 쿼리와 Google Sheets의 REGEX 기능은 데이터 정리를 위한 보완 도구를 제공합니다. 파워 쿼리는 대규모 데이터 세트에서 일괄 변환이 필요한 Excel 사용자에게 이상적인 반면, Google Sheets REGEX 함수는 소규모 데이터 세트에서 텍스트 기반 작업을 위한 유연성을 제공합니다. 강력하고 효율적인 데이터 정리를 위해 두 가지를 효과적으로 사용할 수 있습니다! 두 도구를 모두 이해하면 Excel이든 Google Sheets든 대부분의 정리 작업을 처리할 수 있습니다!