여러 시트 또는 워크북에서 값을 빠르게 검색하려면 어떻게 해야 하나요?

Excel에서 서로 다른 시트나 여러 워크북에 존재할 수 있는 특정 값을 찾아야 했던 경험이 있으신가요? 대형 프로젝트, 월간 보고서 작업, 또는 여러 파일에 걸쳐 정보를 통합해야 할 때 흔히 발생하는 상황입니다. 각 시트나 파일을 수작업으로 일일이 찾아보는 것은 시간 소모가 많을 뿐만 아니라 오류 발생 가능성도 높습니다. 이 튜토리얼에서는 하나의 통합 문서, 여러 워크북, 수식 기반 또는 통합 데이터 등 다양한 상황에서 효율적으로 데이터를 검색할 수 있는 여러 방법을 소개합니다. 이 방법들은 일상적인 Excel 작업이나 데이터 분석 프로젝트에서 마주칠 수 있는 실제 요구를 효과적으로 해결해 줍니다.

Find and Replace 기능으로 통합 문서 내 여러 시트에서 값 검색하기

VBA로 폴더 내 모든 워크북에서 값 검색하기

Kutools for Excel로 여러 개의 열린 워크북 전체에서 빠르게 값 검색하기

Excel 수식으로 여러 시트에서 값 검색하기

Find and Replace 기능으로 통합 문서 내 여러 시트에서 값 검색하기

Excel의 찾기 및 바꾸기 기능은 하나의 통합 문서 내 여러 시트에서 특정 값을 빠르게 검색할 수 있는 기본적이지만 효과적인 방법입니다. 어떤 시트에서 검색할지 알고 있거나 데이터가 하나의 파일 내에 비교적 잘 정리되어 있을 때 가장 효율적입니다. 다만, 이 방법은 다른 파일이나 닫힌 워크북 전체를 검색할 수는 없지만, 빠르게 값을 찾아볼 때 단순하고 직관적으로 사용할 수 있습니다.

1. 먼저 Ctrl 키를 누른 채로 시트 탭 바에서 검색하려는 각 워크시트를 개별적으로 클릭하여 시트 탭을 선택합니다. 이렇게 하면 검색이 선택한 모든 시트에 동시에 적용됩니다. 참고: 아래 스크린샷을 참조하세요.

2. 원하는 시트를 선택한 후, Ctrl + F 를 눌러 찾기 및 바꾸기 대화상자를 엽니다. 찾기 탭 아래의 찾을 내용 텍스트 상자에 검색하려는 값을 입력한 후 모두 찾기 버튼을 클릭합니다. 그러면 Excel은 선택한 시트들에서 해당 값을 포함하는 모든 셀과 위치를 즉시 나열해 보여줍니다. 참고: 아래 스크린샷을 참조하세요.

팁: 찾기 및 바꾸기 도구는 열려 있고 표시 중인 선택된 시트 내에서만 검색합니다. 추가적인 시트도 검색하려면 위에서 안내한 대로 반드시 모두 선택하세요. 실수로 시트를 빼먹었다면, 해당 시트를 선택한 후 다시 동일한 작업을 반복하시면 됩니다.

주의사항: 이 방법은 닫혀 있거나 숨겨진 워크북에서는 검색하지 않으며, 셀을 자동으로 강조 표시하지 않고 결과만 탐색을 위해 리스트로 보여줍니다.

문제 해결: 원하는 결과가 나오지 않는 경우, 시트 선택을 다시 확인하고, 검색을 방해할 수 있는 필터나 셀 보호 기능이 적용되어 있지 않은지 점검하세요.

여러 시트와 워크북에 걸쳐 값 찾기 및 바꾸기를 손쉽게 수행

Kutools for Excel의 고급 찾기 및 바꾸기 기능은 여러 시트 또는 열려 있는 모든 워크북에 걸쳐 값을 검색하고 바꿀 수 있는 효율적인 방법을 제공합니다. 이 고급 기능을 사용하면 대규모 데이터 세트를 다룰 때 시간을 절약하고 오류를 줄일 수 있어 Excel 작업을 더 빠르고 정확하게 처리할 수 있습니다.

Kutools for Excel - 필수적인 300개 이상의 도구로 Excel을 강화하세요. 영구적으로 무료인 AI 기능을 활용하세요! 지금 바로 구매하기

VBA로 폴더 내 모든 워크북에서 값 검색하기

폴더에 저장된 여러 워크북 전체(아직 열지 않은 파일 포함)에서 특정 값을 검색해야 할 때, Excel의 기본 도구만으로는 직접 수행할 수 없습니다. 이럴 때는 VBA(Visual Basic for Applications) 매크로를 활용하면 폴더 내의 각 워크북을 순차적으로 열고, 모든 시트를 자동으로 검사한 뒤 일치하는 위치를 기록할 수 있습니다. 이 방식은 정기적인 감사, 또는 보관 중이거나 일괄적으로 저장된 파일들에서 값 확인이 필요할 때 매우 실용적입니다.

1. 우선 Excel에서 새 워크북(빈 파일)을 엽니다. 검색 결과를 표시하려는 셀(보통 A1셀)을 선택합니다. Alt + F11을 눌러 Microsoft Visual Basic for Applications(매크로 편집기) 창을 엽니다.

2. VBA 편집기에서 메뉴에서 Insert > Module을 선택해 새 모듈을 만든 후, 아래 제공된 VBA 코드를 모듈 창에 붙여넣으세요.

VBA: 폴더 내 모든 워크북에서 값을 검색합니다.

Sub SearchFolders()
'UpdatebyKutoolsforExcel20200913
    Dim xFso As Object
    Dim xFld As Object
    Dim xStrSearch As String
    Dim xStrPath As String
    Dim xStrFile As String
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWk As Worksheet
    Dim xRow As Long
    Dim xFound As Range
    Dim xStrAddress As String
    Dim xFileDialog As FileDialog
    Dim xUpdate As Boolean
    Dim xCount As Long
    Dim xAWB As Workbook
    Dim xAWBStrPath As String
    Dim xBol As Boolean
    Set xAWB = ActiveWorkbook
    xAWBStrPath = xAWB.Path & "\" & xAWB.Name
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a forlder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    xStrSearch = "KTE"
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Set xOut = Worksheets.Add
    xRow = 1
    With xOut
        .Cells(xRow, 1) = "Workbook"
        .Cells(xRow, 2) = "Worksheet"
        .Cells(xRow, 3) = "Cell"
        .Cells(xRow, 4) = "Text in Cell"
        Set xFso = CreateObject("Scripting.FileSystemObject")
        Set xFld = xFso.GetFolder(xStrPath)
        xStrFile = Dir(xStrPath & "\*.xls*")
        Do While xStrFile <> ""
            xBol = False
            If (xStrPath & "\" & xStrFile) = xAWBStrPath Then
                xBol = True
                Set xWb = xAWB
            Else
                Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
            End If
            For Each xWk In xWb.Worksheets
                If xBol And (xWk.Name = .Name) Then
                Else
                Set xFound = xWk.UsedRange.Find(xStrSearch)
                If Not xFound Is Nothing Then
                    xStrAddress = xFound.Address
                End If
                Do
                    If xFound Is Nothing Then
                        Exit Do
                    Else
                        xCount = xCount + 1
                        xRow = xRow + 1
                        .Cells(xRow, 1) = xWb.Name
                        .Cells(xRow, 2) = xWk.Name
                        .Cells(xRow, 3) = xFound.Address
                        .Cells(xRow, 4) = xFound.Value
                    End If
                    Set xFound = xWk.Cells.FindNext(After:=xFound)
                Loop While xStrAddress <> xFound.Address
                End If
            Next
            If Not xBol Then
            xWb.Close (False)
            End If
            xStrFile = Dir
        Loop
        .Columns("A:D").EntireColumn.AutoFit
    End With
    MsgBox xCount & " cells have been found", , "Kutools for Excel"
ExitHandler:
    Set xOut = Nothing
    Set xWk = Nothing
    Set xWb = Nothing
    Set xFld = Nothing
    Set xFso = Nothing
    Application.ScreenUpdating = xUpdate
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

3. F5 키를 누르거나 Run 버튼을 클릭해 매크로를 실행합니다. 그러면 '폴더 선택' 대화 상자가 나타나 검색하려는 워크북이 저장된 폴더를 선택할 수 있습니다. 참고: 아래 스크린샷을 참조하세요.

4. OK를 클릭하세요. 검색이 완료되면 지정한 값이 포함된 셀의 개수를 알려주는 메시지 상자가 표시됩니다. 참고: 아래 스크린샷을 참조하세요.

5. OK를 눌러 메시지를 닫으면 값을 찾은 모든 위치가 새 워크시트에 나열되어, 워크북 이름, 워크시트 이름, 셀 주소, 실제 셀 내용까지 한눈에 확인하실 수 있습니다.

팁: 현재 검색어는 VBA 코드 내에서 “KTE”로 설정되어 있습니다. 원하는 값으로 검색하려면 코드에서 xStrSearch = "KTE" 부분의 "KTE"를 다른 값으로 변경하면 됩니다.

주의사항: 실행 전, 관련된 모든 워크북이 저장 및 닫혀 있는지 반드시 확인하세요(매크로를 실행하는 워크북만 열려 있어야 함). 많은 파일이나 복잡한 Excel 파일이 포함된 큰 폴더는 처리에 시간이 걸릴 수 있습니다. 실행 중에는 매크로를 중단하지 마세요.

문제 해결: 오류가 발생하면, 모든 파일이 정상적인 Excel 워크북인지(손상되거나 비밀번호로 보호되어 있지 않은지)와 매크로 실행 보안 설정이 올바르게 되어 있는지 확인하세요. 매크로가 끝나지 않으면, 우선 파일 수가 적은 폴더에서 먼저 실행해 보세요.

Kutools for Excel로 여러 개의 열린 워크북 전체에서 빠르게 값 검색하기

Excel을 동시에 여러 워크북을 열어놓고 작업할 때, Kutools for Excel은 전용 '찾기 및 바꾸기' 창을 제공하여 보다 쉽고 체계적으로 검색을 할 수 있습니다. 이 기능은 별도의 스크립트 작성이나 복잡한 설정 없이 여러 파일을 빠르게 검사해야 하는 사용자에게 특히 유용하며, 한 번에 여러 파일을 자주 다루면서 검색 작업을 간단하고 쉽게 처리하고 싶은 분들에게 적합합니다.

1. 열려 있는 어떤 워크북에서든, Kutools 탭으로 이동한 후 탐색. 다음으로 찾기 및 바꾸기 버튼을 클릭하여  다음과 같이 찾기 및 바꾸기 창을 열면 보통 Excel 창의 왼쪽에 위치합니다. 참고: 아래 스크린샷을 참조하세요.

2. 찾기 탭에서 검색하려는 값을 찾을 내용 텍스트 상자에 입력하세요. 그리고 모든 워크북 을 선택한 뒤 검색 범위 드롭다운 메뉴에서 선택하여 모든 열린 워크북 전체를 검색할 수 있습니다. 다음으로 모두 찾기 버튼을 누르면 일치하는 셀 목록과 위치가 즉시 표시됩니다. 참고: 아래 스크린샷을 참조하세요.

팁: Kutools for Excel의 고급 찾기 및 바꾸기 기능은 모든 열린 워크북뿐만 아니라, 선택한 시트, 현재 워크북, 현재 워크시트, 혹은 선택한 범위에서만 검색 및 바꾸기가 가능합니다. 이로써 작업 목적에 따라 맞춤형으로 효과적인 검색이 가능합니다.

주의사항: 검색하려는 모든 워크북이 Excel에서 열려 있는지 반드시 확인하세요. 이 도구는 현재 열린 파일만 검색이 가능하며, 닫혀 있는 파일은 검색 대상에 포함되지 않습니다.

문제 해결: 일부 파일이 검색 결과에 나타나지 않는다면, 해당 파일이 Excel에서 완전히 열려 있는지, 보호된 보기나 읽기 전용 모드가 적용되어 있지 않은지 점검하세요. 이런 설정은 검색 접근을 제한할 수 있습니다.

Kutools for Excel로 여러 개의 열린 워크북 전체에서 값 검색 데모

Excel 수식으로 여러 시트에서 값 검색하기

통합 문서 내에 여러 개의 특정 시트가 있고, 이들 중 어디에 어떤 값이 존재하는지 확인해야 할 때 Excel 수식을 사용해 동적으로 검색할 수 있습니다. 이 방법은 검색 결과를 자동으로 항상 최신 상태로 유지하고 싶을 때, 혹은 시트 목록이 비교적 고정되거나 별도의 테이블로 관리되고 있을 때 특히 적합합니다.

이 방법을 사용하려면 검색할 시트 이름을 미리 알고 있거나 목록화해 두어야 하며, 자동 체크, 대시보드, 전체 검색 대신 요약 참조 테이블을 만들고 싶을 때 가장 효율적입니다.

장점: 데이터가 변경될 때마다 결과가 자동 갱신되며, 스크립트나 추가 기능 없이 모두 통합 문서 내부에서 해결됩니다.
단점: 시트 이름이 자주 바뀌거나 시트 수가 너무 많을 경우에는 적합하지 않습니다.

예시 시나리오: 예를 들어, 'Sheet1', 'Sheet2', 'Sheet3'이라는 세 시트가 있다고 가정합니다. 이 시트들 각각의 A1 셀에 "Invoice123"이라는 특정 값이 존재하는지, 또는 해당 값이 어디에라도 있는지 확인하고 싶을 때 활용할 수 있습니다.

Step1. D2:D4(예: D2: Sheet1, D3: Sheet2, D4: Sheet3)에 시트 이름 목록이 있다고 가정합니다. E1 셀에 검색할 값을 입력(e.g., "Invoice123")한 뒤, F2 셀에 아래의 수식을 입력하세요:

=IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"), $E$1) >0, "Found", "Not Found")

Step2. F2셀의 수식을 F4까지 드래그하면 D2:D4에 나열된 모든 시트에 대해 각각 결과가 표시되며, 해당 시트에 값이 있으면 'Found', 없으면 'Not Found'가 반환됩니다.

작동 원리: 이 수식은 INDIRECT를 사용해 각 워크시트의 참조를 만들고, COUNTIF로 E1의 값이 각 시트의 A열에 존재하는지 확인합니다. 필요할 경우 검색 대상을 A:A에서 다른 열이나 특정 범위(A1:Z100 등)로 조정할 수 있습니다.

추가 팁: 해당 값이 포함된 시트 이름 자체를 구하려면, 다음 배열 수식을 사용하세요(이전 Excel 버전에선 Ctrl + Shift + Enter로 입력, Microsoft365/Excel2021 이상 버전에서는 일반 수식으로 입력):

=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"), $E$1)>0, D2:D4, ""))

이 수식은 값을 찾은 모든 시트 이름을 쉼표로 구분하여 반환합니다. 단, INDIRECT 함수는 열린 워크북에서만 동작하며, 닫힌 파일은 검색이 불가능하니 주의하세요.

주의사항: 시트 이름이 변경되거나 삭제되면 수식은 #REF! 오류를 반환하므로 시트 이름 목록이 정확한지 항상 확인하세요. 워크북이 크거나 INDIRECT 기반 수식이 많을 경우, 작업 속도가 느려질 수 있습니다.

문제 해결: 오류가 발생하면 참조된 시트가 모두 존재하는지, 검색 범위가 올바른지 확인하세요. 시트 목록이 동적이라면 이름 정의 범위나 데이터 유효성 검사로 시트 목록이 자동 갱신되도록 설정하는 것을 추천합니다.

관련 문서:

• 여러 워크시트 또는 전체 통합 문서에서 빠르게 찾기 및 바꾸기를 하는 방법은?
• 여러 개의 열린 Excel 파일에서 빠르게 찾기 및 바꾸기를 실행하는 방법은?
• Excel에서 가장 큰 음수(0보다 작은 값)를 찾는 방법은?
• Excel에서 열의 모든 중복 값을 추출하는 방법은?