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

Либо щелкнуть правой кнопкой мыши на точку данных и изменить цвет заливки или границы в появившейся панели.

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

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

Для этого нужно добавить столбец к исходным данным, в нем необходимо с помощью формул оставить только те значения, что мы будем выделять, и заменить те значения, что выделять не планируется, значением #Н/Д (#N/A).

Это можно сделать с помощью функции ЕСЛИ / IF, если мы хотим выделять только значения больше или меньше определенного уровня.

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

И меняем ее тип на «Комбинированную» (Combo). Сделаем оба ряда данных одинакового типа — «Гистограмма» (Clustered Column). Таким образом, они будут накладываться друг на друга. Если во втором ряду нет значений, то будет отображаться первый (синего цвета в нашем примере). Если во втором ряду есть значения (а есть они, напомним, если значение соответствует условию) — они будут отображаться поверх первого ряда своим (оранжевым в нашем примере) цветом. И возникнет ощущение выделения.

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

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

Выделение периода

Допустим, у нас есть данные по продажам в кафе Штруделя, и мы построили диаграммы (гистограмму) с продажами за несколько месяцев.

Часть этого периода — карантин. И мы хотим выделить этот период фоном определенного цвета.

Для этого нужен дополнительный столбец, в который мы вставим числа, примерно соответствующие верхней планке по оси в нашем графике (в нашем примере это 14 000).

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

Высота оси автоматически изменится до 16 000, но ее можно всегда изменить обратно на 14 000 в формате оси, чтобы оранжевая область доходила до самого верха диаграммы.

Флажки (отключение рядов)

Если мы хотим включать и отключать определенные ряды данных на диаграмме, чтобы формировать набор для сравнения (например, выбирать, какие два или три региона из четырех сравнивать друг с другом на графике), можно воспользоваться элементами управления — флажками (Check Box).

Будем рассматривать задачу на примере данных по четырем регионам.

Нам необходимы четыре «переключателя» — флажка, нажимая на которые мы будем включать или выключать определенные регионы на диаграмме.

Флажки, как и другие элементы управления, вставляются на вкладке «Разработчик» (Developer). Если в вашем Excel она не отображается на ленте, ее нужно включить в параметрах Excel.

Флажки вставляются в группе «Элементы управления» — кнопка «Вставить» (Controls — Insert).

После вставки флажка его можно переименовать, чтобы надпись на нем соответствовала региону (щелчок правой кнопкой мыши по флажку — «Изменить текст», Edit Text).

И далее необходимо связать флажок с какой-нибудь ячейкой — это позволит отображать его текущее значение (снят/установлен) в виде логических значений ИСТИНА / ЛОЖЬ (TRUE / FALSE).

Для установки связи с ячейкой (Cell link) необходимо щелкнуть правой кнопкой по объекту и выбрать «Формат объекта» (Format Control).

Теперь при изменении флажка будет меняться и значение в ячейке J1.

Остается создать аналогичные флажки для каждого ряда данных (региона) в нашей таблице.

Теперь у нас есть четыре ячейки, в которых отражается выбор регионов.

Остается создать дополнительную таблицу, в которой будут не все данные (как в A1:E13), а только те, которые мы выбрали. Для этого можно, как мы делали в одном из предыдущих примеров, воспользоваться функциями ЕСЛИ / IF и НД / NA.

Теперь можно построить диаграмму по созданному диапазону A15:E27, в котором отображаются только выбранные данные.

При снятии и установке флажков на диаграмме теперь будут отображаться соответствующие ряды данных (регионы).

Значения ИСТИНА / ЛОЖЬ можно скрыть с помощью форматов или белого шрифта: они не нужны и не являются частью интерфейса, для пользователя здесь предназначены флажки, а значения в ячейках нужны для формул.

А сами флажки можно для большей наглядности передвинуть на диаграмму — к легенде. В таком случае текст на флажках можно удалить — достаточно будет подписей легенды.

Power Map (3D-карты)