在Excel中调整数据不工作(Transposing data in Excel Not Working)

Office IT屋
问 题

I have a file from which I copied a big column and tried to paste it in another excel using paste-special "transpose". But I was unable to do the transpose, it gave me an error.

Error: The information cannot be pasted because the copy area and the paste area are not the same size and shape

解决方案

Here are all the possibilities:

The maximum number of columns on a worksheet is 16,384 - Worksheet size:1,048,576 rows by 16,384 columns for Excel2010. The range you're trying to transpose may have more than 16,384 rows and will thus overflow the maximum number of columns available.

OR

The most likely cause is that you have selected a group of cells to copy and then when you want to paste, you have selected another group of cells that are not the same cell size.

Usually you can get past this error by selecting only a single cell and pasting to that cell. The cell you selected will act as the top-left most cell in the pasted selection, or the top most cell if it’s only a single column of data.

OR

However, there is another cause for this error that can be elusive (Grouping of sheets).

Check to make sure that you have not grouped your sheets. There are a couple of ways to tell.

First, ungrouped sheets are a different color than the selected, active sheet. In the screenshot below, notice the different color between Sheet1 and the other sheets.

enter image description here

In a grouped set of sheets, notice they are all the same color.

enter image description here

One other way to tell is to look to the top of your workbook. If you see [Group] beside the workbook name, then you know you have grouped sheets. You can’t copy a single cell from a group of sheets and paste to a workbook cell with non grouped sheets. What Excel is trying to do is copy the cells you selected from not one workbook, but from all of the grouped workbooks, as if they were stacked. Then when you select a single cell in an ungrouped workbook, Excel determines that you are trying to paste the grouped cells into a single cell on a single worksheet and says “Nope, can’t fit all of that there!Imagine trying to fit a single stack of 100 folded towels into a small suitcase and close the lid. It would be impossible.

To group worksheets, select a worksheet and then hold the SHIFT key down and select another worksheet. You can also hold down the CTRL key to do a relative selection (you can pick and choose). When multiple sheets are selected in this manner, the sheets are automatically grouped and the [Group] warning appears at the top of the workbook. You can ungroup the sheets by selecting only one of the worksheets in a grouping and the rest will turn the darker color. You can also right click a group of selected sheets and left click the “Ungroup Sheets” option.

Reference : The information cannot be pasted because the copy area and the paste area are not the same size and shape

本文地址:IT屋 » Transposing data in Excel Not Working

问 题
我有一个文件,我从中复制了一个大列,并尝试使用粘贴特殊“转置”将其粘贴到另一个excel中。但是我无法做转置,它给了我一个错误。



错误:无法粘贴信息,因为复制区域和粘贴


解决方案

以下是所有的可能性:



工作表上最大列数为16,384 - 工作表大小:Excel2010的1,048,576行16,384列。您尝试转置的范围可能超过16,384行,因此将超出最大可用列数。





最有可能的原因是您选择了一组要复制的单元格,然后当您要粘贴,您已经选择了另一组不一样的单元格大小的单元格。



通常您可以通过仅选择一个单元格来<并粘贴到该单元格。您选择的单元格将作为粘贴选择中的左上角的单元格,或者只是单列数据的顶层单元格。





但是,这个错误的另一个原因是难以捉摸(



选中确保您没有对表进行分组。有几种方法可以告诉。



首先,未分组的工作表与选定的活动工作表不同。在下面的截图中,请注意Sheet1和其他工作表之间的不同颜色。





在分组的工作表中,注意它们都是相同的颜色。





另一种方法可以看看你的工作簿的顶部。如果您在工作簿名称旁边看到[组],那么您就知道您已经分组。您不能从一组工作表复制单个单元格,并使用非分组工作表粘贴到工作簿单元格。 Excel要做的是将不是一个工作簿中选择的单元格复制到所有分组的工作簿中,就像堆叠一样。然后,当您在未分组的工作簿中选择单个单元格时,Excel会确定您正在尝试将分组的单元格粘贴到单个工作表上的单个单元格中,并说“不能适应所有这些单元格! em>“想象一下,尝试将一叠100张折叠的毛巾放入一个小手提箱中并关上盖子。这是不可能的。



要分组工作表,请选择一个工作表,然后按住SHIFT键并选择另一个工作表。您还可以按住CTRL键进行相对选择(您可以选择并选择)。当以这种方式选择多张纸张时,纸张将自动分组,并且[组]警告出现在工作簿的顶部。您可以通过仅选择分组中的一个工作表来取消分组,其余的将变为较暗的颜色。您还可以右键单击一组选定的工作表,然后单击“取消组合表”选项。



参考:由于复制区域和粘贴区域的大小和形状不同,所以无法粘贴信息


本文地址:IT屋 » 在Excel中调整数据不工作