Tw-city.info

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

Excel формула если то иначе

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

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

Коротко о синтаксисе

Функция ЕСЛИ имеет всего три аргумента:

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

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

Пример 1

Обратимся к примеру, приведенному на рисунках ниже. В данном примере функция ЕСЛИ в первую очередь проверят условие A1>25. Если это так, то формула возвратит текстовую строку “больше 25”, в любом другом случае – “меньше или равно 25”.

Пример 2

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

В столбец C нам необходимо выставить результат экзамена, который должен содержать всего два варианта: Сдал или Не сдал. Те, кто набрал более 45 баллов – сдали экзамен, остальные нет.

  1. Выделите ячейку, в которую необходимо ввести формулу. В нашем случае это ячейка C3.
  2. Введите в нее выражение: =ЕСЛИ(B3>45; “Сдал”; “Не сдал”) и нажмите Enter.
  3. Данная формула сравнивает значение в ячейке B3 с числом 45, если значение больше 45, то возвращает строку “Сдал”, иначе “Не сдал”.
  4. Скопировав формулу в остальные ячейки таблицы, можно увидеть, что 2 человека из 5 не прошли переаттестацию.

Функция ЕСЛИ и несколько условий

Функции ЕСЛИ можно вкладывать друг в друга, если необходимо расширить варианты принятия решений в Excel. Например, для рассмотренного ранее случая переаттестации сотрудников, требуется проставить не результат, а оценку из ряда: Отлично, Хорошо и Плохо. Оценка Отлично ставится при количестве баллов более 60, оценка Хорошо при более 45 и оценка Плохо в остальных случаях.

    Чтобы решить эту задачу, введем в ячейку C3 следующую формулу:=ЕСЛИ(B3>60;”Отлично”;ЕСЛИ(B2>45;”Хорошо”;”Плохо”)) и нажмем Enter.

  • Данная формула обрабатывает сразу два условия. Сначала проверяется первое условие: B3>60. Если оно истинно, то формула возвращает значение “Отлично”, а остальные условия не обрабатываются. Если первое условие ложно, то функция ЕСЛИ переходит ко второму: B2>45. Если второе условие истинно, то формула возвращает значение “Хорошо”, а если ложно, то “Плохо”.
  • Скопировав формулу в остальные ячейки таблицы, можно увидеть, что на отлично сдал один человек, а на оценки хорошо и плохо по два человека.
  • Как видите, вместо второго и третьего аргументов функции ЕСЛИ можно подставлять новые функции ЕСЛИ, тем самым расширяя число условий, которое формула может обработать. Таким образом, Вы можете создать нужное количество вложений. Правда есть очевидный недостаток такой конструкции, после 3-5 вложений формула станет нечитаемой и громоздкой, и с ней будет невозможно работать.

    В Excel существуют более благородные инструменты для обработки большого количества условий, например, функция ВПР или ПРОСМОТР.

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

    Функция ЕСЛИ

    В этом курсе:

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

    Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.

    Например, функция =ЕСЛИ(C2=»Да»;1;2) означает следующее: ЕСЛИ(С2=»Да», то вернуть 1, в противном случае вернуть 2).

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

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

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

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

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

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

      В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)

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

        Читать еще:  Обозначение модуля в excel

        B2;»Превышение бюджета»,»В пределах бюджета»)» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

          =ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)

          В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст «Превышение бюджета», в противном случае вернуть текст «В пределах бюджета»)

          B2;C2-B2;»»)» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

            На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение «Фактические» больше значения «Плановые», то вычесть сумму «Плановые» из суммы «Фактические», в противном случае ничего не возвращать).

              В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = «Да», то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)

              Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: «Текст»). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.

              Функция ЕСЛИ (IF) в Excel — правила использования, примеры

              Функция ЕСЛИ() применяется для выборки значения (-ий) по заданному условию (-ий). Аргументы функции:

              Аргумент Лог_выражение применяется для записи непосредственно условия поиска. Возвращает логические значения «истина» или «ложь». Для формирования условия, значения можно брать как из ячеек, так и записывать непосредственно в формуле.

              Два остальных аргумента — для вывода значения, если первый аргумент вывел «истину» и второй если вывел «ложь».

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

              =ЕСЛИ(B2>C2;B2;C2) – читается так, если значение B2 больше C2 выводим B2, иначе C2:

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

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

              В моем примере, если написать в B4 любое другое слово программа скажет:

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

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

              Возьмем предыдущий пример, и напишем дополнительное условие для слова груши:

              Читается так: если B4 равно «яблоко», то выводим слово «фрукт», иначе (выполняем следующую функцию ЕСЛИ) если B4 равно «груша», то выводим слово «фрукт», иначе выводим «не фрукт».

              Таких вложений может быть до 64!

              Функция ЕСЛИ() выполняет условия строго по порядку и завершает свою работу, если условие было выполнено.

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

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

              Применяем такую формулу:

              и протягиваем ее:

              Получаем 10% для всех значений. Формула начинается работу с первого аргумента – он возвращает истину, и формула завершает свою работу.

              Здесь спасти положение может только добавление в функцию ЕСЛИ() функцию И(), для проверки в каком диапазоне находится себестоимость, и какую наценку надо выводить:

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

              Заменим в предыдущем примере столбец наценка на столбец цена и будем выводить сразу итоговую цену. Формула примет следующий вид:

              Главное не запутаться в скобках 😊. Протягиваем формулу:

              На этом все, если у вас есть вопросы оставляйте в комментариях.

              Оператор “ЕСЛИ” в программе Microsoft Excel: применение и примеры

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

              В этой статье мы поговорим о том, что же такое оператор «ЕСЛИ», а также рассмотрим область применения и принципы работы с ним.

              Содержание: Функция “ЕСЛИ” в Excel

              Определение функции “ЕСЛИ” и ее назначение

              Оператор «ЕСЛИ» – это инструмент программы Эксель для проверки определенного условия (логического выражения) на выполнение.

              То есть, представим, что у нас есть какое-то условие. Задача “ЕСЛИ” – проверить соблюдение данного условия и вывести в ячейку с функцией значение, основанное на результате проверки.

              1. Если логического выражение (условие) выполняется, то значение соответствует истине.
              2. Если логического выражение (условие) не выполняется, значение является ложным.
              Читать еще:  Доверительный интервал в excel

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

              =ЕСЛИ(условие; [значение если условие выполнено]; [значение если условие не выполнено])

              Использование функции “ЕСЛИ” на примере

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

              У нас есть таблица с наименованиями спортивной обуви. Представим, что скоро у нас будет распродажа, и всей женской обуви нужно проставить скидку 25%. В одном из столбцов в таблице как раз прописан пол для каждого наименования.

              Наша задача — в столбце “Скидка” вывести значение «25%» для всех строк с наименованиями для женского пола. И соответственно, значение «0», если в столбце «Пол» стоит значение «мужской»

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

              Для выполнения этой задачи потребуется составить следующую формулу ниже:

              =ЕСЛИ(B2=”женский”;25%;0)

              • Логическое выражение: B2=”женский”
              • Значение в случае, условие выполняется (истина) – 25%
              • Значение, если условие не выполняется (ложь) – 0.

              Данную формулу пишем в самой верхней ячейке столбца “Скидка” и нажимаем Enter. Не забываем ставить знак равно (=) перед формулой.

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

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

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

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

              Мы только что рассмотрели пример использования оператора «ЕСЛИ» с одним логическим выражением. Но в программе также имеется возможность задавать больше одного условия. При этом сначала будет проводиться проверка по первому, и в случае его успешного выполнения сразу отобразится заданное значение. И только если не будет выполнено первое логическое выражение, в силу вступит проверка по второму.

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

              Первое условия – это проверка пола. Если “мужской” – сразу выводится значение 0. Если же это “женский”, то начинается проверка по второму условию. Если вид спорта бег – 20%, если теннис – 10%.

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

              =ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))

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

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

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

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

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

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

              =ЕСЛИ(И(B2=”женский”;С2=”бег”);30%;0)

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

              Аналогично примерам выше, растягиваем формулу на остальные строки.

              Оператор «ИЛИ»

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

              Поставим задачу следующим образом. Скидка 35% предоставляется только на мужскую обувь для тенниса. Если это мужская обувь для бега или любая женская обувь, скидка равна 0.

              В данном случае нужна следующая формула:

              =ЕСЛИ(ИЛИ(B2=”женский”; C2=”бег”);0;35%)

              После нажатия Enter мы получим требуемое значение.

              Растягиваем формулу вниз и скидки для всего ассортимента готовы.

              Как задать функции ЕСЛИ через Построитель формул

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

              Давайте разберем, как это работает. Допустим, нам снова как и в первом примере нужно проставить скидку на всю женскую обувь в размере 25%.

              1. Ставим курсор на нужную ячейку, заходим во вкладку “Формулы”, далее щелкаем “Вставить функцию”.
              2. В открывшемся списке Построителя формул выбираем “ЕСЛИ” и нажимаем “Вставить функцию”.
              3. Открывается окно настройки функции. В поле “логическое выражение” пишем условие, по которому будет проводиться проверка. В нашем случае это “B2=”женский”.
                В поле “Истина” пишем значение, которое должно отобразиться в ячейке, если условие выполняется.
                В поле “Ложь” – значение при невыполнении условия.
              4. После того, как все поля заполнены, жмем “Готово”, чтобы получить результат.
              Читать еще:  Где найти автосохраненные документы excel

              Заключение

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

              СТАТЬИ ПО ТЕМЕБОЛЬШЕ ОТ АВТОРА

              Как сделать зачеркнутый текст в Эксель

              Как перенести таблицу из Word в Excel

              Как сделать разделение ячеек в Excel

              Импорт содержимого CSV-файла в Excel

              Защита ячеек от изменений в Эксель

              Статистические функции в Microsoft Excel

              Математические функции в Microsoft Excel

              Извлечение корня в Excel с помощью формулы и функции

              Регрессионный анализ данных в Excel

              Метод наименьших квадратов в Excel

              Как решить транспортную задачу в Excel

              Что делать, если исчезла строка формул в Эксель

              3 КОММЕНТАРИИ

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

              Я ничего не знал о функции IF в Excel, очень интересен ее охват и принципы работы с ней. Мне понравилась статья, как хорошо они затрагивают эти вопросы, о которых почти никто не говорит.

              По моему это очень давно и в БД, и в EXCEL существует. На кириллице операторы только непривычны.

              Функция ЕСЛИ() в EXCEL

              Функция ЕСЛИ() относится к наиболее часто используемым функциям.

              Синтаксис функции

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

              Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. =ЕСЛИ(A1>=100;»Бюджет превышен»;»ОК!») Т.е. если в ячейке A1 содержится значение большее или равное 100, то формула вернет ОК!, а если нет, то Бюджет превышен .

              В качестве аргументов функции, могут участвовать формулы, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше, то по столбцу С .

              Вложенные ЕСЛИ

              В EXCEL 2007 в качестве значений аргументов значение_если_истина и значение_если_ложь можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ() . =ЕСЛИ(A1>=100;»Бюджет превышен»;ЕСЛИ(A1>=90;»Крупный проект»;ЕСЛИ(A1>=50;»Средний проект»;»Малый проект «)))

              Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы:

              Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:

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

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

              =ПРОСМОТР(A24;<-1E+307;0;1E-307>;A27:A29) (см. файл примера )

              Опущен третий аргумент [значение_если_ложь]

              Третий аргумент функции не обязателен, если его опустить, то функция вернет значение ЛОЖЬ (если условие не выполнено). =ЕСЛИ(A1>100;»Бюджет превышен») Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.

              Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

              Т.к. значение ЛОЖЬ эквивалентно 0, то формулы =ЕСЛИ(0;»Бюджет превышен»;»ОК!») или (если в ячейке A1 содержится значение 0) =ЕСЛИ(A1;»Бюджет превышен»;»ОК!») вернут ОК!

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

              Связь функции ЕСЛИ() с другими функциями использующие условия

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

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

              Пусть данные находятся в диапазоне A6:A11 (см. файл примера)

              Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ() , записав =СУММЕСЛИ(A6:A11;»>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))

              Ссылка на основную публикацию
              ВсеИнструменты 220 Вольт
              Adblock
              detector
              ×
              ×