Excel에서 여러 워크시트의 동일한 셀을 참조하는 방법은 무엇입니까?

여러분이 여러 워크시트를 포함하고 있는 통합 문서가 있다고 가정해 봅시다. 이제 여러 워크시트에 걸쳐 동일한 셀 내의 데이터를 하나의 주요 워크시트로 추출하려고 합니다. 예를 들어, Sheet1, Sheet2, Sheet3, Sheet4...에서 B8 셀을 가져와 다음 스크린샷과 같이 주요 시트로 정리합니다. Excel에서 이 작업을 어떻게 신속하고 쉽게 해결할 수 있을까요?

수식을 사용하여 여러 워크시트의 동일한 셀을 하나의 주요 시트로 참조하기

VBA 코드를 사용하여 여러 워크시트의 동일한 셀을 하나의 주요 시트로 참조하기

놀라운 기능을 사용하여 여러 워크시트의 동일한 셀을 하나의 주요 시트로 참조하기

수식을 사용하여 여러 워크시트의 동일한 셀을 하나의 주요 시트로 참조하기

워크시트 이름이 기본 시트 이름인 경우, 즉 Sheet1, Sheet2, Sheet3...이라면 Excel에서 이 작업을 빠르게 처리하기 위해 수식을 사용할 수 있습니다.

1. 먼저, 도움이 되는 숫자 목록을 만들어야 합니다. 1, 2, 3, 4...과 같은 순차적인 번호를 입력하세요. 이 번호들은 참조해야 할 워크시트를 나타냅니다. 아래 스크린샷을 참조하세요:

2. 그런 다음, 데이터를 추출하려는 셀에 아래 수식을 입력하고, 원하는 범위까지 채우기 핸들을 드래그하면 여러 워크시트에서 동일한 셀 값이 모두 추출됩니다. 아래 스크린샷을 참조하세요:

참고: 위 수식에서 E2는 1단계에서 입력한 도움이 되는 숫자이며, B8은 추출하려는 셀 참조입니다. 이 수식은 워크시트 이름이 Sheet1, Sheet2, Sheet3...인 경우에만 잘 작동합니다.

VBA 코드를 사용하여 여러 워크시트의 동일한 셀을 하나의 주요 시트로 참조하기

Sheet1, Sheet2 대신 다양한 내용으로 된 워크시트 이름이 많다면, 다음 VBA 코드를 사용하여 여러 워크시트에서 동일한 셀 참조를 한 번에 하나의 워크시트로 채울 수 있습니다.

1. 마스터 워크시트에서 다른 워크시트로부터 추출하려는 셀 B8을 클릭하세요.

2. ALT + F11 키를 누르면 Microsoft Visual Basic for Applications 창이 열립니다.

3. 삽입 > 모듈을 클릭하고 모듈 창에 다음 코드를 붙여넣으세요.

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 Sub

4. 그런 다음 F5 키를 눌러 이 코드를 실행하면, 다른 모든 워크시트에서 B8 셀의 값이 마스터 워크시트로 가져옵니다. 스크린샷을 참조하세요:

참고: 이 VBA 코드는 클릭한 셀에 적용됩니다. 예를 들어, 특정 워크시트에서 A1 셀을 클릭하면, 다른 모든 워크시트에서 A1 셀의 값이 이 워크시트로 채워집니다.

놀라운 기능을 사용하여 여러 워크시트의 동일한 셀을 하나의 주요 시트로 참조하기

VBA 코드에 익숙하지 않다면, 여기 유용한 도구인 Kutools for Excel을 소개합니다. 워크시트 참조 자동 증가 기능을 통해 Excel에서 여러 워크시트의 셀 참조를 쉽게 채울 수 있습니다.

Kutools for Excel 설치 후 다음과 같이 하세요:

1. 다른 워크시트로부터 추출하려는 마스터 시트의 B8 셀을 클릭하세요.

2. 그런 다음 Kutools > 더 보기 > 워크시트 참조 자동 증가를 클릭하세요. 스크린샷을 참조하세요:

3. 워크시트 참조 채우기 대화 상자에서 '채우기 순서'에서 '열로 채우고 행으로 채우기'를 선택하고 수식 텍스트 상자 옆의 작은 잠금 아이콘을 클릭하면 회색 잠금이 노란색 잠금으로 바뀝니다. 이는 수식과 셀 참조가 잠겼음을 의미합니다. 그런 다음 다른 워크시트에서 B8 셀 참조를 추출하기 위해 아무 셀이나 클릭할 수 있습니다. 이 예에서는 B2 셀을 클릭하겠습니다. 그런 다음 셀 참조를 추출할 워크시트를 선택하세요. 스크린샷을 참조하세요:

참고: 이 작은 잠금을 잠그지 않으면 선택한 셀에 따라 셀 참조가 변경됩니다.

4. 그런 다음 '범위 채우기' 버튼을 클릭하면 다른 워크시트에서 B8 셀의 값이 마스터 워크시트에 세로로 나열되고, 대화 상자를 닫습니다. 스크린샷을 참조하세요:

지금 바로 Kutools for Excel을 다운로드하고 무료로 사용해 보세요!

추가 글: