Создать новую электронную таблицу данных из многих книг Excel

Question

Оглядываясь назад, это проблема сбора данных, но я хочу посмотреть, есть ли простой способ сделать это без каждого файла.

У меня есть около 350 отдельных книг Excel. Каждая рабочая тетрадь содержит немного информации, относящейся к одному человеку.

То, что я хочу сделать, - это создать простую электронную таблицу с необходимыми данными, чтобы ее можно было использовать.

Чтобы дать вам идею. A1 перечисляет "Имя" с B1, имеющим имя человека. A2 имеет "Последние 4 SSN", а B2 имеет 4 цифры.

Затем А3 имеет слово "Год", затем А4 "2014 est", А5 - "2013", А6 - «2012 и т.д.» До 2009 года.

Затем в строке 3 от B до N есть разные фрагменты информации.

То, что я хочу сделать, если это возможно, это иметь основную электронную таблицу со столбцами имени, последние 4 ссн, а затем годы для каждой из частей данных. то есть. 2013-Процентный

Это вообще возможно?Я надеюсь это имеет смысл.

Answer 1

Я бы использовал Power Query Add-In для этого. Вы можете запустить запрос из таблицы Excel. Если электронные таблицы находятся в отдельных файлах, у него есть отличная функция - импортировать все файлы в папке Windows за один шаг и добавлять все их данные вместе (при условии, что столбцы файлов совпадают). Если они представляют собой несколько листов в одном файле, вам потребуется несколько запросов, которые можно объединить с помощью команды «Добавить».

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

Существует также команда Unpivot для преобразования данных, хранящихся в нескольких столбцах, в несколько строк - возможно, это решит вашу многолетнюю задачу.

Answer 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.