[第14期] 不要有空格的行和列

qee用

czzqb

昨晚上传了一个，由于网络屡屡发生问题，也不知传上了没有。

今天又想到了两个解法，连同昨天的答案，一共三个解法，一起作到这个附件里。如果昨天的已经上传，请以这个为准：

多种解法，拓宽思路，好。

[此贴子已经被山菊花于2006-8-24 12:40:51编辑过]

hbhfgh4310

交卷！为什么我能看到自己的帖子啊？以往不是看不见吗。

[em06][em06][em06][em06][em06][em06]

我已删除答案！

答案已发至dsjb@21cn.com，请查收！

QUOTE:

思路与结果都正确。

不是区域数组公式，不合答题要求。

[此贴子已经被山菊花于2006-8-24 12:08:58编辑过]

长安布衣

我没有用数组,只是加了一行和一列辅助列,为了简化公式,定义了一个名称

 

QUOTE:

本公式可作为数组公式。

给1分鼓励。

[此贴子已经被山菊花于2006-8-24 12:13:10编辑过]

BIN_YANG168

测试一下答题是否可见?

[此贴子已经被作者于2006-8-17 22:07:15编辑过]

jssy

我的答案：

QUOTE:

答案不正确，少了两行。

[此贴子已经被山菊花于2006-8-24 13:17:35编辑过]

山菊花

本题的解题思路可分为三个层次(以当前给定数据为例)。

一、建立两个数组X1、Y1：

X1={1,2,FALSE,4,FALSE,FALSE}

Y1={1;2;3;4;5;FALSE;7;8;9;10;11;FALSE;FALSE;FALSE;15;16;17;18;FALSE;FALSE;21;22;23;24}

分别与数据Data中无空格的行列对应，数值代表行号和列号。

在朋友们的答案中，建立数组X1、Y1的方法，主要有：

1、MMULT()函数法：

X1=IF(MMULT(COLUMN(INDIRECT("rc1:rc"&ROWS(data),))^0,0+IF(data="",COLUMN(data)))=0,COLUMN(INDIRECT("rc1:rc"&COLUMNS(data),)))

Y1=IF(MMULT(0+IF(data="",COLUMN(data)),ROW(INDIRECT("1:"&COLUMNS(data)))^0)=0,ROW(INDIRECT("1:"&ROWS(data))))

公式可简化为：

QUOTE:

X1=IF(MMULT(TRANSPOSE(ROW(data))^0,0+IF(data="",COLUMN(data)))=0,COLUMN(data)-2)

Y1=IF(MMULT(0+IF(data="",COLUMN(data)),TRANSPOSE(COLUMN(data))^0)=0,ROW(data)-2)

2、MMULT()函数法：

X1=IF(TRANSPOSE(MMULT(IF(TRANSPOSE(data)<>"",0,1),ROW(data)^0))=0,COLUMN(data)-2)

Y1=IF(MMULT(IF(data<>"",0,1),ROW(题目!$C$3:$C$8)^0)=0,ROW(data)-2)

3、SUBTOTAL()函数法：

X1=IF(SUBTOTAL(2,OFFSET(data,,COLUMN(data)-3,,1))=ROWS(data),COLUMN(data)-2)

Y1=IF(SUBTOTAL(2,OFFSET(data,ROW(data)-3,,1))=COLUMNS(data),ROW(data)-2)

4、MATCH()函数法：

X1=IF(ISNA(MATCH(COLUMN(data),SMALL(IF(data="",COLUMN(data)),COLUMN(INDIRECT("1:"&COUNTIF(data,"")))),0)),COLUMN(data)-2)

Y1=IF(ISNA(MATCH(ROW(data),SMALL(IF(data="",ROW(data)),ROW(INDIRECT("1:"&COUNTIF(data,"")))),0)),ROW(data)-2)

5、FREQUENCY()函数法：

X1=IF(TRANSPOSE(FREQUENCY(IF(data<>"",COLUMN(data)),COLUMN(data))=ROWS(data)),COLUMN(data)-2)

Y1=IF(FREQUENCY(IF(data<>"",ROW(data)),ROW(data))=COLUMNS(data),ROW(data)-2)

二、根据X1、Y1，把行号和列号按从小到大顺序排列，生成数组X、Y：

X={1,2,4,#NUM!,#NUM!,#NUM!}

Y={1;2;3;4;5;7;8;9;10;11;15;16;17;18;21;22;23;24;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}

方法大同小异：

X=SMALL(X1,COLUMN(data)-2)

Y=SMALL(Y,ROW(data)-2)

三、在目标区域用公式Index(Data,Y,X)返回原始数据表中相应单元格的值。

为了不显示错误值，一般可用ISERROR()函数处理：

=IF(ISERROR(INDEX(data,Y,X)),"",INDEX(data,Y,X))

 

在解题过程中，还有不少细微处的变化，从朋友们的答案中细细琢磨，另有一番情趣。

在审题过程中，得到 czzqb 兄的帮助，在此感谢。

 

[此贴子已经被作者于2006-8-30 17:53:06编辑过]

chrisfang

QUOTE:

以下是引用山菊花在2006-8-24 15:16:37的发言：

三、在目标区域用公式Index(Data,X,Y)返回原始数据表中相应单元格的值。

为了不显示错误值，一般可用ISERROR()函数处理：

=IF(ISERROR(INDEX(data,X,Y)),"",INDEX(data,X,Y))

呵呵，为了追求返回结果的延展性，我特意没用index来返回结果，而是采用了内存数组法(最后的公式可以作为定义名称用于另外的公式运算)，结果反而弄巧成拙：

当内存数组从二维变为一维时，多单元格的数组公式自动填充了缺少的那一维。

这个错误版主也能发现，厉害！

山菊花

这错误不是我发现的。czzqb兄细心，发现了该错误。 

czzqb

因为这个错误也是我刚刚犯过的，所以一眼就看出来chrisfang兄也跟我掉到一个坑里去了。

为追求完美（同时也形成一个内存数组），我曾把X1={1,2,FALSE,4,FALSE,FALSE}通过small（）整理为X1={1,2,4}的形式（y1相同），然后用OFFSET(C3,X1,Y1)返回结果。

作完后才发现当X1或Y1为一个元素的数组时，结果不对。这才发现chrisfang斑竹说的那个问题：“当内存数组从二维变为一维时，多单元格的数组公式自动填充了缺少的那一维。”

还是对这种三维引用的掌握不好。