陶金武
大家好,我是头条号Excel自学成才
Excel行列快速转换有以下两种方法,各有优缺点,下面进行简要的介绍
方法一:使用选择性粘贴进行转置的方法
我们复制原始数据,然后右键,点击选择性粘贴,然后在弹出的窗口中,选择转置,点击确定,即可得到结果
优点:容易记忆,操作也方便,下次拿到可以直接用
缺点:将原始数据中有一个数据更改的时候,转置的数据源不会自动更新
方法二:使用transpose函数法
需要先选中转置后数据的位置,并且选好几行几列,然后在公式编辑栏里面输入公式:
=TRANSPOSE(A1:B8),按CTRL+SHIFT+ENTER三键得到结果,操作动图如下所示:
这个的缺点很明显,我们要对转置后的结果是几行几列需要很了解,然后公式记忆以及输入难度比较大
优点就是,当原始数据中更新了一个内容时,下面转置 的数据区域也会自动的进行更新,无需再次进行手动的操作,
如我们把A4单元格的内容进行更换时,第1种技巧的方法得到的结果没有任何的变化,而使用公式法,下面的数据自动的进行了更新~
这两个实用的方法,你都get到了么?
头条号:Excel自学成才,主页有更多精彩内容!
Excel自学成才
比如:问题中的表格如下,要把列和行互换,有三种方法。
图一
方法一:采用选择性粘贴的转置来实现表格行列转换。
步骤1、选中要进行行列互换的表格单元格区域,复制;
步骤2、点击要放置数据的单元格位置,接着点击“粘贴”->“选择性粘贴”项;
步骤3、在弹出的【选择性粘贴】窗口中,勾选【转置】项,并点击【确定】。
此时就可以看到表格行列转换成功。
方法二、利用TRANSPOS函数
TRANSPOS函数可以说就是为“行列转换”而生。
TRANSPOS函数的语法结构:TRANSPOSE(array)。
参数array:表示要转换的工作表区域。
步骤1、我们观察要做行列转换的原来的表格是13*5,进行行列互换的话,互换后就变成5*13,所以首先我们要选择转置后要放的单元格区域位置,5*13,这里我们选择G2:Q6;
步骤2、选择好后,在编辑栏里输入调用函数,即输入“=TRANSPOSE”;
步骤3、设定准备转置的单元格区域,即输入:“=TRANSPOSE(A1:E13)”
步骤4、按下Ctrl+Shift+Enter组合键,表格自动完成转置。
最后,可以对表格进行排版一下,得到下图:
方法三、利用INDIRECT函数和ADDRESS函数相结合
indirect函数的语法格式:=INDIRECT(ref_text,[a1])。
参数Ref_text :为对单元格的引用,一种加引号,一种不加引号。加引号,文本引用,不加引号,地址引用。
参数a1 :为一逻辑值,指明包含在单元格ref_text 中的引用的类型。a1可以为TRUE 或为 FALSE,也可以省略。
ADDRESS函数:按照给定的行号和列标,建立文本类型的单元格地址。
ADDRESS函数语法:ADDRESS(row_num,column_num,abs_num,a1,sheet_text),
Row_num参数:为在单元格引用中使用的行号,
Column_num参数:为在单元格引用中使用的列标,
ABS_num参数:为返回的引用类型,具体如下:
A1参数:为用以指定 A1 或 R1C1 引用样式的逻辑值。如果 A1 为 TRUE 或省略,函数 ADDRESS 返回 A1 样式的引用;如果 A1 为 FALSE,函数 ADDRESS 返回 R1C1 样式的引用。
Sheet_text参数:为一文本,指定作为外部引用的工作表的名称,如果省略 sheet_text,则不使用任何工作表名。
所以,以上图一的表格,要把行和列转置,可以直接运用公式:=INDIRECT((ADDRESS(COLUMN(A1),ROW((A1))))),具体看以下步骤:
步骤1、首先我们在转置后要放的单元格区域的第一个单元格,现在我这里选择G1单元格,输入以下公式:=INDIRECT((ADDRESS(COLUMN(A1),ROW((A1))))),即插入A1单元格的内容;
步骤2、要做行列转换的原来的表格是13*5,进行行列互换的话,互换后就变成5*13,在步骤1的时候,转置后的新表格第一单元格我们是放在G1,所以这里,转置后的新单元格区域是G1:Q5,所以我们复制G1单元格的公式,填充到G1:Q5区域的所有单元格,可以看到原来的表格行和列就互换成功了。
Office学习
能完成这个需求的技巧非常多,今天我就挑其中最简单最实用的技巧给大家介绍一下。
一、强大无比的选择性粘贴(转置)
选择性粘贴是Excel自带的一个非常强大实用的简单功能之一。其不仅可以直接执行粘贴的任务,而且还可以执行运算、转置等任务。那么我们该如何利用选择性粘贴搞定上面的需求呢?
方法:选择A1:N6区域,按下Ctrl+C组合键进行复制,然后选中A9单元格,按下Ctrl+Alt+V组合键打开“选择性粘贴”对话框,其他项下勾选转置,单击确定即可。
二、简单实用的Transpose函数
函数永远都是Excel中最实用强大的功能之一,可以这样说,如果Excel没有函数的话,整个Excel软件都会变得毫无魅力。Transpose函数为转置函数,可以批量互换表格的行与列。依然以此例来进行操作,仅仅一条公式即可达到我们的目的。
注意:1.在利用transpose函数进行行列转换时,一定记得选择的单元格区域要与原数据区域匹配,本例中,原数据的行数等于新数据区域的列数,原数据的列数等于新数据区域的行数。
2.编辑输入公式后,记得按住Ctrl+Shift+Enter组合键,而不是只敲回车,完成数组公式的录入。
我叫胡定祥,酷爱Excel。头条号:傲看今朝。自由撰稿人,办公室er.酷爱Excel,一个有两把“刷子”的胖子。欢迎关注我,有任何问题,十分欢迎大家在评论区留言。傲看今朝
对数据进行不同维度快速拆分
案例说明:将对应人员产品的销量,按照右边的格式进行快速拆分
函数公式:
SUMPRODUCT(($B$3:$B$14=$G3)*($C$3:$C$14=H$2)*($D$3:$D$14))
函数讲解:
这里我们可能需要注意的就是了解相对引用和绝对引用方法的使用。比如:
($B$3:$B$14=$G3),我们固定了G3的列,这样是我们往右边拖动的时候,对应的姓名不会变,往下拖动姓名才会变;
($C$3:$C$14=H$2,我们固定了行,因为这样我们往右边拖动的时候,列可以自动发生变化,但是往下拖动的时候行不会发生变化。
Excel函数与VBA实例
在Excel表中我们经常会遇到把行转为列,把列转为行。当然相信还有不少人都是一个一个去把行的内容复制粘贴到列,这样做重复了很多次而且工作效率极低。那么有什么方法可以快速把Excel表的行转为列,列转为行呢?这里给大家介绍2个方法,保证你看完一遍就懂!
当下面表格的名字越来越多时,表格也就越来越来,查看也就越来越麻烦。
而下面这个表格显示看起来比较舒心,这也是我们平时制作表格的方式。
那么通过对比,我们知道哪个图片才是我们想要的。但是有时候别人发给我们的表格并不是我们想要的方式,而且数据量很大,这时该用什么方法变成我们想要的呢?
方法一:使用“选择性粘贴”
步骤:选中数据区域 -- 按快捷键“Ctrl + C”复制 -- 在空白单元格点击鼠标右键 -- “选择性粘贴”-- “转置”。
动图演示:
方法二:使用行列转置函数“TRANSPOSE”。
步骤:选择一个单元格区域用于显示结果(A8:F16) -- 在编辑栏输入公式“=TRANSPOSE($A$1:$I$6)”-- 按快捷键“Ctrl + Shift + Enter”即可。
这个方法有一个不足之处就是:生成的区域无法直接修改。只有当原数据更改时,生成的数据才会随着更改。
如果您觉得本文不错,请关注+转发+点赞+评论!
跟小小筱学办公技能
在工作中,我们经常会用到Excel,学会一些Excel操作技巧,能让我们在工作中省力不少,今天我们就来看看Excel中的行列互换问题。
一、通过函数来转换行列
1、一列变一行:
=INDEX($A1:$A9,COLUMN(A1))
2、一行变一列:
=INDEX(A$5:I$5,ROW(A1))
3、一行变多行:
①先行后列:=INDEX($A$1:$I$1,(ROW(A1)-1)*3+COLUMN(A1))
②先列后行:=INDEX($A$1:$I$1,(COLUMN(A1)-1)*3+ROW(A1))
二、其他便捷转换技巧
上面的函数一个都记不住,怎么办?下面在来看一些使用的行列互转技巧吧。
1、转置
操作方法:复制表格后,选择“选择性粘贴”,勾选“转置”,不用繁琐的函数,直接转换行列。
2、利用剪贴板
想将多列变成一列,可以借助剪贴板功能,将内容先复制到剪贴板中,在进行粘贴。
3、错位引用
场景①:多列变一列
如下图:在A5输入“=B1”,错位粘贴,接着复制A列中的所有数据,选择“选择性粘贴—数值”,将公式转成数值,将多列变成一列。
场景②:一列变多列
在C1输入“=C6”(第二列开始位置),接着向下、向右填充数据,然后复制“D1:G5”区域,选择“选择性粘贴—数值”。
以上就是一些关于Excel行列转换的技巧了。如果想要转换Excel表格格式,2010办可以直接通过“另存为”功能,更改保存类型。
而低版本的Excel如果不想下载软件,就需要借助在线转换工具了。
奇趣软件技巧达人
行列转换可以分几种情况:
1、单列(行)的转换
单行换单列,一般我们使用Excel的选择性粘贴-转置即可,多行转多列,也是同理
同理也是可以使用Transpose函数处理,但是一般还是不推荐,好奇的同学可以尝试一下
具体看效果图:
2、一列转多列
利用相对引用的原理。在旁边的列=数据源中需要切割的位置,比如本示例中,3个一列,那么就是等于4,使用总数/3 就是最后拆分出来的列数
3、多列转一列
原理基本同2
具体看效果图:
4、二维转一维
使用PQ中的逆透视功能,完成二维转一维,方便透视处理
Power Query 是2010版本新增的功能,2010和2013版本需要下载安装插件,具体百度一下,按照自己的版本下载安装,如果是2016版本,那么自带PQ,在数据选项卡下
我们本例是用时了,转换功能中的逆透视其他列功能完成,非常的高效快捷!!
5、一维转二维(多维)
利用数据透视表,可以完成多维的分析,非常的方便
一维数据是非常方便分析,一般在Excel中使用透视表即可完成一维的各种转换分析
Excel办公实战
关于行列转换,我提供一个非常简单的方法:
案例:
怎么把 A 列数据快速填充到黄色区域?按先行后列的习惯排列?
解决方案:
1. 在黄色区域依次填入A1~A24,用填充+拖动方法可以快速实现
- A可以是任意值
- 数据列的第一行为几,数字就以几开头,比如本例的第一个数据在行 1
2. 选中黄色区域 --> 按 Ctrl+F --> 将“A”全部替换为“=a”
这就已经完成了。
Excel学习世界
EXCEL中的行列转换常见有几种方法:
1、选择性粘贴
2、Transpose 函数
3、INDEX函数
下面具体来看下怎么操作吧:
选择性粘贴:复制→选择性粘贴→转置
Transpose函数:选中转置后区域,输入下列函数
=TRANSPOSE(A1:D5) 按住Ctrl+Shift+Enter输入
INDEX函数:输入下面函数,往右往下拖即可:
=INDEX($A$1:$D$5,COLUMN(A:A),ROW(1:1))
选择性粘贴法最为简便,但每次都需要操作一次,对于使用频次非常高的场景可能效率会有点低。函数法不能保留格式,但是源数据发生更改可以自动更新,并且一次操作永久保留,TRANSPOSE函数使用更加简洁,但需要事先选择好转置后区域,数据量较大时使用不太方便,INDEX函数使用比较灵活!结合您的需要酌情使用!
Excel精选技巧
选中整个表格,按 ctrl+c 复制,在下方点击右键,点击“选择性粘贴”中的 “转置”,这样新复制出来的表格行列内容就互换了
打开链接可以查看操作视频哦
https://quqi.gblhgk.com/s/839459/hQBmnFW2ze7AOlga