Лабораторные, контрольные и самостоятельные работы по информатике (стр. 7 )

Лабораторная работа E5 «Консолидация данных»

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

Рис. 4.9. Пояснение лабораторной работы «Консолидация данных»

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

Последовательность работы:

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

2)  Вызвать команду Данные/Консолидация.

3)  В окне Консолидация (Рис. 4.9 – внизу) в списке Функция укажите Сумма. В этом же окне установите флажок Использовать в качестве имен значения левого столбца (имеется в виду левый столбец в каждом выделяемом далее исходном блоке ячеек).

4)  Установите курсор в строку Ссылка. Выделите первую исходную область. В нашем примере это B3: E5 и нажмите (щелчок мышью) кнопку Добавить.

5)  Повторите эти действия для блоков B6:E8 и B9:E10, затем нажмите кнопку ОК -будет построена таблица консолидированных данных (Рис 4.9 – строки 12-15).

Лабораторная работа E6 «Сводная таблица»

Построение сводной таблицы выполняется с помощью событийной процедуры, называемой Мастером сводных таблиц. Для построения сводной таблицы используйте те же самые данные, которые были исходными для консолидации (строки 1-10 на рис. 4.9).

Порядок работы:

1)  Установите курсор в ячейку, начиная с которой желательно поместить сводную таблицу, например, в ячейку A20. Выполните команду Данные/Сводная таблица и выберите режим Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Перейдите к следующему шагу – кнопка Далее.

2)  Укажите диапазон данных. В нашем случае это A2:E10 (Рис. 4.9)

3)  На третьем шаге нужно создать макет сводной таблицы. Для этого выберите вкладку Макет (Рис. 4.10) и переместите мышкой кнопку Кафедра на поле Страница, кнопку Наименование – на поле Строка, кнопку Цена – на поле Столбец, кнопку Сумма – на поле Данные (Рис. 4.10). После нажатия кнопки Далее будет создана сводная таблица, изображенная на рисунке 4.11. Из нее видно, по какой цене и на какую сумму приобретено оборудование каждого типа.

Рис. 4.10. Мастер сводных таблиц, шаг третий

Кафедра	(Все)
Сумма по полю Сумма	Цена
Наименование	250	1900	2000	5400	5800	20000	22000	Общий итог
Аудиоколонки	500							500
Компьютеры						220000	88000	308000
Принтеры				16200	5800			22000
Сканеры		3800	2000					5800
Общий итог	500	3800	2000	16200	5800	220000	88000	336300

Рис. 4.11. Сводная таблица

4.4. Самостоятельные работы по Excel

Самостоятельная работа «Проходной балл»

Подготовить таблицу (Рис. 4.12): ввести данные по оценкам (от 2 до 5) с использованием функции получения случайных чисел =ОКРУГЛ(СЛЧИС()*3+2;0), к полученным оценкам (случайным числам) применить специальную вставку, чтобы заменить формулы полученными значениями. Вычислить суммарный балл и число двоек. В столбец «Сообщение о зачислении» занесите “Зачислить”, если сумма баллов больше проходного, а число двоек равно нулю, или “Отказать” в обратном случае. Используя условное форматирование выделите цветом все сообщения “Зачислить”. С помощью функции СЧЁТЕСЛИ подсчитайте количество зачисленных абитуриентов.

Ведомость приёма на обучение в университет
		Проходной балл	xx
№ абит.		Математика	Физика	Литература	Химия	Суммарный балл	Число двоек	Сообщение о зачислении
1
2
3
4
5
6
7
8
9
10
Средний балл за экзамен					Количество зачисленных

Рис. 4.12. Пояснение самостоятельной работы «Проходной балл»