1. 首页
  2. 自学中心
  3. 软件
  4. Excel

SQL在Excel中的应用

在Excel中使用SQL语句对多个数据表进行精确筛选

来自 http://343766868.blog.163.com/blog/static/48314056201131035011833/

在使用Excel的时候,常常会遇到这样的问题:有2个数据表,数据表1中记录了大量的信息,比如人员的培训的信息,包含姓名、时间、培训内容等,如图1所示
medish15
另外还有一张表,表2,包含的是单纯的名单信息,如图2
medish14
现在想统计表2中名单上的人在表1中的培训记录。通常在表内的数据量不大的时候,手工一条条核对数据也是可以的,但遇到数据表非常庞大的时候,手工核对就费时费力还容易出错了。这里给大家介绍一种在Excel中使用简单SQL语句的方法来实现对不同表格间数据的整合和筛选。

我这里的Excel版本是Excel 2010 beta,和Office2007类似。首先,也是最重要的一部是为这两个表命名,方法是选中表格后单击右键选择“定义名称”,如图3所示
medish

单击后,出现命名对话框,如图4

medish2

这里将表1和表2分别命名为Table1和Table2。

medish13
medish7

然后选择上方的“数据”选项卡,如图6所示
medish3
选择“自其他来源”下的“来自Microsoft Query ”选项,如图7所示
medish1

在弹出的对话框中选择Excel Files*那一项,并且把对话框下面的“使用“查询向导”创建/编辑查询”勾掉,如图8所示
medish4

然后点击“确定”,便出现“选择工作簿”的对话框,这里选择包含表1和表2的工作表Sample.xlsx
medish6

点击确定后之后弹出添加表的对话框,如图9所示

medish5

这里要将Table1和Table2都添加一遍,添加完成后,查询器应当是如图10所示的样子
medish16

此时,单击图10中输入SQL语句的按钮,弹出输入SQL语句的对话框,如图11所示
medish12

图11中的代码是这样的

SELECT Table1.姓名, Table1.时间, Table1.培训内容, Table2.姓名
FROM Table1,Table2
WHERE Table1.姓名 = Table2.姓名

复制代码

其基本含义就是将表1中和表2中姓名相符的记录从表1中筛选出来。SELECT语句是SQL语言中最基础也是最重要的语句之一,加上WHERE语句后的限制条件,可以实现大多数的数据查询和筛选工作,其语法也不困难,稍微学习一下就会了。输入完代码,单击确定,就可以看到筛选出来的数据表了,如图12所示
medish8

接下来的工作就是将筛选出来的数据表再返回至Excel工作表当中,选择菜单中的“文件”——“将数据返回Microsoft Excel”,如图13所示
medish9

然后单击你想讲返回数据导入的单元格,会弹出对话框,如图14所示
medish11

选择好位置和显示方式,便可以看到最后筛选的结果,如图15所示
medish10

至此,就在Excel中使用SQL语句完成了更加复杂的数据筛选工作,SQL语句在查询和筛选数据上更加灵活多变和准确,通过此方法,还可以使用SQL语句达成更多更精确的筛选结果,这里仅仅是抛砖引玉,希望能给大家的工作和学习带来帮助。

Excel工作表之SQL查询方法

来自 http://www.2cto.com/database/201212/180310.html

近期在单位上做业务数据分析,发现还是Excel用的直接,筛选、求和、分类等等也是不亦乐乎,但是发现一些函数的效率与SQL还是有着较大差距,甚至是天壤之别,故作文一篇,提供Excel中的SQL查询使用方式。
查询的工作表可以是当前工作簿中的,也可以是其他工作簿中的。例如,图1所示的“网站数据.xlsx”工作簿中,Sheet1表格存储的是网站访问信息统计,现在需要从Sheet1中获取浏览次数大于500的城市。
20121230012544319
图1 Sheet1中存储的访问数据
可以在当前工作簿的其他表格中运行SQL查询,也可以新建一个工作簿,在本示例中选择当前工作簿的Sheet2表格,然后单击“获取外部数据”模块的“现有连接”按钮,在打开的“现有连接”对话框中单击“浏览更多”按钮,在打开的“选取数据源”对话框中定位到存储源数据的Excel工作簿文件─网站数据.xlsx,如图2所示。
20121230012544929
图2 定位存储源数据的工作簿文件
单击“打开”按钮,打开如图3所示的“选择表格”对话框,勾选“数据首行包含列标题”复选框,选择Sheet1工作表。
单击“确定”按钮,将打开如图4所示的“导入数据”对话框,在“请选择该数据在工作簿中的显示方式”选项中选择“表”,“数据的放置位置”选择“现有工作表”并指定位置为A1单元格。
 20121230012545415
图3 “选择表格”对话框
20121230012545704
图4 “导入数据”对话框
单击“属性”按钮将打开如图5所示的“连接属性”对话框,在“命令类型”下拉列表中选择“SQL”,在命令文本中输入SQL查询语句“SELECT * FROM [Sheet1$] WHERE 浏览次数>500”,其中“Sheet1”即指定的Sheet1工作表,当在SQL中引用Excel工作表时,需要在名称后面加上“$”符并将其包含在方括号内,“*”表示取出工作表中的全部字段,WHERE子句用于指定筛选条件,即浏览次数大于500。
20121230012545459
图5 “连接属性”对话框
单击“确定”按钮返回到“导入数据”对话框,再次单击“确定”按钮即可看到查询结果,如图6所示。
 20121230012545193
图6 SQL查询结果

 

 

如何在EXCEL中使用SQL进行数据处理与分析

来自 http://blog.sina.com.cn/s/blog_49f78a4b0102ds7q.html

方法一:利用Microsoft Query使用SQL语句

134845j5fpdo5jcjqqjzlz

方法二:利用现有链接–导入数据–连接属性–文本命令方式使用SQL语句

134745ahh4cyxw8xj3xbc6

 

最后,来段vba的代码,有兴趣的同学可以研究下。

'********************************************
'**********  北极狐工作室出品  **************
'**********  QQ:14885553      **************
'*** 新浪微博  http://weibo.com/1053147023 **
'********************************************


Sub Opiona()
Dim f
'On Error Resume Next
Application.ScreenUpdating = False '关闭屏幕刷新
Application.DisplayAlerts = False '关闭提示
Dim SQL$, Cnn As Object
Set Cnn = CreateObject("Adodb.Connection")
t = Timer
s = "\*.xls"
Set SH1 = Sheets("Sheet1")
 SH1.Range("A2:G65536").ClearContents  '清场
n = 0: SQL = ""
Dim Str经销商 As String
Dim Str时间 As String
Dim Str As String
f = Dir(ThisWorkbook.Path & s) '生成查找EXCEL的目录
    Do While f > ""   '在目录中循环
          If f <> ThisWorkbook.Name Then   '如果不是打开的工作簿
                  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & f) '打开已经存在的EXCEL工件簿文件
                  Cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no';Data Source=" & ThisWorkbook.Path & "\" & f
                  
                  Str时间 = Mid(f, 3, Len(f) - 6)
                  SQL = ""
              For i = 0 To 8
                  Str经销商 = wb.Sheets("Sheet1").Cells(1, i * 4 + 2)
                  Str = ""
                  Str = Str & "F1 AS 名称,"
                  Str = Str & " '" & Str经销商 & "'" & " as 经销商,"
                  Str = Str & "F" & i * 4 + 2 & " AS 数量,"
                  Str = Str & "F" & i * 4 + 3 & " AS 实洋,"
                  Str = Str & "F" & i * 4 + 4 & " AS 码洋,"
                 ' Str = Str & "F" & i * 4 + 5 & " AS 折扣,"
                  Str = Str & " '" & Str时间 & "'" & " as 时间表 "
                  SQL = SQL & "select " & Str & "from [Sheet1$A3:AK2000] union all "
              Next i
              SQL = Mid(SQL, 1, Len(SQL) - 10)
              SQL = "select * from (" & SQL & ") where 名称<>'合计' and abs(数量)>0"
              r = SH1.Range("A65536").End(xlUp).Row + 1
              SH1.Range("A" & r).CopyFromRecordset Cnn.Execute(SQL)
              Cnn.Close
              wb.Close
          End If
          f = Dir
    Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "用时:" & Format(Timer - t, "#0.0000") & " 秒", , "北极狐提示!!"
End Sub

原创文章,作者:xsmile,如若转载,请注明出处:http://www.17bigdata.com/sql%e5%9c%a8excel%e4%b8%ad%e7%9a%84%e5%ba%94%e7%94%a8/

联系我们

在线咨询:点击这里给我发消息

邮件:23683716@qq.com

跳至工具栏