이 글에서는 엑셀(Excel)에서 3D 참조가 제대로 작동하지 않는 이유를 살펴봅니다. 데이터 배치 방향이나 셀에 오류 값이 존재하는 경우, 3D 참조는 데이터를 다룰 때 항상 효과적으로 작동하지 않습니다. 이러한 문제의 가능한 원인들을 하나씩 짚어보고, 각 문제에 대한 해결 방법까지 함께 소개해 드리겠습니다.
실습용 통합 문서는 아래에서 다운로드할 수 있습니다.
엑셀에서 3D 참조란 무엇일까요?
엑셀의 3D 참조는 여러 개의 워크시트에 걸쳐 동일한 셀 또는 셀 범위를 연결하는 기능입니다. 같은 형식으로 구성된 여러 워크시트의 데이터를 빠르고 간편하게 합산할 수 있는 방법이죠. 엑셀의 '통합(Consolidate)' 기능을 사용하는 대신 3D 참조를 활용하면 더욱 효율적으로 작업할 수 있습니다.
엑셀에서 3D 참조가 작동하지 않는 3가지 원인과 해결 방법
이번 글에서는 엑셀에서 3D 참조가 작동하지 않는 3가지 원인을 다룹니다. 먼저 각 문제의 원인을 설명한 후, 이를 해결하는 방법을 단계별로 안내해 드립니다.
원인 1: 워크시트가 연속적으로 배치되어 있지 않은 경우
엑셀 워크시트들이 연속적이지 않으면 3D 참조가 제대로 작동하지 않습니다. 정확히 말하면, 참조 수식에 지정된 워크시트 순서와 실제 통합 문서 내 워크시트 순서가 일치하지 않는 경우입니다. 아래 단계를 통해 확인해 보겠습니다.
단계:
- 먼저 Total이라는 이름의 시트에서 셀 C5에 2019년부터 2021년까지 John의 총 판매 금액을 계산하려고 합니다.
- 그런데 현재 워크시트들이 연속적으로 배치되어 있지 않습니다.
- Total 시트로 이동합니다.
- 해당 셀에 다음 수식을 입력합니다.
=SUM('2019:2021'!C5)
- Enter 키를 누릅니다.
- 수식이 결과를 반환하기는 하지만 올바른 값이 아닙니다. 위 수식은 2020 시트의 C5 값을 건너뛰었는데, 그 이유는 수식이 2019:2021 범위를 참조하는데 실제 2020 시트가 두 시트 사이에 위치하지 않기 때문입니다.
해결 방법:
이 문제를 해결하려면 아래 단계를 따르세요.
단계:
- 먼저 수식에서 사용되는 대로 워크시트를 연속적으로 배치합니다. 2020 시트를 드래그하여 2019 시트와 2021 시트 사이로 옮깁니다.
- 다음으로 Total 시트로 이동하면, 셀 C5의 값이 자동으로 수정된 것을 확인할 수 있습니다.
- 이후 채우기 핸들(Fill Handle) 도구를 셀 C5에서 C8까지 드래그합니다.
- 마지막으로 아래 이미지와 같은 결과를 얻게 됩니다.
원인 2: 참조 수식 범위의 셀에 오류 값이 포함된 경우
엑셀에서 3D 참조가 작동하지 않는 또 다른 이유는 작업 중인 데이터 집합에 하나 이상의 오류 값이 존재하기 때문입니다. 단계별로 자세히 살펴보겠습니다.
단계:
- 먼저 2020 시트의 셀 C5에 #DIV/0! 오류가 표시되어 있습니다.
- 다음으로 Total 시트로 이동합니다.
- 아래 이미지에서 볼 수 있듯이, 셀 C5에 입력된 참조 수식이 #DIV/0! 오류를 반환합니다.
- 즉, 수식 범위 내 어떤 셀에든 오류 값이 포함되어 있으면 3D 참조는 정상적으로 작동하지 않습니다.
해결 방법:
아래 단계를 따라 문제를 해결하세요.
단계:
- 오류 값이 들어 있는 2020 시트의 셀 C5로 이동합니다.
- 아래 이미지처럼 해당 셀에 유효한 값을 입력합니다.
- 그런 다음 Total 시트로 돌아갑니다.
- 마지막으로 아래 이미지에서 원하는 결과를 확인할 수 있습니다.
원인 3: 모든 시트의 데이터 패턴이 동일하지 않은 경우
대부분의 경우 3D 참조 수식은 일단 결과를 반환합니다. 하지만 그 결과가 올바르지 않거나 원하는 값이 아닐 수 있습니다. 이는 참조되는 워크시트들 간의 데이터 패턴 차이 때문에 발생합니다. 예를 들어, 아래 데이터 집합에서 다른 워크시트에 John의 총 판매 금액을 계산한다고 가정해 보겠습니다. 잘 살펴보면 모든 워크시트의 데이터 패턴이 동일하지 않다는 것을 알 수 있습니다. 이를 위해 별도의 워크시트에서 이 시트들을 참조해 보겠습니다.
이 문제를 확인하는 단계를 살펴보겠습니다.
단계:
- 먼저 Total이라는 이름의 시트로 이동합니다.
- 해당 셀에 다음 수식을 입력합니다.
=SUM('2019:2021'!C5)- Enter 키를 누릅니다.
- 셀 C5에 $44,500이라는 값이 나타납니다.
- 하지만 이것은 우리가 원하는 값이 아닙니다. 이 수식은 2020 시트의 C5 셀까지 계산했는데, 그 값은 Rick의 판매 금액이기 때문입니다.
해결 방법:
아래 단계를 따라 이 문제를 해결할 수 있습니다.
단계:
- 먼저 영업사원 배열을 다른 두 개의 시트와 동일한 패턴으로 정렬합니다.
- 다음으로 Total 시트로 이동합니다.
- 그러면 셀 C5의 값이 $44,500에서 $47,500으로 자동 업데이트됩니다. 업데이트된 값이 우리가 원하는 올바른 값이며, 이전 값은 잘못된 것이었습니다.
- 이후 채우기 핸들(Fill Handle) 도구를 셀 C5에서 C8까지 드래그합니다.
- 마지막으로 아래 이미지와 같은 결과를 얻게 됩니다.
마무리
정리하자면, 이 튜토리얼을 통해 엑셀에서 3D 참조가 작동하지 않는 이유를 명확하게 이해하셨기를 바랍니다. 실력을 점검해 보려면 이 글에 포함된 실습용 워크시트를 활용해 보세요. 궁금한 점이 있다면 아래에 댓글을 남겨 주시면 최대한 빠르게 답변드리겠습니다. 앞으로도 더 유용한 마이크로소프트 엑셀(Microsoft Excel) 활용 팁을 계속 소개해 드릴 예정이니 많은 관심 부탁드립니다.