3-52. 스프레드시트 찾기 및 바꾸기

스프레드시트의 찾기 및 바꾸기 기능을 활용하면 특정 문자열 패턴을 대량으로 일괄 변경할 수 있어, 데이터 표준화 및 일관성 유지 작업을 효율적으로 수행할 수 있습니다.

 

🔗 지금 바로 사용해보세요!

📥 찾기 및 바꾸기 스프레드시트 바로가기

클릭 한 번으로 바로 접속해서 복사하여 사용하실 수 있습니다

 

 

1. 주제 설명: 개념과 필요성

찾기 및 바꾸기(Find & Replace)는 지정된 문자열 패턴을 찾아 다른 문자열로 일괄 대체하는 기능입니다.

·        기본 기능

    o   메뉴: 편집 → 찾기 및 바꾸기 (Ctrl+H)

     o   함수: =SUBSTITUTE(텍스트, 찾을문자, 바꿀문자)

·        고급 옵션

     o   정규식 사용: 패턴 매칭으로 복잡한 규칙 적용

     o   대소문자 구분: 정확한 매칭 조건 설정

     o   전체 단어만: 부분 일치 방지

·        필요성

     o   데이터 표준화: 통일된 형식으로 변환

     o   오타 수정: 반복되는 오류 일괄 처리

     o   업무 효율: 수백 개 셀을 한 번에 수정

 

 

2. 실습 방법: 단계별 적용법

1.      RawData 시트 준비

     o   A1:D51에 번호, 예시데이터1, 예시데이터2, 결과값 헤더 입력

     o   B열에 apple_old, banana_test 등 패턴이 포함된 텍스트 입력

     o   C열에 찾을 패턴(_old) 입력

 

2.     SUBSTITUTE 함수 활용

     o   D2셀에 다음 수식 입력 후 D2:D51 복사

=SUBSTITUTE(B2, C2, "_new") 

     o   결과: apple_old → apple_new로 자동 변환

 

3.      메뉴를 통한 찾기 및 바꾸기

     o   B열 전체 선택 → Ctrl+H → 찾을 내용: _old → 바꿀 내용: _new → 모두 바꾸기

 

4.     정규식 활용 (고급)

     o   찾기 옵션에서 '정규식 사용' 체크

     o   예: _.*$ (밑줄 뒤 모든 문자) → _updated로 변경

 

 

3. CSV 예시 데이터 파일

아래는 50개 샘플 중 처음 5개 행 예시입니다. 결과값 열에는 =SUBSTITUTE 함수가 적용된 형태입니다.

번호,예시데이터1,예시데이터2,결과값
1,grape_test,_old,=SUBSTITUTE(B2,C2,"_new") →grape_test
2,apple_new,_old,=SUBSTITUTE(B3,C3,"_new") →apple_new
3,apple_old,_old,=SUBSTITUTE(B4,C4,"_new") →apple_new
4,grape_old,_old,=SUBSTITUTE(B5,C5,"_new") →grape_new
5,apple_final,_old,=SUBSTITUTE(B6,C6,"_new") →apple_final

 

 

4. XLSX 예시 템플릿 갤러리 파일

·        RawData 시트

     o   B열: 예시데이터1에 fruit_suffix 형태 텍스트

     o   C열: 예시데이터2에 찾을 패턴(_old)

     o   D열: =SUBSTITUTE(B2,C2,"_new") 수식 적용(2행부터 51행까지)

·        ProcessedData 시트

     o   RawData 시트 참조하여 처리 결과 표시

     o   헤더 행 연한 녹색 배경, 굵은 글씨 적용

·        Dashboard 시트

     o   전체 항목 수: =COUNTA(RawData!A2:A51)

     o   _old 포함 항목: =COUNTIF(RawData!B2:B51,"*_old*")

     o   처리 후 _new 항목: =COUNTIF(RawData!D2:D51,"*_new*")

     o   막대 차트: 찾기/바꾸기 전후 비교 시각화

·        스타일

     o   대시보드 제목 폰트 크기 16, 굵게

     o   차트 제목: '찾기/바꾸기 전후 비교'

     o   시트명!셀참조 방식으로 모든 수식 적용

 

 

5. 실무 및 교육 현장 활용 사례

중학생도 이해할 수 있도록 구체적인 예시입니다.

·        학생 이메일 주소 일괄 수정
기존 @oldschool.edu 도메인을 @newschool.edu로 일괄 변경합니다.
=SUBSTITUTE(B2, "@oldschool.edu", "@newschool.edu") 수식을 사용해 500명 학생의 이메일을 한 번에 업데이트할 수 있습니다.

·        상품명 표준화
상품명_구버전을 상품명_신버전으로 통일합니다.
재고 관리 시스템에서 일관된 제품명을 유지하여 검색 오류를 방지하고 보고서 정확도를 높입니다.

·        전화번호 형식 통일
010-1234-5678, 010.1234.5678, 01012345678 등 다양한 형식을 010-1234-5678로 표준화합니다.
여러 단계의 SUBSTITUTE 함수를 중첩해 점(.)을 하이픈(-)으로 바꾸고, 공백을 제거하는 등 복합적인 변환 작업을 수행합니다.