자습서: Excel로 데이터 가져오기 및 데이터 모델 만들기

이 자습서의 섹션은 다음과 같습니다.

• 데이터베이스에서 데이터 가져오기

• 스프레드시트에서 데이터 가져오기

• 복사 및 붙여넣기를 사용하여 데이터 가져오기

• 가져온 데이터 간의 관계 만들기

• 확인 사항 및 퀴즈

이 자습서의 끝에는 배운 내용을 테스트하기 위한 퀴즈가 있습니다.

이 자습서 시리즈에서는 올림픽 메달, 개최국, 다양한 올림픽 스포츠 이벤트를 설명하는 데이터를 사용합니다. 각 자습서를 순서대로 살펴보는 것이 좋습니다. 

데이터베이스에서 데이터 가져오기

빈 통합 문서에서 이 자습서를 시작합니다. 이 섹션의 목표는 외부 데이터 원본에 연결하고 추가 분석을 위해 해당 데이터를 Excel로 가져오는 것입니다.

우선 인터넷에서 일부 데이터를 다운로드해 보겠습니다. 올림픽 메달을 설명하는 데이터로, Microsoft Access 데이터베이스입니다.

1. 다음 링크를 클릭하여 이 자습서 시리즈에서 사용하는 파일을 다운로드합니다. 다운로드 또는 내 문서와 같이 쉽게 액세스할 수 있는 위치 또는 만든
   새 폴더에 4개의 파일을 각각 다운로드합니다. > OlympicMedals.accdb Access 데이터베이스
   Excel 통합 문서
   OlympicSports.xlsx > Excel 통합 문서
   Population.xlsx > Excel 통합 문서DiscImage_table.xlsx >

2. Excel에서 빈 통합 문서를 엽니다.

3. 데이터 > 외부 데이터 가져오기 > Access를 클릭합니다. 리본 메뉴가 통합 문서의 너비에 따라 동적으로 조정되므로 리본 메뉴의 명령이 다음 화면과 약간 다르게 보일 수 있습니다. 첫 번째 화면은 통합 문서가 넓은 경우에 리본 메뉴를, 두 번째 이미지는 화면의 일부만 차지하도록 크기가 조정된 통합 문서를 보여 줍니다.
   
   
   
   
   
    

4. 다운로드한 OlympicMedals.accdb 파일을 선택하고 열기를 클릭합니다. 데이터베이스에서 발견된 테이블을 표시하는 다음과 같은 테이블 선택 창이 나타납니다. 데이터베이스의 테이블은 Excel의 워크시트나 표와 비슷합니다. 여러 테이블 선택 사용 확인란을 선택하고 모든 테이블을 선택합니다. 그런 다음 확인을 클릭합니다.
   
   

5. 데이터 가져오기 창이 나타납니다.

   참고: 다음 화면에 표시된 데이터 모델에 이 데이터를 추가할 수 있는 창 아래쪽의 확인란을 확인합니다. 데이터 모델은 둘 이상의 테이블을 동시에 가져오거나 작업할 때 자동으로 만들어집니다. 데이터 모델은 테이블을 통합하여 피벗 테이블, 파워 피벗 및 파워 뷰를 사용하여 광범위한 분석을 가능하게 합니다. 데이터베이스에서 테이블을 가져올 때 해당 테이블 간의 기존 데이터베이스 관계는 Excel에서 데이터 모델을 만드는 데 사용됩니다. 데이터 모델은 Excel에서 투명하지만 파워 피벗 추가 기능을 사용하여 직접 보고 수정할 수 있습니다. 데이터 모델은 이 자습서의 뒷부분에서 자세히 설명합니다.

   
   표를 Excel로 가져오고 가져온 테이블을 분석하기 위한 피벗 테이블을 준비하는 피 벗 테이블 보고서 옵션을 선택하고 확인을 클릭합니다.
   
   

6. 데이터 가져오기가 완료되면 가져온 테이블을 사용하여 피벗 테이블이 만들어집니다.
   
   

Excel로 데이터를 가져오면 데이터 모델이 자동으로 만들어지므로 이제 데이터를 탐색할 수 있습니다.

피벗 테이블을 사용하여 데이터 탐색

피벗 테이블을 사용하면 가져온 데이터를 손쉽게 탐색할 수 있습니다. 테이블(예: 방금 전에 Access 데이터베이스에서 가져온 테이블)에서 피벗 테이블의 여러 영역으로 필드(Excel의 열과 비슷함)를 끌어 데이터가 표시되는 방법을 조정합니다. 피벗 테이블에는 4개의 영역(필터, 열, 행, 값)이 있습니다.

필드를 끌어야 하는 영역을 결정하는 데 몇 가지 실험이 걸릴 수 있습니다. 피벗 테이블이 원하는 대로 데이터를 표시할 때까지 테이블에서 원하는 수 또는 몇 개의 필드를 끌 수 있습니다. 피벗 테이블의 다른 영역으로 필드를 끌어 자유롭게 탐색할 수 있습니다. 피벗 테이블의 필드를 정렬할 때 기본 데이터는 영향을 받지 않습니다.

피벗 테이블에서 올림픽 메달 데이터를 살펴보겠습니다. 종목, 메달 종류, 선수의 국가 또는 지역을 기준으로 정리된 올림픽 메달리스트가 그 첫 번째입니다.

1. 피벗 테이블 필드에서 옆에 있는 화살표를 클릭하여 Medals를 확장합니다. 확장된 Medals 테이블에서 NOC_CountryRegion 필드를 찾아 열 영역으로 끌어다 놓습니다. NOC는 한 국가 또는 지역의 조직 단위를 말하는 국가 올림픽 위원회(National Olympic Committees)의 약어입니다.

2. 다음으로 Disciplines 테이블에서 Discipline을 행 영역으로 끌어다 놓습니다.

3. 5가지 스포츠(양궁, 다이빙, 펜싱, 피겨 스케이팅, 스피드 스케이팅)만 표시하도록 종목을 필터링해 보겠습니다. 피벗 테이블 필드 영역 안이나 피벗 테이블 자체의 행 레이블 필터에서 이 작업을 수행할 수 있습니다.

   1. 피벗 테이블의 아무 곳이나 클릭하여 Excel 피벗 테이블이 선택되어 있는지 확인합니다. 분야 테이블이 확장된 피벗 테이블 필드 목록에서 해당 분야 필드 위로 마우스를 가져가면 필드 오른쪽에 드롭다운 화살표가 나타납니다. 드롭다운을 클릭하고 (모두 선택)을 클릭하여 모든 선택을 제거한 다음 아래로 스크롤하여 양궁, 다이빙, 펜싱, 피겨 스케이팅 및 스피드 스케이팅을 선택합니다. 확인을 클릭합니다.

   2. 또는 피벗 테이블의 행 레이블 섹션에서 피벗 테이블의 행 레이블 옆에 있는 드롭다운을 클릭하고 (모두 선택)을 클릭해 모든 선택 항목을 제거한 다음 아래로 스크롤하여 Archery, Diving, Fencing, Figure Skating, Speed Skating을 선택합니다. 확인을 클릭합니다.

4. 피벗 테이블 필드의 Medals 테이블에서 Medal을 값 영역으로 끌어다 놓습니다. 값은 숫자여야 하므로 Excel에서 Medal이 개수: Medal로 자동 변경됩니다.

5. Medals 테이블에서 Medal을 다시 선택하고 필터 영역으로 끌어다 놓습니다.

6. 총 메달 수가 90개가 넘는 국가나 지역만 표시하도록 피벗 테이블을 필터링해 보겠습니다. 방법은 다음과 같습니다.

   1. 피벗 테이블에서 열 레이블 오른쪽에 있는 드롭다운을 클릭합니다.

   2. 값 필터를 선택하고 보다 큼…을 선택합니다.

   3. 마지막 필드(오른쪽)에 90을 입력합니다. 확인을 클릭합니다.
      

피벗 테이블의 모양은 다음 화면과 같습니다.

별다른 노력 없이도 서로 다른 테이블 3개의 필드가 포함된 기본 피벗 테이블이 완성되었습니다. 테이블 간에 관계가 이미 존재했기 때문에 이 작업을 간단하게 실행할 수 있었습니다. 원본 데이터베이스에 테이블 관계가 존재했고 모든 테이블을 한 번의 작업으로 가져왔기 때문에 Excel이 해당 데이터 모델에서 이러한 테이블 관계를 다시 만들 수 있었습니다.

하지만 가져온 데이터의 원본이 서로 다르거나 데이터를 나중에 가져온다면 어떨까요? 일반적으로 일치하는 열을 기준으로 새 데이터에 관계를 만들 수 있습니다. 다음 단계에서는 추가로 테이블을 가져오고 새 관계를 만드는 방법을 알아봅니다.

스프레드시트에서 데이터 가져오기

이번에는 다른 원본, 즉 기존 통합 문서에서 데이터를 가져온 다음 기존 데이터와 새 데이터 간의 관계를 지정해 보겠습니다. 관계를 통해 Excel에서 데이터 모음을 분석하고 가져온 데이터를 생동감 있게 시각화할 수 있습니다.

우선 빈 워크시트를 만든 다음 Excel 통합 문서에서 데이터를 가져옵니다.

1. 새 Excel 워크시트를 삽입하고 Sports라는 이름을 붙입니다.

2. 다운로드한 예제 데이터 파일이 들어 있는 폴더를 찾고 OlympicSports.xlsx를 엽니다.

3. Sheet1의 데이터를 선택해 복사합니다. 데이터가 있는 셀(예: A1 셀)을 선택한 경우 Ctrl+A를 눌러 인접한 모든 데이터를 선택할 수 있습니다. OlympicSports.xlsx 통합 문서를 닫습니다.

4. Sports 워크시트에서 A1 셀에 커서를 놓고 데이터를 붙여 넣습니다.

5. 데이터가 여전히 강조 표시되어 있는 상태에서 Ctrl+T를 눌러 데이터의 서식을 표로 지정합니다. 홈 > 표 서식을 선택하여 리본 메뉴에서 데이터의 서식을 표로 지정할 수도 있습니다. 데이터에는 머리글이 있으므로 여기 나와 있는 것처럼 표시되는 표 만들기 창에서 머리글 포함을 선택합니다.
   
   
   
   데이터 서식을 테이블로 지정하면 많은 이점이 있습니다. 쉽게 식별할 수 있도록 테이블에 이름을 지정할 수 있습니다. 피벗 테이블, 파워 피벗, Power View에서 탐색과 분석이 가능하도록 테이블 사이에 관계를 설정할 수도 있습니다.

6. 표의 이름을 지정합니다. 표 도구 > 디자인 > 속성에서 표 이름 필드를 찾고 Sports를 입력합니다. 통합 문서의 모양은 다음 화면과 같습니다.
   

7. 통합 문서를 저장합니다.

복사 및 붙여넣기를 사용하여 데이터 가져오기

지금까지 Excel 통합 문서에서 데이터를 가져왔으며, 이번에는 웹 페이지에서 찾을 수 있는 테이블이나 Excel에 복사해 붙여 넣을 수 있는 다른 원본에서 데이터를 가져오겠습니다. 다음 단계에서는 표에서 올림픽 개최 도시를 추가합니다.

1. 새 Excel 워크시트를 삽입하고 Hosts라는 이름을 붙입니다.

2. 표 머리글을 포함해 다음 표를 선택하고 복사합니다.

1. Excel에서 Hosts 워크시트의 A1 셀에 커서를 놓고 데이터를 붙여 넣습니다.

2. 데이터의 서식을 표로 지정합니다. 이 자습서의 앞부분에서 설명한 대로 Ctrl+T를 누르거나 홈 > 표 서식에서 데이터 서식을 표로 지정합니다. 데이터에 머리글이 있으므로 표시되는 표 만들기 창에서 머리글 포함을 선택합니다.

3. 표의 이름을 지정합니다. 표 도구 > 디자인 > 속성에서 표 이름 필드를 찾고 Hosts를 입력합니다.

4. Edition 열을 선택하고 홈 탭에서 소수 자릿수가 0개인 숫자로 서식을 지정합니다.

5. 통합 문서를 저장합니다. 통합 문서의 모양은 다음 화면과 같습니다.

이제 테이블이 포함된 Excel 통합 문서가 있으므로 테이블 간의 관계를 만들 수 있습니다. 테이블 간의 관계를 만들면 두 개의 테이블에서 데이터의 매시업을 만들 수 있습니다.

가져온 데이터 간의 관계 만들기

가져온 테이블에서 피벗 테이블의 필드를 사용하여 바로 시작할 수 있습니다. Excel에서 피벗 테이블로 필드를 통합하는 방법을 결정할 수 없는 경우에는 기존 데이터 모델로 관계를 설정해야 합니다. 다음 단계에서는 서로 다른 원본에서 가져온 데이터 간에 관계를 만드는 방법을 알아봅니다.

1. Sheet1의피벗 테이블 필드 맨 위에서모두 를 클릭하여 다음 화면에 표시된 것처럼 사용 가능한 테이블의 전체 목록을 봅니다.
   

2. 방금 추가한 새 테이블을 보려면 목록을 스크롤합니다.

3. Sports를 확장하고 Sport를 선택하여 피벗 테이블에 추가합니다. Excel에 다음 화면과 같이 관계를 만들라는 메시지가 나타납니다.
   
    

   이 알림은 원본으로 사용하는 데이터 모델에 속하지 않는 테이블의 필드를 사용했기 때문에 나타납니다. 데이터 모델에 테이블을 추가하는 한 가지 방법은 이미 데이터 모델에 있는 테이블에 대해 관계를 만드는 것입니다. 관계를 만들려면 테이블 중 하나에 반복되는 않는 고유한 값으로 이루어진 열이 있어야 합니다. 데이터베이스에서 가져온 예제 데이터의 Disciplines 테이블에 SportID라는 스포츠 코드 필드가 포함되어 있습니다. 가져온 Excel 데이터에 이와 동일한 스포츠 코드가 필드의 형태로 존재합니다. 관계를 만들어 보겠습니다.

4. 다음 화면과 같이 강조 표시된 피벗 테이블 필드 영역에서 만들기...를 클릭하여 관계 만들기 대화 상자를 엽니다.
   
   

5. 테이블의 드롭다운 목록에서 Disciplines를 선택합니다.

6. 열(외래)에서 SportID를 선택합니다.

7. 관련 표에서 Sports를 선택합니다.

8. 관련 열(기본)에서 SportID를 선택합니다.

9. 확인을 클릭합니다.

피벗 테이블이 새 관계를 반영하여 변경됩니다. 하지만 행 영역의 필드 순서 때문에 피벗 테이블의 모양이 아직 만족스럽지 않습니다. Discipline이 지정된 스포츠의 하위 범주이기는 하지만 행 영역에서 Sport 위에 Discipline을 정렬했으므로 제대로 정리되지 않습니다. 다음 화면은 이처럼 원치 않는 순서를 보여 줍니다.

1. ROWS 영역에서 Sport를 분야 위로 이동합니다. 이는 훨씬 더 나은 기능이며, 피벗 테이블은 다음 화면과 같이 보려는 데이터를 표시합니다.
   
   

Excel에서는 전체 통합 문서의 피벗 테이블, 피벗 차트, 파워 피벗 또는 Power View 보고서에 사용할 수 있는 데이터 모델을 백그라운드에서 만듭니다. 테이블 관계는 데이터 모델의 기반이 되며, 탐색 및 계산 경로를 결정합니다.

다음 자습서에서는 Excel,파워 피벗및 DAX를 사용하여 데이터 모델 관계 확장 에서 여기에서 배운 내용을 기반으로 하며 파워 피벗 라는 강력하고 시각적인 Excel 추가 기능을 사용하여 데이터 모델을 확장하는 단계를 진행합니다. 또한 테이블의 열을 계산하고 해당 계산 열을 사용하여 관련 없는 테이블을 데이터 모델에 추가할 수 있도록 하는 방법도 알아봅니다.

확인 사항 및 퀴즈

배운 내용 복습

지금까지 여러 테이블의 데이터에 액세스하는 피벗 테이블이 포함된 Excel 통합 문서를 만들었으며 이 테이블 중 일부는 따로 가져왔습니다. 데이터베이스나 다른 Excel 통합 문서에서 가져오거나 데이터를 복사해 Excel에 붙여 넣는 방법을 배웠습니다.

데이터를 함께 사용하기 위해 Excel에서 행의 상관 관계를 지정하는 데 사용하는 테이블 관계를 만들어야 했습니다. 한 테이블의 열이 다른 테이블의 데이터와 상관 관계가 있어야만 관계를 만들고 관련 행을 찾을 수 있다는 사실도 배웠습니다.

이제 이 시리즈의 다음 자습서로 넘어가도 좋습니다. 링크는 다음과 같습니다.

자습서: Excel, Power Pivot, DAX를 사용하여 데이터 모델 관계 확장

퀴즈

배운 내용을 얼마나 잘 기억하고 싶으신가요? 여기에 당신의 기회입니다. 다음 퀴즈에서는 이 자습서에서 배운 기능, 기능 또는 요구 사항을 강조 표시합니다. 페이지 아래쪽에서 답변을 찾을 수 있습니다. 행운을 빕니다!

질문 1: 가져온 데이터를 테이블로 변환하는 것이 중요한 이유는 무엇인가요?

A: 가져온 데이터가 모두 테이블로 자동으로 바뀌기 때문에 테이블로 변환할 필요가 없습니다.

B: 가져온 데이터를 테이블로 변환하면 데이터 모델에서 제외됩니다. 데이터 모델에서 제외된 데이터만 피벗 테이블, 파워 피벗, Power View에서 사용할 수 있습니다.

C: 가져온 데이터를 테이블로 변환하면 데이터 모델에 포함할 수 있으며 피벗 테이블, 파워 피벗, Power View에서 사용할 수 있습니다.

D: 가져온 데이터를 테이블로 변환할 수 없습니다.

질문 2: 다음 중 Excel로 가져와서 데이터 모델에 포함할 수 있는 데이터 원본은 무엇입니까?

A: Access 데이터베이스 및 기타 여러 데이터베이스

B: 기존 Excel 파일

C: 웹 사이트, 문서 또는 Excel에 붙여 넣을 수 있는 기타 항목의 데이터 테이블을 비롯한 복사하여 Excel에 붙여 넣고 표로 서식을 지정할 수 있는 모든 항목

D: 위의 항목 모두

질문 3: 피벗 테이블에서 4개의 피벗 테이블 필드 영역의 필드 순서를 다시 지정하면 어떻게 되나요?

A: 아무 변화가 없습니다. 피벗 테이블 영역에 넣은 후에는 필드의 순서를 바꿀 수 없습니다.

B: 피벗 테이블 서식이 레이아웃을 반영하도록 변경되지만 원본 데이터는 영향을 받지 않습니다.

C: 피벗 테이블 서식이 레이아웃을 반영하도록 변경되며, 모든 원본 데이터가 영구적으로 변경됩니다.

D: 원본 데이터가 변경되고 새 데이터 집합이 만들어집니다.

질문 4: 테이블 간에 관계를 만들 때 필요한 것은 무엇인가요?

A: 테이블에는 반복되지 않는 고유한 값이 포함된 열이 없어야 합니다.

B: 하나의 테이블이 Excel 통합 문서의 일부가 아니어야 합니다.

C: 열을 테이블로 변환하지 말아야 합니다.

D: 위에 정답 없음

퀴즈 정답

1. 정답: C

2. 정답: D

3. 정답: B

4. 정답: D