Computer >> 컴퓨터 >  >> 소프트웨어 >> Office

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

데이터를 다루다 보면 특정 값을 일괄적으로 수정해야 하는 경우가 자주 발생합니다. 특히 여러 워크시트와 여러 통합 문서(파일)를 동시에 다뤄야 할 때는 하나하나 수정하기가 매우 번거롭습니다. 하지만 엑셀의 찾기 및 바꾸기(Find and Replace) 도구와 VBA 매크로를 제대로 활용할 줄 안다면 이런 까다로운 작업도 몇 번의 클릭만으로 해결할 수 있습니다. 이 글에서는 여러 시트와 여러 파일에서 원하는 값을 한꺼번에 찾아 바꾸는 방법을 찾기 및 바꾸기 기능과 VBA 매크로 두 가지 관점에서 소개합니다.

실습용 통합 문서 다운로드

글에서 사용한 예제 파일은 무료 실습용 통합 문서로 내려받아 직접 따라 해볼 수 있습니다.

엑셀에서 여러 시트·파일의 값을 찾아 바꾸는 3가지 방법

이어지는 섹션에서는 찾기 및 바꾸기 기능과 VBA 매크로를 활용해 여러 엑셀 시트와 파일에서 특정 값을 찾아 바꾸는 방법을 단계별로 알아봅니다.

1. 하나의 엑셀 파일에서 모든 시트의 값 찾아 바꾸기

현재 열려 있는 통합 문서의 모든 워크시트에서 특정 값을 한 번에 찾아 원하는 값으로 교체할 수 있습니다. 엑셀의 찾기 및 바꾸기 도구를 활용하는 방법을 살펴보겠습니다.

아래 이미지에는 One이라는 이름의 시트가 있습니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

같은 통합 문서 안에는 Two라는 또 다른 시트도 있습니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

이제 찾기 및 바꾸기 기능으로 특정 단어를 다른 단어로 교체해 보겠습니다. 여기서는 "Ambrose"라는 단어를 "Moxley"로 바꿔 보겠습니다.

진행 순서:

  • 홈 탭에서 찾기 및 선택 → 바꾸기를 클릭합니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

  • 찾기 및 바꾸기 팝업 창이 나타나면 옵션 >> 버튼을 클릭합니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

  • 확장된 옵션에서 찾을 내용 칸에 찾고 싶은 단어(예: "Ambrose")를 입력합니다.
  • 바꿀 내용 칸에는 교체할 단어(예: "Moxley")를 입력합니다.
  • 찾는 범위(Within) 드롭다운 목록에서 통합 문서(Workbook)를 선택합니다. 이렇게 하면 해당 통합 문서의 모든 시트에서 단어를 검색하고 한꺼번에 바꿔 줍니다.
  • 마지막으로 모두 바꾸기(Replace All)를 클릭합니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

그러면 몇 개가 바뀌었는지 알려주는 확인 메시지 상자가 나타납니다. 이 예제에서는 시트가 2개뿐이므로 2건이 교체되었습니다. 시트가 더 많으면 그만큼 더 많은 항목이 한 번에 교체됩니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

이제 실제로 바뀌었는지 확인해 보겠습니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

아래 이미지와 비교해 보세요.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

두 시트 모두에서 "Ambrose"가 "Moxley"로 성공적으로 교체된 것을 확인할 수 있습니다.

함께 읽으면 좋은 글: VBA로 엑셀에서 여러 값 찾아 바꾸기(예제 3가지)

2. 여러 통합 문서(파일)의 여러 시트에서 값 찾아 바꾸기

알고 계셨나요? 특정 워크시트에서 찾기 및 바꾸기 작업을 마친 후에도 찾기 및 바꾸기 팝업 창은 직접 닫기 전까지 열려 있는 상태로 유지됩니다. 이 특성을 활용하면 여러 통합 문서에 걸친 값 교체 작업을 아주 편리하게 처리할 수 있습니다.

아래 이미지처럼 엑셀 통합 문서 두 개를 열어 두었다고 가정해 보겠습니다. 하나는 Find and Replace Value(아래 이미지)입니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

다른 하나는 Transpose Multiple Columns(아래 이미지)입니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

앞서 설명한 방법으로 한 워크시트의 값을 교체한 뒤에도 찾기 및 바꾸기 팝업 창이 그대로 열려 있는 것을 볼 수 있습니다.

  • 이때 닫기 버튼을 누르지 말고, 다른 통합 문서 창으로 전환한 뒤 곧바로 모두 바꾸기를 누르기만 하면 됩니다. 그러면 해당 통합 문서의 모든 워크시트에서도 같은 조건으로 값을 찾아 교체해 줍니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

필요한 만큼 여러 통합 문서에 대해 반복해서 적용할 수 있습니다.

함께 읽으면 좋은 글: 엑셀에서 수식으로 찾아 바꾸기(예제 4가지)

관련 글:

  • 엑셀 수식에서 텍스트 바꾸는 7가지 쉬운 방법
  • 엑셀에서 별표(*) 문자 찾아 바꾸는 방법
  • Excel VBA로 범위 내 텍스트 찾아 바꾸기(매크로와 사용자 폼)
  • 엑셀 열에서 찾아 바꾸는 6가지 방법
  • 엑셀에서 선택 영역 내에서만 찾아 바꾸는 7가지 방법

3. VBA 매크로로 열려 있는 통합 문서 전체의 값 찾아 바꾸기

앞서 소개한 모든 작업은 VBA 코드 하나로 처리할 수도 있습니다. 엑셀을 능숙하게 다루는 중급~상급 사용자라면 이 방법이 가장 적합합니다. VBA는 엑셀에서 어떤 작업이든 실행할 때 가장 효율적이고 빠르며 안전한 방법입니다. 통합 문서의 모든 워크시트에서 값을 찾아 교체하는 절차를 살펴보겠습니다.

진행 순서:

  • 먼저 키보드에서 Alt + F11을 누르거나 개발 도구 → Visual Basic 탭으로 이동해 Visual Basic Editor(VBE)를 엽니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

  • 코드 창이 열리면 메뉴 모음에서 삽입 → 모듈을 클릭합니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

  • 그다음 아래 코드를 복사하여 코드 창에 붙여넣기 합니다.
Sub MultipleFindAndReplace()
Dim iSheet As Worksheet
Dim OldValue As Variant
Dim NewValue As Variant
Dim i As Long
OldValue = Array("John", "Roman", "Dean", "Seth", "Finn")
NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh")
  For i = LBound(OldValue) To UBound(OldValue)
      For Each iSheet In ActiveWorkbook.Worksheets
        iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      Next iSheet
  Next i
End Sub

코드 실행 준비가 완료되었습니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

이 코드는 "John, Roman, Dean, Seth, Finn" 값들을 각각 "Ben, Alex, Joe, Chris, Josh"로 교체합니다.

  • 이제 F5 키를 누르거나, 메뉴 모음에서 실행 → Sub/UserForm 실행을 선택합니다. 서브 메뉴 모음의 작은 실행 아이콘을 클릭해도 됩니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

코드 실행 후 결과는 아래 이미지와 같습니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

코드에 지정한 모든 값("John, Roman, Dean, Seth, Finn")이 "Ben, Alex, Joe, Chris, Josh"로 성공적으로 교체되었습니다.

이 코드는 여러 통합 문서의 여러 워크시트에서 여러 값을 찾아 바꾸는 작업에도 활용할 수 있습니다.

  • 한 통합 문서에서 매크로를 실행한 뒤, 다른 통합 문서 창으로 전환하고 개발 도구 → 매크로로 이동합니다.
  • 매크로 팝업 창이 열리면 매크로 이름을 선택하고 실행(Run)을 클릭합니다.

엑셀에서 여러 시트·여러 파일의 값을 한 번에 찾아 바꾸는 3가지 방법

그러면 나중에 연 통합 문서에도 동일한 결과가 적용되며, 코드에 입력한 찾기·바꾸기 값들이 해당 파일에서도 정상적으로 작동합니다.

VBA 코드 해설

Dim iSheet As Worksheet
Dim OldValue As Variant
Dim NewValue As Variant
Dim i As Long

사용할 변수들을 선언하는 부분입니다.

OldValue = Array("John", "Roman", "Dean", "Seth", "Finn")
NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh")

OldValue 변수에는 찾을 값을, NewValue 변수에는 교체할 값을 배열로 지정합니다.

  For i = LBound(OldValue) To UBound(OldValue)
      For Each iSheet In ActiveWorkbook.Worksheets
        iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      Next iSheet
  Next i

이 코드는 먼저 배열 목록의 각 항목을 순회한 뒤, ActiveWorkbook(활성 통합 문서)의 각 워크시트를 차례대로 살펴봅니다. 일치하는 값을 발견하면 OldValue를 NewValue로 교체하고, 한 시트의 순회가 끝나면 다음 시트로 넘어가 같은 작업을 반복 실행합니다.

함께 읽으면 좋은 글: 엑셀에서 여러 값 찾아 바꾸기(6가지 빠른 방법)

마무리

지금까지 엑셀에서 찾기 및 바꾸기 도구와 VBA 매크로를 활용해 여러 시트와 여러 파일의 값을 찾아 바꾸는 다양한 방법을 살펴보았습니다. 이 글이 업무 효율을 높이는 데 도움이 되기를 바랍니다. 내용에 대해 궁금한 점이 있다면 언제든 질문해 주세요.

관련 글

  • 엑셀 데이터 정리 기법: 셀에 텍스트 추가하기
  • 엑셀에서 수식 앞에 텍스트 추가하는 4가지 쉬운 방법
  • 엑셀에서 와일드카드로 값 찾아 바꾸기
  • 엑셀 VBA의 SUBSTITUTE 함수 활용법(예제 3가지)
  • 엑셀에서 특수 문자 바꾸는 6가지 방법