В этой статье вы узнаете два простых способа изменить фон ячеек в зависимости от их содержимого в последних версиях 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 () (ISBLANK в русской версии), если вы хотите изменить фон пустой ячейки или = IsError () (ISERROR в русской версии), если вам нужно найти ячейку, в которой есть формула, написанная неправильно. Поскольку в этом случае нам нужно отредактировать пустые ячейки, введите формулу = IsBlank (), затем поместите курсор между скобками и нажмите кнопку рядом с полем ввода формулы. После этих манипуляций следует вручную выделить диапазон ячеек. Также вы можете указать диапазон самостоятельно, например = IsBlank (B2: H12).
4. Нажмите кнопку «Форматировать», выберите подходящий цвет фона и сделайте все, как описано в пункте 5 раздела «Динамическое изменение цвета фона ячейки», затем нажмите «ОК». Там же вы можете увидеть, какой будет цвет ячейки. Окно будет выглядеть так.
5. Если вам нравится фон ячейки, вам нужно нажать кнопку «ОК», и изменения будут внесены в таблицу немедленно.
Статическое изменение фонового цвета специальных ячеек
В этой ситуации однажды назначенный цвет фона останется прежним, независимо от того, как изменится ячейка.
Если вам необходимо навсегда изменить специальные ячейки (пустые или содержащие ошибки), следуйте этим инструкциям:
- Выберите документ или несколько ячеек и нажмите F5, чтобы открыть окно «Переход», затем нажмите кнопку «Выбрать».
- В открывшемся диалоговом окне нажмите кнопку «Пустые» или «Пустые ячейки» (в зависимости от версии программы — русской или английской), чтобы выделить пустые ячейки.
- Если вам нужно выделить ячейки, в которых есть формулы с ошибками, вы должны выбрать пункт «Формулы» и оставить единственный флажок рядом со словом «Ошибки». Как следует из скриншота выше, вы можете выбирать ячейки с любыми параметрами, и каждая из описанных настроек доступна при необходимости.
- Наконец, вам нужно изменить цвет фона выбранных ячеек или настроить их другим способом. Для этого нужно воспользоваться описанным выше способом.
Просто помните, что сделанные таким образом изменения форматирования останутся, даже если вы заполните пробелы или измените специальный тип поля. Конечно, вряд ли кто-то захочет воспользоваться этим методом, но на практике все может быть.
Как выжать максимум из Excel?
Как заядлый пользователь Microsoft Excel, вы должны знать, что он содержит множество функций. Некоторые мы знаем и любим, в то время как другие остаются загадочными для обычного пользователя, и большое количество блоггеров пытаются пролить на них хоть какой-то свет. Но есть общие задачи, которые нужно будет выполнить каждому из нас, и Excel не предоставляет некоторые функции или инструменты для автоматизации некоторых сложных действий.
И решение этой проблемы — дополнения. Некоторые из них распространяются бесплатно, другие — за деньги. Есть много похожих инструментов, которые могут выполнять разные функции. Например, найти дубликаты в двух файлах без загадочных формул или макросов.
Если вы объедините эти инструменты с основными функциями Excel, вы можете получить отличные результаты. Например, вы можете узнать, какие цены на топливо изменились, а затем найти в файле дубликаты за последний год.
Мы видим, что условное форматирование — полезный инструмент, позволяющий автоматизировать работу с таблицами без специальных навыков. Теперь вы знаете, как по-разному заполнять ячейки в зависимости от их содержимого. Теперь остается только применить его на практике. Удачи!