들어가기 전에
Excel은 복잡한 데이터 표를 만들 수 있는 프로그램으로 잘 알려져 있습니다. 그러나 이러한 표와 함께 제공되는 특별한 유형의 참조, 즉 구조화된 참조(Structured Reference)에 대해서는 알고 있는 사람이 많지 않는 것 같습니다. Excel 2016 이상에서 사용할 수 있는 Excel의 구조적 참조에 대해 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Everything You Need to Know About Structured References in Excel
- URL: https://www.howtogeek.com/structured-references-in-excel-explained/
Excel의 "구조적 참조"란?
Excel의 직접 셀 참조는 열 및 행 머리글 레이블을 사용하여 한 셀을 다른 셀에 연결합니다. (예를 들어, A3 셀에 A1과 A2 셀의 값(숫자)를 더하려 한다면 A3 셀에 =A1+A2 이런 수식을 사용합니다.
반면에 구조화된 참조는 열과 행 머리글 레이블을 사용하는 대신 표와 열 이름을 사용합니다 예를 들어, 다음 수식은 은 'Daily profit'이라는 열의 각 셀에 있는 값을 가져와 7을 곱합니다.
=SUM([@[Daily profit]]*7)
표 내에서 구조적 참조 사용
위의 예에서처럼 표 내에서 구조화된 참조를 사용하려면 먼저 Excel 워크시트에 데이터를 추가하고 이 데이터의 상단에 열 이름을 포함시켜야 합니다. 열 이름을 추가하지 않으면 Excel은 기본적으로 열 1, 열 2 등으로 표시되므로 나중에 수식에 머리글을 사용하려는 경우 훨씬 명확하지 않습니다.
데이터를 사용하여 계산을 만들기 전에 데이터 내의 셀을 선택하고, [홈] 탭 - [스타일] 그룹 - [표 서식]을 클릭합니다. 다양한 표 스타일 중에서 마음에 드는 디자인을 선택합니다.
[표 만들기] 대화 상자에서 모든 데이터가 선택되었는지 확인하고 [머리글 포함] 확인란을 선택한 다음, [확인]을 클릭합니다.
이제 데이터가 서식이 지정된 Excel 표로 만들어졌으므로 구조화된 참조를 사용할 준비가 되었습니다.
이 예제에서는 셀 C2(계산을 만들려는 첫 번째 셀)를 선택하고 다음과 같이 입력합니다.
=SUM(
'Daily profit' 열의 데이터가 포함된 첫 번째 셀을 클릭하면 Excel에서 해당 열에 대한 구조화된 참조를 삽입합니다.
=SUM([@[Daily profit]]]
사각형 괄호는 구조화된 참조를 사용하고 있음을 나타내는 Excel의 방식이며 @ 기호(교차 연산자라고도 함)는 계산이 표 내의 각 행에 각각 적용됨을 의미합니다.
마지막으로 주별 수익을 생성하기 위해 '일일 수익' 열의 값에 7을 곱하고 둥근 괄호를 닫습니다.
=SUM([@[Daily profit]]*7)
Enter 키를 누르면 수식이 'Weekly profit' 열의 나머지 셀에 자동으로 복사됩니다.
테이블 외부에서 구조적 참조 사용
구조적 참조는 테이블 데이터를 참조하는 Excel 테이블 외부의 수식 내에서도 사용됩니다. 이 예에서는 셀 E2에 명시된 사이트를 기준으로 셀 E4에서 주간 수익을 생성하기 위해 XLOOKUP 수식을 사용합니다.
첫 번째 단계는 테이블 이름을 지정하는 것입니다. 그렇지 않으면 Excel에서 데이터 이름이 Table1로 지정되어 통합 문서에 여러 개의 테이블이 있는 경우 혼란스러울 수 있습니다. 스프레드시트의 다른 곳에서 참조할 의도가 없더라도 Excel에서 테이블을 만들 때마다 이름을 지정하는 습관을 들이는 것이 좋습니다.
이렇게 하려면 표 내의 셀을 선택하고 리본에서 '표 디자인' 탭을 연 다음 표 이름 필드에 적절한 이름을 입력합니다. 여기서는 테이블 이름을 “Profits”라고 하겠습니다.
테이블 이름을 지을 때 지켜야 할 몇 가지 규칙이 있습니다.
- 테이블 이름은 문자, 밑줄(_) 또는 백슬래시(\)로 시작해야 합니다.
- 나머지 테이블 이름은 최대 255자까지 문자, 숫자, 마침표, 밑줄을 조합하여 사용할 수 있습니다.
- Excel에서 다른 용도로 사용되므로 테이블의 이름을 'C', 'c', 'R' 또는 'r'이라고 할 수 없습니다.
- 또한 테이블은 A1 또는 $A$1과 같은 셀 참조와 같을 수 없습니다.
- 참조하기 쉽도록 테이블 이름을 한 단어로 유지하세요. 하지만 두 단어 이상을 사용해야 하는 경우에는 각 단어 사이에 공백이 아닌 밑줄을 사용하세요.
- 통합 문서의 다른 곳에서 테이블 이름이 사용되지 않았는지 확인합니다.
E4 셀에 다음과 같이 입력합니다.
=XLOOKUP(E2
그런 다음 셀 A2에서 A9까지의 조회 배열을 선택해야 합니다. 이 때 수식이 구조화된 참조로 자동 변환되는 것을 볼 수 있는데, 여기서 'Profits'는 앞서 만든 테이블 이름이고 'Site'는 열 머리글입니다.
=XLOOKUP(E2,Profits[Site],
마지막으로, 반환 배열인 C2~C9 셀을 선택하고 둥근 괄호를 닫습니다. 다시 말하지만, Excel은 이를 구조화된 참조로 변환합니다.
=XLOOKUP(E2,Profits[Site],Profits[Weekly profit])
Enter 키를 누르면 테이블에서 주간 수익이 성공적으로 검색됩니다.
구조적 참조를 사용하는 이유
Excel에서 테이블을 다룰 때 직접 참조 대신 구조적 참조를 사용하면 여러 가지 이점이 있습니다.
첫째, 구조화된 참조는 직접 참조보다 만들고 읽기가 더 쉽습니다. 따라서 수식을 한 눈에 보는 것만으로도 수식이 무엇을 하려는 것인지 이해할 수 있고, Excel에서 오류가 발생해도 수식을 쉽게 구문 분석할 수 있습니다.
둘째, 오른쪽 아래 모서리에 있는 핸들(아래 스크린샷에서 'A'로 표시)을 클릭하고 끌어서 테이블에 행을 추가한 다음 추가 행을 추가 데이터로 채우면 스프레드시트에서 이미 사용한 구조화된 참조가 이 새 데이터에 자동으로 적용됩니다.
이 예에서는 추가 사이트(10행)에 대한 데이터를 추가했습니다. 일일 수익을 추가했을 때 주간 수익이 자동으로 계산되었을 뿐만 아니라 셀 E2의 사이트 참조를 사이트 I로 변경했더니 데이터가 올바르게 검색되었습니다. 직접 참조를 사용했다면 동일한 결과를 얻기 위해 여러 번 수동으로 조정해야 했을 것입니다.
셋째, 'Daily profit'과 'Weekly profit' 열 사이에 다른 열을 추가하더라도 각 열에 연결된 문자가 아니라 열 이름을 참조했으므로 기존 열에 대한 참조가 안전하게 유지된다는 사실을 알고 안심할 수 있습니다.
마지막으로, 구조화된 참조는 동적입니다. 테이블 열 중 하나의 이름을 변경하면 관련된 모든 구조화된 참조가 그에 따라 업데이트됩니다. 이 예에서는 'Weekly profit' 열을 'TOTALS'로 변경했으며, 셀 E4의 XLOOKUP 수식이 이 변경 사항을 적용했습니다.
Excel에서 표 형태의 데이터를 최대한 활용하기 위해 구조화된 참조를 사용하는 것을 고려할 필요가 있습니다.
마치며
이상에서 소개한 장점 외에도, 구조적 참조는 직접 참조보다 컴퓨터 메모리를 덜 사용하므로 Excel 스프레드시트 속도 향상에도 도움이 될 수 있습니다(크지는 않을 것 같지만).
'Excel' 카테고리의 다른 글
| 데이터를 망칠 수 있는 엑셀의 문제점과 해결 방법 5가지 (4) | 2025.01.16 |
|---|---|
| 엑셀 "채우기 핸들"보다 "Sequence 함수"가 나은 이유 (5) | 2025.01.13 |
| 소규모 기업이 Excel로 CRM을 구축하기 위한 7가지 팁 (3) | 2025.01.12 |
| Excel 이미지 처리 관련 4가지 최신 업데이트 (5) | 2025.01.11 |
| 엑셀에서 VLOOKUP은 잊으세요 ㅡ XLOOKUP을 사용하는 이유 (2) | 2025.01.10 |
