Трюк №2. Одновременный ввод данных в несколько рабочих листов

Часто бывает так, что одни и те же данные одновременно содержатся в нескольких рабочих листах. Можно применить инструмент группировки Excel, чтобы данные в одной рабочей книге можно было одновременно вводить в несколько рабочих листов. Кроме того, мы предлагаем более быстрый и гибкий подход, в котором используется пара строчек кода VBA (Visual Basic for Applications).

Встроенный механизм Excel для одновременного размещения данных в нескольких листах — это средство под названием Группа (Group). Оно позволяет группировать листы, связывая их внутри рабочей книги.

Группировка рабочих листов вручную

Чтобы применить средство Группа (Group) вручную, щелкните лист, на котором будете вводить данные, а затем, удерживая клавишу Ctrl (Shift на Macintosh), щелкните ярлычки с именами рабочих листов, куда должны одновременно вводиться те же данные. Когда вы будете вводить данные в любую ячейку на вашем рабочем листе, они автоматически появятся и на остальных сгруппированных листах. Миссия окончена.

Чтобы отменить группировку листов, либо выберите любой лист, не включенный в группировку, либо щелкните правой кнопкой мыши любой ярлычок листа и выберите команду Разгруппировать листы (Ungroup Sheets).
[stextbox id=»warning»]Если у вас сгруппировано несколько рабочих листов, то, взглянув на строку заголовка, вы увидите слово Группа (Group) в квадратных скобках. Это позволяет понять, что ваши листы объединены в группу. Однако если вы не обладаете орлиным взглядом и острым, как лезвие бритвы, умом, весьма вероятно, что вы не заметите это слово или же просто забудете, что сгруппировали несколько листов. По этой причине мы советуем разгруппировывать листы сразу же, как только закончите делать то, для чего была необходима группировка.[/stextbox]
Хотя это очень простой метод, он означает, что вам необходимо помнить о необходимости группировать и отменять группировку листов, иначе вы можете случайно заменить или стереть нужные данные с другого рабочего листа. Также это означает, что данные будут одновременно вводиться на другом листе независимо от того, на какой ячейке вы находитесь в данный момент. Например, вам может потребоваться одновременный ввод данных, только если вы находитесь в определенном диапазоне ячеек.

Автоматическая группировка рабочих листов

Эти трудности можно преодолеть при помощи очень простого кода VBA. Чтобы этот код заработал, он должен находиться в частном модуле (private module) для объекта Sheet (Лист). Чтобы быстро перейти в частный модуль, правой кнопкой мыши щелкните ярлычок листа и в контекстном меню выберите команду Исходный текст (View Code). Затем для помещения кода в частный модуль для объекта Лист (Sheet) вы сможете использовать одно из событий листа Excel, то есть событий, которые происходят в вашем листе, например, изменение ячейки, выбор диапазона, активация, деактивация и так далее.

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

1
2
3
4
5
6
7
8
// Листинг 1.1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("MyRange"), Target) Is Nothing Then
        Sheets(Array("Sheet5", "Sheet3", "Sheet1")).Select
    Else
       Me.Select
    End If
End Sub

В этом коде мы использовали имя диапазона MyRange. (Если вы не знакомы с именованными диапазонами, обратитесь к разделу «Адресация данных по имени» [Трюк № 39].) Измените MyRange на имя диапазона, которое вы используете в своем листе. Кроме того, измените в коде три имени листов на имена листов, которые хотите сгруппировать (рис. 1.5). Закончив, закройте окно модуля или нажмите сочетание клавиш Alt/Apple+Q, чтобы вернуться в Excel.

Рис. 1.5. Код автоматической группировки рабочих листов

Рис. 1.5. Код автоматической группировки рабочих листов

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

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

1
2
3
4
5
6
7
8
9
// Листинг 1.2
Private Sub worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("MyRange"), Target) Is Nothing Then
       With Range("MyRange")
         .Copy Destination:=Sheets("Sheet3").Range("A1")
         .Copy Destination:=Sheets("Sheet1").Range("D10")
    End With
  End If
End Sub

Этот код также необходимо записать в частный модуль объекта Лист (Sheet). Чтобы раскрыть его, выполните действия, описанные ранее в этом трюке.

Top