Foreversoft.ru

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

Очистить форматирование в excel

Как очистить ячейки в Excel? Выборочная очистка ячеек от содержимого

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

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

Как очистить ячейки стандартными средствами Excel?

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

Рассмотрим пункты меню кнопки «Очистить» более детально.

Как очистить все в выделенном диапазоне ячеек?

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

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

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

Как очистить ячейки от содержимого?

При выборе третьего пункта «Очистить содержимое» из выделенного диапазона будет удалена только та часть информации, которую вы можете видеть в строке формул, то есть удалены будут только значения ячеек.

Как очистить ячейки от примечаний?

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

Как очистить ячейки средствами VBA?

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

Использование надстройки позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. на выбор пользователя удалять значения ячеек, примечания, гиперссылки, форматы, шрифты, заливки, рамки и объединения ячеек;

3. выбирать по своему усмотрению один из четырех режимов обработки данных (смотри скриншот).

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

Надстройка легко устанавливается и запускается одним нажатием кнопки, выведенной прямо в главное меню Excel.

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).

Читать еще:  Vba excel преобразовать число в строку

Объем данных

Вернемся к книге, содержащей 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 КВ. Поэтому, создавая набор сводных таблиц, старайтесь использовать один кеш. И только если вы собираетесь применять к сводным таблицам различные настройки (например, в одной таблице группировать даты по месяцам, а в другой – по неделям), предусмотрите для каждой таблицы свой кеш.

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

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

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

«Мусор»

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

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

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

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

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

Макросы

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

Читать еще:  Excel подсчет символов в строке

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

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

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

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

Очистка файлов от формул, скрытых строк, листов и пр.

Программа «Очистка файлов» удаляет из выбранных файлов Excel:

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

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

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

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

  • 2007-2016 ExcelРецензированиеЗащитить книгу;
  • 2003 Excel:СервисЗащитаЗащитить книгу.

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

Если установлена защита на книгу и она мешает выполнению какого-либо метода очистки (обычно это удаление листов), то появится такое сообщение:

Вам необходимо ввести пароль и нажать Ок. Если пароль введен неверно или не введен вовсе, то книга будет пропущена.

При наличии пароля на листах, появится очень похожая форма:

Главное отличие: если на все листы установлен одинаковый пароль, то Вы можете ввести его и установить флажок «Для всех листов одинаковый пароль». После этого не придется вводить пароль для каждого листа отдельно.

Так же если программа обнаружит, что VBA проект защищен паролем — то появиться уже знакомая форма:

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

Каждый параметр удаления имеет свои дополнительные настройки, которые скрываются за пунктом Расширенные опции

Читать еще:  Неизвестный формат файла excel

Если вкратце об этих пунктах:

  • для Замены условного форматирования можно выбрать какие условия заменять, а какие нет (границы ячеек, заливка, шрифт и т.п.)
  • для макросов можно указать какие типы модулей удалять(стандартные, модули форм, модули классов, модули листов и книг)
  • для скрытых строк и столбцов — удалять скрытые группировкой строки/столбцы
  • для скрытых листов — отдельно удалять листы скрытые и очень скрытые
  • для объектов указываются типы удаляемых объектов, включая примечания в ячейках
  • возможность указать, создавать ли копии обработанных файлов(чтобы не испортить оригиналы), с возможностью указания постфикса(слова, которое будет добавлено в конец каждого файла) и папки для сохранения копий

Так же в Расширенных опциях расположены настройки создания копий файлов и настройка доступа к проекту VBA файлов.

Бесплатная ДЕМО-версия программы содержит в себе два ограничения — обрабатывать можно только файлы, размер которых не превышает 100Кб и нельзя за один раз обработать более одного файла. Считаю этого вполне достаточно для того, чтобы протестировать и понять — нужна эта программа или нет.


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

Очистка данных: набор основных инструментов очистки больших массивов данных 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. Нажмите кнопку «Применить» > Готово. Каждой преобразованной ячейке назначен текстовый формат, также в верхнем левом углу появится обозначение, что число сохранено как текст.

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

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