本文链接：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是数据区域的行数，使用时候需要根据实际更改，其他参数都不用改。
评论