여러 시트 또는 통합 문서에서 값을 빠르게 검색하는 방법은 무엇입니까?

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 편집기에서 삽입 > 모듈로 이동하여 새 모듈을 생성하고 다음 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 키를 누르거나 실행 버튼을 클릭하여 매크로를 실행합니다. 폴더 선택 대화 상자가 나타나며 검색할 통합 문서가 들어 있는 폴더를 선택할 수 있습니다. 스크린샷 보기:

4. 확인 버튼을 클릭하세요. 검색이 완료된 후 메시지 상자가 나타나 지정한 값이 포함된 셀의 총 개수를 알려줍니다. 스크린샷 보기:

5. 확인 버튼을 클릭하여 메시지를 닫습니다. 값이 발견된 모든 위치는 새로운 워크시트에 나열되며, 통합 문서 이름, 워크시트 이름, 셀 참조 및 검토를 위한 정확한 셀 내용이 포함됩니다.

팁: 현재 검색어는 VBA에서 “KTE”로 설정되어 있습니다. xStrSearch = "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이라는 3개의 시트가 있다고 가정합니다. 특정 값(예: "Invoice123")이 A1 셀에 있는 시트를 확인하거나 해당 값이 해당 시트들에 존재하는지 여부를 확인하고자 합니다.

1단계. D2:D4(D2: Sheet1, D3: Sheet2, D4: Sheet3)에 시트 이름 목록이 있다고 가정합니다. E1에 검색할 값(예: "Invoice123")을 입력합니다. 그런 다음 F2에 다음 수식을 입력하세요:

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

2단계. F2의 수식을 F4까지 드래그하여 D2:D4에 나열된 모든 시트를 확인합니다. 그러면 각 시트에 대해 "Found" 또는 "Not Found"가 반환됩니다.

작동 원리: 이 수식은 INDIRECT를 사용하여 각 워크시트에 대한 참조를 생성하고 COUNTIF를 사용하여 E1의 값이 각 시트의 A열에 있는지 확인합니다. 필요한 경우 범위 A:A를 다른 열 또는 특정 범위(예: A1:Z100)로 조정하세요.

추가 팁: 값을 포함하는 시트 이름을 가져오려면 다음 배열 수식을 사용하세요(이전 Excel 버전에서는 Ctrl + Shift + Enter로 입력하거나 Microsoft 360/Excel 2021+에서는 일반 수식으로 입력):

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

이 수식은 값이 발견된 모든 시트 이름을 쉼표로 구분된 목록으로 반환합니다. INDIRECT는 열려 있는 통합 문서에서만 작동하며 닫힌 파일을 검색할 수 없다는 점에 유의하세요.

주의사항: 시트 이름이 변경되거나 삭제되면 수식은 #REF! 오류를 반환합니다; 항상 시트 이름 목록이 올바른지 확인하세요. 큰 통합 문서의 경우 INDIRECT 기반 수식이 통합 문서 성능을 저하시킬 수 있습니다.

문제 해결: 오류가 발생하면 참조된 모든 시트가 존재하는지와 검색 범위가 올바른지 확인하세요. 동적 시트 목록의 경우 시트 목록을 자동으로 업데이트하도록 명명된 범위나 데이터 유효성 검사를 사용하는 것을 고려하세요.

관련 기사:

• 여러 워크시트 또는 전체 통합 문서에서 빠르게 찾아 바꾸는 방법은 무엇입니까?
• 여러 열린 Excel 파일에서 빠르게 찾아 바꾸는 방법은 무엇입니까?
• Excel에서 가장 큰 음수 값(0보다 작은 값)을 찾는 방법은 무엇입니까?
• Excel에서 열의 모든 중복 항목을 추출하는 방법은 무엇입니까?