Excel for Mac 结合了 Power Query(也称为“获取和转换”)技术,可在导入、刷新和身份验证数据源、管理 Power Query 数据源、清除凭据、更改基于文件的数据源的位置以及将数据调整为符合要求的表时提供更大的功能。 还可以使用 VBA 创建 Power Query 查询。 

注意: SQL Server 数据库数据源只能在预览体验成员 Beta 版中导入。

可以使用来自各种数据源的 Power Query 将数据导入 Excel: Excel 工作簿、文本/CSV、XML、JSON、SQL Server 数据库、SharePoint Online 列表、OData、空白表和空白查询。

  1. 选择 数据 > 获取数据 PQ Mac Get Data (Power Query).png

  2. 若要选择所需的数据源,请选择 获取数据 (Power Query)。

  3. 选择数据源 对话框中,选择一个可用的数据源。  要在对话框中选择的数据源示例

  4. 连接到数据源。 若要详细了解如何连接到每个数据源,请参阅 从数据源导入数据

  5. 选择要导入的数据。

  6. 单击 加载 按钮加载数据。

结果

导入的数据将显示在新工作表中。

查询的典型结果

后续步骤

若要使用 Power Query 编辑器来调整和转换数据,请选择 转换数据。 有关详细信息,请参阅 Power Query 编辑器形状数据

注意: 此功能已正式发布给 Microsoft 365 订阅者,运行版本 16.69 (23010700) 或更高版本的 Excel for Mac。 如果你是 Microsoft 365 订阅者,请确保拥有最新版本的 Office。

过程

  1. 选择 数据 > 获取数据 (Power Query)

  2. 若要打开查询编辑器,请选择 启动 Power Query 编辑器 PQ Mac Editor.png

    提示: 还可以通过选择 获取数据 (Power Query),选择数据源,然后单击 下一步 来访问 Power Query。

  3. 像在 Excel for Windows 中一样,使用查询编辑器来调整和转换数据。 Power Query 编辑器 有关详细信息,请参阅 Power Query Excel 帮助

  4. 完成后,选择 主页 > 关闭并加载

结果

新导入的数据将显示在新工作表中。

查询的典型结果

可以刷新以下数据源:SharePoint 文件、SharePoint 列表、SharePoint 文件夹、OData、text/CSV 文件、Excel 工作簿 (.xlsx)、XML 和 JSON 文件、本地表和范围以及 Microsoft SQL Server 数据库。

第一次刷新

首次尝试在工作簿查询中刷新基于文件的数据源时,可能需要更新文件路径。

  1. 选择 数据获取数据旁边的箭头,然后选择 数据源设置。 将显示 数据源设置 对话框。

  2. 选择连接,然后选择 更改文件路径

  3. 文件路径 对话框中,选择一个新位置,然后选择 获取数据

  4. 选择“关闭”。

刷新后续时间

若要刷新:

  • 工作簿中的所有数据源,选择 数据 > 全部刷新

  • 特定数据源,右键单击工作表上的查询表,然后选择 刷新

  • 数据透视表,在数据透视表中选择一个单元格,然后选择 数据透视表分析刷新数据

首次访问 SharePoint、SQL Server、OData 或其他需要权限的数据源时,必须提供适当的凭据。 你可能还需要清除凭据以输入新凭据。

输入凭据

首次刷新查询时,系统可能会要求你登录。 选择身份验证方法并指定登录凭据以连接到数据源并继续刷新。

如果需要登录,则将显示 输入凭据 对话框。

例如:

  • SharePoint 凭据: Mac 上 的SharePoint 凭据提示

  • SQL Server 凭据: 用于输入服务器、数据库和凭据的 SQL Server 对话框

清除凭据

  1. 选择 数据 > 获取数据 > 数据源设置

  2. 数据源设置 对话框中,选择所需的连接。

  3. 在底部,选择 清除许

  4. 确认这是你想要执行的操作,然后选择 删除

尽管 Excel for Mac 中不提供 Power Query 编辑器创作,但 VBA 支持 Power Query 创作。 将文件中的 VBA 代码模块从 Excel for Windows 传输到 Excel for Mac 的过程分为两步。 本部分末尾提供了一个示例程序。

步骤 1:Excel for Windows

  1. 在 Excel Windows 上,使用 VBA 开发查询。 在 Excel 对象模型中使用以下实体的 VBA 代码也适用于 Excel for Mac: 查询对象,WorkbookQuery 对象,Workbook.Queries 属性。有关详细信息,请参阅 Excel VBA 参考

  2. 在 Excel 中,通过按 ALT+F11 确保 Visual Basic 编辑器已打开。

  3. 右键单击该模块,然后选择 导出文件。 此时将出现 导出对话框。

  4. 输入文件名,确保文件扩展名为 .bas,然后选择 保存

  5. 将 VBA 文件上传到联机服务,使该文件可从 Mac 访问。 可以使用 Microsoft OneDrive。 有关详细信息,请参阅 在 Mac OS X 上与 OneDrive 同步文件。

步骤 2:Excel for Mac

  1. 将 VBA 文件下载到本地文件,即保存在“步骤一:Excel for Windows”中并上传到联机服务的 VBA 文件。

  2. 在 Excel for Mac 中,选择 工具 > > Visual Basic 编辑器。 将显示 Visual Basic 编辑器 窗口。

  3. 右键单击“项目”窗口中的对象,然后选择 导入文件。 将显示 导入文件 对话框。

  4. 找到 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

  1. 打开 Excel 工作簿。

  2. 如果收到有关禁用外部数据连接的安全警告,请选择 启用内容

  3. 如果出现 授予文件访问权限 对话框,请选择 选择,然后选择向包含数据源文件的顶级文件夹 授予访问权限

  4. 选择 数据 > 来自文本(旧的)对话框出现。

  5. 找到 .txt 或 .csv 文件,然后选择 打开。 将显示 文本导入向导提示    反复检查所选 数据预览 窗格以确认你的选择。

  6. 在第一页中,执行以下操作:

    文件类型    若要选择文本文件的类型,请选择 带分隔符的 固定宽度的行号    在开始导入于行 中,选择一个行号以指定要导入的数据的第一行。字符集    在 文件来源中,选择文本文件中使用的字符集。 在大多数情况下,可以将此设置保留为默认值。

  7. 在第二页中,执行以下操作:分隔 如果在第一页上选择了 分隔 ,请在 分隔符下,选择分隔符或使用“其他”复选框输入未列出的分隔符。 如果数据字段之间包含一个以上字符的分隔符或多个自定义分隔符,请选择 将连续的分隔符视为一个文本限定符中,选择文本文件中包含值的字符,该字符通常是引号 (") 字符。

    固定宽度 如果在第一页上选择了 固定宽度 ,请按照说明在 所选数据的预览 框中创建、删除或移动断线。

  8. 在第三页中,执行以下操作: 对于 所选数据预览 下的每列,选择它,然后根据需要将其更改为其他列格式。 可以进一步设置日期格式,并选择 高级 以更改数值数据设置。 还可以在导入数据后转换数据。 选择 完成。 将显示 导入数据 对话框。

  9. 选择要添加数据的位置:在现有工作表、新工作表或数据透视表中。

  10. 选择“确定”。

    若要确保连接正常工作,请输入一些数据,然后选择 连接 > 刷新

  1. 选择 数据 > 来自 SQL Server ODBC。 将显示“连接到 SQL Server ODBC 数据源”对话框。 用于输入服务器、数据库和凭据的 SQL Server 对话框

  2. 服务器名称 框中输入服务器,也可以在 数据库名称 框中输入数据库。 从数据库管理员处获取此信息。

  3. 身份验证下,从列表中选择一种方法:用户名/密码KerberosNTLM

  4. 用户名密码 框中输入凭据。

  5. 选择 连接。 将显示 导航器 对话框。

  6. 在左窗格中,导航到所需的表,然后选择它。

  7. 确认右窗格上的 SQL 语句。 可以根据需要更改 SQL 语句。

  8. 若要预览数据,请选择 运行

  9. 准备就绪后,选择 返回数据。 将显示 导入数据 对话框。  用于定位数据的“导入数据”对话框

  10. 选择要添加数据的位置:在现有工作表、新工作表或数据透视表中。

  11. 要在 属性 对话框的 用法 定义 选项卡上设置连接属性,请选择 属性。 导入数据后,还可以选择 数据 > 连接,然后在 连接属性对话框中选择 属性

  12. 选择“确定”。

  13. 若要确保连接正常工作,请输入一些数据,然后选择 数据 > 刷新全部

如果要使用 SQL 数据库(例如 FileMaker Pro)的外部源,可以使用 Mac 上安装的开放数据库连接 (ODBC) 驱动程序。 此网页提供有关驱动程序的信息。 安装数据源的驱动程序后,请执行以下步骤:

  1. 选择 数据 > 来自数据库 (Microsoft Query)

  2. 添加你的数据库的数据源,然后选择 OK

  3. 在 SQL Server 凭据提示符处,输入身份验证方法、用户名和密码。 备用文字

  4. 在左侧,选择服务器旁边的箭头以查看数据库。

  5. 选择所需数据库旁边的箭头。

  6. 选择所需的表。

  7. 若要预览数据,请选择 运行

  8. 准备就绪后,选择 返回数据

  9. 导入数据 对话框中,选择数据的放置位置:现有工作表中、新工作表中或数据透视表中。

  10. 选择“确定”。

  11. 若要确保连接正常工作,请输入一些数据,然后选择 数据 > 刷新全部

如果你的权限不起作用,则不能先清除它们,然后登录。

  1. 选择 数据 > 连接。 将显示 工作簿连接 对话框。

  2. 在列表中选择所需的连接,然后选择 清除权限 删除 Mac 上数据连接的已保存凭据

另请参阅

Microsoft Power Query for Excel 帮助

与 Excel for Mac 兼容的 ODBC 驱动程序

创建数据透视表以分析工作表数据

需要更多帮助?

需要更多选项?

了解订阅权益、浏览培训课程、了解如何保护设备等。

社区可帮助你提出和回答问题、提供反馈,并听取经验丰富专家的意见。