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

Тема Табличный процессор EXCEL-2003

Практическое занятие № 5

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

Задание:

1. Логическая функция ЕСЛИ

=ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) - принимает одно из двух указанных значений, в зависимости от выполнения условия.

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

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

Запустить Excel и создать таблицу по образцу.

В ячейку D2 ввести формулу =ЕСЛИ(С2>=3;”Экзамен сдан”;”Переэкзаменовка”)

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

2. Логическая функция И

И(логическое_значение1; логическое_значение2;...) - принимает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; принимает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Пример: Для студентов, сдававших экзамен, вывести сообщение «Оценка получена», для студентов, проигнорировавших экзамен, вывести сообщение «Не пришел».

Скопировать таблицу на Лист2 и удалить формулы в ячейках D2:D11. Изменить оценки и двум студентам вместо оценок ввести «неявка».

В ячейку D2 ввести формулу =ЕСЛИ(И(С2>=2;С2<=5);”Оценка получена”;”Не пришел”)

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

3. Логическая функция ИЛИ

ИЛИ(логическое_значение1; логическое_значение2; ...) - принимает зна­чение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; принимает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Пример: Для студентов, сдавших экзамен на 3, 4 или 5, вывести сообщение «Оценка положительная», остальным студентам – «Очень плохо».

Скопировать таблицу на Лист3 и удалить формулы в ячейках D2:D11.

В ячейку D2 ввести формулу =ЕСЛИ(ИЛИ(С2=3;С2=4;С2=5);”Оценка положительная”;”Очень плохо”)

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

4. Статистическая  функция СЧЕТЕСЛИ

=СЧЕТЕСЛИ( диапазон; критерий)

            где:

            - Диапазон – блок ячеек, содержащий проверяемые значения;

            - Критерий – константа или условие проверки;

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

Пример: Необходимо подсчитать количество различных оценок по результатам экзамена.

Вставить Лист4, переместить его на место (за Лист3). Скопировать на этот лист диапазон ячеек А1:С11 с листа 1. Оформить ячейки столбца D по образцу.

В ячейку Е2 ввести формулу =СЧЕТЕСЛИ(С2:С11;5). В ячейку Е3 ввести формулу, используя мастер функций.

По аналогии ввести формулы в ячейки Е4 и Е5. При вводе формулы в ячейку Е6 в поле Критерий ввести неявка.

Показать результаты преподавателю, сохранить файл с именем Стат-ф-4.

5. Математическая функция СУММЕСЛИ

=СУММЕСЛИ( Диапазон; Критерий [; Диапазон суммирования ] )

где:

            - Диапазон – диапазон проверяемых значений;

            - Критерий – константа или условие проверки;

            - Диапазон суммирования – обрабатываемый диапазон. Если он отсутствует, то 1-й аргумент функции становится и проверяемым и обрабатываемым диапазоном;

            - [ ] – указание на то, что данный аргумент функции может отсутствовать.

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

Вставить Лист5. Разместить листы по порядку. Оформить таблицу по образцу.

В ячейку А10 ввести текст Сумма, если >4000. В ячейку С10 ввести формулу =СУММЕСЛИ(С2:С8;">4000").

Из примера видно, что суммироваться будут значения только тех ячеек диапазона С2:С8, которые отвечают условию «>4000». В данном случае диапазон C2:C8 и проверяется и обрабатывается.

Рассмотрим использование функции с тремя аргументами.

В ячейку А11 ввести текст Сумма для НР. В ячейку С11 ввести формулу =СУММЕСЛИ(А2:А8; "НР";С2:С8).

В данном случае производится суммирование ячеек диапазона С2:С8 только в тех случаях, если в соседней ячейке диапазона А2:А8 находится запись «НР». Здесь диапазон А2:А8 проверяется, а диапазон С2:С8 обрабатываются.

Показать результаты преподавателю, сохранить файл с именем Мат-ф-5.

6. Вложенная функция ЕСЛИ

Вставить Лист6. Разместить листы по порядку. Оформить таблицу по образцу.

Предположим, что премия начисляется при следующих условиях:

-          если средний балл меньше 3, то премия равна 0,

-          если средний балл больше 3, но меньше 4.5, то премия равна 500р,

-          если средний балл больше 4.5, то премия равна 800р.

Поместить курсор в ячейку D2 и вызвать Мастера функций.

На первом шаге в левом окне выбрать категорию Логические. В правом окне выбрать функцию ЕСЛИ.

Щелкнуть по кнопке ОК. Появится окно функции ЕСЛИ.

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

Значение среднего балла находится в ячейке С2. Поэтому надо щелкнуть мышью по кнопке с красной стрелочкой в верхнем поле справа. Диалоговое окно свернется в строку.

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

Теперь с помощью клавиатуры надо дополнить условие С2<3. Можно было в первом поле ввести это условие с клавиатуры, не пользуясь кнопкой с красной стрелочкой (кнопкой свертки).

Во второе поле ввести значение для случая, когда C2<3, то есть, ввести 0.

Нажать клавишу Tab или щелкнуть мышкой в третьем поле.

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

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

В первое поле нового окна ввести второе условие C2<4,5, во второе поле ввести значение 500, а в третье поле ввести значение 800.

Щелкнуть по кнопке ОК.

В ячейку D2 будет помещен результат расчета, а в строке формул отображена расчетная формула с вложенной функцией ЕСЛИ. Скопировать эту формулу в ячейки D3:D5 и посмотреть результаты расчета.

Показать преподавателю и сохранить файл с именем Если-влож.

Вставить Лист7. Разместить листы по порядку. Скопировать на Лист7 таблицу с Листа6, удалить в столбце Премия формулы. Добавить в список фамилию Чернов, средний балл 1,5. Исправить Белову средний балл 0,8.

Определить премию в зависимости от среднего балла при следующих условиях:

если <1, вывести сообщение «штраф»,

если >1, но <2, то премия равна 0,

если >2, но <3, то премия равна 100,

если >3, но <4, то премия равна 200,

если >4, то премия равна 300.

Получить решение, показать преподавателю и сохранить файл с именем Если-сам.

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