double arrow

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


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

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

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

Задание

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

Фамилия Имя отчество год рождения предмет рейтинг
1 часть 2 часть 3 часть 4 часть
Амигов Иван Альбертович 27.09.1991 История
Воробьев Алексей Николаевич 07.02.1992 История
Валкин Олег Михайлович 20.05.1991 Математика
Горохов Петр Петрович 30.07.1991 История
Дроздовский Семен Владимирович 01.01.1992 Информатика
Коломейцев Алексей Петрович 29.07.1991 Математика
Лешенин Дмитрий Валерьевич 04.06.1993 Информатика
Менялов Михаил Олегович 30.04.1990 Экономика
Обухов Иван Владимирович 07.11.1990 Математика
Семенова Ольга Дмитриевна 23.06.1992 Экономика
Маякова Елена Васильева 04.04.1988 Химия
Орлова Галина Ивановна 09.10.1990 Экономика
Черепанов Леонид Владимирович 12.06.1991 История
Желейнов Олег Игоревич 20.08.1991 Математика
Свиридова Жанна Петровна 06.07.1992 Химия

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.

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


Сейчас читают про: