Как убрать форматирование ячеек в excel - IT Справочник
Llscompany.ru

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

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

Условное форматирование в Excel

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

Основы условного форматирования в Excel

Используя условное форматирование, мы можем:

  • закрашивать значения цветом
  • менять шрифт
  • задавать формат границ

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

Где находится условное форматирование в Эксель?

Кнопка “Условное форматирование” находится на панели инструментов, на вкладке “Главная”:

Как сделать условное форматирование в Excel?

При применении условного форматирования системе необходимо задать две настройки:

  • Каким ячейкам вы хотите задать формат;
  • По каким условиям будет присвоен формат.

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

  • В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

  • Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
    • Правила выделения
    • Правила отбора первых и последних значений
    • Гистограммы
    • Цветовые шкалы
    • Наборы значков
  • В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:

Также, доступны следующие условия:

  1. Значения больше или равны какому-либо значению;
  2. Выделять текст, содержащий определенные буквы или слова;
  3. Выделять цветом дубликаты;
  4. Выделять определенные даты.
  • Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

  • Для присвоения формата вы можете использовать пред настроенные цветовые палитры, а также создать свою палитру. Для этого кликните по пункту:

  • Во всплывающем окне формата укажите:
    • цвет заливки
    • цвет шрифта
    • шрифт
    • границы ячеек

  • По завершении настроек нажмите кнопку “ОК”.

Ниже пример таблицы с применением условного форматирования по заданным нами параметрам. Данные с отрицательными значениями выделены красным цветом:

Как создать правило

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

  • Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:

  • Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип “Форматировать только ячейки, которые содержат”. После этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:

  • Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:

Условное форматирование по значению другой ячейки

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

Для создания условия по значению другой ячейки выполним следующие шаги:

  • Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
  • Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.

  • Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на кнопку “Изменить правило”:

  • В поле слева всплывающего окна “очистим” ссылку от знака “$”. Нажимаем кнопку “ОК”, а затем кнопку “Применить”.

  • Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на “валик” и присвоим формат остальным ячейкам:

На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

Как применить несколько правил условного форматирования к одной ячейке

Возможно применять несколько правил к одной ячейке.

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

Читать еще:  Ранг в excel

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

  • Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

  • Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

Как редактировать правило условного форматирования

Для редактирования присвоенного правила выполните следующие шаги:

  • Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
  • Перейдите в пункт меню панели инструментов “Условное форматирование”. Затем, в пункт “Управление правилами”. Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку “Изменить правило”:

  • После внесения изменений нажмите кнопку “ОК”.

Как копировать правило условного форматирования

Для копирования формата на другие ячейки выполним следующие действия:

  • Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов “Формат по образцу”.
  • Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

Как удалить условное форматирование

Для удаления формата проделайте следующие действия:

  • Выделите ячейки;
  • Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню выберите метод удаления:

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

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

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

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

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

Удаление значений в ячейках с ручным форматированием

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

Удаление значений в ячейках с условным форматированием

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

Читать еще:  Как считать градусы в excel

Видео по работе с надстройкой

Слишком много различных форматов ячеек

Это может случиться и с вами.

Работая с большой книгой в Excel в один совсем не прекрасный момент вы делаете что-то совершенно безобидное (добавление строки или вставку большого фрагмента ячеек, например) и вдруг получаете окно с ошибкой «Слишком много различных форматов ячеек»:

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

Почему это происходит

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

  • для Excel 2003 и старше — это 4000 форматов
  • для Excel 2007 и новее — это 64000 форматов

Причем под форматом в данном случае понимается любая уникальная комбинация параметров форматирования:

  • шрифт
  • заливки
  • обрамление ячеек
  • числовой формат
  • условное форматирование

Так, например, если вы оформили небольшой фрагмент листа подобным образом:

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

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

Как с этим бороться

Направлений тут несколько:

  1. Если у вас файл старого формата (xls), то пересохраните его в новом (xlsx или xlsm). Это сразу поднимет планку с 4000 до 64000 различных форматов.
  2. Удалите избыточное форматирование ячеек и лишние «красивости» с помощью команды Главная — Очистить — Очистить форматы (Home — Clear — Clear Formatting) . Проверьте, нет ли на листах строк или столбцов отформатированных целиком (т.е. до конца листа). Не забудьте про возможные скрытые строки и столбцы.
  3. Проверьте книгу на наличие скрытых и суперскрытых листов — иногда на них и кроются «шедевры».
  4. Удалите ненужное условное форматирование на вкладке Главная — Условное форматирование — Управление правилами — Показать правила форматирования для всего листа (Home — Conditional Formatting — Show rules for this worksheet) .
  5. Проверьте, не накопилось ли у вас избыточное количество ненужных стилей после копирования данных из других книг. Если на вкладке Главная (Home) в списке Стили (Styles) огромное количество «мусора»:

. то избавиться от него можно с помощью небольшого макроса. Нажмите Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert — Module и скопируйте туда код макроса:

Запустить его можно с помощью сочетания клавиш Alt+F8 или кнопкой Макросы (Macros) на вкладке Разработчик (Developer) . Макрос удалит все неиспользуемые стили, оставив только стандартный набор:

Работа со стилями форматирования ячеек Excel

Зачем нужны именованные стили

Идея именованных стилей заключается в следующем:

  1. Можно создать собственный набор стилей для форматирования, например, заголовков, итогов, обычного текста. А после применять готовые стили на другие ячейки не тратя время на воспроизведение точно такого же формата.
  2. Если изменить формат стиля, то все ячейки, к котором применен данный стиль будут автоматически отформатированы. Таким образом, можно быстро пересматривать любой формат и не тратить время на форматирование ячеек по отдельности.

Стили Excel позволяют отформатировать следующие атрибуты:

  • числовой формат (например, число, короткий формат даты, формат телефонного номера и т.п.);
  • выравнивание (по вертикали и горизонтали);
  • шрифт (название, размер, цвет и пр.);
  • граница (тип линии, цвет границы);
  • заливка (цвет фона, узор);
  • защита (защищаемая ячейка, скрытие формул).

Применение стилей к ячейкам листа

В Excel изначально установлено множество встроенных стилей. Найти их можно в меню Стили ячеек, которая расположена на вкладке Главная -> Стили.

Откроется галерея стилей (рисунок справа).

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

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

Создание новых стилей

Если встроенных стилей Excel не достаточно, то можно создать собственные стили. Это достаточно просто сделать:

  1. Выберите любую ячейку и отформатируйте ее обычным способом так как вам нравится. Форматирование этой ячейки в дальнейшим мы сохраним как именованный стиль. Вы можете использовать любое доступное форматирование из диалогового окна Форматирование ячеек.
  2. Перейдите на вкладку Главная ->Стили ячеек и выберите команду Создать стиль ячейки. Откроется диалоговое окно Стиль.
  3. Выберите Имя стиля, которое будет в дальнейшем будете использовать.
  4. Выберите опции, которые будут применяться к выбранному стилю (по умолчанию отмечены все опции). Если нет необходимости в какой то опции, например вы не хотите изменять шрифт ячейки — то снимите выбор.
  5. Нажмите кнопку OK.

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

Изменение существующих стилей

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

  1. Перейти на вкладку Главная ->Стили ячеек.
  2. Щелкнуть правой кнопкой мыши по стилю, который хотите изменить и выбрать команду Изменить.
  3. Откроется диалоговое окно Стиль, в котором указано применяемое к ячейке форматирование.
  4. Нажмите на кнопку Формат, и в появившемся диалоговом окне Формат ячеек задайте необходимое форматирование. Например, чтобы изменить размер шрифта перейдите на вкладку Шрифт, задайте нужный размер и нажмите кнопку ОК.
  5. Нажмите еще раз кнопку ОК, чтобы закрыть окно Стиль и применить форматирование к изменяемому стилю.

Перенос стилей в другую книгу

К сожалению, стили применяются к текущей книге. Т.е. если вы откроете новую книгу, то в ней будут отсутствовать новые стили, которые были ранее созданы. Чтобы повторно не создавать стили существует инструмент объединения стилей. Для этого нужно:

  1. Открыть книгу, которая уже содержит новые стили. И открыть файл, в которую нужно перенести эти стили.
  2. Перейдите к книге, которая содержит новые стили и откройте меню стилей Главная ->Стили ячеек.
  3. Выберите команду Объединить, откроется диалоговое окно Объединение стилей.
  4. В этом окне содержится перечень всех открытых книг. Выберите книгу в которую необходимо скопировать стили и нажмите кнопку OK.

Назначение и удаление форматов в Excel

Форматирование в Excel применяется для облегчения восприятия данных, что играет немаловажную роль в производительности труда.

Назначение формата

  1. Выделите ячейку (диапазон ячеек).
  2. Выберите команду «Формат»-«Ячейки» (Ctrl+1).
  3. В появившемся окне диалога (подробно окно будет рассмотрено позже) введите нужные параметры форматирования.
  4. Нажмите кнопку «Ок»

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

Удаление формата

  1. Выделите ячейку (диапазон ячеек).
  2. Выберите команду «Правка»-«Очистить»-«Форматы».
  3. Для удаления значений в ячейках надо выбрать команду «Все» подменю «Очистить».

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

Форматирование с помощью панелей инструментов

Наиболее часто используемые команды форматирования вынесены на панель инструментов «Форматирование». Чтобы применить формат с помощью кнопки панели инструментов, выделите ячейку или диапазон ячеек и затем нажмите кнопку мышью. Для удаления формата надо нажать кнопку повторно.

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

Форматирование отдельных символов

Форматирование можно применять к отдельным символам текстового значения в ячейке так же, как и ко всей ячейке. Для этого выделите нужные символы и затем в меню «Формат» выберите команду «Ячейки». Установите нужные атрибуты и нажмите кнопку «Ок». Нажмите клавишу Enter, чтобы увидеть результаты своего труда.

Применение автоформата

Автоматические форматы Excel — это заранее определенные сочетания числового формата, шрифта, выравнивания, границ, узора, ширины столбца и высоты строки.

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

  1. Введите нужные данные в таблицу.
  2. Выделите диапазон ячеек, которые необходимо форматировать.
  3. В меню «Формат» выберите команду «Автоформат». При этом откроется окно диалога.

В начало страницы

В начало страницы

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector