Технология выполнения работы

Задание 1. Даны сведения об успеваемости учащихся 2 курса по трем предметам. Вычислить среднюю успеваемость по группам по каждому предмету.

1. Создайте таблицу согласно рис.1.

Рис.1.

2. Выделите диапазон таблицы, включая шапку А3: F 14 (рис.1). Выполните команду Данные-Итоги. В диалоговом окне Промежуточные итоги:

· в поле При каждом изменении в: из раскрывающего списка выбрать Группа;

· в поле Операция из раскрывающего списка выбрать Среднее;

· в поле Добавить итоги по: установите флажки;

Рис.2.

· установите флажок Итоги под данными.

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

· Округлить полученные итоги до двух десятичных знаков с помощью команды Формат-ячейки.

    Результат работы представлен на рис.3.

Рис.3.

Задание 2. Пусть на разных листах рабочей таблицы представлены отчеты о продаже товаров за три месяца различными филиалами фирмы. Необходимо построить объединенную таблицу по филиалам, показывающую общее количество товара по месяцам.

Под термином консолидация подразумевается ряд стандартных операций с несколькими рабочими таблицами и рабочими книгами. В некоторых случаях консолидация может включать в себя создание связанных формул. Основной фактор, влияющий на консолидацию данных – это способ размещения информации в рабочих таблицах. Если размещение информации во всех таблицах одинаково, то говорят о консолидации по позиции. В том случае, когда размещение информации не идентично, но достаточно похоже, то можно объединить данные по заголовкам строк и/или столбцов. Такая консолидация называется консолидацией по категориям. Если же рабочие таблицы имеют мало общего друг с другом, то необходимо отредактировать листы, чтобы они стали единообразными.

 Как видно, списки включенных в них товаров, а также порядок перечисления в них различны. Другими словами, способ размещения информации в этих рабочих таблицах не одинаков. Поэтому для получения итоговых данных о продаже изделий фирмой по месяцам, необходимо выполнить консолидацию по категории.

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

    Создайте новую рабочую книгу (выберите новый рабочий лист), где должны размещаться результаты консолидации. Выполните команду Данные / Консолидация.

    Задание параметров для диалогового окна «Консолидация».

· В поле «Функция» укажите функцию Сумма, которая показывает тип объединения данных.

· В поле «Ссылка» введите ссылку на диапазон первой рабочей таблицы, которые должны быть консолидированы. Если нужная книга закрыта, щелкните по кнопке «Обзор», чтобы найти нужный файл на диске. Ссылка может задавать диапазон больший, по числу строк, чем нужно консолидировать, но в случае добавления новых строк, параметры консолидации не нужно будет изменять. Когда в поле «Ссылка» будет введена нужная ссылка, щелкните по кнопке «Добавить», чтобы добавить ее к списку диапазонов.

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

· Так как способы размещения информации в рабочих таблицах различны, установим опции Подписи верхней строки и Значения левого столбца. В результате Excel будет подбирать данные по заголовкам (рис.4).

Рис.4.

· Для того, чтобы консолидация была динамической, установим опцию Создавать связи с исходными данными и нажмем кнопку «ОК». В результате Excel создаст структуру, содержащую внешние ссылки (рис.5).

Рис.5.

Задание 3.

       Фирма "Рога и копыта" закупила для своих подразделений мониторы и принтеры. Общие результаты покупки представлены в таблице.

· Получить общее количество и общую стоимость всех мониторов, общее количество и общую стоимость всех принтеров, а также общее количество и общую стоимость всех купленных изделий;

· Определить средние значения цены мониторов и цены принтеров;

· Рассчитать общее количество и общую стоимость каждой разновидно­сти мониторов и принтеров;

· Полученные в предыдущем задании данные представить в виде, пока­занном на рис.6.

Рис.6.

· Определите средние значения цены каждой разновидности мониторов и принтеров.

Задание 4. Фирма "Рога и копыта" закупила для своих подразделений мониторы и принтеры. Общие результаты покупки представлены в двух таблицах (рис. 7, 8).

Перенести данные каждой таблицы на отдельный лист (значения в гра­фе Общая стоимость определить по формуле).

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

Рис.9.

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

Сводные таблицы.