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

Создаем через меню Вставка Þ Сводная таблица. Появится последовательность диалоговых окон, в которых надо задать:

1. Откуда берутся данные для сводной таблицы

2. Диапазон

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

4. В четвертом окне можно задать некоторые параметры, определяющие вид сводной таблицы, например,

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

§ В поле Название таблицы указать название сводной таблицы

§ Установить флажки для подведения общих итогов по строкам и столбцам, если они необходимы.

После щелчка на кнопке Готово будет создана сводная таблица.

Работа со сводной таблицей

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

2. Для обновления сводной таблицы после изменения данных в база данных надо выполнить команду Обновить данные в контекстном меню или в меню Данные

3. Изменить поле сводной таблицы можно, дважды щелкнув его название. Открывшееся диалоговое окно Вычисление поля сводной таблицы позволяет изменить имя поля и операцию, на основе которой составляется сводная таблица.

4. Для удаления поля из сводной таблицы достаточно щелкнуть его название и перетащить за пределы сводной таблицы

5. Перемещая названия полей, можно менять их местоположение

6. Двойной щелчок на ячейке сводной таблицы вызывает создание новой таблицы (на новом рабочем листе), содержащей обобщенную информацию об этой ячейке.

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

8. Чтобы разгруппировать данные, надо выбрать названия соответствующих полей и выполнить команду Данные, Структура, Разгруппировать.

9. Мастер сводных таблиц может применяться для определения зависимости между различными элементами, например, для вычисления накопленного значения, межпериодных разностей, доли каждого элемента в общем итоге для всех элементов поля. Технология работы:

а) вызвать мастер сводных таблиц

б) в область данных поместить еще раз поле, по которому хотим определить зависимость (например, размер вклада2)

в) вызвать окно диалога Вычисление поля двойным щелчком по заголовку поля в области Данных (например, сумма по полю размер вклада2). В этом окне выбрать Дополнительно, Отличие ( для определения в абсолютных величинах) или Приведенное отличие (для расчета в %).

Задание 1

Вариант 1

1. На основании данных таблицы (см. файл Сводная таблица.xls, лист 2) создайте сводную таблицу, в которой содержится информация о суммарном размере вклада в каждом отделении банка и общий итог.

2. Сделайте детализацию по типу вклада для Западного отделения.

3. Измените таблицу так, чтобы типы вклада (депозит, текущий) и общий итог были в столбцах

4. Получите расшифровку суммарного значения депозитного вклада в Центральном отделении

5. Выведите информацию о количестве вкладов каждого типа в отделениях банка

6. Объедините Северное и Западное отделения в Северо-Западное и выведите данные о суммарном размере вклада по каждому типу.

7. Найдите долю каждого отделения банка по текущим и депозитным вкладам.

Вариант 2

1. На основании данных таблицы (см. файл СписокСотрудников.xls,) создайте сводную таблицу, в которой содержится информация о общем фонде заработной платы в каждом отделе и общий итог.

2. Сделайте детализацию по полу для 2-го отдела..

3. Измените таблицу так, чтобы пол и общий итог были в столбцах

4. Получите расшифровку суммарного значения для мужчин в 3-м отделе.

5. Выведите информацию о количестве работников каждого пола в отделах.

6. Объедините 1 и 3 отделы в службу Администрация и выведите данные о среднем размере оклада сотрудников каждого пола.

7. Найдите долю каждого отдела по общему ФЗП и численному составу.

Вариант 3

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

2. Сделайте детализацию по курсам и группам для УЭФ

3. Измените таблицу так, чтобы факультет отображался на оси страниц, а курс – в стоках, а группа - в столбцах

4. Получите расшифровку суммарного значения для 1 группы 1 курса УЭФ.

5. Выведите информацию о количестве студентов каждого пола на факультетах.

6. Определите средний балл по математике по факультетам.

7. Найдите долю каждого факультета в общем количестве студентов.