Как сделать градацию в excel?

Представьте себе монитор, где выведены рабочие узлы атомной электростанции, который отображает стабильность протекания всех процессов. Но вдруг один узел выходит из строя и сигнализирует диспетчеру о сбое, загораясь ярким красным светом. Согласитесь, очень удобно? Похожим целям служит функция условного форматирования в Excel – обеспечение наилучшей наглядности информации.

Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

Создать правило

• Для создания правила условного форматирования в Excel кликните по соответствующей кнопке на ленте, раскрыв следующее меню:
• 
• Выбрав пункт «Создать правило…», приложение отобразит окно:
• 
• В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).

Виды условного форматирования

Форматировать все ячейки на основании их значений

Этот вид правила применяется для сравнения числовых значений в диапазоне. В описании можно выбрать стиль формата и соответствующие этому стилю параметры.

Гистограмма

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

Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).

1. 
2. Настройки стиля:
3. 

• Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;
• Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.
• Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;
• Направление столбца – определяет способ направленности (слева направо либо наоборот);
• Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:
  • Установить свой цвет заливки столбца и его границу или сделать их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они различаются);
  • Задать положение оси или одинаковую направленность для всех значений.

Цветовые шкалы

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

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

Здесь Вы можете установить, что считать минимальным значением, что средним, а что максимальным. Также возможно задать предпочтительный цвет и тип показателя.Разберем установки, представленные на изображении:

• Минимальным числом задан ноль, а значения меньше его, будут иметь такие же цвет и насыщенность;
• Средним значением указана единица и желтый цвет. Это значит, что переход шкалы от красного к желтому будет осуществлен между 0 и 1;
• 4 является максимальным значением. Все, что превышает его, получает те же установки. Переход от желтого к зеленому происходит между 1 и 4.

Наборы значков (флажков)

Этот вид условного форматирования, в отличие от цвета заливки, использует различные значки в виде фигур, направлений, индикаторов и оценок.

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

Форматировать только ячейки, которые содержат

1. Этот вид условного форматирования отличается от первого тем, что он создает правило, которое должно соблюдаться, чтобы формат был применен к ячейке.
2. Рассмотрим правила, которые имеются в этом пункте:

• Значение ячейки. Предполагает работу с числами и текстом. Сравнение производится по шкале сортировки.
• Текст. Позволяет проверить наличие или отсутствие подстроки в тексте.
• Даты. С его помощью легко создать правила типа «вчера», «сегодня», «завтра», «на прошлой неделе», «в следующем месяце» и т.п.
• Пустые. Форматирует пустые ячейки. Пробелы не учитываются.
• Непустые. Противоположное предыдущему правилу.
• Ошибки. Истинно, когда значением ячейки является ошибка.
• Без ошибки. Противоположное предыдущему правилу.

Форматировать только первые и последние значения

Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.

Формула в условном форматировании

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

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

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

Используем 2 условия со следующими формулами:

• Если на складе нет товара, т.е. равен 0, то подсвечиваем позицию заказа красным – =ВПР(D3;A:B;2;ЛОЖЬ)=0;
• Если на складе есть товар, но его количество меньше, чем указано в позиции заказа, то последнюю подсвечиваем желтым – =И(ВПР(D3;$A:$B;2;ЛОЖЬ)

Источник: http://office-menu.ru/uroki-excel/14-professionalnoe-ispolzovanie-excel/58-uslovnoe-formatirovanie-excel

Условное форматирование в Excel

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

Основы условного форматирования в Excel

Используя условное форматирование, мы можем:

• закрашивать значения цветом
• менять шрифт
• задавать формат границ

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

Где находится условное форматирование в Эксель?

Кнопка “Условное форматирование” находится на панели инструментов, на вкладке “Главная”:

Как сделать условное форматирование в Excel?

При применении условного форматирования системе необходимо задать две настройки:

• Каким ячейкам вы хотите задать формат;
• По каким условиям будет присвоен формат.

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

• В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

• Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
  • Правила выделения
  • Правила отбора первых и последних значений
  • Гистограммы
  • Цветовые шкалы
  • Наборы значков
• В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:

Также, доступны следующие условия:

1. Значения больше или равны какому-либо значению;
2. Выделять текст, содержащий определенные буквы или слова;
3. Выделять цветом дубликаты;
4. Выделять определенные даты.

• Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

• Для присвоения формата вы можете использовать пред настроенные цветовые палитры, а также создать свою палитру. Для этого кликните по пункту:

• Во всплывающем окне формата укажите:
  • цвет заливки
  • цвет шрифта
  • шрифт
  • границы ячеек

• По завершении настроек нажмите кнопку “ОК”.

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

Как создать правило

Если пред настроенные условия не подходят, вы можете создавать свои правила. Для настройки проделаем следующие шаги:

• Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:

• Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип “Форматировать только ячейки, которые содержат”. После этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:

• Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:

Условное форматирование по значению другой ячейки

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

Для создания условия по значению другой ячейки выполним следующие шаги:

• Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
• Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.

• Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на кнопку “Изменить правило”:

• В поле слева всплывающего окна “очистим” ссылку от знака “$”. Нажимаем кнопку “ОК”, а затем кнопку “Применить”.

• Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на “валик” и присвоим формат остальным ячейкам:

На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

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

Возможно применять несколько правил к одной ячейке.

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

Для применения нескольких условий к одной ячейке выполним следующие действия:

• Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

• Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

Как редактировать правило условного форматирования

Для редактирования присвоенного правила выполните следующие шаги:

• Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
• Перейдите в пункт меню панели инструментов “Условное форматирование”. Затем, в пункт “Управление правилами”. Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку “Изменить правило”:

• После внесения изменений нажмите кнопку “ОК”.

Как копировать правило условного форматирования

Для копирования формата на другие ячейки выполним следующие действия:

• Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов “Формат по образцу”.
• Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

Как удалить условное форматирование

Для удаления формата проделайте следующие действия:

• Выделите ячейки;
• Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню выберите метод удаления:

Источник: https://excelhack.ru/uslovnoe-formatirovanie-v-excel/

Как создать диаграмму с интерактивными подписями данных в Excel

Как создать диаграмму с интерактивными подписями данных в Excel

Reviewed by Unknown on
2016-03-19T12:13:00+02:00

Rating: 5

Unknown

12:13:00

инструкция
,
сводная таблица
,
Excel для опытных

Edit

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

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

• Какая общая сумма ряда?
• Какой процент у каждого сегмента в ряду?
• Какое процентное изменение по сравнению с предыдущим периодом?

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

• Функция ТЕКСТ
• Функция ВЫБОР
• Одна сводная таблица
• Один срез
• Одна гистограмма

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

Скачать файл

Загрузите файл в качестве примера, что бы следовать за моими пояснениями. Внимание: Этот файл работает правильно в Excel 2013 или 2016. Первым шагом является создание обычной гистограммы с общими итогами над столбцами. Думаю, как ее создать Вы знаете 😉

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

  Я создал раздел на листе для каждого измерения: Сумма, % от общего, и % изменения.
Это довольно легко, и я не буду вдаваться в детали каждого расчета. Как правило, диаграмма отображает подписи данных на основе основного источника для диаграммы. В Excel 2013 была введена новая возможность под названием «Значения из ячеек». Эта возможность позволяет указать диапазон, который мы хотим использовать для подписей данных. Так как наши подписи данных изменяются между финансовым ($) и процентным (%) форматом, нам нужен способ, чтобы также изменить форматирование чисел на графике. В противном случае Excel будет отображать десятичное число, вместо процентов. К счастью, мы можем использовать функцию ТЕКСТ для этого. Функция ТЕКСТ позволяет Вам взять число и указать формат, в котором Вы хотели бы, что бы оно отобразилось.

  Функция ТЕКСТ на самом деле возвращает текст в ячейку, даже если он выглядит как число. Таким образом, мы можем использовать эту функцию в качестве источника наших подписей данных и гистограмма будет отображать правильное форматирование. Используйте функцию ВЫБОР, для того что бы определить какие именно подписи данных необходимо отобразить на диаграмме. У нас есть три раздела, которые содержат различные метрики. Далее мы создадим еще один раздел, который будет содержать информацию для подписей данных на гистограмме. Нам нужно будет использовать функцию поиска, чтобы возвратить правильную метрику по выбору среза. Функция ВЫБОР отлично подходит для этого. Функция ВЫБОР позволяет нам указать индекс (1,2,3,…) и она возвратит то значение, которое соответствует индексу. Сейчас мы просто добавим ячейку, которая содержит номер индекса, и указывает на три метрики для каждого значения в формуле ВЫБОР.

В итоге срез будет управлять индексом. Следующим шагом является изменение подписей данных, чтобы они отображали значения из ячеек, сформированных нами при помощи формулы ВЫБОР.
Как я говорил ранее, мы будем использовать функцию «Значения из ячеек», которая есть в Excel 2013 или 2016, что бы сделать это проще.
Вам нужно выбрать на графике ряд подписей, затем нажать на кнопку «Выбрать диапазон» в меню Параметры подписи.

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

Повторите этот шаг для каждого ряда на гистограмме. Последний шаг позволит сделать наши подписи данных интерактивными. Мы сделаем это при помощи сводной таблицы и среза. Исходные данные для сводной таблицы — это таблица на рисунке ниже.

  Эта таблица содержит три варианта для различных подписей данных.
Она также включает в себя номер индекса, на который будет ссылаться формула ВЫБОР (см. шаг 4).
Создайте сводную таблицу. Добавьте Name, Index и Symbol в строки сводной таблицы.

 Затем вставьте срез для поля Символ. Для этого кликните на сводную таблицу, зайдите в меню Конструктор и там нажмите кнопку Вставить срез.

Срез может быть отформатирован, чтобы соответствовать оформлению Вашего графика. Я  изменил количество столбцов до 3, что бы показать кнопки среза по горизонтали.

  Последний шаг состоит в том, что бы в ячейке С38 сослаться на значение индекса из сводной таблицы для корректной работы формулы ВЫБОР. Когда пользователь нажимает на кнопку среза, в ячейке С38 отразится индекс выбранного элемента сводной таблицы. Формулы ВЫБОР автоматически отобразят подписи данных для выбранного среза. Было проделано много шагов, что бы создать эту интерактивную гистограмму. К счастью, Вы можете скачать пример и модифицировать его для себя. Вы можете перемещать срез в области диаграммы, а также добавить инструкции, что бы пользователь понимал, как это работает. Спасибо за внимание. Какие другие подписи данных или виды диаграмм использовали бы Вы?

Пожалуйста, оставляйте свои комментарии ниже, а также вопросы и предложения.

Источник: http://www.excelguide.ru/2016/03/dynamic-chart-data-labels.html

Условное форматирование в EXCEL (заливка ячеек) ч.2

• Самые популярные виды условного форматирования: больше/меньше, между/равно, между, дата, первые/последние значения/%  в первой части — Условное форматирование в EXCEL (ч.1)
• Excel  предоставляет удобные инструменты условного форматирования, можно сделать заливку ячеек цветовыми градиентами, добавить мини графики и значки. Такое форматирование будет удобным для анализа данных, раскрасив различными форматами данные в отчёте хватит даже беглого взгляда на лист, чтобы визуально и наглядно оценить положение дел:

Гистограммы (градиентная заливка):

• выделяем диапазон который форматируем G2:G9
• переходим Главная → Условное форматирование → Гистограмма.
• в выпадающем списке выбираем цвет форматируемых ячеек и вид градиентная или сплошная

Форматирование ячеек с использованием цветовых шкал:

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

• выделяем диапазон который форматируем D2:D9
• переходим Главная → Условное форматирование → Цветовые шкалы.
• в выпадающем списке выбираем необходимую цветовую шкалу

Форматирование ячеек с использованием наборов значков:

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

• выделяем диапазон который форматируем E2:E9
• переходим Главная → Условное форматирование → Наборы значков
• в выпадающем списке выбираем любой необходимый набор значков.

про самые популярные виды условного форматирования тут условное форматирование ч.1 (больше/меньше, между/равно, между, дата, первые/последние значения/%)

Источник: https://www.myexcel.today/uslovnoe-formatirovanie-v-excel-zalivka-yacheek/

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

Группировка в Эксель, чаще всего, применяется, когда есть организация со структурой, и мы оцениваем некоторые показатели согласно этой структуры.

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

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

Согласитесь, такой вид таблицы более нагляден и показателен для анализа работы Компании в целом. Но как получить такую таблицу достаточно быстро, не скрывая и не удаляя ячейки? Очень просто, задайте структуру документа.

На рисунке выше я привел уже структурированный документ, с маркерами группы. В таблице сгруппированы менеджеры по регионам и регионы в компании, а так же, дни недели в месяцы.

Чтобы скрыть какую-то отдельную группу, кликните на значке «минус» в итоговой строке этой группы». Чтобы раскрыть – на значке «плюс».

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

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

Есть два способа создать структуру листа: автоматический и ручной.

Автоматическое создание структуры в Excel

Программа может попытаться создать структуру автоматически. Для этого нажмите на ленте: Структура – Группировать – Создать структуру.

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

Ручное создание структуры

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

1. Оформите и наполните таблицу, создайте итоговые строки и столбцы
2. Кликните на маленькой пиктограмме со стрелкой в нижнем правом углу ленточной группы Данные – Структура. В открывшемся окне настройте расположение строк и столбцов итогов

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

1. Нажмите на ленте Структура – Группировать (ли нажмите Alt+Shift+стрелка вправо). Будет создана группировка

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

Когда Вы вставляете новые строки и столбцы в структурированную таблицу – будьте осторожны. Если, например, добавляется новый менеджер в группу, вставляйте строку где-то между сгруппированными строками, не первым элементом группы и не последним. В противном случае, новая строка не впишется в структуру.

При копировании диапазона ячеек, скрытые строки и столбцы не копируются. Но когда они скрыты группировкой – все равно копируются. Чтобы выделить только данные, отображенные на экране, выделите нужный диапазон и нажмите F5. В открывшемся окне выберите Выделить – Только видимые ячейки. Теперь ячейки, скрытые группировкой, не будут скопированы.

Если Вам больше не нужна структура – удалите ее. Для этого выполните на ленте Данные – Структура – Разгруппировать – Удалить структуру.

Вот и все, тренируйтесь и применяйте на практике структурирование данных в Экселе. Приглашаю Вас комментировать изложенный материал, задавайте Ваши вопросы и делитесь опытом!

Следующая статья будет посвящена подключению внешних данных и их консолидации. Это неотъемлемая часть работы большинства пользователей Excel. Присоединяйтесь к чтению!

Источник: https://officelegko.com/2017/01/31/gruppirovka-dannyih-v-excel-pridaem-tablitsam-stroynosti/

Правила работы с цветовыми шкалами Excel

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

Чтобы быстро освоить правила работы с цветовыми шкалами Excel, мы будем использовать условное форматирование основано на цветовых шкалах. Выделите диапазон ячеек D1:D12. Далее выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирования»-«Цветовые шкалы». Из предлагаемой галереи готовых стилей выберите шкалу «Зеленый-желтый-красный».

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

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



Для редактирования правила шкалы цветов:

1. Выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».



2. В появившемся окне «Диспетчер правил условного форматирования» выберите правило «Шкала цветов» и нажмите на кнопку «Изменить правило».



3. Появится окно «Изменение правила форматирования», в котором уже выбрана опция «Форматировать все ячейки на основании их значений». А в выпадающем списке «Стиль формата:» уже выбрано значение «Трехцветная шкала». Для настойки шкалы пользователю предоставляется целый ряд параметров. Можно указать значение его тип и цвет для максимума минимума или среднего.
4. Измените значение на 0 для параметра «Среднее», а из выпадающего списка «Тип:» укажите для него «Число». И нажмите ОК на всех открытых окнах.



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

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

Сама таблица изменила способ заполнения диапазона ячеек разными цветами в соответствии с нашими настройками критериев.

Источник: https://exceltable.com/formatirovanie/cvetovye-shkaly-v-excel

Как применять заливки, шаблоны и градиенты к ячейкам в Excel 2010 2019

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

Применение цвета заливки

Чтобы выбрать новый цвет заливки для выбора ячейки, выполните следующие действия:

1. На вкладке «Главная» в группе «Шрифт» нажмите раскрывающееся меню кнопки «Цвет заливки».

   Появится палитра «Цвет заливки».

   Используйте кнопку «Цвет заливки», чтобы выбрать сплошной цвет фона для добавления в выбранные ячейки.

2. Выберите цвет, который вы хотите использовать в раскрывающемся палитре.

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

Добавление паттернов к ячейкам

Выполните следующие шаги, чтобы выбрать шаблон для выбора ячейки:

1. Нажмите кнопку запуска диалогового окна «Шрифт» на вкладке «Главная» (или нажмите Ctrl + 1).

   Запуск диалогового окна «Шрифт» — это маленький значок в нижнем правом углу группы «Шрифт». Откроется диалоговое окно Формат ячеек.

2. Перейдите на вкладку «Заливка».

   Выберите новый шаблон для выбора ячейки на вкладке «Заливка» диалогового окна «Формат ячеек».

3. Выберите образец образца из раскрывающегося меню кнопки «Стиль рисунка».

4. Выберите цвет шаблона из раскрывающейся палитры «Цветная кнопка».

   В поле Sample отображается выбранный шаблон и цвет.

5. (Необязательно) Чтобы добавить цвет заливки на фон шаблона, щелкните его образец цвета в разделе «Цвет фона».

6. Нажмите «ОК».

Применение эффекта градиента

Чтобы добавить эффект градиента к выбору ячейки, выполните следующие действия:

1. Нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек», а затем нажмите вкладку «Заливка».

2. Нажмите кнопку Fill Effects.

   Появится диалоговое окно Fill Effects с элементами управления, которые позволят вам определить два цвета для использования, а также стиль и вариант затенения.

   Используйте диалоговое окно Fill Effects для применения эффекта градиента для выбранных ячеек.

3. Выберите два цвета, которые вы хотите использовать в разделе «Цвета».

4. Выберите один из параметров Shading Styles, чтобы выбрать тип шаблона градиента, который вы хотите использовать; затем выберите вариант, который вы хотите использовать.

   В поле Sample отображаются текущие значения.

5. Дважды нажмите OK, чтобы закрыть оба диалоговых окна.

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

Источник: https://ru.howtodou.com/how-to-apply-fill-colors-patterns-and-gradients-to-cells-in-excel-2010

Как в Excel сделать заливку чередующихся строк с помощью условного форматирования — Трюки и приемы в Microsoft Excel

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

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

Отображение чередующейся заливки

На рисунке 1 вы видите пример. Вот как применить чередующуюся окраску к строкам.

1. Выберите диапазон ячеек для форматирования.
2. Выполните команду Главная → Условное форматирование → Создать правило. Откроется диалоговое окно Создание правила форматирования.
3. В разделе Выберите тип правила укажите вариант Использовать формулу для определения форматируемых ячеек.
4. Введите следующую формулу в поле Форматировать значения, для которых следующая формула является истиной: =ОСТАТ(СТРОКА();2)=0.

Рис. 1. Использование условного форматирования для чередующейся заливки четных и нечетных строк

При таком условном форматировании используется функция СТРОКА (возвращающая номер строки) и функция ОСТАТ (возвращающая остаток деления ее первого аргумента на второй аргумент). Для ячеек из четных строк функция ОСТАТ возвращает значение 0, и форматирование применяется к ячейккам этих строк.

Чтобы обеспечить чередование столбцов, используйте вместо функции СТРОКА функцию СТОЛБЕЦ.

Заливка ячеек в шахматном порядке

Следующая формула является вариантом примера выше. Она применяет чередующееся форматирование к строкам и столбцам так, что таблица получает двухцветную заливку в шахматном порядке. На рисунке 2 вы видите результат применения следующей формулы: =ОСТАТ(СТРОКА();2)=ОСТАТ(СТОЛБЕЦ();2).

Рис. 2. Заливка ячеек в шахматном порядке, полученная в результате условного форматирования

Заливка групп строк

Рассмотри еще один вариант заливки строк. Следующая формула применяет чередующуюся заливку не к отдельным строкам, а к их группам. Мы имеем четыре идущие друг за другом строки без заливки, далее подряд четыре строки с заливкой и т.д.: =ОСТАТ(ЦЕЛОЕ((СТРОКА()-1)/4)+1;2).

Если вас интересует группа другого размера, измените 4 на другое значение. Например, следующая формула позволяет применить чередующуюся заливку к группам по две строки: =ОСТАТ(ЦЕЛОЕ((СТРОКА()-1)/2)+1;2). На рисунке 3 показан результат этой операции.

Рис. 3. С помощью условного форматирования создано три группы строк с чередующейся заливкой

Источник: http://excelexpert.ru/kak-excel-sdelat-zalivku-chereduyushhixsya-strok-pomoshhyu-uslovnogo-formatirovaniya

Как в Excel создать диаграмму-воронку? — Microsoft Excel для начинающих

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

Попробуйте создать собственную диаграмму-воронку и увидите, что это требует определённой сноровки. Excel предоставляет инструменты для создания перевернутых пирамид, но это требует определенных усилий.

Ниже показано, как создать диаграмму-воронку в Excel 2007-2010 и Excel 2013.

Создаем диаграмму-воронку в Excel 2007-2010

Изображения в этом разделе были взяты из Excel 2010 для Windows .

• Выделите данные, которые нужно включить в диаграмму. Для примера возьмём число абонентов, подключенных к трубопроводу (столбец Number of Accounts in the Pipeline в таблице на картинке ниже).
• На вкладке Вставка (Insert) нажмите кнопку Гистограмма (Column) выберите Нормированная пирамида с накоплением (100% stacked pyramid).
• Выберите ряд данных, нажав на любую точку данных.
• На вкладке Конструктор (Design) в группе Данные (Data) нажмите кнопку Строка/столбец (Switch Row/Column).
• Щелкните правой кнопкой мыши по пирамиде и выберите Поворот объёмной фигуры (3-D Rotation) в появившемся меню.
• Измените угол поворота по осям X и Y на 0°.
• Щелкните правой кнопкой мыши по вертикальной оси и в появившемся меню выберите Формат оси (Format Axis).
• Отметьте галочкой Обратный порядок значений (Values in reverse order) – диаграмма-воронка готова!

★ Более подробно читайте в статье: → Как построить диаграмму воронки продаж в Excel

Создаем диаграмму-воронку в Excel 2013

Изображения в этом разделе были взяты из Excel 2013 для Windows7.

• Выделите данные, которые нужно включить в диаграмму.
• На вкладке Вставка (Insert) выберите Объёмную гистограмму с накоплением (3-D Stacked Column chart).
• Щелкните правой кнопкой мыши по любому столбцу и в появившемся меню выберите Формат ряда данных (Format Data Series). Откроется одноименная панель.
• Из предложенных вариантов формы выберите Полная пирамида (Full Pyramid).
• Выберите ряд данных, нажав на любую точку данных.
• На вкладке Конструктор (Design) в разделе Данные (Data) нажмите кнопку Строка/столбец (Switch Row/Column).
• Щелкните правой кнопкой мыши по пирамиде и в появившемся меню выберите Поворот объёмной фигуры (3-D Rotation).
• В появившейся панели Формат области диаграммы (Format Chart Area) в разделе Поворот объёмной фигуры (3-D Rotation) измените угол вращения по осям X и Y на 0°.
• Щелкните правой кнопкой мыши по вертикальной оси и в появившемся меню выберите Формат оси (Format Axis).
• Отметьте галочкой Обратный порядок значений (Values in reverse order) – диаграмма-воронка готова!

Когда диаграмма-воронка готова и повёрнута в нужном направлении, можно удалить подписи данных и название диаграммы, а также настроить дизайн по своему вкусу.

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

Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/kak-v-excel-sozdat-diagrammu-voronku-524.html