ExcelHome技术论坛

标题: 一对多查询新思路 [打印本页]

作者: sbdk007 时间: 2019-2-1 20:21
标题: 一对多查询新思路
在论坛学习了不少，发现一对多查询，基本都是数组公式，于是萌生了不用数组公式的写法。

作者: sbdk007 时间: 2019-2-1 21:12
不考虑辅助列的情况下，个人觉得效率较高的是方法一、二、五、六、十

作者: 玻璃深灰白丶 时间: 2019-2-14 11:47
学习，知道lookup如何用，但是现阶段要自己熟练的使用还是觉得太难了。

作者: jivy1212 时间: 2019-2-14 11:56
=LOOKUP(ROW(A1)-1,COUNTIF(OFFSET($A$1,,,ROW($1:$10)),$C$2),$B$2:$B$5)&"" 其实核心没那么多种，就是外套不一样而已。

作者: sbdk007 时间: 2019-2-18 21:38

jivy1212 发表于 2019-2-14 11:56
=LOOKUP(ROW(A1)-1,COUNTIF(OFFSET($A$1,,,ROW($1:$10)),$C$2),$B$2:$B$5)&"" 其实核心没那么多种，就是外 ...

谢谢大佬关注。思路是只有几种，但网上多为数组公式，我只是想尝试用非数组公式的写法(当然本质上仍然是要进行数组运算)

作者: 绿谷龙芽 时间: 2019-9-19 21:34
=FILTER(B2:B5,A2:A5=C2)
来助个兴

作者: 轩檐 时间: 2019-9-20 09:16
同意jivy1212老师的说法，看了很多一对多的操作，拆解完之后，发现其实原理真的不多。
嘿嘿，补一条思路吧，textjoin+filterxml也可以一对多查询的~~

作者: 象山海鲜 时间: 2019-9-20 09:55
本帖最后由象山海鲜于 2019-9-20 10:08 编辑

用FILTERXML自带条件提取：

=FILTERXML("<a>"&CONCAT("")&"</a>","//@"&C2)

复制代码

=FILTERXML("<a></a>","//@"&C2)

复制代码

=FILTERXML("<a><"&TEXTJOIN({" b='","'/><"},1,A2:B7)&"'/></a>","//"&C2&"/@b")

复制代码

再加一个

=FILTERXML("<a>"&TEXTJOIN("",1,A2:B6)&"</a>","//b[.='"&C2&"']/following::*[1]")

复制代码

作者: micch 时间: 2019-9-20 10:02
海鲜版主这个数组用法好厉害，没用过新版本，不知道居然可以数组连接多个连接符。

作者: 轩檐 时间: 2019-9-20 10:04

象山海鲜发表于 2019-9-20 09:55
用FILTERXML自带条件提取：

难道海版换版本了吗？可以用高版本函数了吗？

作者: jivy1212 时间: 2019-9-20 10:07

象山海鲜发表于 2019-9-20 09:55
用FILTERXML自带条件提取：

FILTERXML这函数感觉会是我迈不去的槛了，云里雾里，一点头绪都没，没点编程知识理解这个麻烦的。

作者: 象山海鲜 时间: 2019-9-20 10:07

轩檐发表于 2019-9-20 10:04
难道海版换版本了吗？可以用高版本函数了吗？

没有高版本，看了帮助，有说明
依葫芦画瓢..........

作者: 象山海鲜 时间: 2019-9-20 10:12

jivy1212 发表于 2019-9-20 10:07
FILTERXML这函数感觉会是我迈不去的槛了，云里雾里，一点头绪都没，没点编程知识理解这个麻烦的。

和编程没有太大的关系，只要知道函数的用法就好。就是FILTERXML函数有自带函数，实际不难，就是多了有点麻烦。实际和excel函数的嵌套一样。

作者: 象山海鲜 时间: 2019-9-20 10:13

micch 发表于 2019-9-20 10:02
海鲜版主这个数组用法好厉害，没用过新版本，不知道居然可以数组连接多个连接符。

我也没用过，帮助里有说明的
https://support.office.com/zh-cn/article/textjoin-函数-357b449a-ec91-49d0-80c3-0e8fc845691c

作者: jivy1212 时间: 2019-9-20 10:18

象山海鲜发表于 2019-9-20 10:12
和编程没有太大的关系，只要知道函数的用法就好。就是FILTERXML函数有自带函数，实际不难，就是多了有点 ...

海鲜老师你确定么，比如这一段（//b[.='"&C2&"']/following::*[1]），我打都打不清（指要打好几次），正常函数全靠自动补全。上次没出完整版资料的，自己纯手打的，那几个后缀老半天，一周下来都没理解到啥。

作者: 象山海鲜 时间: 2019-9-20 10:33

jivy1212 发表于 2019-9-20 10:18
海鲜老师你确定么，比如这一段（//b[.='"&C2&"']/following::*[1]），我打都打不清（指要打好几次），正 ...

我对多于3个字母的函数也不会拼写，我就记着前面三个，然后就去找，再复制的
是不是很low，谁也没说记不住或英语不好就不能学函数是吧，我就用这种方式的。
对于FILTERXML 分为2块：xml和xpath
xml只要记住他的数据结构
XPATH 以找地址为主，就是上下文
FILTERXML返回的结果只有2种：属性值或文本内容
其他慢慢了解就好了

作者: liu2336904 时间: 2019-9-20 10:39
本帖最后由 liu2336904 于 2019-9-20 10:41 编辑

哈哈，看到这么多人不会全拼函数我就放心了，看来我不是另类啊

话说FILTERXML看了海鲜老师的入门简单，复杂的就要翻表了。。。

作者: 单身深蓝 时间: 2019-9-20 11:19
为何我解压后都变这样咯

作者: liu2336904 时间: 2019-9-20 11:22

单身深蓝发表于 2019-9-20 11:19
为何我解压后都变这样咯

13版函数。。。。

作者: 单身深蓝 时间: 2019-9-20 11:23

liu2336904 发表于 2019-9-20 11:22
13版函数。。。。

好吧。。。。。。。。。。。。。。

作者: 流浪铁匠 时间: 2019-9-20 12:58
2010的aggregate其实就可以凑出一个万金油的变种解法

(, 下载次数: 2)

作者: jivy1212 时间: 2019-9-20 13:04

流浪铁匠发表于 2019-9-20 12:58
2010的aggregate其实就可以凑出一个万金油的变种解法

aggregate代替small了吧，算同一条思路，量大的执行效率可以对比一下。（不过换个角度说，不用三键真是比较惬意的）

作者: 流浪铁匠 时间: 2019-9-20 13:14

jivy1212 发表于 2019-9-20 13:04
aggregate代替small了吧，算同一条思路，量大的执行效率可以对比一下。（不过换个角度说，不用三键真是比 ...

万金油为了效率考虑肯定使用区域数组公式
只进行一次数组运算
而不是每个单元格都要分别进行数组运算

aggregate代替的是small+if，因为可以指定参数忽略错误值

作者: 象山海鲜 时间: 2019-9-20 13:24

INDEX(B:B,MODE.MULT((A2:A5<>C2)*{1,2}%+ROW(2:5)))

复制代码

个数未知，容错

=IFERROR(INDEX(B:B,INDEX(MODE.MULT((A$2:A$5<>C$2)*{1,2}%+ROW($2:$5)),ROW(A1))),"")

复制代码

作者: 满坛皆为吾师 时间: 2019-9-20 13:44
补个TEXTJOIN的思路

=TRIM(MID(TEXTJOIN(REPT(" ",9),,IF(C$2=A$2:A$5,B$2:B$5,"")),ROW(A1)*9-8,9))

复制代码

作者: micch 时间: 2019-9-20 13:52
例如，=TEXTJOIN (" ",TRUE, "", "太阳", "将", "升", "起"、"明天") 将返回明天将升起太阳。

没法测试，这个函数这么聪明的吗？智能组织语言？？

另外：
=TEXTJOIN(", ", TRUE, A2:B8)

结果：

a1、b1、a2、b2、a4、b4、a5、b5、a6、b6、a7、b7

如果 ignore_empty = FALSE, 结果将为: a1、b1、a2、b2、a4、b4、a5、b5、a6、b6、a7 和 b7

二参还有这种用法，这啥意思？为什么多个和字？？

作者: 象山海鲜 时间: 2019-9-20 13:54
哈哈，这个贴差不多都汇总到一起了
可以作为一个专题的查询贴了

作者: solextrade 时间: 2019-9-20 14:01
怎能缺少海版的 FQ :

N2 =LOOKUP(,0/FREQUENCY(1,(A$2:A$5=C$2)*ISNA(MATCH(B$2:B$5,N$1:N1,))),B$2:B$5)&""

作者: liu2336904 时间: 2019-9-20 15:12

micch 发表于 2019-9-20 13:52
例如，=TEXTJOIN (" ",TRUE, "", "太阳", "将", "升", "起"、"明天") 将返回明天将升起太阳。

没法测试 ...

看英文版就知道，翻译问题
=TEXTJOIN(" ",TRUE, "The", "sun", "will", "come", "up", "tomorrow.") will return The sun will come up tomorrow

作者: 静看流年RSSQ 时间: 2019-9-20 15:34
套路太多了。玩不转啊。

要是不在乎B列的顺序的话，顺手也水个365的
=SORT(IF(A2:A5=C2,B2:B5,""),,-1)

考虑顺序就没啥好想法了、仅针对此题水个、
=MID(SORT(IF(A2:A5=C2,SEQUENCE(COUNTA(A:A)-1,1,9,-1)&B2:B5,""),,-1),2,99)

作者: sbdk007 时间: 2019-9-20 18:47
感谢各位老师指导，受宠若惊

作者: micch 时间: 2019-9-20 23:32

liu2336904 发表于 2019-9-20 15:12
看英文版就知道，翻译问题
=TEXTJOIN(" ",TRUE, "The", "sun", "will", "come", "up", "tomorrow.") wil ...

服了，原来如此，这么大个微软，居然连翻译都不请个好的

作者: bpw0258 时间: 2019-9-21 08:36
学习了，谢谢分享！

作者: wenxio 时间: 2019-10-8 09:00
膜拜一波，思路真棒!!

作者: ghexcel 时间: 2019-10-16 16:42
(, 下载次数: 10) 弱弱问下这是怎么肥事?度娘大概说是2010不支持此函数，那么，请各位老师，要在2010实现这种显示公式要用什么公式或函数？小菜一个，请多指教

作者: niko88819 时间: 2019-11-7 15:11
太精彩了，一一收藏学习。

作者: yaoxue15 时间: 2019-11-7 16:45
有个问题，麻烦帮澄清下，万金油公式： index+small+if+row 模式，为什么不加small 结果全都一样（错误值），公式分解的话，small的作用不是只对if数组重新排序的吗？想不通，能解释下为什么非要加small 才能得到正确结果？？谢谢！

作者: 浮生若梦~~~ 时间: 2019-11-7 20:00
=INDEX(B:B,SMALL(--TEXT(MMULT((A$2:A$5=C$2)*ROW($2:$5),1),"0;;99"),ROW(A1)))&""

作者: yeshuangyong 时间: 2019-11-19 11:52
这是一个非常好的天地，这里有你更精彩，谢谢！
看不明白

作者: cam333 时间: 2019-11-26 17:29

象山海鲜发表于 2019-9-20 09:55
用FILTERXML自带条件提取：

用你的公式，数据显示错误，是版本的级别不够吗？？

作者: 象山海鲜 时间: 2019-11-26 17:37

cam333 发表于 2019-11-26 17:29
用你的公式，数据显示错误，是版本的级别不够吗？？

是的，要最新版的才能用

作者: xxw1984 时间: 2019-11-27 10:38
本帖最后由 xxw1984 于 2019-11-27 10:45 编辑

多谢了。我看了附件，怎么显示的公式不是你贴图的公式呢？好好研究。。

欢迎光临 ExcelHome技术论坛 (https://club.excelhome.net/)