☰

Excel 电子表格数据转换

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 pivot-table transpose
Excel 电子表格数据转换

我在表中有需要转换为时间序列行数据的数据。

目前我的数据如下列和格式：

GEOID, July, August, September, October, November, December, January, February, March, April, May, June

行中我们有值。

我需要 .csv 格式的数据，其列如下：

GEOID, Month, Value

我怎样才能做到这一点？

答案1

您可以使用免费的 Microsoft Excel 插件Power Query（来自 Excel 2010）取消透视数据并以表格形式获取列表。

要进行转换，请按照MS 网站。

  1. 在 Power Query 中导入数据（功能区 Power Query -> 从表格，光标必须位于数据的某个位置）
  2. 选择第一列 (GEOID)，在功能区变换中，单击“取消透视列”>“取消透视其他列”
  3. 关闭并加载到 Excel（Ribbon Home）

答案2

如果正确理解了问题，那么问题在于您有 13 列数据，并希望将其转换为 2 列以用于 .csv。我将使用名称管理器，因为它更容易操作，但一切都可以用普通公式完成

步骤 1> 创建一个名为 csv 或 export 的新选项卡，或者其他有意义的名称，例如 fy16。.csv 将仅输出 1 张表。保存两次。一次保存为 .xlsx 以保存所有数据和公式，然后在准备好 csv 时，选择 csv 选项卡，然后另存为 csv。

步骤 2>（公式功能区 -> 名称管理器）创建 2 个名称

  • Step=13（进入名称管理新建，将其命名为步骤，并在底部输入=1）这是月份数
  • 具有 GEOID 的单元格的名称 Spot，如果它在 sheet1 中，单元格 a1 将是 =Sheet1!$A$1

在 csv 表的 A1 到 c1 中输入“GEOID,Month,Value”

现在只需要每列的公式。此公式使用偏移公式并计算行和列

GEOID，我假设每个条目都需要相同的 GEOID，而不仅仅是第一个条目，输入 A2 并复制以下公式

  • =+OFFSET(点,INT((ROW()-1)/step)+1,0)

它的作用是从 Spot 开始，每 12 次向下移动一行（使用 int 和 / step），使用 -1 和 +1 来使第一个单元格 [A2] 正确

月这将循环在顶行放入 B2 并复制以下公式

  • =+OFFSET(Spot,0,1+MOD(ROW()-2,step)) 与之前相同，只是从点开始跨列移动，而不是向下移动行。Mod 与 step 使其循环

值将沿行向下移动并跨列移动。行和列将与前两个类似。输入 C2 并向下复制

  • =+OFFSET(点,1+INT((ROW()-2)/step),1+MOD(ROW()-2,step))

如果您不想使用名称管理器，则在公式中有步骤的地方使用 12。并在必须同时使用 $ 的地方使用适当的 Sheet1!$A$1 引用

答案3

答案是否像Save As> 选择CSV文件类型一样简单？

保存为 CSV 后，在记事本或首选文本编辑器中编辑文档以检查格式并根据需要对列进行进一步调整。

我发现 TextPad 非常强大。尝试利用 TextPad 中的查找和替换功能查找逗号并将其替换为“returns(new line)” \n。将结果粘贴到 Excel 中，您的月份列现在将以行的形式显示。

相关内容