[分享]”多行多列”转换为“多行单列”的解题思路

zhongyl · 发表于 2008-4-15 16:42

这个东西其实还是很有用的

LangQueS · 发表于 2008-4-15 21:09

真不错，samsoncsr辛苦了！

hhzjxss · 发表于 2008-4-15 21:18

楼主真厉害，把一个复杂问题，讲得这么简单化了！

samsoncsr · 发表于 2008-4-16 02:08

大家对靶标法感兴趣，那我再拓展一下靶标法的用途：如何把多行多列的数据进行列调整

rRxVo4Lz.rar (5.7 KB, 下载次数: 113)

samsoncsr · 发表于 2008-4-16 02:33

今天心情不错，放假一周，那就叨磕些偏离靶标法的函数话题，谈谈INDEX函数的妙用。

INDEX函数的精华是数组应用：

数组形式

INDEX(array,row_num,column_num)

在我以前的一个帖子《函数INDEX数组应用精解》有个“月产能工时图表自动排序”的应用，大家感兴趣可以看看。

INDEX函数功能是很强大的，是一个神奇的魔法师，能进行纵向变横向、横向变纵向、多行多列转换成多行单列、多行单列转换成多行多列、多行单列选择性的截取数据。

5IoMk7mT.rar (11.07 KB, 下载次数: 99)

山菊花 · 发表于 2008-4-16 09:16

H1=INDIRECT(TEXT(SMALL(IF(B$1:E$6<>"",ROW($1:$6)*1000+COLUMN(B:E)),ROW()),"r#c000"),)

排错(假设IV999单元格是一空白单元格)：

H1=INDIRECT(TEXT(SMALL(IF(B$1:E$6<>"",ROW($1:$6)*1000+COLUMN(B:E),999256),ROW()),"r#c000"),)&""

或：

H1=IF(ROW()>COUNTA(B$1:E$6),"",INDIRECT(TEXT(SMALL(IF(B$1:E$6<>"",ROW($1:$6)*1000+COLUMN(B:E)),ROW()),"r#c000"),))

公式均为数组公式，按Ctrl+Shift+Enter结束。

andysky · 发表于 2008-4-16 11:21

QUOTE:

以下是引用samsoncsr在2008-4-11 3:20:16的发言：

昨遇一贴，要求如下：

1：H列>>用数组公式把B1:E6的多行多列字符转换成多行单列。
2：G列>>用数组公式把H列字符所对应的A列数值进行单列排列。
3：不用辅助行。

这个公式有点繁烦，还可以大大的精简。

看了看共有176个字符，而且没有防错的功能。

加上防错100个字符以内就可完成同样功能。

=INDIRECT(TEXT(SMALL(IF(B$1:E$6<>"",ROW($1:$6)*1000+COLUMN(B:E),65536001),ROW()),"R0C000"),)&""

其实还可以延伸一下，求出该区域的不重复值，以前我在2007专区发过这个公式----求多行多列的不重复值

[此贴子已经被作者于2008-4-16 11:40:30编辑过]

shaowu459 · 发表于 2008-4-16 11:32

QUOTE:

H1=INDIRECT(TEXT(SMALL(IF(B$1:E$6<>"",ROW($1:$6)*1000+COLUMN(B:E)),ROW()),"r#c000"),)

不愧是超级斑竹……每个函数都能用的这么奇妙……

花儿无缺 · 发表于 2008-4-16 12:26

QUOTE:

以下是引用samsoncsr在2008-4-16 2:33:43的发言：

今天心情不错，放假一周，那就叨磕些偏离靶标法的函数话题，谈谈INDEX函数的妙用。

INDEX函数的精华是数组应用：

数组形式

INDEX(array,row_num,column_num)

在我以前的一个帖子《函数INDEX数组应用精解》有个“月产能工时图表自动排序”的应用，大家感兴趣可以看看。

INDEX函数功能是很强大的，是一个神奇的魔法师，能进行纵向变横向、横向变纵向、多行多列转换成多行单列、多行单列转换成多行多列、多行单列选择性的截取数据。

呵呵,关键是数组的思想,用思想来引领思路就能产生一个又一个技巧.数组的灵魂就是一一对应,数组的一一对应包括值和数组元素的"位置".把握一点,无论是因变量还是最后结果,其一一对应都是对应本初变量的数组属性的.这里INDEX第二个参数的数组化用的就很灵性.

就这个思想也很好结实SUMIF中对第三个参数的特殊用法,呵呵.我想楼主,通过这个帖子的综合运用已经对数组有了比较高级的认识.呵呵,收藏学习了，呵呵

samsoncsr · 发表于 2008-4-16 13:52

呵呵！

山版的=TEXT(M1:M17,"r#c000")这个函数用法是鲤鱼跳龙门呀，直接跳过附件中的3、4两个步骤（分离行号、分离列号）了，真是很经典。

andysky版主的=TEXT(M1:M17,"R0C000")这个函数也是鲤鱼跳龙门。

TEXT的第二参数功能很神奇的咧！

真是提炼精华之笔！

		自动登录	找回密码
密码			免费注册