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

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

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

В условном форматировании есть три опции для визуализации данных с помощью графических элементов: гистограммы (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).