Foreversoft.ru

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

Очистка данных в excel

Очистка данных: набор основных инструментов очистки больших массивов данных Excel

Сколько времени у вас занимает очистка данных перед тем, как можно будет приступить к анализу? Лишние пробелы, непечатаемые символы, разный регистр текста… Все это неизбежно при импорте данных в Excel или объединении нескольких книг. Тогда возникает вопрос: как быстро очистить большие таблицы, когда поиск, удаление вручную или копирование функций Excel не продуктивно?

Надстройка «Очистка данных» поможет очистить большие массивы данных в секунды:

  • Удаление лишних пробелов, пробелов в начале и в конце ячейки
  • Удаление переносов строк и непечатаемых знаков
  • Изменение регистра текста во всём диапазоне
  • Преобразование чисел в текстовом формате в полноценные числа
  • Преобразование чисел в текстовые значения

Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

Добавить «Очистку данных» в Excel 2019, 2016, 2013, 2010

Подходит для: Microsoft Excel 2019 – 2010, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

Внимание: отмена последнего действия (UNDO) невозможна. Рекомендуем сохранять резервную копию файлов или использовать «Контроль версий» XLTools для отслеживания изменений.

Как удалить пробелы в начале и в конце строки или все лишние пробелы

  1. Выберите диапазон > Нажмите кнопку «Очистка данных».
  2. На панели слева выберите одну или обе операции:
    • Удалить пробелы в начале/в конце строки
    • Удалить все лишние пробелы
  3. В нижней части панели нажмите кнопку «Применить» > Готово, все ненужные пробелы в текстовых строках удалены.

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

  1. Выберите диапазон > Нажмите кнопку «Очистка данных».
  2. На панели слева выберите «Удалить переносы строк внутри ячеек».
  3. Нажмите кнопку «Применить» > Готово, все переносы строк (переносы текста на новую строку внутри ячейки) удалены.

Как удалить непечатаемые знаки в ячейках

  1. Выберите диапазон > Нажмите кнопку «Очистка данных».
  2. На панели слева выберите «Удалить непечатаемые знаки».
  3. Нажмите «Применить» > Готово, все непечатаемые символы удалены.

Как изменить регистр текста в ячейках

  1. Выберите диапазон > Нажмите кнопку «Очистка данных».
  2. На панели слева выберите «Изменение регистра букв» и выберите нужный вариант:
    • Каждое Слово С Заглавной Буквы
    • Предложение с заглавной буквы
    • все буквы в нижнем регистре
    • ВСЕ БУКВЫ В ВЕРХНЕМ РЕГИСТРЕ
  3. Нажмите кнопку «Применить» > Готово, регистр текста унифицирован во всём диапазоне.

Как преобразовать числа (сохранённые как текст) в полноценные числа

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

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

Как преобразовать числа в текстовые значения

Если вам нужно, чтобы числовые значения в ячейке обрабатывались как текст, вы можете добавить апостроф перед значением:

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

Появились вопросы или предложения? Оставьте комментарий ниже.

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

Большие файлы Excel часто добавляют хлопот пользователям из-за увеличения времени их обработки. Иногда проблемы становятся настолько острыми, что с файлом невозможно работать. Несколько лет назад я предложил два варианта, как бороться с мусорными объектами, которые могут появляться при импорте данных из 1С, SAP и других программ: Excel тормозит. Что делать? и Excel «тормозит». Что делать? Дубль 2. Заметки пользуются большой популярностью, при этом в комментариях задают вопросы, напрямую не связанные с паразитными объектами. А около года назад, когда я читал книгу Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel, то обратил внимание на то, как много внимания автор уделяет скорости работы формул, постоянно сравнивая время выполнения обычных формул, формул баз данных и формул массива. Так и родилась идея этой заметки – показать, как различные элементы книги Excel влияют на ее размер. [1]

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

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

Возможности Excel по работе с большими файлами и скорость работы с ними в значительной степени зависят от объема оперативной памяти. Например, я ощутил существенную прибавку в этом аспекте увеличив память в своем моноблоке с 2 до 4 ГВ. А в одном из комментариев к заметке «Excel тормозит. Что делать?» упоминается, что выделение 14 000 объектов заняло 12 с при 32 ГВ оперативки (я на своем моноблоке с 2 ГВ не смог выделить и за полчаса).

Число листов

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

Объем данных

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

Кстати, для заполнения ячеек можно воспользоваться следующим методом. Набираем Ctrl+G, и в окне Переход в поле Ссылка вводим адрес А10000, и жмем Ok. Мы перешли к последней ячейке заполняемого диапазона. Вводим в ячейку А10000 значение 1. Возвращаемся к ячейке А1. Удерживая Shift, нажимаем на цифровой клавиатуре кнопку End и затем стрелку вниз. Выбран диапазон А1:А10000. Не снимая выделение, вводим единицу (она появится в ячейке А1) и, по-прежнему не убирая выделение, жмем Ctrl+Enter. 10 000 ячеек заполнились единицами.

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

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

Формулы и функции

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

Рис. 3. Ссылка вместо константы

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

Сводные таблицы

Исходные данные занимали прямоугольную область: 10 столбцов * 10 000 строк (рис. 4). Файл весил 569 КВ. Добавление сводной таблицы (рис. 5) увеличило размер файла до 750 КВ (на 30%). Форматирование сводной таблицы с использованием стандартных стилей (доступны по меню Работа со сводными таблицами –> Конструктор –> Стили сводной таблицы) практически не изменяет размер файла. Добавление второй сводной таблицы на основе того же кеша увеличило размер файла до 903 КВ (подробнее об использование кеша см. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Добавление второй сводной таблицы на основе нового кеша увеличило файл с 750 до 1 060 КВ. Поэтому, создавая набор сводных таблиц, старайтесь использовать один кеш. И только если вы собираетесь применять к сводным таблицам различные настройки (например, в одной таблице группировать даты по месяцам, а в другой – по неделям), предусмотрите для каждой таблицы свой кеш.

Читать еще:  Excel vba list

Рис. 4. Исходные данные

Рис. 5. Сводная таблица

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

«Мусор»

Иногда (каким-то непостижимым образом) на листе Excel вводятся ненужные данные в «далекой» ячейке. На одном листе Excel2013 может быть 1 048 576 строк и 16 384 столбцов. Однажды мне встретился файл, в котором было введена единица в предельной ячейке (рис. 6). К счастью, современные версии Excel легко справляются с этой ситуацией, и размер файлов от таких ошибок не распухает, чего не скажешь о более ранних версиях. Увидеть, что на вашем листе есть «мусор» можно с помощью бегунка, который будет иметь неестественно большое поле для пробега (см. нижнюю часть рис. 6).

Рис. 6. «Мусорное» значение в последней ячейке

Форматирование

Еще одна причина распухания файла — наложение форматирования на столбцы или строки целиком, а не только на используемый диапазон данных. Чтобы исправить эту ошибку, нужно исключить излишнее форматирование. Для начала вручную найдите последнюю ячейку с фактическими данными. Если ваш лист не содержит «мусора», то в последнюю ячейку можно попасть, прокрутив бегунок по вертикали и горизонтали в крайние положения (но не более того). Далее выделите горизонтальную область, лежащую ниже данных, и содержащую форматирование, и пройдите по меню Главная –> Редактирование –> Очистить –> Очистить всё. Повторите операцию для вертикальной области, лежащей справа от области с данными.

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

Макросы

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

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

Возможно, ваша книга или отдельные листы повреждены. К сожалению, определение точки повреждения требует выполнить процесс исключения вручную. Сохраните резервную копию книги. Чтобы гарантировать, что вы ничего не пропустите, отобразите все скрытые листы. Для этого щелкните правой кнопкой мыши на ярлычке любого листа и выберите команду Показать. Если эта команда недоступна, значит у вас нет скрытых листов. Теперь, когда все листы видимы, начните с самого левого и по очереди обрабатывайте все листы. Для каждого листа: удалите лист, сохраните книгу и проверьте размер файла, например, пройдя по меню Файл –> Сведения, область Свойства –> Размер. Если размер файла существенно уменьшился, учитывая количество данных на удаленном листе, то вы, вероятно, нашли место повреждения.

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

[1] В том числе использованы некоторые идеи из книги Д.Холи, Р.Холи. Excel 2007. Трюки, стр. 58–60

Первые 10 способов очистки данных

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

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

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

Для очистки данных нужно выполнить следующие основные действия:

Импортируйте данные из внешнего источника.

Создайте резервную копию исходных данных в отдельной книге.

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

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

Затем выполните задачи, требующие операций со столбцами. Для работы со столбцами нужно выполнить следующие действия:

Вставьте новый столбец (B) рядом с исходным (A), который требуется очистить.

Добавьте формулу, которая будет преобразовывать данные, вверху нового столбца (B).

Заполните вниз формулу в новом столбце (B). В таблице Excel будет автоматически создан вычисляемый столбец с заполненными вниз значениями.

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

Удалите исходный столбец (A). При этом новый столбец B станет столбцом A.

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

Инструкции по использованию команды Заполнить.

Инструкции по созданию таблицы Excel и добавлению или удалению столбцов и вычисляемых столбцов.

Несколько способов автоматизировать повторяющиеся задачи с помощью макроса.

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

Инструкции по исправлению слов с ошибками на листе.

Инструкции по использованию настраиваемых словарей.

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

Описание двух тесно связанных процедур: фильтрации по уникальным строкам и удаления повторяющихся строк.

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

Инструкции по использованию команды Найти и нескольких функций по поиску текста.

Инструкции по использованию команды Заменить и нескольких функций для удаления текста.

Инструкции по использованию диалоговых окон Найти и Заменить.

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

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

Читать еще:  Как нарисовать линию в excel

Инструкции по использованию трех функций «Регистр».

Преобразует все прописные буквы в текстовой строке в строчные.

Первая буква в строке текста и все первые буквы, следующие за знаками, отличными от букв, преобразуются в прописные (верхний регистр). Все прочие буквы в тексте преобразуются в строчные (нижний регистр).

Преобразует все буквы текста в прописные.

Иногда текстовые значения содержат начальные, конечные либо последовательные пробелы (значения 32 и 160 кодировки Юникод) или непечатаемые знаки (значения Юникода с 0 по 31, 127, 129, 141, 143, 144 и 157). Наличие таких знаков может иногда приводить к непредсказуемым результатам при сортировке, фильтрации или поиске. Например, во внешнем источнике данных пользователь может сделать опечатку, нечаянно добавив лишний пробел; импортированные из внешних источников текстовые данные также могут содержать непечатаемые знаки внутри текста. Поскольку такие знаки незаметны, неожиданные результаты бывает трудно объяснить. Чтобы удалить эти ненужные знаки, можно использовать сочетание функций СЖПРОБЕЛЫ, ПЕЧСИМВ и ПОДСТАВИТЬ.

Инструкции по удалению всех пробелов и непечатаемых знаков Юникода.

Возвращает числовой код первого знака в текстовой строке.

Удаляет из текста первые 32 непечатаемых знака в 7-битном коде ASCII (значения с 0 по 31).

Удаляет из текста знак пробела в 7-битной кодировке ASCII (значение 32).

Функцию ПОДСТАВИТЬ можно использовать для замены символов Юникода с более высокими значениями (127, 129, 141, 143, 144, 157 и 160) знаками 7-битной кодировки ASCII, для которых предназначены функции СЖПРОБЕЛЫ и ПЕЧСИМВ.

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

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

Преобразует число в текст и добавляет обозначение денежной единицы.

Преобразует значение в текст в заданном числовом формате.

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

Преобразует строку текста, отображающую число, в число.

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

Описание системы дат в Office Excel.

Инструкции по преобразованию значений времени в различные единицы.

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

Возвращает целое число, представляющее определенную дату. Если до ввода этой функции форматом ячейки был «Общий», результат будет отформатирован как дата.

Преобразует дату, представленную в виде текста, в порядковый номер.

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

Возвращает время в виде десятичного числа, представленное текстовой строкой. Значение времени — это десятичное число в интервале от 0 до 0,99999999, представляющее время от 0:00:00 до 23:59:59.

После импорта данных из внешнего источника в общую задачу можно объединить несколько столбцов в один или разделить один столбец на несколько столбцов. Например, может потребоваться разделить столбец, содержащий полное имя, в имя и фамилию. Кроме того, может потребоваться разделить столбец, содержащий поле адреса, в отдельные столбцы «улица», «город», «регион» и «почтовый индекс». Кроме того, обратное может быть верно. Возможно, потребуется объединить первый столбец имени и фамилии в столбец с полным именем или объединить отдельные столбцы адреса в один столбец. Дополнительные распространенные значения, для которых может потребоваться объединение в один столбец или разделение на несколько столбцов, включают коды продуктов, пути к файлам и IP-адреса.

Типичные примеры объединения значений из нескольких столбцов.

Инструкции по использованию мастера для разделения столбцов с учетом различных часто используемых разделителей.

Инструкции по использованию функций ЛЕВСИМВ, ПСТР, ПРАВСИМВ, ПОИСК и ДЛСТР для разделения столбца имени на несколько столбцов.

Инструкции по использованию функции СЦЕПИТЬ, оператора & (амперсанда) и мастера текстов.

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

Соединяет несколько текстовых строк в одну строку.

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

Возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот.

Иногда администраторы баз данных используют Office Excel для поиска и исправления ошибок соответствия, когда объединяются несколько таблиц. Этот процесс может включать сверку двух таблиц на различных листах, например для того, чтобы просмотреть все записи в обеих таблицах или сравнить таблицы и найти строки, которые не согласуются.

Часто используемые способы поиска данных с помощью функций поиска.

Возвращает значение из строки, столбца или массива. Функция ПРОСМОТР имеет две синтаксические формы: векторную и форму массива.

Ищет значение в первой строке таблицы или массива и возвращает значение, находящееся в том же столбце в заданной строке таблицы или массива.

Ищет значение в первом столбце таблицы и возвращает значение в той же строке из другого столбца таблицы.

Возвращает значение или ссылку на значение из таблицы или диапазона. Функция ИНДЕКС имеет две формы: ссылочную и форму массива.

Возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. Функция ПОИСКПОЗ используется вместо функций типа ПРОСМОТР, если нужна позиция элемента в диапазоне, а не сам элемент.

Данная функция возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов.

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

Примечание: Корпорация Майкрософт не поддерживает сторонние продукты.

Как уменьшить размер файла и ускорить его

Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, то попробуйте пробежаться по описанным ниже пунктам — возможно один или несколько из них укоротят вашего «переростка» до вменяемых размеров и разгонят его «тормоза» 🙂

Проблема 1. Используемый диапазон листа больше, чем нужно

Если ваша таблица занимает 5 на 5 ячеек, то это отнюдь не означает, что Excel запоминает при сохранении этого файла только 25 ячеек с данными. Если вы в прошлом использовали какие-либо ячейки на этом листе, то они автоматически включаются в используемый диапазон (так называемый Used Range), который и запоминается при сохранении книги. Проблема в том, что при очистке используемых ячеек Excel далеко не всегда автоматически исключает их из используемого диапазона, т.е. начинает запоминать в файле больше данных, чем реально имеется.

Проверить это просто – нажмите на клавиатуре сочетание клавиш Ctrl+End и посмотрите куда переместится активная ячейка. Если она прыгнет на фактическую последнюю ячейку с данными на листе – отлично. А если вдруг ускачет сильно правее и/или ниже «в пустоту» – дело плохо: все эти ненужные пустые ячейки Excel тоже запоминает внутри файла.

Лечится это, тем не менее, достаточно легко:

  1. Выделите первую пустую строку под вашей таблицей
  2. Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
  3. Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows) .
  4. Повторите то же самое со столбцами.
  5. Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а «в пустоту» ниже и/или правее.
  6. Сохраните файл (обязательно, иначе изменения не вступят в силу!)
Читать еще:  Язык vba в excel

Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.

Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB

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

Начиная с верии Excel 2007 Microsoft ввела новые форматы сохранения файлов, использование которых заметно облегчает жизнь и — ваши файлы:

  • XLSX — по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
  • XLSM — то же самое, но с поддержкой макросов.
  • XLSB — двоичный формат, т.е. по сути — что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер — минимален.

Вывод: всегда и везде, где можно, переходите от старого формата XLS (возможно, доставшегося вам «по наследству» от предыдущих сотрудников) к новым форматам.

Проблема 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 и особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу. Для удаления:

  1. нажмите Alt+F11, чтобы войти в редактор Visual Basic
  2. найдите окно Project Explorer’а (если его не видно, то выберите в меню View — Project Explorer)
  3. удалите все модули и все формы (правой кнопкой мыши — 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) .

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