美文网首页
Excel中一个被严重忽视的大杀器功能

Excel中一个被严重忽视的大杀器功能

作者: Solo_db5d | 来源:发表于2019-12-29 10:36 被阅读0次

 一说到Excel工作簿内容合并、多工作簿查询、外部数据源的查询引用的实现,那么很多人第一想法就是使用公式、VBA、Power query。没错,这几个确实可以。我本人是一个Power query重度依赖者,经常使用PP来做数据查询。可后来慢慢的发现,Power query问题真的很多,比如说占用内存太大、运行非常缓慢。另外,一些低版本的Excel不支持PP,同时WPS也是不支持PP的。这个让我非常头疼,于是不得不找其它办法。于是在不经意间发觉了以下这个一直被忽视的东西:Microsoft Query。于是,我就使用这玩意试着分别连接SQL Server做了一个动态财务的科目汇总表查询,和连接Access做了一个动态查询。效果如下:

科目汇总查询表 工程款支付查询表

 以上,两个测试完全实现了使用参数的动态查询。以往,实现这种效果,要么使用VBA、要么使用power query。以下,我们来看一下怎么实现它呢。
1、打开菜单栏,找到Microsoft Query的

image

2、进入数据源配置界面

image

 这里已经存在了常用的Excel、Access这两种数据源连接方式。但其他的,我们需要单独建立。在此以SQL Serve为例。

image image

刚才建立的数据源就出现在了列表里面

image

接下来我们来看如何实施查询:
首先,选择一下数据库,然后添加一下需要用到的表

image image

 以上这个界面,作为使用过Foxpro的人应该不陌生,果然是上古的杰作。我们看到,从数据添加的表,自带了表间关系。这个非常省心,如果是Excel查询,那么就得自己建立表间关系了。
 接下来,我们把需要用到的的字段全部添加到列表里面,因为有表间关系,会自动做数据参照。查询器里面的好多细节操作,在此就不过多描述了。

现在我们来看一下,查询器自己生成的 SQL语句

image

 在使用过程中,因为需要做动态查询,因此加入了查询参数,然后这部分参数又被绑定到了工作表的单元格上。只要在这部分单元格就可以作为查询条件来使用,只要这部分单元格数值有变动,那么查询就会同步更新,根本不需要刷新或者添加按钮来执行查询。具体如下:

image

现在,让我们来看看,做出同样效果的东西,VBA和Power Query的工作量:
首先,来看VBA的工作量:
1、单独设计的查询界面

image

2、部分VBA代码

image

  至于power query稍微好一些,在引入数据的时候一句SQL语句就能解决,但是这货对于使用参数做动态查询实在太麻烦,需要合并好几几次查询才行。
 最后,对比Microsoft Query、VBA、Power query。可以说Power Query应该是Microsoft Query 的升级版,但是PQ使用的是M语言,在使用便利性和性能上不占优势,随便一个查询动辄就是几个G的内存占用,分分钟就死机。使用过多以后,感觉目前的PQ,还真只是一个玩具,它还有很大的改进空间。而VBA,这个看似无所不能,但正是无所不能才更加不那么好控制。至于Microsoft Query而言,并非没有缺点,最大的缺点我认为是多表连接查询时候没法使用左连接、右连接以及外部连接,只能使用内部或者说等值连接。所以,对于日常的合并工作表,还是建议使用MQ。

相关文章

  • Excel中一个被严重忽视的大杀器功能

     一说到Excel工作簿内容合并、多工作簿查询、外部数据源的查询引用的实现,那么很多人第一想法就是使用公式、VBA...

  • excel的系统学习第十三天打卡

    今天学习excel填充之字符数字填充(编号批量操作),填充是excel中一个较方便的功能 在数字填充里,选择其中一...

  • EXCEL服务器IIS配置

    前言 为了保证EXCEL服务器网页功能,APP和微信功能正常安装使用,在安装EXCEL服务器端之前,要先正确配置服...

  • 3 分钟入门 Excel 被低估的数据工具——分列

    在数据处理方面,分列功能是 Excel 被严重低估的功能之一,相当一部分人几乎没有用过该功能。根据要处理的数据,合...

  • 被严重忽视了的作业

    低下、重复、无序的作业导致学生负担重、压力大,让学生疲于应付,养成抄袭作业的恶习,甚至使学生丧失学习的兴趣,以至于...

  • ArcGIS Explorer入门-1

    ArcGIS Explorer是一款被广大GISer严重忽视的轻量级软件,操作简单,一些功能极为好用,再结合Esr...

  • 用Excel写日记才是大杀器

    一、为什么要用Excel写日记? 想解决这个问题,首先要说明一下纸质日记和手机软件的不方便之处。 纸质日记:本子大...

  • Axure那些不得不知的知识

    Axure是头大象,其实有很多不容易被大家注意到的功能。Axure以高度的自由度著称,用好了绝对是大杀器。本篇讲讲...

  • 大杀器

    有不少村民反应,没理清混沌村人物关系, 下面简单做个主要人物介绍: 黎三,著名剩男,混沌村小学老师,画漫画的。 凸...

  • 大杀器

    上天赐予了我们一副完备的,够我们搅翻世界的肉体和灵魂,但在成长过程中因为这样那样的原因一个个自我被锁住,在自我觉醒...

网友评论

      本文标题:Excel中一个被严重忽视的大杀器功能

      本文链接:https://www.haomeiwen.com/subject/krkyoctx.html