Что вы узнаете

  1. Как раскрашивать ячейки.
  2. Как автоматически выделять самые большие и самые маленькие числа.
  3. Как автоматически выделять приближающиеся даты.
  4. Как подкрашивать строку при выполнении условия.

Время прохождения урока — 10—15 минут.

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

Как раскрасить ячейки

Можно менять цвет самой ячейки или текста в ней — две эти кнопки находятся рядом на панели инструментов:

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

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

Разберемся, в каких случаях это может быть полезно и как это все настраивается.

Как автоматически выделять самое маленькое или самое большое число

Чтобы таблица подкрашивала нужные нам значения, мы продолжим ставить ей условия, как делали в предыдущем уроке. Но на этот раз мы будем задавать условия не через функцию IF(), а с помощью меню таблицы — инструмента «Условное форматирование».

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

А затем можно попросить таблицу автоматически выделить цветом самый дешевый вариант. Вот как это сделать.

Выделяем диапазон, к которому мы применим нашу автоматизацию, и идем в меню «Формат» → «Условное форматирование»:

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

Шаг 1. Задаем условие для раскрашивания ячеек. Выбираем правило форматирования. Есть несколько предустановленных правил форматирования: например, можно раскрашивать ячейки, которые содержат или, наоборот, не содержат определенный текст или число, ячейки с датами до или после сегодняшней. Но нас интересует точное совпадение с заданной формулой — поэтому мы выбираем «Равно»:

Шаг 2. Учим таблицу выделять минимальное значение. Пишем формулу, с которой будем сравнивать. Минимальное число из диапазона определяет функция MIN(). В нашем случае формула будет выглядеть как =MIN(E$2:E$11).

Важно, что номера строк в формуле нужно закрепить символами $. Иначе для каждой ячейки, к которой применяется форматирование, диапазон будет смещаться: для ячейки E2 будет определяться минимальное значение в диапазоне E2:E11, а для ячейки E3 — уже в диапазоне E3:E12. Нам это не нужно.

Шаг 3. Выбираем формат. Можно автоматически делать текст подчеркнутым, зачеркнутым, выделять жирным шрифтом и курсивом. Еще можно красить сам текст, а можно — фон, то есть ячейку. Тут все опять же ограничивается вашим воображением. Мы для минимальных значений выбрали светло-зеленый цвет:

Жмем «Готово» и радуемся. Минимальное значение теперь будет безошибочно подкрашиваться — если какой-то из магазинов запустит акцию и вы обновите цену, выделение автоматически переместится на самую дешевую модель:

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

Задание

Откройте рабочую тетрадь и настройте окрашивание самой дорогой посудомойки в красный цвет — чтобы обходить этот магазин стороной.

Если что-то пошло не так, сверьтесь с решебником, тут условия уже прописаны. Чтобы посмотреть правила, зайдите в «Формат» → «Условное форматирование».

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

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

В рабочей тетради на листе «Акции» выполните задание пошагово:

  1. Выделите диапазон и откройте меню условного форматирования.
  2. Задайте условие — значение из диапазона должно быть равно нашей формуле.
  3. Пропишите формулу для минимального значения. Не забудьте про символ $.
  4. Повторите то же самое для максимального значения.

Если что-то пошло не так, сверьтесь с решебником.

Как подкрашивать приближающиеся события

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

Здесь мы настроим сразу два правила в условном форматировании:

  1. Если срок прошел, то есть число в «Осталось дней» отрицательное, — выделить ячейку красным.
  2. Если до даты осталось меньше 14 дней — выделить ячейку зеленым. Чем ближе к дате, тем темнее цвет.

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

Красим просроченное. Выделяем наш диапазон в столбце C и идем в условное форматирование, как в прошлом примере: меню «Формат» → «Условное форматирование».

Выбираем правило форматирования. Правило простое: если число меньше 0, нужно покрасить ячейку. В правилах форматирования выбираем «Меньше», указываем число 0. Цвет — по желанию, мы выбрали тревожный красный:

Жмем «Готово» — первое правило начинает работать:

Подкрашиваем приближающееся. Теперь наша задача — подкрашивать те даты, что приближаются. Начинается все так же: выделяем диапазон C2:C9 и идем в «Формат» → «Условное форматирование». Первое правило уже работает, менять его мы не хотим, поэтому нужно создать новое. Жмем «Добавить правило»:

Если мы хотим, чтобы цвет становился темнее при приближении даты, меняем режим форматирования с «Один цвет» на «Градиент»:

Теперь задаем условия, по которым будут подсвечиваться ячейки. Для примера мы взяли диапазон 14 дней: если до даты обслуживания остается меньше двух недель, ячейка будет подсвечиваться — и чем ближе к этой дате, тем цвет будет темнее. Если до даты обслуживания больше двух недель, ячейка подсвечиваться не будет.

Эти числа мы зададим вручную: выбираем в меню «Мин. точка» тип «Числовой» и указываем 0 — если дата обслуживания сегодня, цвет будет самый темный. В качестве максимальной точки указываем 14 — у этой даты будет самый бледный оттенок. А если дата больше, подсветки не будет вообще:

Жмем «Готово», второе правило тоже начинает работать. Чем ближе дата обслуживания, тем темнее зеленый цвет:

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

Как подкрашивать всю строку при выполнении условия

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

Вот так это может выглядеть — если в столбце «Выполнено» стоит «Да», подкрашивается вся строка:

Откройте рабочую тетрадь на листе «Ежедневник» — настроим такие же условия.

Наши условия:

  1. Если задание не выполнено, показывать, сколько дней до дедлайна.
  2. Если задание выполнено, не показывать количество дней до дедлайна.
  3. Если задание выполнено, подкрашивать всю строку светло-зеленым.

Шаг 1. Посчитаем количество дней до дедлайна для невыполненных задач и попросим не показывать его, если задача выполнена. Нам нужно поставить таблице условие — вспомним, как мы это делали в предыдущих уроках. Формула для строки 2 будет такой: если в ячейке D2 стоит «Да», нужно оставить ячейку C2 пустой. А если «Да» не стоит, нужно посчитать количество оставшихся дней — то есть из даты дедлайна вычесть сегодняшнюю.

В девятом уроке мы научимся создавать выпадающие списки — тогда слово «Да» можно будет даже не писать, а просто выбрать из списка в ячейке. Но об этом позже.

Соберем формулу: =IF(D2="Да";"";(B2-H$1)).

D2="Да" — условие, которое мы проверяем.

"" — пустая ячейка, если наше условие соблюдается.

B2-H$1 — действие, которое нужно выполнить, если условие не соблюдается: из даты дедлайна вычитаем сегодняшнюю. Обратите внимание, что мы поставили знак $ при указании ячейки H1. Сегодняшняя дата у нас всегда прописана в строке 1 — и мы хотим, чтобы при копировании этот адрес не смещался.

Вводим эту формулу и копируем ее на следующие ячейки. Сразу же можем проверить — если в столбец D вписать «Да», расчет дней из соответствующей строки пропадет:

Шаг 2. Сделаем так, чтобы строка раскрашивалась. Условие такое: если в столбце D стоит «Да», всю строку нужно выделить зеленым. Получится вот так:

Идем в «Формат» → «Условное форматирование» и добавляем новое правило.

Первым делом нам нужно указать диапазон, который должен раскрашиваться. Мы хотим, чтобы цветом выделялись все ячейки, которые относятся к нашей строке, то есть в столбцах от A до D. По вертикали мы будем вписывать наши задачи в строки со второй и до бесконечности — можно указать, например, по тысячную. Итоговый диапазон — A2:D1000:

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

Разберемся, что есть что в формуле =$D2=”Да”.

$D2 мы указали со знаком $, потому что мы всегда будем искать слово «Да» в столбце D — и не хотим, чтобы поиск смещался на другие столбцы. Значит, адрес столбца нужно зафиксировать. Начали именно с ячейки D2, потому что это начало нашего диапазона, в котором будем искать нужное значение.

”Да” — это наше условие. Если вместо «Да» в ежедневнике вы хотите писать «Выполнено», условие будет ”Выполнено”.

Готово! Теперь можно проверять нашу таблицу — все должно работать так же, как на иллюстрации в начале раздела. Если что-то пошло не так, сверьтесь с решебником — там мы добавили еще пару правил, которые выделяют дедлайн цветом, если он приближается или просрочен.

Запомним

  1. В таблице можно раскрашивать ячейки в разные цвета самостоятельно, а можно настроить автоматическое подкрашивание по разным условиям.
  2. Ставить таблице условия можно как с помощью формул, так и используя инструменты меню — для этого нужно открыть «Формат» → «Условное форматирование» → «Правила форматирования».
  3. В «Правилах форматирования» можно задать выделение текста — цветом, полужирным, курсивом и так далее — по разным параметрам. Например, форматировать ячейки, которые содержат или не содержат какое-то слово, дату или число.
  4. В ячейке, которую мы хотим выделить, может стоять формула. Тогда таблица подкрасит ее, если итог формулы соответствует нашему условию.

Функции и инструменты, которые вы освоили

  1. «Формат» → «Условное форматирование» → «Правила форматирования».
  2. MAX() и MIN() находят максимальное и минимальное значение соответственно.