Как сделать ячейку активной по условию в excel?

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хо…

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

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

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

Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»:

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

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

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

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

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

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

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

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

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

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

Гистограммы

Если заливка ячейки цветом вас не устраивает – применяйте инструмент «Гистограмма». Предлагаемая окраска легче воспринимается на глаз в большом объеме информации, функциональные правила подстраиваются под требования пользователя.

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

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

Наборы значков

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

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

Функция «Создать правило» полностью дублирует «Другие правила» из перечисленных выше, создает выборку изначально по требованию пользователя.

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

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

Отбор ячеек по датам

Чтобы разобраться, как в excel сделать цвет ячейки от значения установленной даты, рассмотрим пример с датами закупок у поставщиков в январе 2019 года.

Для применения такого отбора нужны ячейки с установленным форматом «Дата». Для этого перед внесением информации выделите необходимый столбец, щелкните правой кнопкой мыши и в меню «Формат ячеек» найдите вкладку «Число».

Установите числовой формат «Дата» и выберите его тип по своему усмотрению.

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

• выделяем столбцы с датами (в нашем случае за январь);
• находим инструмент «Условное форматирование»;
• в «Правилах выделения ячеек» выбираем пункт «Дата»;
• в правой части форматирования открываем выпадающее окно с правилами;
• выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
• в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
• выборка окрасилась, жмем «ОК».

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

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

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

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

• выделяем столбец с январскими закупками;
• кликаем инструмент «Условное форматирование»;
• переходим в «Правила выделения ячеек»;
• пункт «Больше…»;
• в правой части форматирования устанавливаем сумму 100 000 рублей;
• в левом поле переходим на вкладку «Пользовательский формат» и выбираем синий цвет;
• необходимая выборка окрасилась в синий цвет, жмем «ОК».

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

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

Источник: https://FreeSoft.ru/blog/kak-v-excel-vydelit-yacheyku-tsvetom-pri-opredelennom-uslovii-primery-i-metody

Суммирование ячеек в Excel по условию — подробная инструкция

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

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

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

Суммирование ячеек в Excel по условию

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

Функция «СУММ»

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

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

   Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию

2. Выбрать «fx» и в списке найти действие «СУММ», это можно сделать в категории «Полный алфавитный перечень» или в «Математических».
   

   Нажимаем по значку функции «fx»

   

   В списке «Категория» выбираем «Полный алфавитный перечень»

   

   Находим и щелкаем по функции «СУММ», нажимаем «ОК»

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

   Ставим курсор мышки в поле «Число1», щелкаем по ячейке со значением

   

   Ставим курсор мышки поочередно в поля «Число2» и «Число3», щелкаем по ячейкам со значением, нажимаем «ОК»

   Важно! Знайте, что программа проигнорирует логическое или текстовое значения.

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

   В выделенной ранее ячейке появится результат функции «СУММ»

При суммировании чисел с одним условием применяйте функцию «СУММЕСЛИ»

Для этого:

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

   Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию

2. Нажмите на кнопку «fx» («Вставить функцию»), которая расположена перед строкой формул.

   Нажимаем по значку функции «fx»

3. Возникнет «Мастер функций». Выберите «СУММЕСЛИ», это можно сделать в категориях: «Полный алфавитный перечень» или в «Математические».

   В списке «Категория» выбираем «Математичсекие», находим и щелкаем по функции «СУММЕСЛИ», затем «ОК»

4. Проявится всплывающее меню «Аргументы функции».

   Окно «Аргументы функции»

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

   Большим плюсом нажмем на первую ячейку и, не отпуская, доведем до последней ячейки в диапазоне

6. В строку «Критерий» впишем «Васечкин» (можно писать как с кавычками, так и нет, программа их выставит автоматически).

   В поле «Критерий» вписываем «Васечкин»

   Примечание! Можно вводить также математическое выражение.

7. Отмечаем диапазон суммирования из столбца «Сумма».

   В поле «Диапазон суммирования», вводим ячейки из столбца «Сумма», выделив первую ячейку и, не отпуская левую кнопку мышки, протягиваем до последней ячейки

8. Нажмите «ОК». Результат действия появится в выбранной ранее ячейке.

   Проверяем данные, нажимаем «ОК»

   В ячейке появится результат, заданной функции

На заметку! Эту функцию также можно ввести вручную, используя базовую запись: «=СУММЕСЛИ(x), где х – диапазон, критерий и диапазон суммирования, которые перечисляются через «;». Например, «=СУММЕСЛИ(А1:А2;«Условие»;В1:В2)».

Вводим вручную данные, в выбранную ячейку, нажимаем «Enter»

Однако, если нужно отобрать информацию по нескольким разным критериям?

Если вы хотите узнать, как написать формулу в Excel, а также обучиться азам работы в Excel, вы можете прочитать статью об этом на нашем портале.

Функция «СУММЕСЛИМН»

Данная функция работает, как и «СУММЕСЛИ», но дает больше возможностей в задании параметров математической операции. Для применения этой функции, вам нужно выполнить следующие шаги:

1. Отметьте ячейку.

   Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию

2. Нажмите на «fx» или «Вставить функцию».

   Нажимаем по значку функции «fx»

3. Появится окно, где из перечня выберите функцию «СУММЕСЛИМН».

   Выбираем функцию «СУММЕСЛИМН»

4. Кликните на «ОК» или два раза нажмите на «СУММЕСЛИМН».

   Щелкаем по функции дважды или нажимаем «ОК»

5. Проявится меню «Аргументы функции».

   В поле «Диапазон суммирования», вводим ячейки из столбца «Сумма», протянув левую кнопку мышки от первой до последней ячейки

   Важно! Обратите внимание – в отличие от «СУММЕСЛИ», в данном окне сначала задается диапазон суммирования, а потом уже условия. Также можно ввести до 127 условий.

6. Заполните диапазоны условий и сами условия.

   В поле «Диапазон условия1» вводим ячейки из столбца «Товар», выделив их левой кнопкой мышки

   В поле «Условие1» вводим «яблоки» или другое значение, результат которого мы хотим получить

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

   Щелкаем по кнопке «ОК»

   Примечание! Более подробную инструкцию вы можете найти в этой статье чуть выше.

8. Предположим, нам нужно узнать, на какую сумму Васечкин продал яблок. У нас есть всего 2 условия – продавец должен быть Васечкин, а товар – яблоки. В нашем случае, аргументы функции будут выглядеть следующим образом.

   Вводим данные в соответствующие поля, задаем необходимые условия в кавычках, щелкаем по кнопке «ОК»

   Результат, высчитанный функцией «СУММЕСЛИМН»

Эту функцию также можно ввести вручную, однако это будет довольно сложно, поскольку присутствует много условий. Чтобы программа работала корректно, и вы не потеряли время, пытаясь найти ошибку в длинной записи, рекомендуем все же воспользоваться действием через «fx».

С наглядной инструкцией вы также можете ознакомиться в видео.

Видео — Суммирование по условию в Excel, функция «СУММЕСЛИМН»

Источник: https://pc-consultant.ru/rabota-na-komp-yutere/summirovanie-jacheek-v-excel-po-usloviju/

Обучение условному форматированию в Excel с примерами

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

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.



Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ!, а не абсолютная.

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

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Сравните:

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

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

1. Выделите первую ячейку диапазона с условным форматированим.
2. Откройте меню инструмента, нажмите «Управление правилами».

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

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

• 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
• 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

• Выделяем диапазон с датами.
• Применим к нему «УФ» — «Дата».
• В открывшемся окне появляется перечень доступных условий (правил):
• Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

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

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

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

1. Порядок заполнения условий для форматирования «завершенных проектов»:
2. Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).
3. Аналогично задаем правила форматирования для незавершенных проектов.
4. В «Диспетчере» условия выглядят так:
5. Получаем результат:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

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

Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке

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

Пусть в диапазоне А6:С16 имеется таблица с перечнем работ, сроками выполнения и статусом их завершения (см. файл примера).

Задача1 — текстовые значения

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

Решение1

Создадим небольшую табличку со статусами работ в диапазоне Е6:Е9.

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

Убедимся, что выделен диапазон ячеек А7:С17 (А7 должна быть активной ячейкой). Вызовем команду меню .

• в поле «Форматировать значения, для которых следующая формула является истинной» нужно ввести =$C7=$E$8 (в ячейке Е8 находится значение В работе). Обратите внимание на использоване смешанных ссылок;
• нажать кнопку Формат;
• выбрать вкладку Заливка;
• выбрать серый цвет;
• Нажать ОК.

ВНИМАНИЕ: Еще раз обращаю внимание на формулу =$C7=$E$8. Обычно пользователи вводят =$C$7=$E$8, т.е. вводят лишний символ доллара.

Нужно проделать аналогичные действия для выделения работ в статусе Завершена. Формула в этом случае будет выглядеть как =$C7=$E$9, а цвет заливки установите зеленый.

В итоге наша таблица примет следующий вид.

Примечание: Условное форматирование перекрывает обычный формат ячеек. Поэтому, если работа в статусе Завершена, то она будет выкрашена в зеленый цвет, не смотря на то, что ранее мы установили красный фон через меню .

Как это работает?

В файле примера для пояснения работы механизма выделения строк, создана дополнительная таблица с формулой =$C7=$E$9 из правила Условного форматирования для зеленого цвета. Формула введена в верхнюю левую ячейку и скопирована вниз и вправо.

Как видно из рисунка, в строках таблицы, которые выделены зеленым цветом, формула возвращает значение ИСТИНА. 

В формуле использована относительная ссылка на строку ($C7, перед номером строки нет знака $).

Отсутствие знака $ перед номером строки приводит к тому, что при копировании формулы вниз на 1 строку она изменяется на =$C8=$E$9, затем на =$C9=$E$9, потом на =$C10=$E$9 и т.д. до конца таблицы (см.

ячейки G8, G9, G10 и т.д.). При копировании формулы вправо или влево по столбцам, изменения формулы не происходит, именно поэтому цветом выделяется вся строка.

В случае затруднений можно потренироваться на примерах, приведенных в статье Условное форматирование в MS EXCEL.

Прием с дополнительной таблицей можно применять для тестирования любых формул Условного форматирования.

Рекомендации

При вводе статуса работ важно не допустить опечатку. Если вместо слово Завершена, например, пользователь введет Завершено, то Условное форматирование не сработает.

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

Чтобы быстро расширить правила Условного форматирования на новую строку в таблице, выделите ячейки новой строки (А17:С17) и нажмите сочетание клавиш CTRL+D. Правила Условного форматирования будут скопированы в строку 17 таблицы.

Задача2 — Даты

Предположим, что ведется журнал посещения сотрудниками научных конференций (см. файл примера лист Даты).

Источник: https://excel2.ru/articles/vydelenie-strok-tablicy-v-ms-excel-v-zavisimosti-ot-usloviya-v-yacheyke

Закрасить ячейку по условию или формуле

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

Инструкция для Excel 2010

ВКЛЮЧИТЕ СУБТИТРЫ!

Как это сделать в Excel 2007

ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».

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

Как это сделать в Excel 2003

ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.

Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.

Покоряйте Excel и до новых встреч!

Источник: https://myblaze.ru/kak-v-excel-zakrasit-yacheyku-po-usloviyu-ili-formule/

Как в EXCEL сложить числа в ячейках по определённому условию

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

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

Для примера возьмём категорию расходов «Покупки в магазинах». Нам надо, чтобы EXCEL находил все затраты по данной категории в детальной статистике, суммировал расходы по данной категории и записывал полученную сумму в ячейку D10.

Сначала запишем готовую формулу, которую вставляем в ячейку D10, а потом начнём разбираться в деталях. Готовая формула выглядит следующим образом (только для нашей статьи):

=СУММЕСЛИ($G$5:$G$300;(«Покупки в магазинах»);$H$5:$H$300)

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

• СУММЕСЛИ – этим условием мы говорим, что в ячейку надо записывать сумму значений определённых ячеек, если они соответствуют определённым условиям;
• $G$5:$G$300 – здесь мы указываем EXCEL, в каком столбце нам надо искать условие для выборки. В нашем случае поиск происходит в столбце G начиная со строки 5 и заканчивая строкой 300;
• («Покупки в магазинах») – здесь мы указываем искомое условие и по этому условию будут суммироваться значения ячеек, которые мы указываем далее…;
• $H$5:$H$300 – здесь мы указываем столбец, из которого будут браться числа для суммирования. В нашем случае значения берутся в столбце H начиная со строки 5 и заканчивая строкой 300.

Подводя итог можно сказать, что EXCEL суммирует только те значения из диапазона H5:H300, для которых соответствующие значения из диапазона G5:G300 равны «Покупки в магазинах» и записывает результат в ячейку D10.

Соответствующим образом можно в EXCEL сложить числа в ячейках по любому условию.

Знак $ в формуле используется для того, чтобы при копировании формулы с ячейки D10 в другие ячейки не происходило смещение. Рассмотрим пример формулы без знака $. К примеру, в ячейке D10 у нас вписана формула:

=СУММЕСЛИ(G5:G300;(«Покупки в магазинах»);H5:H300)

Далее мы хотим выводить сумму обедов в ячейке D11. Чтобы нам не переписывать формулу, нам можно копировать ячейку D10 и вставить в ячейку D11. Благодаря этому формула будет вставлена в D11, но тут мы можем заметить, что формула изменила значения заменив 5 на 6 и 300 на 301:

=СУММЕСЛИ(G6:G301;(«Покупки в магазинах»);H6:H301)

Произошло смещение. Если мы скопируем формулу в D12, то увидим уже смещение на 2 и так далее. Чтобы этого избежать мы формулу пишем со знаком $. Такие особенности EXCEL.

Таблица «Учёт ежемесячных расходов» — скачать

Источник: https://victorz.ru/2016042149

Перекрестное выделение активной ячейки

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

Перекрестное выделение

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

Итак, начнём.

У нас имеется файл с большой таблицей, которая начинается с ячейки C5 (значения больше 400 000 выделены в таблице красным цветом с помощью условного форматирования). Шапка и первый столбец закреплены, но определять к какому месяцу, году и товару относится выделенная в середине таблицы ячейка всё равно неудобно.

Таблица по умолчанию

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

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

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

Где посмотреть адрес активной ячейки

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

«Главная» — «Условное форматирование» — «Создать правило«

Команда на ленте

В открывшемся окне выберите «Использовать формулу для определения форматируемых ячеек«

Создание правила с формулой

Теперь самая интересная часть. Нужно ввести формулу, которая будет отвечать за подсветку строки и столбца активной ячейки. Для этого нам понадобится функция ЯЧЕЙКА().

Для этого нужно указать в качестве ЕДИНСТВЕННОГО аргумента функции слово «строка» или «столбец» соответственно.

Чтобы подсветить строку, в которой находится активная ячейка, нужно прописать формулу:

=ЯЧЕЙКА(«строка»)=СТРОКА(D6)

Обратите внимание на аргумент функции СТРОКА. Это ячейка D6, то есть та ячейка, с которой мы начинали выделение диапазона для применения правила условного форматирования. Если Вы начинаете выделение диапазона с другой ячейки, то должны указать ее. И обязательно без закрепления (знака $), чтобы правило работало на весь диапазон.

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

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

Если мы хотим, чтобы выделялась вся строка, но активная ячейка не подсвечивалась (как в первой гифке), то нужно добавить условие:

=И(ЯЧЕЙКА(«строка»)=СТРОКА(D6);ЯЧЕЙКА(«столбец»)СТОЛБЕЦ(D6))

В этой формуле функция И объединяет два условия: заливка выполняется, только если строка ячейки совпадает со строкой активной ячейки, а столбец не совпадает (совпадение столбца будет только в самой активной ячейке). Таким образом будет залита вся строка, кроме активной ячейки.

• Так как нам нужно выделять и строку, и столбец активной ячейки, сделаем аналогичную формулу для подсветки столбца:
• =И(ЯЧЕЙКА(«столбец»)=СТОЛБЕЦ(D6);ЯЧЕЙКА(«строка»)СТРОКА(D6))
• Осталось объединить эти формулы в одно условие с помощью функции ИЛИ (не запутайтесь в скобках).
• =ИЛИ(И(ЯЧЕЙКА(«строка»)=СТРОКА(D6);ЯЧЕЙКА(«столбец»)СТОЛБЕЦ(D6));И(ЯЧЕЙКА(«столбец»)=СТОЛБЕЦ(D6);ЯЧЕЙКА(«строка»)СТРОКА(D6)))
• Именно эту формулу и нужно вставить в строку формулы правила условного форматирования.

Строка ввода формулы

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

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

Теперь жмите ОК

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

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

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

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

Открытие модуля листа

Перед Вами откроется редактор VBE с окном кода указанного листа. Здесь Вам нужно выбрать в выпадающем списке слева вверху «Worksheet», а в выпадающем списке справа вверху — «SelectionChange».

В окне кода появится следующая конструкция:

1. Это процедура, которая будет запускать каждый раз, когда на указанном листе будет происходить изменение активной ячейки. А так как нам нужен пересчет листа в этот момент, то мы должны ввести между строками Private Sub и End Sub строчку:
2. ActiveSheet.Calculate
3. Должна получиться вот такая конструкция:

Теперь можете закрыть окно редактора VBE на крестик в правом верхнем углу. Поздравляем! Вы создали код обработки события! Сохраните файл на всякий случай (сохранять теперь придется в формате .xlsm или .xlsb, чтобы работали макросы).

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

Создание выключателя перекрестного выделения

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

Итак, выделите какую-то пустую ячейку в пределах видимости. Перейдите на вкладку «Данные» — «Проверка данных» — «Список» и введите в строку «Источник» слова «Да» и «Нет» через точку с запятой.

Создание списка

• Теперь в ячейке появился выпадающий список с пунктами «Да» и «Нет» (можете для удобства добавить к ним условное форматирование, например, «Да» заливать зеленым, а «Нет» — красным цветом)
• Мы хотим, чтобы при выборе в ячейке слова «Да» перекрестное выделение работало, а при слове «Нет» — отключалось.
• Для этого нужно добавить в наше правило, созданное в предыдущем пункте, функцию И с еще одним условием. Принцип такой:

=И($C$3=»Да»;»Наше правило»)

То есть, правило будет срабатывать только если выполняются все условия, описанные в предыдущем пункте, и если в ячейке $C$3 значение «Да».

Ячейка $C$3 может быть заменена на любую другую, но обязательно должна быть полностью закреплена двумя знаками $

1. Теперь формула для перекрестного выделения примет вид:
2. =И($C$3=»Да»;ИЛИ(И(ЯЧЕЙКА(«строка»)=СТРОКА(D6);ЯЧЕЙКА(«столбец»)СТОЛБЕЦ(D6));И(ЯЧЕЙКА(«строка»)СТРОКА(D6);ЯЧЕЙКА(«столбец»)=СТОЛБЕЦ(D6))))
3. Готово! Теперь перекрестное выделение включается, если в ячейке выбрано «Да» и выключается, если выбрано «Нет»

Конечный результат

Управление перекрестным выделением

Напоследок пару нюансов управления перекрестным выделением.

• Чтобы перекрестное выделение не перезатирало другие правила условного форматирования, опустите его в списке правил в самый низ. Выберите «Главная» — «Условное форматирование» — «Управление правилами«, кликните нужное правило и с помощью стрелки «Вниз» опустите его в конец очереди;

Изменение приоритета правил

• Чтобы расширить/сузить диапазон действия перекрестного выделения, достаточно в диспетчере правил (рисунок выше) изменить диапазон действия в поле «Применяется к:» (но ячейка, указанная в тексте правила (D6 в нашем случае) должна оставаться первой в указываемом диапазоне);
• Перекрестное выделение создается только для одного листа. Чтобы создать его на другом листе или в другом файле придется повторить все шаги заново.

Надеемся, подобный прием пригодится Вам в работе. Файл-пример из данной статьи можете скачать вот по этой ссылке.

• Поддержать наш проект и его дальнейшее развитие можно вот здесь.
• Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
• С уважением, команда tDots.ru

Источник: https://zen.yandex.ru/media/id/59affb7afd96b11e8eadd771/5a98ff3f3dceb774ad79c268

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

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

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

лог_выражение – это проверяемое условие. Например, A230) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ.

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

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

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

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться.

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

в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая.

Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура.

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

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

Источник: https://statanaliz.info/excel/funktsii-i-formuly/neskolko-uslovij-funktsii-esli-eslimn-excel/