Добавление шкалы гистограммы в ячейку Excel

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

Гистограмма в ячейке Excel и условное форматирование

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

В первую очередь графически экспонируем в таблице количество заказов. В старых версиях Excel для реализации данной задачи нужно было прибегать к написанию сложных макросов или выполнять большой объемы работы, вручную вводить множество формул и форматировать диапазоны ячеек. Но начиная с версии Excel 2010 можно очень быстро получить такой эффект. В принципе достаточно просто выделить диапазон E4:E15 и присвоить форматирование гистограммой: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Гистограммы»-«Синяя»:

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

В ячейку F2 введите относительную ссылку на ячейку =E2 и скопируйте ее вдоль целого столбца. А потом выделите диапазон F2:F13 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Гистограммы»-«Другие правила».

В появившемся окне «Создание правила форматирования» выберите опцию: «Форматировать все ячейки на основании их значений» и поставьте галочку на против опции «Показывать только столбец».

При необходимости выберите желаемый цвет и нажмите на кнопку ОК.

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

Чтобы удалить только эти гистограммы выделите диапазон F2:F13 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Удалить правила»-«Удалить правила из выделенных ячеек». Или еще проще удалите целый столбец F предварительно выделив его (F:F), нажмите комбинацию горячих клавиш CTRL+-(минус).

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

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

Чтобы реализовать данную задачу инструмент «Условное форматирование» проанализирует каждую ячейку на соответствие из значений с предварительно установленными пользователем значениями в критериях. Если в процессе такого теста, например, ячейка A2 (A2>100) возвращает результат «ИСТИНА», сразу ей присваивается новый формат, предварительно определенный пользователем. В противные случаи тест возвращает результат «ЛОЖЬ» и ячейка сохранит свой изначальный формат (не обязательно по умолчанию «Общий», а тот который был ей присвоен до теста).

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