Microsoft Excel에서 구조화된 참조를 활용하기 위한 4가지 효과적인 방법

Excel 사용은 종종 서로 다른 데이터 집합 간의 관계를 식별하는 데 중점을 둡니다. 그러나 복잡한 수식을 삽입할 때 상대 및 절대 명시적 셀 참조(예: “B7” 및 그 변형)를 과도하게 사용하면 수식 막대가 어수선하고 혼란스러워질 수 있습니다.

Excel에서 구조화된 참조를 활용하면 테이블과 헤더에 이름을 지정할 수 있으므로 이 프로세스를 개선할 수 있습니다. 이러한 지정된 이름은 암묵적 셀 참조 역할을 하여 Excel에서 구조화된 데이터를 자동으로 검색하고 계산할 수 있습니다.

Excel에서 구조화된 참조를 사용하는 효과적인 방법은 다음과 같습니다.

1. 테이블 내에서 계산 수행

구조적 참조는 표에서만 작동하므로, 구조적 참조를 활용하는 가장 좋은 방법은 이러한 유형의 표 내에서 사용하는 것입니다.

예를 들어, 매장의 판매 정보를 포함하는 B2에서 F8까지의 간단한 표를 만듭니다. 표에 “판매”라는 레이블을 붙이세요(왼쪽 상단 모서리에 있는 “표 이름”을 확인하세요).

다음으로, 각 개별 판매에 대한 총액을 계산합니다.

1단계: 셀 F2를 선택합니다(드롭다운 아이콘이 클릭되지 않았는지 확인합니다). “홈”으로 이동한 다음 “삽입”을 선택하고 “오른쪽에 표 열 삽입”을 선택하여 표에 새 열을 자동으로 포함합니다.

2단계: 새 열 G의 머리글을 “총계”로 지정합니다.

3단계: 셀 G3에 입력 =[@PricePerUnit]*[@Quantity]하고 Enter를 누릅니다. 필요에 따라 셀 서식을 조정합니다.

참조 “[@PricePerUnit]” 및 “[@Quantity]”는 열 내의 해당 필드에 해당합니다. 열 이름 앞에 있는 “@” 기호는 각 결과 셀이 테이블의 동일한 행에서 값을 파생한다는 것을 나타냅니다.

명확히 하자면, =[@PricePerUnit]*[@Quantity]G3의 공식은 실제로 에 대응합니다 =$C3*$D3.

2. 테이블 너머의 범위 검색

테이블 밖에 있는 셀에서 구조화된 참조를 활용하려는 경우 참조를 테이블 이름으로 시작해야 합니다. 예를 들어, 이전 예에서 “Sales[Total]”을 사용하면 “Sales” 테이블에서 “Total” 열 아래의 전체 값 범위를 검색합니다. 이렇게 하면 추가 조작을 위한 여러 값의 배열이 제공됩니다.

다음은 Excel에서, 구체적으로 셀 I3에 나타나는 방식입니다. 여기서는 범위가 아래로 확장될 수 있는 충분한 공간이 남아 있습니다.

3. 열 합산 및 부분 합산

전체 열의 빠른 합계를 얻으려면 “표 디자인” 설정(“표 스타일 옵션” 내)에 있는 “총계 행” 체크박스를 활용하세요. “수량”과 “총계” 열에 대한 합계를 구하는 방법은 다음과 같습니다.

‘합계’ 행은 다른 위치로 이동할 수 없고 지속적으로 표의 끝에 위치하지만(추가 항목 허용) 다른 곳에 결과를 복제할 수 있습니다.

“합계” 열에 있는 모든 항목의 합계를 계산하려면 .을 사용합니다 =SUM(Sales[Total]).
표 필터링 후 나타나는 항목과 같이 보이는 항목의 합계만 구하려면 를 적용합니다 =SUBTOTAL(109,Sales[Total]). 이 공식은 표 서식의 “총계 행” 옵션이 행에 대해 수행하는 작업을 반영합니다.

또한, 표에서 찾은 특정 기준에 따라 부분 합계를 계산할 수도 있는데, 서식을 지정할 필요가 없습니다. 예를 들어:

Mike에게 귀속된 총 매출을 찾으려면 =SUMIF(Sales[Seller]," Mike" ,Sales[Total]). 여기서 “Mike”는 수동으로 입력된 문자열입니다.
제품 ID “41230”으로 표현된 모든 제품을 합산하려면 =SUMIF(Sales[ProductID],41230,Sales[Total]). ProductID 열이 “일반”으로 포맷되어 있으므로 숫자를 직접 입력할 수 있습니다.

4. INDIRECT를 사용하여 테이블에서 데이터 검증 구현

이전에 언급한 Sales 테이블로 작업한다고 가정하면 사용자 지정 데이터 검증 설정을 설정하여 데이터를 더 쉽게 검색할 수 있습니다. 제품 ID, 날짜 또는 판매자를 선택할 수 있는 더 작은 테이블을 만들어 이러한 세트에서 단일 항목을 선택하여 소계를 표시해 보겠습니다.

1단계: 셀 B13에서 데이터 유효성 검사를 설정합니다(데이터 탭 > 데이터 도구 > 데이터 유효성 검사).