Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

Обратите внимание на доллары в ссылке на ячейку E1 в правиле — это «закрепленная» (абсолютная, со знаками доллара) ссылка на ячейку E1. Мы форматируем целый столбец B с продажами, а сравниваем каждое число в нем с одной и той же ячейкой E1. Если бы мы не закрепили ссылку на нее долларами, то с ней сравнивалась бы только первая ячейка диапазона — B2. А уже B3 бы сравнивалась с пустой ячейкой E2, B4 — с E3 и так далее (по аналогии с протягиванием формул, при котором все относительные ссылки смещаются).

Теперь при изменении числа в E1 форматирование будет изменяться автоматически, так как именно эта ячейка выступает условием для нашего правила.

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

В таком случае нам нужна относительная ссылка, ведь B2 будет сравниваться с C2, B3 c С3 и так далее — каждый раз это разные ячейки в рамках одной и той же строки.

Поэтому после выделения всего столбца нужно задать условие для первой форматируемой ячейки и сделать его в виде относительной ссылки (мы задаем условие для первой ячейки форматируемого диапазона и представляем, что оно протягивается на все остальные, как формула).

ГИСТОГРАММЫ, ЦВЕТОВЫЕ ШКАЛЫ И ЗНАЧКИ

В условном форматировании есть три опции для визуализации данных с помощью графических элементов: гистограммы (Data Bars), цветовые шкалы (Color Scales) и значки (Icon Sets). Причем их можно применять как к обычным ячейкам с данными, так и к полям сводных.

В Google Таблицах графических элементов (гистограмм и значков) в условном форматировании нет — только цветовые шкалы. Значки можно вставлять с помощью текстовых функций или через функцию IMAGE (если у вас есть ссылка на значок или эмодзи). А гистограмму можно построить с помощью функции SPARKLINE (горизонтальная гистограмма в этой функции имеет тип bar).

Гистограммы (Data bars)

Гистограммы — это маленькие графики (горизонтальные линии, или, как они называются в Excel, столбики) в ячейках. Их ширина зависит от значения в ячейке, а максимальная ширина (во всю ячейку) определяется максимальным значением в диапазоне.

Есть двенадцать готовых вариантов гистограмм — со сплошным цветом и полупрозрачных.

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

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

— если сразу выбрать в списке наборов значков не готовый вариант, а «Другие правила» (More Rules), тогда вы попадете в диалоговое окно с настройками;

— через «Управление правилами» (Manage Rules) в «Условном форматировании».

Например, так будет выглядеть гистограмма, если активировать опцию «Показывать только столбец» (Show Bar Only) в настройках.

Цветовые шкалы (Color Scales)

Цветовые шкалы позволяют залить ячейки разными цветами (изменить цвет фона) в зависимости от их значений. В цветовых шкалах задаются «пограничные» цвета: для минимальных и максимальных значений (а также средних значений, хотя можно задать и только два цвета) форматируемого диапазона. Ячейки будут окрашиваться в соответствующие оттенки в зависимости от того, к чему ближе числа в этих ячейках.

Есть встроенные двух- и трехцветные шкалы, которые подойдут для многих случаев.

Есть возможность настраивать свои шкалы, для этого нужно выбрать вариант «Другие правила» (More Rules). В диалоговом окне выбираем нужные цвета и в образце видим, как будет выглядеть фон ячеек.

Наборы значков (Icon Sets)

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

По умолчанию значки отображаются следующим образом: числа в форматируемом диапазоне разбиваются на равные части (три, четыре или пять), и для каждой части применяется свой значок.

Если их три, то для каждой трети (меньшая треть, средняя и наибольшая).

Если четыре, то для каждой из четырех равных частей.

Если пять, то, соответственно, для каждой из пяти.

Но настройки можно изменять — см. «Настройка графических объектов условного форматирования» ниже.

Превращаем цифры (оценки) в значки

Значки хорошо подходят для оценок, например от 1 до 5.

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

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

Настройка графических объектов условного форматирования

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

• В случае с цветовой шкалой все числа в диапазоне сортируются: для минимального применяется один «крайний» цвет шкалы, для максимального — другой, для среднего (если в шкале три цвета) — «средний» цвет, а для остальных — в зависимости от положения числа на спектре всех значений в диапазоне.

• В случае с иконками все числа в диапазоне будут разделены на три, четыре или пять (смотря сколько иконок в выбранном наборе) равных частей и для каждой из них будет применяться значок из набора.

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

Если вам нужно что-то другое, заходите в настройки правила или сразу выбирайте вариант «Другие правила» (More Rules).

Для шкал можно выбирать варианты с двумя или тремя цветами, задавать любые цвета и менять то,