Office中国论坛/Access中国论坛

标题: 浅谈Excel查询 [打印本页]

作者: roych 时间: 2011-3-12 23:37
标题: 浅谈Excel查询
本帖最后由 roych 于 2011-3-13 15:04 编辑

   跟Access相比，Excel的查询功能尽管比较弱，但还是可以进行的，大体有以下几种方法：
   1、自动筛选或者高级筛选法。如果不需要常常更改条件的话，自动筛选或者高级筛选，不失为一种好办法。毕竟操作的步骤并不多，即便条件再多，一分钟内应该也可以完成。
   2、公式法：用Index+Small，再加上条件数组，是可以进行一定的查询的。格式一般是：{=Index(区域，small(if(条件，区域行数，），row(1:1)))}，详细见附件。
   3、导入数据法。要领在于导入之后再编辑SQL语句，以达到查询的目的。需要注意的是，数据源位置必须正确，详细看附件中的“说明”工作表。
   4、利用内置函数，写VBA进行查询。利用内置函数的Countif、CountA等函数，再加上For语句，逐个历遍，以达到查询的目的。
   5、利用ADO对象，编写数据集语句执行。

   好，方法说完了，有兴趣的可以看看它们之间的优缺点了：
   前面三个针对单次查询较为有效，可以省却编程的繁琐。

   方法1，如果需要对所筛选的数据进行求和，则需要先筛选出来，再求和（当然，你也可以用状态栏来显示求和结果）。
   方法2，当然也无法求和的了，借助单元格变量，明显比自动筛选方便。而且，你还可以多加几个条件，——把if里的语句改为：（Range1=条件1）*（Range2=条件2）……。而和第一种方法相比，其缺点在于数据较多时，对内存影响比较大。
   方法3，比起方法2，方法3更加灵活，可以随时变更SQL条件而达到多次查询的目的。当然，前提是，您必须知道SQL语句的基本语法。
   方法4，比起方法3,不用每次都更改SQL语句，而只须在单元格填上条件即可，自然方便多了。不足之处在于条件较多，循环语句历遍时，对内存要求比较高，也因此而变得不易于调试。例子来源于Polygala1978：
   http://www.office-cn.net/forum.php?mod=viewthread&tid=96395&page=1#pid586469，在此一并谢过。
   方法5，和方法4相比，同样可以达到相同的查询目的，而由于没有用到循环语句（或者循环语句比方法4的要少），从而更加优化了代码的执行效率。在执行查询前，应先调试SQL语句是否有误，还要注意字段类型等，详见附件的代码注释。
   希望大家不要觉得Roy过于罗嗦噢。不过，Excel查询又岂是三言两语可以说得很清楚的呢？如有不清楚之处，请大家随时反馈。

   更新后的附件，有了模糊查询的功能。相信大家更加喜欢吧。O(∩_∩)O~~
   在VBA语言里，Access和Excel的是不同的，前者为星号（*），后者为百分号（%），这很是困惑了我一阵子。在这里顺便提一下，以便新手少走一些弯路。

作者: 余方方 时间: 2011-3-13 09:16
学习了，谢谢

作者: aslxt 时间: 2011-3-13 10:50
本帖最后由 aslxt 于 2011-3-13 11:22 编辑

好好学习天天向上
请问（方法4）：
查询条件输入的那些单元格是否可以做成组合框的形式？
可否改成模糊查询？
谢谢

作者: polygala1978 时间: 2011-3-13 13:24
谢谢楼主大人

作者: polygala1978 时间: 2011-3-13 13:34
本帖最后由 polygala1978 于 2011-3-13 13:37 编辑

体验了下，发现模糊（字段不全情况下）查询不能用，希望能添加上。

作者: roych 时间: 2011-3-13 13:36
回复 aslxt 的帖子

改成有效性序列时，就相当于组合框了~~但是这个数据较多，可能做成二级下拉菜单要好些。

作者: roych 时间: 2011-3-13 13:41
回复 polygala1978 的帖子

没有啊，我输入两个条件还是可以查询的啊？
[attach]45103[/attach]
你的意思可能是想改成Like语句，用通配符来做是吧？那我抽空再改改了。O(∩_∩)O~

作者: polygala1978 时间: 2011-3-13 14:30
回复 roych 的帖子

主要是可投区和不可投区需要实现模糊查询，（主要是顾客信息不全，需要模糊搜索）例如：城关镇，输入城关就能查询到与城关相关的地区。不知道能否实现。再次感谢您的帮助。

作者: roych 时间: 2011-3-13 14:59
回复 polygala1978 的帖子

可以实现。详见我更新后的附件。

作者: pureshadow 时间: 2011-3-13 15:58
怎么单单漏了“查找”和“定位”两个功能

作者: roych 时间: 2011-3-13 17:55
本帖最后由 roych 于 2011-3-13 18:23 编辑

上次小妖姐姐在灌水帖里评了-30分，这次只好贴几个实例上来好混点评分，——请让小生自恋三秒钟，(*^__^*) 嘻嘻……）
没想到，小妖姐姐一上来就刁难Roy。。。

。
   小妖姐姐说的定位，是不是Move系列方法（MoveFirst、MoveLast等等）。不过由于记录集属于内存数据，没试过，不知道行不行。
   而查找功能，应该是记录集中的Find方法，大家可以试试。语法为：rst.Find (条件, 起始行, [搜索行方式],查找方式 )
例如，从第一行搜索Roy的数据可以这样写：rst.Find("姓名=Roy",1,,adSearchForward)，——第一行开始，肯定是向前搜索的了。
   不过，有这个必要么？

定位功能。Roy觉得查询出来之后，根据记录的条数，再用offset方法来定位岂非简单多了？
查找功能。直接用系统自带的查找功能也不算太烂吧？即便想用VBA，也可以用For语句+Find方法来查找数据啊。是不是？

狠狠地鄙视下小妖姐姐的无厘头难题~~~

作者: aslxt 时间: 2011-3-13 18:12

roych 发表于 2011-3-13 13:36
回复 aslxt 的帖子

改成有效性序列时，就相当于组合框了~~但是这个数据较多，可能做成二级下拉菜单要好些。 ...

简单改成有效性系列时，如何解决以下问题：
1、序列的唯一性
2、序列的排序
3、下拉高度（只有8行，找了很久都没有方法改变这个高度，也就是下拉后显示的行数）
有知道的请指教一下

作者: roych 时间: 2011-3-13 18:34

aslxt 发表于 2011-3-13 18:12
简单改成有效性系列时，如何解决以下问题：
1、序列的唯一性
2、序列的排序

问题1：分两种情况：
如果是固定的序列（如中国的省份等等），可以设置一个唯一的数列，定义好名称后在有效性里设置好就行了。
如果不是固定的序列，例如随时增加序列号的话，则需要写数组公式来列出不重复值，然后再动态定义该序列。有时间的话，我会写点这方面的实例。——个人觉得这倒不如用数据透视表做好之后，每次打开时刷新透视表，从而得到动态数据要好些。
问题2、顺序是必须排列好的。当然，你也可以在启用宏的情况下，每次打开时自动对序列进行排序（用Sort方法）。
问题3、Roy暂时没研究。

作者: pureshadow 时间: 2011-3-14 10:53
妖是V白！
查找和定位指的是CTRL+F和F5的两个功能，这对于V白们来说是个极好的查找手段，现实中让我去查找一堆东东，除非迫不得已坚决不用FIND，更不用那些让人头晕的数组函数。
顺便还有，筛选也是一种查找手段。
PS，让鄙视来得更猛烈些吧！！！！

作者: roych 时间: 2011-3-14 11:21
对于小妖姐姐，这一点我是赞同的。我对所有人都是这么说的，能用命令时决不用公式，能用公式时决不用数组，能用数组时决不用VBA。
不到万不得已，谁会那么有空去调试代码呢？而且，我写这个的初衷，也不是为了鼓励大家去学VBA，更不是为了炫耀我的菜鸟VBA水平，而是希望这个能作为一种工具，能够简化大家的工作。——当然，前提是你必须知道怎么改数据源（这要求应该不算太高吧？），否则也是“授人以鱼”。

作者: polygala1978 时间: 2011-3-14 11:22
回复 roych 的帖子

无比激动的感谢楼主，帮了大忙

作者: pureshadow 时间: 2011-3-14 17:10

roych 发表于 2011-3-14 11:21
对于小妖姐姐，这一点我是赞同的。我对所有人都是这么说的，能用命令时决不用公式，能用公式时决不用数组， ...

没有鄙视？盾牌白准备了

作者: q3747 时间: 2011-3-16 22:13
谢谢分享，学习学习

作者: sukii 时间: 2011-3-17 15:35
厉害啊

作者: huanglucy 时间: 2011-3-22 09:47
学习

作者: podzhit 时间: 2011-5-10 15:29
kankan zenme nong

作者: zsd5237 时间: 2011-5-23 22:26
学习

作者: zsd5237 时间: 2011-6-6 06:46
谢谢

作者: jinzhanxi 时间: 2011-8-17 08:27
好好学习一下，顶了

作者: yanwei82123300 时间: 2011-8-29 20:41
学习了，谢谢

作者: joyark 时间: 2011-9-23 01:05
怎么,好不容易详见我更新后的附件。

作者: excel7707 时间: 2011-9-23 11:44
学习学习

作者: williamwangc 时间: 2011-10-28 17:59
看看。应该不错的。

作者: 关向齐 时间: 2011-11-2 23:19
xuexi

作者: 18zhangyong 时间: 2011-11-14 12:08

作者: wanghaijuan@ 时间: 2011-11-25 20:22
果然如此。。。。泪奔

作者: charlieyoung 时间: 2011-11-26 00:07
rs3.Open "select * from T_individual_addressbook where T_individual_addressbook.PersonalName Like '%" & Cells(rowexcel, 2) & "%'", conn, 1, 3

作者: treefive 时间: 2011-12-22 17:35
再接再厉，楼主辛苦了。

作者: wsqhm 时间: 2012-3-8 23:29
太棒了，顶

作者: pml 时间: 2012-3-26 00:33
学习了，谢谢楼主

作者: 不想读书 时间: 2012-5-16 16:04
看看.........

作者: qqmei888 时间: 2012-5-17 08:23
xiexei !!!!

作者: yangzh800 时间: 2012-5-17 09:48
学习学习

作者: fnsmydyang 时间: 2012-5-24 09:16
学习一下下

作者: ycxchen 时间: 2012-5-24 09:25
看看，学习！

作者: efcndi 时间: 2012-6-1 08:19
look look

作者: sharon_lan 时间: 2012-8-29 17:09
好好研究学习下

作者: WANGDUYU 时间: 2013-1-15 21:50
感谢指点。

作者: 风啸啸 时间: 2013-1-19 16:56
看看，谢谢分享。

作者: hyh11111111 时间: 2013-4-29 02:31
学习

作者: jzbinbin5 时间: 2013-10-31 08:05
弟弟顶顶顶顶顶

作者: liumporite 时间: 2013-12-5 12:47
DDDDDDDDDDDDDDDDDD

作者: 董秋 时间: 2014-3-27 16:28
haohaohhao

作者: 刘青梅 时间: 2014-4-23 16:29
学习了，谢谢

作者: wwlsn 时间: 2014-5-17 11:23
学习了

作者: zjj8989 时间: 2014-7-16 08:17
dddddddd

作者: cfedf 时间: 2014-11-23 21:16
学习一下

作者: friendship 时间: 2014-12-12 22:42

作者: 山东小妮子 时间: 2015-2-1 21:36
学习

作者: jackzhao2015 时间: 2015-2-5 11:33
学习学习

作者: 764300778 时间: 2015-10-7 23:27
EEEE

作者: dazuping 时间: 2015-10-13 15:46
1111

作者: zhuangyongjin 时间: 2015-12-17 16:31
我想要看隐藏内容

作者: wgh3g 时间: 2015-12-20 13:12
谢谢楼主，学习！

作者: 天天福满多 时间: 2016-2-15 09:13
谢谢分享

作者: 天涯沦落20131 时间: 2016-3-21 09:37
11111111111

作者: ly 时间: 2016-5-21 21:17
感谢老师把excel查询方法讲得那么全面！

作者: aide 时间: 2016-5-23 09:35
谈Excel查询 [修

作者: Figueroa 时间: 2016-5-25 00:39
一定要好好学习，EXCEL查询用的地方最多了，感谢分享你的经验，支持，祝工作顺利！

作者: jingag 时间: 2016-8-16 12:37
准备下了看看，多写分享

作者: ardu95 时间: 2016-9-28 05:54
x学习一下学习一下

作者: nextto 时间: 2016-12-14 15:09

作者: lywaterfall 时间: 2017-1-16 07:31

学习了，谢谢

作者: lkkl66 时间: 2017-3-23 12:35
谢谢

作者: qcq127 时间: 2018-7-15 10:25
来学习新技能。

作者: niculas 时间: 2019-5-23 09:25
学习

作者: lihonj 时间: 2020-7-25 07:28
学习中，谢谢

作者: aqukhn81 时间: 2020-7-27 16:07
学习学习

作者: leonshi 时间: 2022-1-25 10:29
学习大师的作品

作者: mizi0931 时间: 2022-8-30 16:47
谢谢楼主大人

作者: wuwu200222 时间: 2022-10-18 15:56
学习

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)