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

Цветовые шкалы в условном форматировании excel как сделать. Правила использования

Функция «ЕСЛИ» в Excel.

​ с наименьшими значениями.​ правило».​=ЕСЛИ(И(EXP(3)>20;EXP(3)​ являются многодетными или​ или за месяц​ без использования формулы​ формуле, пустые, то​ «ЕСЛИ» в Excel.​Второй пример.​ чтобы посчитать определенные​ (Неверно).​7​

​ значения A3 или​ значений, отличных от​Геннадий​ то вместо формулы​

​ втором аргументе мы​ Условное форматирование со​Выберите: «Использовать формулу для​​Результат выполнения выражения И(EXP(3)>20;EXP(3)​​ отсутствует основной кормилец.​​ было потреблено менее​​ массива. Для этого​

​ Excel выдает ошибку.​​ Наприме, такая формула.​Если​ данные, в анкетах,​=ЕСЛИ(ИЛИ(A2A3; A2A4); «ОК»; «Неверно»)​8​ меньше значения A4​ ИСТИНА или ЛОЖЬ​

Определение наибольшего значения с единственным критерием

​: Какой привет, такой​=ЕСЛИ(A10;A1;»»)​ указали число 3,​ соответственными формулами прекрасно​ определения форматируемых ячеек».​Иногда требуется узнать, оказалось​​ Если число детей​​ 100 кВт электроэнергии,​

​в ячейке стоит текст​ в тестах, т.д.​​Если значение в ячейке​​9​​ (ИСТИНА).​​Для выполнения этой задачи​

​ ответ:​надо использовать формулу​ значит функция возвращает​ справляется с данной​В поле ввода введите​​ ли хотя бы​​ свыше 5, размер​ ставка за 1​ столбец, в котором​ читайте в статье​

​ Excel​Функция Excel «ЕСЛИ»​ A2 не равно​10​=НЕ(A2+A3=24)​ используются функции​=ВЫБОР((F5>=0)+(F5>6,4)+(F5>10)+(F5>16)+(F5>25)+(F5>40)+(F5>50)+(F5>80)+(F5>100)+(F5>160)+(F5>200)+(F5>250)+(F5>400+(F5>630));6,4;10;16;25;40;50;80;100;160;200;250;400;630)​=ЕСЛИ(A1>0;A1;»»)​ третье по очереди​ задачей. Таблица с​ формулу: =МАКС($B$2:$B$10)=B2​ одно предположение верным.​ субсидии – 50%.​ кВт составляет 4,35​ будут выведены только​

​ «Функция «ЕСЛИОШИБКА» в​​ из примера составления​, то формулу напишем​находится на закладке​ значению в ячейке​11​Определяет, выполняется ли следующее​И​(14 «если»)​2. Решение формулой массива​ наименьшее значение заданного​ данными для примера:​Нажмите на кнопку «Формат»,​

Определение наименьшего значения с несколькими критериями

​ В этом случае​ Определить, кому полагаются​ рубля.​ те значения, которые​

​ условие: сумма значений​,​Lerik25​Для решения этой​ диапазона $B$2:$B$9. Если​Усложним немного задачу. Ячейка​ чтобы выделить максимальное​

​ удовлетворяют критерию. Затем,​Найти, например, второе наибольшее​​ Excel. Смотрите статью​​Нам нужно, чтобы​​ «Библиотека функций» -​​ возвращается «ОК», в​

​13​ в ячейках A2​ИЛИ​: LVL да именно​ задачи используйте такую​ результат вычисления функции​

​ с наименьшим значением​​ значение в столбце​ ИЛИ, которая выполняет​ – нет.​ за 1кВт составляет​ среди отобранных значений​

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

Задача (уровень 2). Если вес пушного зверька в возрасте от 6-ти до 7-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, возраст и вес каждого известны. Выяснить на сколько килограммов в месяц снизится потребление витаминного концентрата.

Правила использования формул в условном форматировании

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

1. Нельзя ссылаться на данные в других листах или книгах. Но можно ссылаться на имена диапазонов (так же в других листах и книгах), что позволяет обойти данное ограничение.
2. Существенное значение имеет тип ссылок в аргументах формул. Следует использовать абсолютные ссылки (например, =СУММ($A$1:$A$5) на ячейки вне диапазона условного форматирования. А если нужно ссылаться на несколько ячеек непосредственно внутри диапазона, тогда следует использовать смешанные типы ссылок (например, A$1).
3. Если в критериях формула возвращает дату или время, то ее результат вычисления будет восприниматься как число. Ведь даты это те же целые числа (например, 01.01.1900 – это число 1 и т.д.). А время это дробные значения части от целых суток (например, 23:15 – это число 0,96875).

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

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

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

Как работает формула

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

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

Поскольку мы используем И с двумя условиями, формула возвращает ИСТИНА только тогда, когда оба условия возвращают ИСТИНА, применяя условное форматирование.

Использование других
ячеек для указания значений

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

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

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

С именованными
диапазонами

Лучший способ зафиксировать ссылки — использовать
именованные диапазоны, поскольку именованные диапазоны автоматически абсолютны.
Если вы называете ячейку E2 «нижняя_граница» и ячейку G2 «верхняя_граница», вы
можете написать формулу условного форматирования следующим образом:

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

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

Создание правил

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

Для этого, нужно нажать в любом подразделе меню условного форматирования на пункт «Другие правила…», расположенный в самом низу списка». Или же кликнуть по пункту «Создать правило…», который расположен в нижней части основного меню условного форматирования.

Открывается окно, где нужно выбрать один из шести типов правил:

1. Форматировать все ячейки на основании их значений;
2. Форматировать только ячейки, которые содержат;
3. Форматировать только первые и последние значения;
4. Форматировать только значения, которые находятся выше или ниже среднего;
5. Форматировать только уникальные или повторяющиеся значения;
6. Использовать формулу для определения форматируемых ячеек.

Согласно выбранному типу правил, в нижней части окна нужно настроить изменение описания правил, установив величины, интервалы и другие значения, о которых мы уже говорили ниже. Только в данном случае, установка этих значений будет более гибкая. Тут же задаётся, при помощи изменения шрифта, границ и заливки, как именно будет выглядеть выделение. После того, как все настройки выполнены, нужно нажать на кнопку «OK», для сохранения проведенных изменений.

Правила выделения ячеек

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

Кликаем по пункту меню «Правила выделения ячеек». Как видим, существует семь основных правил:

Рассмотрим применение этих действий на примерах. Выделим диапазон ячеек, и кликнем по пункту «Больше…».

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

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

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

После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».

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

По такому же принципу выделяются значения при применении правил «Меньше», «Между» и «Равно». Только в первом случае, выделяются ячейки меньше значения, установленного вами; во втором случае, устанавливается интервал чисел, ячейки с которыми будут выделяться; в третьем случае задаётся конкретное число, а выделяться будут ячейки только содержащие его.

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

Правило «Дата» применяется к ячейкам, которые содержат значения в формате даты. При этом, в настройках можно установить выделение ячеек по тому, когда произошло или произойдёт событие: сегодня, вчера, завтра, за последние 7 дней, и т.д.

Применив правило «Повторяющиеся значения» можно настроить выделение ячеек, согласно соответствию размещенных в них данных одному из критериев: повторяющиеся это данные или уникальные.

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

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

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

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

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

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

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

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

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

• < Назад
• Вперёд >

Новые статьи:

• Критерий Манна-Уитни
• Подключение MySQL в Excel
• Подключение Excel к SQL Server

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

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

Например, используя в работе набор из трех флажков, можно получить следующую картину: красные флажки будут стоять рядом с маленькими значениями (то есть менее 33%), желтые рядом со средними значениями (находящимися в диапазоне между 33 и 67%), а зеленые – с наибольшими значениями (более 67%).

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

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

Правила использования формул в условном форматировании

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

1. Нельзя ссылаться на данные в других листах или книгах. Но можно ссылаться на имена диапазонов (так же в других листах и книгах), что позволяет обойти данное ограничение.
2. Существенное значение имеет тип ссылок в аргументах формул. Следует использовать абсолютные ссылки (например, =СУММ($A$1:$A$5) на ячейки вне диапазона условного форматирования. А если нужно ссылаться на несколько ячеек непосредственно внутри диапазона, тогда следует использовать смешанные типы ссылок (например, A$1).
3. Если в критериях формула возвращает дату или время, то ее результат вычисления будет восприниматься как число. Ведь даты это те же целые числа (например, 01.01.1900 – это число 1 и т.д.). А время это дробные значения части от целых суток (например, 23:15 – это число 0,96875).

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

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

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

Глава 20. Условное форматирование и формулы массива

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Несколько важных фактов об условном форматировании на основе формул:

• Проверка условия возвращает значения ИСТИНА/ЛОЖЬ и применяется к диапазону ячеек.
• Условие проверяется для каждой ячейки в диапазоне. Если условие выполнено, ячейка получает форматирование. Если условие не выполнено, ячейка не форматируется.
• Для создания условия подходит любая формула, возвращающая значения ИСТИНА/ЛОЖЬ (если формула возвращает число, то любое число, кроме нуля приравнивается к значению ИСТИНА, а значение ноль – ЛОЖЬ).
• Условное форматирование можно использовать, как с обычными формулами, так и с формулами массива.
• Условное форматирование является волатильным (летучим): оно пересчитывается довольно часто, даже, когда вы напрямую не задействуете диапазон, в отношении которого применено условное форматирование (см. главу 13). Это замедляет работу.

Скачать заметку в формате Word или pdf, примеры в формате Excel

Чтобы создать условное форматирование с помощью формул:

Выделите диапазон ячеек

Обратите внимание, активна левая верхняя ячейка диапазона (она светлее других).
Пройдите по меню ГЛАВНАЯ –>Условное форматирование –>Создать правило, или нажав, и удерживая клавишу Alt, последовательно нажмите Я, Л, С. Откроется диалоговое окно Создание правила форматирования.
Выберите опцию Использовать формулу для определения форматируемых ячеек.
Поместите курсор в поле Форматировать значения, для которых следующая формула является истинной.
Наберите формулу, которая будет применяться к активной ячейке выделенного диапазона

Примените абсолютные и относительные ссылки, как если бы вы копировали формулу на весь диапазон (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel). Помните, что условие, которое вы создаете, будет оцениваться отдельно для каждой ячейки, чтобы определить, должна ли эта ячейка получить форматирование.
Нажмите кнопку Формат и выберите любую комбинацию форматирования на четырех вкладках: Число, Шрифт, Граница и Заливка.
Нажмите несколько раз кнопку ОК, закрывая все диалоговые окна.

Рассмотрим два примера условного форматирования на основе формул массива. Первый пример (рис. 20.1) показывает, как выделить строку, содержащую наименьшее значение для города, введенного в ячейку A11. Выделимте диапазон А3:В8 и откройте диалоговое окно Создание правила форматирования, как описано выше. (Если вы выполняете пример в приложенном файле Excel, у вас откроется окно Изменение правила форматирования, так как правило уже создано)

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

Обратите внимание, что формула массива возвращает одно и тоже значение для каждой ячейки в диапазоне. Но только в ячейках А5 и В5 это значение равно проверяемому. Если ячеек много волатильный характер вычислений может замедлить работу. Второй пример (рис. 20.2), обходит эту трудность, используя вспомогательную ячейку.

Рис. 20.1. Формула массива в диалоговом окне Изменение правила форматирования; Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Цель форматирования (рис. 20.2) – выделить строки с N лучшими результатами гонщика, указанного в ячейке А17. Число N указано в ячейке В17. Формулы массива, вычисляющие второе лучшее время для гонщика, приведены в ячейках D18 и D19 (используйте любую из них). Формула =И($A3=$A$17;$B3 Архивы

Для чего нужно условное форматирование ячеек в Excel?

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

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

По одному условию

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

Что нас может заинтересовать в этих заказах? Начнем с выделения тех заказов, которые стоят более 10 000.

У нас эти данные записаны в столбце F. Выделите мышкой диапазон значений значения ордера F2: F22.

Затем нажмите Меню -> Формат -> Условное форматирование.

Нажмите «Форматировать ячейки» и в появившемся окне выберите «Больше или равно», а затем в поле чуть ниже укажите значение 200. Это означает, что все ячейки в выбранном нами диапазоне, значение которых больше или равно 200, он будет выделен в формате, который мы создали здесь, например: жирным красным шрифтом на желтом фоне.

Сразу видим результат: изменилось расположение отдельных ячеек.

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

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

Выделение по условию целиком строки или столбца.

Выделение строки.

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

Прежде всего выделяем при помощи мыши весь диапазон интересующих нас данных – A2:D21. Шапку таблицы выделять не нужно. Затем действуем по уже отработанной схеме: вызываем меню функции и выбираем последний пункт – «Использовать формулу для определения форматируемых ячеек» (1). Далее записываем выражение (2):

Мы должны закрасить вторую строку таблицы в зависимости от значения в С2. Здесь есть маленькая хитрость.

Обратите внимание, что абсолютная ссылка (знак $) установлена здесь только на столбец С. То есть, мы проверяем на условие «Бразилия» в выделенном нами диапазоне все позиции в этом столбце, то есть С2, С3, C4 и так далее

А вот закрашивать будем всю строку, так как ранее выделена была вся таблица. Для этого выбираем вариант оформления (3): цвет фона или шрифта, либо оба.

Напомню, что знак $, стоящий перед буквой столбца, означает абсолютную ссылку на этот столбец. А если знак $ находится перед цифрой, то абсолютная ссылка установлена на строку.

Выделение столбца.

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

Тем не менее, давайте рассмотрим пример с выделением по условию столбцов таблицы.

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

Как и в предыдущем примере, определим для начала диапазон, который мы будем форматировать: =$B$3:$S$7. И вновь будем использовать формулу (2) для определения условия.

Функция ДЕНЬНЕД позволяет определить номер дня недели по указанной дате. Цифра 2 означает, что используется привычный нам порядок, когда первый день недели – это понедельник.

Таким образом, если номер окажется больше 5 (то есть, это будет суббота или воскресенье), то необходимо применить указанный нами формат (3) и закрасить выходной день.

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

И после этого применять формат к столбцу.

Функция ЕСЛИ в Excel. Примеры (с несколькими условиями)

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

Вторая модернизация коснулась вспомогательного класса Buffer, который создается сканером для чтения входящего потока символов. “Из коробки” Coco/R содержит пару реализаций Buffer и UTF8Buffer. Оба они работают с потоком. Нам же поток не нужен: достаточно работы со строкой. Для этого создадим третью реализацию StringBuffer, попутно выделив интерфейс IBuffer:

1 сторона	2 сторона	3 сторона	существование треугольника	периметр треугольника
3	5	7	=ЕСЛИ(И(A2 <e2;B2<f2;C2<d2);»существует»;»не существует»)	15
4	4	8	не существует	нет
1	2	3	не существует	нет
5	2	4	существует	11
0,7	1,2	3,5	не существует	нет
1,8	2	3	существует	6,8
5	5	10	не существует	нет
5,7	2,9	8	существует	16,6

Технические характеристики → Полезное видео → Общие данные → Таблица размеров → Полезные советы → Как правильно рассчитать → Сыпучие продукты → Натяжные потолки и технологии→ Мера жидких продуктов

1) Как использовать условное форматирование?

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

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

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

А диапазон G2:G5 заполняется формулами: =СУММ(B2:F2).

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

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

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

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