Как закрепить условное форматирование в сводной таблице
Перейти к содержимому

Как закрепить условное форматирование в сводной таблице

  • автор:

Как закрепить условное форматирование в сводной таблице

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Как закрепить условное форматирование в сводной таблице

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

то на первый взгляд всё сработает:

Правило будет выглядеть так

Однако, если вы обновите сводную таблицу, то форматирование исчезнет! А правило неожиданно станет таким:

В чём тут дело? Оказывается при создании правила УФ нельзя совместно выделять и обычные ячейки и ячейки сводной таблицы. Встаньте на любую ячейку сводной таблицы из области значений и начните создавать правило УФ:

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

Обратите внимание как выглядит правило УФ — добавляется иконка сводной таблицы.

Подобное УФ сохранит свою работоспособность даже, если вы поле Город перетащите из раздела СТРОКИ в раздел КОЛОННЫ сводной таблицы. Вот так:

Это всё, что я имел сказать про условное форматирование сводных таблиц 🙂

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

Условное форматирование обеспечивает наглядность при исследовании и анализе данных, обнаружении критических проблем, шаблонов и тенденций. Модуль Генератор отчетов поддерживает большинство правил условного форматирование, используемые в MS Excel, основные из которых описаны ниже. Более подробная справка есть в самом приложение MS Excel, раздел Справка «Параметры диалогового окна для изменения, поиска и отмены условного форматирования».

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

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

1. Выделите одну или несколько ячеек в диапазоне.

2. На вкладке Вид в группе Вид щелкните кнопку Условное форматирование и выберите пункт Цветовые шкалы.

3. Выберите двухцветную шкалу.

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

4. Выделите одну или несколько ячеек в диапазоне.

5. На вкладке Вид в группе Вид щелкните кнопку Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.

6. Выполните одно из указанных ниже действий.

• Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.

• Для изменения условного форматирования выполните указанные ниже действия.

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

2. При необходимости вы можете изменить диапазон ячеек.

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

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

8. В полях Тип параметров Минимальное значение и Максимальное значение выберите их типы.

• Форматирование минимального и максимального значений. Выберите минимальное значение и максимальное значение.

В этом случае не вводите минимальное и максимальное значения.

• Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное и максимальное значения.

• Форматирование процентного значения. Введите минимальное и максимальное значения.

Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.

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

• Форматирование процентиля. Выберите элемент Процентиль, а затем введите минимальное и максимальноезначения. Допустимыми являются значения процентилей от 0 (нуля) до 100. Не вводите знак процента.

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

• Форматирование результата формулы. Выберите элемент Формула, а затем введите минимальное и максимальное значения.

• Формула должна возвращать число, дату или время.

• Начинайте ввод формулы со знака равенства (=).

• Недопустимая формула не позволит применить форматирование.

• Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
Примечания

• Убедитесь, что минимальное значение меньше, чем максимальное.

• Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения.

9. Чтобы выбрать параметры Минимальное значение и Максимальное значение цветовой шкалы, щелкните элемент Цвет для каждого значения, а затем выберите цвет.

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

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

Цветовые шкалы — это визуальные элементы, которые помогают понять распределение и разброс данных. Трехцветная шкала помогает сравнить диапазон ячеек путем использования градации трех цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, в красно-желто-зеленой шкале можно указать, что ячейки с высокими значениями будут зелеными, ячейки со средними значениями — желтыми, а ячейки с низкими значениями — красными.

10. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

11. На вкладке Вид в группе Вид щелкните кнопку Условное форматирование и выберите пункт Цветовые шкалы.

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

Просмотрите значки цветовой шкалы и определите значок, соответствующий трехцветной шкале.

13. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

14. На вкладке Вид в группе Вид щелкните кнопку Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.

15. Выполните одно из указанных ниже действий.

• Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.

• Для изменения условного форматирования выполните указанные ниже действия.

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

2. При необходимости вы можете изменить диапазон ячеек.

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

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

17. Выберите тип параметров Минимальное значение, Среднее значение и Максимальное значение. Выполните одно из указанных ниже действий.

• Форматирование минимального и максимального значений. Выберите элемент Среднее значение.

В этом случае не вводите минимальное и максимальное значения.

• Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное, среднее и максимальное значения.

• Форматирование процентного значения. Выберите элемент Процент, а затем введите минимальное, среднее и максимальное значения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.

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

• Форматирование процентиля. Выберите элемент Процентиль, а затем введите Минимальное значение, Среднее значение и Максимальное значение.

Допустимыми являются значения процентилей от 0 (нуля) до 100.

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

• Форматирование результата формулы. Выберите элемент Формула, а затем введите минимальное, среднее и максимальное значения.

Формула должна возвращать число, дату или время. Начинайте ввод формулы со знака равенства (=). Недопустимая формула не позволит применить форматирование. Проверьте формулу и убедитесь, что она не возвращает значение ошибки.
Примечания

• Можно задать минимальное, среднее и максимальное значения для диапазона ячеек. Убедитесь, что минимальное значение меньше среднего, а оно, в свою очередь, меньше максимального.

• Для параметров Минимальное значение, Среднее значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения, процентиль для среднего значения и процентный тип для максимального значения.

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

18. Для выбора цветовой шкалы Минимальное значение, Среднее значение и Максимальное значение щелкните элемент Цвет для каждого из них, а затем выберите цвет.

• Чтобы выбрать дополнительные цвета или создать пользовательский цвет, нажмите кнопку Другие цвета.

• Выбранная цветовая шкала появится в окне предварительного просмотра.

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

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

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

19. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

20. На вкладке Вид в группе Вид щелкните кнопку Условное форматирование и выберите пункт Гистограммы, а затем выберите гистограмму.

1. Выделите одну или несколько ячеек в диапазоне.

2. На вкладке Вид в группе Вид щелкните кнопку Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.

3. Выполните одно из указанных ниже действий.

• Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.

• Для изменения условного форматирования выполните указанные ниже действия.

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

2. При необходимости вы можете изменить диапазон ячеек.

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

4. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений с использованием гистограммы.

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

• Форматирование минимального и максимального значений. Выберите Минимальное значение и Максимальное значение.

В этом случае не вводите Минимальное значение и Максимальное значение.

• Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное и максимальное значения.

• Форматирование процентного значения. Выберите элемент Процент, а затем введите минимальное и максимальное значения.

Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.

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

• Форматирование процентиля. Выберите элемент Процентиль, а затем введите значения параметров Минимальное значение и Максимальное значение.

Допустимыми являются значения процентилей от 0 (нуля) до 100.

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

• Форматирование результата формулы. Выберите элемент Формула, а затем введите значения параметров Минимальное значение и Максимальное значение.

• Формула должна возвращать число, дату или время.

• Начинайте ввод формулы со знака равенства (=).

• Недопустимая формула не позволит применить форматирование.

• Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
Примечания

• Убедитесь, что минимальное значение меньше, чем максимальное.

• Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения.

6. Для выбора цветовых шкал Минимальное значение и Максимальное значение выберите пункт Цвет столбца.

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

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

8. Чтобы применить к столбцам гистограммы сплошную границу, выберите в списке Граница вариант Сплошная граница, а затем выберите цвет границы.

9. Чтобы сделать столбцы сплошными или градиентными, выберите в списке Заливка вариант Сплошная заливка или Градиентная заливка.

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

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

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

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

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

Совет : Если какая-либо из ячеек в диапазоне содержит формулу, возвращающую ошибку, условное форматирование к ней не применяется.

21. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

22. На вкладке Вид в группе Вид щелкните кнопку Условное форматирование и выберите пункт Набор значков, а затем выберите сам набор значков.

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

2. На вкладке Вид в группе Вид щелкните кнопку Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.

3. Выполните одно из указанных ниже действий.

• Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.

• Для изменения условного форматирования выполните указанные ниже действия.

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

2. При необходимости вы можете изменить диапазон ячеек.

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

4. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений с использованием набора значков.

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

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

7. Выполните одно из указанных ниже действий.

• Форматирование числового значения, значения даты или времени. Выберите элемент Число.

• Форматирование процентного значения. Выберите элемент Процент.

Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.

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

• Форматирование процентиля. Выберите элемент Процентиль. Допустимыми являются значения процентилей от 0 (нуля) до 100.

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

• Форматирование результата формулы. Выберите элемент Формула, а затем введите формулы в каждое поле Значение.

• Формула должна возвращать число, дату или время.

• Начинайте ввод формулы со знака равенства (=).

• Недопустимая формула не позволит применить форматирование.

• Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

8. Чтобы первый значок соответствовал меньшим значениям, а последний — большим, выберите параметр Обратный порядок значков.

9. Для отображения только значка, но не значения в ячейке, выберите параметр Показать только значок.

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

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

Условное форматирование из сводной таблицы Excel

В данном примере мы будем использовать данные из сводной таблицы в качестве критерия для условного форматирования значений Excel. Эта статья является продолжением для примера: Форматирование сводной таблицы Excel для планирования продаж.

Подготовка плана продаж для условного форматирования

Создадим лист под названием «Шаблон анализа» как указано ниже на рисунке:

Шаблон анализа.

Важно чтобы в диапазоне A2:A5 находились имена тех самых клиентов, а в диапазоне B1:F1 – наименование тех самых товаров. В диапазон ячеек B6:G6 вводим формулы: =СУММ(B2:B5). А диапазон G2:G5 заполняется формулами: =СУММ(B2:F2).

Примечание. Для быстрого заполнения указанных выше диапазонов ячеек формулами удобно использовать комбинацию клавиш CTRL+Enter. Сначала выделяем диапазон B12:F12 так чтобы активной осталась ячейка B12 (то есть с нее начинаем выделять диапазон). Нажимаем клавишу F2 вводим формулу =СУММ(B2:B11) и нажимаем комбинацию горячих клавиш CTRL+Enter. Благодаря этому выделенный диапазон сам автоматически заполняется формулами и подставляет нужные ссылки в параметрах функции СУММ. Аналогично выполняем эти действия для автоматического заполнения формулами диапазона G2:G11.

Таблица для планирования продаж будет рассчитана на рост +5% по отношению к предыдущему году. Поэтому ее значения будут увеличены на 5%. Для того чтобы комфортно корректировать план следует записать предполагаемый уровень роста в отельную ячейку и присвоить ей имя. Ссылаясь в формулах на имя этой ячейки, мы можем изменять параметры плана с 5% на любой другой уровень роста. Для этого в ячейку H1 введем текст «Уровень роста», а в ячейку H2 вводим 5%. После чего не убирая курсор с ячейки H2 выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя» и в поле «Имя:» введите значение «урРост» (или просто введите слово «урРост» в поле имен при активной ячейке H2).

Присвоить имя.

Так как данный лист будет содержат дополнительные анализы, то желательно убрать сетку с листа. Для этого снимите галочку с опции: «ВИД»-«Показ»-«Сетка»:

Убрать сетку.

Шаблон для аналитического планирования продаж – готов.

Загрузка данных для шаблона анализа

Теперь подготовим данные:

  1. На новый лист скопируйте данные из таблицы ниже во фрейме:
  2. На основе этих исходных данных создадим сводную таблицу: «ВСТАВКА»-«Сводная таблица» Сводная таблица.
  3. Сразу после создания нового листа со сводной таблицей присваиваем новые имена и для листа, и для сводной таблицы. Чтобы переименовать лист щелкните правой кнопкой мышки по ярлычку нового листа и выберите из контекстного меню опцию «Переименовать». Потом введите новое имя «Сводный отчет» и нажмите клавишу Enter для подтверждения переименования листа. Сводный отчет.
  4. Чтобы наша сводная таблица отображала нужные нам данные в соответственном порядке, упорядочиваем значения в полях: Настройка полей данных.

Параметры полей значений в сводной таблице:

  • в полю СТРОКИ – значения Клиент и Год;
  • в полю КОЛОННЫ – значения Товар;
  • в полю ЗНАЧЕНИЯ – Количество.

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

Сводная таблица.

Теперь, когда данные подготовлены возвращаемся на шаблон анализа. Автоматически заполним табличную часть, сложной формулой которая ссылается на имя «Рост» и сводную таблицу на листе «Сводный отчет». Предварительно выделите диапазон табличной части B2:F11 так чтобы активной была ячейка B2. Потом нажмите клавишу F2 и введите формулу:

После ввода формулы нажмите комбинацию клавиш CTRL+Enter.

Автоматическое заполнение формулами.

Данная формула использует функцию =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(). Название данной функции говорит само за себя. В первом аргументе указывается поле для исходных данных (КОЛИЧЕСВТО). Во втором аргументе следует указать любой диапазон сводной таблицы (‘Сводный отчет’!$A$3). Третий аргумент содержит в себе пару поле/элемент которая описывает данные запроса (например, «Год»;2014). В данном примере год товара и клиента должен быть тот-же 2014. Выбор клиента и товара будет постоянно изменяться – соответственно. Поэтому следует их параметризировать смешанными ссылками: $A2 – для клиента и B$1– для товара.

В данном шаблоне изначально было запроектировано, что бюджетирование будет учитывать рост продаж для следующего года +5%. Поэтому в формуле мы умножаем функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() на (1+урРост). Ведь рост — это имя ячейки, которая в данный момент содержит значение 5%. Вся эта формула помещается внутрь функции =ЕСЛИОШИБКА(). Благодаря ей все ошибочные результаты вычисления будут заменятся на значение 0. Например, если определенный клиент не покупал определенный товар в 2014 году, тогда формула возвращает значение ошибки #ЗНАЧ!. Но благодаря функции ЕСЛИОШИБКА мы просто получим 0.

Шаблон анализа с условным форматированием

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

  1. Изменено значение продаж в соответствии с настройками (рост продаж +5% или больше).
  2. Изменено значение продаж на меньше установленного роста 5%.

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

  1. Выделите диапазон табличной части B2:F11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». Создать правило.
  2. В появившемся окне «Создание правила форматирования» активируйте опцию «Использовать формулу для определения форматируемых ячеек». Использовать формулу.
  3. В поле ввода вводим формулу: ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Количество»;’Сводный отчет’!$A$3;»Клиент»;$A5;»Товар»;E$1;»Год»;2014)*(1+урРост)’ >
  4. Нажмите на кнопку «Формат», чтобы задать зеленый цвет для шрифта значений ячеек.

Формат зеленый шрифт.

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

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

Управление правилами.

В результате получаем эффективный конструктор для планирования бюджета продаж. Теперь мы видим, что в ячейке B2 слишком маленькое значение и в ячейке C2 соответственно:

Анализ готов.

В ячейке H2 можем изменять значения (например, вместо 5% вводим 7%) и после нажатия клавиши Enter сразу получаем новый отчет для анализа.

7 процентов.

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

  • Создать таблицу
  • Форматирование
  • Функции Excel
  • Формулы и диапазоны
  • Фильтр и сортировка
  • Диаграммы и графики
  • Сводные таблицы
  • Печать документов
  • Базы данных и XML
  • Возможности Excel
  • Настройки параметры
  • Уроки Excel
  • Макросы VBA
  • Скачать примеры

Добавить комментарий

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