谈谈excel的表格转置.pdfVIP

谈谈excel的表格转置 （胖猴 日本地震，造成国内谣言四起，抢购食盐的风潮席卷全国。昨天，上贺州红豆论坛，发现几乎全是关 于盐的帖子。本猴突发一念头，想统计一下坛子里究竟有多少关于盐的帖子，于是想用excel 导入论坛的 列表，不料竟不成功。只好将数据存为“文本文件”，再进行转换。上网搜了一下相关的文章发现，关于表 格转置的问题真的值得写一写。 表格转置，有三种方法： （1）直接用excel 的“选择性粘贴”－“转置”。 （2）借用word 的“文字转表格”，再复制到excel. （3）用excel 的公式 offset() 对表格转置。 第一种方法适用于简单地把表格的行与列进行转换，如图1 方法：选中要复制的表格＞复制＞菜单“编辑”＞选择性粘贴＞选“转置”＞确定＞完成。如图2 第二种方法，适用于比较整齐的，单列的表格。例如：从红豆论坛上取到的数据，以文本方式粘贴到 word 中。我们可以看出，它是每三行一组的，即每三行为一条记录。现在要把它转换成表格，也就是每 三行文本转换成表格中同一记录的三列。如图4 首选，要做一些处理：将文本中的“换行符号”，也就是回车符号换掉，换成可以被word 识别的制表 符号。可以用任意符号，但在这里我建议用一些特别些的符号，关键是文本中没有出现过的符号，所以我 用了这个“◤”。（见图5）为什么呢？因为，如果用了文本中出现过的符号，就可能在不需要制表符的地方 出现制表符，造成表格分行的错误。 替换后，如图6 然后，选中文本＞在菜单“表格”＞转换＞文本转换成表格＞在对话框中选好“列数＝3” ＞确定。如图 7、图8： 完成后的word 表格，如图9。然后把这个表格全选，复制粘贴到excel 就可以了。 第三种方法：用函数 offset() 进行表格转置。 函数：OFFSET(reference,rows,cols,height,width) 以指定的引用为参照系，通过给定偏移量得到新的引 用。返回的引用可以为一个单元格或单元格区域。 说明：参数1－reference，参照区域，标杆单元格 参数2－rows,相对于标杆单元格的行偏移 参数3－cols，相对于标杆单元格的列偏移 参数4－height，指定返回区域的行数 参数5－width，指定返回区域的列数 例子：如图10 说明：公式＝offset(B3,2,-1,1,1) 就是以B3为参照格，返回B3往下2行，往左1列（正数表示往右，负 数表示往左）的1×1的区域。也就是A5的值。 各位可以改一下参数，看看效果，就会加深对这个函数的理解。 好回到我们图3的例子： 在A列中，每三行为一组，实际上是论坛上的一条记录，现在要把每组的三行分别转换成三列。如图11， 在B1中输入公式 =OFFSET($A$1,ROW()*3-3,0) 然后填充公式到B2、B3往下，可以看到，在B 列中得到每组数据的第一个值。 同理在C1中输入公式 =OFFSET($A$1,ROW()*3-2,0) 然后填充公式到C2、C3往下，可以看到， 在C列中得到每组数据的第二个值。 在D1中输入公式 =OFFSET($A$1,ROW()*3-1,0) 然后填充公式到D2、D3往下，可以看到，在D 列中得到每组数据的第三个值。 于是完成了我们想要的结果。如图12 结束语： 表格转置是常用的操作，多数情况下可以不用公式轻松解决，但对于复杂的转换，遇要用到公式。不 过掌握公式要点耐心。