Как убрать название файла в excel
Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в 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) .
Каждый раз при создании документа Word задает свойство Author (Автор) на основе параметра Имя пользователя, который отображается в диалоговом окне Параметры Word. Аналогично PowerPoint задает это свойство для новых презентаций, используя параметр в PowerPointПараметры , а Excel задает это свойство для новых книг, которые вы создаете, с помощью параметра в Excel Параметры. Сведения о пользователе также являются источником имени и инициалов, отображаемых в комментариях и исправлениях.
Чтобы увидеть свойство Author (Автор) для документа или книги, щелкните Файл >сведения, а затем найдите author (Автор) в области Related People (Связанные люди) справа.
Изменение имени автора для новых документов, презентаций и книг
Важно: Эта процедура изменяет связанные параметры для всех приложений Office, независимо от того, какое приложение вы используете при их изменении.
Выберите Файл > Параметры.
В поле Личная Microsoft Officeвведите новое имя в поле Имя пользователя.
Убедитесь, что для этого не выбран Office всегда использовать эти значения.
Изменение имени автора только в существующем документе, презентации или книге
Щелкните Файл и найдите Автор в разделе Связанные люди справа.
Щелкните правой кнопкой мыши имя автора и выберите команду Изменить свойство.
Введите новое имя в диалоговом окне Изменение пользователя.
Примечание: Изменение свойства Author в области свойств существующего документа не влияет на параметр Имя пользователя в диалоговом окне Параметры Word, PowerPoint параметрыили параметры Excel параметры.
Важно: Office 2007 больше не поддерживается. Перейдите на Microsoft 365, чтобы работать удаленно с любого устройства и продолжать получать поддержку.
1 В новых документах имя, отображаемое в свойстве "Автор" (которое видно в области сведений о документе), берется из поля Имя пользователя в диалоговом окне Параметры Word.
Чтобы увидеть свойство Author (Автор) для документа, нажмите кнопку Microsoft Office , выберите подготовить ,а затем щелкните Свойства.
Изменение имени автора для новых документов
Выберите категорию Основные.
В разделе Личная настройка Office введите имя автора в поле Имя пользователя.
Изменение имени автора в существующем документе
В области сведений о документе введите имя в поле Автор.
Примечание: Изменение свойства "Автор" существующего документа в области сведений о документе не влияет на сведения о пользователе в диалоговом окне Параметры Word.
Иногда бывает необходимо заполучить на лист Excel список файлов в заданной папке и ее подпапках. В моей практике такое встречалось неоднократно, например:
- перечислить в приложении к договору на проведение тренинга список файлов из раздаточных материалов для особо щепетильных юристов в некоторых компаниях
- создать список файлов для ТЗ проекта
- сравнить содержимое папок (оригинал и бэкап, например)
Для реализации подобной задачи можно использовать несколько способов.
Способ 1. Скелет из шкафа - функция ФАЙЛЫ
Этот способ использует древнюю функцию ФАЙЛЫ (FILES) , оставшуюся в Microsoft Excel с далеких девяностых. Вы не найдете эту функцию в общем списке функций, но для совместимости, она всё ещё остаётся внутри движка Excel, и мы вполне можем её использовать.
1. В любую ячейку листа (например, в А1) введём путь к папке, список файлов из которой мы хотим получить.
Обратите внимание, что путь должен оканчиваться шаблоном со звездочками:
- *.* - любые файлы
- *.xlsx - книги Excel (только с расширением xlsx)
- *.xl* - любые файлы Excel
- *отчет* - файлы, содержащие слово отчет в названии
2. Создадим именованный диапазон с помощью вкладки Формулы - далее кнопка Диспетчер имен - Создать (Formulas - Names Manger - Create) . В открывшемся окне введем любое имя без пробелов (например Мои_файлы) и в поле диапазона выражение:
После нажатия на ОК будет создан именованный диапазон с именем Мои_файлы, где хранится список всех файлов из указанной в А1 папки. Останется их оттуда только извлечь.
3. Чтобы извлечь имена отдельных файлов из созданной переменной, используем функцию ИНДЕКС (INDEX) , которая в Excel вытаскивает данные из массива по их номеру:
Если лениво делать отдельный столбец с нумерацией, то можно воспользоваться костылем в виде функции СТРОКИ (ROWS) , которая будет подсчитывать количество заполненных строк с начала списка автоматически:
=ИНДЕКС(Мои_файлы; ЧСТРОК($B$3:B3) )
= ЕСЛИОШИБКА( ИНДЕКС(Мои_файлы; ЧСТРОК($B$3:B3)) ; "")
Важное примечание : формально функция ФАЙЛЫ относится к макро-функциям, поэтому необходимо будет сохранить ваш файл в формате с поддержкой макросов (xlsm или xlsb).
Способ 2. Готовый макрос для ленивых
Если вы знакомы с макросами (не в смысле их программирования, а в смысле копипастинга готовых кодов на VBA), то вам, возможно, отлично зайдёт небольшой макрос, добавляющий в текущую книгу новый пустой лист и выводящий на него список всех файлов с их параметрами из заданной пользователем папки.
Для добавления макроса в вашу книгу нажмите сочетание клавиш Alt + F11 , или кнопку Visual Basic на вкладке Разработчик (Developer) , в открывшемся окне редактора Visual Basic вставьте новый модуль через меню Insert - Module и скопируйте туда текст этого макроса:
Для запуска макроса нажмите сочетание клавиш Alt + F8 ,или кнопку Макросы (Macros) на вкладке Разработчик (Developer) , выберите наш макрос FileList и нажмите кнопку Выполнить (Run) . В диалоговом окне выберите любую папку или диск и - вуаля!
Если захотите, чтобы вместо пути к файлу в столбце B выводилась живая гиперссылка, то замените 52-ю строку
Cells(r, 2).Formula = FileItem.Path
Cells(r, 2).Formula = "=HYPERLINK(""" & FileItem.Path & """)"
Способ 3. Мощь и красота - надстройка Power Query
Power Query - это очень мощная и при этом бесплатная надстройка для Excel от Microsoft, упрощающая множество задач по загрузке и трансформации данных. В нашей ситуации она тоже может здорово помочь.
Если у вас Excel 2016 или новее, то Power Query уже встроена в Excel по умолчанию, поэтому просто на вкладке Данные выберите команду Создать запрос / Получить данные - Из файла - Из папки (Create Query / Get Data - From file - From folder) . Если у вас Excel 2010-2013, то Power Query нужно будет скачать с сайта Microsoft и установить как отдельную надстройку и она появится у вас в Excel в виде отдельной вкладки Power Query. На ней будет аналогичная кнопка Из файла - Из папки (From file - From folder) .
В открывшемся окне нужно будет указать папку, содержимое которой мы хотим получить. После нажатия на ОК Power Query обшарит указанную папку и все вложенные подпапки и выдаст на экран окно с предварительным просмотром результатов:
Если внешний вид списка вас устраивает, то можно смело жать внизу кнопку Загрузить (Load) , чтобы залить эти данные на новый лист. Если же хочется дополнительно обработать список (удалить лишние столбцы, отобрать только нужные файлы и т.п.), то нужно выбрать команду Изменить / Преобразовать данные (Edit / Transform Data).
Поверх окна Excel откроется окно редактора Power Query, где мы увидим список всех наших файлов в виде таблицы:
Дальше возможны несколько вариантов:
-
Если нужны только файлы определенного типа, то их можно легко отобрать с помощью фильтра по столбцу Extension:
После того, как необходимые файлы отобраны, можно смело удалить ненужные столбцы, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить (Remove column ) . Это, кстати, уже никак не повлияет на фильтрацию или сортировку нашего списка:
Если в будущем планируется подсчитывать количество файлов в каждой папке (например, для контроля поступивших заявок или подсчета статистики по заявкам), то имеет смысл дополнительно сделать ещё пару действий:
- Щелкните правой кнопкой мыши по столбцу Folder Path и выберите команду Дублировать столбец (Duplicate Column) .
- Выделите скопированный столбец и на вкладке Преобразование (Transform) выберите Разделить столбец - По разделителю (Split Column - By delimiter)
Мы получим рядом с нашими данными еще несколько столбцов, где будут продублированы имена вложенных папок - это пригодится нам чуть позже для подсчета статистики с помощью сводной таблицы:
Получившиеся столбцы можно переименовать (Диск, Папка1, Папка2 и т.д.), просто щёлкнув дважды по заголовку каждого.
И, наконец, когда список готов, то его можно выгрузить на лист с помощью команды Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close & Load - Close & Load to. ) :
И, само-собой, теперь можно построить по нашей таблице сводную (вкладка Вставка - Сводная таблица), чтобы легко подсчитать количество файлов в каждой папке:
Дополнительным бонусом можно сделать еще один столбец с функцией ГИПЕРССЫЛКА (HYPERLINK) , которая создаст красивые стрелочки-ссылки для моментального перехода к каждому файлу:
Мелочь, а приятно :)
И вдвойне приятно, что в будущем, при изменении содержимого исходной папки, достаточно будет просто щелкнуть мышью по нашей таблице и выбрать команду Обновить (Refresh) - и Power Query выполнит всю цепочку запрограммированных нами единожды действий уже автоматически, отобразив все изменения в составе папки.
Здравствуйте. Рад представить Вам пошаговую инструкцию по удалению скрытых имен в Экселе. Вы возможно сталкивались с ситуацией, когда при копировании листа в книге Excel возникала ошибка, которая сообщает что Имя уже существует и нужно либо выбрать новое, либо использовать тоже. Хорошо если таких ошибок 2 - 3, а если их несколько сотен или тысяч, тогда никакого терпения не хватит нажимать ОК. Используя рекомендации, представленные ниже, Вы избавитесь от ошибки навсегда! Итак, приступим:
1. Создание макроса DeleteHiddenNames.
Встроенной функции в Excel для решения этой проблемы я не нашел, зато есть замечательный макрос, с помощью которого мы от нее избавимся. Сначало надо зайти в редактор макросов, для этого запустите Excel, откройте файл с проблемой и нажмите ALT+F11. Откроется Microsoft Visual Basic for Applications, далее заходим в меню Insert и выбираем Module.
Открывается окно модуля. Туда Вы должны вставить следующий код макроса:
Sub DeleteHiddenNames()
Dim n As Name
Dim Count As Integer
On Error Resume Next
For Each n In ActiveWorkbook.Names
If Not n.Visible Then
n.Delete
Count = Count + 1
End If
Next n
MsgBox "Скрытые имена в количестве " & Count & " удалены"
End Sub
Выглядеть это должно в результате следующим образом:
Отлично. Макрос мы создали, теперь нам осталось его применить.
2. Использования макроса для удаления скрытых имен в Excel.
Переключаемся назад на нашу Эксель книгу, переходим в меню Вид, там выбираем блок Макросы и в нем нажимаем на меню Макросы.
В открывшемся меню выбираем макрос DeleteHiddenNames и нажимаем кнопку выполнить.
Читайте также: