목차
기본편에서 다룬 참조 외에 3가지 참조를 더 소개해 드리고자 합니다. 3차원 참조, 외부참조, 구조적 참조입니다.
3차원 참조는 다른 시트의 같은 영역, 외부참조는 다른 워크북(엑셀파일), 구조적 참조는 엑셀 테이블에서 참조하는 방식입니다.
엑셀 테이블은 일반적인 스프레드시트와 비교하여 많은 이점을 제공합니다. 일단, 행과 열의 간단한 구조는 데이터를 쉽게 읽고 이해할 수 있습니다. 또한 테이블은 열 헤더를 통해 각 열의 데이터 유형을 나타내고, 행 헤더를 통해 각 항목의 고유한 식별자를 제공합니다.
참조기능에 대해 잘 모르신다면 아래 링크를 통해 기본사항을 읽어보시고 오시길 권해드립니다.
엑셀 참조기능을 이용해 업무 효율 높이기(기본편)
엑셀은 기본적으로 데이터를 다루는 것에 특화되어 있기 때문에 참조기능은 아주 많은 곳에서 쓰입니다. 참조란, 엑셀에서 데이터를 저장하고 있는 위치를 나타내는 것입니다. 엑셀에서는 참조
dolpali.tistory.com
1. 3차원 참조
3차원 참조는 여러 개의 워크시트에서 셀을 참조할 때 사용하는 방식입니다.
예를 들어, 워크시트1의 A1 셀과 워크시트2의 A1 셀에 각각 "10"이라는 값을 입력한 후, 워크시트3의 A1 셀에 "=SUM(Sheet1:Sheet2!A1)"이라는 수식을 입력하면, 워크시트3의 A1 셀은 워크시트1과 워크시트2의 A1 셀의 합계인 "20"이 출력됩니다.
3차원 참조는 기본적으로 다른 시트의 구조가 같은 경우 사용합니다.
왜냐하면 같은 위치의 셀을 참조하기 때문입니다.
3차원 참조에 사용되는 시트의 중간에 다른 시트가 추가된다면 자동으로 해당 값도 참조하기 때문에 변화에 좀 더 유연하게 대처할 수 있습니다.
예를 좀 더 들어보겠습니다.
지급액 시트에 있는 1월의 총지급액이 입력되어야 하는 B2에 제임스, 제인, 마이클의 1월 값의 합이 들어가게 하려고 합니다.
이런 경우 위 그림처럼 가져올 수 있다면 관리가 훨씬 수월하겠죠.
이런 경우는 지급액 시트의 B2에 아래처럼 입력합니다.
=SUM(제임스:마이클!B2)
위 수식은 "제임스 시트에서 마이클 시트까지 B2에 있는 값을 가져와 더해라(SUM)"라는 뜻입니다.
제임스~마이클 사이에 다른 시트가 추가된다면 그 시트의 B2값도 가져오게 됩니다.
2. 외부참조
외부참조는 다른 워크북의 셀을 참조할 때 사용하는 방식입니다. 셀 참조에 파일 경로와 파일명을 추가하여 사용합니다. 예를 들어, 워크북1에서 A1 셀에 "10"이라는 값을 입력하고, 워크북2에서 A1 셀에 "=워크북1.xlsx!A1"이라는 수식을 입력하면, 워크북2의 A1 셀은 워크북1의 A1 셀의 값인 "10"이 출력됩니다.
3차원 참조의 예시에서는 여러 사람의 한 달 지급액을 사람별로 시트를 만들어 관리했습니다.
이 파일 하나가 어떤 지점의 파일이라면 지점이 여러 개 있는 본사에서는 비슷한 여러 개의 파일을 가질 수 있고 전체 지급액을 알고 싶을 수 있습니다.
각각의 파일을 열어서 값을 더 할 수도 있지만 그렇게 해서는 효율이 안 생기겠죠.
그래서 이번에는 외부 참조를 사용하여 다른 파일의 값을 가져와 보겠습니다.
위 그림과 같이 체인점의 전체 지급액을 계산하는 엑셀과 여의도점, 종로점에서 받은 지급액 파일이 있다고 가정해 보겠습니다.
각 지점 파일은 3차원 참조에서 사용했던 파일과 같은 양식입니다.
"체인전체지급액" 파일에서 지급액을 더해야 할 셀을 클릭하고 아래 수식을 입력합니다.
또는 "="까지 입력하고 열려 있는 파일에서 참조할 위치를 클릭해도 됩니다.
=[종로점.xlsx]지급액!$B$2+[여의도점.xlsx]지급액!$B$2
"[종로점.xlsx]지급액"은 "종로점.xlsx"이라는 엑셀 파일을 참조하라는 뜻입니다.
"!$B$2"은 B2위치의 값을 절대 참조해서 가져오라는 뜻입니다.
이런식으로 하면 그 다음 수식도 여의도점.xlsx 파일의 B2위치의 값을 절대값으로 가져와 두 값을 더하라는 뜻인 것을 알 수 있습니다.
190만원+330만원에 대한 결과로 520만원이 "체인전체지급액.xlsx"파일에 나오니 원하는 대로 되었습니다.
3. 구조적 참조(테이블 참조)
구조적 참조는 위에서 설명한 다른 참조들과는 조금 다릅니다.
다른 시트나 파일의 값을 참조하는 것이 아닌 엑셀 테이블에서 참조하는 방식을 말합니다.
구조적 참조는 테이블의 크기가 변경되더라도 참조 범위가 자동으로 조정되므로 유용합니다. 예를 들어, 엑셀 테이블에서 A열에 "10", "20", "30"을 입력하고, B열에 "100", "200", "300"을 입력한 후, "=SUM(Table1[Column1])"이라는 수식을 입력하면, 테이블의 A열의 값인 "10", "20", "30"의 합계인 "60"이 출력됩니다.
해당 테이블 영역에서만 사용하므로 좀 더 수식이 간단해집니다.
구조적 참조 자체는 간단하지만 테이블이라는 개념을 모르실 수도 있어 간단히 설명하고 넘어가겠습니다.
엑셀에 위와 같은 데이터가 입력되어 있다고 예를 들어 보겠습니다.
1. 테이블로 지정하고 싶은 영역을 드래그하여 선택합니다.
2. [홈] 탭을 클릭합니다.
3. [표 서식]을 클릭합니다.
4. 원하는 서식을 선택합니다.
5. [확인]을 클릭하여 적용시킵니다.
이렇게 하면 지정했던 범위에 보기 좋게 꾸며진 것 뿐만 아니라 구조적 참조를 사용할 준비가 된 것입니다.
테이블로 만든 영역에서 1월의 지급액의 합계를 구하고 싶다면 B8에 SUM 함수와 열 이름을 입력해주기만 하면 됩니다.
=SUM([1월])
이렇게 수식이 간단하고 보기 편해집니다.
행도 마찬가지로 이름을 이용하여 관리할 수 있으며 다른 테이블이나 시트에서 이 테이블을 참조할 때에도 테이블 이름으로 참조하기 때문에 수식이 간단해집니다.
'업무 프로그램 > MS 엑셀' 카테고리의 다른 글
| 엑셀 차트를 이용해 데이터를 강조 및 축소 하기 (0) | 2023.03.04 |
|---|---|
| 엑셀 피벗(Pivot)으로 데이터 분석을 더 쉽고 빠르게! (0) | 2023.03.03 |
| 엑셀 참조기능을 이용해 업무 효율 높이기(기본편) (0) | 2023.03.01 |
| 엑셀에서 큰 수, 작은 수를 순서대로 구하기 (0) | 2023.02.19 |
| 엑셀 데이터 유효성 검사 기본(입력값 제한, 검증) (0) | 2023.02.12 |
