Одновременное изменение данных в нескольких листах в excel
Всем привет! В Excel есть множество способов облегчить и ускорить свою работу, прибегая к некоторым уловкам и лайфхакам. Об этих моментах не рассказывается подробно в инструкциях и встроенной справке, поэтому по ходу публикаций о стандартной работе в программе, я также буду выкладывать разных хитрости и трюки )) Если вы считаете, что для вас это будет полезно, следите за обновлениями!
Итак. Все знают, что такое рабочие книги в Excel — это модельное представление. Большинство знают, что много открытых одновременно рабочих книг, могут вызывать большое количество загвоздок и помех. Особенно это касается если необходимо работать со всеми сразу. Потому первый сегодняшний трюк о том, как облегчить ваши страдания при работе с множеством открытых окон рабочих книг.
Чтобы одновременно отобразить на экране несколько рабочих книг, в любой из открытых книг выберите Окно → Упорядочить все . Если установлен флажок «Только окна текущей книги» уберите его, а затем установите нужный переключатель и щелкните на кнопке ОК.
Если выбрать отметку Рядом , ваши книги будут располагаться рядом черепицей.
Выбрав отметку Сверху вниз , вы расположите книги друг над другом.
Выберите отметку Слева направо , чтобы расположить книги поочередно, друг с другом.
Отметка Каскадом позволит расположить все книги поверх друг друга.
Расположив книги удобнее, вы легко сможете копировать, вставлять, перетаскивать и изменять в них данные из одной книги в другую, если это нельзя сделать другими способами типа формул.
Если вы хотите повторить созданное представление, то можно сохранить его как рабочую область. Для этого выберите команду Файл → Сохранить рабочую область . Файл будет сохранен с расширением .xlw, а не стандартным .xlsx. Если вы захотите позже открыть те же файлы, откройте файл .xlw , и ранее сохраненное представление чудесным образом будет восстановлено для всех книг.
Затратив немного времени на настройку личных представлений для задач, требующих одновременного открытия нескольких рабочих книг, вы со временем убедитесь, что справляться с этими задачами станет легче. Для разных задач можно конструировать разные представления, в зависимости от типа задачи или вашего самочувствия в конкретный день.
На этом у меня всё. Если вам понравился сегодняшний трюк, ставьте лайки и подписывайтесь на канал чтобы не пропустить еще более интересных материалов )) Спасибо!
В Excel можно ввести одинаковые данные на несколько таблиц без повторного ввода и копирования и вставки текста на каждый из них. Например, вы хотите поместить один и тот же текст заголовка на все книги. Один из способов сделать это — ввести текст на одном из них, а затем скопировать и ввести его на другие. Если у вас несколько таблиц, это может быть очень утомительным.
Для этого проще всего использовать клавишу CTRL для группировки. При группировке листов операции, выполняемые на одном листе, автоматически будут выполняться на других листах.
Запустите Excel. Появится новая пустая книга.
Вы можете создать сколько угодно рабочих таблиц.
Нажмите и удерживайте клавишу CTRL, а затем выберите Лист1, Лист2и так далее, пока не завершите выбор всех листов.
При этом группы будут временно группться. В заголовке вы увидите имя книги и слово [Группа].
Щелкните ячейку A1 на листе Лист1 и введите:
Эти данные будут отображаться на каждом листе.
СОВЕТ. Чтобы одновременно врезать данные на несколько листов, сначала в одном из них в документ, а затем выберите диапазон, содержащий данные, которые вы хотите в нее врезать, и воспользуйтесь функцией Заливка >на разных листах.Значения будут заполнены на всех выбранных листах.
Щелкните Лист2 и обратите внимание, что текст, который вы только что ввести на листе Лист1, также отображается в ячейке A1 листа 2 и на всех остальных листах.
Совет: Чтобы разгруппировать книги, просто дважды щелкните любой из них.
Запустите Excel. Появится новая пустая книга.
В нижней части экрана щелкните вкладку Вставка таблицы.
Вы можете создать сколько угодно рабочих таблиц.
Нажмите и удерживайте клавишу CTRL, а затем выберите Лист1, Лист2и так далее, пока не завершите выбор всех листов.
При этом группы будут временно группться. В заголовке вы увидите имя книги и слово [Группа].
Щелкните ячейку A1 на листе Лист1 и введите:
Эти данные будут отображаться на каждом листе.
Щелкните Лист2 и обратите внимание, что текст, который вы только что ввести на листе Лист1, также отображается в ячейке A1 листа 2 и на всех остальных листах.
Совет: Чтобы разгруппировать книги, просто дважды щелкните любой из них.
Допустим у Вас есть книга с n-ным количеством однотипных листов. Иногда приходится проделывать в каждом листе одну и ту же операцию(к примеру написание заголовков). Чаще всего в таких случаях проделывается следующая операция: на одном листе меняются заголовки, затем копируются и поочередно вставляются на остальные листа. Это долго, нудно и неинтересно. Благо разработчики Microsoft предвидели подобные мучения и снабдили Excel средством для более быстрого осуществления этой задачи.
Итак, для произведения одинаковых изменений сразу на нескольких листах все, что необходимо сделать, это выделить все необходимые листы с зажатой клавишей Shift или Ctrl(зависит от расположения листов, данные в которых надо менять. Ctrl позволяет выделить несмежные листы(скажем через один), а с Shift выделяются все листы от первого выделенного до последнего). Выделили. Теперь все действия, производимые на активном(видимом) листе в точности повторяется на других выделенных листах. И что не менее важно - если прописать в активном листе формулу, то ссылки на других листах будут у каждого свои, т.е. ссылаться формулы будут на тот лист, в котором написаны(если в формуле не указаны явно ссылки на другие листы). То же самое и с копированием. Если на одном листе в ячейке А1 написано Дом, а на другом Холм, то при копировании ячейки А1 на активном листе и вставки её в ячейку А2 , то на каждом листе в ячейку А2 будет скопировано своё значение - Дом и Холм соответственно.
Подобная работа с листами еще называется работой с группой листов. Определить, что на данный момент выделена группа листов(и изменения будут производиться на всех этих листах) очень просто - в заголовке Excel будет отображен текст: [Группа] [Groupe]
Разгруппировать такие листы еще проще:
- если в группу объединены не все листы - выделить любой лист вне группы
- если в группе все листы книги - выделить любой лист, который на данный момент не является активным
Так же см.:
[[Как одновременно просматривать информацию с нескольких листов/диапазонов?]]
Решение, описанное в данной статье поможет просматривать информацию одновременно сразу на нескольких листах(диапазонах) книги. К примеру имеется книга Excel с тремя листами. На каждом листе свои данные: на листе "Факт" в диапазоне A1:C25 заносятся данные по доходам и расходам за последние два года. На листе "План" в диапазоне B2:C13 в зависимости от данных на листе "Факт" рассчитываются доходы и расходы на будущий год. На листе "Прибыль" в зависимости от данных листов План и Факт рассчитывается планируемая прибыль на будущий месяц. И если необходимо видеть результаты вычислений во всех этих диапазонах в зависимости от изменений на листе Факт, то придется либо переходить с одного листа на другой, либо воспользоваться предлагаемым ниже методом.
Все расчеты в приложенном файле носят показательный характер и не могут быть использованы для расчета реального прогноза доходов и прибыли
Для начала нам потребуется добавить одну команду на Панель Быстрого Доступа Excel - команда Камера. Как это сделать:
После того, как мы вынесли на панель эту команду остается дело за малым. На примере диапазона А1:А10 на Лист2 :
- Выделяем диапазон B2:C13 на листе "План"
- Жмем значок Камера на панели. Курсор мыши приобретет вид тоненького крестика
- Переходим обратно на лист " Факт " и рисуем этим крестиком область, примерно того же размера, что и диапазон B2:C13 или просто щелкаем левой кнопкой мыши на любой ячейке листа
Теперь мы видим на листе в выделенной области объект, отображающий всю информацию из "снятого" диапазона листа План . Эта информация отображается в режиме он-лайн. Т.е. если на листе " План " в "снятой" области произойдут изменения - то они отобразятся и в данном объекте. Повторяем эти действия для всех необходимых диапазонов.
Так же подобное можно создать макросом. Как ни странно, но макрорекордер не записывает действие создания инструмента Камера. А между тем код создания весьма прост:
Sub Create_Camera() Sheets("План").Range("B2:C13").Copy 'диапазон, снимок которого надо сделать ActiveSheet.Pictures.Paste(Link:=True).Select 'вставляем на тот же лист, начиная с выделенной ячейки End Sub
Запускать код надо с того листа, на который необходимо поместить объект Камера. Сам объект будет вставлен в активную ячейку этого листа. А данные для просмотра берутся из диапазона "B2:C13" листа "План" .
Чтобы самим указать, в какую ячейку вставить объект Камера, достаточно перед вставкой активировать нужную ячейку и нужный лист, а в коде указать имя листа, диапазон с которого надо отслеживать, и сам диапазон:
Sub Create_Camera() Sheets("План").Range("B2:C13").Copy 'применяем к диапазону "B2:C13" на "План" 'Sheets("Факт").Activate 'если надо принудительно вставить снимок в другой лист '[E2].Select 'если надо вставить в конкретную ячейку - E2 ActiveSheet.Pictures.Paste(Link:=True).Select 'вставляем снимок в активную ячейку текущего листа End Sub
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще. Меньше
Важно: Общие книги ранее использовались для организации совместной работы с книгой. Эта функция имеет множество ограничений и была заменена функцией совместного редактирования. Excel совместное Приложения Microsoft 365 Excel для настольных ПК и Excel для подписчиков Microsoft 365 браузера.
Включение общих книг
Прежде чем продолжить, вы должны быть уверены, что хотите использовать этот способ. Общие книги имеют ограничения, и в частности, их невозможно редактировать с помощью Excel в Интернете. Поэтому мы настоятельно рекомендуем использовать вместо общих книг совместное авторство.
Создайте новую книгу или откройте существующую. Затем добавьте ее в сетевую папку. Например, поместите его в расположение, например \\server_name\folder_name. Не сохраняйте файл в OneDrive или на SharePoint. Если вы хотите сохранить ее в эти расположения, используйте совместное редактирование.
На вкладке Рецензирование нажмите кнопку Общий доступ к книге.
Имейте в виду, что в последних версиях Excel эта кнопка скрыта. Ниже описано, как ее отобразить.
На вкладке Правка установите флажок Разрешить изменять файл нескольким пользователям одновременно.
На вкладке Подробнее выберите параметры для отслеживания и обновления изменений, а затем нажмите кнопку ОК.
Если книга новая, введите ее имя в поле Имя файла. Если используется существующая книга, нажмите кнопку ОК, чтобы сохранить ее.
Если книга содержит ссылки на другие книги и документы, проверьте их и исправьте все неработающие ссылки.
В меню Файл нажмите кнопку Сохранить.
Когда все будет готово, в верхней части окна Excel рядом с именем файла появится надпись — общая.
Дополнительные сведения
Имейте в виду, что общие книги — это устаревший метод предоставления общего доступа. Поэтому при его использовании не поддерживаются различные возможности и действия. В таблице ниже приведн неполный список функций, которые не поддерживаются.
Неподдерживаемые возможности
Неподдерживаемые действия
Создание и вставка таблиц
Вставка и удаление групп ячеек
Добавление и изменение условного форматирования
Добавление или изменение проверки данных
Объединение ячеек и разделение объединенных ячеек
Создание и изменение диаграмм или отчетов сводных диаграмм
Сортировка и фильтрация по формату
Вставка или изменение рисунков и других объектов
Использование инструментов рисования
Вставка и редактирование гиперссылок
Назначение, изменение или удаление паролей
Создание, изменение и просмотр сценариев
Защита листов или книг и снятие защиты
Автоматическая вставка промежуточных итогов
Группировка или структурирование данных
Создание таблиц данных
Написание, запись, изменение, просмотр и назначение макросов
Создание и изменение отчетов сводных таблиц
Изменение или удаление формул массива
Создание и применение срезов
Добавление, переименование или удаление карт XML
Создание и изменение спарклайнов
Сопоставление ячеек с XML-элементами
Добавление или изменение листов диалогов Microsoft Excel 4
Использование области задач "XML-источник", панели инструментов XML или XML-команд в меню "Данные"
Использование формы данных для добавления новых данных
Добавление цепочки комментариев
Редактирование и удаление цепочки комментариев
Перед отключением пользователей убедитесь, что они завершили работу с этой книгой. При удалении активных пользователей будут потеряны все их несохраненные изменения.
На вкладке Рецензирование нажмите кнопку Общий доступ к книге.
Имейте в виду, что в последних версиях Excel эта кнопка скрыта. Ниже описано, как ее отобразить.
На вкладке Правка в списке Файл открыт следующими пользователями просмотрите имена пользователей.
Выберите имя пользователя, которого вы хотите отключить и нажмите кнопку Удалить пользователя. Хотя данное действие отключает пользователя от книги, это не мешает ему снова начать ее редактирование.
Для удаления личных параметров просмотра удаленного пользователя щелкните Вид > Представления и удалите представления других пользователей.
Прежде чем отключить эту функцию, может потребоваться создать копию журнала изменений. Убедитесь, что все пользователи завершили свою работу.
На вкладке Рецензирование нажмите кнопку Отслеживание изменений и выберите команду Выделить исправления.
Имейте в виду, что в последних версиях Excel эта кнопка скрыта. Ниже описано, как ее отобразить.
В списке по времени выберите вариант Все.
Снимите флажки пользователем и в диапазоне.
Установите флажок Вносить изменения на отдельный лист, а затем нажмите кнопку ОК.
Теперь можно распечатать лист журнала или скопировать его и вставить в другую книгу.
На вкладке Рецензирование нажмите кнопку Общий доступ к книге.
Имейте в виду, что в последних версиях Excel эта кнопка скрыта. Ниже описано, как ее отобразить.
На вкладке Правка убедитесь, что вы — единственный пользователь, находящийся в списке Файл открыт следующими пользователями.
Снимите флажок Разрешить изменять файл нескольким пользователям одновременно. Если этот флажок недоступен, необходимо сначала отменить защиту книги. Для этого сделайте следующее:
На вкладке Рецензирование в группе Изменения нажмите кнопку Снять защиту общей книги.
Если будет предложено, введите пароль, а затем нажмите кнопку ОК.
На вкладке Рецензирование в группе Изменения нажмите кнопку Доступ к книге.
На вкладке Правка снимите флажок Разрешить изменять файл нескольким пользователям одновременно.
Включение общих книг
Прежде чем продолжить, вы должны быть уверены, что хотите использовать этот способ. Общие книги имеют ограничения, поэтому мы настоятельно рекомендуем использовать вместо них совместное редактирование.
Создайте новую книгу или откройте существующую. Затем добавьте ее в сетевую папку. Не сохраняйте файл в OneDrive или на SharePoint. Если вы хотите сохранить ее в эти расположения, используйте совместное редактирование.
На вкладке Рецензирование нажмите кнопку Общий доступ к книге.
На вкладке Правка установите флажок Разрешить изменять файл нескольким пользователям одновременно.
На вкладке Подробнее выберите параметры для отслеживания и обновления изменений, а затем нажмите кнопку ОК.
Если книга новая, введите ее имя в поле Имя файла. Если используется существующая книга, нажмите кнопку ОК, чтобы сохранить ее.
Если книга содержит ссылки на другие книги и документы, проверьте их и исправьте все неработающие ссылки.
В меню Файл нажмите кнопку Сохранить.
Когда все будет готово, в верхней части окна Excel рядом с именем файла появится надпись — общая.
Дополнительные сведения
Имейте в виду, что общие книги — это устаревший метод предоставления общего доступа. Поэтому при его использовании не поддерживаются различные возможности и действия. В таблице ниже приведн неполный список функций, которые не поддерживаются.
Неподдерживаемые возможности
Неподдерживаемые действия
Создание и вставка таблиц
Вставка и удаление групп ячеек
Добавление и изменение условного форматирования
Добавление или изменение проверки данных
Объединение ячеек и разделение объединенных ячеек
Создание и изменение диаграмм или отчетов сводных диаграмм
Сортировка и фильтрация по формату
Вставка или изменение рисунков и других объектов
Использование инструментов рисования
Вставка и редактирование гиперссылок
Назначение, изменение или удаление паролей
Создание, изменение и просмотр сценариев
Защита листов или книг и снятие защиты
Автоматическая вставка промежуточных итогов
Группировка или структурирование данных
Создание таблиц данных
Написание, запись, изменение, просмотр и назначение макросов
Создание и изменение отчетов сводных таблиц
Изменение или удаление формул массива
Создание и применение срезов
Добавление, переименование или удаление карт XML
Создание и изменение спарклайнов
Сопоставление ячеек с XML-элементами
Добавление или изменение листов диалогов Microsoft Excel 4
Использование области задач "XML-источник", панели инструментов XML или XML-команд в меню "Данные"
Импорт, обновление и экспорт данных XML
Использование формы данных для добавления новых данных
Перед отключением пользователей убедитесь, что они завершили работу с этой книгой. При удалении активных пользователей будут потеряны все их несохраненные изменения.
На вкладке Рецензирование нажмите кнопку Общий доступ к книге.
На вкладке Правка в списке Файл открыт следующими пользователями просмотрите имена пользователей.
Выберите имя пользователя, которого вы хотите отключить и нажмите кнопку Удалить пользователя. Хотя данное действие отключает пользователя от книги, это не мешает ему снова начать ее редактирование.
Для удаления личных параметров просмотра удаленного пользователя щелкните Вид > Представления и удалите представления других пользователей.
Прежде чем отключить эту функцию, может потребоваться создать копию журнала изменений. Убедитесь, что все пользователи завершили свою работу.
На вкладке Рецензирование нажмите кнопку Отслеживание изменений и выберите команду Выделить исправления.
В списке по времени выберите вариант Все.
Снимите флажки пользователем и в диапазоне.
Установите флажок Вносить изменения на отдельный лист, а затем нажмите кнопку ОК.
Теперь можно распечатать лист журнала или скопировать его и вставить в другую книгу.
На вкладке Рецензирование нажмите кнопку Общий доступ к книге.
На вкладке Правка убедитесь, что вы — единственный пользователь, находящийся в списке Файл открыт следующими пользователями.
Снимите флажок Разрешить изменять файл нескольким пользователям одновременно. Если этот флажок недоступен, необходимо сначала отменить защиту книги. Для этого сделайте следующее:
На вкладке Рецензирование нажмите кнопку Защитить книгу.
Если будет предложено, введите пароль, а затем нажмите кнопку ОК.
На вкладке Рецензирование нажмите кнопку Общий доступ к книге.
На вкладке Правка снимите флажок Разрешить изменять файл нескольким пользователям одновременно.
Прежде чем отключить эту функцию, может потребоваться создать копию журнала изменений. Убедитесь, что все пользователи завершили свою работу.
На вкладке Рецензирование нажмите кнопку Отслеживание изменений и выберите команду Выделить исправления.
В списке по времени выберите вариант Все.
Снимите флажки пользователем и в диапазоне.
Установите флажок Вносить изменения на отдельный лист, а затем нажмите кнопку ОК.
Теперь можно распечатать лист журнала или скопировать его и вставить в другую книгу.
На вкладке Рецензирование нажмите кнопку Общий доступ к книге.
На вкладке Правка убедитесь, что вы — единственный пользователь, находящийся в списке Файл открыт следующими пользователями.
Снимите флажок Разрешить изменять файл нескольким пользователям одновременно. Если этот флажок недоступен, необходимо сначала отменить защиту книги. Для этого сделайте следующее:
На вкладке Рецензирование нажмите кнопку Защитить книгу.
Если будет предложено, введите пароль, а затем нажмите кнопку ОК.
На вкладке Рецензирование нажмите кнопку Общий доступ к книге.
На вкладке Правка снимите флажок Разрешить изменять файл нескольким пользователям одновременно.
Читайте также: