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

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

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

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

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

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

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

ПРИМЕР 1
Выделяем самое маленькое или самое большое число

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

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

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

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

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

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

  1. Мы прописываем формулу, которая находит максимальное значение из диапазона.
  2. В условном форматировании все числа из диапазона сравниваются с результатом формулы. Где число совпадет, та ячейка и окрасится.

Расскажем, как этого добиться.

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

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

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

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

Шаг 4. Жмем «Готово» и радуемся. Максимальное значение теперь будет безошибочно подкрашиваться:

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

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

ПРИМЕР 2
Подкрашиваем приближающиеся события

Если пример про инвестиции близок не каждому, то следующий будет настолько бытовым, насколько это возможно.

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

У этого форматирования два правила:

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

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

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

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

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

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

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

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

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

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

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

Что вы узнали

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