求Excel表格 多行多列 转成 多行多列 公式

发布网友

我来回答

共3个回答

热心网友

假设你的源数据在Sheet1，A1开始。
在Sheet2的A1输入
=OFFSET(Sheet1!$A$1,(COLUMN(A:A)-1)/5+(ROW(1:1)-1)*2,MOD(COLUMN(A:A)-1,5))
回车比你向右填充到J1，再一起向下填充。
也可以在Sheet2的名称框输入
A1:J40000
——回车——在编辑栏输入以上公式——按Ctrl+Enter结束公式一步搞定。追问完美,测试了一下,不过我还是想请教一下这里这几个数值怎么变换。
如果源表格是 8万行 3列 第4列是空的
我想转换成 7列 第4列后边空一列
那么填充的公式就是
=OFFSET(Sheet1!$A$1,(COLUMN(A:A)-1)/3+(ROW(1:1)-1)*2,MOD(COLUMN(A:A)-1,4))
我理解的对么?

追答给你解释一下函数意义吧：
=OFFSET(Sheet1!$A$1,(COLUMN(A:A)-1)/5+(ROW(1:1)-1)*2,MOD(COLUMN(A:A)-1,5))
这是一个偏移函数公式，里面只用了4个函数。
1、OFFSET()——偏移函数，语法：OFFSET(引用,偏上下,偏左右,范围高,范围宽)
第二个参数起可以省略，前面的参数省略时要保留逗号，后面的参数省略时也可不留逗号。
2、COLUMN()——列号函数，COLUMN(A:A)=1、向右填充COLUMN(B:B)=2、COLUMN(C:C)=3、……
3、ROW()——行号函数，ROW(1:1)=1、向下填充ROW(2:2)=2、ROW(3:3)=3、……
4、MOD()——余数函数，语法：MOD(被除数,除数)
这样你应该会明白吧？
————————
你要3列（第4列是空的）转换成7列（第4列空1列）：
=OFFSET(Sheet1!$A$1,(COLUMN(A:A)-1)/4+(ROW(1:1)-1)*2,MOD(COLUMN(A:A)-1,4))

热心网友

举例：
1、将一列60个数据转换为5行12列。

首先，在B1格输入“A1”，B2格输入“A13”，然后选中B1、B2，将鼠标移到选中框的右下角（此时鼠标变为“+”形，下同），按住左键不放将框下拉至B5处。此时B3、B4、B5格将分别填充“A25”、“A37”、“A49”。

2、继续将鼠标放至右下角，按住右键不放将鼠标右拉至M列处，此时弹出对话框。在对话框中选择“填充序列”，结果如图。

3、找到上方工具栏中的“查找与选择”，单击“替换”。在弹出的对话框中，查找内容中键入"A",替换内容键入"=A",单击"全部替换"。

4、至此完成转换。追问你说的是60个数据,这个方法我试过了,而且人家答案有GIF动画的比你更清楚。问题是源表8万行,即便是转成10列还有8000行,你能告诉我怎么能一下子填充1万行公式吗?我用ctrl+D填充的都是第一行数据

热心网友

这么大的数据量，建议用宏代码，公式运行起来应该会很卡追问确实卡了一会。那所以说,宏怎么弄?通配1列或者多列源表格的

追答

用宏代码的话只需以下几行即可，自己看着都能修改，主要是瞬间就可完成：