Консолидация данных из нескольких листов с одинаковыми столбцами, но с разным количеством строк в Excel

Question

У меня есть книга Excel с несколькими листами. Все они имеют одинаковые имена столбцов. Тем не менее, количество строк отличается от листа к листу. Я хочу создать обзорный лист, где я могу видеть все данные с каждого отдельного листа. Я мог бы пойти на каждом листе просто скопировать и вставить данные из второй строки в последнюю строку с данными на каждом листе. Но я думаю, что есть более простой способ сделать это?

Спасибо за вашу помощь!

Answer 1

Это решение с использованием VBA:

Public Sub overview()
    resultsheet = "Overview"
    Dim wkb As Workbook
    Dim wks, wks1 As Worksheet
    Set wkb = ThisWorkbook
    On Error GoTo SheetError:
        Set wks1 = wkb.Sheets(resultsheet)
    destrow = 1
    totalcolumn = 1
    totalwks = wkb.Sheets.Count
    For i = 1 To totalwks
        Set wks = wkb.Sheets(i) 'Iterate over all sheets
        wksname = wks.Name
        If wksname <> resultsheet Then 'Exclude the overview sheet
            rowdata = True
            columndata = True
            thisrow = 2
            thiscolumn = 1
            totalempty = 0
            While rowdata = True
                If i = 1 Then 'First sheet section
                    a = wks.Cells(thisrow - 1, thiscolumn)
                    If a <> "" Then
                        wks1.Cells(destrow, thiscolumn) = a
                        thiscolumn = thiscolumn + 1
                    Else
                        If thisrow = 2 Then
                            totalcolumn = thiscolumn
                        End If
                        totalempty = totalempty + 1
                        If totalempty = totalcolumn Then
                            rowdata = False
                        End If
                        If thiscolumn = totalcolumn Then
                            thisrow = thisrow + 1
                            thiscolumn = 1
                            destrow = destrow + 1
                            totalempty = 0
                        End If
                    End If
                Else ' Any other Sheet section
                    a = wks.Cells(thisrow, thiscolumn)
                    If a <> "" Then
                        rowdata = True
                        wks1.Cells(destrow, thiscolumn) = a
                        thiscolumn = thiscolumn + 1
                    Else
                        totalempty = totalempty + 1
                        If totalempty = totalcolumn Then
                            rowdata = False
                        End If
                        If thiscolumn = totalcolumn Then
                            thisrow = thisrow + 1
                            thiscolumn = 1
                            destrow = destrow + 1
                            totalempty = 0
                        End If


                    End If
                End If
            Wend

        End If
    Next i
    Exit Sub
SheetError:
    If Err.Number = 9 Then
        createwks = MsgBox("Worksheet " & resultsheet & " doesn't exist" & vbCrLf & "Do you want to create it?", vbYesNo, Error)
    End If
    If createwks = 6 Then
        Set wks1 = wkb.Worksheets.Add(After:=Worksheets(Worksheets.Count))
        wks1.Name = resultsheet
        Resume
    End If
End Sub

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

Answer 2

Я бы сделал это вручную :)

Вы можете взглянуть на это решение (консолидировать данные по позициям), вы увидите, что это аналогичные накладные расходы:

На каждом листе, который содержит данные, которые вы хотите консолидировать, настройте данные, выполнив следующие действия:

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

Поместите каждый диапазон на отдельную рабочую таблицу, но не помещайте никакие диапазоны на рабочую таблицу, в которую вы планируете поместить консолидацию.

Убедитесь, что каждый диапазон имеет одинаковое расположение.

В основной рабочей таблице щелкните верхнюю левую ячейку области, в которой вы хотите отобразить консолидированные данные.

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

... и так долго ...

Answer 3

Я не вижу никакого преимущества в этом. Я не могу сказать, что вы должны сделать, но если вы разделили данные на несколько листов, у вас должна быть причина для этого. Если у вас есть причина, зачем объединяться? Это не имеет никакого смысла для меня. Хорошо, что я хотел бы сделать, так это то, почему я должен видеть в первую очередь все вместе? Это просто затрудняет поиск чего-либо, подождите, да, только если вы хотите использовать функцию редактирования, чтобы найти конкретные данные на нем. Но нужно ли вам делать это много раз? Я бы, если есть что-нибудь связанное, сделал бы, например, СУММУ или другое резюме на каждом листе, и на новом листе сделал бы перекрестную ссылку для этого поля в каждой отдельной электронной таблице. Чтобы объединить данные, не копируя все данные только на один лист. Если вы хороший программист, первое решение для копирования может помочь, но это не так просто, вы увидите. И, наконец, что не менее важно, если каждый лист увеличивается в количестве строк, убедитесь, что сумма или любой другой элемент находятся на самом верхнем и правом краях, чтобы лист увеличивался, но данные консолидации не затрагивались.