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

Представляем вам гостевой пост, из которого вы узнаете, как избежать самых распространённых проблем с Excel, которые мы создаём себе сами.

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

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

Чтобы удалить все форматирование в Excel, просто сделайте следующее: Выберите ячейку или диапазон ячеек, из которых вы хотите очистить форматирование. На вкладке «Главная» в группе «Редактирование» щелкните стрелку рядом с кнопкой «Очистить». Выберите параметр «Очистить форматы».

Excel тормозит: данные, форматирование, формулы, макросы, сводные

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

Сочетание клавиш F4 повторяет последнюю команду. Этот ярлык также будет применяться, если вы решите использовать метод удаления правой кнопкой мыши. Примечание. В Microsoft Excel 2010 и 2013 есть новое сочетание клавиш для удаления листа — «Alt» + «H», «D», «S».

Как убрать лишнее форматирование ячеек?

Чтобы удалить лишнее форматирование на текущем листе, сделайте следующее:

1. На вкладке «Запрос» щелкните «Очистить избыточное форматирование ячеек».
2. Выберите, очищать ли только активный лист или все листы. После удаления лишнего форматирования нажмите «Да», чтобы сохранить изменения на листах, или «Нет», чтобы отменить.

Обратите внимание, что рекомендуется применить команду Очистить, а не Удалить (строки, столбцы). В противном случае в формулах, которые могут ссылаться на удаленные ячейки возникнет ошибка.

Excel/Эксель — 3 варианта удаление дубликатов строк через функции и без макросов

1. Используйте стандартный шрифт. Используя один и тот же шрифт для всех ячеек, вы можете уменьшить количество комбинаций форматирования.
2. Если вы используете границы на листе, используйте их последовательно. …
3. Если вы применяете узоры к ячейкам, удалите узоры. …
4. Используйте стили для стандартизации форматирования всей книги.

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

Excel тормозит: данные, форматирование, формулы, макросы, сводные

Рис. 1. Зависимость размера книги от числа листов в ней; 255 пустых листов увеличивают размер книги до 131 КВ

Начнем с того, что Microsoft принципиально переработал версию Excel в 2007 году, так что, если вы используете более раннюю версию, советую вам перейти на Excel2007 или более позднюю версию.

Свое исследование я начал с создания книги Excel, не содержащей данных, и включающей всего 1 лист. Ее размер оказался 7,6 КВ. Книга Excel может содержать не более 255 листов (рис. 1).

Вернемся к книге, содержащей 1 лист, и в ячейку А1 введем значение 1. Размер книги не изменится – 7,6 КВ. Если же единицами заполнить 10 000 ячеек в столбце А, то размер увеличится до 613 КВ (рис. 2, синяя прямая).

Рис. 2. Зависимость размера книги от числа заполненных ячеек

Любопытно, что заполнение единицами квадратной области (100х100, …, 1000х1000 ячеек) сокращает объем файла (рыжая кривая на рис. 2). Для меня оказалось неожиданным, что замена единицы на текстовый массив (я использовал около 1500 символов в каждой ячейке), увеличило размер файла лишь 5–10%. К еще меньшему эффекту приводит замена единицы на большое число с десятичной запятой.

Добавление формул почти удваивает размер файла. При этом даже простейшая ссылка на предыдущую ячейку (рис. 3), протянутая на миллион ячеек, увеличивает размер файла с 5,15 МВ до 9 МВ. Дальнейшее усложнение формул увеличивает размер файла лишь на несколько процентов.

Однако, в Excel есть особые функции, пересчитываемые при любом изменении данных, например, =СЛЧИС(). Если протянуть эту функцию на миллион ячеек, размер файла увеличится до 22 МВ!

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

Обратите внимание, что рекомендуется применить команду Очистить, а не Удалить (строки, столбцы). В противном случае в формулах, которые могут ссылаться на удаленные ячейки возникнет ошибка.

Если вы не используете макросы, сохраните книгу с расширением xlsx. Если вы не используете отдельные макросы, можете экспортировать соответствующие модули, а затем удалить их. Иногда (я такого не встречал) какие-то проблемы содержатся в рабочих модулях. Может помочь последовательный экспорт, удаление модуля, импорт.

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

[1] В том числе использованы некоторые идеи из книги Д.Холи, Р.Холи. Excel 2007. Трюки, стр. 58–60 Выделите текст, для которого нужно вернуть форматирование по умолчанию. На вкладке «Главная» в группе «Шрифт» щелкните «Очистить все форматирование». На вкладке «Главная» в группе «Шрифт» щелкните «Очистить все форматирование». На вкладке Сообщение в группе Основной текст щелкните Очистить все форматирование.

Как очистить все форматирование в Excel? Справочник по настройке компьютера

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