Одновременный ввод данных на несколько листов в excel
Иногда одни и те же данные одновременно содержатся в нескольких рабочих листах. Чтобы выполнить ввод данных в несколько рабочих листов, можно применить инструмент группировки Excel. Эту группировку можно выполнить стандартными методами Excel или с использованием кода VBA. [1] Встроенный механизм Excel для одновременного размещения данных в нескольких листах — это средство под названием Группа. Оно позволяет группировать листы, связывая их внутри рабочей книги. Чтобы применить средство Группа, щелкните лист, на котором будете вводить данные, а затем, удерживая клавишу Ctrl, щелкните ярлычки с именами рабочих листов, куда должны одновременно вводиться те же данные (рис. 1).
Рис. 1. Три первых листа объединены в группу
Если нужно выбрать несколько листов, расположенных по порядку, можно кликнуть на первый из них, а затем удерживая Shift кликнуть на последний. Когда вы будете вводить данные в любую ячейку на вашем рабочем листе, они автоматически появятся и на остальных сгруппированных листах. Вы можете переходить между листами в группе, при этом группировка не пропадет. Чтобы отменить группировку листов, либо выберите любой лист, не включенный в группировку, либо щелкните правой кнопкой мыши любой ярлычок листа и выберите команду Разгруппировать листы.
Если у вас сгруппировано несколько рабочих листов, то, взглянув на строку заголовка, вы увидите слово Группа в квадратных скобках. Это позволяет понять, что ваши листы объединены в группу. Однако эта надпись не бросается в глаза, поэтому мы советуем разгруппировывать листы сразу же, как только закончите делать то, для чего была необходима группировка.
Хотя это очень простой метод, он означает, что вам необходимо помнить о необходимости группировать и отменять группировку листов, иначе вы можете случайно заменить или стереть нужные данные с другого рабочего листа. Также это означает, что данные будут одновременно вводиться на другом листе независимо от того, на какой ячейке вы находитесь в данный момент. Например, вам может потребоваться одновременный ввод данных, только если вы находитесь в определенном диапазоне ячеек.
Эти трудности можно преодолеть при помощи кода VBA. Чтобы этот код заработал, он должен находиться в частном модуле для объекта Лист (а не в модуле рабочей книги). Ранее я однажды уже использовал такой код (см. Создание указателя листов в книге). Чтобы быстро перейти в частный модуль, щелкните правой кнопкой мыши ярлычок листа и в контекстном меню выберите команду Исходный текст. Для запуска кода, расположенного в частном модуле для объекта Лист, вы сможете использовать одно из событий листа Excel, то есть событие, которое происходит в вашем листе, например, изменение ячейки, выбор диапазона, активация, деактивация и так далее. В указанном выше примере макрос запускался каждый раз при переходе на соответствующий лист.
Первое, что необходимо сделать для обработки группировки, — это выбрать имя для диапазона ячеек, который вы хотите сгруппировать, чтобы данные автоматически отображались на других листах. Я, например, выбрал на Лист3 область С3:F8 и ввел в поле имени MyRange (рис. 2). Чтобы убедитьбся, что диапазону присвоено имя, можно перейти на вкладку Формулы и кликнуть на кнопке Диспетчер имен. В открывшемся окне вы увидите строку с параметрами только что определенного имени диапазона.
Рис. 2. Присвоение диапазону ячеек имени MyRange
Убедиться, что вы находить в частном модуле для объекта Лист можно, посмотрев на заголовок окна модуля, в котором отражаются названия файла и листа (см. выделение на рис. 3). В частный модуль введите код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range( " MyRange " ), Target) Is Nothing Then
Sheets(Array( " Лист3 " , " Лист1 " , " Лист2 " )).Select
Рис. 3. Код VBA для автоматической группировки листов
В этом коде мы использовали имя диапазона MyRange. Измените MyRange на имя диапазона, которое вы используете в своем листе. Кроме того, измените в коде три имени листов на имена листов, которые хотите сгруппировать. Закончив ввод, закройте окно модуля или нажмите сочетание клавиш Alt+Q, чтобы вернуться в Excel. Сохраните книгу Excel с расширением .xlsm (с поддержкой макросов).
Важно помнить, что первым в этом массиве должно стоять имя листа, содержащего этот код, то есть листа, на котором вы будете вводить данные. После того как код создан, каждый раз, когда вы будете выбирать ячейку на Лист3, код будет проверять, принадлежит ли выбранная ячейка диапазону с именем MyRange (С3:F8). Если да, то код будет автоматически группировать нужные рабочие листы. Если нет, он будет разгруппировывать листы, активируя тот лист, на котором вы в данный момент находитесь. Прелесть этого трюка в том, что вручную группировать листы не нужно, и нет риска забыть разгруппировать их.
Вы также можете сделать так, чтобы данные появлялись на других листах, но не в ячейках с теми же адресами. Для этого я определил новый диапазон на Лист6 – Пример2 (В2:Н11). Далее записал следующий код в частный модуль объекта Лист6:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range( " Пример2 " ), Target) Is Nothing Then
With Range( " Пример2 " )
.Copy Destination: =Sheets ( " Лист4 " ).Range ( " A1 " )
.Copy Destination:=Sheets( " Лист5 " ).Range( " D10 " )
Этот код левому верхнему углу диапазона на Лист 6 – В2 поставит в соответствие диапазон на Лист4 с вершиной в А1 и на Лист5 – с вершиной D10. Вводя на Лист6 значения в ячейки диапазона Пример2 (В2:Н11), вы получите аналогичные значения в диапазонах на Лист4 (А1:G10) и Лист5 (D10:J19).
Безусловно, возможности excel очень широки. Большая часть из постоянно используемых инструментов широко освещена в интернете, книгах и на форумах. Также, в большинстве своем, эти возможности в excel размещены перед глазами и интуитивно до них доходишь самостоятельно, изучая ленту и диалоговые окна.
Однако, в excel есть возможности, если о существовании которых не знаешь - вряд ли отыщешь. Они не лежат на поверхности, не так часто используются, но могут быть полезны при решении определенных нетиповых задач. Я бы хотела о некоторых из них рассказать (растягивать 1 лайфхак на 1 статью и лить воду не хочется):
1. Одновременный ввод значений на нескольких листах . Теоретически может возникнуть необходимость одновременного ввода данных в одной книге excel, но на разных листах одновременно. Для этого перед вводом значений необходимо выделить 2 листа с помощью кнопки Ctrl.
В левой нижней части окна Excel при зажатой кнопке Ctrl нажмите на названиях необходимых листов (у меня на картинке выделены Лист 1 и Лист 3), после этого начинайте вводить значения на одном из листов, на втором они также появятся:
Выделение нескольких листов и одновременный ввод значений Выделение нескольких листов и одновременный ввод значенийЧтобы убрать выделение нескольких листов - нажмите на любой другой.
2. Нестандартное автозаполнение ячеек - прогрессия . Когда мы делаем список чего-либо, например последовательности дат, чтобы не вводить все значения можно воспользоваться автозаполнением. В обычном виде это выглядит так: ввели в столбец несколько значений и протянули вниз за черный крестик (он появится, когда вы наведете курсор на правый нижний угол последней в списке ячейки).
Но если нужно нестандартное автозаполнение, например прогрессия, да еще и геометрическая, то можно воспользоваться кнопкой Прогрессия. Вначале в первоначальную ячейку (у нас это J1) введите число, с которого будет начинаться прогрессия, затем переходите на Ленту во вкладку Главная в блоке Редактирование и нажмите Прогрессия:
Часто бывает так, что одни и те же данные одновременно содержатся в нескольких рабочих листах. Можно применить инструмент группировки Excel, чтобы данные в одной рабочей книге можно было одновременно вводить в несколько рабочих листов. Кроме того, мы предлагаем более быстрый и гибкий подход, в котором используется пара строчек кода VBA (Visual Basic for Applications).
Встроенный механизм Excel для одновременного размещения данных в нескольких листах — это средство под названием Группа (Group). Оно позволяет группировать листы, связывая их внутри рабочей книги.
Группировка рабочих листов вручную
Чтобы применить средство Группа (Group) вручную, щелкните лист, на котором будете вводить данные, а затем, удерживая клавишу Ctrl (Shift на Macintosh), щелкните ярлычки с именами рабочих листов, куда должны одновременно вводиться те же данные. Когда вы будете вводить данные в любую ячейку на вашем рабочем листе, они автоматически появятся и на остальных сгруппированных листах. Миссия окончена.
Чтобы отменить группировку листов, либо выберите любой лист, не включенный в группировку, либо щелкните правой кнопкой мыши любой ярлычок листа и выберите команду Разгруппировать листы (Ungroup Sheets).
Хотя это очень простой метод, он означает, что вам необходимо помнить о необходимости группировать и отменять группировку листов, иначе вы можете случайно заменить или стереть нужные данные с другого рабочего листа. Также это означает, что данные будут одновременно вводиться на другом листе независимо от того, на какой ячейке вы находитесь в данный момент. Например, вам может потребоваться одновременный ввод данных, только если вы находитесь в определенном диапазоне ячеек.
Автоматическая группировка рабочих листов
Эти трудности можно преодолеть при помощи очень простого кода VBA. Чтобы этот код заработал, он должен находиться в частном модуле (private module) для объекта Sheet (Лист). Чтобы быстро перейти в частный модуль, правой кнопкой мыши щелкните ярлычок листа и в контекстном меню выберите команду Исходный текст (View Code) . Затем для помещения кода в частный модуль для объекта Лист (Sheet) вы сможете использовать одно из событий листа Excel, то есть событий, которые происходят в вашем листе, например, изменение ячейки, выбор диапазона, активация, деактивация и так далее.
Первое, что необходимо сделать для обработки группировки, — это выбрать имя для диапазона ячеек, который вы хотите сгруппировать, чтобы данные автоматически отображались на других листах. В частный модуль введите код из листинга 1.1.
// Листинг 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. Код автоматической группировки рабочих листов
Важно помнить, что первым в этом массиве должно стоять имя листа, содержащего этот код, то есть листа, на котором вы будете вводить данные. После того как код создан, каждый раз, когда вы будете выбирать ячейку на рабочем листе, код будет проверять, принадлежит ли выбранная ячейка диапазону с именем MyRange. Если да, то код будет автоматически группировать нужные рабочие листы. Если нет, он будет разгруппировывать листы, активируя тот лист, на котором вы в данный момент находитесь. Прелесть этого трюка в том, что вручную группировать листы не нужно, и нет риска забыть разгруппировать их. Этот подход может сэкономить много времени и усилий.
Если вы хотите, чтобы те же данные появлялись на других листах, но не в ячейках с теми же адресами, используйте код из листинга 1.2.
// Листинг 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). Чтобы раскрыть его, выполните действия, описанные ранее в этом трюке.
Нам может потребоваться разработать набор листов с одинаковыми стилями в книгах для разных целей. Например, рабочая тетрадь со всеми листами ежемесячных отчетов о продажах за год или рабочая тетрадь с ежемесячными листами оценки персонала. Для этих листов в определенной книге все они имеют один стиль, поскольку все они работают для одной цели. Поэтому для экономии времени они, как правило, готовят эти рабочие листы заранее для использования в будущем. В этой статье мы покажем вам, как создать несколько листов с одинаковыми данными в Excel.
Вводите одни и те же данные в несколько листов одновременно в Excel
1. Создайте новую книгу, добавьте новые рабочие листы и переименуйте их. Или откройте созданную вами книгу.
2. Выберите рабочие листы.
1). Если вы просто хотите ввести один и тот же дада на нескольких рабочих листах в книге, щелкните вкладку рабочего листа, удерживая Ctrl , а затем щелкните, чтобы выбрать другие листы один за другим.
2). Если вы хотите выбрать все листы в книге, выберите вкладку первого листа, удерживая Shift , а затем щелкните последнее имя листа в книге.
3. После выбора рабочих листов заполните текущий активный рабочий лист необходимыми данными и стилем. В то же время все выбранные рабочие листы заполняются абсолютно одинаковым содержанием.
Создавайте сразу несколько листов с одинаковыми данными с помощью Kutools for Excel
Здесь я представлю Копировать рабочие листы и Переименовать лист коммунальные услуги Kutools for Excel. С помощью этих двух утилит вы можете быстро создать несколько рабочих листов на основе определенного рабочего листа, а затем переименовать созданные рабочие листы с указанными данными в большом количестве.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. После установки Kutools for Excel, нажмите Kutools Plus > Рабочий лист > Копировать рабочие листы.
2. В диалоговом окне «Копировать несколько листов» выберите рабочий лист, который вы хотите скопировать, укажите количество копий, выберите место для размещения листов и, наконец, щелкните значок OK кнопку.
3. Нажмите OK в следующем всплывающем диалоговом окне. Теперь рабочие листы с точно такими же данными выбранного рабочего листа успешно скопированы.
4. Теперь вам нужно переименовать эти листы. Нажмите Kutools Plus > Рабочий лист > Переименовать листы.
5. в Переименовать несколько листов диалоговом окне, вам необходимо проверить скопированные рабочие листы, которые необходимо переименовать в Worksheets коробка; Выбрать Заменить исходное имя листа вариант в Параметры переименования раздел; проверить Из определенного диапазона вариант и выберите диапазон ячеек с содержимым, которое вы будете использовать в качестве имен листов, и, наконец, нажмите кнопку OK кнопку.
Затем все выбранные листы сразу переименовываются.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: