Одновременное изменение данных в нескольких листах в excel

Обновлено: 06.07.2024

Я не знаю, можно ли решить мою проблему не касаясь VBA, поэтому пишу в эту ветку.
Если я ошибся - пожалуйста, перенесите тему.

Есть Excel файл, в нём несколько листов со списком работников.
Если конкретнее, то листов 12 (по месяцам).
На каждом листе идёт оценка работников за текущий месяц.
Если работник увольняется или приходит новый - необходимо удалить или же добавить новую строчку.

Как сделать так, чтобы изменяя список на первом листе, списки бы автоматически менялись на всех листах сразу?
Т,е. удалил на первом, а удалилось на всех 12-ти сразу. Ну или добавил вниз новую строчку, ввёл туда имя и фамилию работника, а добавилось сразу на всех листах сразу.

Возможно ли сделать такое не прибегая к VBA?
Как вообще более логично решить такую вот задачу? Просто вставлять новые строки на 12-ти листах не очень прикольно, хотелось бы оптимизировать!

Большое спасибо заранее!

Большое спасибо заранее! KIMVSR

Большое спасибо заранее! Автор - KIMVSR
Дата добавления - 25.01.2016 в 19:42

Допустим у Вас есть книга с n-ным количеством однотипных листов. Иногда приходится проделывать в каждом листе одну и ту же операцию(к примеру написание заголовков). Чаще всего в таких случаях проделывается следующая операция: на одном листе меняются заголовки, затем копируются и поочередно вставляются на остальные листа. Это долго, нудно и неинтересно. Благо разработчики Microsoft предвидели подобные мучения и снабдили Excel средством для более быстрого осуществления этой задачи.

Итак, для произведения одинаковых изменений сразу на нескольких листах все, что необходимо сделать, это выделить все необходимые листы с зажатой клавишей Shift или Ctrl(зависит от расположения листов, данные в которых надо менять. Ctrl позволяет выделить несмежные листы(скажем через один), а с Shift выделяются все листы от первого выделенного до последнего). Выделили. Теперь все действия, производимые на активном(видимом) листе в точности повторяется на других выделенных листах. И что не менее важно - если прописать в активном листе формулу, то ссылки на других листах будут у каждого свои, т.е. ссылаться формулы будут на тот лист, в котором написаны(если в формуле не указаны явно ссылки на другие листы). То же самое и с копированием. Если на одном листе в ячейке А1 написано Дом, а на другом Холм, то при копировании ячейки А1 на активном листе и вставки её в ячейку А2 , то на каждом листе в ячейку А2 будет скопировано своё значение - Дом и Холм соответственно.

Подобная работа с листами еще называется работой с группой листов. Определить, что на данный момент выделена группа листов(и изменения будут производиться на всех этих листах) очень просто - в заголовке Excel будет отображен текст: [Группа] [Groupe]

Разгруппировать такие листы еще проще:

если в группу объединены не все листы - выделить любой лист вне группы
если в группе все листы книги - выделить любой лист, который на данный момент не является активным

Так же см.:
[[Как одновременно просматривать информацию с нескольких листов/диапазонов?]]

Безусловно, возможности 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).

Если у вас сгруппировано несколько рабочих листов, то, взглянув на строку заголовка, вы увидите слово Группа (Group) в квадратных скобках. Это позволяет понять, что ваши листы объединены в группу. Однако если вы не обладаете орлиным взглядом и острым, как лезвие бритвы, умом, весьма вероятно, что вы не заметите это слово или же просто забудете, что сгруппировали несколько листов. По этой причине мы советуем разгруппировывать листы сразу же, как только закончите делать то, для чего была необходима группировка.
Хотя это очень простой метод, он означает, что вам необходимо помнить о необходимости группировать и отменять группировку листов, иначе вы можете случайно заменить или стереть нужные данные с другого рабочего листа. Также это означает, что данные будут одновременно вводиться на другом листе независимо от того, на какой ячейке вы находитесь в данный момент. Например, вам может потребоваться одновременный ввод данных, только если вы находитесь в определенном диапазоне ячеек.

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

Эти трудности можно преодолеть при помощи очень простого кода 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). Чтобы раскрыть его, выполните действия, описанные ранее в этом трюке.

Читайте также: