사이의 통신 엑셀 테이블다른 통합 문서의 셀에서 데이터를 반환하는 수식입니다. 링크가 포함된 통합 문서를 열면 Excel은 원본 통합 문서의 최신 정보를 읽습니다(링크 업데이트).

Excel의 테이블 간 관계는 통합 문서의 다른 시트와 다른 Excel 통합 문서 모두에서 데이터를 검색하는 데 사용됩니다. 예를 들어, 총 매출 금액을 계산하는 테이블이 있습니다. 계산에는 제품 가격과 판매량이 사용됩니다. 이 경우 가격 데이터가 포함된 별도의 테이블을 생성하는 것이 합리적입니다. 이 테이블은 첫 번째 테이블의 연결을 사용하여 가져옵니다.

테이블 간의 관계를 만들 때 Excel에서는 괄호로 묶인 원본 통합 문서 이름, 느낌표 뒤에 오는 워크시트 이름, 을 포함하는 수식을 만듭니다.

통합 문서 간 링크 만들기

  1. Excel에서 두 통합 문서를 모두 엽니다.
  2. 원본 통합문서에서 연결이 필요한 셀을 선택하고 복사하세요(단축키 Ctrl+C)
  3. 대상 도서로 이동하여 클릭 오른쪽 클릭연결을 배치하려는 셀 위에 마우스를 놓습니다. 드롭다운 메뉴에서 선택
  4. 선택하다 연결을 삽입합니다.

테이블 간의 관계를 생성하는 또 다른 더 간단한 옵션이 있습니다. 링크를 삽입하려는 셀에 등호(일반 수식과 동일)를 입력하고 원본 통합 문서로 이동하여 연결하려는 셀을 선택한 후 Enter를 클릭합니다.

일반 수식과 마찬가지로 관계 수식에도 복사 및 자동 완성 도구를 사용할 수 있습니다.

테이블 간의 관계를 만들기 전에

중요한 아이디어에 대한 지식을 전파하기 전에 다음은 Excel에서 관계 작업을 위한 몇 가지 팁입니다.

쉽게 추적 가능한 연결을 만드세요.존재하지 않습니다 자동 검색링크를 포함하는 모든 셀. 따라서 다른 테이블에 대한 링크를 빠르게 식별하려면 특정 형식을 사용하십시오. 그렇지 않으면 링크가 포함된 문서가 유지 관리하기 어려울 정도로 커질 수 있습니다.

자동 계산.원본 통합 문서는 자동 계산 모드(기본적으로 설정됨)에서 작동해야 합니다. 계산 매개변수를 전환하려면 탭을 클릭하세요. 방식그룹에 계산.선택하다 계산 옵션 –> 자동.

연결 업데이트 중

을 위한 수동 업데이트테이블 간 연결, 탭으로 이동 데이터그룹에 사이. 버튼을 클릭하세요 연결을 변경합니다.

나타나는 대화 상자에서 연결 변경 업데이트.

Excel 통합 문서의 링크 끊기

소스 연결을 해제하면 기존 링크 수식이 반환된 값으로 대체됩니다. 예를 들어 링크 =[Source.xlsx]Prices!$B$4는 16으로 대체됩니다. 링크를 끊으면 취소할 수 없으므로 작업을 수행하기 전에 책을 저장하는 것이 좋습니다.

탭으로 이동 데이터그룹에 사이. 버튼을 클릭하세요 연결을 변경합니다.나타나는 대화 상자에서 연결 변경관심 있는 연결을 선택하고 버튼을 클릭하세요. 연결을 끊습니다.

VLOOKUP 함수를 사용하여 한 테이블에서 다른 테이블로 열 데이터를 이동했습니까? 이제 Excel에는 기본 제공 데이터 모델이 있으므로 VLOOKUP 함수는 더 이상 사용되지 않습니다. 두 테이블의 일치하는 데이터를 기반으로 두 테이블 간의 관계를 만들 수 있습니다. 그런 다음 원본이 다르더라도 각 테이블의 필드가 포함된 Power View 시트, 피벗 테이블 및 기타 보고서를 만들 수 있습니다. 예를 들어, 고객 판매 데이터가 있는 경우 연도별, 월별 판매 추세를 분석하기 위해 가져와서 연결할 수 있습니다.

통합 문서의 모든 테이블은 피벗 테이블 및 Power View 필드 목록에 나열됩니다.

관계형 데이터베이스에서 관련 테이블을 가져올 때 엑셀 데이터백그라운드에서 생성된 데이터 모델에서 이러한 관계를 생성할 수 있는 경우가 많습니다. 다른 경우에는 링크를 수동으로 생성해야 합니다.

팀의 경우 관계액세스할 수 없습니다. 이는 통합 문서에 테이블이 하나만 포함되어 있음을 의미합니다.

    창문에서 관계 관리버튼을 클릭하세요 만들다.

    창문에서 연결 만들기필드 옆에 있는 화살표를 클릭하세요. 테이블드롭다운 목록에서 테이블을 선택합니다. 일대다 관계에서 이 테이블은 여러 요소가 포함된 부분이어야 합니다. 고객 및 시간 논리 예제에서는 매일 많은 판매가 발생할 가능성이 높으므로 먼저 고객 판매 테이블을 선택해야 합니다.

    요소의 경우 칼럼(외계인)요소와 관련된 데이터가 포함된 열을 선택합니다. 예를 들어 두 테이블 모두에 날짜 열이 있는 경우 지금 해당 열을 선택해야 합니다.

    현장에서 관련 테이블필드에서 선택한 테이블과 관련된 데이터 열이 하나 이상 포함된 테이블을 선택합니다. 테이블.

    현장에서 관련 열(기본 키)필드에서 선택한 열의 값과 일치하는 고유한 값이 포함된 열을 선택합니다. 열.

    버튼을 클릭하세요 좋아요.

Excel의 테이블 간 관계에 대해 자세히 알아보기

연결에 대한 참고 사항

예. 시간 로직 데이터와 항공사 비행 데이터 연결

Microsoft Azure Marketplace의 무료 데이터를 사용하여 테이블과 논리가 시간과 어떻게 관련되는지 알아볼 수 있습니다. 이러한 데이터 세트 중 일부는 매우 커서 합리적인 시간 내에 다운로드하려면 빠른 인터넷 연결이 필요합니다.

이제 연도, 월 및 기타 달력 값을 기준으로 도착 지연을 구분할 수 있습니다.

조언:기본적으로 월은 알파벳순으로 나열됩니다. Power Pivot 추가 기능을 사용하면 시간순으로 표시되도록 정렬 순서를 변경할 수 있습니다.

    테이블 기본캘린더US Power Pivot 창에서 열려야 합니다.

    기본 테이블에서 열을 기준으로 정렬.

    정렬 필드에서 다음을 선택합니다. MonthInCalendar.

    작성자 필드에서 다음을 선택합니다. 연도의 월.

이제 피벗 테이블은 각 월과 연도 조합(2011년 10월, 2011년 11월)을 해당 연도의 월 수(10, 11)를 기준으로 정렬합니다. 채널이 정렬되어 있으므로 정렬 순서를 변경하는 것은 쉽습니다. 날짜 스트림이 스크립트가 작동하는 데 필요한 모든 열을 제공합니다. 다른 시간 논리 테이블을 사용하면 작업이 달라집니다.

"테이블 간의 관계가 필요할 수 있습니다."

피벗 테이블에 필드를 추가하면 피벗 테이블에서 선택한 필드를 이해하기 위해 테이블을 연결해야 한다는 알림이 표시됩니다.

Excel에서는 관계가 필요한 시기를 알려줄 수 있지만 어떤 테이블과 열을 사용해야 하는지, 테이블 간의 관계가 가능한지 여부는 알려줄 수 없습니다. 질문에 대한 답변을 얻으려면 다음을 시도해 보세요.

1단계: 연결할 테이블 결정

모델에 테이블이 몇 개만 포함된 경우 어떤 테이블을 사용할지 명확합니다. 그러나 더 큰 모델의 경우 도움이 필요할 수 있습니다. 한 가지 방법은 Power Pivot 추가 기능에서 차트 보기를 사용하는 것입니다. 차트 보기는 데이터 모델의 모든 테이블을 시각화합니다. 이를 통해 모델의 나머지 부분과 분리된 테이블을 빠르게 확인할 수 있습니다.

메모:피벗 테이블 또는 Power View 보고서에서 사용할 때 유효하지 않은 모호한 관계를 만들 수 있습니다. 모든 테이블은 어떤 방식으로든 모델의 다른 테이블과 관련될 수 있지만 다른 테이블의 필드를 조인하려고 하면 "테이블 관계가 필요할 수 있습니다."라는 메시지가 표시됩니다. 가장 가능성이 높은 이유는 다대다 관계에 직면하고 있기 때문입니다. 필요한 테이블에 연결되는 테이블 관계 체인을 따라가면 테이블 간에 두 개 이상의 일대다 관계가 있음을 알 수 있습니다. 모든 상황에 적용할 수 있는 쉬운 해결 방법은 없지만 계산된 열을 만들어 사용하려는 열을 하나의 테이블에 통합해 볼 수 있습니다.

2단계: 한 테이블에서 다른 테이블로의 경로를 만드는 데 사용할 수 있는 열 찾기

모델의 나머지 부분과 관련되지 않은 테이블을 확인한 후에는 해당 테이블의 열을 검토하여 모델의 다른 열에 해당 값이 포함되어 있는지 확인하세요.

지역별 제품 판매가 포함된 모델이 있고 이후에 인구통계 데이터를 가져와서 각 지역의 판매와 인구통계 추세 사이에 상관 관계가 있는지 확인한다고 가정해 보겠습니다. 인구통계 데이터는 다양한 소스에서 나오므로 해당 테이블은 처음에는 모델의 나머지 부분과 격리됩니다. 인구통계 데이터를 모델의 나머지 부분과 통합하려면 인구통계 테이블 중 하나에서 이미 사용 중인 것과 일치하는 열을 찾아야 합니다. 예를 들어 인구통계 데이터가 지역별로 구성되어 있고 판매 데이터가 판매 지역을 정의하는 경우 주, 우편번호 또는 지역과 같은 공통 열을 찾아 조회를 제공함으로써 두 데이터 세트를 연결할 수 있습니다.

값 일치 외에도 관계 생성을 위한 몇 가지 추가 요구 사항이 있습니다.

스프레드시트의 정보 마이크로소프트 엑셀도면에서 추출된 데이터와 결합할 수 있습니다.

Excel 스프레드시트에 대한 링크를 생성하면 전체 워크시트 또는 일부의 정보를 포함할 수 있습니다. 명명된 범위. 외부 데이터 링크 대화 상자에서 다음을 수행할 수 있습니다.

  • 도면에서 추출된 데이터를 링크할 XLS 파일을 선택하여 데이터 링크를 설정합니다.
  • 추출된 도면 데이터와 스프레드시트 데이터 간의 매핑을 설정합니다.
  • 추출된 도면 데이터와 병합할 XLS 파일의 열을 선택합니다.

데이터 수정 페이지에서는 다음에 대한 액세스를 제공합니다. 대화 상자"외부 데이터에 연결": 추출된 데이터를 전체 Excel 스프레드시트 또는 행과 열 범위에 연결할 수 있습니다.

스프레드시트에 대한 링크를 만들려면 다음이 필요합니다. 마이크로소프트 애플리케이션뛰어나다. 설치되지 않은 경우 마이크로소프트 프로그램 Excel 또는 XLS 파일은 데이터를 검색하는 컴퓨터에 로컬로 저장되지 않으며 데이터에 연결할 수 없습니다.

행이나 열을 추가하는 등 링크된 스프레드시트를 변경한 후 LINK UPDATE 명령을 사용하여 해당 변경 사항을 반영하도록 도면의 테이블을 업데이트할 수 있습니다. 마찬가지로 도면의 테이블을 변경하는 경우 동일한 명령을 사용하여 연관된 스프레드시트를 업데이트할 수 있습니다.

참고: 데이터 링크를 제거하려면 LINK 명령을 사용하십시오. 팔레트의 데이터에 대한 링크 외부 링크"삭제할 수 없습니다.

데이터 연결 및 일치 정보

데이터 연결에는 테이블 전체 또는 일부를 추출된 도면 데이터와 결합하기 위해 Microsoft Excel 스프레드시트를 선택하는 작업이 포함됩니다. 추가 정보데이터 바인딩에 대해 알아보려면 "외부 데이터에 대한 테이블 링크 만들기"를 참조하세요.

데이터 매핑 프로세스에서는 추출된 도면 데이터 테이블 열의 데이터와 Excel 스프레드시트 열의 데이터라는 두 데이터 세트 간의 관계를 만듭니다.

다음 예에서는 간단한 사양을 검사합니다. 세 개의 데이터 열이 도면에서 검색됩니다. 이러한 열에는 다음 데이터가 포함됩니다. 부품 번호, 사설그리고 수량. 사양에 스프레드시트의 데이터 열 두 개를 추가로 포함할 수 있습니다. 이 열에는 다음 데이터가 포함됩니다. 가격그리고 공급자.

안에 이 예에서는스프레드시트에는 헤더가 있는 부품 번호 열이 포함되어 있습니다. 카탈로그 번호그리고 열에 해당 부품 번호추출된 데이터 테이블에서 외부 데이터 링크 대화 상자에서 "부품 번호"도면 데이터의 열로 "카탈로그 번호"외부 데이터의 열로 사용됩니다. 도면 데이터 열과 외부 데이터 열 모두에 해당 데이터 셀이 하나 이상 있으므로 데이터 추출 기능은 두 소스가 어떻게 관련되어 있는지를 알고 있습니다.

외부 데이터 링크 대화 상자에서 준수 확인 버튼을 클릭하여 데이터의 일관성을 확인할 수 있습니다. 일치가 올바르지 않으면 이유를 나타내는 경고 메시지가 표시됩니다. 어떤 경우에는 올바른 생성도면 데이터와 스프레드시트 데이터 사이에는 연결이 없습니다.

일치하는 데이터 검증

데이터 일치 실패는 도면 데이터와 외부 데이터(스프레드시트의) 간의 유효한 일치를 찾을 수 없음을 의미합니다. 다음 오류 메시지가 표시됩니다.

도면 데이터의 값과 외부 데이터 사이에 일치하는 항목이 없습니다.

  • 문제를 해결할 수 없는 경우 외부 데이터 병합이 발생하지 않습니다. 스프레드시트 열 데이터와 도면 데이터를 비교하고 해당 값을 찾아보세요. 예를 들어 도면 데이터와 외부 데이터를 다음과 같이 설정할 수 있습니다. 다른 의미정확성.

외부 데이터의 데이터 열에 고유한 값이 포함되어 있지 않습니다.

  • 외부 데이터 열에는 동일한 데이터가 포함된 하나 이상의 셀이 포함되어 있습니다. 이러한 상황에서는 도면 데이터와 외부 데이터 간의 대응 관계를 명확하게 정의하는 것이 불가능합니다.

Excel에서 특정 작업을 수행할 때 상호 연결된 여러 테이블을 처리해야 하는 경우가 있습니다. 즉, 한 테이블의 데이터를 다른 테이블로 가져오고 데이터가 변경되면 연결된 모든 테이블 범위의 값이 다시 계산됩니다.

연결된 테이블은 많은 양의 정보를 처리할 때 사용하기 매우 편리합니다. 더욱이, 모든 정보를 하나의 테이블에 담는다면, 그것이 동질적이지 않다면 그다지 편리하지 않습니다. 그러한 개체를 가지고 작업하고 검색하는 것은 어렵습니다. 정보가 분산되어 있으면서도 동시에 상호 연관되어 있는 연결된 테이블은 이러한 문제를 해결하기 위해 설계되었습니다. 관련 표 범위는 하나의 시트나 하나의 통합 문서 내에 위치할 수 있을 뿐만 아니라 별도의 통합 문서(파일)에도 위치할 수 있습니다. 마지막 두 가지 옵션은 실제로 가장 자주 사용됩니다. 이 기술의 목적은 정확하게 데이터 축적에서 벗어나는 것이고, 한 페이지에 쌓아 두는 것은 근본적으로 문제를 해결하지 못하기 때문입니다. 이러한 유형의 데이터 관리를 생성하고 사용하는 방법을 알아 보겠습니다.

연결 테이블 생성

먼저, 서로 다른 테이블 범위 사이에 관계를 생성하는 것이 어떻게 가능한지에 대한 질문을 살펴보겠습니다.

방법 1: 수식을 사용하여 테이블 직접 연결

데이터를 연결하는 가장 쉬운 방법은 다른 표 범위를 참조하는 수식을 사용하는 것입니다. 직접 바인딩이라고 합니다. 이 방법은 단일 테이블 배열의 데이터에 대한 링크를 생성하는 것과 거의 동일한 방식으로 링크를 수행하므로 직관적입니다.

예를 들어 직접 결합을 통해 어떻게 결합이 형성되는지 살펴보겠습니다. 두 장의 시트에 두 개의 테이블이 있습니다. 계산은 하나의 테이블에서 수행됩니다. 임금근로자 임금에 모든 사람에게 공통되는 계수를 곱하는 공식을 사용합니다.

두 번째 시트에는 직원 목록과 급여가 포함된 테이블 범위가 포함되어 있습니다. 두 경우 모두 직원 목록은 동일한 순서로 표시됩니다.

두 번째 시트의 요율 데이터를 첫 번째 시트의 해당 셀로 가져왔는지 확인해야 합니다.

  1. 첫 번째 시트에서 "베팅" 열의 첫 번째 셀을 선택합니다. 여기에 "=" 기호를 넣습니다. 그런 다음 상태 표시줄 위 Excel 인터페이스 왼쪽에 있는 “시트 2” 탭을 클릭합니다.
  2. 문서의 두 번째 영역으로 이동합니다. "베팅" 열의 첫 번째 셀을 클릭하세요. 그런 다음 키보드의 Enter 버튼을 클릭하여 이전에 등호를 설정한 셀에 데이터를 입력합니다.
  3. 그런 다음 첫 번째 시트로 자동 전환됩니다. 보시다시피 두 번째 테이블의 첫 번째 직원의 비율이 해당 셀로 가져옵니다. 베팅이 포함된 셀에 커서를 놓으면 화면에 데이터를 표시하는 데 일반적인 공식이 사용되는 것을 볼 수 있습니다. 그러나 데이터가 출력되는 셀의 좌표 앞에는 해당 데이터가 있는 문서 영역의 이름을 나타내는 "Sheet2!"라는 표현이 있습니다. 우리의 경우 일반 공식은 다음과 같습니다.
  4. 이제 기업의 다른 모든 직원의 요율에 대한 데이터를 전송해야 합니다. 물론 첫 번째 직원에 대한 작업을 완료한 것과 동일한 방식으로 수행할 수 있지만 두 직원 목록이 모두 동일한 순서로 위치한다는 점을 고려하면 작업이 크게 단순화되고 해결 속도가 빨라질 수 있습니다. 아래 범위에 수식을 복사하면 됩니다. Excel의 링크는 기본적으로 상대적이기 때문에 링크를 복사하면 값이 이동하는데 이것이 바로 우리에게 필요한 것입니다. 복사 절차 자체는 채우기 마커를 사용하여 수행할 수 있습니다.

    따라서 수식이 있는 요소의 오른쪽 하단 영역에 커서를 놓습니다. 그런 다음 커서는 검은색 십자 형태의 채우기 표시로 변해야 합니다. 마우스 왼쪽 버튼을 누른 채 커서를 열의 맨 아래로 끕니다.

  5. 시트 2의 유사한 열에 있는 모든 데이터는 시트 1의 테이블로 가져왔습니다. 시트 2의 데이터가 변경되면 첫 번째 데이터도 자동으로 변경됩니다.

방법 2: INDEX - SEARCH 연산자 조합 사용

하지만 테이블 배열의 직원 목록이 동일한 순서가 아닌 경우 어떻게 해야 할까요? 이 경우 앞서 설명한 대로 한 가지 옵션은 연결해야 하는 각 셀 사이에 수동으로 연결을 설정하는 것입니다. 그러나 이것은 작은 테이블에만 적합합니다. 대규모 범위의 경우 이러한 옵션은 구현하는 데 기껏해야 많은 시간이 걸리며 최악의 경우 실제로는 전혀 실현 가능하지 않습니다. 하지만 결정해 이 문제 INDEX – SEARCH 연산자의 조합을 사용하여 수행할 수 있습니다. 이전 방법에서 설명한 표 형식 범위의 데이터를 연결하여 이를 수행하는 방법을 살펴보겠습니다.

  1. "베팅" 열의 첫 번째 요소를 선택하세요. “함수 삽입” 아이콘을 클릭하여 함수 마법사로 이동합니다.
  2. 함수 마법사의 "링크 및 배열" 그룹에서 "INDEX"라는 이름을 찾아 선택합니다.
  3. 이 연산자에는 배열 작업을 위한 형식과 참조 형식이라는 두 가지 형식이 있습니다. 우리의 경우 첫 번째 옵션이 필요하므로 다음 양식 선택 창이 열리면 해당 옵션을 선택하고 "확인"버튼을 클릭하십시오.
  4. INDEX 연산자 인수 창이 시작되었습니다. 지정된 함수의 작업은 지정된 숫자가 있는 줄에 선택한 범위에 있는 값을 표시하는 것입니다. INDEX 연산자의 일반 공식은 다음과 같습니다.

    INDEX(배열;라인_번호;)

    "배열"은 지정된 행 수만큼 정보를 추출할 범위의 주소를 포함하는 인수입니다.

    "줄 번호"는 바로 이 줄의 번호를 나타내는 인수입니다. 줄 번호는 전체 문서를 기준으로 지정해서는 안 되고 선택한 배열을 기준으로만 지정해야 한다는 점을 아는 것이 중요합니다.

    "열 번호"는 선택적 인수입니다. 특정 문제를 해결하는 데 사용하지 않으므로 본질을 별도로 설명할 필요가 없습니다.

    "배열" 필드에 커서를 놓습니다. 그런 다음 시트 2로 이동하여 마우스 왼쪽 버튼을 누른 채 "베팅" 열의 전체 내용을 선택합니다.

  5. 운영자 창에 좌표가 표시된 후 커서를 "라인 번호" 필드에 놓습니다. MATCH 연산자를 사용하여 이 인수를 출력합니다. 따라서 함수 라인 왼쪽에 있는 삼각형을 클릭합니다. 최근에 사용한 연산자 목록이 열립니다. 그중에 "SEARCH"라는 이름이 있으면 클릭하시면 됩니다. 그렇지 않으면 클릭하세요. 마지막 포인트목록 - "기타 기능...".
  6. 표준 함수 마법사 창이 열립니다. 여기에서 동일한 그룹 "링크 및 배열"로 이동합니다. 이번에는 목록에서 “SEARCH” 항목을 선택하세요. "확인"버튼을 클릭하십시오.
  7. MATCH 연산자 인수 창이 활성화됩니다. 이 함수는 특정 배열의 값 개수를 이름으로 표시하도록 설계되었습니다. 이 기능 덕분에 INDEX 함수에 대한 특정 값의 행 번호를 계산할 수 있습니다. MATCH 구문은 다음과 같이 표시됩니다.

    MATCH(조회_값, 조회_배열;)

    "검색 값"은 해당 셀이 위치한 타사 범위에 있는 셀의 이름이나 주소를 포함하는 인수입니다. 계산해야 할 대상 범위 내에서 이 항목의 위치입니다. 우리의 경우 첫 번째 인수는 직원 이름이 있는 시트 1의 셀에 대한 링크입니다.

    "조회 배열"은 위치를 결정하기 위해 지정된 값을 검색하는 배열에 대한 참조인 인수입니다. 우리의 경우 이 역할은 시트 2의 "이름" 열의 주소로 수행됩니다.

    "일치 유형"은 선택적 인수이지만 이전 문과 달리 이 선택적 인수가 필요합니다. 연산자가 검색 값을 배열과 일치시키는 방법을 지정합니다. 이 인수는 세 가지 값 중 하나를 가질 수 있습니다: -1; 0; 1. 정렬되지 않은 배열의 경우 옵션 "0"을 선택합니다. 이 옵션은 우리의 경우에 적합합니다.

    이제 인수 창의 필드를 작성해 보겠습니다. "검색 값" 필드에 커서를 놓고 시트 1의 "이름" 열의 첫 번째 셀을 클릭합니다.

  8. 좌표가 표시된 후 "보기 가능한 배열" 필드에 커서를 놓고 상태 표시줄 위 Excel 창 하단에 있는 "시트 2" 바로가기로 이동합니다. 마우스 왼쪽 버튼을 누른 채 커서로 "이름" 열의 모든 셀을 선택합니다.
  9. 해당 좌표가 "Viewed Array" 필드에 표시된 후 "Matching Type" 필드로 이동하고 키보드를 사용하여 거기에 숫자 "0"을 설정합니다. 그런 다음 다시 "Viewed array" 필드로 돌아갑니다. 사실은 이전 방법에서 했던 것처럼 수식을 복사한다는 것입니다. 주소가 변경되지만 표시되는 배열의 좌표를 수정해야 합니다. 움직이면 안 됩니다. 커서로 좌표를 선택하고 기능 키 F4를 누릅니다. 보시다시피 좌표 앞에 달러 기호가 나타납니다. 이는 참조가 상대 기준에서 절대 기준으로 바뀌었음을 의미합니다. 그런 다음 "확인"버튼을 클릭하십시오.
  10. 결과는 화면의 "베팅" 열의 첫 번째 셀에 표시됩니다. 하지만 복사하기 전에 INDEX 함수의 첫 번째 인수인 영역을 하나 더 확보해야 합니다. 이렇게 하려면 수식이 포함된 열 요소를 선택하고 수식 표시줄로 이동합니다. INDEX 연산자의 첫 번째 인수(B2:B7)를 선택하고 F4 버튼을 클릭합니다. 보시다시피 선택한 좌표 근처에 달러 기호가 나타납니다. Enter 키를 클릭하십시오. 일반적으로 공식은 다음과 같은 형식을 취했습니다.

    INDEX(시트2!$B$2:$B$7,MATCH(시트1!A4,시트2!$A$2:$A$7,0))

  11. 이제 채우기 핸들을 사용하여 복사할 수 있습니다. 앞서 이야기한 것과 같은 방식으로 호출하고 테이블 범위의 끝까지 확장합니다.
  12. 보시다시피 연결된 두 테이블의 행 순서가 일치하지 않음에도 불구하고 모든 값은 직원의성에 따라 조정됩니다. 이는 INDEX-MATCH 연산자 조합을 사용하여 달성되었습니다.

또한 읽어보세요:

Excel의 INDEX 함수 Excel의 MATCH 함수

방법 3: 연결된 데이터에 대해 수학적 연산 수행

직접 데이터 연결은 다른 테이블 범위에 표시된 값을 테이블 중 하나에 표시할 수 있을 뿐만 아니라 이를 사용하여 다양한 작업을 수행할 수 있다는 점에서도 좋습니다. 수학 연산(덧셈, 나눗셈, 뺄셈, 곱셈 등).

이것이 실제로 어떻게 수행되는지 살펴 보겠습니다. 시트 3에는 직원별로 분류하지 않고 기업의 일반 급여 데이터가 표시되도록 만들어 보겠습니다. 이를 위해 시트 2에서 직원 요율을 가져와서 합산하고(SUM 함수 사용) 수식을 사용하여 계수를 곱합니다.

  1. 시트 3에서 급여 계산 결과가 표시될 셀을 선택합니다. “함수 삽입” 버튼을 클릭합니다.
  2. 함수 마법사 창이 시작되어야 합니다. "수학" 그룹으로 이동하여 "SUM"이라는 이름을 선택합니다. 다음으로, “확인” 버튼을 클릭하세요.
  3. 선택한 숫자의 합을 계산하는 SUM 함수가 인수 창으로 이동되었습니다. 다음과 같은 구문이 있습니다.

    SUM(숫자1;숫자2;…)

    창의 필드는 지정된 함수의 인수에 해당합니다. 그 수는 255개에 달할 수 있지만 우리의 목적에는 하나만 있으면 충분합니다. 커서를 “Number1” 필드에 놓습니다. 상태 표시줄 위의 "시트 2" 바로가기를 클릭하세요.

  4. 책의 원하는 섹션으로 이동한 후 합산할 열을 선택합니다. 마우스 왼쪽 버튼을 누른 상태에서 커서로 이 작업을 수행합니다. 보시다시피, 선택한 영역의 좌표가 인수 창의 필드에 즉시 표시됩니다. 그런 다음 "확인"버튼을 클릭하십시오.
  5. 그런 다음 자동으로 시트 1로 이동합니다. 보시다시피 직원 요율의 총액이 해당 요소에 이미 표시되어 있습니다.
  6. 하지만 그게 전부는 아닙니다. 우리가 기억하는 것처럼 급여는 베팅에 계수를 곱하여 계산됩니다. 따라서 합산된 값이 있는 셀을 다시 선택합니다. 그런 다음 수식 입력줄로 이동합니다. 수식에 곱셈 기호(*)를 추가한 다음 계수 표시기가 있는 요소를 클릭합니다. 계산을 수행하려면 키보드의 Enter 키를 클릭하십시오. 보시다시피 프로그램은 기업의 총 임금을 계산했습니다.
  7. 시트 2로 돌아가서 직원의 요율을 변경합니다.
  8. 이후 다시 총액이 표시된 페이지로 이동합니다. 보시다시피, 연결 테이블의 변경으로 인해 총 급여 결과가 자동으로 다시 계산되었습니다.

방법 4: 특수 삽입

선택하여 붙여넣기를 사용하여 Excel에서 테이블 배열을 연결할 수도 있습니다.

  1. 다른 테이블로 "드래그"해야 할 값을 선택합니다. 우리의 경우 이는 시트 2의 "Rate" 열 범위입니다. 선택한 조각을 마우스 오른쪽 버튼으로 클릭합니다. 열리는 목록에서 "복사"를 선택하십시오. 대체 단축키는 Ctrl+C입니다. 그 후 Sheet 1로 이동합니다.
  2. 필요한 책 영역으로 이동한 후 값을 가져와야 할 셀을 선택합니다. 우리의 경우에는 "Rate" 열입니다. 선택한 조각을 마우스 오른쪽 버튼으로 클릭합니다. 안에 상황에 맞는 메뉴"옵션 삽입" 도구 블록에서 "링크 삽입" 아이콘을 클릭하세요.

    또한 있다 대체 옵션. 그건 그렇고, 이전 버전의 Excel에서는 유일한 것입니다. 컨텍스트 메뉴에서 커서를 "선택하여 붙여넣기" 항목으로 이동합니다. 열리는 추가 메뉴에서 동일한 이름을 가진 위치를 선택합니다.

  3. 선택하여 붙여넣기 창이 열립니다. 셀 왼쪽 하단에 있는 “링크 삽입” 버튼을 클릭하세요.
  4. 어떤 옵션을 선택하든 한 테이블 배열의 값이 다른 테이블 배열에 삽입됩니다. 소스의 데이터가 변경되면 삽입된 범위에서도 자동으로 변경됩니다.

단원: Excel에서 선택하여 붙여넣기

방법 5: 여러 통합 문서의 테이블 연결

또한 서로 다른 통합 문서의 테이블 영역 간의 관계를 구성할 수 있습니다. 이것은 붙여넣기 특수 도구를 사용합니다. 작업은 수식을 입력할 때 동일한 책의 영역 간이 아니라 파일 간을 탐색해야 한다는 점을 제외하면 이전 방법에서 고려한 작업과 완전히 유사합니다. 당연히 링크된 책은 모두 오픈되어 있어야 합니다.

  1. 다른 통합 문서로 전송해야 하는 데이터 범위를 선택합니다. 마우스 오른쪽 버튼을 클릭하고 열리는 메뉴에서 "복사"를 선택하십시오.
  2. 그런 다음 이 데이터를 삽입해야 할 책으로 이동합니다. 원하는 범위를 선택하세요. 마우스 오른쪽 버튼을 클릭하세요. 컨텍스트 메뉴의 "옵션 삽입" 그룹에서 "링크 삽입"을 선택합니다.
  3. 그 후에 값이 삽입됩니다. 원본 통합 문서의 데이터가 변경되면 통합 문서의 테이블 형식 배열이 자동으로 업데이트됩니다. 더욱이 이를 위해 두 책을 모두 공개할 필요는 전혀 없습니다. 하나의 통합 문서만 열면 충분하며, 이전에 변경 사항이 있는 경우 닫힌 연결된 문서에서 자동으로 데이터를 가져옵니다.

그러나 이 경우 삽입은 불변 배열 형태로 수행된다는 점에 유의해야 합니다. 데이터가 삽입된 셀을 변경하려고 하면 변경이 불가능하다는 메시지가 나타납니다.

다른 책에 연결된 배열에 대한 변경은 링크를 끊어야만 가능합니다.

테이블 간의 관계 끊기

때로는 테이블 범위 간의 관계를 끊어야 할 때도 있습니다. 그 이유는 위에서 설명한 경우, 다른 통합 문서에서 삽입된 배열을 변경해야 하는 경우 또는 단순히 한 테이블의 데이터가 다른 테이블에서 자동으로 업데이트되는 것을 사용자가 꺼리는 경우일 수 있습니다.

방법 1: 책 간의 연결 끊기

기본적으로 한 번의 작업을 수행하면 모든 셀의 책 간의 연결을 끊을 수 있습니다. 이 경우 셀의 데이터는 그대로 유지되지만 이미 다른 문서에 전혀 종속되지 않는 정적이며 업데이트되지 않는 값입니다.

  1. 다른 파일의 값을 불러오는 통합 문서에서 '데이터' 탭으로 이동합니다. 리본의 "연결" 도구 블록에 있는 "연결 편집" 아이콘을 클릭합니다. 현재 책에 다른 파일에 대한 링크가 포함되어 있지 않으면 이 버튼이 비활성화됩니다.
  2. 연결 변경 창이 열립니다. 연결된 책 목록(여러 개가 있는 경우)에서 연결을 끊고 싶은 파일을 선택합니다. "연결 끊기" 버튼을 클릭하세요.
  3. 결과에 대한 경고가 포함된 정보 창이 열립니다. 추가 조치. 무엇을 하려는지 확신이 들면 "연결 끊기" 버튼을 클릭하십시오.
  4. 그 후에는 현재 문서의 지정된 파일에 대한 모든 참조가 정적 값으로 대체됩니다.

방법 2: 값 삽입

그러나 위의 방법은 두 책 사이의 모든 연결을 완전히 끊어야 하는 경우에만 적합합니다. 동일한 파일 내에 있는 관련 테이블을 분리해야 하는 경우 어떻게 해야 합니까? 데이터를 복사한 다음 값과 동일한 위치에 붙여넣으면 됩니다. 그런데 같은 방법으로 파일 간의 일반적인 연결을 끊지 않고도 서로 다른 책의 개별 데이터 범위 간의 연결을 끊을 수 있습니다. 이 방법이 실제로 어떻게 작동하는지 살펴보겠습니다.

  1. 다른 테이블과의 관계를 삭제하려는 범위를 선택합니다. 마우스 오른쪽 버튼을 클릭합니다. 열리는 메뉴에서 "복사"를 선택하십시오. 이러한 작업 대신 대체 단축키 조합 Ctrl+C를 입력할 수 있습니다.
  2. 그런 다음 동일한 조각을 선택 취소하지 않고 다시 마우스 오른쪽 버튼으로 클릭합니다. 이번에는 작업 목록에서 "삽입 옵션" 도구 그룹에 있는 "값" 아이콘을 클릭합니다.
  3. 그 후에는 선택한 범위의 모든 참조가 정적 값으로 대체됩니다.

보시다시피 Excel에는 여러 테이블을 함께 연결하는 방법과 도구가 있습니다. 동시에 표 형식 데이터는 다른 시트나 다른 책에서도 찾을 수 있습니다. 필요한 경우 이 연결은 쉽게 끊어질 수 있습니다.

문제 해결에 도움을 드릴 수 있어서 기쁘게 생각합니다.

문제의 본질을 자세히 설명하면서 의견에 질문하십시오. 우리 전문가들이 최대한 빨리 답변을 드리도록 노력하겠습니다.

이 기사가 도움이 되었나요?

Excel 테이블 링크는 다른 통합 문서의 셀에서 데이터를 반환하는 수식입니다. 링크가 포함된 통합 문서를 열면 Excel은 원본 통합 문서의 최신 정보를 읽습니다(링크 업데이트).

Excel의 테이블 간 관계는 통합 문서의 다른 시트와 다른 Excel 통합 문서 모두에서 데이터를 검색하는 데 사용됩니다. 예를 들어, 총 매출 금액을 계산하는 테이블이 있습니다. 계산에는 제품 가격과 판매량이 사용됩니다. 이 경우 가격 데이터가 포함된 별도의 테이블을 생성하는 것이 합리적입니다. 이 테이블은 첫 번째 테이블의 연결을 사용하여 가져옵니다.

테이블 간의 관계를 만들 때 Excel에서는 괄호로 묶인 원본 통합 문서의 이름, 워크시트 이름 뒤에 느낌표가 오는 항목, 셀 참조가 포함된 수식이 만들어집니다.

통합 문서 간 링크 만들기

  1. Excel에서 두 통합 문서를 모두 엽니다.
  2. 원본 통합문서에서 연결이 필요한 셀을 선택하고 복사하세요(단축키 Ctrl+C)
  3. 대상 통합 문서로 이동하여 연결하려는 셀을 마우스 오른쪽 버튼으로 클릭합니다. 드롭다운 메뉴에서 선택 특수 인서트
  4. 특수 인서트선택하다 연결을 삽입합니다.

테이블 간의 관계를 생성하는 또 다른 더 간단한 옵션이 있습니다. 링크를 삽입하려는 셀에 등호(일반 수식과 동일)를 입력하고 원본 통합 문서로 이동하여 연결하려는 셀을 선택한 후 Enter를 클릭합니다.

일반 수식과 마찬가지로 관계 수식에도 복사 및 자동 완성 도구를 사용할 수 있습니다.

테이블 간의 관계를 만들기 전에

중요한 아이디어에 대한 지식을 전파하기 전에 다음은 Excel에서 관계 작업을 위한 몇 가지 팁입니다.

쉽게 추적 가능한 연결을 만드세요.링크가 포함된 모든 셀에 대한 자동 검색은 없습니다. 따라서 다른 테이블에 대한 링크를 빠르게 식별하려면 특정 형식을 사용하십시오. 그렇지 않으면 링크가 포함된 문서가 유지 관리하기 어려울 정도로 커질 수 있습니다.

자동 계산.원본 통합 문서는 자동 계산 모드(기본적으로 설정됨)에서 작동해야 합니다. 계산 매개변수를 전환하려면 탭을 클릭하세요. 방식그룹에 계산.선택하다 계산 옵션 –> 자동.

연결 업데이트 중

테이블 간의 관계를 수동으로 업데이트하려면 탭으로 이동하세요. 데이터그룹에 사이. 버튼을 클릭하세요 연결을 변경합니다.

나타나는 대화 상자에서 연결 변경 업데이트.

Excel 통합 문서의 링크 끊기

소스 연결을 해제하면 기존 링크 수식이 반환된 값으로 대체됩니다. 예를 들어 링크 =Prices!$B$4는 16으로 대체됩니다. 링크를 끊으면 취소할 수 없으므로 작업을 수행하기 전에 책을 저장하는 것이 좋습니다.

탭으로 이동 데이터그룹에 사이. 버튼을 클릭하세요 연결을 변경합니다.나타나는 대화 상자에서 연결 변경관심 있는 연결을 선택하고 버튼을 클릭하세요. 연결을 끊습니다.

피벗 테이블에 아직 익숙하지 않다면 이 문서부터 시작하세요.

문제

분석된 데이터는 별도의 테이블 형태로 제공되지만 연결이 필요합니다. MS Access에서는 이 작업을 쉽게 수행할 수 있지만 Excel에서는 이를 위해 항상 VLOOKUP 유형 수식을 사용해야 했습니다. 그러나 Excel 2013부터는 피벗 테이블을 작성할 때 키 필드로 연결된 여러 테이블을 소스로 사용할 수 있습니다.

예

이 예에는 Orders, Order Lines, Products, Customers라는 4개의 테이블이 있습니다.

주문 테이블:

주문 라인 테이블:

제품 테이블

클라이언트 테이블

소스 테이블은 Orders, OrderLines, Goods 및 Clients와 같은 스마트 테이블로 설계되었습니다.

Orders 및 OrderLines 테이블은 Order_ID 필드로, Orders 및 Clients 테이블은 Customer_ID 필드로, OrderLines 및 Goods 테이블은 Product_ID 필드로 연결될 수 있다는 점은 매우 분명합니다.

예제 다운로드

데이터 모델 생성

기존 테이블을 기반으로 피벗 테이블을 만들어 보겠습니다.

    삽입 메뉴에서 피벗 테이블을 선택합니다. 해당 대화 상자에는 이 데이터를 데이터 모델에 추가하는 옵션이 표시됩니다. 선택할 수도 있지만 다른 것을 추천합니다. 편리한 방법. 확인을 클릭하시면 됩니다.

    표시되는 피벗 테이블 필드 패널에 OTHER TABLES...라는 문구가 표시됩니다.

    눌러보자. 다음 질문이 나타날 것입니다:

    필드 선택을 시작하면 잠시 후 필드 목록에 CREATE 버튼이 나타납니다.

    이를 클릭하고 테이블 간의 연결을 만들어 보겠습니다. 그러면 Orders 테이블과 OrderLines 간의 연결이 생성됩니다. Excel에서는 일대일 또는 일대다 관계를 만들 수 있습니다. 또한 "다수"가 있는 테이블을 먼저 표시해야 합니다. 그렇지 않으면 Excel이 맹세하고 교체를 제안합니다.

    같은 방식으로 다른 연결을 만듭니다.

    관계 관리 대화 상자는 ANALYSIS 리본, 관계 명령을 통해 액세스할 수 있습니다.

    피벗 테이블 필드 패널에서 더 많은 필드를 보려면 도구 단추(기어 모양)를 사용하여 이 보기를 선택할 수 있습니다.

    결과는 다음과 같습니다:

    결과적으로 이제 모든 테이블이 연결되었으며 예를 들어 이러한 보고서를 생성할 수 있습니다.

연결된 테이블은 단일 단위로 관리할 수 있는 데이터의 모음입니다.

버튼을 사용하여 연결된 테이블을 생성하세요. "테이블 형식"패널에 "스타일"고삐 "집".


향후 테이블에 대해 원하는 스타일을 선택하고 관련 테이블이 생성될 기준으로 셀 범위를 지정합니다.



모든 매개변수를 설정한 후 관련 테이블을 얻습니다. 열 제목 옆에 화살표가 있는 버튼이 나타나고(필터링과 유사) 상황에 맞는 도구가 프로그램 창에 나타납니다. "테이블 작업"테이프가 들어있는 "건설자".



연결된 각 테이블에는 고유한 이름이 지정됩니다. 기본값은 "Table_number"입니다. 패널에서 테이블 이름을 변경할 수 있습니다. "속성".



패널에서 "표 스타일"필요한 경우 연결된 테이블의 스타일을 변경하거나 사용자 정의할 수 있습니다.

연결된 테이블을 다시 일반 셀 범위로 바꿀 수 있습니다. 이런 버튼이 있어요 "범위로 변환"패널에 "서비스"문맥 피드 "건설자".

앞서 언급했듯이 열 헤더 옆에는 데이터를 정렬하고 필터를 적용할 수 있는 정렬 및 필터 버튼이 있습니다.

연결된 테이블에서는 열 합계를 계산할 수 있습니다. 이렇게 하려면 확인란을 선택하세요. "총 행"패널에 "표 스타일 옵션".



드롭다운 목록을 사용하여 최종 기능의 원하는 값을 선택할 수 있습니다.



연결된 테이블에 행과 열을 추가/제거할 수 있습니다.

이는 여러 가지 방법으로 수행할 수 있습니다.

1. 버튼을 이용하세요 "테이블 크기 조정"패널에 "속성".



2. 새 열 (행)을 추가해야하는 링크 된 테이블의 셀과 패널에 커서를 놓습니다. "세포"고삐 "집"버튼을 사용하세요 "끼워 넣다".