Работа с логическими функциями и консолидация данных

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

Расчёт стипендии.

Задание:

1. Запустить табличный процессор EXCEL и убедиться, что открыта чистая электронная таблица с именем Книга1. Переименовать её в Задание №2.

2. Оформить таблицу по образцу и заполнить ее произвольными значениями.

	A	B	C	D	E
	ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ
		Группа	Дата экзамена	Название предмета -
	№	Фамилия студента	№ зачетной книжки	Оценка	Экзаменатор
…	…	…	…	…	…

№ зачетной книжки – любое 4-значное число,

Оценка – 5, 4, 3, 2, неявка.

Фамилию экзаменатора ввести один раз, а затем использовать режим автозаполнения.

3. Создайте по образцу следующую таблицу, ниже первой:

	В	С
	Количество отличных оценок	=СЧЕТЕСЛИ (D4:D12; 5)
	Количество хороших оценок	……………….
	Количество удовл. оценок	………………..
	Количество неудовл. оценок	…………………
	Количество неявившихся	……………..
	Всего оценок	=СЧЕТ(…….)
	Средний балл	=СРЗНАЧ(……)

Поместить курсор в ячейку С15. С помощью функции СЧЕТЕСЛИ подсчитайте количество отличных оценок. Для этого с помощью мастера функций надо указать диапазон ячеек D4:D12, а вместо условия поставить цифру 5. Формула должна иметь вид =СЧЕТЕСЛИ(D4:D12;5). Аналогично в ячейках С16, С17 и С18 посчитать количество оценок 4, 3 и 2 соответственно. Чтобы определить количество не явившихся студентов, надо в условии функции СЧЕТЕСЛИ текстовое выражение ставить в кавычках, то есть, ²неявка².

В ячейке С20 определить общее количество оценок, используя функцию СЧЕТ. Для определения среднего балла используйте функцию СРЗНАЧ.

4. Скопируйте первую таблицу на 2, 3, 4 и 5 листы. Измените дату, название дисциплины, фамилию экзаменатора и оценки студентов. На рабочем Листе 6 создайте новую таблицу по образцу, приведённому ниже. Самостоятельно определите минимальный размер стипендии (в данном примере – 500).

	A	B	C	D
	Минимальный размер стипендии:
	№	Фамилия студента	Средний балл	Стипендия
			=СРЗНАЧ(Лист1:Лист5!D4)	=ЕСЛИ(С3<3;0;ЕСЛИ(С3<4,5;D1;D1*1,5))
…	…

Чтобы определить средний балл первого в списке студента, воспользуемся консолидацией данных (объединение данных из различных источников).

Легко видеть, что оценки первого студента на всех пяти листах находятся в ячейке D4. Поместить курсор в ячейку С3 на Листе 6. Ввести формулу =СРЗНАЧ(Лист1:Лист5!D4). Для того, чтобы ввести её аргументы, не прибегая к использованию клавиатуры, необходимо проделать следующее:

- вызвать Мастером функцию =СРЗНАЧ() (пока без аргументов);

- щелкнуть по корешку Лист 5;

- нажать и удерживать клавишу Shift;

- щелкнуть по корешку Лист 1;

- отпустить и снова нажать клавишу Shift;

- выделить клетку D4 на Лист 5 и отпустить клавишу Shift;

- нажать кнопку ОК.

Вернувшись к Лист 6, скопировать формулу в ячейки С4:С12.

5. Для расчета стипендии использовать функцию ЕСЛИ. Условия назначения стипендии следующие:

- если средний балл студента выше 4.5, стипендия выплачивается с надбавкой 50%;

- если средний балл студента ниже 4.5, но выше 3, выплачивается минимальный размер стипендии;

- если средний балл студента ниже 3, стипендия не выплачивается.

Пусть минимальный размер стипендии 500 рублей. Для расчета стипендии первого студента поместить курсор в ячейку D4 и ввести формулу ЕСЛИ с помощью Мастера функций:

=ЕСЛИ(С3<3;0;ЕСЛИ(С3>4,5;D1*1.5;D1))

После заполнения аргументов внешней функции ЕСЛИ панель Мастера функций должна выглядеть так, как показано ниже. Третье окно панели Значение_если_ложь остаётся свободным. Туда необходимо поместить вложенную функцию ЕСЛИ. Поскольку сам Мастер функций в этом положении недоступен, следует воспользоваться кнопкой, расположенной левее строки формул. На ней уже стоит символ функции ЕСЛИ. Сейчас нужно щелкнуть мышкой в третьем окне панели, а затем по кнопке ЕСЛИ. На экране должна возникнуть новая панель функции ЕСЛИ, с незаполненными аргументами, а в строке формул запись:

=ЕСЛИ(С3<3;0;ЕСЛИ())

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

Скопировать формулу в ячейки D4:D12.

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

Дата добавления: 2015-07-20