复制/导出/输出多个特定的单元格数据到新的电子表格 [英] Copy/export/output multiple specific cell data to a new spreadsheet

问题描述

我有一个大型Excel电子表格，该电子表格已经过几年的定制和扩展(有许多工作表)，我想从中提取特定数据，然后按照严格的格式将这些数据插入新的电子表格中

I have a large Excel spreadsheet, that has been customized and augmented over a few years (with many worksheets), from which I'd like to extract specific data, and have that data inserted into a new spreadsheet following a strict format.

第二个较新的电子表格是较大和较旧的电子表格的子集，但是第二个电子表格中的数据必须遵循严格的格式-没有标签或标题，仅是列和行中的数据，从单元格A1开始，并从在那里，还有多个工作表.

The second newer spreadsheet is a subset of the larger and older spreadsheet, however the data in the second spreadsheet must follow a strict format - no labels or headers, just data in columns and rows, starting with cell A1, and proceeding from there, with multiple worksheets as well.

我如何才能从原始电子表格中的特定单元格中获取数据，并通过宏或其他某种机制以编程方式将其插入到第二个较新的电子表格中，这样我只需单击此宏按钮"，数据便会转移?较大的旧电子表格中的数据不一定是完整的行或列(尽管可以)，而可能只是一个单元格，必须将其放置在较新的电子表格的特定单元格中.

How can I take data from specific cells in the original spreadsheet, and programmatically insert them into the second newer spreadsheet, via a macro or some other mechanism, such that I might simply click this "macro button" and the data will be transferred? The data from the older larger spreadsheet will not necessarily be an entire row or column (although it can be), but may also simply be a single cell that must be placed into a specific cell of the newer spreadsheet.

推荐答案

我将您的问题解释为意味着您在源工作簿中有大量的单元格和范围要移动到目标工作簿.试图使数百个复制语句正确可能是一项非常困难的任务.还有一个问题是，知道要去哪里的工作簿的所有者必须对此进行记录，以供程序员实施.这增加了工作总量，并增加了沟通不畅的机会.

I interpret your question to mean that you have a substantial number of cells and ranges in the source workbook to be moved to the destination workbook. Attempting to get hundreds of copy statements correct can be a very difficult task. There is also the problem that the owners of the workbook, who know what is to go where, have to document this for the programmer to implement. This increases the total amount of work and increases the opportunities for miscommunication.

我发现成功的一种技术是在第三本工作簿中使用复制宏.第三本工作簿包含一个工作表，用于指定所有副本.例如

A technique I have found successful is to have the copy macro in a third workbook. This third workbook contains a worksheet that specifies all the copies. For example

|     A     |     B   |     C     |     D   |
| ------ Source ----- | --- Destination --- |
| Worksheet |  Range  | Worksheet |   Cell  |

对于每个数据行，A列和B列标识源工作簿中的范围，而C列和D列标识目标工作表中范围的左上角单元格.

For each data row, columns A and B identify a range within the source workbook while columns C and D identify the top left cell of the range within the destination worksheet.

此技术的优点包括:

• 任何Excel用户都可以理解该格式；它不需要任何编程技能.
• 如果涉及多个用户来指定目标工作簿，则每个人都可以独立地开发其说明部分.
• 遵循"此指令列表需要最少的代码.

代码要执行的任务是:

• 开源工作簿.
• 打开空的目标工作簿.
• 找到最后一行说明.
• 对于每行指令:将A列和B列定义的范围复制到C列和D列定义的单元格.
• 通过保存关闭目标工作簿
• 关闭源工作簿而不保存

目标工作簿的副本可以传递给所有者以供批准.任何错误都将需要更新指令列表并重新运行宏.要获得理想的结果可能需要花费多个周期，但每个周期都非常易于管理.

Copies of the destination workbook can be passed to the owners for approval. Any mistakes will require updating of the instruction list and rerunning of the macro. It may take a number of cycles to get the desired result but each cycle is quite easy to manage.

上表是我实现过的最简单的表，但可能有更复杂的版本，例如:

The table above is the simplest I have ever implemented but more complicated versions are possible For example:

 |     A     |     B   |     C     |     D   |
1| ------ Source ----- | --- Destination --- |
2| Worksheet |  Range  | Worksheet |   Cell  |
3|Sheet1     |A1:T230  |Data       |A1       |
4|Sheet2     |A1:T150  |Data       |A?       |
5|Sheet1     |A1:T150  |Data       |A1       |
6|Sheet2     |A1:X150  |Data       |?1       |

在第4行中，问号指示目的地是最后一个副本之后的下一个空闲行.当合并几个小表时，这非常方便，因为不必计算哪一行是下一行.在第6行中，目标是下一个空闲列.

In row 4, the question mark indicates that the destination is the next free row after the last copy. This is very convenient when several small tables are combined since it is not necessary to calculate which row is the next row. In row 6, the destination is the next free column.

在没有清晰了解重建复杂性的情况下，无法建议您应该采用这种技术的程度.您需要检查所有类型的副本，并确定指定这些副本的最简单方法，而又不会使编程变得太困难.

Without a clear idea of the complexity of the rebuild, it impossible to suggest how far you should take this technique. You need to review all the types of copy and determine the easiest way of specifying those copies without making the programming too difficult.

这篇关于复制/导出/输出多个特定的单元格数据到新的电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！