Задание и порядок выполнения работы

Краткие теоретические сведения

Консолидация - объединение данных, представленных в исходных областях – источниках.

Результат консолидации находится в области назначения. Области- источники могут находиться на различных листах и книгах. В консолидации могут участвовать до 255 областей- источников.

Существуют следующие варианты консолидации данных:

1. с помощью формул, где используются ссылки;

2. по расположению данных для одинаково организованных областей- источников (фиксированное расположение);

3. по категориям для различающихся по своей структуре области данных;

4. с помощью сводной таблицы;

5. консолидация внешних данных.

Консолидация с помощью сводной таблицы.

Команда Вставка - Сводная таблица вызывает Мастера сводных таблиц для построения сводов- итогов определенных видов на основе списков, других сводных таблиц, внешних баз данных и т.д. Сводная таблица обеспечивает различные способы объединения информации.

Мастер сводных таблиц осуществляет построение сводной таблицы в несколько этапов:

Этап 1. Указание вида источника сводных таблиц:

- использование списка

- использование внешнего источника данных

- использование нескольких диапазонов консолидации

- использование данных из другой сводной таблицы

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

Этап 2. Указание диапазонов, содержащих исходные данные. Список обязательно должен содержать имена полей (столбцов). Полное имя диапазона записывается в виде:

[Имя книги] имя листа!диапазон

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

Этап 3. Построение макета сводной таблицы. Структура сводной таблицы состоит из следующих областей, определяемых в макете:

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

2) столбец - поля размещаются слева направо, обеспечивая группировку данных сводной таблицы по иерархии полей; при условии существования области страницы или строки, определять столбец необязательно.

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

4) данные – поля, по которым подводятся итоги согласно выбранной функции; эту область определять обязательно.

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

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

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

Этап 4. Выбор места расположения и параметров сводной таблицы (на новом или существующем листе). После нажатия кнопки «Готово» будет сформирована сводная таблица со стандартным именем.

Кнопка «Параметры» в диалоговом окне последнего шага вызывает диалоговое окно, в котором устанавливается вариант вывода информации в сводной таблице:

1) общая сумма по столбцам – внизу сводной таблицы выводятся общие итоги по столбцам.

2) общая сумма по строкам – формируется итоговый столбец

3) сохранить данные вместе с таблицей- сохраняется не только макет, но и результат построения сводной таблицы.

4) автоформат

После завершения построения сводной таблицы могут изменяться исходные данные. Для обновления данных следует выполнить команду Данные-Обновить данные.

Задание и порядок выполнения работы

1. В программе Excel создайте и заполнить таблицу анализа продаж:

2. произвести расчеты, выделив минимальную и максимальную продажи (количество и сумму); произведите фильтрацию по цене, превышающей 30000 р., постройте гистограмму отфильтрованных значений изменения выручки по видам продукции.

Формулы для расчета: Всего = Безналичные платежи + Наличные платежи;

Выручка от продажи = Цена * Всего.

Лист 1 назовите «Продажи».

3. На Листе 2 создайте таблицу «Канцелярские принадлежности» с графами: № п/п, наименование, цена, количество, стоимость, продано, сумма проданного, заполните ее данными по предложенному ниже образцу:

Выполнение расчетов

4. Рассчитайте значение стоимости по формуле: цена * количество.

5. Заполните столбец Продано произвольными данными.

6. Вычислите Сумму проданного товара по формуле: продано* цена.

7. Дополните таблицу графой Остаток, в ней вычислите сколько товара осталось на складе, после продажи.

8. Скопируйте полученную таблицу на Лист3, Лист4, Лист5, Лист6 (предварительно создав необходимое количество листов). Лист2 назовите Канцелярия.

Фильтрация данных

9. На Листе3 с помощью Расширенного фильтра отобразите только те товары, цена которых превышает 20 руб. Для этого постройте сначала таблицу-критерий под исходной таблицей, в ней должны содержаться 2 графы: Наименование и Цена, после чего в графу Цена, необходимо внести условие (>20).

10. Затем используйте Данные – Фильтр – Расширенный фильтр, выделив в качестве Исходного диапазона исходную таблицу, а в качестве Диапазона условий – Таблицу-критерий. Просмотрите какие данные выводятся, проверьте их правильность. Назовите Лист3 Фильтр.

Создание структуры таблицы

11. Чтобы упростить представление данных создают Структуру таблицы.

12. Создайте Структуру для таблицы Канцелярские принадлежности на Листе4. Для этого щелкните в любой ячейки таблицы, выберите команду Данные – Группировать – Создание структуры. После этого появятся кнопки уровней детализации, кнопки сворачивания и разворачивания структуры, щелкнув по ним можно перейти к сокращенному виду таблицы, в котором отсутствуют столбцы, отмеченные в структуре скобкой. Просмотрите на изменения в таблице. Назовите Лист4 Структура.

Проведение промежуточных итогов

13. Еще одной автоматически выполняемой операцией является анализ записей на наличие повторяющихся данных в столбце и проведение промежуточных итогов.

14. Выполните подведение промежуточных итогов на Листе5. Для этого сначала отсортируйте данные в таблице по полю Наименование по возрастанию.

15.  Затем выполните команду Данные – Промежуточные Итоги, в появившемся окне установите флажки по образцу:

16. Просмотрите получившуюся таблицу, изучите процесс сворачивания элементов структуры. Назовите Лист5 Итоги.

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

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

17. Перейдите на Лист6 вставьте в таблицу после столбца № п/п столбец Магазин и заполните его, внесите два названия магазина «Луч» и «Глобус», сделайте так, чтобы в магазинах было несколько одинаковых товаров, как на примере:

18. Создайте сводную таблицу (позволяет автоматически делать выборку) для определения объема продаж товаров в двух магазинах «Луч» и «Глобус». Для этого:

1. выберите Вставка – Сводная таблица, в появившемся окне Мастера создания сводных таблиц установите флажки напротив опций В списке или базе данных Microsoft Excel и Сводная таблица.
2. На следующем шаге нужно указать диапазон данных, обычно он выделяется автоматически, если нет – выделите всю таблицу.
3. Далее указать место размещения сводной таблицы – Новый лист, после чего щелкнуть по кнопке Макет и задать макет создаваемой таблицы, перетащив кнопку Наименование в область Строка, Название магазина – в область Столбец, кнопку Сумма проданного – в область Данные, после чего подтвердить все проделанное, нажав Ok, затем Готово. В результате должна появиться Сводная таблица вида:

19. Из Списка полей сводной таблицы перетащите поле Продано в область, находящуюся над сводной таблицей. Отобразите какой-либо элемент, пронаблюдайте за изменениями. То же проделайте с полем Цена, затем отобразите все данные.

20. Продемонстрируйте результаты работы преподавателю.

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

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

2. Какие варианты консолидации существуют?

3. Для чего предназначены сводные таблицы?

4. Как создать сводную таблицу?

5. Как выполнить консолидацию с помощью сводной таблицы?