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

Задание 1. Консолидация данных по расположению.

Подготовим исходные данные.

В качестве таблицы источника выберем «Ведомость» (см. пред. работу).

С помощью команды Фильтр / Автофильтр выберем из нее последовательно записи, относящиеся к месяцам (октябрь и ноябрь).

Скопируем их и разнесем по разным листам книги. Листы назовем Октябрь и Ноябрь.

Из таблиц удалим столбцы Стаж и Месяц.

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

Рис.2.2

Рис.2.3

Выполним консолидацию данных по расположению.

Добавим в книгу новый лист Консолидация.

Активизируем ячейку А1 и выполним команду Данные / Консолидация.

В открывшемся окне введем диапазон, занимаемым первым списком – Октябрь!$A$3:$I$7. Нажмем кнопку Добавить.

Далее введем диапазон, занимаемым вторым списком – Ноябрь!$A$3:$I$7. Нажмем кнопку Добавить.

Установим функцию из раскрываемого списка, например Сумма (рис.2.4)

Рис.2.4

Установим флажки на опциях: Использование в качестве имен, или Создавать связи с исходными данными (но не одновременно). Нажмем ОК.

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

Рис.2.5

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

Подготовим исходные данные.

В качестве таблицы источника выберем «Ведомость» (см. пред. работу).

С помощью команды Фильтр / Автофильтр выберем из нее последовательно записи, относящиеся к месяцам (сентябрь, октябрь и ноябрь).

Скопируем их и разнесем по разным листам книги. Листы назовем Сентябрь, Октябрь и Ноябрь.

Из таблиц удалим столбцы Стаж и Месяц.

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

Выполним консолидацию всех списков по описанной выше технологии. Получим таблицу консолитизированных данных (рис.2.6).

Рис.2.6

Задание 3. Консолидация списков с разным числом полей.

При консолидации данных из списков с разным числом полей, дополнительные поля присоединяются справа, например структура списков источников (рис.2.7).

Рис.2.7

Список «Ведомость за сентябрь». Начисление (рис.2.8)

Рис.2.8

2. Список «Ведомость за октябрь». Удержание (рис.2.9)

Рис.2.9

3. Список «Ведомость за ноябрь». Удержание (рис.2.10)

Рис.2.10

4. Структура результата консолидации.

Задание 4. Создание сводной таблицы.

Выполним анализ данных и консолидацию на основе сводных таблиц

Откройте новый рабочий лист и присвойте ему имя Таблица.

Выполните команду Данные/Сводная таблица. После ее активизации откроется первое диалоговое окно Мастера сводных таблиц (рис.2.11)

Рис.2.11

Шаг 1. В этом окне вам предлагается выбрать один из четырех источников данных для сводной таблицы и выбрать форму отчета.. Одобрите выбор мастера - В списке или базе данных Microsoft Office Excel - и нажмите кнопку Далее.

Шаг 2. В следующем диалоговом окне необходимо указать интервал ячеек, данные из которого будут представлены в сводной таблице. Выберем диапазон ячеек списка Ведомость (кроме строк Ведомость оплаты труда, Начислено и Удержано). Нажмем кнопку Далее (рис.2.12)

Рис.2.12

Шаг 3. Определим расположение сводной таблицы: На существующем листе.

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

Рис.2.13

Поместим: в область строк - поле ФИО; в область столбцов – поле Месяц; в область данных - поля Начислено, Удержано, К выдаче. Щелкнем Готово. Сводная таблица построена (рис.2.14).

Рис.2.14

Щелкнув в поле сводной таблицы правой кнопкой, вызывается контекстное меню по изменению всего процесса построения. Попробуйте выбрать одну из команд и проведите изменения в таблице (рис.2.15).

Рис.2.15

Постройте сводную диаграмму, выбрав соответствующую команду из контекстного меню или кнопку на панели Сводные таблицы (рис.2.16).

Рис.2.16

Отредактируйте и отформатируйте сводную диаграмму (рис.2.17).

Рис.2.17

Задание 5. Создание серийных писем (приглашений). Источник данных – MS Access.

В практике делопроизводства часто возникает задача создания нескольких типовых документов, отличающихся друг от друга конкретными данными (имя, фамилия, адрес и т.д.). Например, необходимо разослать письмо-приглашение по нескольким адресам. Для этого создается шаблон письма, в который вставляются поля слияния из источника данных. В качестве источника данных может выступать документ Word, таблица или запрос MS Access, таблица MS Excel. Рассмотрим процедуру создания серийных писем, где данные будут взяты из БД «Борей».

В текстовом процессоре Word откройте новый документ Файл/Создать/Шаблоны на моем компьютере. Перейдите на вкладку «Слияние» и выберите «Современное составное письмо».

Загрузите панель инструментов «Слияние» (Вид/Панели инструментов/Слияние) (см. рис.2.18).

Рис. 2.18. Панель инструментов «Слияние»

Откройте источник данных, используя пиктограмму «Открыть источник данных». В качестве источника данных выберите БД «Борей» (….\ Microsoft Office\Office11\Samples\Борей.mdb).

В окне «Выделить таблицу» (см. рис.2.19) выберите таблицу «Сотрудники».

Рис.2.19. Окно выбора таблиц БД «Борей»

Сформируйте шаблон «Приглашение на семинар» по образцу (см. рис.2.20). Сохраните в своей папке под названием «Приглашение».

Для добавления полей слияния используйте пиктограмму «Вставить поля слияния». Выберите по очереди следующие поля слияния: Должность, Страна, Индекс, Город, Адрес, Фамилия, Имя (см. рис.2.21). Расположите и отформатируйте их согласно образцу.

Рис. 2.20. Добавление полей слияния в шаблон

Рис.2.21. Шаблон «Приглашение на семинар»

Для того чтобы обращение к сотруднику формировалось автоматически и корректно, воспользуйтесь полями слияния Word с помощью пиктограммы . Из раскрывающегося списка выберите оператор If…Then…Else и сформируйте поле слияния согласно рис.2.22.

Рис.2.22. Вставка поля If

После того как шаблон отредактирован и вставлены поля слияния из таблицы «Сотрудники», необходимо провести слияние в новый документ, используя пиктограмму .

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

Задание 6. Импорт данных в MS Excel.

Откройте новую книгу MS Excel и сохраните в своей папке под именем «Импорт продаж».

Для импорта данных из БД «Борей» выполните команду Данные/Импорт внешних данных/Импортировать данные. Откройте источник данных (….\ Microsoft Office\Office11\Samples\Борей.mdb).

В окне «Выделить таблицу» выберите запрос «Продажи по типам».

Поместите данные в ячейку А1 текущего листа (см. рис.2.23).

Рис. 2.23. Импорт данных

Отсортируйте столбец «Код типа» по возрастанию. Дайте листу имя Продажи по типам.

Скопируйте всю таблицу на новый лист и дайте ему имя Продажи по категориям.

Вернитесь на лист Продажи по типам и подведите промежуточные итоги Продаж по типам, используя команду Данные/Итоги…(см. рис.2.24), предварительно выделив диапазон данных, включая имена столбцов.

Рис. 2.24. Промежуточные итоги

Сверните полученную структуру (см. рис.2.25).

Рис.2.25. Таблица итогов по полю КодТипа

Постройте объемную круговую диаграмму продаж по типам согласно образцу (см. рис.2.26).

Рис.2.26. Диаграмма «Объемы продаж по типам»

Перейдите на лист Продажи по категориям и проанализируйте объемы продаж по категориям, следуя пунктам 5-8 задания 2.

Задание 7. Создание сводных таблиц на основе данных из внешнего источника.

Откройте новую книгу MS Excel и сохраните в своей папке под именем «Сводная таблица».

Выполните команду Данные/Сводные таблицы. В первом окне Мастера сводных таблиц и диаграмм установите опцию «Во внешнем источнике данных» (см. рис. 2.27).

Рис.2.27. Шаг 1. Мастер сводных таблиц и диаграмм

На втором шаге Мастера щелкните по кнопке «Получить данные».

В окне «Выбор источника данных» выделите строку «База данных MS Access» и нажмите кнопку ОК (см. рис.2.28).

Рис. 2.28. Выбор источника данных

Далее укажите путь к БД «Борей» и нажмите кнопку ОК (см. рис.2.29).

Рис. 2.29. Выбор базы данных

В следующем окне разверните структуру запроса «Продажи товаров в 1997» и перетащите все столбцы в область запроса (см. рис.2.30). Нажмите кнопку Далее.

Рис. 2.30. Создание запроса: выбор столбцов

В окне Создание запроса: отбор данных нажмите также на кнопку Далее.

В окне Создание запроса: порядок сортировки установите сортировку по полю Квартал Исполнения по возрастанию. Нажмите кнопку Далее.

На заключительном шаге установите опцию «Вернуть данные в MS Excel и нажмите кнопку Готово (см. рис.2.31).

Рис. 2.31. Создание запроса: заключительный шаг

Если данные успешно получены, то вновь откроется окно 2-го шага Мастера сводных таблиц и диаграмм с сообщением, что данные получены. Нажмите кнопку Далее.

Далее укажите месторасположение данных (на этом же листе в ячейке А1).

В результате успешного импорта откроется область для создания сводных таблиц (см. рис.2.32).

Рис. 2.32. Область формирования сводной таблицы

В область столбцов перетащите поле Квартал Исполнения, в область строк – поле Категория, в область данных – Продажи Товаров. В результате получится сводная таблица (см. рис. 2.33).

Рис. 2.33. Сводная таблица «Продаж товаров за 1997 год»

Задание для самостоятельной работы

1. Выполните консолидацию списков.

Предмет	Семестр	Лекции	Практические	Всего	Лекции	Практические	Всего
иностранный	весенний
культурология	весенний
математика	весенний
микроэкономика	весенний
статистика	весенний

Предмет	Семестр	Лекции	Практические	Всего	Лекции	Практические	Всего
иностранный	осенний
информатика	осенний
макроэкономика	осенний
математика	осенний
статистика	осенний

2. Постройте сводную таблицу для списка ЗАКАЗЫ (лаб. №2).

3. По данным сводной таблицы постройте сводную диаграмму (рис.2.33).

Контрольные вопросы

1. Что такое консолидация.

2. Какие виды консолидации вы знаете.

3. В чем они отличаются.

4. Можно ли изменять функцию консолидации.

5. Можно ли связать результат консолидации с источниками.

6. Что такое сводная таблица?

7. Как изменить параметры таблицы.

8. Как построить макет таблицы.

9. Опишите технологию создания серийных писем с помощью документа Word или запроса MS Access.

10. Опишите технологию импорта данных в MS Excel.

11. Как осуществляется создание сводных таблиц на основе данных из внешнего источника?

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