☰

如何重组大型 Excel 文件

tag-icon • tag-icon microsoft-excel
如何重组大型 Excel 文件

我的一个朋友“继承”了一个用作地址文件的 Excel 工作表。

不幸的是，它的创造者想出了一个可怕的主意，把它做成这样：

Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302
       |                    |         |                    |        |
Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302
       |                    |         |                    |        |
Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302
       |                    |         |                    |        |
Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302

当然，我的朋友想将其重新格式化为：

|Name         | Surname      |Tel       |City         |Street    |ZipCode |
|Lorem        |Ipsum         |01010101  |Ipsumville   |Lorem St. |10139   |
|    ...      | ...          | ...      | ...         | ...      |  ...   |
|Lorem        |Ipsum         |01010101  |Ipsumville   |Lorem St. |10139   |
|Lorem        |Ipsum         |01010101  |Ipsumville   |Lorem St. |10139   |

我可能将其保存为 csv，拼凑一个 Perl 脚本来解析 csv 并以更合理的方式重新组织它，但我现在很忙，所以如果有人有一些非编程解决方案，我会看看他是否可以自己管理它。

坏消息 电子表格不规则。也就是说，整个地址可能跨越 2 行以上，这或多或少是随机的。显然，这是来自一些小型会计程序的一种报告，它们打印到文件中，然后将其放入 Excel 中。我不会看看解析-o-matic可以帮助。

（我正在研究 Parse-O-Matic，因为几年前我曾用它解决过类似的问题，有一个免费的基本版本，我希望它将来可以作为通用工具对我的朋友有用）。

答案1

这很简单。假设您的数据从A1单元格开始：

| A1 | B1 | C1 | ...

| A2 | B2 | C2 | ...

在顶行的第一个空单元格中输入公式=A2，然后将单元格向右拖动/填充相同数量的列，这样您的工作表看起来就会像这样：

| A1 | B1 | C1 | ... | =A2 | =B2 | =C2 | ...

| A2 | B2 | C2 | ...

  1. 选择顶行中包含公式的所有单元格，然后将其向下拖动/填充到其余行
  2. 选择整个工作表，然后将特殊（仅值）复制并粘贴到另一张工作表中
  3. 在新工作表中，过滤掉除第一列中带有“名称”的行之外的所有内容
  4. 使用实际数据为列创建标题
  5. 删除带有旧标题的列

答案2

我不能保证这比其他建议的想法省力得多，但这是我的解决方案；我翻译了

                在此处输入图片描述

到

                在此处输入图片描述

（在 上Sheet2）。公式如下：

  • A2：=OFFSET(Sheet1!$B$1, ROW()*3-6, 0, 1, 1)
  • B2：=OFFSET(Sheet1!$D$1, ROW()*3-6, 0, 1, 1)
  • C2：=OFFSET(Sheet1!$F$2, ROW()*3-6, 0, 1, 1)
  • D2：=OFFSET(Sheet1!$D$2, ROW()*3-6, 0, 1, 1)
  • E2：=OFFSET(Sheet1!$B$2, ROW()*3-6, 0, 1, 1)
  • F2：=OFFSET(Sheet1!$F$2, ROW()*3-6, 0, 1, 1)

当然，您可以向下拖动/填充。每次OFFSET调用中的第一个参数是第一张表中所需字段第一次出现的位置；例如，您想要Tel在 列 中，而中的C第一个字段在 单元格 中。将当前行号乘以 3，因为 上的每个“记录”只有一行，但 上的记录各有三行。-6 只是一个模糊的因子，让它正确出现，假设您有 上的标题而 上没有任何标题–– 第一个记录在第 2 行，2*3 是 6，但是，要通过 的偏移量访问，您需要 0 的偏移量（因此，减去 6）。TelSheet1F2Sheet2Sheet1Sheet2Sheet1Sheet1!B1Sheet1!B1

您可以让 Sheet2 永久链接到 Sheet1，也可以复制和粘贴值，然后丢弃它。

答案3

我想到了一个可能的解决方案：

  1. 创建一个新的空白表。
  2. 在原始工作表中，创建一个自动过滤器，并在第一列上按“名称”进行过滤。这将“选择”所有奇数行。在新工作表中选择并复制。
  3. 创建另一个过滤器，使用不同的单元格内容（街道）。这将选择所有偶数行。将这些复制到新工作表中，将行放在第一个选择的右侧。
  4. 如果您的数据分布在 3 行或更多行上，则相同的方法可以起作用（我的示例只有两行，但这可能适用于 3 行或更多行模式）。
  5. 在顶部创建一行，添加字段名称
  6. 删除字段标签所在的列。

相关内容