二维码 购物车
部落窝在线教育欢迎您!

用MS QUERYE做动态查询,不用函数!

 

作者:小窝来源:部落窝教育发布时间:2023-11-17 01:34:37点击:512

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

在低版本Excel中因为缺乏Filter函数,用公式做动态查询,尤其是多条件模糊查询要编写复杂的公式。今天分享用Microsoft Query做动态查询,只需简单几步操作即可。

 

Microsoft QueryExcel高低版本中都可以使用。为了方便低版本(小于2019)的伙伴,小窝用Excel 2016演示。

 

启动Excel 2016,打开素材。这是一份人员名单。

 

 

如果按部门进行查询,先筛选:

 

 

然后再选择可见单元格进行复制粘贴得到需要的数据:

 

 

有没有觉得笨拙?

 

下面感受一下Microsoft Query建立的动态查询。

精确查询:

 

 

模糊查询:

 

 

怎么样,很牛吧?!

 

下面就看看是怎么实现的。

1.通过MS Query建立数据连接

Step 01 首先通过数据验证建立用于筛选的下拉菜单。

 

 

step 02 单击“数据 自其他来源 来自Microsoft Query”,然后选择“Excel Files”作为数据源,确定后从弹出的“选择工作簿”对话框中选择要查询的素材文件。

 

 

Step 03 查询向导对话框中首先单击“选项”,把“系统表”勾选上。然后展开工作表,选择需要的列(后续还可以增删列)加入查询结果的列中。

 

 

注:如果你遇到警告“数据源中没有包含可见的表格”,不急,进入选项中勾选“系统表”即可。

 

Step 04 多次单击“下一步”,最后选择“在Microsoft Query中查看数据或编辑查询”单击“完成”。

 

 

现在进入了MS Query中。

 

 

2.添加精确查询条件

Step 01 单击“显示/隐藏条件”按钮将条件编辑栏显示出来。

 

 

Step 02 在第一列第一行中单击选择“部门”作为条件字段。在第二行中双击,弹出编辑条件对话框,运算符选择“等于”,指定值中输入参数名称“[bumen]”(不与条件字段相同的任何文字)。(也可以不双击,直接在值行中输入参数名称[bumen]并回车。)确定或回车后会弹出“输入参数值”对话框,任意输入一个部门名称即可。确定后可看到下方的数据已经完成了筛选。

 

说明:方括号必须,用于标记参数名称。

 

Step 03 单击“将数据返回到Excel”按钮回到Excel界面,弹出“导入数据”对话框。单击下方“属性”按钮,切换到“定义”选项卡。单击下方的“参数”按钮弹出“查询参数”设置框,选择“从下列单元格中获取数值”,拾取L2单元格(前面做的下拉菜单单元格),勾选“单元格值更改时自动刷新”选项。两次确定后回到“导入数据”对话框,拾取查询数据放置位置后单击“确定”。

 

 

Step 04 L2单元格选择不同部门即可看到查询结果。

 

 

我们可以随时编辑查询,修改条件。下方通过修改创建模糊查询。

 

3.编辑查询

Step 01 单击任意查询数据,右击鼠标,选中“表格 编辑查询”命令。会弹出“查询向导无法编辑此查询”的警告,直接确定。确定后会让输入参数值,可以随意输入一个部门,如“技术部”进行确定,也可以不输入而直接确定。

 

 

Step 02 在条件编辑栏上方单击选中当前的整个条件,然后按Delete键删除。

 

 

下面我们以姓名作为模糊筛选条件。

Step 03 在条件字段行中输入字段“姓名”;双击值行,运算符选择“包含”,指定值中输入参数名“[xingming]”。确定后值行显示查询语句“Like '% [xingming] %'”。将该语句修改成“Like '%' & [xingming] & '%'”,回车后弹出输入参数值对话框,随意输入一个姓氏,如“李”即可。(如果熟悉语句了,就不用双击值行而直接输入Like '%' & [xingming] & '%'即可

 

 

说明:MS Query采用SQL语句。like代表“包含”运算,可以实现模糊查找。%代表任意字符。'%'& [xingming] & '%'相当于Excel工作表中使用通配符的查找条件“*关键字*”。

 

Step 04 单击“将数据返回到Excel”按钮回到Excel中。删除原来的部门筛选下拉菜单,“部门”改“姓名”。在查询数据上右击,选择“表格 参数”修改查询参数的设置。完成动态模糊查询。

 

 

4.多条件动态查询


采用相同的方法可以增加筛选条件,实现多条件动态筛选。

1)添加并列条件

在条件栏的第2列中增加条件即可表达并列多个条件查询。

譬如再添加一个部门模糊查询,实现姓名、部门的双查询。

 

右键“编辑查询”回到MS Query中,新增一个包含条件即可。

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

ok,用Microsoft Query进行动态查询就介绍到这里。

 

再譬如修改查询为工龄范围查询。范围查询,并列两个条件,字段都是“工龄”,一个值是“>=[gongling1]”,另一个值是“<=[gongling2]”。如图:

 

表格
描述已自动生成

 

注:

1)也可以不用建立两列条件,就在条件1的值中写成“>=[gongling1] and <=[gongling2]”。

 

图形用户界面, 表格
描述已自动生成

 

2)值中支持比较运算符“>>=<><<=”,支持逻辑符号“andornot”

3)如果提示“标准表达式数据类型不匹配”,确定后不输入值即可。

 

2)添加或条件

或条件就在同列的或行中增加。

譬如修改查询为查找学历是某某或者某某。

 

图形用户界面, 表格
描述已自动生成

 

注:同样可以不用在或行输入,可以直接在第一个值中输入“[xueli1] or [xueli2]”。

 

OK,关于用 MS Query做动态查询就介绍这么多。

  

亲们,点赞、分享,走起!

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

多个工作表合并为一个表

第一次感受超级透视表

提取两份表格的异同,用PQ快得很

计算单元格中的文本算式

版权申明:

本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。