Excel

아티클
02/21/2024

요약

항목	설명
릴리스 상태	일반 공급
제품	Excel Power BI(의미 체계 모델) Power BI(데이터 흐름) 패브릭(데이터 흐름 Gen2) Power Apps(데이터 흐름) Dynamics 365 Customer Insights Analysis Services
지원되는 인증 유형	익명(온라인) 기본(온라인) 조직 계정(온라인)
함수 참조 설명서	Excel.Workbook Excel.CurrentWorkbook

참고 항목

일부 기능은 한 제품에 있을 수 있지만 배포 일정 및 호스트별 기능으로 인해 다른 기능은 없을 수 있습니다.

필수 조건

레거시 통합 문서(예: .xls 또는 .xlsb)에 연결하려면 ACCESS 데이터베이스 엔진 OLEDB(또는 ACE) 공급자가 필요합니다. 이 공급자를 설치하려면 다운로드 페이지로 이동하여 관련 버전(32비트 또는 64비트)을 설치합니다. 설치되지 않은 경우 레거시 통합 문서에 연결할 때 다음 오류가 표시됩니다.

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ACE는 클라우드 서비스 환경에 설치할 수 없습니다. 따라서 클라우드 호스트(예: 파워 쿼리 온라인)에서 이 오류가 표시되는 경우 ACE가 설치된 게이트웨이를 사용하여 레거시 Excel 파일에 연결해야 합니다.

지원되는 기능

가져오기

파워 쿼리 데스크톱에서 Excel 통합 문서로 커넥트

파워 쿼리 데스크톱에서 연결하려면 다음을 수행합니다.

데이터 가져오기 환경에서 Excel 통합 문서를 선택합니다. 파워 쿼리 데스크톱의 데이터 가져오기 환경은 앱마다 다릅니다. 파워 쿼리 데스크톱 앱에 대한 데이터 환경 가져오기에 대한 자세한 내용은 데이터를 가져올 위치로 이동합니다.
로드할 Excel 통합 문서를 찾아 선택합니다. 그런 다음, 열기를 선택합니다.

Excel 통합 문서가 온라인인 경우 웹 커넥터를 사용하여 통합 문서에 연결합니다.
탐색기에서 원하는 통합 문서 정보를 선택한 다음 로드를 선택하여 데이터를 로드하거나 데이터 변환을 선택하여 Power Query 편집기 데이터를 계속 변환합니다.

파워 쿼리 Online에서 Excel 통합 문서로 커넥트

파워 쿼리 Online에서 연결하려면 다음을 수행합니다.

데이터 가져오기 환경에서 Excel 통합 문서 옵션을 선택합니다. 다른 앱에는 파워 쿼리 온라인 데이터 가져오기 환경에 액세스하는 다양한 방법이 있습니다. 파워 쿼리 온라인 앱에서 데이터 환경을 가져오는 방법에 대한 자세한 내용은 데이터를 가져올 위치로 이동합니다.
표시되는 Excel 대화 상자에서 Excel 통합 문서의 경로를 제공합니다.
필요한 경우 온-프레미스 데이터 게이트웨이를 선택하여 Excel 통합 문서에 액세스합니다.
이 Excel 통합 문서에 처음 액세스한 경우 인증 종류를 선택하고 계정에 로그인합니다(필요한 경우).
탐색기에서 원하는 통합 문서 정보를 선택한 다음 데이터를 변환하여 Power Query 편집기 데이터를 계속 변환합니다.

제안된 테이블

단일 테이블을 구체적으로 포함하지 않는 Excel 통합 문서에 연결하는 경우 파워 쿼리 탐색기에서 선택할 수 있는 제안된 테이블 목록을 만들려고 시도합니다. 예를 들어 A1에서 C5까지의 데이터, D8에서 E10까지의 더 많은 데이터 및 C13에서 F16까지의 데이터가 포함된 다음 통합 문서 예제를 고려해 보세요.

세 개의 데이터 집합이 있는 Excel 통합 문서의 스크린샷

파워 쿼리의 데이터에 연결하면 파워 쿼리 탐색기에서 두 개의 목록을 만듭니다. 첫 번째 목록에는 전체 통합 문서 시트가 포함되고, 두 번째 목록에는 제안된 테이블 3개가 포함됩니다.

탐색기에서 전체 시트를 선택하면 통합 문서가 Excel에 표시되고 빈 셀이 모두 null로 채워집니다.

제안된 테이블 중 하나를 선택하면 파워 쿼리가 통합 문서의 레이아웃에서 확인할 수 있었던 각 개별 테이블이 탐색기에서 표시됩니다. 예를 들어 표 3을 선택하면 원래 셀 C13에서 F16까지의 셀에 나타난 데이터가 표시됩니다.

참고 항목

시트가 충분히 변경되면 테이블이 제대로 새로 고쳐지지 않을 수 있습니다. 데이터를 다시 가져오고 제안된 새 테이블을 선택하여 새로 고침을 수정할 수 있습니다.

문제 해결

숫자 정밀도(또는 "내 숫자가 변경된 이유는 무엇인가요?")

Excel 데이터를 가져올 때 파워 쿼리로 가져올 때 특정 숫자 값이 약간 변경되는 것을 알 수 있습니다. 예를 들어 Excel에서 0.049가 포함된 셀을 선택하면 이 숫자가 수식 입력줄에 0.049로 표시됩니다. 그러나 동일한 셀을 파워 쿼리로 가져와서 선택하면 미리 보기 세부 정보에 0.04900000000000000002로 표시됩니다(미리 보기 표에서는 0.049 형식임에도 불구하고). 무슨 일이 일어나고 있는 건가요?

대답은 약간 복잡하며 Excel에서 이진 부동 소수점 표기법이라는 항목을 사용하여 숫자를 저장하는 방법과 관련이 있습니다. 결론은 Excel에서 100% 정밀도로 나타낼 수 없는 특정 숫자가 있다는 것입니다. .xlsx 파일을 열고 저장되는 실제 값을 확인하면 .xlsx 파일에 0.049 가 실제로 0.049000000000000000으로 저장되는 것을 볼 수 있습니다. 파워 쿼리가 .xlsx 읽는 값이므로 파워 쿼리에서 셀을 선택할 때 나타나는 값입니다. 파워 쿼리의 숫자 정밀도에 대한 자세한 내용은 다음의 "10진수" 및 "고정 소수 자릿수" 섹션 으로 이동하세요.파워 쿼리의 데이터 형식입니다.)

온라인 Excel 통합 문서에 커넥트

Sharepoint에서 호스트되는 Excel 문서에 연결하려면 Power BI Desktop, Excel 및 데이터 흐름의 웹 커넥터와 데이터 흐름의 Excel 커넥터를 통해 연결할 수 있습니다. 파일에 대한 링크를 얻으려면 다음을 수행합니다.

Excel Desktop에서 문서를 엽니다.
파일 메뉴를 열고 정보 탭을 선택한 다음, 경로 복사를 선택합니다.
주소를 파일 경로 또는 URL 필드에 복사하고 주소 끝에서 ?web=1을 제거합니다.

레거시 ACE 커넥터

파워 쿼리는 ACCESS 데이터베이스 엔진(또는 ACE) OLEDB 공급자를 사용하여 레거시 통합 문서(예: .xls 또는 .xlsb)를 읽습니다. 따라서 OpenXML 통합 문서(예: .xlsx)를 가져올 때 발생하지 않는 레거시 통합 문서를 가져올 때 예기치 않은 동작이 발생할 수 있습니다. 다음은 몇 가지 일반적인 예제입니다.

예기치 않은 값 서식 지정

ACE로 인해 레거시 Excel 통합 문서의 값을 예상보다 정밀도나 충실도가 낮은 값으로 가져올 수 있습니다. 예를 들어 Excel 파일에 표시할 서식이 "1,024.23"인 숫자 1024.231이 포함되어 있다고 상상해 보십시오. 파워 쿼리로 가져올 때 이 값은 기본 전체 충실도 번호(1024.231)가 아닌 텍스트 값 "1,024.23"으로 표시됩니다. 이 경우 ACE는 파워 쿼리에 기본 값을 표시하지 않고 Excel에 표시되는 값만 표시하기 때문입니다.

예기치 않은 null 값

ACE가 시트를 로드할 때 처음 8개 행을 확인하여 열의 데이터 형식을 확인합니다. 처음 8개 행이 이후 행을 대표하지 않는 경우 ACE는 해당 열에 잘못된 형식을 적용하고 형식과 일치하지 않는 값에 대해 null을 반환할 수 있습니다. 예를 들어 열에 처음 8개 행(예: 1000, 1001 등)에 숫자가 포함되지만 이후 행에 숫자가 아닌 데이터(예: "100Y" 및 "100Z")가 있는 경우 ACE는 열에 숫자가 포함되고 숫자가 아닌 값은 null로 반환됩니다.

일관성 없는 값 서식 지정

경우에 따라 ACE는 새로 고침 간에 완전히 다른 결과를 반환합니다. 서식 섹션에 설명된 예제를 사용하면 "1,024.23" 대신 값 1024.231이 갑자기 표시될 수 있습니다. 이 차이는 레거시 통합 문서를 파워 쿼리로 가져오는 동안 Excel에서 열어 두면 발생할 수 있습니다. 이 문제를 해결하려면 통합 문서를 닫습니다.

Excel 데이터 누락 또는 불완전

경우에 따라 파워 쿼리가 Excel 워크시트에서 모든 데이터를 추출하지 못합니다. 이 오류는 워크시트에 잘못된 차원이 있기 때문에 발생합니다(예: 실제 데이터가 3개 이상의 열 또는 200개 행을 차지하는 경우의 A1:C200 차원이 있는 경우).

잘못된 차원을 진단하는 방법

워크시트의 차원을 보려면 다음을 수행합니다.

xlsx 파일의 이름을 .zip 확장명으로 바꿉니다.
파일을 파일 탐색기 엽니다.
xl\worksheets로 이동합니다.
문제가 있는 시트의 xml 파일(예: Sheet1.xml)을 zip 파일에서 다른 위치로 복사합니다.
파일의 처음 몇 줄을 검사합니다. 파일이 충분히 작으면 텍스트 편집기에서 엽니다. 파일이 너무 커서 텍스트 편집기에서 열 수 없을 경우 명령 프롬프트에서 다음 명령을 실행합니다 . 더 많은 Sheet1.xml.
태그( <dimension .../> 예: <dimension ref="A1:C200" />)를 찾습니다.

파일에 단일 셀(예: <dimension ref="A1" />)을 가리키는 차원 특성이 있는 경우 파워 쿼리는 이 특성을 사용하여 시트에서 데이터의 시작 행과 열을 찾습니다.

그러나 파일에 여러 셀(예: <dimension ref="A1:AJ45000"/>)을 가리키는 차원 특성이 있는 경우 파워 쿼리는 이 범위를 사용하여 시작 행과 열 , 끝 행 및 열을 찾습니다. 이 범위에 시트의 모든 데이터가 포함되어 있지 않으면 일부 데이터가 로드되지 않습니다.

잘못된 차원을 수정하는 방법

다음 작업 중 하나를 수행하여 잘못된 차원으로 인한 문제를 해결할 수 있습니다.

Excel에서 문서를 열고 다시 저장합니다. 이 작업은 파일에 저장된 잘못된 차원을 올바른 값으로 덮어씁니다.
Excel 파일을 생성한 도구가 차원을 올바르게 출력하도록 고정되어 있는지 확인합니다.
잘못된 차원을 무시하도록 M 쿼리를 업데이트합니다. 2020년 12월 파워 쿼리 Excel.Workbook 릴리스를 기준으로 이제 옵션을 지원합니다 InferSheetDimensions . true이면 이 옵션을 사용하면 함수가 통합 문서에 저장된 차원을 무시하고 대신 데이터를 검사하여 결정합니다.

이 옵션을 제공하는 방법의 예는 다음과 같습니다.

Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

Excel 데이터의 느린 로드는 잘못된 차원으로 인해 발생할 수도 있습니다. 그러나 이 경우 속도가 느려지는 것은 크기가 너무 작지 않고 필요한 것보다 훨씬 커지기 때문입니다. 크기가 너무 크면 파워 쿼리가 실제로 필요한 것보다 훨씬 많은 양의 데이터를 통합 문서에서 읽습니다.

이 문제를 해결하려면 워크시트의 마지막 셀 찾기 및 재설정을 참조하여 자세한 지침을 확인할 수 있습니다.

컴퓨터 또는 SharePoint에서 Excel에서 데이터를 검색할 때는 관련된 데이터의 볼륨과 통합 문서의 복잡성을 모두 고려합니다.

SharePoint에서 매우 큰 파일을 검색할 때 성능이 저하됩니다. 그러나 이것은 문제의 한 부분일 뿐입니다. SharePoint에서 검색되는 Excel 파일에 중요한 비즈니스 논리가 있는 경우 데이터를 새로 고칠 때 이 비즈니스 논리를 실행해야 하므로 계산이 복잡해질 수 있습니다. 데이터를 집계 및 미리 계산하거나, 더 많은 비즈니스 논리를 Excel 계층에서 파워 쿼리 계층으로 이동하는 것이 좋습니다.

Excel 커넥터를 사용하여 CSV 파일을 가져올 때 오류 발생

CSV 파일은 Excel에서 열 수 있지만 Excel 파일은 아닙니다. 대신 Text/CSV 커넥터를 사용합니다.

"Strict Open XML 스프레드시트" 통합 문서를 가져올 때 오류 발생

Excel의 "엄격한 열기 XML 스프레드시트" 형식으로 저장된 통합 문서를 가져올 때 다음 오류가 표시될 수 있습니다.

DataFormat.Error: The specified package is invalid. The main part is missing.

이 오류는 ACE 드라이버가 호스트 컴퓨터에 설치되지 않은 경우에 발생합니다. "Strict Open XML 스프레드시트" 형식으로 저장된 통합 문서는 ACE에서만 읽을 수 있습니다. 그러나 이러한 통합 문서에서는 일반 Open XML 통합 문서(.xlsx)와 동일한 파일 확장자를 사용하므로 확장명은 일반적인 the Access Database Engine OLEDB provider may be required to read this type of file 오류 메시지를 표시할 수 없습니다.

오류를 해결하려면 ACE 드라이버를 설치합니다. 클라우드 서비스에서 오류가 발생하는 경우 ACE 드라이버가 설치된 컴퓨터에서 실행되는 게이트웨이를 사용해야 합니다.

"파일에 손상된 데이터가 포함됨" 오류

특정 Excel 통합 문서를 가져올 때 다음 오류가 표시될 수 있습니다.

DataFormat.Error: File contains corrupted data.

일반적으로 이 오류는 파일 형식에 문제가 있음을 나타냅니다.

그러나 파일이 open XML 파일(예: .xlsx)인 것처럼 보이지만 실제로 파일을 처리하기 위해 ACE 드라이버가 필요한 경우 이 오류가 발생할 수 있습니다. ACE 드라이버가 필요한 파일을 처리하는 방법에 대한 자세한 내용은 레거시 ACE 커넥터 섹션으로 이동합니다.

알려진 문제 및 제한 사항

파워 쿼리 Online에서 암호화된 Excel 파일에 액세스할 수 없습니다. "Public" 또는 "Non-Business"가 아닌 민감도 형식으로 레이블이 지정된 Excel 파일은 암호화되므로 파워 쿼리 Online을 통해 액세스할 수 없습니다.
파워 쿼리 온라인은 암호로 보호되는 Excel 파일을 지원하지 않습니다.