Top-office11.ru

IT и мир ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Трюк №14. Предотвращение распухания рабочей книги Excel

Трюк №14. Предотвращение распухания рабочей книги Excel

Вы когда-нибудь замечали, что рабочая книга увеличивается в размере с пугающей скоростью без какой-либо очевидной причины? Это происходит по нескольким причинам, и мы предлагаем решения, помогающие вашим книгам похудеть.

Бывало ли с вами такое, что из-за переедания случались проблемы пищеварения? Распухание книги в Excel очень похоже на такую ситуацию. Распухание рабочей книги — это термин, обозначающий ситуацию, когда в книге сделано так много и она увеличилась до такого размера, что не может правильно функционировать.

Мы проверили размер типичной книги, содержащей достаточно много данных. Только с данными размер файла книги был равен 1,37 Мбайт. Затем мы добавили сводную таблицу, которая ссылалась на четыре полных столбца исходных данных, и заметили, что размер файла существенно увеличился — до 2,4 Мбайт. Добавьте еще немного форматирования, и размер типичной книги возрастет практически вдвое после выполнения всего лишь нескольких действий.

Одна из наиболее часто встречающихся причин распухания файла, особенно в ранних версиях Excel, — это наложение форматирования на столбцы или строки целиком, а не только на используемый диапазон данных. Другая причина — это ссылки на полные столбцы как на источники данных для диаграмм и сводных таблиц, а не только на ячейки с фактическими данными. Чтобы исправить эти ошибки, понадобится исключить излишнее форматирование и ограничить источники данных только нужными диапазонами ячеек.

Устранение излишнего форматирования

Первый шаг при устранении излишнего форматирования — это вычисление ячейки, где заканчиваются данные на рабочем листе, то есть правого нижнего угла таблицы данных. Не полагайтесь на команду Правка → Перейти → Выделить → Последнюю ячейку (Edit → Go To → Special → Last Cell), так как она приведет вас к последней ячейке, содержащей форматирование, а не фактические данные. Вручную найдите последнюю ячейку с фактическими данными, выделите строку сразу же за ней. Удерживая клавиши Ctrl и Shift, нажимайте на клавиатуре стрелку вниз, чтобы выделить все строки ниже, и выберите команду Правка → Очистить → Все (Edit → Clear → All), чтобы очистить их.

Теперь примените ту же логику к нежелательному форматированию в столбцах. Найдите ячейку в последнем столбце, содержащем данные, и щелкните кнопку столбца справа. Удерживайте сочетание клавиш Ctlr+Shift и нажимайте на клавиатуре стрелку вправо, чтобы выделить все остальные столбцы справа, а затем очистите их командой Правка → Очистить → Все (Edit → Clear → All).

Если у вас есть макросы, то теперь необходимо обратиться к модулям, в которые помещен код макросов. Это достаточно быстрый, безболезненный и очевидный процесс, который включает экспорт всех модулей и пользовательских форм UserForm на жесткий диск, удаление существующих модулей и форм, сохранение и импортирование только что экспортированных модулей.

Чтобы сделать это, перейдите в редактор Visual Basic и в окне Project Explorer правой кнопкой мыши щелкните каждый модуль и выберите команду Remove Module1 (считайте, что вместо Module1 стоит имя любого вашего модуля). Когда появится сообщение с вопросом, нужно ли экспортировать модуль перед удалением, ответьте Yes и запомните путь, куда будут экспортированы модули.

Выполните это для всех модулей по очереди, а также для любых форм UserForm, которые у вас могут быть. Не забывайте частные модули вашей книги и листов, если в них также содержится код. Закончив, сохраните рабочую книгу. Затем выберите команду Файл → Импорт (File → Import File) и импортируйте все модули и формы UserForm обратно в рабочую книгу. При этом будет создан текстовый файл для каждого модуля и удалено все лишнее, что может содержаться в модулях.

В сети можно найти несколько бесплатных утилит, которые до некоторой степени автоматизируют эту задачу, но мы встречались со случаями, когда такие утилиты только усложняли код, увеличивая размер файлов. Если вы все же применяете подобные утилиты, всегда сначала сохраняйте резервную копию книги, так как разработчики утилит не несут ответственности за потерю данных.

Настройка источников данных

Если после выполнения предыдущих действий вы все же уверены, что размер файла слишком большой, можно проверить ссылки на неиспользуемые ячейки в сводных таблицах (PivotTable) и сводных диаграммах (PivotChart). Это особенно касается сводных таблиц, так как пользователи часто создают ссылки на все 65 536 строк, чтобы избежать обновления диапазонов вручную после добавления данных. Если это ваш случай, используйте динамические именованные диапазоны [Трюк № 42] для источников данных.

Очистка поврежденных книг

Если вы все так же считаете, что ваша книга слишком большая, возможно, эта книга или составляющие ее листы повреждены. К сожалению, определение точки повреждения требует выполнить процесс исключения вручную.

Для каждого листа: удалите лист, сохраните книгу и проверьте размер файла, выбрав команду Файл → Свойства → Общие (File → Properties → General). Если размер файла существенно уменьшился, учитывая количество данных на удаленном листе, то вы, вероятно, нашли место повреждения.

Чтобы заменить в книге поврежденный лист, создайте новый лист, вручную выделите данные на поврежденном листе, вырежьте (не копируйте!) и вставьте их на новый лист. Удалите поврежденный лист из книги, сохраните ее и повторите процесс.

Читать еще:  Как перевести деньги с одного кошелька QIWI на другой

Но если все-таки нельзя удалить картинку или фото из документа, ее можно сжать. Выделяем изображение и кликаем на вкладку «Формат». Здесь нам нужно выбрать пункт «Сжать рисунки».

Выставляем настройки так, как показано на скриншоте, и нажимаем «ОК».

Качество изображение почти не изменилось, но его размер сократился. Так можно проделать и с другими картинками в таблице.

12 наиболее распространённых проблем с Excel и способы их решения

12 наиболее распространённых проблем с Excel и способы их решения

Читатели Лайфхакера уже знакомы с Денисом Батьяновым, который делился с нами секретами Excel. Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.

Сразу оговорюсь, что материал статьи предназначается для начинающих пользователей Excel. Опытные пользователи уже зажигательно станцевали на этих граблях не раз, поэтому моя задача уберечь от этого молодых и неискушённых «танцоров».

Вы не даёте заголовки столбцам таблиц

Многие инструменты Excel, например: сортировка, фильтрация, умные таблицы, сводные таблицы, — подразумевают, что ваши данные содержат заголовки столбцов. В противном случае вы либо вообще не сможете ими воспользоваться, либо они отработают не совсем корректно. Всегда заботьтесь, чтобы ваши таблицы содержали заголовки столбцов.

Пустые столбцы и строки внутри ваших таблиц

Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.

На одном листе располагается несколько таблиц

Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.

Вам будет неудобно полноценно работать больше чем с одной таблицей на листе. Например, если одна таблица располагается слева, а вторая справа, то фильтрация одной таблицы будет влиять и на другую. Если таблицы расположены одна под другой, то невозможно воспользоваться закреплением областей, а также одну из таблиц придётся постоянно искать и производить лишние манипуляции, чтобы встать на неё табличным курсором. Оно вам надо?

Данные одного типа искусственно располагаются в разных столбцах

Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

Казалось бы, перед нами безобидный формат для накопления информации по продажам агентов и их штрафах. Подобная компоновка таблицы хорошо воспринимается человеком визуально, так как она компактна. Однако, поверьте, что это сущий кошмар — пытаться извлекать из таких таблиц данные и получать промежуточные итоги (агрегировать информацию).

Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.

Если вы построите сводную таблицу, то обнаружите, что нет возможности легко получить данные по году или кварталу, так как показатели разнесены по разным полям. У вас нет одного поля по объёму продаж, которым можно удобно манипулировать, а есть 12 отдельных полей. Придётся создавать руками отдельные вычисляемые поля для кварталов и года, хотя, будь это всё в одном столбце, сводная таблица сделала бы это за вас.

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Рекомендуемый формат таблицы выглядит так:

Разнесение информации по разным листам книги «для удобства»

Ещё одна распространенная ошибка — это, имея какой-то стандартный формат таблицы и нуждаясь в аналитике на основе этих данных, разносить её по отдельным листам книги Excel. Например, часто создают отдельные листы на каждый месяц или год. В результате объём работы по анализу данных фактически умножается на число созданных листов. Не надо так делать. Накапливайте информацию на ОДНОМ листе.

Информация в комментариях

Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.

Бардак с форматированием

Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:

  1. Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
  2. Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
  3. Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.
Читать еще:  Как перезагрузить МакБук

Объединение ячеек

Используйте объединение ячеек только тогда, когда без него никак. Объединенные ячейки сильно затрудняют манипулирование диапазонами, в которые они входят. Возникают проблемы при перемещении ячеек, при вставке ячеек и т.д.

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

Избегайте хранить числовые данные в ячейке в формате текста. Со временем часть ячеек в таком столбце у вас будут иметь текстовый формат, а часть в обычном. Из-за этого будут проблемы с формулами.

Если ваша таблица будет презентоваться через LCD проектор

Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

Страничный режим листа в Excel

Это тот самый режим, при котором Excel показывает, как лист будет разбит на страницы при печати. Границы страниц выделяются голубым цветом. Не рекомендую постоянно работать в этом режиме, что многие делают, так как в процессе вывода данных на экран участвует драйвер принтера, а это в зависимости от многих причин (например, принтер сетевой и в данный момент недоступен) чревато подвисаниями процесса визуализации и пересчёта формул. Работайте в обычном режиме.

Подтяжка старых текстов по группам и кампаниям к новым

Помогает при сравнении файлов — например, для старых и новых текстов из аккаунта. Переставлять тексты руками — скучно и нудно, здесь помогут функции СЦЕПИТЬ и ВПР.

Для этого в файл со старыми и с новыми текстами добавляем дополнительный столбец. Затем при помощи функции ЕСЛИ присвоим номер каждой строке.

Создаем еще один дополнительный столбец. При помощи функции СЦЕПКА соединяем название кампании, название группы и номер строки.

Теперь при помощи функции ВПР стягиваем данные из одного файла в другой.

Вот как вы можете исправить слишком много различных форматов ячеек Ошибка Excel

1. Очистите все ячейки электронной таблицы

  • Самый простой способ исправить ошибку « Слишком много разных форматов ячеек » – это очистить все ячейки электронной таблицы. Для этого нажмите сочетание клавиш Ctrl + A, чтобы выделить все ячейки в электронной таблице.
  • Выберите вкладку «Главная», показанную ниже.

Нажмите кнопку Очистить , чтобы открыть подменю на снимке экрана непосредственно под снимком.

  • Затем выберите параметр Очистить форматы . После этого вы можете применить новое форматирование к электронной таблице.

– СВЯЗАННО: высокая загрузка ЦП в Excel? У нас есть решения, чтобы это исправить

2. Добавьте стандартный шрифт ко всем ячейкам и удалите цвета заливки

Однако очистка всех форматов листа может быть немного радикальным решением. Если вы предпочитаете сохранить часть форматирования в электронной таблице, лучше исправить уменьшение количества уникальных форматов ячеек. Один из лучших способов сделать это – добавить стандартизированный шрифт в электронную таблицу, чтобы все ячейки имели одинаковый шрифт.

Кроме того, удаление всех отдельных цветов заливки на листе Excel также может уменьшить количество уникальных форматов ячеек. Следуйте приведенным ниже инструкциям, чтобы стандартизировать шрифт листа и заполнить форматирование цвета.

  • Сначала нажмите сочетание клавиш Ctrl + A, чтобы выбрать все ячейки электронной таблицы.
  • Затем щелкните меню шрифтов на вкладке «Главная», чтобы выбрать новый шрифт для всех ячеек.

  • Нажмите кнопку Цвет заливки , чтобы открыть палитру, как на снимке экрана ниже.

  • Выберите параметр Без заливки , чтобы удалить все цвета заливки из электронной таблицы. Тогда все ячейки электронной таблицы будут иметь один и тот же шрифт и не будут включать какое-либо форматирование цвета заливки.

3. Выберите опцию «Очистить лишнее форматирование ячеек».

Помните, что пустые ячейки электронной таблицы Excel также могут включать форматирование. Например, столбец будет включать одинаковый формат шрифта в пустые ячейки и те, в которых есть данные, если вы примените один и тот же шрифт ко всему столбцу. Таким образом, пустые ячейки, которые не используются, также могут увеличить количество уникально отформатированных ячеек.

Таким образом, удаление лишнего форматирования из электронной таблицы является еще одним потенциальным решением для ошибки « форматов ячеек ». Вы можете сделать это быстро с помощью опции Форматирование избыточной ячейки в дополнении Inquire. Inquire – это надстройка, которую вы можете использовать в Excel 2013 и более поздних версиях. Таким образом вы можете стереть лишнее форматирование в пустых ячейках электронной таблицы с помощью Inquire.

  • Откройте вкладку “Файл” и выберите Параметры , чтобы открыть окно параметров Excel.
Читать еще:  Как отключить фоновые приложения на Андроид

  • Нажмите Надстройки в левой части окна параметров Excel.
  • Затем нажмите раскрывающееся меню Управление и выберите Надстройки COM .
  • Установите флажок «Запросить» в окне «Надстройки COM», а затем нажмите кнопку ОК .
  • После этого вы можете выбрать вкладку Inquire в окне Excel.
  • Нажмите кнопку Очистить избыточное форматирование ячеек на вкладке “Запрос”.
  • Выберите, чтобы очистить все рабочие листы в электронной таблице. После этого нажмите Да , чтобы сохранить изменения в электронной таблице.

– СВЯЗАННО: Excel не открывает файлы, вместо этого отображается белый экран? Вы можете это исправить

4. Проверьте инструмент уменьшения стиля для Microsoft Excel

Сообщение об ошибке « Слишком много разных форматов ячеек » может появиться, когда некоторые пользователи пытаются открыть файл электронной таблицы. Следовательно, файл электронной таблицы Excel не открывается; и пользователи не могут избавиться от форматирования ячеек, как описано выше. В таких обстоятельствах стоит обратить внимание на инструмент уменьшения стиля для Excel. Это утилита, которая исправляет ошибку « форматы ячеек », уменьшая дублирующиеся стили форматирования.

Нажмите кнопку Загрузить на этой странице, чтобы сохранить программное обеспечение в папке. Затем откройте окно инструмента уменьшения стиля, показанное непосредственно ниже, и нажмите кнопку Файл , чтобы выбрать таблицу для исправления. Нажмите кнопку Анализировать , чтобы найти дубликаты стилей, и нажмите кнопку «Исправить», чтобы удалить форматирование.

5. Откройте файл в альтернативной программе для работы с электронными таблицами.

Кроме того, вы можете попробовать открыть файл Excel в альтернативном программном обеспечении. Тогда вы сможете открыть и применить больше форматирования к электронной таблице в альтернативном приложении без каких-либо проблем. Существует множество программ для работы с электронными таблицами, которые также поддерживают формат файлов Excel. LibreOffice Calc – это одно приложение, в котором вы можете открывать файлы Excel, и вы можете загрузить этот офисный пакет с этой веб-страницы.

– СВЯЗАН: Microsoft Excel ожидает, пока другое приложение завершит действие OLE [FIX] .

6. Восстановите электронную таблицу с помощью Stellar Repair for Excel.

Файл Excel также может быть поврежден, если при попытке открыть его появляется сообщение об ошибке « cell format ». Поврежденные электронные таблицы Excel можно исправить с помощью программного обеспечения Stellar Repair for Excel для Windows. Программное обеспечение в настоящее время продается по цене 39 долларов на сайте издателя. Вы также можете попробовать демонстрационную версию программного обеспечения, нажав Бесплатная загрузка на этой веб-странице.

После установки и открытия программного обеспечения вы можете нажать Выбрать файл , чтобы выбрать электронную таблицу, которая может быть повреждена. Нажмите Сканировать , чтобы начать сканирование. После этого вы увидите предварительный просмотр поврежденного файла; и можете нажать кнопку Восстановить , чтобы исправить это.

Таким образом, вы можете исправить ошибку « Слишком много разных форматов ячеек ». Сокращение уникального форматирования ячеек в электронных таблицах, безусловно, решит проблему, когда вы сможете редактировать листы. Если вы не можете открыть файл в Excel, воспользуйтесь инструментом сокращения стилей и программой Stellar Repair for Excel или попробуйте отредактировать электронную таблицу с помощью другого приложения.

Решение 3.
Восстановить перезаписанный файл Excel из временного файла (автоматическое восстановление включено)

Функция автоматического восстановления Microsoft Office также сохраняет все редактируемые файлы Excel в виде временного файла и сохраняет их в определенном месте на диске Windows C.

Таким образом, если ваш файл Excel был утерян, заменен или даже перезаписан, вы можете напрямую восстановить его из файлов TMP.

Шаг 1 Перейдите временный файл Excel с расширением .TMP.

Для пользователей Windows 7:

C: Users имя AppData Roaming Microsoft Excel

Для пользователей Windows 8/10:

C: Users \ AppData Local Microsoft Office UnsavedFiles

Или вы можете нажать клавишу Windows + R> введите% temp%, чтобы перейти во временную папку.

Найти временные файлы

Шаг 2 Найдите и найдите временные файлы Excel, которые начинаются с «$» или «

» и заканчиваются «.TMP».

Выберите точный временный файл Excel по дате, скопируйте его в другое место и измените расширение с .TMP на .XLS / .XLSX или .CSV.

Конечно, если вы не включили автообновление в Excel, то как это сделать?

Конечно, обратитесь за помощью к резервной копии системы Windows.

Для чего нужно уменьшать размер документов?

Как правило данные действия проводят в трех случаях:

  • Для освобождения дискового пространства. Вы сами видели сколько места можно освободить.
  • Для пересылки. Многие сайты, программы и почтовые клиенты не принимают для пересылки большие объемы и ограничены каким-то максимальным размером. Компрессия может нам в этом помочь.
  • Скорость. Чем массивнее документ, тем дольше он открывается. Иногда, если компьютер слабоват, это может привести даже к зависанию.

Ну а на этом я свою сегодняшнюю статью заканчиваю. Я думаю, что теперь у вас не возникнет никаких проблем с тем, как сжать файл пдф для пересылки или загрузки на сайт без ощутимой потери качества. А если статья вам понравилась, то не забудьте подписаться на обновления моего блога, а также поделиться материалами статьи в социальных сетях. Удачи вам. Пока-пока!

Ссылка на основную публикацию
Adblock
detector