分享

Excel VBA ADO SQL入门教程001:认识SQL In Excel

 H0ing 2018-03-28


我知道我一直有双隐形的翅膀, 带我飞给我希望……

1.

本文所有观点及论述均是基于Excel平台,更准确的说是MS Excel……如无特殊情况,文中将不再特殊说明。


2.

以星光俺行走江湖多年的经验来看,学习者可以分为两类,一类是被动的学习者,完全或者少有清醒的自我思考意识,书上写什么,我就看什么,老师讲什么,我就听什么,啊,世界如此单纯我亦无忧无虑不要长大不要……。还有一类是主动的学习者,简而言之,TA知道自己学的是什么,为什么而学,怎么样才能学的透彻……

譬如说罢,咱们今天和以后分享的SQL、ADO等,后者就会问,这俩货是什么?为什么要学呀?学了有什么用啊?难不难学——和谈恋爱比起来?


3.

那就先说SQL吧。

SQL是一种结构化查询语言(Structured Query Language),是一种声明式语言,敲黑板划重点【结构化和声明式】。SQL的核心是对表的引用,声明你想从数据源中获取什么样的结果,而不用告诉计算机如何才能够得到结果——

后面这句话似乎很难理解,举例来说,倘若我们需要获取上图所示表格(Sheet1)成绩大于等于80分的人员名单,如果用命令式程序语言,比如VBA,是这样的:

Sub MyFind()

    Dim arr, brr, i&, k&

    arr = Sheet1.[a1].CurrentRegion

    ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))

    For i = 1 To UBound(arr)

        If arr(i, 2) >= 80 Then

            k = k 1

            brr(k, 1) = arr(i, 1)

            brr(k, 2) = arr(i, 2)

        End If

    Next

    [d:f].ClearContents

    [d1].Resize(k, 2) = brr

End Sub

你需要通过VBA编程告诉计算机每一步怎么走,数据从哪里来,从哪里开始遍历,行列是多少,符合条件的数据装入哪里,怎么装等等……

而如果用声明式SQL语言呢?只要告诉计算机我要什么就可以了。

SELECT 姓名,成绩 FROM [Sheet1$] WHERE 成绩>=80

我要Sheet1表(FROM [Sheet1$])……成绩大于等于80(WHERE 成绩>=80)……姓名和成绩的数据(SELECT 姓名,成绩)。

只要结果,不问过程。

就酱紫的声明式霸道总裁范。


4.

为什么要学习SQL In Excel(Excel支持的SQL语言)呢?

换言之,相比于Excel其它功能,例如函数、VBA、POWER PIVOT等,SQL有何优势?

首先,必须严肃脸说明的是,对于普通Excel使用者而言,VBA、SQL以及以后提及的ADO并不是非学不可的,非学不可的是基础操作、函数、透视表、图表……

然而大数据时代,对于另外相当一部分表族而言,Excel用久了,慢慢的,会意识到一个大问题;曾经在你心中无比强大的Excel函数,原来只适合小数据的腾挪躲闪;当数据量稍大后,函数这货就像未嗑士力架的姚明——不来劲的很哩。

SQL In Excel则可以解决函数处理大数据效率低下的问题,嗯~使用SQL语言,你甚至可以将Excel作为前台数据管理界面,数据库(例如ACCESS)作为后台数据储存仓库,进而储存、分析、管理远超Excel体积的数据量。


打个响指,我们之前讲过,VBA处理数据的核心是数组 字典,倘若SQL和它比较起来有何优劣?

作为一个正努力成为乐观主义者的人,星光还是先说优点吧。

通过上面代码的栗子我们很直观的看到,SQL的书写要比VBA编程简洁的多,甚至比小巧灵的函数还要简洁;此外,SQL高效处理的数据量上限,也是远远大于VBA数组 字典的;字典装上50W的数据,一般电脑的计算效率就开始垂直下降了,而SQL 还是风轻云淡脸;最后,SQL ADO VBA可以通过Excel直接处理数据库(例如ACCESS)来源的数据……。

然后说劣势。

SQL作为一种数据库结构化查询语言,对表的结构和数据的类型有着严格的要求,而严格来说Excel并非数据库,尽管它支持ADO和SQL(谁说装了数据就是数据库的?拉出去自弹小丁丁500下,好冷)。Excel对表的结构和数据的类型并没有严格的限定,例如合并单元格,多行表头,空记录,一列之内存在多种数据类型等等劣迹存在,因此,字典 数组处理EXCEL数据的灵活性要远远高于SQL,毕竟数组遍历在手,天下我有,什么合并单元格多行表头,统统都是浮云……


最后,SQL In Excel 和Power BI For Excel(以下简称Power BI)相比优势在哪里?

从Excel的角度讲,SQL和Power BI最大的优势是,SQL支持VBA语言。通过ADO执行SQL语言,VBA可以获取、分析、管理多种来源的数据,甚至进而对获取的数据再搭配字典、数组以及各种Excel自带的功能作进一步自动化、智能化处理……换句话说,VBA运行SQL语句后,可以再整合Excel所有的功能进一步处理数据,除了Power BI——是的,Power BI不支持VBA,耸肩,无奈。

从数据的角度讲,Power BI是一款数据分析的软件,包含了M和DAX查询语言,SQL则是一种数据管理的语言。查询和管理有何不同?简而言之,SQL不但可以查询数据,还可以操纵数据,例如增、改、删等等。而M和DAX语言对数据则只能查询,不能操纵。就像我们在Power BI入门教程中讲的,它只能改变自己,永远无法改变对方(指的是数据源,不是我们的爱情)

另外,SQL是一门广被接受和支持的语言。Excel,ACCESS,R,Python,JAVA,C等等软件和语言,均是支持SQL的;而POWER BI显然没有这样的待遇。

我们很久以说,作为一名数据分析员有三个必须掌握的技能,SQL获得数据,EXCEL分析数据,PPT展现数据。POWER BI出现后,有人说学了POEWR BI,就不用学SQL了。如果你能意识到两者之间的不同,显然就会明白这是低头说话不看前路哦。

当然,如果你是一名数据分析员,POWER BI最好也是要学的。原因很简单,它很简单。


5.

说了这么多,那么,如何在Excel中使用SQL?

一般有三种方法。

一种是MS Query法,不常用,省略。

一种是OLE DB法,具体过程是,单击Excel【数据】选项卡下的【现有链接】,在弹出的【现有链接】对话框中,单击【浏览更多】,选取目标文件后,依次【确定】,得到下面的【导入数据】对话框。

这种方法通常搭配数据透视表(上图显示方式选择【数据透视表(P)】),也可以搭配Power Pivot(高级版本Excel勾选上图的【将此数据添加到数据模型】)。

单击【属性】按钮后,得到【链接属性】对话框,再单击【定义(D)】选项卡,即可在【命令文本】编辑框中输入SQL语句,并【确定】执行。

关于上图【连接字符串】中的关键字和关联值,我们会在以后的AOD部分详加说明,此处先过。

最后一种是VBA ADO法,也是我们后文中常使用的方法。

相比于第2种方法,VBA ADO法的优点……

首先是自动化,它可以使用VBA代码绑定ADO,设定链接字符串,执行SQL语句,进而一键获取分析数据。其次,VBA编程可以使用变量编辑SQL语句,这远比第2种方法手动输入SQL语句要灵活智能的多,另外,VBA ADO法不但可以SELECT(查询)数据,还可以INSERT(增)DELETE(删)UPDATE(改)数据库的数据等。


6.

……握握爪,今天我们就先聊到这里吧,下期我们简单聊下ADO,然后聊SQL语言中最常用的SELECT语句……

嗯,忘记回答一个很重要的问题。

SQL难吗?

入门很容易,精通很难。

对于EXCELer,并没有精通的必要性,搭配ADO以及VBA自身的功能,例如数组和字典,入门SQL已经足够了。

你要对……我有信心(忐忑脸)。

安,爱你们,下期见。



一码不扫,
可以扫天下?

ExcelHome

VBA编程学习与实践




    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多