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 함수를 중첩해 점(.)을 하이픈(-)으로 바꾸고, 공백을 제거하는 등 복합적인 변환 작업을 수행합니다.