Содержание:
- Динамическое изменение цвета фона ячейки
- Как сохранить цвет ячейки неизменным, даже если значение изменится?
- Выбрать все ячейки, содержащие определенное условие
- Изменение фона выбранных ячеек через окно «Формат ячеек»
- Редактирование цвета фона для специальных ячеек (пустых или с ошибками при написании формулы)
- Как получить максимальную отдачу от Excel?
В этой статье вы узнаете два простых способа изменить фон ячеек в зависимости от их содержимого в последних версиях Excel. Вы также поймете, какие формулы использовать для изменения оттенка ячеек или ячеек, где формулы написаны неправильно, или где информация вообще отсутствует.
Всем известно, что редактирование фона простой ячейки – простая процедура. Нажмите «Цвет фона». Но что, если вам нужна коррекция цвета на основе содержимого определенной ячейки? Как я могу сделать, чтобы это происходило автоматически? Далее следует ряд полезной информации, которая позволит вам найти правильный способ выполнения всех этих задач.
Динамическое изменение цвета фона ячейки
Сложность задачи : у вас есть таблица или набор значений, и вам необходимо отредактировать цвет фона ячеек в зависимости от того, какое число там введено. Вам также необходимо убедиться, что оттенок реагирует на изменение значений.
Решения: Для этой задачи предусмотрена функция «условного форматирования» Excel, позволяющая раскрашивать ячейки с числами больше X, меньше Y или между X и Y.
Допустим, у вас есть набор товаров в разных штатах с указанием их цен, и вам нужно знать, какие из них стоят дороже 3,7 доллара. Поэтому мы решили выделить красным цветом те продукты, стоимость которых превышает это значение. А клетки, имеющие аналогичное или большее значение, было решено окрасить в зеленый оттенок.
Примечание: Скриншот сделан в 2010 версии программы. Но это ни на что не влияет, так как последовательность действий одинакова, независимо от того, какой версией – последней или нет – пользуется человек.
Итак, вот что вам нужно сделать (пошагово):
1. Выделите ячейки, в которых нужно изменить оттенок. Например, диапазон $B$2:$H$10 (названия столбцов и первый столбец, в котором указаны названия штатов, исключены из выборки).
2. Нажмите на «Главная» в группе "стиль". Там будет предмет «Условное форматирование». Там же нужно выбрать «Новое правило». В английской версии Excel последовательность действий следующая: «Главная», «Группа Стили», «Условное форматирование > Новое правило».
3. В открывшемся окне поставьте галочку «Форматировать только те ячейки, которые содержат» («Форматировать только те ячейки, которые содержат» в английской версии).
4. Внизу этого окна под надписью «Форматировать только ячейки, соответствующие следующему условию» (Форматировать только ячейки) можно назначить правила, по которым будет выполняться форматирование. Мы выбрали формат указанного значения в ячейках, который должен быть больше 3.7, как видно на скриншоте:
5. Далее нажмите на кнопку "Формат". Появится окно с областью выбора цвета фона слева. Но перед этим нужно открыть вкладку "Наполнять" ("Наполнять"). В данном случае это красный цвет. После этого нажмите на кнопку «ОК».
6. Потом вы вернетесь к окну «Новое правило форматирования», но уже внизу этого окна вы можете предварительно просмотреть, как будет выглядеть эта ячейка. Если все в порядке, нужно нажать на кнопку «ОК».
В результате вы получите что-то вроде этого:
Далее нам нужно добавить еще одно условие, то есть изменить фон ячеек со значениями меньше 3.45 на зеленый. Для выполнения этой задачи необходимо нажать на «Новое правило форматирования» и повторите шаги выше, только условие нужно установить как «меньше или эквивалентно» (в английском варианте «меньше или равно», а затем напишите значение. В конце нужно нажать кнопку «ОК».
Теперь таблица отформатирована таким образом.
Он отображает самые высокие и самые низкие цены на топливо в различных штатах, и вы сразу можете определить, где ситуация наиболее оптимистична (в Техасе, конечно).
Рекомендация: При необходимости можно использовать аналогичный метод форматирования, редактируя не фон, а шрифт. Для этого в появившемся на пятом этапе окне форматирования необходимо выбрать вкладку "Шрифт" и следуйте подсказкам в окне. Все интуитивно понятно, и разобраться сможет даже новичок.
В результате у вас получится такая таблица:
Как сохранить цвет ячейки неизменным, даже если значение изменится?
Сложность задачи : Вам нужно раскрасить фон так, чтобы он никогда не менялся, даже если фон изменится в будущем.
Решения: найти все ячейки с определенным номером, используя функцию Excel "Найти все" «Найти все» или дополнение «Выбрать специальные ячейки» («Выбрать специальные ячейки»), а затем отредактируйте формат ячейки с помощью функции "Формат ячеек" ("Формат ячеек").
Это одна из тех нечастых ситуаций, которые не описаны в руководстве по Excel, да и в Интернете решение данной проблемы можно найти довольно редко. Что неудивительно, поскольку задача эта нестандартная. Если вы хотите постоянно редактировать фон, чтобы он никогда не менялся, пока он не будет настроен вручную пользователем программы, вы должны следовать инструкциям выше.
Выбрать все ячейки, содержащие определенное условие
Существует несколько возможных методов, в зависимости от того, какое конкретное значение необходимо найти.
Если вам необходимо обозначить ячейки с определенным значением специальным фоном, вам нужно перейти на вкладку «Главная» , а затем выбрать «Найти и выбрать» – «Найти».
Введите необходимые значения и нажмите "Найти все".
Документи: Вы можете нажать на кнопку "Параметры" справа, чтобы воспользоваться некоторыми дополнительными настройками: где искать, как просматривать, соблюдать ли прописные и строчные буквы и так далее. Вы также можете написать дополнительные символы, например звездочку (*), чтобы найти все строки, содержащие эти значения. Если вы используете знак вопроса, вы можете найти любой отдельный символ.
В нашем предыдущем примере, если бы мы хотели найти все цены на топливо в диапазоне от 3,7 до 3,799 долларов США, мы могли бы уточнить наш поисковый запрос.
Теперь выберите любое из значений, которые нашла программа внизу диалогового окна, и нажмите на одно из них. После этого нажмите комбинацию клавиш «Ctrl-A», чтобы выбрать все результаты. Далее нажмите на кнопку «Закрыть».
Вот как можно выбрать все ячейки с определенными значениями с помощью функции «Найти все». В нашем примере нам нужно найти все цены на топливо выше $3,7, и, к сожалению, Excel не позволяет сделать это с помощью функции «Найти и заменить».
Здесь на свет выходит «бочка меда», потому что есть еще один инструмент, который поможет в таких сложных задачах. Это называется «Выбрать специальные ячейки». Это дополнение (которое необходимо установить в Excel отдельно) поможет:
- найти все значения в определенном диапазоне, например между -1 и 45,
- получить максимальное или минимальное значение в столбце,
- найти строку или диапазон,
- поиск ячеек по цвету фона и многое другое.
После установки дополнения просто нажмите кнопку «Выбрать по значению» («Выбрать по значению»), а затем уточнить поисковый запрос в окне дополнения. В нашем примере мы ищем числа больше 3,7. Нажимать "Выбирать" («Выбрать»), и через секунду вы получите такой результат:
Если вас заинтересовало дополнение, вы можете скачать пробную версию по адресу ссылку.
Изменение фона выбранных ячеек через окно «Формат ячеек»
Теперь, после того как все ячейки с определенным значением были выбраны одним из описанных выше способов, осталось указать для них цвет фона.
Для этого вам нужно открыть окно «Формат ячейки»нажав клавишу Ctrl+1 (также можно щелкнуть правой кнопкой мыши по выделенным ячейкам и левой кнопкой мыши по пункту «форматирование ячеек») и настроить нужное вам форматирование.
Мы выберем оранжевый оттенок, но вы можете выбрать любой другой.
Если вам нужно отредактировать цвет фона без изменения других параметров внешнего вида, вы можете просто нажать на «цветовая заливка» и выберите цвет, который идеально вам подходит.
В результате получается такая таблица:
В отличие от предыдущего метода, здесь цвет ячейки не изменится, даже если значение будет отредактировано. Это можно использовать, например, для отслеживания динамики товаров в определенной ценовой группе. Их стоимость изменилась, но цвет остался прежним.
Редактирование цвета фона для специальных ячеек (пустых или с ошибками при написании формулы)
Как и в предыдущем примере, пользователь имеет возможность редактировать цвет фона специальных ячеек двумя способами. Есть статические и динамические варианты.
Применение формулы для редактирования фона
Здесь цвет ячейки будет редактироваться автоматически в зависимости от ее значения. Этот метод очень помогает пользователям и востребован в 99% ситуаций.
В качестве примера можно использовать предыдущую таблицу, но теперь часть ячеек будет пуста. Нам необходимо определить, какие из них не содержат никаких показаний, и отредактировать цвет фона.
1. На вкладке «Главная» вам нужно нажать на «Условное форматирование» -> «Новое правило» (то же, что и в шаге 2 первого раздела «Динамическое изменение цвета фона»).
2. Далее вам необходимо выбрать пункт «Используйте формулу, чтобы определить…».
3. Введите формулу =Пустой() (ISBLANK в версии), если вы хотите отредактировать фон пустой ячейки, или =ЕстьОшибка() (ISERROR в версии), если нужно найти ячейку, где ошибочно написана формула. Так как в данном случае нам необходимо редактировать пустые ячейки, вводим формулу =Пустой(), а затем поместите курсор в круглые скобки и нажмите кнопку рядом с полем ввода формулы. После этих манипуляций диапазон ячеек следует выделить вручную. Кроме того, вы можете указать диапазон самостоятельно, например, =Пусто(B2:H12).
4. Нажмите на кнопку «Формат» и выберите подходящий цвет фона и сделайте все, как описано в пункте 5 раздела «Динамическое изменение цвета фона ячейки», после чего нажмите «ОК». Там же можно увидеть, какого цвета будет ячейка. Окно будет выглядеть примерно так.
5. Если вам нравится фон ячейки, необходимо нажать на кнопку «ОК», и изменения в таблицу сразу же будут внесены.
Статическое изменение цвета фона специальных ячеек
В этой ситуации после присвоения цвет фона будет оставаться таким, независимо от того, как изменяется ячейка.
Если вам необходимо навсегда изменить специальные ячейки (пустые или содержащие ошибки), следуйте этим инструкциям:
- Выделите документ или несколько ячеек и нажмите F5, чтобы открыть окно «Перейти», а затем нажмите кнопку "Выделять".
- В открывшемся диалоговом окне выберите кнопку «Пробелы» или «Пустые ячейки» (в зависимости от версии программы – или английскую), чтобы выделить пустые ячейки.
- Если вам необходимо выделить ячейки, содержащие формулы с ошибками, следует выбрать пункт «Формулы» и оставьте единственную галочку возле слова «Ошибки». Как следует из скриншота выше, ячейки можно выбирать по любым параметрам, и каждая из описанных настроек доступна при необходимости.
- Наконец, вам нужно изменить цвет фона выделенных ячеек или настроить их каким-либо другим способом. Для этого используйте метод, описанный выше.
Просто помните, что изменения форматирования, внесенные таким образом, сохранятся, даже если вы заполните пробелы или измените специальный тип ячейки. Конечно, вряд ли кто-то захочет воспользоваться этим методом, но на практике всякое бывает.
Как получить максимальную отдачу от Excel?
Как активные пользователи Microsoft Excel, вы должны знать, что он содержит множество функций. Некоторые из них мы знаем и любим, а другие остаются загадочными для рядового пользователя, и большое количество блоггеров пытаются пролить на них хоть немного света. Но есть общие задачи, которые придется выполнять каждому из нас, и Excel не предоставляет каких-то функций или инструментов для автоматизации некоторых сложных действий.
И решение этой проблемы — дополнения (аддоны). Некоторые из них распространяются бесплатно, другие – за деньги. Существует множество подобных инструментов, которые могут выполнять разные функции. Например, найти дубликаты в двух файлах без загадочных формул или макросов.
Если вы объедините эти инструменты с основными функциями Excel, вы сможете добиться очень хороших результатов. Например, вы можете узнать, какие цены на топливо изменились, а затем найти в файле дубликаты за прошедший год.
Мы видим, что условное форматирование — это удобный инструмент, позволяющий автоматизировать работу с таблицами без каких-либо специальных навыков. Теперь вы знаете, как заполнять ячейки несколькими способами в зависимости от их содержимого. Теперь осталось только применить это на практике. Удачи!