Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 3187|回复: 11

[已解决]整理后重新开帖,有劳各位大神!关于查找引用公式优化改进,谢谢!

[复制链接]
发表于 2014-10-30 10:43 | 显示全部楼层 |阅读模式
本帖最后由 1681008 于 2014-10-30 19:37 编辑

      我想查找判断汇总工作表里的某个单元格的文本或数值是否存在于 数据工作表的 AA列至AY列中的某一列,如果存在,返回数据工作表中此列第二行的文本或数值
     PS:因论坛权限不够上传不了附件,excel测试文件我上传到网盘---pan.baidu.com/s/1kT9lgHL

如下所示:
(注:两个工作表Sheet1和Sheet2我重命名为 数据 和 汇总)


工作表1名称:数据
       AA       AB         AC      ...    AY
1   
2   BZH-1  28.05    YZH-1   ...  -2.8
3   10-1    158       D-108   ...  1-11
4   12-58   9-88      TD-1     ...  17-99
         .
         .
         .
工作表2名称:汇总
       A(输入)   B(公式,可下拉)
1   
2   17-99      -2.8(判断A2的文本或数值是否在工作表 数据AA至 AY列的其中一列,并返回所在这一列第2行的值)
3   D-108      YZH-1
4   12-58      BZH-1
5   TD-1       YZH-1
6    1-11      -2.8
7    9-88      28.05
8    158       28.05
9    10-1      BZH-1   
         .
         .
         .
PS:我现在是这么实现的
=IF(ISERROR(MATCH(A2,数据!$AA:$AA,0)),IF(ISERROR(MATCH(A2,数据!$AB:$AB,0)),IF(ISERROR(MATCH(A2,数据!$AC:$AC,0)),IF(ISERROR(MATCH(A2,数据!$AD:$AD,0)),IF(ISERROR(MATCH(A2,数据!$AE:$AE,0)),IF(ISERROR(MATCH(A2,数据!$AF:$AF,0)),IF(ISERROR(MATCH(A2,数据!$AG:$AG,0)),IF(ISERROR(MATCH(A2,数据!$AH:$AH,0)),IF(ISERROR(MATCH(A2,数据!$AI:$AI,0)),IF(ISERROR(MATCH(A2,数据!$AJ:$AJ,0)),IF(ISERROR(MATCH(A2,数据!$AK:$AK,0)),IF(ISERROR(MATCH(A2,数据!$AL:$AL,0)),IF(ISERROR(MATCH(A2,数据!$AM:$AM,0)),IF(ISERROR(MATCH(A2,数据!$AN:$AN,0)),IF(ISERROR(MATCH(A2,数据!$AO:$AO,0)),IF(ISERROR(MATCH(A2,数据!$AP:$AP,0)),IF(ISERROR(MATCH(A2,数据!$AQ:$AQ,0)),IF(ISERROR(MATCH(A2,数据!$AR:$AR,0)),IF(ISERROR(MATCH(A2,数据!$AS:$AS,0)),IF(ISERROR(MATCH(A2,数据!$AT:$AT,0)),IF(ISERROR(MATCH(A2,数据!$AU:$AU,0)),IF(ISERROR(MATCH(A2,数据!$AV:$AV,0)),IF(ISERROR(MATCH(A2,数据!$AW:$AW,0)),IF(ISERROR(MATCH(A2,数据!$AX:$AX,0)),IF(ISERROR(MATCH(A2,数据!$AY:$AY,0)),"",数据!$AY$2),数据!$AX$2),数据!$AW$2),数据!$AV$2),数据!$AU$2),数据!$AT$2),数据!$AS$2),数据!$AR$2),数据!$AQ$2),数据!$AP$2),数据!$AO$2),数据!$AN$2),数据!$AM$2),数据!$AL$2),数据!$AK$2),数据!$AJ$2),数据!$AI$2),数据!$AH$2),数据!$AG$2),数据!$AF$2),数据!$AE$2),数据!$AD$2),数据!$AC$2),数据!$AB$2),数据!$AA$2)

狂汗下。。
求助论坛大神、老师给优化优化改进改进!
另如别无它法,最好不用 数组公式,因数据量比较大一般都有1000+行以上,同时按下“Ctrl+Shift+Enter”不太好用


还望各大神、老师给指导指导!
有劳了,衷心感谢,谢谢!!

补充说明下:
工作表 汇总中要查询的文本或数值(是唯一的),并且这个文本或数值可能出现在 工作表  数据 AA至AY的任意一列之中(当然这个文本或数值在数据中AA至AY中也是唯一的、不重复的)

最佳答案
2014-10-30 16:25
{=IF(COUNTIF($AA$1:$AY$1000,F4)=0,$A$2,INDEX($AA$1:$AY$1000,2,MIN(IF($AA$1:$AY$1000=F4,COLUMN($AA$1:$AY$1000)))-26))}
  
修改了,原来把-26放错位置了
发表于 2014-10-30 10:53 | 显示全部楼层
源表设计一规范,一个VLOOKUP直接解决
回复

使用道具 举报

发表于 2014-10-30 10:58 | 显示全部楼层
回复

使用道具 举报

 楼主| 发表于 2014-10-30 11:13 | 显示全部楼层
蝶·舞 发表于 2014-10-30 10:53
源表设计一规范,一个VLOOKUP直接解决

谢谢!
补充说明下:
工作表 汇总中要查询的文本或数值(是唯一的)可能出现在 工作表  数据 AA至AY的任意一列之中,当然数据中AA至AY的所有出现的文本和值也是不重复的。
回复

使用道具 举报

 楼主| 发表于 2014-10-30 11:18 | 显示全部楼层
zyouong 发表于 2014-10-30 10:58
不是已经有答案了,重新发贴呀

对不起,可能是我表达能力有限,上一帖没说清楚,浪费大家宝贵时间了,请大神、老师们见谅!
重新发的这贴也望大神、老师们能在百忙中抽空给指导指导,有劳了!谢谢!!

回复

使用道具 举报

发表于 2014-10-30 14:00 | 显示全部楼层
本帖最后由 zyouong 于 2014-10-30 14:03 编辑

{=IF(COUNTIF($AA$1:$AY$1000,F4)=0,$A$2,INDEX($AA$1:$AY$1000,2,MIN(IF($AA$1:$AY$1000=F4,COLUMN($AA$1:$AY$1000))-26)))}
根据需要改一下区间
回复

使用道具 举报

 楼主| 发表于 2014-10-30 15:14 | 显示全部楼层
zyouong 发表于 2014-10-30 14:00
{=IF(COUNTIF($AA$1:$AY$1000,F4)=0,$A$2,INDEX($AA$1:$AY$1000,2,MIN(IF($AA$1:$AY$1000=F4,COLUMN($AA$1: ...

试着按区域修改后按CTRL+SHIFT+ENTER键显示#VALUE!错误,

试着将公式里的函数分开用,里面的查找判断指定区域有无查询的值,这段没问题;
返回查找的值 所在工作表中哪一列,并返回此列中第2行的值,这段没弄明白

有劳大神看看实际的excel测试文件TEST(1).xlsx 11kB,能否修改下上传到此贴的附件中
权限不够,我上传到网盘---pan.baidu.com/s/1kT9lgHL
有劳了!
万分感谢!!
回复

使用道具 举报

发表于 2014-10-30 16:25 | 显示全部楼层    本楼为最佳答案   
{=IF(COUNTIF($AA$1:$AY$1000,F4)=0,$A$2,INDEX($AA$1:$AY$1000,2,MIN(IF($AA$1:$AY$1000=F4,COLUMN($AA$1:$AY$1000)))-26))}
  
修改了,原来把-26放错位置了
回复

使用道具 举报

 楼主| 发表于 2014-10-30 16:38 | 显示全部楼层
zyouong 发表于 2014-10-30 16:25
{=IF(COUNTIF($AA$1:$AY$1000,F4)=0,$A$2,INDEX($AA$1:$AY$1000,2,MIN(IF($AA$1:$AY$1000=F4,COLUMN($AA$1: ...

刚才试了下,可以用,太感谢了!
另外请教下这个用数组公式,录入的数据量大了,EXCEL在输入数据时会不会卡顿啊?
比如EXCEL右下角出现  正在计算:(4个处理器)n%

回复

使用道具 举报

发表于 2014-10-30 16:46 | 显示全部楼层
数据量大左下角都会显示计算进度,一般几秒钟都正常,如果时间较长,说明不行。你测试一下。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 23:53 , Processed in 0.784970 second(s), 8 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表