Задание. Цель работы: Освоить работу с логическими функциями (ЕСЛИ, И, ИЛИ) и функцией баз данных (ВПР)

Лабораторная работа №12

ЛОГИЧЕСКИЕ ФУНКЦИИ, ФУНКЦИИ БАЗ ДАННЫХ

Цель работы: Освоить работу с логическими функциями (ЕСЛИ, И, ИЛИ) и функцией баз данных (ВПР).

Задание

1. Назовите лист Рейтинг. На этом листе наберите следующую таблицу:

2. Ниже, пропустив несколько пустых строк наберите еще одну таблицу:

3. Скопируйте лист Рейтинг, назовите копию Промежуточный.

4. На листе Рейтинг в таблице добавьте еще столбцы: Итоговый рейтинг; Вид отчетности; Оценка за экзамен; Информация о зачете; Итоговая оценка/зачет. Заполните их следующим образом:

Итоговый рейтинг - = (часть 1 + часть 2 + часть 3 + часть 4)/ 400. Задать процентный формат.

Вид отчетности – в итоге в этом столбце должно появиться слово либо экзамен, либо зачет.

Используем функцию (Формулы- Ссылки и массивы) ВПР: искомое значение – выделите название предмета первой строки, таблица – абсолютная ссылка на таблицу ниже (выделите таблицу ниже, затем удалите в этой ссылке знаки доллара), номер столбца - 2.

Оценка за экзамен – должно выводиться отлично, если Итоговый рейтинг меньше 0,85; хорошо если Итоговый рейтинг между 0,7 и 0,85 и удовлетворительно, если Итоговый рейтинг меньше 0,7. Задаем по формуле (Итоговый рейтинг – это ссылка на значение первой строки этого столбца):

= ЕСЛИ (Итоговый рейтинг < 0,7; "удовлетворительно"; ЕСЛИ (Итоговый рейтинг > 0,85; "отлично";"хорошо"))

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

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

5. Перейдите на лист Промежуточный. Добавьте столбцы: Возраст, 1-2 части(80),
2-3 части(75), 1(80)-3(85) части, 2(80) и 4(75) части.

Возраст – подсчитать возраст, использовав функцию ГОД:

=2011-ГОД(год рождения); задать формат числовой, без знаков после запятой.

1-2 части(80) – определить студентов, у которых рейтинг за 1 или 2 часть выше 80, используя функцию ИЛИ.

2-3 части (75) – определить студентов, у которых рейтинг за 2 и 3 часть выше 75, используя функцию И.

1(80)-3(85) части - определить студентов, у которых рейтинг за 1 выше 80 и за 3 часть выше 85, используя функцию И.

2(80) и 4(75) части - определить студентов, у которых рейтинг за 2 выше 80 или за 4 часть выше 75, используя функцию ИЛИ.

6. Измените в последних четырех столбцах функцию так, чтобы вместо значения ИСТИНА выводилось значение Выполнил, а вместо ЛОЖЬ значение Не выполнил. Использовать функцию ЕСЛИ.

7. Новый лист назовите Итог. На листе Итог наберите таблицу:

Заполнить столбцы, используя данные с листа Рейтинг следующим образом:

Инициалы – по функции ПСТР в первом столбце должна выводиться первая букву имени, во втором – отчества.

Аргументы функции в столбце Имя: текст – данные столбца имя с листа Рейтинг, начальная позиция – 1. кол-во знаков – 1.

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

Предмет – по функции ВПР. Аргументы: Искомое значение –данные столбца фамилия, Таблица – абсолютная ссылка на всю таблицу с листа Рейтинг; номер столбца – 5.

Вид отчетности, итоговая оценка/зачет – функция строится аналогично функции в столбце предмет, за исключением номера столбца.