个人永久性免费-Excel催化剂功能第19波-Excel与Sqlserver零门槛交互-查询篇

minren118

Excel催化剂插件的系列功能介绍可跳转到此链接查看：
一文带你全面认识Excel催化剂（个人永久性全功能免费Excel插件）系列功能
http://club.excelhome.net/thread-1422426-1-1.html

对频繁使用Excel的高级应用的尝试用户来说，绕不过的一个问题Excel的性能问题，对于几万条数据还说得过去，上了10万行的数据量，随便一个函数公式的运算都是一个不小的负荷，有些上进一点的用户会往Access桌面数据库去进深，试图通过一些数据库的技术解决下数据性能问题。

但毕竟要学习SQL查询，学习许多数据库的技术，也把许多人挡在通往美妙的数据之旅上。而且另一问题是Access其实也是很鸡肋的一个东西，特别是它的SQL语法支持和多人共享查询上。

【Excel催化剂】拿出私藏很久的秘密武器，老规距，对个人用户一贯地任性永久性免费使用。让普通用户也能享受到驾驭大数据的畅快感。

文章出处说明

原文在简书上发表，再同步到Excel催化剂微信公众号或其他平台上，文章后续有修改和更新将在简书上操作， 其他平台不作同步修改更新，因此建议阅读其他出处的文章时，尽可能跳转回简书平台上查看。

视频演示

暂无，功能操作性较简单，如有强烈需求，后补视频或直播

使用场景

随着工作上的专业化分工越来越明细化，对一个业务导向的普通用户来说，能够掌握到足够多的Excel普通技能才是投资回报率最高的选择。

同样地在企业内的IT人员，如果能够有一种方式，能够让业务线上的普通用户更轻松地对自身的需求进行一些自助化地处理，不用没完没了地提一些低级的业务需求，这也是个美好的愿景。

今天，或许在【Excel催化剂】的桥梁搭建上，这样的愿景越发地明晰化和成为可能发生的事情。

IT人员在数据库上为用户编写视图，然后给用户开放有限度的权限的数据库帐号，仅有特定的视图/表的查询权限。普通用户可以使用最熟悉的数据处理工具-Excel，通过IT协助拿到需要的数据源，数据直接存放到Excel中，无需IT开发繁琐地数据条件查询，数据导出等功能。数据到了Excel后，只需简单根据自身的业务逻辑增加一些数据计算列，再搭配数据透视表和图表功能，立马对数据源进行多维度拖拉式无死角的分析。

技术疑问点Excel真的能直接访问数据库查询数据吗？

大部分的IT人员估计只知道导出Excel的功能，在他们的眼中，Excel就是个临时存储数据的一张大画布，用户没能力操作数据库拿到数据，只能把数据导出来让他们自己再加工。

其实Excel作为一款出色的数据处理软件，访问数据库是一个再普通不过的需求，只是一般的用户没法驾驭到这个层级的功能。Excel估计访问几乎一切存储数据的介质是没问题的，如xml/json/txt/csv/html/所有关系型数据库/hapdoop等等。

Excel2016版操作界面，其他版本类似

Excel用户不懂写SQL查询对数据库发出特定条件的查询怎么办？

当前一个问题不是问题后，剩下的用户的技能问题，确实是个头痛事，不可能对用户有过高的要求，懂编写SQL这个把大批的人挡在门外了，但如果只需用户会看懂简单的SQL如下：
SELECT 字段名 FROM 表名 WHERE 条件
会不会让这个困难能够看到一线曙光呢？理解别人写的和自己动手写出来是两个不同层次的要求。

Excel催化剂给大家带来的解决方案是：
用户只需要懂得简单的窗体按钮操作，加上对Excel基础的表格筛选操作（自动筛选功能，小白用户可掌握），在插件的帮助上，轻松自动生成所需的SQL语句向数据库发出查询

详细操作流程用户向IT数据库管理者请求必要性帮助

• 给用户开通帐号，并告之用户数据库的相关信息（服务器地址、数据库名、用户、密码）
• 对用户所需要的数据进行SQL编写视图查询最终交付用户视图对象名称
  因关系型数据库一般为了规范化范式要求，一个主题的数据分散到多个表内存储，底层表字段为英文名不友好，需要通过视图的方式，把同一主题的不同表进行反规范化关联起来，如订单数据，需关联订单主表+订单明细表+商品资料表+其他维度的资料扩展表（渠道、地区、促销活动等），最终形成一份普通用户可阅读的数据表。
• 给用户授权这个视图/表的访问权限
  

填写基础的数据信息

从IT数据库管理者拿到对应的基础信息进行填写。

填写好需查询的表/视图的基础信息

小技巧：使用复制行记录，可将选定行的信息复制到新行中，只需修改下表/视图名即可新增一条新的记录，一般数据库都较为固定，下次新增时只需填写好IT数据库管理者提供的表／视图名即可。

表／视图字段选择

选择要查询的表对应行，点击上图的【Sql查询】即可此表中的【查询初始记录数】所定义的记录到Excel表中（查询初始记录数是为了用户可以查询数据时，不会因表返回的数据量过大，Excel无法加载完成或加载速度慢而附加的，在后面的操作中再作介绍）。

不同的数据使用场景，可能对同一个表/视图的字段需求不一样，即同一份数据源其实可以多处分析场景使用到，一些不使用的字段可以不选，减少从数据库查询过程中的数据传输负荷

同时IT人员可以多作展望性思考，把用户可能会用到的字段都写到视图里，防止用户频繁地需求变化索取追加不同的字段。

选择字段窗体，一般没什么特殊全选也行

通过Excel表的自动筛选功能，把所需的数据筛选出来

因前面操作中，此时仅返回所有数据的前N条数据，数据量不全，如所有数据是2011年至今的数据，可能只显示了2011年1月份的不全的数据。
所以对下面的筛选操作，可能操作下来，不会有可见数据出现在当前筛选条件下，这个没关系的，此步骤的操作作用是：用Excel用户能够掌握的筛选数据的方法，最终经过程序的转换构造出真正可以向数据库发出筛选的SQL条件查询语句，实现无需会写SQL一样可查询数据库

minren118

Excel自动筛选一般因数据类型分为三大类：

数字类

一般会用到大于、小于、不等于等条件对数据进行区间查询，要写两个并列条件，请使用自定义筛选

数据类的筛选

示例：一定范围内的数值的筛选方式，也可单向筛选一个大于或小于

一定范围内的数值的筛选方式

文本类

可用开头、结尾、包含、不包含等方式来筛选文本，要写两个并列条件，请使用自定义筛选

文本类的筛选

示例：文本筛选某几项可用筛选下方的勾选的方式

文本多项筛选

示例：包含、开头是、结尾是M的等文本筛选方式

包含M字母的文本筛选

日期类

因日期格式本质是数字，同样可用于大于、小于、不等于等操作，同时因Excel对日期有智能的识别，有明天、今天、本月、上月等选择和筛选器下方自动把日期归类为年、月、日的结构，Excel催化剂未对这些智能部分进行转换实现，请同样地使用大于、小于、介于等常规的数字操作来筛选日期。

日期类的筛选

示例：筛选2007年11月的数据，注意前方的大于等于和小于的日期表示方式

日期筛选示例

通过Excel表的自动筛选功能，把所需的数据筛选出来

多个筛选条件下，数据为且的关系，可能出现无数据的情况

综合多列同时筛选，为且的关系

使用【SQL筛选查询】，把自动筛选的条件传递到SQL查询去

image.png

已经转换好的SQL语句

最终用户端所需要的数据已经在Excel催化剂的帮助下，顺利由普通用户在无SQL知识下操作成功，达到前面愿景式的场景需求。

一般Excel用户直接使用数据库的数据一大难点是数据库的表数据量很大，一般百万级别的数据量都是很常见，用户一般的分析仅需要的时间周期为一周、一个月等，一般对筛选的要求，只是做一个日期字段的筛选即可大范围地减少SQL查询后的数据记录数，对于其他不想查看的数据类别，大可采用在透视表里用筛选字段将其隐藏。

因数据库查询对索引维护要求较高，不见得筛选多个条件返回小部分的数据，比筛选日期字段，返回大部分的数据来得更快。

数据库的日期字段需要使用DateTime格式，使用Date格式将无法正确识别成日期格式

最后数据库返回了当前查询条件的数据结果集

Excel智能表返回的数据，和原生的Excel向数据库查询一致

因Excel催化剂仅作用于SQL语句的构造工作，最终的呈现是在【外部数据属性】下的【连接属性】的连接字符串和命令文本的修改，保留了原生Excel的智能表可通过刷新操作向数据库请求新内容的功能。
若日期的筛选条件只使用单方向的大于某一天的日期，后续若想得到新的数据，仅需刷新一下智能表，无需重复之前的所有操作

image.png

总结

让Excel用户可以轻松操作数据库，拿到所需的数据进行其他分析需求，这个过去很遥远的愿景，如今在Excel催化剂的作用下成为现实，整个操作过程全程无复杂的技术要求，仅使用Excel用户即手可得的现成的知识体系即可完成，同时对IT数据库管理者来说，想对Excel用户开发一些数据查询需求，也将变得如此简单，无需再频繁地开发前端界面的筛选操作、导出Excel等功能。仅仅作了IT该做的部分，其他的部分完全地交给用户自己完成，实现了IT搭建舞台，用户在其上愉快地唱戏的美好生活。