注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oh! I see you!

Hi! ppmm~~

 
 
 

日志

 
 

Excel矩阵区域数据转换成一列(行)或者多列  

2009-12-09 02:24:20|  分类: Excel |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

本文链接:http://oicu.cc.blog.163.com/blog/static/123039471200911922420966/

矩阵转向量 / Matrix To Vector

数据在Sheet1,结果放Sheet2,下面的函数都是在Sheet2使用。

Sheet1!A1:C2的数据:
1    2    3
4    5    6

Sheet2!A1=OFFSET(Sheet1!$A$1,INT((ROW(1:1)-1)/3),MOD(ROW(1:1)-1,3))&""
公式中的3为数据区域的列数。向下填充,得到结果:
1
2
3
4
5
6


Sheet2!A1=OFFSET(Sheet1!$A$1,MOD(ROW(1:1)-1,2),INT((ROW(1:1)-1)/2))&""
公式中的2为数据区域的行数。向下填充,得到结果:
1
4
2
5
3
6

如要转换成一行则把ROW(1:1)改为COLUMN(A:A)即可。


上面的例子太简单,来个难点的,要把下面的数据转换成2列,第1列的值搭配第2列之后的每个值。

A       A1
B       B1      B2      B3
C       C1      C2

要转换成:

A       A1
B       B1
B       B2
B       B3
C       C1
C       C2

 

法0:
如果有 Excel 2016 可以使用自带的强大的数据分析工具:

1、选择数据区域,数据——从表格——创建表,不勾选“表包含标题”。
2、在“查询编辑器”里,选择列1,转换——逆透视其他列,删除多余的属性列,开始——关闭并上载。


法1:
最简单又无后遗的当然是使用vba来解决了。
Sub test()
    Worksheets(2).Range("A:B").Clear
    m = 0
    For i = 1 To Sheets(1).UsedRange.Rows.Count
        For j = 2 To WorksheetFunction.CountA(Sheets(1).Rows(i))
       '或者指定6列
       'For j = 2 To WorksheetFunction.CountA(Sheets(1). _
       '    Range(Sheets(1).Cells(i, 1), Sheets(1).Cells(i, 6)))

            m = m + 1
            Sheets(2).Cells(m, 1) = Sheets(1).Cells(i, 1)
            Sheets(2).Cells(m, 2) = Sheets(1).Cells(i, j)
        Next j
    Next i
End Sub

 

法2:
另存Sheet1为“文本文件(制表符分隔)(*.txt)”,假设文件名为In.txt。
注:不能存为Unicode文本。

下载awk for windows版本:
http://gnuwin32.sourceforge.net/downlinks/gawk-bin-zip.php
解压bin里的gawk.exe,打开cmd,执行下面的命令:

gawk "{ for (i=2; i<=NF; i++) print $1 \"\t\" $i }" In.txt>Out.xls

打开Out.xls复制数据到原工作簿的Sheet2即可。

Windows版的awk不能使用单引号,如果在Linux下使用,命令是:
awk '{i=NF;while(i>1){print $1,$i;i--}}' In.txt
或者
awk '{for(i=2;i<=NF;i++) print $1,$i }' In.txt

 

法3:
A1=IF(B1="","",OFFSET(Sheet1!$A$1,INT((ROW(1:1)-1)/6),0))&""
B1=OFFSET(Sheet1!$A$1,INT((ROW(1:1)-1)/6),MOD(ROW(1:1)-1,6)+1)&""
向下填充。上面公式中的三个数字6是随便取的,>=(Sheet1数据区域的列数-1)

填充后,复制2列,选择性粘贴(数值),排升序,删掉空行。
这个函数较简单,但不能动态变化,操作麻烦。

&""的作用是去掉显示0、及转成字符格式。

 

法4:
加一辅助列,且函数也比较麻烦,但区域不改变大小时(特别是行数不变时),可以动态显示结果。

辅助列的作成:
在Sheet1的数据区域最右边选取一列,比如H列。如果Sheet1的数据有3行,
则同时选择H1:H3,然后输入数组公式:
=COUNTA(INDIRECT(ADDRESS(1,2)&":"&ADDRESS(3+1-ROW($1:$3),6)))
或者
=COUNTA(INDIRECT("$B$1:$D"&(3+1-ROW($1:$3))))
数组公式输入后要按Ctrl+Shift+Enter确定。

上面公式中的“3+1”和“$3”里的3是数据区域的行数;
6和列数有关,>=(Sheet1数据区域的列数-1)
$D同样也和列数有关,>=数据区域的列数。

然后在Sheet2中用公式:
A1=IF(ROW(1:1)>Sheet1!$H$1,"",INDEX(Sheet1!$A$1:$A$3,3+1-MATCH(ROW(1:1),Sheet1!$H$1:$H$3,-1)))&""
B1=IF(A1="","",OFFSET(Sheet1!$A$1,3+1-MATCH(ROW(1:1),Sheet1!$H$1:$H$3,-1)-1,COUNTIF($A$1:A1,A1)))&""
上面公式中的“3+1”和“$3”里的3是数据区域的行数,使用时候需要根据实际更改,其他参数都不用改。

  评论这张
 
阅读(13078)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018