[求助]请问这个公式怎样理解，关于非重复、非空白单元格的？

chx75

三菊花老师的讲解非常清楚，理解了。非常感谢，但是提取非空白的公式有疑问：

"要排除数据列中的空白数据，可以在定义X时外层加一个If()函数：
x=IF($A$2:$A$7"",IF(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1,ROW($A$2:$A$7)))"

好像提取的空白，重复的值。空白值本身就重复了！

如果要达到只提取非空白值，是否应改为:x=IF($A$2:$A$7<>"",ROW($A$2:$A$7)),但是公式试验有不对！请教了。

chx75

原来是多单元格的数组输入，刚刚看懂。

进阶学习，多列多行的做法。三菊花老师能否再指点一下。

非空白值的挑选，x定义为即成功

=IF($A$2:$A$7<>"",ROW($A$2:$A$7))

山菊花

这段时间跟供电部门有仇似的，常常整天停电。

进步真大，祝贺。

因论坛的原因，<>不能显示，让你走弯路了。

多行多列的做法，参照竞赛区版块的帖子，你现在能看懂它了。

chx75

以下是引用[I]山菊花[/I]在2006-3-17 18:42:00的发言：[BR]

这段时间跟供电部门有仇似的，常常整天停电。

进步真大，祝贺。

因论坛的原因，<>不能显示，让你走弯路了。

多行多列的做法，参照竞赛区版块的帖子，你现在能看懂它了。

plK0JMBS.rar (1.58 KB, 下载次数: 118)

2006-3-18 09:46 上传

点击文件名下载附件
[求助]请问这个公式怎样理解，关于非重复、非空白单元格的？

多行列的解法的步骤，应该是： 1、先将区域转换为1列； 2、再跟据单列的解法来提取非重值，或非空值。 但是第一关键环节怎样实现还望老师指教。竞赛区的帖子已经下载学习了，还是不懂，我觉得看三菊老师的讲解对整个解决思路非常清晰，不仅仅对公式的理解加深了，更能够帮助我等愚人进步！

lyr

XXAB1C0t.zip (2.74 KB, 下载次数: 153)

2006-3-18 14:52 上传

点击文件名下载附件
[求助]请问这个公式怎样理解，关于非重复、非空白单元格的？

看看这个释可否

山菊花

从多行多列中提取不重复数据，总的思路是，先把多行多列区域转换成一列多行的区域，然后用已经掌握的方法去提取新区域中的不重复值。这个转换的结果可以在工作表中建一个辅助列来表示，也可以将它保存在一个内存数组里。

作

为入门级练习，我们准备一个最简单的题目，B4:F23中没有空白单元格，每个单元格的数据都是文本，我们的任务是提取这个区域中的不重复文字到O列，从O4单元格开始，向下排列。

为了把公式写得简短些，我们先定义两个名称：
data=sheet1!$B$4:$F$23
Row=ROW(INDIRECT("1:" & COUNTA(sheet1!data)))

Row是一个数组，数组元素是一个序数集合，最小数是1，依次是2、3、4……，最大数是data的个数。这个公式一定要领会好，以后还有许多地方用得着它，公式计算结果如下：
Row=ROW(INDIRECT("1:" & COUNTA(sheet1!data)))

==> Row=ROW(INDIRECT("1:100"))

==> Row=ROW(1:100)

==> Row={1;2;3……98;99;100}

在以下的公式中，我们要对原数据区data中100个单元格进行一一判断，从1到100，要用到一个序数，这个序数就是Row。

准备工作做完了。第一步，就是把这100个文本，从J4单元格开始，排成一列，共有100行。点击“偏移量”单元格，看着AI:AN列：
AI列是序数，从1到100，AN列是排列结果，对应data中的100个文本。
我们设定一个规则，把data排成AN列，按先行后列的顺序：
1-5对应第一行，
6-10对应第二行，
11-15对应第三行……

AN列使用的核心公式是Offset()，第一个参数B4，是data左上角单元格，第二个参数称“行偏移量”，即是离B4单元格有多少行，第三个参数是“列偏移量”，同理，即目标单元格离B4单元格有多少列。

用 ROUNDUP(Row/COLUMNS(data),0)-1 可以返回“行偏移量”
用 =MOD(Row-1,COLUMNS(data)) 可以返回“列偏移量”

这个公式自己领会一下，说得太罗嗦了。
如果排列顺序为“先列后行”，偏移量公式见K列。

AN列使用的是普通公式。如果你愿意在工作表中增加这样一个辅助列，那么我们可以到此为止了，接下来的工作前面已经说过，依据AN列完全可以提取出它的不重复数据了。

如果不用辅助列，我们可以把这个结果保存到一个内存数组里，我们把这个数组命名为data_new，用定义名称的办法，把J列或K列的公式上粘贴到“引用位置”编辑栏里，注意，公式用了一个T()函数，只有这样，才能使结果为一个内存数组，到这一步，我也卡壳了，我试图弄明白这个道理，呵，水平有限，没辙了。暂不管它，总之，照着这样做就行了。

完成定义data_new这一步的工作之后，我们的脑子里就得有这么一个概念，有一个数组，在我们的工作表中看不到它，但它又与J列或K列一样存在，我们可以用data_new引用它，就像引用J4:J103一样。

如果一样，那么，定义X数组就可以这样了：
=IF(MATCH(data_new,data_new,0)=Row,Row)

最后一步，把结果写在工作表某单元格区域中，无论如何，这一步都不写了，留给你。

山菊花

楼上附件，忘了。 evbGKWe9.rar (12.04 KB, 下载次数: 410)

2006-3-18 15:57 上传

点击文件名下载附件
[求助]请问这个公式怎样理解，关于非重复、非空白单元格的？

从多行多列中提取不重复数据，方法不少。现给出一个可能是最简单的方案，先看看。竞赛区的那些帖子还得仔细地看，看那些帖子就是挖金子啊。

[此贴子已经被作者于2006-3-18 16:02:17编辑过]

chx75

谢谢，看了两遍才基本看懂，不过三菊花老师的讲解确实非常清晰，一步一步的说清楚了实现思路。说实话，我没有想到我能够这么快理解到这样高级的解法。但是还有几个地方是我基本功的问题还要进一步请教：

1、row＝ROW(INDIRECT("1:" & COUNTA(sheet1!data)))

为什么不直接用ROW(1: COUNTA(sheet1!data))

而要用indirect来转换实现。或者定义COUNTA(sheet1!data)为“个数”，用row(1:个数）来实现。

2、如果整个区域有空白，而不管有无空白格区域全部转换（即空白也参与排列）,是否应把COUNTA(sheet1!data)，变成rows（data)*COLUMNS(data)。

山菊花

1、首先，这种写法是不合法的，你试试。其次，有时，有特定条件下，我们预先知道COUNTA(sheet1!data)是一个固定值，比如50，完全可以写成：

row=Row(1:50)

但前辈的经验告诉我们，这样还是不如：

row=Row(indirect("1:50"))

你可以做个试验，定义好名称后，删除前面几行，你再查看一下名称：

=ROW(Sheet1!$1:$45)

使用了绝对引用都没用，它还是被改变了。而后一种定义方法却完好无损、固若金汤。这就是使用Indirect()的理由。

2、没什么说的，你真聪明。

chx75

1、定义为row＝row（1：50），删除前几行，定义没有变化。

若定义为row($a$1:$a$50),则定义会变化。

2、indirect,我感觉就是为了counta而用的，因为直接在row()中使用counta是不行的。

三菊花老师，我的理解对吗？