如何在Excel中使用SQL
您可以使用Excel进行大量强大的事项,例如连接到其他数据源。在本文中,我们将研究我们如何在Excel中使用SQL。
SQL是什么?
SQL代表“结构化查询语言”。Microsoft SQL Server只是使用它的众多数据库之一。在数据库中:
- 您将数据存储在表中
- 您可以运行SQL查询来检索数据。
使用Microsoft SQL Server这样的数据库来存储数据的优点包括:
- 数据是强类型的,这意味着您无法在日期字段中存储一个数字。这使您的数据立即验证。
- 它可以是关于多个项目数据的中央数据存储库。
- 多个人可以同时访问相同的数据。这减少了复制和不一致。
- 它在关系数据库管理系统内具有良好的内置安全性。Microsoft SQL Server提供多个安全性。
最后更新:2021年2月
Microsoft Excel Pivot表,GET和Transform(Power查询)和电源枢轴(DAX)进行高级数据分析。70-779。|由菲利普伯顿
探索课程欲了解更多信息,请参阅我的Udemy文章“Excel vs SQL Server。“
如何访问SQL Server?首先,您需要一个数据连接。如果你使用的是工作SQL Server,那么你的IT部门会给你服务器的详细信息。这将包括:
- 服务器名称:如果您拥有它,它也可以从连接字符串中取出。
- 身份验证方法:您将使用以下任一:
- Windows身份验证,使用您的Windows用户名和密码
- SQL Server身份验证,使用单独的用户名和密码
如果您自己的计算机上有Microsoft SQL Server,则服务器名称可以是“localhost”或“。”,您可能会使用Windows身份验证。
您可以使用此连接检索Microsoft SQL Server数据。
Excel中有三个不同的地方,您可以在其中加载SQL数据:
- 在主Excel窗口中
- 在Get和Transform窗口中(也称为电源查询编辑器)
- 在电源枢轴窗口中(也称为数据模型)
我们将逐一了解这些地方。
将SQL连接到主Excel窗口
主要Excel窗口是您每次打开Excel时都使用的窗口。要从SQL Server加载数据,请转到Data - 获取数据 - 从数据库 - 从SQL Server数据库中获取。这已经取代了先前使用的方法,例如Microsoft查询。
然后,您必须提供服务器名称。
您可以查询四个SQL Server数据源来返回结果。
- 您可能需要一个表中的数据。这是原始数据。
- 您可能需要先前创建的视图的查询结果。这是SQL Server数据分析的结果。
- 您可能需要从存储过程中的结果。这可能是一个更复杂的分析,或涉及参数的分析。例如,您可能只希望来自佛罗里达州的所有销售。在这里,'佛罗里达'将是一个参数。
- 您可能希望使用SELECT语句运行AD HOC SQL查询。
如果要运行存储过程或ad hoc查询,则在此阶段,您需要单击“高级选项”并在提供的框中写下查询。您还需要输入数据库的名称。
接下来,您需要提供身份验证模式和所需的任何凭据:
如果要检索表或查询的结果,则可以选择表格或查询。如果您然后单击“加载”,它将加载到您的Excel工作簿中。如果在本文的下一部分中单击“转换数据”,我们会查看会发生什么。
一旦完成了链接,它将将数据加载到Excel表中。然后,您可以像存储在表中的其他数据一样使用它。
您可以随时刷新数据,只需在表内右击并选择refresh,或者转到表设计-刷新。
连接SQL以获得和转换
连接SQL数据的第二种方法是使用Get和Transform窗口。
这遵循与上述连接到SQL Server的相同过程,除了按“转换数据”而不是加载。
完成此操作后,数据位于Get和Transform窗口中,也称为电源查询编辑器。
您还可以直接从电源查询编辑器加载数据。要做这件事,请去家 - 新来源。
然后,您可以在数据传输到Excel之前执行其他操作。例如,您可能想要:
- 隐藏一些列或行(通过进入首页-选择/删除列)
- 使用公式添加额外的列。(然而,Power Query使用一种名为M的语言,这与Excel有很大的不同。)
- 使用Group By函数总结数据
如果在Power查询中执行此操作,则会减少进入Excel的数据量。功率查询通过名为查询折叠的过程从SQL Server中减少了它从SQL Server接收的数据量。例如,您可以将表的所有内容检索到功率查询中,将行数限制为仅为50的行数,并减少用于仅为两个的列数。
这种减少将被合并到SQL语句中,以便Excel只从SQL Server检索所需的行和列。这减少了网络流量,提高了检索数据的速度。
当你离开“电源查询”窗口,点击“首页-关闭&加载”,它会像以前一样将数据加载到Excel表格中。
但是,如果您转到“首页 - 关闭并加载...”,那么您可以:
- 在枢轴表或图表中使用它,而无需将数据作为表作为表格。
- 将其保存为连接(不将数据加载到Excel表中)。
如果将其保存为连接,则可以稍后使用它作为任何新枢轴表中的数据源。
在“保存和加载到...”中,“将此数据添加到数据模型”复选框。如果您单击此项,则Excel将将数据导出到Power Pivot,也称为数据模型。我们将在本文的下一部分中查看数据模型。
将SQL连接到Power Pivot
将SQL直接连接到Excel的第三种方式是通过使用数据模型,也称为Power Pivot。要打开数据模型,您需要转到数据 - 管理数据模型。
然后你可以导入数据到Power Pivot通过去Home -获取外部数据-从数据库-从SQL Server。然后按照与前面类似的过程连接到SQL Server。
导入数据后,您可以创建计算列或度量。Power Pivot使用称为Dax的公式语言来构建公式。DAX是Excel公式的扩展版本。
完成后,您可以通过进入归档 - 数据透视表来创建电源枢轴表。
这允许您从该数据创建数据透视表或图表。
到哪里去获取更多信息
我希望你喜欢这篇文章。
您是否对电源查询或电源枢轴感兴趣?那为什么不加我的人用Microsoft Excel分析和可视化数据当然,我们在那里看看这些主题,以及枢轴表。
你想快速学习SQL语句吗?请看我的"一个小时内的SQL Server Essentials”课程。我们将看看SQL SELECT语句的六个主要子句:SELECT、FROM、WHERE、GROUP BY、HAVING和ORDER BY。