Как сохранить рабочую область в excel
Функциональность табличного процессора, большой спектр решаемых с его помощью задач, в том числе и довольно сложных, для которых необходимы несколько рабочих книг приводит к тому, что очень часто, работая с Excel, у пользователя открыты несколько файлов с данными. И вернувшись к такой задаче, не так-то просто найти и открыть все рабочие книги. Особенно эта проблема возникает в том случае, если рабочие книги Excel связаны между собой. Напомним, что рабочие листы и рабочие книги Excel можно связывать между собой, обеспечив таким образом изменение во вставленных данных при соответствующих изменениях в источнике. Если связываются рабочие листы, то в их сохранении проблем не возникает - ведь они являются частью одной рабочей книги. При связи нескольких рабочих книг, особенно, если таких книг больше двух, необходимо помнить, где расположена и как называется каждая из них. Для решения задачи сохранения нескольких открытых рабочих книг в Excel используется понятие рабочей области.
Рабочая область не содержит включенные в нее рабочие книги. Но она позволяет сохранить все открытые в данный момент рабочие книги в таком положении, в котором они находились до их сохранения. Причем файлы рабочих книг могут физически находится на разных дисках и в разных каталогах, у каждого из них могут быть установлены свои параметры страницы, масштаб и другие характеристики, рабочая область сохранит все это и при последующем открытии отобразит все в таком виде, в котором оно находилось до сохранения.
Для сохранения рабочей области необходимо выбрать "Файл -> Сохранить рабочую область" и в появившемся окне задать имя файла рабочей области. Расширение файлов рабочих областей - xlw.
Сохранение рабочей области Excel.
Открытие рабочей области осуществляется так же как и открытие любой рабочей книги Excel: можно выбрать "Файл -> Открыть" или дважды щелкнуть по ее пиктограмме. В результате этого будут открыты все рабочие книги, которые вошли в состав рабочей области.
При копировании файлов-элементов рабочей области, равно как и файла рабочей области, необходимо быть очень осторожным, ибо рабочая область сохраняет ссылки на конкретное месторасположение файла. Поэтому если файл перенести в другое место, при последующем открытии рабочей области, в которую он входил, файл может быть не найден. По возможности лучше всего сохранять рабочую область и файлы, входящие в ее состав, в одном каталоге, и так же, все вместе их копировать.
Про сборку листов из нескольких книг в одну текущую я уже писал здесь. Теперь разберем решение обратной задачи: есть одна книга Excel, которую нужно "разобрать", т.е. сохранить каждый лист как отдельный файл для дальнейшего использования.
Примеров подобного из реальной жизни можно привести массу. Например, файл-отчет с листами-филиалами нужно разделить на отдельные книги по листам, чтобы передать затем данные в каждый филиал и т.д.
Если делать эту процедуру вручную, то придется для каждого листа выполнить немаленькую цепочку действий (выбрать лист, правой кнопкой по ярлычку листа, выбрать Копировать, указать отдельный предварительно созданный пустой файл и т.д.) Гораздо проще использовать короткий макрос, автоматизирующий эти действия.
Способ 1. Простое разделение
Нажмите сочетание Alt+F11 или выберите в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст этого макроса:
Если теперь выйти из редактора Visual Basic и вернуться в Excel, а затем запустить наш макрос (Alt+F8), то все листы из текущей книги будут разбиты по отдельным новым созданным книгам.
Способ 2. Разделение с сохранением
При необходимости, можно созданные книги сразу же сохранять под именами листов. Для этого макрос придется немного изменить, добавив команду сохранения в цикл:
Этот макрос сохраняет новые книги-листы в ту же папку, где лежал исходный файл. При необходимости сохранения в другое место, замените wb.Path на свой путь в кавычках, например "D:\Отчеты\2012" и т.п.
Если нужно сохранять файлы не в стандартном формате книги Excel (xlsx), а в других (xls, xlsm, xlsb, txt и т.д.), то кроме очевидного изменения расширения на нужное, потребуется добавить еще и уточнение формата файла - параметр FileFormat:
Для основных типов файлов значения параметра FileFormat следующие:
- XLSX = 51
- XLSM = 52
- XLSB = 50
- XLS = 56
- TXT = 42
Способ 3. Сохранение в новые книги только выделенных листов
Если вы хотите раскидать по файлам не все листы в вашей книге, а только некоторые, то макрос придется немного изменить. Выделите нужные вам листы в книге, удерживая на клавиатуре клавишу Ctrl или Shift и запустите приведенный ниже макрос:
Создавать новое окно и копировать через него, а не напрямую, приходится потому, что Excel не умеет копировать группу листов, если среди них есть листы с умными таблицами. Копирование через новое окно позволяет такую проблему обойти.
Способ 4. Сохранение только выделенных листов в новый файл
Во всех описанных выше способах каждый лист сохранялся в свой отдельный файл. Если же вы хотите сохранить в отдельный новый файл сразу группу выделенных предварительно листов, то нам потребуется слегка видоизменить наш макрос:
Способ 5. Сохранение листов как отдельных PDF-файлов
- для этого используется уже другой метод (ExportAsFixedFormat а не Copy)
- листы выводятся в PDF с параметрами печати, настроенными на вкладке Разметка страницы (Page Layout)
- книга должна быть сохранена на момент экспорта
Нужный нам код будет выглядеть следующим образом:
Способ 6. Готовый макрос из надстройки PLEX
Если лень или нет времени внедрять все вышеописанное, то можно воспользоваться готовым макросом из моей надстройки PLEX:
Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, то попробуйте пробежаться по описанным ниже пунктам - возможно один или несколько из них укоротят вашего "переростка" до вменяемых размеров и разгонят его "тормоза" :)
Проблема 1. Используемый диапазон листа больше, чем нужно
Если ваша таблица занимает 5 на 5 ячеек, то это отнюдь не означает, что Excel запоминает при сохранении этого файла только 25 ячеек с данными. Если вы в прошлом использовали какие-либо ячейки на этом листе, то они автоматически включаются в используемый диапазон (так называемый Used Range), который и запоминается при сохранении книги. Проблема в том, что при очистке используемых ячеек Excel далеко не всегда автоматически исключает их из используемого диапазона, т.е. начинает запоминать в файле больше данных, чем реально имеется.
Проверить это просто – нажмите на клавиатуре сочетание клавиш Ctrl+End и посмотрите куда переместится активная ячейка. Если она прыгнет на фактическую последнюю ячейку с данными на листе – отлично. А если вдруг ускачет сильно правее и/или ниже "в пустоту" – дело плохо: все эти ненужные пустые ячейки Excel тоже запоминает внутри файла.
Лечится это, тем не менее, достаточно легко:
- Выделите первую пустую строку под вашей таблицей
- Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
- Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows) .
- Повторите то же самое со столбцами.
- Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а "в пустоту" ниже и/или правее.
- Сохраните файл (обязательно, иначе изменения не вступят в силу!)
Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.
Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB
Много лет и версий подряд еще с начала девяностых в Excel был один формат файлов - XLS. Это, конечно, убирало проблемы совместимости, но, сам по себе, этот формат давно устарел и имел много неприятных недостатков (большой размер, непрозрачность внутренней структуры данных, легкую повреждаемость и т.д.)
Начиная с верии Excel 2007 Microsoft ввела новые форматы сохранения файлов, использование которых заметно облегчает жизнь и - ваши файлы:
- XLSX - по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
- XLSM - то же самое, но с поддержкой макросов.
- XLSB - двоичный формат, т.е. по сути - что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер - минимален.
Проблема 3. Избыточное форматирование
Сложное многоцветное форматирование, само-собой, негативно отражается на размере вашего файла. А условное форматирование еще и ощутимо замедляет его работу, т.к. заставляет Excel пересчитывать условия и обновлять форматирование при каждом чихе.
Оставьте только самое необходимое, не изощряйтесь. Особенно в тех таблицах, которые кроме вас никто не видит. Для удаления только форматов (без потери содержимого!) выделите ячейки и выберите в выпадающем списке Очистить - Очистить форматы (Clear - Clear Formats) на вкладке Главная (Home) :
Особенно "загружают" файл отформатированные целиком строки и столбцы. Т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс. столбцов), то для запоминания и обрабоки подобного форматирования нужно много ресурсов. В Excel 2013-2016, кстати, появилась надстройка Inquire, которая содержит инструмент для быстрого избавления от подобных излишеств - кнопку Удалить избыточное форматирование (Clean Excess Cell Formatting) :
Она мгновенно удаляет все излишнее форматирование до конца листа, оставляя его только внутри ваших таблиц и никак не повреждая ваши данные. Причем может это сделать даже для всех листов книги сразу.
Если вы не видите у себя в интерфейсе вкладку Inquire, то ее необходимо подключить на вкладке Разработчик - Надстройки COM (Developer - COM Addins) .
Проблема 4. Ненужные макросы и формы на VBA
Большие макросы на Visual Basic и особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу. Для удаления:
- нажмите Alt+F11, чтобы войти в редактор Visual Basic
- найдите окно Project Explorer’а (если его не видно, то выберите в меню View - Project Explorer)
- удалите все модули и все формы (правой кнопкой мыши - Remove - дальше в окне с вопросом о экспорте перед удалением - No):
Также код может содержаться в модулях листов - проверьте их тоже. Также можно просто сохранить файл в формате XLSX без поддержки макросов - все макросы и формы умрут автоматически. Также можно воспользоваться инструментом Очистить книгу от макросов из надстройки PLEX.
Проблема 5. Именованные диапазоны
Если в вашем файле используются именованные диапазоны (особенно с формулами, динамические или получаемые при фильтрации), то имеет смысл от них отказаться в пользу экономии размера книги. Посмотреть список имеющихся диапазонов можно нажав Ctrl+F3 или открыв окно Диспетчера имен (Name Manager) на вкладке Формулы (Formulas) :
Также вычищайте именованные диапазоны с ошибками (их можно быстро отобрать с помощью кнопки Фильтр в правом верхнем углу этого окна) - они вам точно не пригодятся.
Проблема 6. Фотографии высокого разрешения и невидимые автофигуры
Если речь идет о фотографиях, добавленных в книгу (особенно когда их много, например в каталоге продукции), то они, само-собой, увеличивают размер файла. Советую сжимать их, уменьшая разрешение до 96-150 точек на дюйм. На экране по качеству это совершенно не чувствуется, а размер файла уменьшает в разы. Для сжатия воспользуйтесь кнопкой Сжать рисунки (Compress pictures) на вкладке Формат (Format) :
Кроме видимых картинок на листе могут содержаться и невидимые изображения (рисунки, фотографии, автофигуры). Чтобы увидеть их, выделите любую картинку и на вкладке Формат (Format) нажмите кнопку Область выделения (Selection Pane) .
Для удаления вообще всех графических объектов на текущем листе можно использовать простой макрос:
Проблема 7. Исходные данные сводных таблиц
По-умолчанию Excel сохраняет данные для расчета сводной таблицы (pivot cache) внутри файла. Можно отказаться от этой возможности, заметно сократив размер файла, но увеличив время на обновление сводной при следующем открытии книги. Щелкните правой кнопкой мыши по сводной таблице и выберите команду Свойства таблицы (Pivot Table Properties) - вкладка Данные (Data) - снять флажок Сохранять исходные данные вместе с файлом (Save source data with file):
Если у вас несколько сводных таблиц на основе одного диапазона данных, то сократить размер файла здорово помогает метод, когда все сводные таблицы после первой строятся на основе уже созданного для первой таблицы кэша. В Excel 2000-2003 это делается выбором переключателя на первом шаге Мастера сводных таблиц при построении:
В Excel 2007-2016 кнопку Мастера сводных таблиц нужно добавлять на панель вручную - на ленте такой команды нет. Для этого щелкните по панели быстрого доступа правой кнопкой мыши и выберите Настройка панели быстрого доступа (Customize Quick Access Toolbar) и затем найдите в полном списке команд кнопку Мастер сводных таблиц (PivotTable and PivotChart Wizard) :
Проблема 8. Журнал изменений (логи) в файле с общим доступом
Если в вашем файле включен общий доступ на вкладке Рецензирование - Доступ к книге (Review - Share Workbook) , то внутри вашего файла Excel на специальном скрытом листе начинает сохраняться вся история изменений документа: кто, когда и как менял ячейки всех листов. По умолчанию, такой журнал сохраняет данные изменений за последние 30 дней, т.е. при активной работе с файлом, может запросто занимать несколько мегабайт.
Мораль: не используйте общий доступ без необходимости или сократите количество дней хранения данных журнала, используя вторую вкладку Подробнее (Advanced) в окне Доступ к книге. Там можно найти параметр Хранить журнал изменений в течение N дней (Keep change history for N days) или совсем отключить его:
Проблема 9. Много мусорных стилей
Про эту пакость я уже подробно писал ранее в статье о том, как победить ошибку "Слишком много форматов ячеек". Суть, если кратко, в том, что если вы разворачиваете на вкладке Главная список Стили ячеек (Home - Cell Styles) и видите там очень много непонятных и ненужных стилей, то это плохо - и для размера вашего файла Excel и для его быстродействия.
Удалить ненужные стили можно с помощью макроса или готовой команды из надстройки PLEX.
Проблема 10. Много примечаний
Примечания к ячейкам, конечно, не самый вредный момент из всех перечисленных. Но некоторые файлы могут содержать большое количество текста или даже картинок в примечаниях к ячейкам. Если примечания не содержат полезной для вас информации, то их можно легко удалить с помощью команды на вкладке Главная - Очистить - Очистить примечания (Home - Clear - Clear Comments) .
Всем привет! В Excel есть множество способов облегчить и ускорить свою работу, прибегая к некоторым уловкам и лайфхакам. Об этих моментах не рассказывается подробно в инструкциях и встроенной справке, поэтому по ходу публикаций о стандартной работе в программе, я также буду выкладывать разных хитрости и трюки )) Если вы считаете, что для вас это будет полезно, следите за обновлениями!
Итак. Все знают, что такое рабочие книги в Excel — это модельное представление. Большинство знают, что много открытых одновременно рабочих книг, могут вызывать большое количество загвоздок и помех. Особенно это касается если необходимо работать со всеми сразу. Потому первый сегодняшний трюк о том, как облегчить ваши страдания при работе с множеством открытых окон рабочих книг.
Чтобы одновременно отобразить на экране несколько рабочих книг, в любой из открытых книг выберите Окно → Упорядочить все . Если установлен флажок «Только окна текущей книги» уберите его, а затем установите нужный переключатель и щелкните на кнопке ОК.
Если выбрать отметку Рядом , ваши книги будут располагаться рядом черепицей.
Выбрав отметку Сверху вниз , вы расположите книги друг над другом.
Выберите отметку Слева направо , чтобы расположить книги поочередно, друг с другом.
Отметка Каскадом позволит расположить все книги поверх друг друга.
Расположив книги удобнее, вы легко сможете копировать, вставлять, перетаскивать и изменять в них данные из одной книги в другую, если это нельзя сделать другими способами типа формул.
Если вы хотите повторить созданное представление, то можно сохранить его как рабочую область. Для этого выберите команду Файл → Сохранить рабочую область . Файл будет сохранен с расширением .xlw, а не стандартным .xlsx. Если вы захотите позже открыть те же файлы, откройте файл .xlw , и ранее сохраненное представление чудесным образом будет восстановлено для всех книг.
Затратив немного времени на настройку личных представлений для задач, требующих одновременного открытия нескольких рабочих книг, вы со временем убедитесь, что справляться с этими задачами станет легче. Для разных задач можно конструировать разные представления, в зависимости от типа задачи или вашего самочувствия в конкретный день.
На этом у меня всё. Если вам понравился сегодняшний трюк, ставьте лайки и подписывайтесь на канал чтобы не пропустить еще более интересных материалов )) Спасибо!
Читайте также: