여러 시트나 워크북에서 값을 빠르게 검색하는 방법은 무엇인가요？
특정 값을 Excel 의 여러 시트 또는 여러 워크북 전반에서 찾아야 할 필요가 있었나요？ 이는 특히 대규모 프로젝트， 월별 보고서 작업， 또는 여러 파일에 분산된 정보를 통합할 때 흔히 발생하는 상황입니다。 각 시트나 파일을 수동으로 살펴보는 것은 시간이 많이 소요될 뿐만 아니라 오류가 발생하기 쉽습니다。 이 튜토리얼에서는 하나의 워크북 내에서든 여러 워크북에 걸쳐 있든， 또는 수식 기반 해결책이나 통합 데이터 솔루션이 필요한 경우든 관계없이 데이터를 효율적으로 검색할 수 있는 몇 가지 효과적인 방법을 소개합니다。 이러한 접근 방식은 일상적인 Excel 워크플로우나 데이터 분석 프로젝트에서 마주칠 수 있는 실무적 요구사항을 해결해 줍니다。
찾기 및 바꾸기 함수를 사용하여 워크북의 여러 시트에서 값을 검색하는 방법
VBA 를 사용하여 폴더 내 모든 워크북에서 값을 검색하는 방법
Kutools for Excel 을(를) 사용하여 여러 개의 열린 워크북에서 값을 빠르게 검색하는 방법
Excel 수식을 사용하여 여러 시트에서 값을 검색하는 방법
찾기 및 바꾸기 함수를 사용하여 워크북의 여러 시트에서 값을 검색하는 방법
Excel 의찾기 및 바꾸기함수는 동일한 워크북 내의 여러 워크시트에서 특정 값을 빠르게 찾는 기본적이면서도 효과적인 방법입니다。 이 방법은 검색하고자 하는 시트를 알고 있거나 단일 파일 내에서 데이터가 비교적 잘 구조화되어 있을 때 가장 유용합니다。 다른 파일이나 닫힌 워크북에서는 검색이 지원되지 않지만， 빠른 조회를 위한 간단한 방법을 제공합니다。
1. 먼저Ctrl키를 누른 상태에서시트 탭탭 바을 클릭하여 검색에 포함할 시트 탭을 선택합니다。 이렇게 하면 모든 선택한 워크시트에서 동시에 검색이 수행됩니다。 스크린샷 참조：
2. 원하는 시트를 선택한 후Ctrl + F를 눌러찾기 및 바꾸기대화 상자를 엽니다。찾을 내용텍스트 상자에 찾고자 하는 값을 입력하고찾기탭에서모두 찾기버튼을 클릭합니다. 그러면 Excel 이 즉시 해당 선택한 워크시트에서 찾은 모든 셀과 그 위치를 목록으로 표시합니다。 스크린샷 참조：
팁：찾기 및 바꾸기 도구는 열려 있고 표시 중인 선택한 워크시트에서만 검색합니다。 추가 시트를 검색 범위에 포함하려면 위와 같이 해당 시트를 선택해야 합니다。 실수로 시트를 누락한 경우， 해당 시트를 선택한 후 다시 작업을 반복하면 됩니다。
주의사항：이 방법은 닫혀 있거나 숨겨진 워크북에서는 검색이 불가능하며， 셀을 자동으로 강조 표시하지 않습니다。 결과 목록만 제공하여 사용자가 직접 탐색할 수 있도록 합니다。
문제 해결 팁：예상한 결과가 표시되지 않으면 시트 선택을 다시 확인하고 필터나 셀 보호 등 검색을 방해할 수 있는 설정이 적용되지 않았는지 확인하세요。
여러 시트와 워크북에서 값을 손쉽게 찾기 및 바꾸기 |
Kutools for Excel의 고급찾기 및 바꾸기기능을 사용하면 여러 시트는 물론 열려 있는 모든 워크북에서 값을 효율적으로 검색하고 바꿀 수 있습니다。 이 고급 기능으로 대용량 데이터 작업 시 오류를 저장 시간하고 제거하여 엑셀 작업을 더욱 빠르고 정확하게 수행할 수 있습니다。 |
 |
Kutools for Excel- 엑셀에 300 개 이상의 필수 도구를 더해 작업 속도와 편의성을 높이고， 스마트한 데이터 처리 및 생산성을 위한 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 창 왼쪽에 표시되는
찾기 및 바꾸기패널을 표시합니다。 스크린샷 참조： pane, usually located at the left side of your Excel window. See screenshot:
2. 찾기탭에서 찾고자 하는 값을찾을 내용텍스트 상자에 입력합니다。모든 워크북을범위드롭다운 메뉴에서 선택하여 열려 있는 모든 워크북에서 검색을 수행합니다。 그런 다음모두 찾기를 클릭하여 일치하는 모든 셀과 그 위치를 즉시 목록으로 표시합니다。 스크린샷 참조：
팁：Kutools for Excel 의 고급 찾기 및 바꾸기 유틸리티를 사용하면 열려 있는 모든 워크북뿐만 아니라 특정 선택한 시트， 현재 워크북， 현재 워크시트 또는 사용자의 현재 선택에서도 데이터를 검색 및 교체할 수 있습니다。 이를 통해 필요에 따라 맞춤형 제어가 가능합니다。
주의사항：이 도구는 현재 Excel 에서 열려 있지 않은 파일은 검색할 수 없으므로 검색하고자 하는 모든 워크북이 미리 열려 있는지 확인하세요。
문제 해결 팁：일부 파일이 검색 결과에 나타나지 않으면 해당 파일이 Excel 에서 완전히 로드되었는지， 또는 검색 접근을 제한할 수 있는 보호 보기 또는 읽기 전용 모드에 있지 않은지 다시 확인하세요。
데모: Kutools for Excel 을(를) 사용하여 여러 개의 열린 워크북에서 찾을 값
Excel 수식을 사용하여 여러 시트에서 값을 검색하는 방법
워크북에 여러 개의 알려진 워크시트 이름이 있고 해당 시트들 중 특정 값이 존재하는지 여부와 위치를 확인해야 하는 상황에서는 Excel 수식을 사용하여 동적으로 여러 시트를 검색할 수 있습니다。 이 방법은 검색 결과를 자동으로 최신 상태로 유지하고자 하며， 시트 목록이 비교적 고정적이거나 별도의 테이블에서 관리되는 경우에 특히 적합합니다。
이 방법을 사용하려면 검색할 모든 시트의 이름을 미리 알고 있거나 목록으로 작성해야 합니다。 정기적인 자동 점검， 대시보드 구성 또는 매번 전체 검색을 실행하지 않고 요약 참조를 구축하고자 할 때 가장 효과적입니다。
장점：데이터가 변경되면 결과가 자동으로 업데이트됩니다。 스크립트나 애드인이 필요 없으며， 모든 작업이 워크북 내에서 처리됩니다。
단점：시트 이름이 자주 변경되거나 시트 수가 매우 많은 경우에는 적합하지 않습니다。
예시 시나리오：다음 세 개의 시트가 있다고 가정합니다。 시트 이름은 각각Sheet1,Sheet2및Sheet3입니다。 특정 값(예： “Invoice123")이 A1 셀에 나타나는 시트를 찾거나， 해당 값이 이들 시트 어딘가에 존재하는지 여부만 확인하고자 합니다。
1 단계。D2:D4 셀 범위에 시트 이름 목록이 있다고 가정합니다()D2: Sheet1, D3: Sheet2, D4: Sheet3)。 검색할 값을(예： “Invoice123")E1셀에 입력합니다。 그런 다음 F2 셀에 다음 수식을 입력합니다：
=IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"), $E$1) >0, "Found", "Not Found")2 단계。F2 에서 F4 까지 수식을 아래로 끌어 D2:D4 에 나열된 모든 시트를 확인합니다。 그러면 각 시트에 대해 「찾음」 또는 「찾지 못함」 결과가 반환됩니다。
작동 방식：이 수식은 INDIRECT 함수를 사용하여 각 시트에 대한 참조를 생성하고, COUNTIF 함수를 사용해 E1 셀의 값이 각 시트의 A 열에 존재하는지 확인합니다。 필요에 따라 범위A:A를 다른 열이나 특정 범위(예：A1:Z100)로 조정할 수 있습니다。
추가 팁：해당 값을 포함하는 시트 이름을 가져오려면 다음 배열 수식을 사용하세요(이전 버전의 Excel 에서는 Ctrl + Shift + Enter 로 입력하고， Microsoft 365/Excel 2021+에서는 일반 수식으로 입력 가능)：
=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"), $E$1)>0, D2:D4, ""))이 수식은 해당 값을 찾은 모든 시트 이름을 쉼표로 구분한 목록으로 반환합니다。 다만 INDIRECT 함수는 열려 있는 워크북에서만 작동하며， 닫힌 파일은 검색할 수 없으므로 주의해야 합니다。
주의 사항：시트 이름이 변경되거나 삭제되면 수식은 #REF！ 오류를 반환합니다。 항상 시트 이름 목록 이름이 올바른지 확인하세요。 대규모 워크북에서는 INDIRECT 기반 수식이 워크북 성능을 저하시킬 수 있습니다。
문제 해결：오류가 표시되면 참조된 모든 시트가 존재하는지， 검색 범위가 올바른지 확인하세요。 동적인 시트 목록을 사용하는 경우， 이름이 지정된 범위 또는 데이터 유효성 검사를 활용해 시트 목록을 자동으로 최신 상태로 유지하는 것을 고려해 보세요。
관련 문서：
- 여러 워크시트나 전체 워크북에서 찾기 및 바꾸기하는 방법은 무엇인가요？
- 여러 개의 열린 Excel 파일에서 찾기 및 바꾸기하는 방법은 무엇인가요？
- Excel 에서 가장 큰 음수 값(0 보다 작은 값)을 찾는 방법은 무엇인가요？
- Excel 에서 열에서 중복된 모든 값을 추출하는 방법은 무엇인가요？
최고의 Office 생산성 도구
| 🤖 | KUTOOLS AI 도우미： 다음을 기반으로 데이터 분석 혁신하기：지능형 실행 | 코드 생성| 사용자 지정 수식 생성 | 데이터 분석 및 차트 생성| 향상된 함수 호출… |
| 인기 기능:찾기， 강조 표시 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 결합 또는 셀 제거 | 공식을 사용하지 않는 반올림... | |
| 슈퍼 LOOKUP:다중 조건 VLookup | 다중 값 VLookup | 여러 시트에서 VLookup | 퍼지 매치.... | |
| 고급 드롭다운 목록:드롭다운 목록 빠르게 생성 | 종속형 드롭다운 목록 | 다중 선택 드롭다운 목록.... | |
| 열 관리자:특정 수의 열 추가|열 이동|숨겨진 열의 표시 상태 전환|범위 및 열 비교... | |
| 주요 기능:그리드 포커스 | 디자인 보기 |향상된 수식 표시줄 | 워크북 및 시트 관리자 | 자원 라이브러리(자동 텍스트)| 날짜 선택기 | 워크시트 병합 | 암호화/셀 해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵은 글꼴이 있는 셀 필터링/기울임꼴/취소선。。。) 。。。 | |
| 상위 15 도구 모음:12 텍스트도구(텍스트 추가,특정 문자 삭제, ...)| 50+차트유형(간트 차트, ...)| 40+ 실용적인수식(생일을 기준으로 나이 계산, ...)| 19 삽입도구(QR 코드 삽입,경로에서 그림 삽입, ...)| 12 변환도구(단어로 변환하기,환율 변환, ...)| 7 병합 및 분할도구(고급 행 병합,셀 분할, ...)|그 외 더 많은 기능 |
Kutools for Excel 로 Excel 역량을 한 단계 업그레이드하고 전례 없는 효율성을 경험하세요。Kutools for Excel 는 생산성과 저장 시간을 향상시키는 300 개 이상의 고급 기능을 제공합니다。가장 필요한 기능을 지금 바로 확인하세요。。。
Office Tab 가 Office 에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만들어 줍니다
- Word, Excel, PowerPoint 에서 탭 기반 편집 및 읽기 기능을 활성화합니다, Publisher, Access, Visio 및 Project 에서도 사용 가능합니다。
- 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 생성할 수 있습니다。
- 50% 만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄여줍니다！
모든 Kutools 애드인。 하나의 설치 프로그램
Kutools for Office스위트 번들은 Excel, Word, Outlook 및 PowerPoint 용 애드인과 Office Tab Pro 를 포함하며， 다양한 Office 앱을 사용하는 팀에 이상적입니다。
- 올인원 스위트— Excel， Word， Outlook 및 PowerPoint 애드인 + Office Tab Pro
- 하나의 설치 프로그램， 하나의 라이선스— 몇 분 안에 설정 완료(MSI 지원)
- 함께 사용할수록 더 효과적입니다— Office 앱 전반에서 생산성 향상
- 30 일간 모든 기능 무료 체험— 등록이나 신용카드 필요 없음
- 최고의 가성비— 개별 애드인 구매 대비 절약