엑셀 배우기(39) - 표(3)

엑셀배우기-표3.xlsx

0.01MB

8. 구조적 참조(structured references)

위 표에서 M3셀에 실중량부터 총금액까지 합계를 구하려고 하는 경우 표로 되어 있으면, =sum(H3:L3)이라고 표시되지 않고 =SUM(운임표[@[실중량]:[총금액]])로 표시됩니다.

두 번째 합계 수식에서 운임표는 표 이름이고, @는 가로(행) 합계 시 사용하는 기호이고, 실중량과 총금액은 열 이름입니다.

이와 같이 표 이름과 열 이름의 조합을 구조적 참조라고 합니다.

가. 셀 참조와 구조적 참조

셀 참조	구조적 참조
A1 형식 셀 주소 사용	표 이름과 열 이름 사용
예시 : =sum(H3:L3)	예시 : =SUM(운임표[@[실중량]:[총금액]])
3이 행이 바뀜에 따라 자동으로 달라짐. 그러나, 행 합계가 아래 줄에도 자동으로 채워지는 것은 동일함	행이 바뀌어도 열 이름이 바뀌지 않음. 행 합계가 아래 줄에 자동으로 채워짐

① 행 합계를 구하는 경우
아래는 Ctrl 키를 누른 상태에서 1 왼쪽 키인 `(back tick, grave) 키를 눌러 수식을 표시한 것으로, 열 1의 수식은 =SUM(운임표[@[실중량]:[총금액]]) 으로 모두 동일한데, 열 2의 수식은 =SUM(H3:L3) 식으로 행에 대한 숫자 3이 27까지 하나씩 커지고 있습니다.

② 열 합계를 구하는 경우
행 합계를 구할 때는 표 이름과 열 이름으로 수식이 표시되는데,
실중량 등 열에 대한 합계를 구할 때는 3행부터 26행까지는 셀 참조(주소) 형식으로 수식이 표시되다가

표의 데이터 범위를 모두 선택하게 되면, 다시 말해 3행부터 27행까지 범위를 지정하게 되면 표 이름과 열 이름을 사용한 구조적 참조 수식으로 변경됩니다.

엔터키를 치면 값이 #######이라고 표시되는데, 열 너비가 좁아서 그런 것이므로 H열과 I열 사이의 경계선을 더블 클릭해서 열 너비를 자동 조절합니다.

나. 구조적 참조 구문 규칙

구분	의미
표 이름	표 이름, 기본 표 이름은 표1,2 식으로 되어 있지만 다름 이름으로 변경할 수 있습니다. 여기서는 운임표를 사용하고 있습니다.머리글이나 요약 행을 제외한 표 데이터를 참조합니다.
열 지정자	해당 열의 이름을 사용하는 열 지정자입니다. 열 머리글이나 요약 행을 제외한 열 데이터를 참조합니다. 지정자는 항상 대괄호로 묶습니다.
항목 지정자	#모두, #데이터, #머리글, #요약 등 테이블의 특정 부분을 참조하는 지정자입니다.

- 항목 지정자의 의미

구분	설명	예시
#모두	머리글, 요약행, 데이터를 포함한 표 전체입니다.	i29셀 수식 : =SUM(운임표[#모두])
#데이터	머리글과 요약행을 제외한 데이터 영역을 의미합니다.	=SUM(운임표[#데이터])
#머리글	머리글 부분을 의미합니다.	=SUM(운임표[#머리글])
#요약	요약행을 의미합니다.	j29셀 수식 : =COUNTA(운임표[#요약])

해당 열의 이름을 사용하는 열 지정자입니다. 열 머리글이나 요약 행을 제외한 열 데이터를 참조합니다. 지정자는 항상 대괄호로 묶습니다.

표 이름은 표 이름 그대로 사용하고, 열 지정자와 항목 지정자는 대괄호([]) 사이에 입력하며, 행 합계를 구할 때는 @ 기호를 사용합니다.
예를 들어, i29셀을 선택하고 =sum 하고 표 이름인 운임표를 입력하고 [를 열면 아래와 같이 @ - 이행과 열 이름이 표시되고,

아래로 내려가면 열 이름 아래에 #으로 시작하는 항목 지정자인 #모두, #데이터, #머리글, #요약이 표시됩니다.

#모두 를 선택하기 위해 탭키를 누르고, 대괄호를 닫은 다음 소괄호를 닫고 엔터키를 누르면 데이터와 요약 행을 포함해서 모두 합한 값을 반환합니다.

라. #모두 등 항목 지정자와 열 지정자의 결합

① 단일 열 지정
k29셀에 =sum(운임표[세액])이라고 해도 되지만, #모두 등 항목 지정자를 이용할 때는
=sum(운임표 까지 입력한 다음, 대괄호 열기([)를 2번 입력합니다. 왜냐하면 #데이터 등 항목 지정자와 열 이름 2개가 들어가야 하기 때문입니다.

그리고 아래로 내려가면 #모두 등이 표시되는데 #모두를 선택하면 맨 아래 줄 요약 행이 포함되므로 요약 행을 포함하지 않는다면 #데이터를 사용해야 합니다. #데이터로 내려간 후 탭 키(엔터 키 아님)를 누릅니다.

그러면 =sum(운임표[[#데이터 까지 입력됩니다.

대괄호를 닫고(] 입력), 열 이름을 입력하는데 ,(콤마) 기호를 입력해야 합니다. 따라서 아래와 같이 ,를 입력하고 대괄호를 열면([) 다시 열 이름 등이 표시되므로 세액으로 이동한 후 탭키를 누릅니다.

그러면, =sum(운임표[[#데이터],[세액 까지 입력되는데,

대괄호를 2번 닫은 다음(]]) 소괄호())까지 닫으면 K3셀부터 K27셀까지 세액에 해당하는 데이터 영역만 범위로 설정됩니다.

엔터 키를 누르면 =SUM(운임표[[#데이터],[세액]]) 수식이 입력되고, 값 902,162가 구해집니다.

J29셀의 수식은 =COUNTA(운임표[#머리글]) 로서, counta 함수를 사용했기 때문에 머리글에 해당하는 비어 있지 않은 셀의 개수를 반환하며, A부터 N까지 14가 됩니다.

② 연속된 열 지정
연속된 열을 지정할 때는 :을 입력하고, 떨어진 열을 지정할 때는 ,를 사용하는 것은 셀 주소를 이용하여 합계를 구하는 경우와 같습니다.

K29셀을 복사해서 L29셀에 붙여 넣어도 셀 주소가 없어서 =SUM(운임표[[#데이터],[세액]]) 식으로 수식이 상대적으로 변하지 않습니다.

L29셀을 세액부터 실중량까지로 수정하려면
=SUM(운임표[[#데이터],[세액]:[실중량]])
로 [세액] 다음에 콜론 기호를 넣고 열 이름 [실중량]을 추가하면 됩니다. 오른쪽에서 왼쪽까지 열을 지정했지만 문제없습니다.

③ 떨어진 열 지정
L29셀을 복사한 후 M29셀에 붙여 넣습니다. 실중량과 세액 등 떨어진 열을 지정할 때는 수식에서 :만 ,로 바꿔서 아래와 같이 입력하면 에러가 발생하므로
=SUM(운임표[[#데이터],[세액]],[실중량]])

열 이름 앞에 표 이름과 #으로 시작하는 항목 지정자를 붙이고, 앞의 열 이름([세액]) 지정을 끝내기 위해 대괄호를 하나 추가로 입력해서
=SUM(운임표[[#데이터],[세액]],운임표[[#데이터],[실중량]])
로 만들어야 합니다.

엑셀배우기-표3.xlsx

0.01MB