Excel에서 여러 워크시트의 동일한 셀을 참조하는 방법은 무엇입니까?
Excel로 작업할 때 관련 데이터를 하나의 통합 문서 내 여러 워크시트에 걸쳐 정리하는 것이 매우 일반적입니다. 때로는 모든 워크시트에서 동일한 셀(예: B8)을 가져와 단일 요약 또는 마스터 시트로 결합하거나 비교해야 할 수 있습니다. 이러한 접근 방식은 월별 결과나 부서별 합계와 같은 일관된 정보를 포괄적인 개요로 통합하여 더 쉬운 분석과 보고를 가능하게 합니다. 예를 들어, 여러 시트(Sheet1, Sheet2, Sheet3, Sheet4 등)의 셀 B8에 저장된 월별 수치를 추출하고 이를 아래와 같이 마스터 시트에 함께 나열하고자 할 수 있습니다.
 |  |  |  |  |  |
Excel에서는 여러 워크시트의 동일한 셀을 참조하고 결과를 마스터 워크시트에 수집하는 몇 가지 방법을 제공합니다. 공식, VBA 또는 타사 도구 사용에 대한 선호도에 따라 특정 요구에 맞는 방법을 선택할 수 있습니다. 아래에는 이 작업을 효율적으로 수행하기 위한 실용적인 접근 방식이 나와 있습니다. 또한 목차에서 관련 시나리오를 처리할 수 있는 두 가지 다른 권장 솔루션을 찾을 수 있습니다.
공식을 사용하여 여러 워크시트의 동일한 셀을 하나의 마스터 시트로 참조하기
VBA 코드를 사용하여 여러 워크시트의 동일한 셀을 하나의 마스터 시트로 참조하기
놀라운 기능을 사용하여 여러 워크시트의 동일한 셀을 하나의 마스터 시트로 참조하기
공식을 사용하여 여러 워크시트의 동일한 셀을 하나의 마스터 시트로 참조하기
워크시트 이름이 기본 패턴(Sheet1, Sheet2, Sheet3 등)을 따르는 경우 Excel 공식은 여러 워크시트에서 동일한 셀을 참조하는 빠른 방법을 제공합니다. 이 접근 방식은 간단하고 동적이며 코드가 필요하지 않아 자주 시트를 추가하거나 제거하는 경우 특히 적합합니다.
1. 열에 도우미 숫자 목록을 만드는 것으로 시작하세요. 예를 들어 1, 2, 3, 4 등을 입력합니다. 각 숫자는 참조하려는 워크시트 접미사 중 하나에 해당합니다. 이 단계는 참조 과정을 자동화하고 각 시트에 대해 수동으로 공식을 작성하는 것을 방지합니다. 스크린샷 보기:
2. 추출된 값을 표시하려는 셀(예: 마스터 시트의 셀 B2)에 다음 공식을 입력합니다. 그런 다음 채우기 핸들을 열 아래로 드래그하여 각각의 해당 워크시트를 자동으로 참조합니다. 모든 대상 셀 값(여기서는 각 시트의 B8)이 한 번에 검색됩니다.
참고: 이 공식에서 E2는 참조하려는 워크시트 이름 접미사와 일치하는 도우미 숫자(1,2,3, …)를 나타내며, B8은 가져오려는 셀 주소입니다. 실제 레이아웃 및 셀에 따라 E2 및 B8을 필요한 대로 조정하세요. 이 방법은 워크시트가 "Sheet1", "Sheet2" 등의 명명 패턴을 따를 때만 직접 작동합니다.
이 공식 기반 솔루션은 구조화된 통합 문서에 대해서는 빠르고 쉽지만, 시트가 비숫자 이름을 갖고 있거나 자주 추가/제거되는 경우에는 복잡해질 수 있습니다. #REF! 오류가 발생하지 않는지 확인하세요. 이는 도우미 값과 기존 시트 이름 간의 불일치를 나타낼 수 있습니다.
VBA 코드를 사용하여 여러 워크시트의 동일한 셀을 하나의 마스터 시트로 참조하기
통합 문서에 많은 워크시트가 포함되어 있고 그 이름이 예측 가능한 패턴을 따르지 않는 경우(즉, 시트 이름이 사용자 정의되었거나 순차적이지 않은 경우), VBA를 사용하면 모든 시트에서 동일한 셀 참조를 추출하여 마스터 워크시트로 가져올 수 있는 직접적인 접근 방식을 제공합니다. 이 방법은 시트 이름에 관계없이 일괄 처리를 가능하게 하며, 크거나 일관되지 않은 통합 문서를 통합하는 데 유용합니다.
1. 마스터 워크시트에서 다른 워크시트에서 추출한 데이터를 표시하기 시작하려는 셀(예: B8)을 클릭합니다. 이 셀 아래에 각 워크시트에서 추출될 결과를 수용하기에 충분한 빈 행이 있는지 확인하세요.
2. ALT + F11 키를 동시에 눌러 Microsoft Visual Basic for Applications 창을 엽니다.
3. VBA 편집기에서 삽입 > 모듈을 클릭합니다. 다음 코드를 모듈 창에 복사하여 붙여넣습니다.
VBA 코드: 여러 워크시트에서 동일한 셀 참조하기
Sub AutoFillSheetNames()
'Update by Extendoffice
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub4. 코드를 붙여넣은 후 F5 키를 누르거나 실행 버튼을 클릭하여 코드를 실행합니다. 각 워크시트(마스터 시트 자체는 제외, 코드에 따라 다름)의 셀 B8에서 모든 값이 수집되어 선택한 셀부터 아래쪽으로 마스터 워크시트에 채워집니다. 결과는 세로로 나열되어 쉽게 검토하거나 추가 분석할 수 있습니다.
참고: 이 VBA 솔루션은 코드를 실행하기 전에 선택한 셀에 대해 작동합니다. 예를 들어, 셀 A1을 클릭한 경우 다른 모든 시트에서 해당 A1 값을 가져옵니다.
놀라운 기능을 사용하여 여러 워크시트의 동일한 셀을 하나의 마스터 시트로 참조하기
수식이나 코딩 없이 더 편리한 솔루션을 찾는 사용자를 위해 Kutools for Excel에서는 Dynamically Refer to Worksheets라는 직관적인 기능을 제공합니다. 이 도구를 사용하면 어떻게 시트가 명명되었는지에 관계없이 수십 개 또는 수백 개의 워크시트에서 동일한 셀 참조를 몇 번의 클릭만으로 추출할 수 있습니다. 이는 자주 데이터를 통합하고 수식 작성이나 VBA 코드 수정보다는 시각적이고 단계적인 프로세스를 선호하는 사용자에게 이상적입니다.
Kutools for Excel 설치 후 여러 워크시트에서 데이터를 통합하기 위해 다음 단계를 따르세요.
1. 마스터 시트에서 다른 워크시트에서 채워질 참조를 표시하려는 셀(예: B8)을 선택합니다.
2. Kutools 탭으로 이동하여 더 보기(More)를 선택하고 아래와 같이 Dynamically Refer to Worksheets를 선택합니다.
3. Fill Worksheets References 대화 상자에서 'Fill order'에서 ' Fill vertically cell after cell '을 선택하고, 공식 텍스트 상자 옆의 작은 잠금 아이콘을 클릭하면 회색 잠금이 노란색 잠금으로 바뀌어 공식과 셀 참조가 잠겼다는 의미이며, 이제 다른 워크시트에서 셀 B8 참조를 추출하기 위해 아무 셀이나 클릭할 수 있습니다. 이 예에서는 셀 B2를 클릭하겠습니다. 그런 다음, 셀 참조를 추출할 워크시트를 선택하세요. 스크린샷 보기:
참고: 항상 잠금을 클릭하여 선택한 셀 참조를 고정하세요. 잠금이 설정되지 않은 경우 선택에 따라 셀 참조가 변경되어 잘못된 결과가 발생할 수 있습니다.
4. 'Fill Range' 버튼을 클릭합니다. 프로그램은 선택한 모든 워크시트에서 선택한 셀 값(예: B8)을 즉시 추출하여 각각 마스터 워크시트에 자신의 행에 나열합니다. 완료되면 대화 상자를 닫고 요약 시트 서식을 필요에 따라 조정할 수 있습니다.
이 접근 방식은 많은 수의 시트를 다루거나, 시트 이름이 불규칙하고 수작업 참조 또는 공식 조정이 실용적이지 않을 때 특히 유용합니다. 이 과정은 시각적이고 따라하기 쉬워 워크북 통합 작업을 훨씬 더 쉽게 만듭니다.
Kutools for Excel을 지금 다운로드하고 무료로 사용해보세요!
추가 기사:
- 기준에 따라 여러 워크시트에서 행 복사하여 새 시트로 이동하기
- 여러 워크시트가 포함된 통합 문서가 있다고 가정하고, 각 워크시트가 동일한 형식을 공유한다고 가정합니다. 만약 C열에 “Completed” 텍스트가 포함된 모든 행을 새 워크시트로 복사하려는 경우 이 가이드는 효율적으로 이를 달성하는 데 도움이 될 것입니다. 수작업으로 복사하고 붙여넣기를 할 필요가 없으므로 상당한 시간을 절약할 수 있습니다.
- 여러 워크시트에서 고유한 값 목록 만들기
- 통합 문서의 모든 워크시트에서 고유한 값 목록을 빠르게 작성하는 방법을 찾고 있습니까? 예를 들어 여러 시트에 겹치는 이름 목록이 포함된 경우, 이 기술은 각 고유한 이름을 단일 새로운 목록으로 추출하여 분석할 수 있도록 도와줍니다.
- 다양한 워크시트에서 특정 값 카운트하기
- 여러 워크시트에 유사한 데이터가 포함되어 있고, 모든 시트에서 특정 값(예: “Excel”)이 나타나는 횟수를 세려는 경우 이 문서는 사용할 수 있는 효과적인 접근 방식을 보여줍니다.
- 여러 워크시트에 동일한 이미지 삽입하기
- 한 워크시트에 이미지를 삽입하는 것은 쉽지만, 워크북의 모든 워크시트에 동일한 이미지를 표시하려면 어떻게 해야 할까요? 이 가이드는 몇 가지 단계로 이를 효율적으로 달성하는 방법을 보여줍니다.
- Excel에서 여러 워크시트에서 동시에 동일한 매크로 실행하기
- 기본적으로 매크로를 실행하면 활성 시트만 처리되지만, 여러 시트에 한 번에 적용해야 할 수도 있습니다. 이 문서에서는 여러 워크시트에서 동시에 동일한 매크로를 실행하는 방법을 보여주어 효율성을 높입니다.
최고의 오피스 생산성 도구
| 🤖 | Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행 | 코드 생성 | 사용자 정의 수식 생성 | 데이터 분석 및 차트 생성 | Kutools Functions 호출… |
| 인기 기능: 중복 찾기, 강조 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 또는 셀 병합 | 반올림(수식 없이) ... | |
| 슈퍼 LOOKUP: 다중 조건 VLOOKUP | 다중 값 VLOOKUP | 다중 시트 조회 | 퍼지 매치 .... | |
| 고급 드롭다운 목록: 드롭다운 목록 빠르게 생성 | 종속 드롭다운 목록 | 다중 선택 드롭다운 목록 .... | |
| 열 관리자: 지정한 수의 열 추가 | 열 이동 | 숨겨진 열의 표시 상태 전환 | 범위 및 열 비교 ... | |
| 추천 기능: 그리드 포커스 | 디자인 보기 | 향상된 수식 표시줄 | 통합 문서 & 시트 관리자 | 자동 텍스트 라이브러리 | 날짜 선택기 | 데이터 병합 | 셀 암호화/해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵게/이탤릭/취소선 필터 등) ... | |
| 15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...) | 50+ 종류의 차트(간트 차트, ...) | 40+ 실용적 수식(생일을 기반으로 나이 계산, ...) | 19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...) | 12 가지 변환 도구(단어로 변환하기, 통화 변환, ...) | 7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...) | ... 등 다양 |
Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다. 가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...
Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다
- Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
- 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
- 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!
모든 Kutools 추가 기능. 한 번에 설치
Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.
- 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
- 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
- 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
- 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
- 최고의 가성비 — 개별 추가 기능 구매 대비 절약