Microsoft Query를 사용하여 외부 데이터 검색

Microsoft 쿼리를 사용하여 외부 데이터 원본에 연결하고, 해당 외부 원본에서 데이터를 선택하고, 해당 데이터를 워크시트로 가져오고, 필요에 따라 데이터를 새로 고쳐 워크시트 데이터를 외부 원본의 데이터와 동기화할 수 있습니다.

액세스할 수 있는 데이터베이스 유형     Microsoft Office Access, Microsoft SQL Server 및 Microsoft SQL Server OLAP Services를 비롯한 여러 유형의 데이터베이스에서 데이터를 검색할 수 있습니다. Excel 통합 문서 및 텍스트 파일에서 데이터를 검색할 수도 있습니다.

Microsoft Office는 다음 데이터 원본에서 데이터를 검색하는 데 사용할 수 있는 드라이버를 제공합니다.

• Microsoft SQL Server Analysis Services(OLAP 공급자 )

• Microsoft Office 액세스

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• 역설

• 텍스트 파일 데이터베이스

다른 제조업체의 ODBC 드라이버 또는 데이터 원본 드라이버를 사용하여 다른 유형의 OLAP 데이터베이스를 포함하여 여기에 나열되지 않은 데이터 원본에서 정보를 검색할 수도 있습니다. 여기에 나열되지 않은 ODBC 드라이버 또는 데이터 원본 드라이버를 설치하는 방법에 대한 자세한 내용은 데이터베이스에 대한 설명서를 확인하거나 데이터베이스 공급업체에 문의하세요.

데이터베이스에서 데이터 선택     외부 데이터베이스에 저장된 데이터에 대해 묻는 질문인 쿼리를 만들어 데이터베이스에서 데이터를 검색합니다. 예를 들어 데이터가 Access 데이터베이스에 저장된 경우 지역별 특정 제품에 대한 판매 수치를 알고 싶을 수 있습니다. 분석하려는 제품 및 지역에 대한 데이터만 선택하여 데이터의 일부를 검색할 수 있습니다.

Microsoft 쿼리를 사용하면 원하는 데이터 열을 선택하고 해당 데이터만 Excel로 가져올 수 있습니다.

한 작업에서 워크시트 업데이트     Excel 통합 문서에 외부 데이터가 있으면 데이터베이스가 변경되면 요약 보고서와 차트를 다시 만들지 않고도 데이터를 새로 고침 분석을 업데이트할 수 있습니다. 예를 들어 월별 판매 요약을 만들고 새 판매 수치가 들어오면 매월 새로 고칠 수 있습니다.

Microsoft 쿼리에서 데이터 원본을 사용하는 방법     특정 데이터베이스에 대한 데이터 원본을 설정한 후에는 모든 연결 정보를 다시 입력하지 않고도 해당 데이터베이스에서 데이터를 선택하고 검색하는 쿼리를 만들 때마다 사용할 수 있습니다. Microsoft Query는 데이터 원본을 사용하여 외부 데이터베이스에 연결하고 사용 가능한 데이터를 표시합니다. 쿼리를 만들고 Excel로 데이터를 반환한 후 Microsoft Query는 데이터를 새로 고치려는 경우 데이터베이스에 다시 연결할 수 있도록 쿼리 및 데이터 원본 정보가 모두 포함된 Excel 통합 문서를 제공합니다.

Microsoft 쿼리를 사용하여 데이터 가져오기     Microsoft Query를 사용하여 외부 데이터를 Excel로 가져오려면 다음 섹션에 자세히 설명된 다음 기본 단계를 수행합니다.

데이터 원본이란?     데이터 원본은 Excel 및 Microsoft Query가 외부 데이터베이스에 연결할 수 있도록 하는 저장된 정보 집합입니다. Microsoft Query를 사용하여 데이터 원본을 설정하는 경우 데이터 원본에 이름을 지정한 다음 데이터베이스 또는 서버의 이름 및 위치, 데이터베이스 유형, 로그온 및 암호 정보를 제공합니다. 이 정보에는 특정 유형의 데이터베이스에 연결하는 프로그램인 OBDC 드라이버 또는 데이터 원본 드라이버의 이름도 포함됩니다.

Microsoft 쿼리를 사용하여 데이터 원본을 설정하려면 다음을 수행합니다.

1. 데이터 탭의 외부 데이터 가져오기 그룹에서 다른 원본에서를 클릭한 다음 Microsoft 쿼리에서를 클릭합니다.

   참고: Excel 365는 Microsoft 쿼리 를 레거시 마법사 메뉴 그룹으로 이동했습니다.  이 메뉴는 기본적으로 표시되지 않습니다.  사용하도록 설정하려면 파일, 옵션, 데이터로 이동하고 레거시 데이터 가져오기 마법사 표시 섹션에서 사용하도록 설정합니다.

2. 다음 중 하나를 수행합니다.

   • 데이터베이스, 텍스트 파일 또는 Excel 통합 문서의 데이터 원본을 지정하려면 데이터베이스 탭을 클릭합니다.

   • OLAP 큐브 데이터 원본을 지정하려면 OLAP 큐브 탭을 클릭합니다. 이 탭은 Excel에서 Microsoft 쿼리를 실행한 경우에만 사용할 수 있습니다.

3. 새 데이터 원본><두 번 클릭합니다.

   -또는-

   새 데이터 원본><클릭한 다음 확인을 클릭합니다.

   새 데이터 원본 만들기 대화 상자가 표시됩니다.

4. 1단계에서 이름을 입력하여 데이터 원본을 식별합니다.

5. 2단계에서 데이터 원본으로 사용 중인 데이터베이스 유형에 대한 드라이버를 클릭합니다.

   참고 사항: 

   • 액세스하려는 외부 데이터베이스가 Microsoft Query와 함께 설치된 ODBC 드라이버에서 지원되지 않는 경우 데이터베이스 제조업체와 같은 타사 공급업체에서 Microsoft Office 호환 ODBC 드라이버를 가져와 설치해야 합니다. 설치 지침은 데이터베이스 공급업체에 문의하세요.

   • OLAP 데이터베이스에는 ODBC 드라이버가 필요하지 않습니다. Microsoft 쿼리를 설치하면 Microsoft SQL Server Analysis Services를 사용하여 만든 데이터베이스에 대한 드라이버가 설치됩니다. 다른 OLAP 데이터베이스에 연결하려면 데이터 원본 드라이버 및 클라이언트 소프트웨어를 설치해야 합니다.

6. 연결을 클릭한 다음 데이터 원본에 연결하는 데 필요한 정보를 제공합니다. 데이터베이스, Excel 통합 문서 및 텍스트 파일의 경우 사용자가 제공하는 정보는 선택한 데이터 원본 유형에 따라 달라집니다. 로그온 이름, 암호, 사용 중인 데이터베이스 버전, 데이터베이스 위치 또는 데이터베이스 유형과 관련된 기타 정보를 입력하라는 메시지가 표시될 수 있습니다.

   중요: 

   • 대문자, 소문자, 숫자 및 기호를 결합하는 강력한 암호를 사용합니다. 약한 암호의 경우 이러한 요소를 혼합하지 않습니다. 강력한 암호: Y6dh!et5. 약한 암호: House27. 암호 길이는 8자 이상이어야 합니다. 암호를 14자 이상 사용하는 것이 좋습니다.

   • 암호는 반드시 기억해야 합니다. 암호를 잊은 경우 Microsoft에서 사용자의 암호를 다시 찾을 수 없습니다. 따라서 암호를 따로 기록한 후 안전한 장소에 보관해 두세요.

7. 필요한 정보를 입력한 후 확인 또는 마침 을 클릭하여 새 데이터 원본 만들기 대화 상자로 돌아갑니다.

8. 데이터베이스에 테이블이 있고 특정 테이블이 쿼리 마법사에 자동으로 표시되도록 하려면 4단계 상자를 클릭한 다음 원하는 테이블을 클릭합니다.

9. 데이터 원본을 사용할 때 로그온 이름 및 암호를 입력하지 않으려면 데이터 원본 정의에서 내 사용자 ID 및 암호 저장 확인란을 선택합니다. 저장된 암호는 암호화되지 않습니다. 확인란을 사용할 수 없는 경우 데이터베이스 관리자를 참조하여 이 옵션을 사용할 수 있는지 여부를 확인합니다.

   보안 정보: 데이터 원본에 연결할 때 로그온 정보를 저장하지 마세요. 이 정보는 일반 텍스트로 저장될 수 있으며 악의적인 사용자가 정보에 액세스하여 데이터 원본의 보안을 손상시킬 수 있습니다.

이러한 단계를 완료하면 데이터 원본 선택 대화 상자에 데이터 원본 이름이 표시됩니다.

대부분의 쿼리에 쿼리 마법사 사용     쿼리 마법사를 사용하면 데이터베이스의 다른 테이블과 필드에서 데이터를 쉽게 선택하고 가져올 수 있습니다. 쿼리 마법사를 사용하여 포함할 테이블과 필드를 선택할 수 있습니다. 마법사가 한 테이블의 기본 키 필드와 두 번째 테이블에서 이름이 같은 필드를 인식할 때 내부 조인(두 테이블의 행이 동일한 필드 값을 기반으로 결합되도록 지정하는 쿼리 작업)이 자동으로 만들어집니다.

마법사를 사용하여 결과 집합을 정렬하고 간단한 필터링을 수행할 수도 있습니다. 마법사의 마지막 단계에서는 데이터를 Excel로 반환하거나 Microsoft 쿼리에서 쿼리를 추가로 구체화하도록 선택할 수 있습니다. 쿼리를 만든 후 Excel 또는 Microsoft 쿼리에서 실행할 수 있습니다.

쿼리 마법사를 시작하려면 다음 단계를 수행합니다.

1. 데이터 탭의 외부 데이터 가져오기 그룹에서 다른 원본에서를 클릭한 다음 Microsoft 쿼리에서를 클릭합니다.

2. 데이터 원본 선택 대화 상자에서 쿼리 마법사를 사용하여 쿼리 만들기/편집 확인란이 선택되어 있는지 확인합니다.

3. 사용하려는 데이터 원본을 두 번 클릭합니다.

   -또는-

   사용하려는 데이터 원본을 클릭한 다음 확인을 클릭합니다.

다른 유형의 쿼리에 대한 Microsoft 쿼리에서 직접 작업     쿼리 마법사에서 허용하는 것보다 더 복잡한 쿼리를 만들려면 Microsoft 쿼리에서 직접 작업할 수 있습니다. Microsoft 쿼리를 사용하여 쿼리 마법사에서 만들기 시작하는 쿼리를 보고 변경하거나 마법사를 사용하지 않고 새 쿼리를 만들 수 있습니다. 다음을 수행하는 쿼리를 만들려는 경우 Microsoft 쿼리에서 직접 작업합니다.

• 필드에서 특정 데이터 선택     큰 데이터베이스에서 필드의 일부 데이터를 선택하고 필요하지 않은 데이터를 생략할 수 있습니다. 예를 들어 많은 제품에 대한 정보가 포함된 필드에 있는 두 제품에 대한 데이터가 필요한 경우 조건 사용하여 원하는 두 제품에 대한 데이터만 선택할 수 있습니다.

• 쿼리를 실행할 때마다 다양한 조건에 따라 데이터 검색     동일한 외부 데이터의 여러 영역(예: 각 지역에 대한 별도 판매 보고서)에 대해 동일한 Excel 보고서 또는 요약을 만들어야 하는 경우 매개 변수 쿼리 만들 수 있습니다. 매개 변수 쿼리를 실행하면 쿼리에서 레코드를 선택할 때 값이 기준으로 사용할지 묻는 메시지가 표시됩니다. 예를 들어 매개 변수 쿼리는 특정 지역을 입력하라는 메시지를 표시할 수 있으며, 이 쿼리를 다시 사용하여 각 지역 판매 보고서를 만들 수 있습니다.

• 다양한 방법으로 데이터 조인     쿼리 마법사에서 만드는 내부 조인은 쿼리를 만드는 데 사용되는 가장 일반적인 조인 유형입니다. 그러나 경우에 따라 다른 유형의 조인을 사용하려고 합니다. 예를 들어 제품 판매 정보 테이블과 고객 정보 테이블이 있는 경우 내부 조인(쿼리 마법사에서 만든 형식)은 구매하지 않은 고객의 고객 레코드 검색을 방지합니다. Microsoft 쿼리를 사용하여 구매한 고객의 판매 데이터와 함께 모든 고객 레코드를 검색할 수 있도록 이러한 테이블을 조인할 수 있습니다.

Microsoft 쿼리를 시작하려면 다음 단계를 수행합니다.

1. 데이터 탭의 외부 데이터 가져오기 그룹에서 다른 원본에서를 클릭한 다음 Microsoft 쿼리에서를 클릭합니다.

2. 데이터 원본 선택 대화 상자에서 쿼리 마법사를 사용하여 쿼리 만들기/편집 확인란이 명확한지 확인합니다.

3. 사용하려는 데이터 원본을 두 번 클릭합니다.

   -또는-

   사용하려는 데이터 원본을 클릭한 다음 확인을 클릭합니다.

쿼리 재사용 및 공유     쿼리 마법사와 Microsoft 쿼리 모두에서 쿼리를 수정, 재사용 및 공유할 수 있는 .dqy 파일로 저장할 수 있습니다. Excel에서 .dqy 파일을 직접 열 수 있으므로 사용자 또는 다른 사용자가 동일한 쿼리에서 추가 외부 데이터 범위를 만들 수 있습니다.

Excel에서 저장된 쿼리를 열려면 다음을 수행합니다.

1. 데이터 탭의 외부 데이터 가져오기 그룹에서 다른 원본에서를 클릭한 다음 Microsoft 쿼리에서를 클릭합니다. 데이터 원본 선택 대화 상자가 표시됩니다.

2. 데이터 원본 선택 대화 상자에서 쿼리 탭을 클릭합니다.

3. 열려는 저장된 쿼리를 두 번 클릭합니다. 쿼리는 Microsoft 쿼리에 표시됩니다.

저장된 쿼리를 열려고 하고 Microsoft 쿼리가 이미 열려 있는 경우 Microsoft 쿼리 파일 메뉴를 클릭한 다음 열기를 클릭합니다.

.dqy 파일을 두 번 클릭하면 Excel이 열리고 쿼리가 실행된 다음 결과를 새 워크시트에 삽입합니다.

외부 데이터를 기반으로 하는 Excel 요약 또는 보고서를 공유하려면 다른 사용자에게 외부 데이터 범위가 포함된 통합 문서를 제공하거나 서식 파일 만들 수 있습니다. 템플릿을 사용하면 파일이 더 작도록 외부 데이터를 저장하지 않고 요약 또는 보고서를 저장할 수 있습니다. 사용자가 보고서 템플릿을 열면 외부 데이터가 검색됩니다.

쿼리 마법사 또는 Microsoft 쿼리에서 쿼리를 만든 후 Excel 워크시트로 데이터를 반환할 수 있습니다. 그런 다음 데이터는 서식을 지정하고 새로 고칠 수 있는 외부 데이터 범위 또는 피벗 테이블 보고서 됩니다.

검색된 데이터 서식 지정     Excel에서는 차트 또는 자동 부분합과 같은 도구를 사용하여 Microsoft Query에서 검색한 데이터를 표시하고 요약할 수 있습니다. 데이터의 서식을 지정할 수 있으며 외부 데이터를 새로 고칠 때 서식이 유지됩니다. 필드 이름 대신 고유한 열 레이블을 사용하고 행 번호를 자동으로 추가할 수 있습니다.

Excel에서는 이전 행과 일치하도록 범위 끝에 입력한 새 데이터의 서식을 자동으로 지정할 수 있습니다. Excel에서는 이전 행에서 반복된 수식을 자동으로 복사하여 추가 행으로 확장할 수도 있습니다.

언제든지 이 옵션을 켜거나 다시 끌 수 있습니다.

1. 파일 > 옵션 > 고급을 클릭합니다.

2. 편집 옵션 섹션에서 데이터 범위 형식 및 수식 확장 확인을 선택합니다. 자동 데이터 범위 서식을 다시 해제하려면 이 확인란의 선택을 취소합니다.

외부 데이터 새로 고침     외부 데이터를 새로 고치면 쿼리를 실행하여 사양과 일치하는 새 데이터나 변경된 데이터를 검색합니다. Microsoft 쿼리와 Excel 모두에서 쿼리를 새로 고칠 수 있습니다. Excel에서는 통합 문서를 열 때마다 데이터를 새로 고침하고 시간 간격으로 자동으로 새로 고치는 등 쿼리를 새로 고치는 몇 가지 옵션을 제공합니다. 데이터를 새로 고치는 동안 Excel에서 계속 작업할 수 있으며 데이터를 새로 고치는 동안 상태를 확인할 수도 있습니다. 자세한 내용은 Excel에서 외부 데이터 연결 새로 고침을 참조하세요.

맨 위로 이동