Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция меньше или равно в excel

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

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

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

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

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

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

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

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

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

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

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

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

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

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

Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.

Логические функции Excel

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

= Больше или равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ (IF) имеет следующий синтаксис:

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

Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае — 20:

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

=ЕСЛИ(А1>=4;»Зачет сдал»;»Зачет не сдал»)

Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0.

Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:

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

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT) — позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

=И(логическое_значение1;логическое_значение2. )
=ИЛИ(логическое_значение1;логическое_значение2. )

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

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

Приведем пример. Пусть Excel возвращает текст «Прошел», если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

Если значение в ячейке А1 является целым числом, формула читается следующим образом: «Если значение в ячейке А1 равно 100, возвратить строку «Всегда». В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить «Обычно». В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку «Иногда». И, если ни одно из этих условий не выполняется, возвратить строку «Никогда». Всего допускается до 7 уровней вложения функций ЕСЛИ.

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

=ИСТИНА()
=ЛОЖЬ()

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

В противном случае формула возвратит «Стоп».

Функция ЕПУСТО

Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:

=ЕПУСТО(значение)

Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.

В начало страницы

В начало страницы

Если больше или равно в excel

Функция ЕСЛИ

​Смотрите также​ «СУММ» используется для​ цену в 2​ кругозора спасибО)))​Видео: усложненные функции ЕСЛИ​возвращает значение ИСТИНА;​=ЕСЛИ(И(B14>=$B$7,C14>=$B$5),B14*$B$8,0)​Аргумент​

​В Excel существуют логические​ Например, это функции​ с другими функциями.​ формулу.​

​ которое мы укажем​ двух аргументов или​Если у простой функции​ требуется изменять) не​ дополнительные вычисления в​Функция ЕСЛИ — одна из​

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

Технические подробности

​ ЕСЛИ, СЧЕТЕСЛИ, СУММЕСЛИ​=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B11;НАИМЕНЬШИЙ(ЕСЛИ($F$2=A2:A11;СТРОКА(B2:B11)-1;»»);​=ЕСЛИ(A4=»Да»;100;»»)​ в формуле. Можно​ значение ИСТИНА/ЛОЖЬ.​ ЕСЛИ есть только​

​ рекомендуется вводить прямо​

​ зависимости от условий.​

​ самых популярных функций​

​Функция ЕСЛИ позволяет решать​

​Сформируем таблицу с исходными​

​ значение ИСТИНА, функция​​ЕСЛИ​

​СТРОКА()-5));»»)​Обратите внимание!​​ выбрать любые числа,​​»#ИМЯ?» в ячейке​

​ два результата (ИСТИНА​​ в формулу, поскольку​

​ Для выполнения нескольких​ в Excel. Она​​ многочисленные задачи, поэтому​​ параметрами:​

Простые примеры функции ЕСЛИ

    ​Логические функции в Excel​

    ​Функция ЕСЛИ​НЕ​​общие продажи больше​Обязательный аргумент. Первое проверяемое​и​

      ​ логические условия можно​

      ​Эта формула для​В третьем условии​​ слова, т.д.​Как правило, это указывает​ и ЛОЖЬ), то​ в будущем их​

      Читать еще:  Дополнения к опере для скачивания видео

      ​ сравнений можно использовать​ позволяет выполнять логические​ используется чаще всего.​Чтобы решить поставленную задачу,​ проверяют данные и​Функция И​возвращает значение ЛОЖЬ.​ или равны (>=)​ условие, вычисление которого​ЛОЖЬ()​ задавать в обычных​ примера взята из​ в формуле мы​Появившееся диалоговое окно​ на ошибку в​ у вложенных функций​ может быть затруднительно​ несколько вложенных функций​ сравнения значений и​

      ​Задача 1. Проанализировать стоимость​​ воспользуемся логической функцией​ возвращают результат «ИСТИНА»,​Функция ИЛИ​Ниже представлено несколько общих​ целевым продажам​ дает значение ИСТИНА​, которые не имеют​

      Начало работы

      ​ формулах, если необходимо​ статьи «Выбрать сразу​ поставили не «нуль»,​ заполнили так.​ формуле.​ ЕСЛИ может быть​ найти и изменить.​ ЕСЛИ.​ ожидаемых результатов. Самая​ товарных остатков после​ ЕСЛИ. Формула будет​ если условие выполняется,​Полные сведения о​ примеров использования функции​И​ или ЛОЖЬ.​ аргументов. Данные функции​ получить утвердительный ответ:​ много данных из​ а две двойные​В формуле «ЕСЛИ» нужно​Видео: расширенное применение функции​ от 3 до​

      Еще примеры функции ЕСЛИ

      ​ Гораздо удобнее помещать​

      ​Примечание:​ простая функция ЕСЛИ​ уценки. Если цена​​ выглядеть так: =ЕСЛИ(C2>=8;B2/2;B2).​ и «ЛОЖЬ», если​ формулах в Excel​НЕ​число договоров больше​

      B2;C2-B2;»>

        ​ существуют в основном​Да​ таблицы Excel» здесь.​ кавычки – это​ написать три условия.​​ ЕСЛИ​ 64 результатов.​ константы в собственные​ Если вы используете текст​ означает следующее:​ продукта после переоценки​​Логическое выражение «С2>=8» построено​

          ​Рекомендации, позволяющие избежать​как отдельно, так​ или равно (>=)​​Необязательный аргумент. Дополнительные проверяемые​ для обеспечения совместимости​или​ Этой формулой выбираем​ значит «Пусто» (оставить​ В формуле эти​Функция ЕСЛИМН (Office 365, Excel 2016​=ЕСЛИ(D2=1;»ДА»;ЕСЛИ(D2=2;»Нет»;»Возможно»))​

          ​ ячейки, в которых​

          ​ в формулах, заключайте​ЕСЛИ(это истинно, то сделать​ ниже средних значений,​ с помощью операторов​Рассмотрим синтаксис логических функций​ появления неработающих формул​ и в сочетании​ целевому, общие продажи​ условия, вычисление которых​ с другими электронными​Нет​ из таблицы товара​ ячейку пустой). Скопировали​ условия напишутся через​ и более поздние​Показанная на рисунке выше​ они будут доступны​ его в кавычки​ это, в противном​ то списать со​ отношения «>» и​ и примеры применения​Поиск ошибок в​ с функциями​ умножаются на процент​ дает значение ИСТИНА​ таблицами. Вы можете​. К примеру, задавая​ и переносим в​ формулу в ячейку​ точку с запятой.​ версии)​

          Операторы вычислений

          ​ формула в ячейке​ и их можно​ (пример: «Текст»). Единственное​ случае сделать что-то​ склада этот продукт.​​ «=». Результат его​​ их в процессе​​ формулах​​ЕСЛИ​​ премии. В противном​​ или ЛОЖЬ. Условий​ вводить значения ИСТИНА​ простые логические условия,​ лист заказа выбранные​

          Использование функции ЕСЛИ для проверки ячейки на наличие символов

          ​ В5.​Первое условие –​Усложненные функции ЕСЛИ: как​ E2 означает:​ будет легко найти​ исключение — слова ИСТИНА​

          ​ еще)​Работаем с таблицей из​ вычисления – логическая​

          ​ работы с программой​

          ​Сочетания клавиш и​​,​ случае возвращается значение​ может быть не​ и ЛОЖЬ прямо​​ Вы можете ответить​ покупателем позиции.​Третий пример.​ «Лог_выражение». Мы написали​ работать с вложенными​ЕСЛИ(D2 равно 1, то​ и изменить. В​ и ЛОЖЬ, которые​

            ​Поэтому у функции ЕСЛИ​

            ​ предыдущего раздела:​​ величина «ИСТИНА» или​ Excel.​ горячие клавиши в​И​ 0.​​ более 255.​ в ячейки или​ на такие вопросы:​Ещё есть функция​В следующей формуле​ А1=2 – это​

            ​ формулами и избежать​

            ​ вернуть текст «Да»,​ нашем случае все​ Excel распознает автоматически.​ возможны два результата.​​Для решения задачи используем​

            Пример вложенных функций ЕСЛИ

            ​ «ЛОЖЬ». В первом​Название функции​ Excel​и​Видео: усложненные функции ЕСЛИ​Примечания​ формулы, не используя​5 больше 8?​

              ​ «СЧЁТЕСЛИ» в Excel​

              ​ поставили в третьем​ значит, если в​ ошибок​​ в противном случае​ в порядке, так​Прежде чем написать оператор​ Первый результат возвращается​ формулу вида: .​ случае функция возвращает​Значение​Сочетания клавиш в​​ИЛИ​Использование вложенных функций​Аргументы должны давать в​ форму записи функции,​Содержимое ячейки A5 меньше​ — она считает​ условии знак «Тире».​ ячейке А1 стоит​Обучающие видео: усложненные функции​ ЕСЛИ(D2 равно 2,​ как здесь используется​

              Небольшое предупреждение

              ​ ЕСЛИ, подумайте, чего​ в случае, если​ В логическом выражении​ значение «В2/2». Во​Синтаксис​ Excel 2016 для Mac​

              ​.​ в формуле​ результате логические значения​ Excel все прекрасно​ 8?​ количество ячеек по​ =ЕСЛИ(A5=»Да»;100;»-«)​ цифра 2. Мы​ ЕСЛИ​ то вернуть текст​ только одна функция​ вы хотите достичь.​ сравнение истинно, второй —​ «D2​ втором – «В2».​Примечание​Логические функции (ссылка)​

              ​Формула​Функция ЕСЛИ​ (такие как ИСТИНА​ поймет.​А может равно 8?​ нашим условиям. Смотрите​В ячейку В6​ написали условие, по​Подсчет значений на основе​ «Нет», в противном​

              ​ ЕСЛИ, а ставка​ Какое сравнение вы​ если сравнение ложно.​Задача 2. Найти средние​Усложним задачу – задействуем​ИСТИНА​Функции Excel (по​

              Распространенные неполадки

              ​Если Вы уверены, что​​В Excel имеется ряд​​ статью «Функция «СЧЁТЕСЛИ​​ написали такую формулу.​​ которому нужно искать​ одного условия с​ случае вернуть текст​ налога с продажи​

              ​ пытаетесь выполнить? Написать​

              ​Если вы ищете информацию​ продажи в магазинах​ логическую функцию И.​

              См. также

              ​Не имеет аргументов, возвращает​ алфавиту)​

              ​=НЕ(A2>100)​Функция НЕ​ быть массивами или​

              ​ уже достаточно хорошо​ стандартных операторов, которые​ в Excel». Эту​ =ЕСЛИ(A6=»%»;1;»нет») Здесь в​

              ​ ячейки в столбце​ помощью функции СЧЁТЕСЛИ​

              ​ «Возможно»)).​ будет редко изменяться.​ оператор ЕСЛИ не​

              ​ о работе с​ сети.​ Теперь условие такое:​

              ​ логическое значение «ИСТИНА».​Функции Excel (по​A2​

              ​Полные сведения о​ ссылками, содержащими логические​ освоили эту тему,​

              ​ используются для задания​

              ​ функция может посчитать​

              ​ третьем условии написали​

              ​ А (ячейки, в​Подсчет значений на основе​

              ​Обратите внимание на​ Даже если она​

              ​ сложнее, чем выстроить​ несколькими операторами ЕСЛИ,​Составим таблицу с исходными​

              ​ если товар хранится​

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

              Как задать простое логическое условие в Excel

              ​ с функцией​ все ли проверяемые​ в противном случае.​ Excel ячейки в​ С1 слово «Да».​ «6». Можно написать​ с помощью функции​Нужно очень крепко подумать,​ приоритеты.​=ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)​Имя аргумента​​ В9 – условие.​​ или продукт хранится​​ «ЛОЖЬ».​​ Как так сделать?​ значение ЛОЖЬ.​ убедиться, что одно​ЕСЛИ​

              • ​ условия принимают значение​
              • ​ Задавая такое условие,​ определенных строках».​
              • ​ Если число будет​

              Операторы сравнения в Excel

              ​ любое слово в​ проверки ошибок​ чтобы выстроить последовательность​Иногда требуется проверить, пуста​В примере выше функция​Описание​ Третий аргумент –​

              Как задать условие в Excel

              ​ дольше 10 месяцев,​=И (Лог_знач. 1; Лог_знач.​И скажить как​=ЕСЛИ(ИЛИ(НЕ(A350)); A3; «Значение вне​ значение не равно​.​

              ​ ИСТИНА.​​ можно сравнивать текстовые​Можно в Excel​ меньше 2, то​ этом условии, т.д.​Логические функции​ из множества операторов​ ли ячейка. Обычно​ ЕСЛИ в ячейке​лог_выражение​ $C$2:$C$7 – диапазон​ его списывают.​ 2;…)​ написать чтоб EXCEL​

              ​ интервала»)​​ другому.​Формула​Функция​ строки без учета​ суммировать данные из​ ячейка должна оставаться​Третье условие –​Функции Excel (по алфавиту)​ ЕСЛИ и обеспечить​ это делается, чтобы​

              ​ D2 означает:​ (обязательно)​ усреднения; числовые значения,​Для решения используем логические​Принимает до 255 аргументов​ понял что надо​100 не меньше 0​Функция​Описание​И​

              ​ регистра. К примеру,​ таблицы по многим​ пустой. Скопировали формулу​ «Значение_если_ложь» — здесь​Функции Excel (по категориям)​ их правильную отработку​ формула не выводила​

              ​ЕСЛИ(C2 больше B2, то​​Условие, которое нужно проверить.​ которые берутся для​ функции ЕСЛИ и​ в виде условий​ «Больше или равно»,»Меньше​ (ЛОЖЬ) и больше​

              ​НЕ​=И(A2>1;A2​​возвращает значение ИСТИНА,​​ сравнивая «ЯНВАРЬ» и​​ разным условиям. Например,​​ по столбцу С.​ мы напишем, что​Часто применяемая,​ по каждому условию​ результат при отсутствии​ вернуть текст «Превышение​значение_если_истина​ расчета среднего арифметического.​ ИЛИ: =10);»списан»;»»)’ class=’formula’>.​ или ссылок. Обязательным​ или равно», «Не​ чем 50 (ИСТИНА),​меняет значение своего​

              ​Возвращает значение ИСТИНА, если​ если в результате​ «январь» формула возвратит​ в функции «ЕСЛИ»​ Получилось так.​ нужно писать, если​логическая функция в Excel​ на протяжении всей​ входного значения.​ бюджета», в противном​​ (обязательно)​​Функция СРЗНАЧЕСЛИ сопоставляет значение​​ Условие, записанное с​​ является первый.​

              Читать еще:  Как уменьшить масштаб печати в excel

              ​ ровняется». Так для​

              Функция И

              ​ поэтому функция​​ аргумента на обратное.​ число в ячейке​ вычисления всех аргументов​ ИСТИНА.​ можно написать одно​Пятый пример.​

              Пример

              Технические подробности

              ​ в ячейке А1​​ — функция «ЕСЛИ»​​ цепочки. Если при​В данном случае мы​ случае вернуть текст​Значение, которое должно возвращаться,​ ячейки В9 (№1)​ помощью логической операции​ИЛИ​ кругозора))))​НЕ​

              ​Обычно функция​​ A2 больше 1​​ получается значение ИСТИНА,​=A1>B1​ условие поиска данных​Можно функцией «ЕСЛИ»​​ будет написано НЕ​​считает данные ячеек​ вложении операторов ЕСЛИ​ используем ЕСЛИ вместе​ «В пределах бюджета»)​ если​ со значениями в​ ИЛИ, расшифровывается так:​Показывает результат «ИСТИНА», если​​Шит​​изменяет значения аргументов​​НЕ​​И​​ и значение ЛОЖЬ,​​— Следующая формула​ для суммирования. В​ посчитать итог теста,​

              ​ по условию, которое​​ вы допустите малейшую​

              ​ с функцией ЕПУСТО:​​=ЕСЛИ(C2>B2;C2-B2;0)​​лог_выражение​

              Использование ЕСЛИ с функциями И, ИЛИ и НЕ

              Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

              =ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

              Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ (И), только одно условие должно иметь такое значение (ИЛИ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.

              Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.

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

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

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

              Условие, которое нужно проверить.

              Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

              Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

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

              И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

              ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

              НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

              Примеры

              Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.

              Ниже приведены формулы с расшифровкой их логики.

              =ЕСЛИ(И(A2>0;B2 0;B4 50);ИСТИНА;ЛОЖЬ)

              Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

              Если значение A7 («синий») НЕ равно «красный», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

              Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.

              Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

              Ниже приведены формулы с расшифровкой их логики.

              Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

              =ЕСЛИ(И(A3>B2;A3 B2;A4 B2);ИСТИНА;ЛОЖЬ)

              Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

              Использование операторов И, ИЛИ и НЕ с условным форматированием

              Вы также можете использовать операторы И, ИЛИ и НЕ в формулах условного форматирования. При этом вы можете опустить функцию ЕСЛИ.

              На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.

              «Изменить правило» с параметром «Формула»» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

              Вот как будут выглядеть формулы для примеров с датами:

              7 примеров использования формулы СУММЕСЛИ в Excel с несколькими условиями

              В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif) в таблицах Excel. Начнем с самых простых случаев, как можно использовать при этом знаки подстановки, назначить диапазон суммирования, работать с числами, текстом и датами. Особо остановимся на том, как использовать сразу несколько условий. И, конечно, мы применим новые знания на практике, рассмотрев несложные примеры.

              Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel, с 2016 по 2003 год. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.

              Как пользоваться СУММЕСЛИ в Excel – синтаксис

              Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.

              Синтаксис функции выглядит следующим образом:

              =СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])

              Диапазон – это область, которую мы исследуем на соответствие определённому значению.

              Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.

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

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

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

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

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

              Примеры использования функции СУММЕСЛИ в Excel

              Сумма если больше чем, меньше, или равно

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

              D2:D21 – это координаты, в которых мы ищем значение.

              I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.

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

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

              Можно указать его прямо в формуле, как это сделано в I13

              =СУММЕСЛИ(D2:D21;»

              В I3 запишем выражение:

              F2:F21 – это область, в которой мы отбираем подходящие значения.

              I2 – здесь записано, что именно отбираем.

              E2:E21 – складываем числа, соответствующие найденным совпадениям.

              Конечно, можно указать параметр отбора прямо в выражении:

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

              Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.

              Подстановочные знаки для частичного совпадения.

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

              Читать еще:  Как переименовать лист в книге excel

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

              Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.

              Символ “?” позволяет заменить собой один любой символ.

              Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).

              Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –

              =СУММЕСЛИ(C2:C21;I2;E2:Е21) , где в E2 записано *[слово]*

              =СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)

              где * вставлены прямо в выражение и «склеены» с нужным текстом.

              • “*черный*” — мы ищем фразу, в которой встречается это выражение, а до него и после него – любые буквы, знаки и числа. В нашем случае этому соответствуют “Черный шоколад” и “Супер Черный шоколад”.
              • “Д?” — необходимо слово из 2 букв, первая из которых “Д”, а вторая – любая. В нашем случае подойдет “Да”.
              • “. ” — найдем слово из любых 3 букв

              Этому требованию соответствует “Нет”.

              • “. *” — текст из любых 7 и более букв.

              Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.

              • “З*” — мы выбираем фразу, первая буква которой “З”, а далее – любые буквы, знаки и числа. Это “Золотой” и “Зеленый”.
              • “Черный*” — подходит фраза, которая начинаются именно с этого слова, а далее – любые буквы, знаки и числа. Подходит “Черный шоколад”.

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

              ), поставив его перед этими символами. Тогда * и ? будут считаться обычными символами, а не шаблоном:

              Точная дата либо диапазон дат.

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

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

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

              Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.

              Рассчитаем за вчерашний день.

              =СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)

              СЕГОДНЯ()-1 как раз и будет «вчера».

              Складываем за даты, которые предшествовали 1 февраля.

              А если нас интересует временной интервал «от-до»?

              Мы можем рассчитать итоги за определённый период времени. Для этого применим маленькую хитрость: разность функций СУММЕСЛИ. Предположим, нам нужна выручка с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что реализовано до 4 февраля.

              =СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21) — СУММЕСЛИ(A2:A21;»

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

              Точно такой же результат даёт использование вместо звездочки пары знаков «больше» и «меньше» — <>.

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

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

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

              =СУММЕСЛИ(F2:F21;«»;E2:E21)

              Сумма по нескольким условиям.

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

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

              1. СУММЕСЛИ + СУММЕСЛИ

              Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).

              Это самое простое решение, но не самое универсальное и далеко не единственное.

              2. СУММ и СУММЕСЛИ с аргументами массива.

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

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

              Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:

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

              А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.

              =СУММ(СУММЕСЛИ($C$2:$C$21; ;$E$2:$E$21))

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

              3. СУММПРОИЗВ и СУММЕСЛИ.

              А если вы предпочитаете перечислять критерии в какой-то специально отведенной для этого части таблицы? Можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих произведений.

              Вот как это будет выглядеть:

              в H3 и H4 мы запишем критерии отбора.

              Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:

              =СУММПРОИЗВ(СУММЕСЛИ(C2:C21; ;E2:E21))

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

              Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.

              Почему СУММЕСЛИ у меня не работает?

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

              1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.

              Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив <1,2,3>, Excel выдаст сообщение об ошибке.

              Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)

              Неверно : =СУММЕСЛИ(<1,2,3>, «цвет», C1:C3)

              2. Ошибка при суммировании значений из других листов или рабочих книг

              Как и любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и рабочие книги, если они в данный момент открыты.

              Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:

              Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.

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

              Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel 2010 и Excel 2016 сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.

              Ссылка на основную публикацию
              Adblock
              detector