Excel works

Распространенный вопрос по Excel «Как записывать несколько условий в одной формуле?». Особенно часто применяется два и более условий при использовании функции ЕСЛИ. Сделать несколько условий в формуле ЕСЛИ довольно просто, главное знать основные принципы. Их и обсудим ниже.

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

Например, есть вот такая довольно нагроможденная формула:

Разберем на примере, как перенести ее в Excel

Как записать несколько условий в формуле ЕСЛИ?

Понятно, что эта формула будет состоять из 3 частей, как минимум:

Об экспоненте можно подробно прочитать здесь .

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

Ее состав следующий:

=ЕСЛИ(Условие;если условие = ДА (ИСТИНА);если условие = НЕТ (ЛОЖЬ))

Т.е. если мы запишем простую формулу, что мы получим в итоге в ячейке B2?

Верно — отобразится 100. Если же в А1 будет стоять любое другое значение кроме 1, то в B2 отобразится бы 0.

Вернемся к нашей системе условий. Теперь нам надо понимать, как записать сразу два условия до первой точки с запятой. У нас в B1 пусто, а значит = 0, и только при выполнении обоих условий А1=1 и B1=0 (знак *) значение формулы будет равно 100.

Особо разберем * между скобками

Оператор И он же * означает, что должно выполняться оба условия одновременно, А1=1 и B1=0.

Если между скобками поставить + (или), то достаточно будет одного из условий. Например, только если А1=1, то уже будет отображаться 100.

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

Запишем первое условие

Если условие выполняется, то выполняется первая формула с синусом
Если нет, второе условие

Во всех же остальных случаях будет выполнятся формула =EXP(1/B1)
Итого получается:

Запись нескольких формул в одной

Если в ячейки B1 будет текст, то формула выдаст ошибку. Поэтому я часто применяю формулу =ЕСЛИОШИБКА() .

Представим, что вся наша формула из предыдущего пункта — это один условный аргумент А

Или для нашего примера

Пример можно скачать здесь

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

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

И хотя в примере я использовал только 4 варианта и 3 вложения, но в сути я показал как она работает. Как видно функция ЕСЛИ в Excel использует логическое построение, сначала идёт значение, которое, должно быть главным, вторым идёт ответ если в ячейке выполняется главное условие и в этом случае предоставляется текстовое значение. Дальше идёт вариант когда условия не выполнены, я для этого использовал новую формулу условий. А уже в конце подвёл итог, если никакие условия не выполнены, то использовать для данных ячеек текстовое обозначение. Также для получения логического значения, функцию ЕСЛИ используют также, как встроенную, в функциях , математических, текстовых и прочих.

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

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

Тот, кто живет по средствам, страдает от недостатка воображения
Андре Стендер, писатель

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

Вам также могут быть интересны следующие статьи

Нужно вернуть определенное значение из ячейки и посчитать балл, т.е. например в ячейке D3 может быть значение А, Б, В, Г, надо в ячейку D4 вернуть значение в зависимости от буквы, например А=1, Б=2, В=3 и так далее. Как сделать? Можно ли через формулу ЕСЛИ?

Статья хорошая, спасибо.
Но.. вначале статьи планы ставят из минимального расчета 500$, а все дальнейшие расчеты исходят из 400$.
Как бы надо стараться следовать тем планам, что ставите.

Данный метод хорош, если у нас немного критериев (2-3), но когда их 10, то в такой формуле потом трудно разобратся «что и откуда». В таком случае можно (и нужно) обойтись без ЕСЛИ.
Для этого создаем маленькую табличку с нашими критериями: в первой строке по возрастанию заполняем критерии (в приведенном примере это будут 0, 500, 750, 1000); во второй строчке под каждым критерием заполняем соответствующий процент (7, 10, 12,5, 16). Допустим, в диапазоне A1:D1 у нас заполнены критерии, а в диапазоне A2:D2 — соответствующие проценты. В ячейке А5 имеем цифру продаж; для рассчета комиссии используем следующую формулу: =A5*ИНДЕКС($A$2:$D$2;ПОИСКПОЗ(A5;$A$1:$D$1;1)).
ПОИСКПОЗ ищет расположение критерия, который меньше продаж, но наибольший в списке, а ИНДЕКС по полученному номеру выдает нам необходимый процент.

Лабораторная работа №4. проверка условий в ms excel

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

При вычислении значений и формул часто приходится проверять выполнение одного или нескольких условий. Для этого в MS Excel используется функция ЕСЛИ, которая возвращает различные значения в зависимости от выполнения или невыполнения условия.

=ЕСЛИ(условие; значение 1; значение2)

В ячейке, в которой записана функция ЕСЛИ, сначала проверяется выполнение условия. Если условие истинно, то в ячейку помещается значение 1, если условие ложно, то значение 2. В качестве значения 1 или значения 2 может выступать число, формула или другая функция Excel, в том числе и сама функция ЕСЛИ.

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

В таблице 2 приведены операторы сравнения, которые могут использоваться при составлении условий:

При проверке условий также часто используются логические функции

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

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

1. Откройте новую Рабочую книгу. Присвойте Листу1 имя Проба.

2. Заполните таблицу по образцу (рис. 9)

3. В ячейку В4 введите формулу =ЕСЛИ (В1В2; В1; В2). Нажмите клавишу Enter. В ячейке В4 появится значение большего из двух чисел.

4. Попробуйте поменять значения исходных чисел и проанализируйте результат.

ЗАДАНИЕ 1. Использование функции ЕСЛИ

1. Откройте чистый рабочий лист, назовите его ЕСЛИ.

2. Создайте на листе таблицу, показанную на рис. 10 (последнюю графу не заполняйте — она будет использоваться для сверки результатов):

3. В ячейке С4 выведите сообщение «Есть нечетное», если в ячейках А4 и В4 содержится хотя бы одно нечетное число, в противном случае выведите сообщение «Нечетных нет». Внимание! При работе используйте функцию ЕНЕЧЕТ.

4. В ячейке С5 выведите слово «Да», если значение ячейки А5 больше значения ячейки В5. В противном случае выведите слово «Нет».

5. Создайте формулу, записывающую в ячейку С6 слово «Текст», если ячейка А6 содержит текст, и слово «Не текст» в противном случае. Внимание! При работе используйте функцию ЕТЕКСТ.

6. Сформируйте формулу, определяющую значение ячейки С7, как

7. Сверьте полученные результаты.

ЗАДАНИЕ 2. Расчет значения сложной функции

Рассчитайте значения функции

при Х = -2; -1,5; 0; 3,7.

1. Откройте чистый рабочий лист, переименуйте его в Расчет.

2. В ячейку А1 введите текст «Х», в ячейку В1 текст «У».

3. В ячейки А2-А5 введите заданные значения Х.

4. В ячейку В2 введите формулу
= ЕСЛИ(А2

5. Выполните копирование формулы из ячейки В2 в ячейки В3-В5.

ТЕСТОВОЕ ЗАДАНИЕ 1

1. Откройте чистый рабочий лист. Переименуйте его в Тест 1.

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

№ варианта	y=f(x)	Исходные данные

ТЕСТОВОЕ ЗАДАНИЕ 2

Постройте график функции y=f(x), предварительно протабулировав её на отрезке [-4; 8] с шагом h = 0,2. Функцию выберите из табл. 3 в соответствии со своим вариантом.

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

Статьи к прочтению:

Проверка условий и логические функции в Excel

Похожие статьи:

Цель работы: познакомиться с вычислительными возможностями табличного процессора MS Excel, освоить работу с Мастером функций, научиться выполнять сложные…

Цель работы: познакомиться с возможностями организации циклических вычислений на примере расчетов сумм и произведений. Обычно при расчетах сумм или…

Ввод команды в Excel

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

1. Сверху над основной таблицей находим иконку функции и жмем ее.

1. В открывшемся окне нужно выбрать категорию «Ссылки и массивы», затем найти нужный вариант в списке и нажать «ОК».

1. Теперь появятся поля для ввода данных. Заполнять их можно вручную или указывать с помощью выделения ячеек таблицы.

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

Пример использования поиска решений

Теперь перейдем к самой функции.

1) Чтобы включить «Поиск решений», выполните следующие шаги:

• нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
• в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
• в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.

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

Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата труда за 1 изделие» умножить на «Число заготовок, передаваемых в работу». Для того, чтобы узнать «Время на выполнение заказа», нужно «Число заготовок, передаваемых в работу» разделить на «Производительность».

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

4) Заполните параметры «Поиска решений» и нажмите «Найти решение».

Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.

В нашем примере следующие ограничения:

• общее количество изделий 1000 штук ($D$13 = $D$3);
• число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0);
• количество дней меньше либо равно 30 ($F$9:$F$12 < = $D$6, либо как в примере в ячейке F13 задать функцию МАКС(F9:F12) и поставить ограничение $F$13 < = $D$6).

5) В конце проверьте полученные данные на соответствие заданному целевому значению. Если что-то не сходится — нужно пересмотреть исходные данные, введенные формулы и ограничения.

Хотите научиться решать задачи в Excel, как это делают в компаниях-лидерах? Приходите на наш онлайн-курс, на котором вы освоите этот инструмент на уровне профи. Вашими преподавателями будут эксперты-практики, а после обучения вы сможете дополнить резюме весомой строчкой. Регистрируйтесь!

В аргументе Лог_выражение можно использовать как простые сравнения типа F1>10, так и более сложные конструкции на основе функций И(), ИЛИ(), НЕ() и их комбинаций.

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

Приведем несколько полезных примеров.

Формула =ЕСЛИ(ИЛИ(A1;A2);0;2) проверяет одновременность равенства значений А1 и А2 нулю. Если оба значения равны 0, то формула вернет 2. Во всех других случаях будет возвращен 0. А1 и А2 не должны быть пустыми ячейками.

Формула =ЕСЛИ(ИЛИ(A1=0;A2=0);0;2) проверяет равенство 0 хотя бы одного значения из А1 и А2.

Формула =ЕСЛИ(НЕ(A1>0);1;2) эквивалентна формуле =ЕСЛИ(A1 10″) . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )

Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;»>10″) . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))

Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))