ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

新浪微博登陆

只需一步, 快速开始

    高级搜索
高效办公必会的Office实战技巧 魔方网表,Excel终结者,永久免费 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! 国内首部Excel函数公式学习大典 职场充电黑科技, Office微视频教程 免费下载Excel行业应用视频
买教程送最新正版Office 2016! Tableau-数据可视化工具 免费阅读《Excel函数其实很简单Ⅱ》 9门Excel免费公开课任你学
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 免费的Excel考勤计算系统
查看: 4|回复: 4

[求助] 请问这个数组公式错在哪里?

[复制链接]

TA的精华主题

TA的得分主题

发表于 2017-5-19 23:11 | 显示全部楼层 |阅读模式
请问各位高手,把A\B\C三列的内容,范围A2-C501,一起汇总到D列。

现在有数组公式单元格D2=INDIRECT(TEXT(SMALL(IF(A$2:B$501<>"",ROW($2:$501)*100+COLUMN(A:B),9999),ROW(1:1)),"R0C00"),)&""

填充后,只能汇总到100行就不汇总了,谁能告诉我怎么修改?
谢谢

111.zip

27.49 KB, 下载次数: 10

TA的精华主题

TA的得分主题

发表于 2017-5-20 08:09 | 显示全部楼层
本帖最后由 wangjguo44 于 2017-5-20 08:12 编辑

不清楚你想得到怎样的结果,但是你的公式=INDIRECT(TEXT(SMALL(IF(A$2:B$501<>"",ROW($2:$501)*100+COLUMN(A:B),9999),ROW(1:1)),"R0C00"),)&""只适合于统计区域在99行的范围里:试想,IF(A$2:B$501<>"",ROW($2:$501)*100+COLUMN(A:B),9999)得到的是{201,202;301,302;401,402;……,9602;9701,9702;9801,9802;9901,9902;10001,10002;10101,10102;……,20402;20501,20502;20601,20602;20701,9999;20801,9999;9999,9999;9999,……,9999;9999,9999}当拉到198行求第ROW(197:197)小,即求第197得到的不是5位数的那些 正确数,而是9999,第99行第99列数据,当然为空!
改成
  1. =INDIRECT(TEXT(SMALL(IF(A$2:B$501<>"",ROW($2:$501)*100+COLUMN(A:B),99999),ROW(1:1)),"R0C00"),)&""
复制代码


(如果统计范围不是2:501行,而是超过第1000行,用99999还不够,要用999999)

评分

参与人数 1鲜花 +1 收起 理由
johnj4321 + 1 感谢帮助

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-5-20 09:18 | 显示全部楼层
本帖最后由 johnj4321 于 2017-5-20 09:20 编辑
wangjguo44 发表于 2017-5-20 08:09
不清楚你想得到怎样的结果,但是你的公式=INDIRECT(TEXT(SMALL(IF(A$2:B$501"",ROW($2:$501)*100+COLUMN(A: ...

谢谢,换成了99999,解决了。
不过我还是不理解这个9999是怎么计算出来的。

TA的精华主题

TA的得分主题

发表于 2017-5-20 09:41 | 显示全部楼层
  1. =IFERROR(INDIRECT(TEXT(SMALL(IF(A$2:B$501<>"",ROW($2:$501)*10^3+COLUMN(A:B)),ROW(1:1)),"R0C000"),),"")
复制代码

TA的精华主题

TA的得分主题

发表于 2017-5-20 09:57 | 显示全部楼层
johnj4321 发表于 2017-5-20 09:18
谢谢,换成了99999,解决了。
不过我还是不理解这个9999是怎么计算出来的。

数据区在第99行以内列标在2位数之内(A:B只有一位数),那么符合条件的话,ROW($2:$99)*100+COLUMN(A:B)得到最大数是9902,不符合条件用个9999,SMALL(IF(A$2:B$501<>"",ROW($2:$,99)*100+COLUMN(A:B),9999),ROW(1:1)),取符合条件的不会取到9999,
但是当超过100行,如IF(A$2:B$100<>"",ROW($2:$,100)*100+COLUMN(A:B),9999),行号扩大100倍,第100行也是符合条件的话将得到10001和10002,而if第三参数value_if_false却只设到9999,岂不是取不到10001或10002了吗?——用了99999,在999行以内,符合条件的可能是99901或99902,就不会因这个IF的第三参数发生干扰符合条件的数据!
您需要登录后才可以回帖 登录 | 免费注册 新浪微博登陆

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,每天坐享新鲜教程

手机版|Archiver|关于我们|联系我们|ExcelHome   

GMT+8, 2017-5-28 16:44 , Processed in 0.153198 second(s), 20 queries , Gzip On, Memcache On.

Powered by Discuz! X3.3

© 2001-2017 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

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