Как собрать данные с нескольких листов в один

RSS

bylanovandrej

Пользователь

Сообщений: 22 Регистрация: 27.08.2016

26.08.2017 12:01:18

Всем привет. Может кто поможет, не могу разобраться, есть макрос:

Код

Sub Обновить_сводную()
Dim Sht As Worksheet
Dim Wb As Workbook
Dim i As Long
Dim iLastRow_B As Long
Dim iLastRow_Ai As Long

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
    
Set Wb = ThisWorkbook
Sheets("Сводная").Cells.Clear 'очищаем лист "Сводная"
Sheets("Сводная").Range("A1") = "Оценка"
Sheets("Сводная").Range("B1") = "ФИО сотрудника"
Sheets("Сводная").Range("C1") = "Старший"
Sheets("Сводная").Range("D1") = "Группа"
Sheets("Сводная").Range("E1") = "Дата оценки"
Sheets("Сводная").Range("F1") = "Номер звонка"
Sheets("Сводная").Range("G1") = "Пометка на звонок"
Sheets("Сводная").Range("H1") = "Проф. Навыки"
Sheets("Сводная").Range("I1") = "Навыки ведения диалога"
Sheets("Сводная").Range("J1") = "Общая оценка за звонок"
Sheets("Сводная").Range("K1") = "Тематика (1 уровень)"
Sheets("Сводная").Range("L1") = "Тематика (2 уровень)"
Sheets("Сводная").Range("M1") = "Тематика (3 уровень)"
Sheets("Сводная").Range("N1") = "Основная зона роста (1-ый уровень)"
Sheets("Сводная").Range("O1") = "Основная зона роста (2-ый уровень)"
Sheets("Сводная").Range("P1") = "Доп. зона роста (1-ый уровень)"
Sheets("Сводная").Range("Q1") = "Доп. зона роста (2-ый уровень)"
Sheets("Сводная").Range("R1") = "Вес нарушения Основной зоны"
Sheets("Сводная").Range("S1") = "Вес нарушения доп. Зоны"
Sheets("Сводная").Range("T1") = "ст"
Sheets("Сводная").Range("U1") = "Неделя"
Sheets("Сводная").Range("V1") = "Месяц"
Sheets("Сводная").Range("W1") = "Год"
Sheets("Сводная").Range("X1") = "Ошибка"
Sheets("Сводная").Range("Y1") = "Отдел"
Sheets("Сводная").Range("Z1") = "Кодировка"
i = 1
Set Sht = Wb.Sheets(i)
For Each Sht In Worksheets
    If Sht.Name <> "Сводная" Then
      iLastRow_B = Cells(Rows.Count, 2).End(xlUp).Row
      iLastRow_Ai = Wb.Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
      Wb.Sheets(i).Range("A2:Z" & iLastRow_Ai).Copy Cells(iLastRow_B + 1, 1)
    End If
    i = i + 1
    Next
        
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Макрос собирает с нескольких листов данные в один лист. Справляется отлично, но как сделать две доработки:

1) Собирать данные не со всех листов в книге, а только с определенных, например: в книге 5 листов, с названиями Лист1, Лист2, Лист3, Лист4, но нужно собрать данные только с Лист1 и Лист2.

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

Может кто знает как реализовать.

AndreTM

Пользователь

Сообщений: 454 Регистрация: 02.05.2013

26.08.2017 13:19:58

1) Расширьте проверку имени листа, с которого берете данные (вы же проверяете, чтобы с листа "Сводная" данные не брались? - ну, замените условие на такое, чтобы проверялось "такие то листы". Ну или вообще перебирайте только нужные листы.
2) "Добавить в Сводную только добавленную на исходные листы информацию" - это то же самое, что и "Заново собрать сводную из всей информации". Если только вы не "заменили" информацию в исходных листах (например, удалили какие-то строки).

Код

Sub Обновить_сводную()

Dim Sht As Worksheet, Shs As Worksheet
Dim Wb As Workbook
Dim i As Long
Dim iLastRow_B As Long
Dim iLastRow_Ai As Long
 
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
     
    Set Wb = ThisWorkbook
    Set Shs = Wb.Sheets("Сводная")
    Shs.Cells.Clear 'очищаем лист "Сводная"
    Shs.Range("A1:Z1") = Array("Оценка", "ФИО сотрудника", "Старший", "Группа", "Дата оценки", _
        "Номер звонка", "Пометка на звонок", "Проф. Навыки", "Навыки ведения диалога", "Общая оценка за звонок", _
        "Тематика (1 уровень)", "Тематика (2 уровень)""Тематика (3 уровень)""Основная зона роста (1-ый уровень)", _
        "Основная зона роста (2-ый уровень)", "Доп. зона роста (1-ый уровень)", "Доп. зона роста (2-ый уровень)", _
        "Вес нарушения Основной зоны", "Вес нарушения доп. Зоны", "ст", "Неделя", "Месяц", "Год", _
        "Ошибка", "Отдел", "Кодировка")
    
    For Each nm In Array("Лист1", "Лист2")
        Set Sht = Wb.Sheets(nm)
        iLastRow_B = Shs.Cells(Rows.Count, 2).End(xlUp).Row
        iLastRow_Ai = Sht.Cells(Rows.Count, 1).End(xlUp).Row
        Sht.Range("A2:Z" & iLastRow_Ai).Copy Shs.Cells(iLastRow_B + 1, 1)
    Next
         
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic

End With

End Sub

Изменено: AndreTM - 26.08.2017 13:40:15

bylanovandrej

Пользователь

Сообщений: 22 Регистрация: 27.08.2016

26.08.2017 14:16:48

AndreTM, что касается по коду, он выдает ошибку какую-то при запуске и собирает данные только с первого листа: с Лист1. В принципе, с вашей помощью я немного доработал код, поэтому по вопросу:

Цитата
1) Собирать данные не со всех листов в книге, а только с определенных, например: в книге 5 листов, с названиями Лист1, Лист2, Лист3, Лист4, но нужно собрать данные только с Лист1 и Лист2.

в принцепе вопрос закрыт, но вот что касается второго момента, ваш ответ мне всё таки непонятен,

смотрите, у меня есть допустим 3 листа, на которые ежедневно добавляются несколько строк. Если запускать макрос который я изначально прописывал, то он при каждом запуске этого макроса будет стирать все данные со сводного листа и заново собирать с Лист1, Лист2 и Лист3 информацию. В принципе, это не было бы проблемой, если бы строчек на Лист1, Лист2 и Лист3 было бы ну скажем не больше 100 на каждом листе, но если таких строчек будет по 50-100 тыс. то работа макроса может затянуться, с учетом того, что уже добавленные строки на Лист1, Лист2 и Лист3 не меняются, было бы куда практичнее, если бы при запуске макроса собирались только новые добавленные на Лист1, Лист2 и Лист3 данные.

AndreTM

Пользователь

Сообщений: 454 Регистрация: 02.05.2013

27.08.2017 01:08:47

Цитата
bylanovandrej написал: что касается по коду, он выдает ошибку какую-то при запуске и собирает данные только с первого листа: с Лист1

И где этот код, и эта ошибка , и этот файл?
Без образца - это ни о чём.

Цитата

bylanovandrej написал:
если таких строчек будет по 50-100 тыс. то работа макроса может затянуться, с учетом того, что уже добавленные строки на Лист1, Лист2 и Лист3 не меняются, было бы куда практичнее, если бы при запуске макроса собирались только новые добавленные

Вы так уверены? А вы учитываете время, что сначала время уйдет на "сверку каждого из X листов по 50000 строк - со сводной" на предмет того, "что есть, что было"?
Потому что если вы как-то отмечаете отдельно строки, что добавлены - то это одно дело. А если нет - то уж извините, опыт яро протестует против ваших "рассуждений"

Причем я же вам тонко намекнул на то, что данные из листов-источников могут быть удалены - и что даст вам добавление в свод только "добавленных" на листы строк? Из свода же удаленные строки - не удалятся сами

Ну а уж эффективность алгоритма сбора сводной с точки зрения быстродействия - это вопрос отдельный. Почему вы считаете, что сведение данных должно выполняться с той скоростью, что вы заложили в ваш детский велосипед? Используете неэффективный алгоритм и средства - и вместо оптимизации начинаете придумывать костыли.
Одно только копирование с листа на лист через массив в памяти - повысит быстродействие на порядки. Не говоря уже о применении специализированных инструментов для обработки данных (того же PowerQuery или обработки SQL-запросами).

Изменено: AndreTM - 27.08.2017 01:13:41

bylanovandrej Пользователь Сообщений: 22 Регистрация: 27.08.2016	#5 27.08.2017 08:53:15 AndreTM, всё, разобрался, и с ошибкой, а точнее это не ошибка, а я косяк, сделал сбор данных с несуществующего листа, а что касается второго вопроса, да, соглашусь, на практике опробовал вставить безумное количество строк на каждую из страниц, код всё равно быстро собирает, так что второй вопрос в принципе тоже отпал. Огромнейшее вам спасибо!

Читают тему