Нетология - это именитый онлайн-университет, который имеет большое количество направлений курсов. Высококвалифицированные преподаватели раскрывают сильные стороны своих учеников, а акже помогают им выбрать правильный вектор развития.
Онлайн курсы Eduson Academy предлагают своим студентам сделать большой рывок по карьерной лестнице в 2022 году. Каталог обширен и предоставляет специализацию по разным направлениям. Интернет-ресурс взаимодействует с известными российскими брендами.
SkillFactory - один из самых известных сервисов по онлайн обучению программистов и других сотрудников в сфере IT технологий. На портале проходят курсы люди из более 40 стран мира. Выпускники сервиса работают в солидных компаниях с мировым именем.
Skillbox - это онлайн платформа, на которой ежедневно получают знания более 400 000 человек. Один из крупнейших поставщиков курсов в рунете. Более 50 разных направлений доступно прямо сейчас. Они сотрудничают с крупнейшими компаниями в России, часто предлагают лучших студентов в качестве сотрудников.
Качественные онлайн-курсы для IT-специалистов. Отличается глубоким погружением в материал, индивидуальным подходом и профессиональными преподавателями.
Учебный центр АПОК (Академия Профессионального Образования Кадров) — это ведущая организация в области профессиональной переподготовки и повышения квалификации специалистов по всей России. Центр предоставляет обширные возможности для обучения, предлагая программы как для частных лиц, так и для организаций.
В этой статье вы найдете 13 примеров того, как изменить цвет фона ячейки в зависимости от значения в Excel 2016, 2013 и 2010. Кроме того, вы узнаете, как использовать формулы Excel для изменения цвета пустых ячеек с ошибками или ячеек в формулах.
Всем известно, что изменить цвет фона отдельной ячейки или диапазона данных в Microsoft Excel легко, просто нажав кнопку «Цвет заливки». Но что, если вы хотите изменить цвет фона всех ячеек с определенным значением? Кроме того, что, если вы хотите, чтобы он изменялся автоматически при изменении значения ячейки? Далее в этой статье вы найдете ответы на эти вопросы и несколько полезных советов, которые помогут вам выбрать правильный метод для каждого конкретного вида деятельности.
А теперь на простых примерах посмотрим, как в Excel можно автоматически выделять интересующие нас данные.
Ваши индикаторы в электронной таблице могут быть более наглядными, если вы сопроводите их графиком. Просто выберите нужный стиль, как показано на изображении ниже.
Думаю, вы сами откроете для себя настройки, несмотря на их большое количество. Но согласитесь, получается очень познавательно и красиво. Гораздо проще ориентироваться в большом количестве чисел.
Еще одна полезная функция — использование цветовой шкалы. Здесь вы можете установить, как фоновая тень будет меняться от минимальной к максимальной.
Чем насыщеннее и темнее фон, тем больше цифра в таблице. Как видите, легко определить, какие продукты и когда продаются лучше всего.
А вот еще один интересный вариант, с помощью которого можно наглядно показать динамику изменения показателей. Например, у нас есть отчет о статусе выполнения некоторых проектов или мероприятий. Как обычно, прогресс отображается в процентах. Вы можете попытаться сделать эти проценты более заметными.
Затем вернитесь в меню. Думаю, мы уже знаем, как это делать. Затем выберите первый элемент (1) — формат на основе значений. Затем в меню «Стиль формата» (2) найдите пункт «Набор значков». Выбирайте тот набор, который вам больше всего нравится. (3) Теперь давайте установим правила для каждого значка. Думаю, ничего сложного для вас здесь не будет. Я установил пределы 25,50 и 75% (4). Здесь вы также можете выбрать индивидуальный дизайн каждой иконки.
Результат вы можете увидеть на фото выше. При желании вы можете установить соответствующий флажок, чтобы отображался только значок без номера.
Что, если нам нужно изменить цвет ячейки в зависимости от того, содержит ли она текст или число? Действуем по предыдущей схеме.
Выберите A1: B10, затем щелкните вкладку «Главная» — — «Создать правило» — «Использовать формулу.
Это будет выглядеть так:
= ТЕКСТ (A1)
Функция ETEXT проверяет только наличие текста. Если условие выполнено, ячейка будет окрашена.
Просто, не правда ли?
Как условно раскрасить ячейку цифрой? Нам нужно изменить цвет ячейки в зависимости от значения в конкретной ячейке таблицы Excel.
Конечно, действуем по стандартной схеме: выбираем область форматирования, затем используем выражение:
= СЧЁТЕСЛИ (A3; $ A $ 1) = 1
Содержимое A1 заменяется как условие в формуле СЧЁТЕСЛИ. Результатом может быть только ноль или один. И один соответствует логическому ИСТИННО. Именно тогда применяется установленный нами формат (в нашем случае белый шрифт на синем фоне). Итак, мы перебираем все содержимое нашего диапазона.
Этот выбор может быть сделан по столбцам, чтобы привлечь внимание к определенным числам (например, проценту от плана продаж). И что немаловажно, условие можно менять динамически — для этого не нужно исправлять никакую формулу, достаточно изменить текст условия в нужной ячейке таблицы.
С помощью этой функции вы также можете организовать своего рода поиск слов в таблице. Чаще всего ищем фамилию по начальным буквам. Мы создаем позиции с текстом, начинающимся с определенных выделенных букв.
Определим, где мы будем выделять найденное — в A3: F19. Далее в правиле условного форматирования мы используем выражение:
= ВЛЕВО (A3; DLSTR ($ A $ 1)) = $ A $ 1
Объясняем, как это работает. A1 содержит поисковый запрос — первые буквы слова. Используя DLSTR ($ A $ 1), мы определяем длину этой строки, то есть сколько в ней букв. Затем, используя функцию LEFT, в каждой из ячеек нашего диапазона мы берем рассчитанное ранее количество букв, начиная с первой слева, и сравниваем с A1. То есть «отрезаем» кусок такой же длины, как A1 слева, и сравниваем с ним. Если есть совпадение, выберите его.
Как видите, это совсем не сложно, но может быть очень полезно.
Немного усложним предыдущую задачу. Начальная процедура такая же, как и раньше. Хитрость заключается в применении специальной формулы, с помощью которой мы выберем текст, состоящий более чем из 2-х слов.
Правило условного форматирования будет таким:
= DLSTR (ОБРЕЗАТЬ (A1)) — DLSTR (ПОДСТАВИТЬ (A1; «»; «»))> 0
Посмотрим, как это работает. Логика здесь несложная:
Это именно то, что нам нужно. Давайте раскрасим его.
Еще одна маленькая хитрость для проверки правильности ввода данных. Довольно распространенная проблема — лишние пробелы в начале или конце текста. Это затрудняет поиск нужной информации, ее группировку и сравнение данных. Дополнительное пространство невидимо, но на самом деле это уже другой текст. В результате может показаться, что внешне они похожи, а на самом деле разные названия товаров и т.д.
Итак, попробуем обозначить такие неточности обычным для нас способом — изменением внешнего вида.
Поскольку мы будем проверять D4, D6, D8 и D10, мы выбираем их и записываем правило форматирования в виде формулы
= ОБРЕЗАТЬ (D4) D4
Функция TRIM удаляет начальные и конечные пробелы из текста. И если после такого «сжатия» выяснилось, что текст «до» не совпадает с текстом «после», то в начале или в конце есть лишние пробелы. Такие данные будут отмечены соответствующим образом.
Достаточно просто, но полезно.
Чаще всего приходится назначать определенные даты, например, чтобы не пропустить предстоящие события, подготовить будущие экспедиции и т.д. И, конечно, делать это нужно с помощью программы, а не вручную.
Начнем с простого.
У нас есть много дат, из которых мы должны выбрать и выделить те, которые относятся к последнему месяцу. Для этого, как и раньше, выбираем инструмент — Правила выбора ячеек — Дата. А затем из выпадающего списка выбираем нужный нам вариант, а также стиль оформления.
Все достаточно просто, но список опций здесь очень ограничен. Поэтому попробуем второй способ, более сложный, но и более универсальный. Как вы уже, наверное, догадались, мы снова будем использовать формулы.
Итак, обратите внимание на дату доставки.
Если это в прошлом и товар уже доставлен, не о чем беспокоиться. Если это произойдет в будущем, мы должны держать проблему под контролем и не забывать организовать доставку к указанной дате. И, наконец, если дата отправки совпадает с сегодняшним или завтрашним днем, то вам нужно все бросить и позаботиться об этой партии в данный момент (высший приоритет).
Таким образом, у нас есть 3 группы условий, которые мы можем сразу записать в виде правил условного форматирования:
= O ($ E5- $ C $ 2 = 1; $ E5 = $ C $ 2)
= $ E5> $ C $ 2
Обратите внимание на порядок формул. Приведенная выше формула имеет более высокий приоритет, поскольку выполняется раньше. Поэтому вначале мы определяем только сегодняшние и завтрашние даты, затем — все остальное в будущем. Если ваш файл выглядит иначе, чем изображение, используйте клавиши вверх-вниз (3), чтобы просто изменить порядок условий.
Что ж, с датами, которые уже прошли, мы вообще ничего не делаем. Они остаются «естественными» по цвету.
И еще один пример. Выделим дни отдыха.
Столбец A нашей таблицы содержит даты. Мы выделяем их все с помощью мыши, а затем используем формулу для определения условия форматирования уже знакомым нам способом.
= ЕСЛИ (ISBLANK (A1); FALSE; OR (ДЕНЬ НЕДЕЛИ (A1; 2) = 6; ДЕНЬ НЕДЕЛИ (A1; 2) = 7))
Давайте узнаем, как это работает.
Если ячейка пуста, верните ЛОЖЬ и не меняйте ее внешний вид. В этом нам поможет функция ISBLANK, которая станет условием функции ЕСЛИ.
= ЕСЛИ (ISBLANK (A1); FALSE; [условие, если не пусто])
Если он не пустой, мы проверяем выполнение одного из двух условий:
ЕЖЕНЕДЕЛЬНЫЙ ДЕНЬ (A1,2) = 6 и ЕЖЕНЕДЕЛЬНЫЙ ДЕНЬ (A1,2) = 7
Функция DENNED с аргументом 2 помогает нам определить день недели по дате. 6 и 7 дней — как обычно, свободные дни (суббота и воскресенье).
Мы объединяем эти два выражения с помощью ИЛИ, что означает, что нам нужно выполнить хотя бы одно. Оказывается
ИЛИ (ДЕНЬ НЕДЕЛИ (A1,2) = 6; ДЕНЬ НЕДЕЛИ (A1,2) = 7)
В результате будут выбраны выходные дни, как мы определяем с помощью кнопки «Форматировать.
Для двойной проверки мы используем столбец B. В B1 запишите формулу = A1 и скопируйте ее для всех дат. Сначала вы получите копию столбца A.
Чтобы не усложнять таблицу формулами, воспользуемся числовым форматом, чтобы как-то иначе представить эти даты.
Выделите их и нажмите комбинацию клавиш CTRL и 1. В появившемся окне выберите «Все форматы» и в поле «Тип» введите 4 латинские буквы d (как на картинке). И вот у вас есть дата, преобразованная в день недели без формул!
И, как видите, мы назначили именно субботу и воскресенье.
Часто при заполнении различных форм необходимо дождаться полной вставки всех данных и только потом показать результат. Это можно сделать с помощью логических выражений и функции ЕСЛИ, где вычисления будут выполняться только при определенных условиях.
Функция ЕСЛИ в Excel — примеры использования
Возьмем небольшой пример:
Итог отображается только тогда, когда все ячейки заполнены. А вот как выглядит итоговая строка:
Обратите внимание, что шрифт изначально белый на белом фоне. А потом, после заполнения данных по всем кварталам, мы изменим их так, чтобы число стало видимым.
Для диапазона B6: C6 установите желаемый фон ячейки и установите цвет шрифта на «Авто» или принудительно установите белый цвет.
Формула правила форматирования:
= COUNT ($ C $ 2: $ C $ 5) = 4
учитывается только количество чисел в диапазоне. Если введены все четыре, общий вид изменится, и они станут видимыми. А в обычном состоянии вы их просто не увидите из-за белого цвета шрифта.
Рассмотрим еще один подход, который поможет вам проверить правильность ввода данных. Допустим, у нас есть числовые данные. Вам нужно проверить, не получены ли какие-либо из них из формул, так как все они должны вводиться вручную.
Проверяем наличие формулы в ячейке с помощью функции ISFORMULA).
= EFORMULA (B2: D13)
Обратите внимание, что абсолютные ссылки здесь не нужны.
В результате ячейки, содержащие формулы, будут выделены.
Возможно, более интересна альтернативная версия этой формулы, которая выделяет места, где определенные числа или текст вводятся вручную без использования формул.
Мы используем функцию НЕ, которая изменяет логический результат:
= НЕ (EFORMULA (B2: D13))
Теперь мы выделили области, в которых нет формул, а данные просто вводятся вручную. Возможно, это будет полезно в таблицах с множеством формул и ссылок. А если заменить одно из них на число, то при последующем изменении данных в таблице многие расчеты окажутся неверными.
Этот метод очень полезен для быстрого обнаружения попытки пользователя заменить формулу числом.
В MS Excel может быть полезно изменить цвет ячейки не только в зависимости от значения, но и в том случае, если значение отсутствует или произошла ошибка. На такие случаи в таблице часто приходится обращать особое внимание. Особенно это касается ошибок. Сделать это довольно просто.
Выделите всю таблицу, а затем повторно используйте формулу в правиле условного форматирования. Нам потребуются две функции: IsError и IsBlank).
Вы можете создать два условия:
= ISERROR (2 $ B $: 25 M)
= БЕЛЫЙ (2 $ B $: 25 M)
Или объедините их в один с помощью ИЛИ:
= ИЛИ (ЕСТЬ ОШИБКА (2 $ B $: 25 M $); ISBLANK (2 $ B $: 25 M))
Конечно, вы указываете здесь свои диапазоны.
Нажмите кнопку «Форматировать» и выберите подходящий вариант дизайна.
Когда часто возникает необходимость вставить повторяющиеся данные в таблицу, обычно прибегают к использованию раскрывающегося списка.
Как создать выпадающий список в Excel — подробнее.
вы можете применить защиту листа и предотвратить вставку данных в определенные места. Однако вы можете просто закрасить рамку, в которой было введено неправильное значение. Это уведомит пользователя о том, что информация была зарегистрирована с ошибкой.
Для этого нам сначала нужен список допустимых значений. Я думаю, что лучше всего разместить его на отдельном листе вашей рабочей тетради, чтобы он не мешал основным данным и всегда был под рукой. Мы называем этот лист Города и записываем в него действительные названия городов. Нравится:
Итак, на втором листе «Город» есть список допустимых названий.
Нам нужно изменить цвет D5 на красный, если выполняются 2 условия:
1 значение не соответствует действительным;
2 не пусто.
вы можете использовать функцию СЧЁТЕСЛИ в правиле условного форматирования для проверки первого условия).
Определяет, сколько раз записанное значение появляется в списке ссылок. Если ноль раз, это не присутствует, а то, что было введено неверно, необходимо исправить.
= СЧЁТЕСЛИ (D5; Город! A1: A6)
Для проверки второго условия мы используем функцию ISBLASNK, которая проверяет, пуста ли ячейка или на ней что-то написано.
= КВАДРАТ (D5)
Но это пустой чек. Чтобы убедиться, что он не пустой, используйте функцию НЕ. Изменяет результат выполнения логической функции на противоположный: ИСТИНА на ЛОЖЬ и наоборот. «Обратный» элемент управления выглядит так:
= НЕ (ПУСТО (D5))
и для выполнения обоих этих условий мы комбинируем их с функцией AND
= И (НЕ (ПУСТОЙ (D5)); СЧЁТЕСЛИ (Город! A1: A6; D5) = 0)
Если мы введем правильное имя в D5, красная заливка исчезнет.
Хороший способ проверить правильность ввода. Но, насколько мне известно, выпадающий список для проверки правильности ввода будет лучше. Рекомендовать:
Как создать выпадающий список в Excel с помощью формул
До сих пор мы обсуждали, как изменить внешний вид информации в самой ячейке таблицы. Что, если нам нужно изменить цвет или заливку ячейки в Excel, в зависимости от того, как меняются данные в соседних ячейках?
Давайте рассмотрим еще один классный вариант выделения столбца в таблице. Таким образом, у нас есть данные о заказах от разных покупателей в течение года.
Примечание: мы вводим даты в первую строку и не пишем вручную название месяца и года. Затем мы применяем специальный формат даты к B1: M1.
Для этого выделите этот интервал мышью и затем в меню Главная — Число — Формат откройте выпадающий список со всеми возможными форматами. Выбираем последний пункт «Другие числовые форматы». Откроется окно, как показано на картинке. Или просто нажмите комбинацию клавиш CTRL + 1.
Слева в поле «Числовые форматы» выберите внизу «все форматы». А затем в поле «Тип» напишите:
мммм гггг
Нажмите ОК и получите новый формат даты.
Теперь выделим текущий месяц.
Выделяем все столбцы нашей таблицы с датами. В данном случае это диапазон B2: M6. Затем открываем уже знакомое меню функции «Условное форматирование».
Давайте снова воспользуемся формулой, чтобы определить условия:
= МЕСЯЦ (1 млрд долларов США) = МЕСЯЦ (ДАТА())
Кстати, текущий месяц на момент написания — декабрь, то есть 12-е число.
Обратите внимание, что используется абсолютная ссылка на первую строку, которая содержит даты каждого месяца. И если месяц с этой даты совпадает с номером текущего месяца, мы закрасим этот столбец в синий цвет.
А теперь перейдем к более сложной задаче. В каждой строке таблицы мы проверяем, получал ли клиент заказы за последние 3 месяца. Если заказов не было, мы выделяем имя этого покупателя.
Посмотрим, как будет в декабре.
Мы изменим отступ в A2: A6. Но мы проверим выполнение условия в диапазоне B2: M6.
Затем выберите мышью A2: A6 и откройте меню — Создать правило — Использовать формулу.
Напишем это так:
= COUNTIF (OFFSET ($ A2,0; МЕСЯЦ (TDATE ()) — 2,1,3), «> 0») = 0
Сразу обратите внимание, что вы можете использовать СЕГОДНЯ () вместо функции TDATE (). Оба возвращают текущую дату, только TDATA также возвращает время.
= СЧЁТЕСЛИ (СМЕЩЕНИЕ ($ A2,0; МЕСЯЦ (СЕГОДНЯ ()) — 2,1,3), «> 0») = 0
Как видите, мы использовали функцию СЧЁТЕСЛИ для подсчета количества заказов. Он должен указывать диапазон в качестве первого аргумента и условие в качестве второго. И он посчитает, сколько раз это условие было выполнено в указанном диапазоне данных.
Мы используем «> 0» как условие, потому что если был заказ, то для него должна быть указана сумма. Кстати, не забудьте заключить это условие в кавычки, чтобы не было ошибок.
Теперь займемся первым условием. Для наглядности пишем здесь:
СМЕЩЕНИЕ ($ A2,0; МЕСЯЦ (TDATE ()) — 2; 1; 3)
Чтобы вернуть диапазон данных, используйте функцию СМЕЩЕНИЕ. Схема его работы представлена на рисунке выше. Прежде всего, вам нужно указать начальную точку, с которой мы начнем все действия. Пусть это будет $ A2. Опять же, мы делаем абсолютную ссылку на столбец, так что при выполнении условия следующим будет A3, затем A4 и так далее.
Далее нам нужно указать, на сколько строк нужно переместиться вниз. Поскольку нас интересует текущая строка, мы устанавливаем ее в 0.
Теперь нам нужно сдвинуть определенное количество столбцов вправо, где будет начало (верхний левый угол) нашего диапазона. Поскольку, помимо текущего месяца, нас интересуют заказы двух предыдущих (всего будет 3 месяца), перемещаемся вправо от количества столбцов, равного номеру текущего месяца минус 2. Если сейчас 12-й месяц, то мы делаем 10 шагов вправо и переходим к октябрю, который является столбцом K. Это начало нашего диапазона данных.
Далее указываем высоту интервала. Поскольку это влияет только на текущую строку, мы устанавливаем 1.
И наконец, нам нужно определить, сколько столбцов справа будет продолжать наш счет порядка. Ответ исходит из нашего условия: на 3 месяца, то есть берем K, L, M. С октября по декабрь включительно будет K2: M2. Поскольку заказов нет, СЧЁТЕСЛИ вернет 0. В результате условие будет выполнено, и A2 станет красным. И третья строка в K3: M3 содержит данные, поэтому A3 останется прежним.
И еще примечание: в январе и феврале это наше правило работать не будет, так как 3 месяца, необходимые для проверки, еще не прошли.
Итак, мы разобрались, как изменить цвет ячейки в Excel в зависимости от значения. Если есть вопросы, пишите в комментариях, постараюсь ответить.
