☰

从多个 Excel 工作簿中创建新的数据电子表格

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function merge
从多个 Excel 工作簿中创建新的数据电子表格

回想起来，这是一个数据收集的问题，但我想看看是否有一种简单的方法可以做到这一点，而不必处理每个文件。

我有大约 350 个独立的 Excel 工作簿。每个工作簿都包含与一个人相关的一些信息。

我想要做的是创建一个包含每个人必要数据的简单电子表格，以便可以使用。

给你一个想法。A1 列出“姓名”，B1 包含人员姓名。A2 包含“最后 4 位 SSN”，B2 包含 4 位数字。

然后 A3 上有单词“年份”，然后 A4 有“2014 est”，A5 - “2013”​​，A6 - “2012，等等，到 2009 年。

然后在第 3 行从 B 到 N 有不同的信息。

如果可能的话，我想要做的是有一个主电子表格，其中包含姓名列、最后 4 个 ssn，然后是每条数据的年份。例如 2013 年兴趣

这可能吗？我希望这是有意义的。

答案1

我会为此使用 Power Query 插件。您可以从 Excel 表中启动查询。如果您的电子表格位于单独的文件中，它具有一个很棒的功能，可以一步导入 Windows 文件夹中的所有文件并将其所有数据附加在一起（假设文件列一致）。如果它们是单个文件中的多个工作表，那么您将需要多个查询，您可以使用附加命令将它们合并在一起。

http://office.microsoft.com/en-au/excel-help/append-queries-HA104149760.aspx?CTT=5&origin=HA103993872

还有一个 Unpivot 命令可以将存储在多列中的数据转换为多行 - 也许这可以解决您的多年挑战。

答案2

您可以使用 VBA 合并文件夹中所有工作簿的数据。如果您熟悉 VBA，可以将以下代码添加到工作簿并运行它。它将创建一个新的工作簿，其中包含您在 中指定的文件夹中的文件的所有数据FolderPath。

注意：为了安全起见，在运行此代码时将所有文件的副本复制到单独的文件夹中是个好主意。

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range

    ' Create a new workbook and set a variable to the first sheet. 
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Peter\invoices\"

    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")

    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)

        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName

        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. 
        ' It can span multiple rows.
        Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")

        ' Set the destination range to start at column B and 
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)

        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value

        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count

        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False

        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop

    ' Call AutoFit on the destination sheet so that all 
    ' data is readable.
    SummarySheet.Columns.AutoFit
End Sub

仔细阅读注释，根据你的需要修改特定区域。如果你需要参考，这里是此代码的源代码 -在 Excel 2010 中将多个工作簿中的数据合并到摘要工作簿中。

相关内容