在Excel中使用SQL语句对多个数据表进行精确筛选
来自 http://343766868.blog.163.com/blog/static/48314056201131035011833/
在使用Excel的时候,常常会遇到这样的问题:有2个数据表,数据表1中记录了大量的信息,比如人员的培训的信息,包含姓名、时间、培训内容等,如图1所示
另外还有一张表,表2,包含的是单纯的名单信息,如图2
现在想统计表2中名单上的人在表1中的培训记录。通常在表内的数据量不大的时候,手工一条条核对数据也是可以的,但遇到数据表非常庞大的时候,手工核对就费时费力还容易出错了。这里给大家介绍一种在Excel中使用简单SQL语句的方法来实现对不同表格间数据的整合和筛选。
我这里的Excel版本是Excel 2010 beta,和Office2007类似。首先,也是最重要的一部是为这两个表命名,方法是选中表格后单击右键选择“定义名称”,如图3所示
单击后,出现命名对话框,如图4
这里将表1和表2分别命名为Table1和Table2。
然后选择上方的“数据”选项卡,如图6所示
选择“自其他来源”下的“来自Microsoft Query ”选项,如图7所示
在弹出的对话框中选择Excel Files*那一项,并且把对话框下面的“使用“查询向导”创建/编辑查询”勾掉,如图8所示
然后点击“确定”,便出现“选择工作簿”的对话框,这里选择包含表1和表2的工作表Sample.xlsx
点击确定后之后弹出添加表的对话框,如图9所示
这里要将Table1和Table2都添加一遍,添加完成后,查询器应当是如图10所示的样子
此时,单击图10中输入SQL语句的按钮,弹出输入SQL语句的对话框,如图11所示
图11中的代码是这样的
SELECT Table1.姓名, Table1.时间, Table1.培训内容, Table2.姓名
FROM Table1,Table2
WHERE Table1.姓名 = Table2.姓名
复制代码
其基本含义就是将表1中和表2中姓名相符的记录从表1中筛选出来。SELECT语句是SQL语言中最基础也是最重要的语句之一,加上WHERE语句后的限制条件,可以实现大多数的数据查询和筛选工作,其语法也不困难,稍微学习一下就会了。输入完代码,单击确定,就可以看到筛选出来的数据表了,如图12所示
接下来的工作就是将筛选出来的数据表再返回至Excel工作表当中,选择菜单中的“文件”——“将数据返回Microsoft Excel”,如图13所示
然后单击你想讲返回数据导入的单元格,会弹出对话框,如图14所示
选择好位置和显示方式,便可以看到最后筛选的结果,如图15所示
至此,就在Excel中使用SQL语句完成了更加复杂的数据筛选工作,SQL语句在查询和筛选数据上更加灵活多变和准确,通过此方法,还可以使用SQL语句达成更多更精确的筛选结果,这里仅仅是抛砖引玉,希望能给大家的工作和学习带来帮助。
Excel工作表之SQL查询方法
来自 http://www.2cto.com/database/201212/180310.html
-
近期在单位上做业务数据分析,发现还是Excel用的直接,筛选、求和、分类等等也是不亦乐乎,但是发现一些函数的效率与SQL还是有着较大差距,甚至是天壤之别,故作文一篇,提供Excel中的SQL查询使用方式。查询的工作表可以是当前工作簿中的,也可以是其他工作簿中的。例如,图1所示的“网站数据.xlsx”工作簿中,Sheet1表格存储的是网站访问信息统计,现在需要从Sheet1中获取浏览次数大于500的城市。图1 Sheet1中存储的访问数据可以在当前工作簿的其他表格中运行SQL查询,也可以新建一个工作簿,在本示例中选择当前工作簿的Sheet2表格,然后单击“获取外部数据”模块的“现有连接”按钮,在打开的“现有连接”对话框中单击“浏览更多”按钮,在打开的“选取数据源”对话框中定位到存储源数据的Excel工作簿文件─网站数据.xlsx,如图2所示。图2 定位存储源数据的工作簿文件单击“打开”按钮,打开如图3所示的“选择表格”对话框,勾选“数据首行包含列标题”复选框,选择Sheet1工作表。单击“确定”按钮,将打开如图4所示的“导入数据”对话框,在“请选择该数据在工作簿中的显示方式”选项中选择“表”,“数据的放置位置”选择“现有工作表”并指定位置为A1单元格。图3 “选择表格”对话框图4 “导入数据”对话框单击“属性”按钮将打开如图5所示的“连接属性”对话框,在“命令类型”下拉列表中选择“SQL”,在命令文本中输入SQL查询语句“SELECT * FROM [Sheet1$] WHERE 浏览次数>500”,其中“Sheet1”即指定的Sheet1工作表,当在SQL中引用Excel工作表时,需要在名称后面加上“$”符并将其包含在方括号内,“*”表示取出工作表中的全部字段,WHERE子句用于指定筛选条件,即浏览次数大于500。图5 “连接属性”对话框单击“确定”按钮返回到“导入数据”对话框,再次单击“确定”按钮即可看到查询结果,如图6所示。图6 SQL查询结果
如何在EXCEL中使用SQL进行数据处理与分析
来自 http://blog.sina.com.cn/s/blog_49f78a4b0102ds7q.html
方法一:利用Microsoft Query使用SQL语句
方法二:利用现有链接–导入数据–连接属性–文本命令方式使用SQL语句
最后,来段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
最后面两个是动图,如果看不了,请点击看原图。