[已解决]将三列数据动态提取到一列中

好运来5599

一个工作簿中包含《计件》和《明细》两个工作表。
1、《明细》表中A、B、C 三列全部根据《计件》表AR、AS、AT 三列自动生成。
2、举例说明：A、C两列是将《计件》AR列分离后提取的，B列是根据A、C两列生成。
3、现面临如下问题需解决：
    ⑴《计件》表AR列提取完毕之后，如何继续自动提取AS列？AT列？
    ⑵《计件》表AR、AS、AT 三列的行数为动态的，如何使《明细》表A、B、C 三列随之变动？
        例：《计件》表AR列记录到了第67行，则从第68行开始到AS列中提取，而随后，AR列又有新增的，如何使AR列新增的数据接着第67行（即从第68行起）继续记录？即自动把从AS列中提取的数据下移。

最佳答案

月排行榜 / 总排行榜

cabcyvr

2017-9-1 06:27

"明细" A4
=IFERROR(LEFT(
IFERROR(IFERROR(INDEX(OFFSET(计件!$AR$4,,,MAX(IF(计件!$AR$4:$AR$1000<>"",ROW(计件!$AR$4:$AR$1000)))-3,1),ROW(A1)),
INDEX(OFFSET(计件!$AS$4,,,MAX(IF(计件!$AS$4:$AS$1000<>"",ROW(计件!$AS$4:$AS$1000)))-3,1),ROW(A1)-(MAX(IF(计件!$AR$4:$AR$1000<>"",ROW(计件!$AR$4:$AR$1000)))-3) )),
INDEX(OFFSET(计件!$AT$4,,,MAX(IF(计件!$AT$4:$AT$1000<>"",ROW(计件!$AT$4:$AT$1000)))-3,1),ROW(A1)-(MAX(IF(计件!$AR$4:$AR$1000<>"",ROW(计件!$AR$4:$AR$1000)))-3)-(MAX(IF(计件!$AS$4:$AS$1000<>"",ROW(计件!$AS$4:$AS$1000)))-3)  ) ),5),"")      

数组下拉复制，　结果随“计件”中AR/AS/AT自动更新（行数临时设为不超过１０００行）

跳转到最佳答案楼层

cabcyvr

"明细" A4
=IFERROR(LEFT(
IFERROR(IFERROR(INDEX(OFFSET(计件!$AR$4,,,MAX(IF(计件!$AR$4:$AR$1000<>"",ROW(计件!$AR$4:$AR$1000)))-3,1),ROW(A1)),
INDEX(OFFSET(计件!$AS$4,,,MAX(IF(计件!$AS$4:$AS$1000<>"",ROW(计件!$AS$4:$AS$1000)))-3,1),ROW(A1)-(MAX(IF(计件!$AR$4:$AR$1000<>"",ROW(计件!$AR$4:$AR$1000)))-3) )),
INDEX(OFFSET(计件!$AT$4,,,MAX(IF(计件!$AT$4:$AT$1000<>"",ROW(计件!$AT$4:$AT$1000)))-3,1),ROW(A1)-(MAX(IF(计件!$AR$4:$AR$1000<>"",ROW(计件!$AR$4:$AR$1000)))-3)-(MAX(IF(计件!$AS$4:$AS$1000<>"",ROW(计件!$AS$4:$AS$1000)))-3)  ) ),5),"")      

数组下拉复制，　结果随“计件”中AR/AS/AT自动更新（行数临时设为不超过１０００行）

好运来5599

cabcyvr 发表于 2017-9-1 06:27
"明细" A4
=IFERROR(LEFT(
IFERROR(IFERROR(INDEX(OFFSET(计件!$AR$4,,,MAX(IF(计件!$AR$4:$AR$1000"",RO ...

非常感谢！经过测试验证，您的方法正确无误，完全实现了我预期的功能。{:1112:}
我昨晚把您的公式逐段分解地进行理解，可我在这方面的知识实在太浅显，一直未能理解透。可以麻烦您讲解一下吗？
1、ROW(计件!$AR$4:$AR$1000)在函数是什么意思？
2、《明细》中提取了《计件》AR列和AS列之后，又在《计件》AR列新增记录，则新增的会自动插入进来，请问这是什么原理？麻烦讲解一下。
3、《明细》表现在已经能实现自动、动态提取《计件》表中的三列数据，可是反应速度比较慢，是不是因为公式太多？并且大量用到数组？有无办法提升速度呢？

cabcyvr

1、ROW(计件!$AR$4:$AR$1000)在函数是什么意思？
因为你要首先知道AR，AS和AT列中最后一个非空单元格的行位置，下面的故事是为了获取AR列的最后一行非空单元格位置，起始行为第四行，下面的公式如果独立运行会得到６４，表示加上起始行AR４开始到下面最后一个非空单元格的范围有６４个，用范围表示就是AR４：AR６７
另外记得完整的看公式，不要抽出一节来看。
＝MAX(IF(计件!$AR$4:$AR$1000<>"",ROW(计件!$AR$4:$AR$1000)))-3　　　此公式得到的是一个唯一的数值６４.它是要用来作为OFFSET的弟个参数
意思可以理解成如果$AR$4:$AR$1000中哪一行不为空，就显示他们的行号，通过外围MAX就得到最后一个不为空的单元格位置。真正的位置是AR６７，因为是要用作OFFSET的参数做范围，所以要减去３.

OFFSET(计件!$AR$4,,,MAX(IF(计件!$AR$4:$AR$1000<>"",ROW(计件!$AR$4:$AR$1000)))-3,1)
上面的OFFSET得到的是一个范围，不能独立使用，是要用来当作前面INDEX的引用范围，表示AR４开始向下产生一个６４行１列的范围（即AR４：AR６７）


2、《明细》中提取了《计件》AR列和AS列之后，又在《计件》AR列新增记录，则新增的会自动插入进来，请问这是什么原理？麻烦讲解一下。

因为按照顺序查找，AR４：AR６７范围内只有６４个参数，正常情况当到达Row（６５）时就会出现错误信息。这个时候就要用IFERROR并用ROW（６５）减去AR列的有效单元格数量６４，重新回到１，同时将第二个INDEX用AS列的有效范围做参数，重新开始从AS列从1开始提取数据。
AS列有效范围为９，所以当ROW（Ａ７４）的时候也会出错，这是又用第二个IFERROR加INDEX重新引用AT列，同时用ＲＯＷ（Ａ７４）－６４－９将第三列的引用顺序重新归一。

3、《明细》表现在已经能实现自动、动态提取《计件》表中的三列数据，可是反应速度比较慢，是不是因为公式太多？并且大量用到数组？有无办法提升速度呢？
我这里的速度没问题（不到１秒完成计算）。如果想提高速度，可以用辅助列把AR／AS／AT列的有效范围单独算出，再简化Ａ列公式。

好运来5599

cabcyvr 发表于 2017-9-2 11:44
1、ROW(计件!$AR$4:$AR$1000)在函数是什么意思？
因为你要首先知道AR，AS和AT列中最后一个非空单元格的行 ...

非常感谢您这么详细的解说，结合您的解说，我反复琢磨很久，现在基本上能理解了。太谢谢您啦！

好运来5599

好运来5599 发表于 2017-9-3 16:33
非常感谢您这么详细的解说，结合您的解说，我反复琢磨很久，现在基本上能理解了。太谢谢您啦！

原来把B列想得太简单了，现在B列的提取又遇到问题，还得请教您啦！
我另外发了一个帖：http://www.excelpx.com/thread-433318-1-1.html

cabcyvr

因为已经有了Ａ列，其实Ｂ列就是很简单，用Ｂ列中的ROW（Ａ！）参数查询是否超出ＡＲ／ＡＳ／ＡＴ列的有效数据范围加上判断Ａ列是否为空直接来判断“职位”就可以，