Excel에서 구조적 참조를 만드는 방법은 무엇입니까?
구조적 참조는 엑셀 테이블로 시작합니다. Excel에서 테이블이 생성되는 즉시 자동으로 구조화 된 참조가 생성됩니다.
이제 아래 이미지를 살펴보십시오.
- 1 단계 : B3 셀에 대한 링크를 지정했습니다. 링크를 B2로 표시하는 대신 Table1 [@Sales]로 표시합니다. 여기서 Table1 은 테이블의 이름이고 @Sales 는 우리가 참조하는 열입니다. 이 열의 모든 셀은 테이블 이름과 열 머리글 이름으로 참조됩니다.
- 2 단계 : 이제 테이블 이름을 Data_Table 로 변경하고 열 머리글을 Amount로 변경합니다 .
- 3 단계 : 테이블 이름을 변경하려면 테이블 내부에 커서를 놓고 디자인> 테이블 이름으로 이동합니다.
- 4 단계 : 테이블 이름을 Data_Table 로 언급합니다 .
- 5 단계 : 이제 변경하여 B3 셀에 대한 참조를 제공합니다.
따라서 구조화 된 참조에는 테이블 이름과 열 이름의 두 부분이 있음을 이해했습니다 .
예
이 구조적 참조 Excel 템플릿은 여기에서 다운로드 할 수 있습니다. – 구조적 참조 Excel 템플릿예 1
구조화 된 참조를 사용하면 수식을 동적으로 만들 수 있습니다. 일반 셀 참조와 달리 데이터 범위에서 추가 및 삭제시 수식을 사용할 수 있습니다.
정상 범위와 엑셀 테이블 모두에 SUM 공식을 적용하겠습니다.
정상 범위에 대한 SUM 공식.
Excel 표에 대한 SUM 공식.
일반 테이블과 엑셀 테이블의 데이터에 몇 줄을 추가하겠습니다. 데이터에 2 개의 광고 항목을 추가했습니다. 이제 차이점을 확인하세요.
Excel 표의 구조적 참조는 업데이트 된 값을 표시하지만 수식을 수동으로 변경하지 않는 한 일반 데이터 범위에는 업데이트 된 값이 표시되지 않습니다.
예제 # 2
이제 한 가지 더 예를 살펴 보겠습니다. 제품 이름, 수량 및 가격 정보가 있습니다. 이 정보를 사용하여 판매 가치에 도달해야합니다.
판매 가치를 얻기위한 공식은 Qty * Price 입니다. 이 공식을 표에 적용 해 보겠습니다.
수식에 [@QTY] * [@PRICE]가 표시됩니다. 이것은 B2 * C2 의 일반적인 참조보다 더 이해하기 쉽습니다. 테이블 안에 수식을 넣는 경우 테이블 이름을 얻지 못합니다.
Excel 구조적 참조의 문제
구조화 된 참조를 사용하는 동안 아래 나열된 몇 가지 문제에 직면합니다.
문제 # 1
구조화 된 참조에도 자체 문제가 있습니다. 우리는 모두 엑셀 수식을 적용하고 나머지 다른 셀로 복사하거나 드래그하는 데 익숙합니다. 이것은 구조적 참조의 동일한 프로세스가 아니며 약간 다르게 작동합니다.
이제 아래의 예를보십시오. 정상 범위에 대해 Excel에서 SUM 공식을 적용했습니다.
가격과 판매 값을 합산 하려면 현재 수식을 복사하여 붙여 넣거나 다른 두 셀에 끌어서 놓으면 가격 및 판매 값의 SUM 값이 제공됩니다.
이제 Qty 열의 Excel 테이블에 동일한 공식을 적용하십시오.
이제 Qty 열의 합계를 얻었습니다. 일반 범위와 마찬가지로 수식은 현재 수식을 복사하고 가격 열에 붙여 넣어 총 가격을 가져옵니다.
세상에!!! 가격 열의 합계를 표시하지 않고 Qty 열의 합계 만 표시합니다. 따라서이 수식을 복사하여 인접한 셀이나 다른 셀에 붙여 넣어 상대 열이나 행을 참조 할 수 없습니다.
수식을 끌어 참조 변경
이제 우리는 그 한계를 알았으므로 구조화 된 참조로 더 이상 복사-붙여 넣기 작업을 수행 할 수 없습니다. 그렇다면이 한계를 어떻게 극복 할 수 있을까요?
해결책은 매우 간단합니다. 복사하는 대신 수식을 드래그하면됩니다. 수식 셀을 선택하고 채우기 핸들을 사용하여 나머지 두 셀로 끌어 열 참조를 Price 및 Sale Value로 변경합니다.
이제 우리는 각각의 합계를 얻기 위해 공식을 업데이트했습니다.
문제 # 2
구조 참조에 대한 한 가지 문제를 보았고 해결책도 찾았지만 여기에 한 가지 더 문제가 있습니다. 수식을 다른 셀로 드래그하는 경우 절대 참조로 호출 할 수 없습니다.
이제 아래 예제를 살펴 보겠습니다. 여러 항목이있는 판매 테이블이 있고 Excel에서 SUMIF 함수를 사용하여 데이터를 통합하고 싶습니다.
이제 SUMIF 함수를 적용하여 각 제품의 통합 판매 값을 가져옵니다.
구조화 된 참조이므로 나머지 두 열에 수식을 복사하여 붙여 넣을 수 없기 때문에 1 월의 수식을 적용했습니다. 그러면 참조가 2 월과 3 월로 변경되지 않으므로 수식을 드래그합니다.
오!! 2 월 및 3 월 열에서 값을 얻지 못했습니다. 무엇이 문제일까요 ??? 공식을 면밀히 살펴보십시오.
우리는 1 월 달의 공식을 끌어 왔습니다. SUMIF 함수에서 첫 번째 인수는 Sales _Table [Jan]로 변경된 수식을 끌어 왔으므로 Criteria Range Sales_Table [Product ]입니다.
그래서 우리는 그것을 어떻게 처리합니까 ?? 첫 번째 인수, 즉 Product 열을 절대로 만들고 다른 열을 상대 참조로 만들어야합니다. 일반 참조와 달리 참조 유형을 변경하기 위해 F4 키를 사용할 수 없습니다.
해결책은 아래 이미지와 같이 참조하는 열을 복제해야한다는 것입니다.
이제 수식을 다른 두 개의 열로 드래그 할 수 있습니다. 기준 범위는 일정하며 다른 열 참조는 그에 따라 변경됩니다.
프로 팁 : ROW를 절대 참조로 만들려면 이중 ROW 항목을 만들어야하지만 ROW 이름 앞에 @ 기호를 넣어야합니다.
= Sales_Table [@ [제품] : [제품]]
Excel에서 구조적 참조를 해제하는 방법은 무엇입니까?
구조화 된 참조의 팬이 아닌 경우 아래 단계에 따라 끌 수 있습니다.
- 1 단계 : 파일> 옵션으로 이동합니다.
- 2 단계 : 수식> 수식에서 테이블 이름 사용을 선택 취소 합니다.
기억해야 할 사항
- 구조적 참조에서 절대 참조를 만들려면 열 이름을 두 배로 늘려야합니다.
- 구조적 참조의 공식을 복사 할 수 없습니다. 대신 공식을 드래그해야합니다.
- 구조화 된 참조에서 참조하는 셀을 정확히 볼 수 없습니다.
- 구조화 된 참조에 관심이없는 경우 해제 할 수 있습니다.