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

Что возвращает функция

Значение, указанное, когда два условия истинны или ложны.

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

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

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

Комбинация функций SE работает таким образом, что при выполнении одного из указанных условий следующие больше не проверяются. Поэтому важно указывать их в правильной последовательности. Если бы мы начали проверку с B2 <1, тогда условия B2 <0,9 и B2 <0,95 Excel просто «не заметили бы», потому что они находятся в диапазоне B2 <1, который будет проверен в первую очередь (если значение меньше 0,9, конечно, тоже меньше 1). И поэтому у нас будет только два возможных варианта: меньше 1 и альтернатива, например. 1 или более.

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

В итоге нужно закрыть все скобки, иначе Excel выдаст ошибку

Синтаксис функции ЕСЛИ

Вот как выглядит синтаксис этой функции и ее аргументов:

= ЕСЛИ (логическое выражение, значение, если «да», значение, если «нет»)

Логическое выражение — это (обязательное) условие, которое возвращает истину или ложь (да или нет»);

Значение, если «да» — действие (обязательное), которое выполняется в случае положительного ответа;

Значение, если «нет» — действие (обязательное), которое выполняется в случае отрицательного ответа;

Давайте вместе рассмотрим эти темы подробнее.

Первый аргумент — логичный вопрос. И этот ответ может быть только «да» или «нет», «правда» или «ложь».

Как правильно задать вопрос? Для этого можно составить логическое выражение, используя знаки “=”, “>”, “<”, “>=”, “<=”, “<>”.

Расширение функционала с помощью операторов «И» и «ИЛИ»

Когда необходимо проверить несколько истинных условий, используется функция И. Суть заключается в следующем: ЕСЛИ a = 1 И a = 2, ТО значение в ELSE является значением c.

Функция OR проверяет условие 1 или условие 2. Как только хотя бы одно условие выполняется, результат будет истинным. Нижняя строка: ЕСЛИ a = 1 ИЛИ a = 2, ТО значение в ELSE значение c.

Функции И и ИЛИ позволяют проверить до 30 условий.

Пример использования оператора И:

Пример использования функции ИЛИ:

Примеры написания формул в Экселе

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

• с помощью кнопки «Вставить функцию» над рабочим полем;
• перейдя на вкладку «Формулы» в меню и найдя там «Математик» (логика, текст и т д).

• нажав «Вставить функцию» на вкладке «Формулы».

В Excel много разных операторов и функций — давайте подробнее рассмотрим самые популярные.

СУММ — суммирование чисел

Практически каждому нужно что-то просуммировать — поэтому оператор СУММ используется в Excel чаще других. Алгоритм действий при необходимости сложения чисел:

• Щелкните ячейку, в которой вы ожидаете получить результат. Перейдите на вкладку «Формулы» в меню, нажмите «Математика». Найдите функцию СУММ в раскрывающемся списке и щелкните по ней левой кнопкой мыши.

• В появившемся окне нужно ввести аргументы функции. Здесь можно пойти несколькими способами: выбрать нужные ячейки или диапазон или ввести их адреса вручную. При этом следует учитывать, что ячейки перечисляются через точку с запятой (например, A1; A3; A4), а диапазон в формулах обозначается двоеточием (например, B4: B10).

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

Синтаксис функции: = СУММ (число1; число2; число3;…) или = СУММ (число1: числоN).

СУММЕСЛИ — суммирование при соблюдении заданного условия

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

• На вкладке «Формулы» нажмите «Математика» и выберите СУММЕСЛИМН из списка.

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

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

• В результате оператор СУММЕСЛИ добавляет только ячейки зарплаты продавца.

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

Синтаксис функции: = СУММЕСЛИ (диапазон, критерий, диапазон_суммы).

СТЕПЕНЬ — возведение в степень

Часто необходимо до некоторой степени увеличить число, поэтому следует использовать функцию ГРАДУСЫ:

• Нажмите на «математические» формулы в соответствующей вкладке и найдите СТЕПЕНЬ.

• В открывшемся окне введите аргументы функции: число — это основание (то, что мы возводим в степень), степень — это показатель степени.

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

Синтаксис функции: = ГРАДУСЫ (число; градус).

СЛУЧМЕЖДУ — вывод случайного числа в интервале

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

• В «Математических формулах» выберите СЛУЧАЙНО.

• Аргументами функции являются нижняя и верхняя границы: мы вводим их, нажимая на ячейки.

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

Синтаксис функции: = RANDBETWEEN (нижний_ предел, верхний предел).

ВПР — поиск элемента в таблице

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

• На вкладке «Формулы» щелкните «Связи и массивы» и выберите ВПР из списка.

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

• Это позволяет быстро и легко искать в больших таблицах.

Синтаксис функции: ВПР (lookup_value, table, column_num, range_lookup).

СРЗНАЧ — возвращение среднего значения аргументов

Статистическая функция, позволяющая вычислить среднее арифметическое. Допустим, вы хотите узнать среднюю зарплату в компании:

• На вкладке «Формулы» щелкните «Дополнительные функции» и выберите «Статистика». В появившемся списке найдите СРЕДНЕЕ.

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

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

Синтаксис функции: = СРЕДНЕЕ (число1; число2;).

МАКС — определение наибольшего значения из набора

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

• Щелкните ячейку, в которой будет отображаться результат. На вкладке «Формулы» нажмите «Вставить функцию», в «Категории» выберите «Статистика» и в появившемся списке — МАКС.

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

• определено максимальное количество посетителей.

Синтаксис функции: = МАКС (число1; число2;…).

КОРРЕЛ — коэффициент корреляции

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

• На вкладке «Формулы» нажимаем «Вставить функцию», в «Категории» переходим в «Статистика» и находим КОРРЕЛЯЦИЮ в списке.

• Заполняем «Массивы функций»: в качестве «Массив 1» выбираем ячейки с количеством посетителей, в «Массив 2» вставляем данные о показе рекламы.

• В результате функция КОРРЕЛЬ возвращает значение коэффициента корреляции, который показывает наличие или отсутствие зависимости двух значений друг от друга. В нашем примере это 0,7261, что означает довольно тесную взаимосвязь между количеством посетителей сайта и показами рекламы.

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

Синтаксис функции: = КОРРЕЛ (массив1; массив2).

ДНИ — количество дней между двумя датами

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

• На вкладке «Формулы» нажмите кнопку «Дата и время» и выберите функцию ДНИ из списка.

• В поле «Конечная_дата» введите дату окончания выходного, щелкнув соответствующую ячейку, а в «Начальная_дата» — дату его начала.

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

Синтаксис функции: = ДНИ (конечная_дата; начальная_дата).

ЕСЛИ — выполнение условия

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

• На вкладке «Формулы» нажмите «Логические» и найдите ЕСЛИ.

• Заполним «Аргументы функции». В поле «Log_expression» нужно написать условие: в нашем случае выручка от продаж (B5) должна быть больше 40 000 руб. В «Value_if_true» пишем, что будет отображаться в ячейке при выполнении условия. Следовательно, если выручка превышает 40 000 рублей, «План реализован». В «Value_if_false» указываем «План не выполнен» — это предложение появится в ячейке, если доход продавца меньше 40 000 руб.

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

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

СЦЕПИТЬ — объединение текстовых строк

Формула относится к тексту и предназначена для объединения нескольких строк в одну. Часто используется в Excel для объединения двух или более ячеек. Например, фамилии, имена и отчества людей записываются в разных ячейках, и возникла необходимость создать сводный столбец. Мы действуем:

• Переходим на вкладку «Формулы» и нажимаем «Текст». Найдите ЦЕПЬ в списке.

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

• Результат, конечно, далек от идеала: между словами нет пробелов.

• Решить эту проблему не так уж и сложно — некоторые рекомендуют просто добавить пробел после текста в каждой ячейке, однако, если информации много, вы не хотите добавлять лишние символы. Лучше пойти другим путем: поправить формулу. Для этого в синтаксисе функции нужно добавить пробелы между кавычками — » «.

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

Синтаксис функции: = СЦЕПИТЬ (текст1; текст2;…).

Простейший пример применения.

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

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

В этом нам поможет функция SE. Добавим в таблицу данных столбец «Страна». Западный регион — это местные продажи («Местные»), а остальные регионы — зарубежные продажи («Экспорт»).

Как в формуле Excel обозначить постоянную ячейку

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

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

1. Заполним вручную первые столбцы обучающей таблицы.
2. Напомним из математики: чтобы найти стоимость нескольких единиц товара, нужно умножить цену на 1 единицу на количество. Чтобы рассчитать стоимость, введите в ячейку D2 формулу: = цена за единицу * количество. Константы формулы — это ссылки на ячейки с соответствующими значениями.
3. Нажмите ENTER — программа отобразит значение умножения. Такие же манипуляции необходимо проделать для всех клеток. Как установить формулу для столбца в Excel: скопируйте формулу из первой ячейки в другие строки. Относительные ссылки полезны.

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

Отпустите кнопку мыши — формула будет скопирована в выделенные ячейки с их ссылками. То есть у каждой ячейки будет своя формула со своими аргументами.

Ссылки в ячейке сопоставляются со строкой.

Формула абсолютной ссылки относится к той же ячейке. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).

Чтобы указать Excel на абсолютную ссылку, пользователь должен указать знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Находим общую стоимость всех товаров. Выберите числовые значения столбца «Стоимость» и еще одну ячейку. Это диапазон D2: D9
2. Мы используем функцию автозаполнения. Кнопка находится на вкладке «Главная» в наборе инструментов редактирования».
3. После щелчка по значку «Сумма» (или комбинации клавиш ALT + «=») выбранные числа выравниваются, и результат отображается в пустой ячейке.

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

1. Разделите стоимость одного продукта на стоимость всех продуктов и умножьте результат на 100. Ссылка на ячейку с общим значением должна быть абсолютной, чтобы она оставалась неизменной при копировании.
2. Чтобы получить проценты в Excel, не нужно умножать частное на 100. Выделите ячейку с результатом и нажмите «Форматировать процент». Или нажмите комбинацию горячих клавиш: CTRL + SHIFT + 5
3. Копировать формулу во весь столбец — изменяет только первое значение в формуле (относительная ссылка). Вторая (абсолютная связь) осталась прежней. Проверяем правильность расчетов — найдем результат. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

• $ In $ 2 — при копировании столбец и строка остаются неизменными;
• B $ 2 — при копировании строка не меняется;
• $ B2 — столбец без изменений.

Закрепление ячеек для большого диапазона

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

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

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

1. Ячейки.
2. Макро.
3. Функции разного типа.
4. Ссылки и массивы.

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

1. Выберите интервал.
2. Откройте вкладку VBA-Excel, которая появится после установки.
3. Откройте меню «Функции», где находится опция «Фиксированные формулы».
4. После этого появится диалоговое окно, в котором нужно указать требуемый параметр. Этот аддон позволяет добавлять столбец и столбец по отдельности вместе, а также удалять существующий вывод с помощью пакета. После выбора необходимого параметра с помощью соответствующей радиокнопки, необходимо подтвердить действия, нажав «ОК».

Применение «ЕСЛИ» с несколькими условиями

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

Давайте посмотрим на пример той же таблицы. Но на этот раз усложним задачу. Теперь нужно делать скидку на женскую обувь в зависимости от вида спорта.

Первое условие — проверка пола. Если это «мужской», сразу отображается значение 0. Если «женский», проверка начинается в соответствии со вторым условием. Если спорт беговой — 20%, если теннис — 10%.

Пишем формулу этих условий в нужную нам ячейку.

= SE (B2 = «мужской»; 0; SE (C2 = «ход»; 20%; 10%))

Нажмите Enter и получите результат согласно заданным условиям.

Затем мы распространяем формулу на все оставшиеся строки таблицы.

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

Операторы сравнения чисел и строк представлены операторами, состоящими из одного или двух равных и математических неравенств:

• <- минус;
• <= — меньше или равно;
• > — другое;
• > = — больше или равно;
• = — равно;
• <> — не то же самое.

Синтаксис:

1	Результат = Выражение1 Оператор Выражение2

• Результат — любая числовая переменная;
• Выражение — это выражение, возвращающее число или строку;
• Оператор — любой оператор сравнения чисел и строк.

Если переменная Result объявлена ​​как Boolean (или Variant), она вернет False и True.Числовые переменные других типов вернут значения 0 (False) и -1 (True).

Операторы сравнения чисел и строк работают с двумя числами или двумя строками. При сравнении числа со строкой или строки с числом VBA Excel выдаст ошибку несоответствия типа):

12345678910

SubPrimer1 () On ErrorGoToInstrDimmyRes AsBoolean ‘Сравните строку с числом myRes = «five»> 3Instr: IfErr.Description <> «» ThenMsgBox «Произошла ошибка:» & Err.DescriptionEndIfEndSub

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

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

Частичная фиксация ячейки по строке или по столбцу.

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

Вы можете использовать два типа смешанных ссылок:

• При копировании строка подтверждается, а столбец изменяется.
• Столбец заблокирован, а строка изменяется при копировании.

Смешанная ссылка содержит относительную и абсолютную координаты, например $ A1 или A $ 1. Проще говоря, знак доллара используется только один раз.

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

В результате имеем следующее:

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

Фиксированная ячейка	Что происходит, когда вы копируете или перемещаете	Клавиши на клавиатуре
1 австралийский доллар	Столбец и строка не меняются.	Нажмите F4.
1 австралийский доллар	Строка не меняется.	Дважды нажмите F4.
$ A1	Столбец не меняется.	Трижды нажмите F4.

Рассмотрим пример, когда необходимо зафиксировать только одну координату: столбец или строку. И все это в одной формуле.

Предположим, мы хотим рассчитать продажные цены с разными уровнями наценки. Для этого вам нужно умножить столбец цен (столбец B) на 3 возможных значения наценки (записанные в C2, D2 и E2). Вводим выражение для расчета в C3, потом копируем сначала вправо по строке, потом вниз:

= $ B3 * (1 + C $ 2)

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

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

Но во втором множителе мы ставим знак доллара перед номером строки. Следовательно, при копировании вправо координаты столбца изменятся и вместо C $ 2 мы получим D $ 2. В результате в D3 получим выражение:

= $ B3 * (1 + D $ 2)

А когда мы копируем столбец, все будет наоборот: $ B3 изменится на $ B4, $ B5 и так далее, но D $ 2 не изменится, так как строка «заморожена». Следовательно, в C4 получаем:

= $ B4 * (1 + C $ 2)

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

А если ваша наценка внезапно изменится, просто измените числа на C2: E2, и проблема конвертации будет решена практически мгновенно.

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

Одновременное выполнение двух условий

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

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

Для этого воспользуемся следующей формулой:

= ЕСЛИ (И (B2 = «женский»; C2 = «ход»); 30%; 0)

Нажмите клавишу Enter, чтобы просмотреть результат в ячейке.

Как и в приведенных выше примерах, растяните формулу до остальных строк.

Общее определение и задачи

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

Синтаксис этой функции следующий: «ЕСЛИ (логическое выражение; функция, если истина

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

Установите курсор в ячейку G2 и введите знак «=». Для Excel это означает, что будет введена формула. Поэтому, как только буква «е» будет нажата дальше, мы получим предложение выбрать функцию, которая начинается с этой буквы. Выбираем «СЭ”.

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

Пишем в качестве первого аргумента: C2 = «Запад». Как и в других функциях Excel, здесь нет необходимости вручную вводить адрес ячейки, достаточно просто щелкнуть по ней мышью. Поэтому мы ставим «» и указываем второй аргумент.

Второй аргумент — это значение, которое примет ячейка G2, если условие, которое мы отметили, выполнено. Это будет слово «Местный”.

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

Наша функция выглядит так:

= SE (C2 = Запад, Местный, Экспорт”)

Наша ячейка G2 настроена на Local».

Теперь нашу функцию можно скопировать во все остальные ячейки в столбце G.

Дополнительная информация

• В функции ЕСЛИ можно проверить 64 условия одновременно;
• Если один из аргументов функции является массивом, оценивается каждый элемент массива;
• Если вы не укажете условие аргумента FALSE if_value (value_if_false) в функции, например, после аргумента value_if_true (value_if_true) есть только запятая (точка с запятой), функция вернет значение «0», если результат оценки функции ЛОЖЬ).
  В следующем примере формула = ЕСЛИ (A1> 20, «Разрешить») или = ЕСЛИ (A1> 20; «Разрешить»), где значение_если_fe_ (значение_если_false) не указано, но аргумент_значение_если_ истинное значение (значение_если_ истинное значение) по-прежнему является запятой. -отдельно. Функция вернет «0» всякий раз, когда проверяемое условие не соответствует условиям ИСТИНА).
  
  |
• Если вы не укажете условие аргумента ИСТИНА (значение_если_ истинное (значение_если_ истинное)) в функции, например, условие указано только для аргумента значение_если_ ложь (значение_если_ ложь), то формула вернет значение «0», если результат расчет функции ИСТИНА);
  В приведенном ниже примере формула = ЕСЛИ (A1> 20; «Отклонить») или = ЕСЛИ (A1> 20; «Отклонить»), где значение_если_ истинное значение (значение_если_ истинное значение) не указано, формула вернет «0» всякий раз, когда состояние выполнено ИСТИНА).

4. Формула, записанная вручную

Удалите формулу в ячейке B3. Перепишем формулу.

Шаг 1. Введите на клавиатуре знак «=» и введите «pro»:

Появится раскрывающееся меню со списком функций, начинающимся с «pro».

Шаг 2. Дважды щелкните LM на имени функции «ПРОДУКТ» и затем повторите шаги для ввода адресов ячеек, не забывая разделять адреса точкой с запятой ;»:

Если вам нравится английская раскладка клавиатуры, вы можете ввести формулу вручную с клавиатуры. Если вы наберете «А» или «Б» в русской раскладке, Excel вас просто не поймет, формула не будет работать и вы увидите:

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

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

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

Вложенные условия с математическими выражениями.

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

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

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

= L8 * SE (La8> = 101; 12; SE (La8> = 50; 14; SE (La8> = 20; 16; SE (La8> = 11; 18; SE (La8> = 1; 22; “”)))))

И вот результат:

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

Например, вместо того, чтобы «кодировать» цены в самой формуле, вы можете ссылаться на ячейки, в которых они отображаются (ячейки с B2 по B6). Это позволит вам изменить исходные данные без обновления самой формулы:

= L8 * SE (L8> = 101; L6; SE (L8> = 50; L5; SE (L8> = 20; L4; SE (L8> = 11; L3; SE (L8> = 1; L2; “”)))))

Аргументы функции

• логический_тест (logical_test) — это условие, которое вы хотите проверить. Этот аргумент функции должен быть логическим и иметь значение FALSE или TRUE. Аргумент может быть статическим значением или результатом функции или вычисления;
• value_if_true (value_if_true) — (необязательно) — значение, возвращаемое функцией. Он будет отображаться, если проверяемое значение соответствует условию ИСТИНА;
• if_value (if_false_value) — (необязательно) — значение, возвращаемое функцией. Он будет отображаться, если проверяемое условие соответствует условию FALSE.

А если один из параметров не заполнен?

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

= ЕСЛИ (MI2> 100; F2 * 0,1)

Что будет в результате?

Насколько красиво и удобно — судить вам. Я думаю, что в любом случае лучше использовать оба аргумента.

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

= ЕСЛИ (MI2> 100; FA2 * 0,1,””)

Однако эту конструкцию можно использовать в случае, если значение «Истина» или «Ложь» используется другими функциями Excel в качестве логических значений.

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

Кроме того, если вам действительно нужно проверить некоторые условия и получить «Истина» или «Ложь» («Да» или «Нет»), вы можете использовать следующую конструкцию –

= ЕСЛИ (MI2> 100; ИСТИНА; ЛОЖЬ)

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

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

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

= БАЛАНС (значение)

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

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

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

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

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

= ЕСЛИ (A1 = ИСТИНА (), «Пройден», «Стоп”)

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

Составное условие

Составное условие состоит из простых условий, связанных логическими операциями И () и ИЛИ().

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

Простое условие

Для чего нужна функция SE ()? Взгляните на схему. Вот простой пример того, как работает функция при определении знака числа a.

Блок-схема «Простое состояние». Определение отрицательных и неотрицательных чисел

Условие a> = 0 определяет два возможных варианта: неотрицательное число (ноль или положительное) и отрицательное число. Ниже представлена ​​схема написания формулы в Excel. После условия перечислены параметры, разделенные точкой с запятой. Если условие истинно, в ячейке будет отображаться «неотрицательный» текст, в противном случае — «отрицательный». То есть запись, соответствующая ветви схемы, — «Да», за которой следует «Нет».

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

 Увеличиваем неотрицательное число на 10, а отрицательное оставляем без изменений.

Блок-схема «Простое состояние». Расчет данных

На диаграмме показано, что при выполнении условия число увеличивается на десять и вычисленное выражение A1 + 10 записывается в формулу Excel (выделено зеленым цветом). В противном случае число не меняется и здесь вычисляемое выражение состоит только в обозначении самого числа А1 (выделено красным).

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

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

Решение:

Решение этой проблемы можно увидеть на следующем рисунке. Но давайте все же попробуем прояснить эту иллюстрацию. Основные исходные данные для решения этой проблемы находятся в столбцах A и B. В ячейке A5 указано пороговое значение дохода, при котором изменяется ставка налога. Соответствующие ставки показаны в ячейках B5 и B6. Доход от бизнеса отображается в диапазоне ячеек B9: B14. Формула расчета налога записана в ячейке C9: = IF (B9> A $ 5; B9 * B $ 6; B9 * B $ 5). Эту формулу необходимо скопировать в ячейки ниже (выделены желтым).

В формуле расчета адреса ячеек записываются как A $ 5, B $ 6, B $ 5. Знак доллара фиксирует часть адреса, перед которой он устанавливается при копировании формулы. Запрещается менять номер строки в адресе ячейки.

Пример функции с несколькими условиями

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

1. Например, возьмем тот же стол с выплатами премий до 8 марта. Но на этот раз по условиям размер бонуса зависит от категории сотрудника. Женщины со статусом ключевого персонала получают премию в размере 1000 рублей, вспомогательный персонал — только 500 рублей. Конечно, мужчины абсолютно не имеют права на этот вид оплаты, независимо от категории.
2. Первое условие — если наемный работник — мужчина, размер полученного бонуса равен нулю. Если это значение ложно и сотрудник не мужчина (т.е женщина), проверяется второе условие. Если женщина принадлежит к основному персоналу, в ячейке будет отображаться значение «1000», а в противном случае — «500». В виде формулы это будет выглядеть так: «= SE (B6 =« мужской »;« 0 »; SE (C6 =« Главный штаб »;« 1000 »;« 500″))».
3. Вставьте это выражение в верхнюю ячейку столбца «Бонус к 8 марта».



4. Как и в прошлый раз, «растягиваем» формулу вниз.

Пример использования «ЕСЛИ»

Теперь рассмотрим конкретные примеры использования формулы с оператором ЕСЛИ».

1. У нас есть таблица выплат. К 8 марта все женщины имеют право на бонус в размере 1000 рублей. В таблице есть столбец с указанием пола сотрудников. Следовательно, мы должны вычислить женщин из предоставленного списка и в соответствующие строки столбца «Бонус до 8 марта» ввести «1000». При этом, если пол не совпадает с женским, значение таких строк должно соответствовать «0». Функция будет выглядеть так: «ЕСЛИ (B6 =« женский »;« 1000 »;« 0 »)». То есть при результате проверки «истина» (если выясняется, что строка данных занята женщиной с параметром «женский») будет выполнено первое условие — «1000», а если «ложь»), то, соответственно, последний равен «0».
2. Мы вводим это выражение в верхнюю ячейку, где должен отображаться результат. Поставьте знак «=» перед выражением «=».



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



4. Итак, мы получили таблицу с одним столбцом, заполненным функцией «ЕСЛИ».

Источники

• https://exceltut.ru/formula-esli-v-excel-primery-s-neskolkimi-usloviyami/
• [https://konekto.ru/kak-napisat-formulu-v-excel.html]
• [https://exceltable.com/formuly/rabota-v-eksele-s-formulami-i-tablicami]
• [https://office-guru.ru/excel/formuly-funkcii/kak-zakrepit-yachejku-v-formule-excel.html]
• [https://mister-office.ru/excel/fix-cell-references.html]
• [https://prooffice24.ru/excel-9-formulas/]
• [https://planshet-info.ru/kompjutery/jeksel-kak-sdelat-postojannoj-jachejku-v-formule]