Mac용 Excel에서 데이터 가져오기 및 셰이프(Power Query)

Mac용 Excel은 파워 쿼리(가져와 변환하기라고도 함) 기술을 통합하여 데이터 원본을 가져오고, 새로 고치고, 인증하고, 파워 쿼리 데이터 원본을 관리하고, 자격 증명을 지우고, 파일 기반 데이터 원본의 위치를 변경하고, 요구 사항에 맞는 테이블로 데이터를 셰이프할 때 더 큰 기능을 제공합니다. VBA를 사용하여 파워 쿼리의 쿼리를 만들 수도 있습니다.

참고: SQL Server 데이터베이스 데이터 원본은 참가자 베타에서만 가져올 수 있습니다.

Excel 통합 문서, 텍스트/CSV, XML, JSON, SQL Server 데이터베이스, SharePoint Online 목록, OData, 빈 테이블, 빈 쿼리 등 다양한 데이터 원본에서 파워 쿼리를 사용하여 데이터를 Excel로 가져올 수 있습니다.

데이터 > 데이터 가져오기를 선택합니다.
원하는 데이터 원본을 선택하려면 데이터 가져오기(파워 쿼리)를 선택합니다.
데이터 원본 선택 대화 상자에서 사용 가능한 데이터 원본 중 하나를 선택합니다.
데이터 원본에 연결합니다. 각 데이터 원본에 연결하는 방법에 대한 자세한 내용은 데이터 원본에서 데이터 가져오기를 참조하세요.
가져올 데이터를 선택합니다.
로드 단추를 클릭하여 데이터를 로드합니다.

결과

가져온 데이터가 새 시트에 나타납니다.

쿼리에 대한 일반적인 결과

다음 단계

Power Query 편집기 사용하여 데이터를 셰이프하고 변환하려면 데이터 변환을 선택합니다. 자세한 내용은 Power Query 편집기를 사용하여 데이터 셰이프를 참조하세요.

참고: 이 기능은 Mac용 Excel 버전 16.69(23010700) 이상을 실행하는 Microsoft 365 구독자에게 일반 공급됩니다. Microsoft 365 구독자인 경우 최신 Office 버전을 보유하고 있는지 확인하세요.

절차

데이터 > 데이터 가져오기(파워 쿼리)를 선택합니다.
쿼리 편집기를 열려면 파워 쿼리 편집기 시작을 선택합니다.

팁: 데이터 가져오기(파워 쿼리)를 선택하고 데이터 원본을 선택한 다음 다음을 클릭하여 쿼리 편집기에 액세스할 수도 있습니다.
Windows용 Excel에서와 같이 쿼리 편집기를 사용하여 데이터를 셰이프 및 변환할 수 있습니다. 자세한 내용은 Excel용 파워 쿼리 도움말을 참조하세요.
완료되면 홈 > 닫고 로드하기를 선택합니다.

결과

새로 가져온 데이터가 새 시트에 나타납니다.

쿼리에 대한 일반적인 결과

SharePoint 파일, SharePoint 목록, SharePoint 폴더, OData, 텍스트/CSV 파일, Excel 통합 문서(.xlsx), XML 및 JSON 파일, 로컬 테이블 및 범위, Microsoft SQL Server 데이터베이스 등의 데이터 원본을 새로 고칠 수 있습니다.

처음 새로 고침

통합 문서 쿼리에서 파일 기반 데이터 원본을 처음 새로 고치려고 할 때 파일 경로를 업데이트해야 할 수도 있습니다.

데이터, 데이터 가져오기 옆 화살표를 선택한 다음 데이터 원본 설정을 선택합니다. 데이터 원본 설정 대화 상자가 나타납니다.
연결을 선택한 다음 파일 경로 변경을 선택합니다.
파일 경로 대화 상자에서 새 위치를 선택한 다음 데이터 가져오기를 선택합니다.
닫기를 선택합니다.

이후 시간 새로 고침

다음 작업을 통해 새로 고칩니다.

통합 문서의 모든 데이터 원본에서 데이터 > 모두 새로 고침을 선택합니다.
특정 데이터 원본에서 시트의 쿼리 테이블을 마우스 오른쪽 단추로 클릭한 다음 새로 고침을 선택합니다.
피벗 테이블에서는 피벗 테이블의 셀을 선택한 다음 피벗 테이블 분석 > 데이터 새로 고침을 선택합니다.

SharePoint, SQL Server, OData 또는 사용 권한이 필요한 기타 데이터 원본에 처음 액세스할 때는 적절한 자격 증명을 제공해야 합니다. 자격 증명을 지우고 새 자격 증명을 입력할 수도 있습니다.

자격 증명 입력

쿼리를 처음 새로 고치면 로그인하라는 메시지가 표시될 수 있습니다. 인증 방법을 선택하고 로그인 자격 증명을 지정하여 데이터 원본에 연결하고 새로 고침을 계속합니다.

로그인이 필요한 경우 자격 증명 입력 대화 상자가 나타납니다.

예를 들면 다음과 같습니다.

SharePoint 자격 증명:
SQL Server 자격 증명:

자격 증명 지우기

데이터 > 데이터 가져오기 > 데이터 원본 설정을 선택합니다.
데이터 원본 설정 대화 상자에서 원하는 연결을 선택합니다.
아래쪽에서 Permissio 지우기ns를 선택합니다.
실제로 이 작업을 수행할지 확인한 다음 삭제를 선택합니다.

Mac용 Excel에서는 Power Query 편집기 작성을 사용할 수 없지만 VBA는 파워 쿼리 작성을 지원합니다. 파일의 VBA 코드 모듈을 Windows용 Excel에서 Mac용 Excel로 전송하는 작업은 2단계로 이루어집니다. 이 섹션의 마지막 부분에 샘플 프로그램이 제공됩니다.

1단계: Windows용 Excel

Windows용 Excel에서 VBA를 사용하여 쿼리를 생성합니다. Excel의 개체 모델에서 다음 엔터티를 사용하는 VBA 코드는 Mac용 Excel에서도 작동합니다. 쿼리 개체, WorkbookQuery 개체, Workbook.Queries 속성. 자세한 내용은 Excel VBA 참고자료를 참조하세요.
Excel에서 ALT+F11을 눌러 Visual Basic 편집기가 열려 있는지 확인합니다.
모듈을 마우스 오른쪽 단추로 클릭한 다음 파일 내보내기를 선택합니다. 내보내기 대화 상자가 나타납니다.
파일 이름을 입력하고 파일 확장자가 .bas인지 확인한 다음 저장을 선택합니다.
VBA 파일을 온라인 서비스에 업로드하여 Mac에서 파일에 액세스할 수 있도록 합니다. Microsoft OneDrive를 사용할 수 있습니다. 자세한 내용은 Mac OS X에서 OneDrive를 사용하여 파일 동기화를 참조하세요.

2단계: Mac용 Excel

"1단계: Windows용 Excel"에서 저장하여 온라인 서비스에 업로드한 로컬 VBA 파일에 VBA 파일을 다운로드합니다.
Mac용 Excel에서 도구 > 매크로 > Visual Basic 편집기를 선택합니다. Visual Basic 편집기 창이 나타납니다.
프로젝트 창에서 마우스 오른쪽 단추로 개체를 클릭한 다음 파일 가져오기를 선택합니다. 파일 가져오기 대화 상자가 나타납니다.
VBA 파일을 찾은 다음 열기를 선택합니다.

샘플 코드

다음은 조정하여 사용할 수 있는 몇 가지 기본 코드입니다. 1에서 100까지의 값으로 목록을 만드는 샘플 쿼리입니다.

Sub CreateSampleList()
    ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
        "let" & vbCr & vbLf & _
            "Source = {1..100}," & vbCr & vbLf & _
            "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
            "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
        "in" & vbCr & vbLf & _
            "RenamedColumns"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SampleList]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SampleList"
        .Refresh BackgroundQuery:=False
    End With
End Sub