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

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

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

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

В общем виде функция ЕСЛИ в excel имеет следующий синтаксис:

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

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

Рассмотрим синтаксис данной функции на примере алгоритма вычисления суммы с НДС или без НДС.

Лог_выражение – это может быть любое значение или выражение (формула) которое принимает значение ИСТИНА или ЛОЖЬ. Так, например, С5=“с НДС” – это логическое выражение. Если в ячейке С5 находиться текст «с НДС», то логическое выражение принимает значение ИСТИНА, в противном случае – ЛОЖЬ, например, если там содержится текст «без НДС».

Значение_если_истина — значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Так, например, если этот аргумент равен формуле С12*1,18, то при значении логического выражения равному ИСТИНА, вычисления ведется по этой формуле.

Если аргумент «лог_выражение» имеет значение ИСТИНА, а аргумент «значение_если_истина» не задан, возвращается значение 0 (ноль).

Значение_если_ложь — значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ЛОЖЬ. Так, например, если этот аргумент равен формуле С12*1 или просто С12, то при значении логического выражения равному ЛОЖЬ, вычисления ведется по этой формуле, т.е., как в нашем примере, берется просто значение суммы из ячейки С12

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

Создание таблицы «Покупка товаров с предпраздничной скидкой». Понятие формулы и ссылки в Excel. Структура и категории функций, обращение к ним. Копирование, перемещение и редактирование формул, автозаполнение ячеек. Формирование текста функции в диалоге.

Встроенные функции MS Excel

Помимо самой рассматриваем функции, в примере используется текстовая функция ЛЕВСИМВ, которая возвращает часть текста из строки, начиная с левого края, в количестве символов, заданном вторым ее аргументом. С ее помощью мы проверяем, являются ли они равными строке «1111», если да, возвращаем результат «Visa», если нет, то выполняем вложенную функцию ЕСЛИ.

Описание разработки

1. Формирование умений и навыков, носящих в современных условиях общенаучный и общеинтеллектуальный характер.

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

1. Воспитательная — развитие познавательного интереса, воспитание информационной культу­ры.

2. Учебная — изучить и закрепить основные на­выки работы с электронными таблицами.

3. Развивающая — развитие логического мышле­ния, расширение кругозора.

1. Опрос учащихся для проверки уровня под­готовки к усвоению нового материа­ла.

2. Объяснение нового материала и самостоятель­ная работа учащихся на компьютерах.

Вопрос: На прошлом занятии мы уже говорили о использовании возможностях програм­мы Excel – работе со встроенными функциями. математическими и статистическими

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

Ответ: математические, статистические, финансовые, логические, функции даты и времени и др.

При помощью какой команды меню можно вызвать Мастер функций

Ответ: с помощью вызова Мастера функций.

Каким образом можно занести формулу в не­сколько ячеек, т.е. скопировать ее?

Ответ: нужно установить курсор на нижнем правом маркере ячейки (курсор должен при­нять вид маленького черного крестика) и про­тянуть его до последней ячейки в нужном диа­пазоне.

2. Изложение нового материала и самостоятель­ная работа учащихся на компьютерах.

(Проводится одновременно с работой учеников в тетради синхронно с учителем.)

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

Ответ: на уроках информатике, когда изучали логику в программирование и на уроках математики.

Запишем в тетрадь формат условной функции. Общий вид условной функции следующий:

Условие — это логическое выражение, которое может прини­мать значение ИСТИНА или ЛОЖЬ.

и могут быть числами, формулами или текстами (текст записывается с помощью кавычек).

Условная функция, записанная в ячейку таблицы, выполняет­ся так: если условие истинно, то значение данной ячейки опреде­лит , в противном случае — .

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

Вспомним, в каких случаях результатом вычисления логического выражения являются ло­гические величины ИСТИНА или ЛОЖЬ, если мы используем логическое И?

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

Ответ: если хотя бы одно составляющее сложного высказывания будет истинно, то и само высказывание –истинно, ложно только в том случае, когда все составляющие ложны.

Существуют особенности записи логических операций в таб­личных процессорах: сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечис­ляются логические операнды. Например, логическое выражение И(А1>0; А1<1) соответствует системе неравенств 0 < А1 < 1.

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

Задача 3. Ученики перед поступлением в гимназию сдают математику, русский и английский языки. Проходной балл – 12. По математике для поступления нужно получить не менее 4 баллов. Составить отчет о поступлении.

Как сделать импликацию в excel?

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

Практическая часть

3. Лист 1 переименовать в лист с названием Сведения о выполненных маршрутах.

4. На рабочем листе Сведения о выполненных маршрутах MS Excel создать таблицу данных о выполненных маршрутах.

5. Заполнить таблицу данных о выполненных маршрутах исходными данными (рис. 1)

6. Лист 2 переименовать в лист с названием Автомобили.

7. На рабочем листе Автомобили MS Excel создать таблицу, в которой будут содержаться технические характеристики автомобилей.

8. Заполнить таблицу с техническими характеристиками автомобилей исходными данными (рис. 2).

Рис. 2. Таблица «Технические характеристики автомобилей»

9. Лист 3 переименовать в лист с названием Рейсы.

10. На рабочем листе Рейсы MS Excel создать таблицу, в которой будут содержаться данные о протяженности рейсов.

11. Заполнить таблицу «Протяженность рейса» исходными данными (рис. 3).

12. Выделить ячейки A2:C5 в таблице «Протяженность рейсов», находящейся на листе Рейсы, и присвоить им имя Рейсы.

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

Размножить введенную в ячейку F2 формулу для остальных ячеек (с F3 по F8) данной графы (Рис. 4).

14. Выделить ячейки B2:C4 в таблице «Технические характеристики автомобилей», находящейся на листе Автомобили, и присвоить им имя Автомобили.

15. Заполнить графу Расход топлива на 100 км таблицы «Данные о выполненных маршрутах», находящейся на листе Сведения о выполненных маршрутах следующим образом:

Размножить введенную в ячейку G2 формулу для остальных ячеек данной графы (с G3 по G8) (рис. 5).

16. Заполнить графу Израсходовано топлива таблицы «Данные о выполненных маршрутах», находящейся на листе Сведения о выполненных маршрутах следующим образом:

Размножить введенную в ячейку H2 формулу для остальных ячеек (с H3 по H8) (рис. 6).

17. Заполнить графу Грузоподъемность таблицы «Данные о выполненных маршрутах», находящейся на листе Сведения о выполненных маршрутах следующим образом:

Размножить введенную в ячейку I2 формулу для остальных ячеек (с I3 по I8) (рис. 7).

18. Заполнить графу Вес перевезенного груза таблицы «Данные о выполненных маршрутах», находящейся на листе Сведения о выполненных маршрутах следующим образом:

Размножить введенную в ячейку J2 формулу для остальных ячеек (с J3 по J8) (рис. 8).

19. Заполним графу ИТОГО таблицы «Данные о выполненных маршрутах», находящейся на листе Сведения о выполненных маршрутах следующим образом:

Размножить введенную в ячейку F9 формулу для остальных ячеек (с G9 по J9) (рис. 9).

20. Заполним графу В СРЕДНЕМ таблицы «Данные о выполненных маршрутах», находящейся на листе Сведения о выполненных маршрутах следующим образом:

Размножить введенную в ячейку F10 формулу для остальных ячеек (с G10 по J10) (рис. 10).

21. Ячейки F10:J10 с помощью Формата ячеек перевести в числовой формат, сократив число десятичных знаков до 2 (рис. 10).

22. Лист 4 переименовать в лист с названием Ведомость.

23. На рабочем листе Ведомость MS Excel создать ведомость расхода горючего каждым водителем за неделю (рис. 11).

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

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

26. В этой же таблице выделить ячейки B2:J10 и присвоить им имя Маршруты.

27. Заполнить графу № рейса ведомости расхода горючего, находящейся на листе Ведомость, следующим образом:

Размножить введенную в ячейку D11 формулу для остальных ячеек (с D12 по D17) (рис. 12).

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

Размножить введенную в ячейку E11 формулу для остальных ячеек (с E12 по E17) (рис. 13).

29. Заполнить графу Израсходовано топлива ведомости расхода горючего, находящейся на листе Ведомость, следующим образом:

Размножить введенную в ячейку F11 формулу для остальных ячеек (с F12 по F17) (рис. 14).

30. Заполнить графу ИТОГО ведомости расхода горючего, находящейся на листе Ведомость, следующим образом:

Размножить введенную в ячейку E18 формулу для ячейки F18 (рис. 15).

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

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

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

Встроенные функции в Excel: основные данные, категории и особенности применения

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

#3. МИН

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

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

Например, минимальное значение среди двух выбранных диапазонов A1:D2, A4:D4 и числа 54 будет 21. Пустые поля и текстовые выражения функцией исключаются и в расчетах не используются.

=МИНА(значение1;[значение2];…) — находит минимальное значение в списке аргументов, при этом текстовые и ложные логические выражения равняются к нулю, а логическое выражение «ИСТИНА» в ячейке равняется 1.

=МАКС(число1;[число2];…) — находит максимальное значение в списке аргументов, при этом текстовые и пустые выражения игнорируются.

=МАКСА(значение1;[значение2];…) — находит максимальное значение в списке аргументов, при этом текстовые и ложные логические выражения приравниваются к нулю, а логическое выражение «ИСТИНА» в ячейке равняется 1.

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

Логические функции в табличном процессоре Excel

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

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

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

Но это далеко не все достоинства данной категории функций. Большой перечень формул – 50, дает возможность создавать многоуровневые формулы для сложных научно-проектных вычислений, и для расчета систем планирования.

Самая популярная функция из этого радела – СУММ (сумма).

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

Еще один лидер категории – ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Позволяет считать итоговую сумму с нарастающим итогом.

Функции ПРОИЗВЕД (произведение), СТЕПЕНЬ (возведение чисел в степень), SIN, COS, TAN (тригонометрические функции) также очень популярны, и, что самое важное, не требуют дополнительных знаний для их введения.

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

Статистические и логические функции в Excel

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

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

Функция ЕСЛИ

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

Рассмотрим первый простой пример, чтобы понять, как функция работает.

Умышлено в первый аргумент функции вставить функцию ИСТИНА. В результате проверки, будет возвращен 2 аргумент (значение_если_истина), 3 аргумент будет опущен.

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

Имеются банковские карточки с номерами, начинающимися с первых четырех цифр, которые являются идентификатором вида карты:

Используем нашу функцию для определения типа карты.

Помимо самой рассматриваем функции, в примере используется текстовая функция ЛЕВСИМВ, которая возвращает часть текста из строки, начиная с левого края, в количестве символов, заданном вторым ее аргументом. С ее помощью мы проверяем, являются ли они равными строке «1111», если да, возвращаем результат «Visa», если нет, то выполняем вложенную функцию ЕСЛИ.

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

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

#5. ОКРУГЛ

1. Определить количество логических операций и порядок их выполнения:

• B
• A B
• A B C
• (A B C) A

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

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