Цвет ячейки в зависимости от значения в Excel

Процедура изменения цвета ячеек в зависимости от содержимого

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

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

Но выход есть. Для ячеек, содержащих динамические (изменяющиеся) значения, используется условное форматирование, а для статистических данных можно использовать инструмент «Найти и заменить».

Способ 1: условное форматирование

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

Давайте посмотрим, как этот метод работает на конкретном примере. У нас есть таблица доходов компании, где данные разбиты по месяцам. Нам нужно выделить разными цветами те статьи, где сумма дохода меньше 400 000 рублей, от 400 000 до 500 000 рублей и превышает 500 000 рублей.

1. Выбираем столбец, где размещена информация о доходах компании. Далее переходим на вкладку Главная. Нажмите кнопку «Условное форматирование», расположенную на ленте в панели инструментов «Стили». В открывшемся списке выберите пункт «Управление правилами…».
2. Откроется окно «Правила условного форматирования». Поле «Показать правила форматирования для» должно быть установлено на «Текущий фрагмент». По умолчанию он там должен быть указан, но на всякий случай проверьте и, в случае отклонения, измените настройки согласно приведенным выше рекомендациям. Затем нажмите на кнопку «Создать правило…».
3. Откроется окно «Создать правило форматирования». В списке типов правил выберите пункт «Форматировать только те ячейки, которые содержат». В блоке описания правила в первом поле переключатель должен стоять в положении «Значения». Во втором поле установите переключатель в положение «Меньше». В третьем поле укажите значение, элементы листа, содержащие значение меньше указанного, будут окрашены в определенный цвет. В нашем случае это значение будет 400 000. После этого нажмите на кнопку «Форматировать…».
4. Откроется окно Формат ячеек. Перейдите на вкладку «Заполнить». Выбираем цвет заливки, которым хотим выделить ячейки, содержащие значение меньше 400 000. После этого нажимаем кнопку «ОК» внизу окна.
5. Возвращаемся в окно создания правила форматирования и там же нажимаем кнопку «ОК».
6. После этого действия нас снова перенаправит в Диспетчер правил условного форматирования. Как видите, одно правило уже добавлено, но нам нужно добавить еще два. Поэтому снова нажмите на кнопку «Создать правило…».
7. И снова мы приходим к окну создания правила. Перейдите в раздел «Форматировать только те ячейки, которые содержат». В первое поле этого раздела поставьте параметр «Значение ячейки», а во второе установите переключатель в положение «Между». В третьем поле необходимо указать начальное значение области форматирования элементов листа. В нашем случае это число 400 000. В четвертом вводим конечное значение для этого диапазона. Это будет 500 000. После этого нажмите на кнопку «Форматировать…».
8. В окне форматирования снова переходим на вкладку «Заливка», но на этот раз уже выбираем другой цвет, а затем нажимаем кнопку «ОК».
9. Вернувшись в окно создания правила, также нажмите кнопку «ОК».
10. Как видите, мы уже создали два правила в Менеджере правил. Таким образом, осталось сделать третью. Нажмите на кнопку «Создать правило».
11. В окне создания правила снова перейдите к разделу «Форматировать только те ячейки, которые содержат». В первом поле оставьте опцию «Значение ячейки». Во втором поле установите переключатель на полицию «Еще». В третье поле вводим число 500000. Затем, как и в предыдущих случаях, нажимаем на кнопку «Форматировать…».
12. В окне «Формат ячеек» снова перейдите на вкладку «Заливка». На этот раз мы выбираем цвет, отличный от предыдущих двух случаев. Производим щелчок по кнопке «ОК».
13. В окне создания правила еще раз нажмите кнопку «ОК».
14. Откроется Диспетчер правил. Как видите, все три правила созданы, поэтому нажимаем кнопку «ОК».
15. Теперь элементы таблицы окрашиваются в соответствии с заданными условиями и ограничениями в настройках условного форматирования.
16. Если мы изменим содержимое одной из ячеек, выйдя при этом за пределы одного из заданных правил, этот элемент листа автоматически изменит цвет.

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

1. Для этого после перехода из менеджера правил в окно форматирования остаемся в разделе «Форматировать все ячейки на основе их значений». В поле «Цвет» можно выбрать цвет, оттенками которого должны быть заполнены элементы на листе. Затем следует нажать на кнопку «ОК».
2. В Диспетчере правил также нажимаете на кнопку «ОК».
3. Как видите, после этого ячейки в столбце окрашиваются разными оттенками одного цвета. Чем больше значение, содержащее элемент листа, тем светлее оттенок, чем меньше оттенок, тем темнее.

Как построить линейчатую диаграмму в Excel

Нет ничего проще, чем построить гистограмму в Excel. Сначала выберите данные, которые вы хотите отобразить на диаграмме, затем на вкладке «Вставка» в разделе «Диаграммы» щелкните значок гистограммы и выберите подтип, который хотите создать.

В этом примере мы создаем простейшую диаграмму — двухмерный сгруппированный бар):

Сгруппированная гистограмма, вставленная в электронную таблицу Excel, будет выглядеть примерно так:

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

Как увидеть все доступные типы линейчатой диаграммы

Чтобы просмотреть все типы столбчатых диаграмм, доступные в Excel, щелкните ссылку «Дополнительные гистограммы» и выберите один из доступных подтипов диаграмм в открывшемся диалоговом окне «Вставить диаграмму.

Выбираем макет и стиль линейчатой диаграммы

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

• В разделе «Макет диаграммы» нажмите кнопку «Быстрый макет» и попробуйте различные готовые макеты гистограммы;
• Или поэкспериментируйте со стилями гистограммы в разделе «Стили диаграмм).

Настройка линейчатых диаграмм в Excel

Как и другие типы диаграмм Excel, линейчатые диаграммы предлагают множество настроек для таких элементов, как заголовки диаграмм, оси, метки данных и многое другое. Более подробную информацию вы можете найти по ссылкам ниже:

• Добавить заголовок диаграммы
• Настройка осей карты
• Добавление меток данных
• Добавляйте, удаляйте, перемещайте и настраивайте формат легенды
• Показать или скрыть линии сетки
• Изменить или скрыть ряд данных
• Изменение типа и стиля диаграммы
• Изменить цвета диаграммы по умолчанию

Теперь давайте рассмотрим некоторые конкретные приемы, применимые к линейчатым диаграммам в Excel.

Изменение ширины полос и расстояния между полосами диаграммы

Гистограмма, созданная в Excel с настройками по умолчанию, имеет слишком большое расстояние между столбцами. Выполните следующие шаги, чтобы сделать полосы шире и визуально ближе друг к другу. Таким же образом можно сделать полосы более узкими и увеличить расстояние между ними. На плоской 2D-диаграмме столбцы могут даже перекрываться.

1. В линейчатой ​​диаграмме Excel щелкните правой кнопкой мыши ряд данных (полосу) и выберите «Формат ряда данных» в контекстном меню
2. На появившейся панели в разделе «Параметры серии» выполните одно из следующих действий:
   • Для 2D- или 3D-диаграммы: чтобы изменить ширину столбца и промежуток между категориями, переместите ползунок для параметра «Ширина разрыва» или введите процентное значение от 0 до 500 в поле ввода. Чем меньше значение, тем тоньше полосы и меньше зазор между ними, и наоборот.
   • Для плоской диаграммы: чтобы изменить расстояние между рядами в одной категории, переместите ползунок параметра перекрытия рядов или введите процентное значение от -100 до 100 в поле ввода. Чем больше значение, тем больше перекрытие ряда. Отрицательное значение приведет к разрыву между строками, как на изображении ниже:
   • Для трехмерной диаграммы: чтобы изменить разрыв между рядами данных, переместите ползунок «Глубина разрыва» или введите значение от 0 до 500 процентов. Чем больше значение, тем больше расстояние между полосами. Изменение фронтального зазора видно на большинстве линейчатых диаграмм Excel, но лучше всего на трехмерной гистограмме, как показано на следующем изображении:

Построение линейчатой диаграммы с отрицательными значениями

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

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

Настраиваем подписи вертикальной оси

Чтобы изменить внешний вид вертикальной оси, щелкните одну из меток и выберите «Формат оси» в контекстном меню или просто дважды щелкните метки оси. В правой части таблицы появится панель.

Перейдите на вкладку «Параметры оси», разверните раздел «Метки» и установите для параметра «Положение метки» значение «Низкое).

Варианты построения диаграммы Ганта

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

Microsoft Excel

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

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

2. Перейдите на вкладку «Вставка» и ознакомьтесь с доступными диаграммами. Нас интересует тип «Линейный».

3. Разместите презентационную схему в подходящем месте и завершите рисунок.

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

LibreOffice Calc

Давайте кратко рассмотрим главного конкурента Excel, LibreOffice Calc. Это программное обеспечение распространяется бесплатно.

В этом софте также нужно заранее подготовить таблицу данных, затем перейти в раздел «Вставка» и выбрать «Диаграмма». В окне «Типы диаграмм» укажите «Линейчатая диаграмма с накоплением». Переверните вертикальную ось, щелкнув ее правой кнопкой мыши и выбрав «Формат оси» — «Масштабирование» — «Обратный». Кроме того, мы разберем, как отображать дату на горизонтальной оси.

1. Нажмите на горизонтальную ось и выберите «Формат оси» в появившемся контекстном меню».
2. Перейдите на вкладку «Числа» и найдите там тип даты.
3. откройте вкладку «Масштабирование» и отключите автоматическое добавление значений для всех элементов.
4. Установите дату и время начала задачи для «Минимума» и время окончания для «Максимума» самостоятельно, а интервал установите числом 1. Для долгосрочных задач сделайте основной интервал больше, чтобы диаграмма была более компактной.‎‎

 

Типы линейчатых диаграмм в Excel

При создании гистограммы в Excel можно выбрать один из следующих подтипов:

Линейчатая с группировкой

Сгруппированная гистограмма (2-D или 3-D) сравнивает значения в категориях данных. В сгруппированной гистограмме категории обычно откладываются по вертикальной оси (ось Y), а значения — по горизонтальной оси (ось X). Сгруппированная трехмерная гистограмма не отображает третью ось, а только делает столбцы на графике трехмерными.

Линейчатая с накоплением

Столбчатая диаграмма с накоплением показывает пропорции отдельных элементов по отношению к целому. Как и сгруппированная гистограмма, она может быть плоской (2D) или трехмерной (3D):

Нормированная линейчатая с накоплением

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

Цилиндры, конусы и пирамиды

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

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

Ленты Excel 2013 и Excel 2016 не предлагают цилиндры, конусы или пирамиды. Согласно Microsoft, эти типы диаграмм были удалены, потому что слишком много типов диаграмм в предыдущих версиях Excel мешало пользователю выбрать правильный тип. Однако возможность использовать цилиндр, конус или пирамиду также доступна в современных версиях Excel, хотя для этого требуется несколько дополнительных действий.

Как в Excel 2013 и 2016 при построении диаграммы использовать цилиндр, конус или пирамиду

Чтобы использовать цилиндрическую, конусную или пирамидальную диаграмму в Excel 2013 и 2016, создайте трехмерную линейчатую диаграмму нужного типа (сгруппированную, с накоплением или нормализованным с накоплением), а затем измените формы, используемые для построения ряда:

• Выберите все столбцы на диаграмме, щелкните их правой кнопкой мыши и выберите «Формат ряда данных» в контекстном меню или просто дважды щелкните столбец диаграммы.
• В появившейся панели в разделе «Параметры ряда» выберите соответствующую форму столбца).

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

Сферы использования диаграммы Ганта

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

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

Теория цветового кодирования

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

В то время было слишком много цветов, поэтому команде Google пришлось уменьшить масштаб и найти идеальное соответствие, чтобы правильно выделить все важные аспекты карты, а также иметь возможность перемещаться по ней, не перегружаясь. Затем за этим последовали дополнительные детали и детали для Google Maps.

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