Практическое занятие № 9
Технологии обработки финансово-экономической и статистической информации
Табличный процессор Microsoft Excel
Тема: Управление данными и их анализ в MS Excel
Цель:
* научиться применять функцию автозаполнения
* научиться создавать сводную таблицу
* научиться проводить консолидацию данных
I. Итоги. Сводная таблица.
1. В Microsoft Excel создать таблицу:
№
Дата
Наименование
Получатель
Кол-во
Цена
Стоимость
1
04.01.05
печенье
Весна
23
25
575
2
04.01.05
вафли
Весна
43
30
1290
3
04.01.05
карамель
Лето
65
32,5
2112,5
4
04.01.05
мармелад
Лето
67
43
2881
5
04.01.05
шоколад
Сезам
45
3,8
171
6
05.01.05
печенье
Сезам
67
25
1675
7
05.01.05
вафли
Сезам
34
35
1190
8
05.01.05
мармелад
Весна
76
48
3648
9
05.01.05
карамель
Весна
45
30
1350
10
07.01.05
шоколад
Лето
8
120
960
11
07.01.05
вафли
Лето
2
6
12
12
07.01.05
печенье
Сезам
33
5,5
181,5
13
08.01.05
мармелад
Лето
98
13
1274
14
08.01.05
карамель
Лето
57
34
1938
15
09.01.05
газ.вода
Лето
89
20
1780
16
09.01.05
мармелад
Лето
45
40
1800
17
09.01.05
печенье
Весна
34
23
782
18
09.01.05
вафли
Весна
78
67
5226
Примечание:
1. Для заполнения столбца № достаточно ввести 1 (в ячейку А2), 2 (в ячейку А3), выделить мышью ячейки А2 и А3, в правом нижнем углу ячейки А3 появится маркер автозаполнения, нужно поставить на него мышь, так чтобы она приняла вид простого черного креста, нажать левую кнопку мыши и тянуть до ячейки А18; в результате остальные номера заполнятся автоматически.
2. Для заполнения столбца Стоимость используйте формулы (стоимость = кол-во * цена).
Переименовать Лист1: Исходная таблица.
2. Создание итогов: На втором листе получить итоги для каждой фирмы по общей стоимости полученных ею товаров. Для этого:
* скопировать данные с первого листа;
* отсортировать данные по фирмам;
* поставить курсор на любую ячейку таблицы, например на ячейку A1;
* Данные/Итоги…;
* в поле При каждом изменении в выбрать Получатель;
* в поле Операция выбрать Сумма;
* в поле Добавить итоги по поставить флаг напротив Стоимость;
* поставить флаг Итоги под данными;
* щелкнуть ОК.
Справа на экране Вы видите структуру таблицы. Пощелкайте мышью по всем знакам «-» , затем по всем знакам «+». Что при этом происходит?
Переименовать Лист2: Итоги.
3. Создание сводной таблицы: На третьем листе получить отчет о стоимости и кол-ве каждого товара для каждой фирмы. Для этого:
* скопировать данные с первого листа на третий;
1шаг
* Поставить курсор в место вставки сводной таблицы – в ячейку А20;
* Данные/Сводная таблица…;
* в окне Мастер сводных таблиц и диаграмм в поле Создать таблицу на основе данных, находящихся выбрать вариант в списке или базе данных Microsoft Excel;
* в поле Вид создаваемого отчета выбрать вариант Сводная таблица;
* щелкнуть по кнопке Далее;
2шаг
* указать диапазон всей таблицы;
* щелкнуть по кнопке Далее;
3шаг
* щелкнуть по кнопке Макет;
* левой кнопкой мыши (ЛКМ) захватить поле Дата и переместить его в область Страница;
* ЛКМ захватить поле Наименование и переместить его в область Строка;
* поле Получатель переместить в область Столбец;
* в область Данные переместить поля Кол-во и Стоимость;
* щелкнуть по кнопке ОК;
* щелкнуть по кнопке Готово.
Переименовать Лист3: Сводная таблица.
4. Работа со сводной таблицей:
* отобразить в сводной таблице закупки, сделанные 5 января;
* отобразить в сводной таблице все закупки;
* отобразить в сводной таблице закупки мармелада и печенья фирмой Весна;
* отобразить в сводной таблице все закупки.
Задание
1. На новом листе создать сводную таблицу, информирующую о средней цене каждого товара для каждой фирмы. Переименовать новый лист: Задание1.
2. На листе Исходная таблица отсортировать данные:
* по наименованию товара;
* по цене товара.
3. На листе Исходная таблица с помощью автофильтра отобразить:
* продажи печенья;
* все закупки, сделанные фирмой Сезам;
* товары, отпущенные до 8 января.
4. На четвертом листе построить диаграмму изменения спроса на мармелад. Переименовать Лист4: Спрос.
II. Консолидация данных
1. В Microsoft Excel на первом листе создайте таблицу:
Примечание:
* чтобы заполнить столбец Сумма используйте формулы (сумма = кол-во · цена); в ячейку D4 введите формулу =B4*C4, с помощью маркера автозаполнения заполните формулами ячейки D5, D6,
* в столбце Сумма в строку Итого (в ячейку D7) вставьте формулу =СУММ(D4:D6),
* ячейки D9 – D13 и D15 – D18 заполните аналогично,
* в столбце Сумма в строку Всего (в ячейку D20) вставьте формулу =СУММ(D7;D13;D18).
2. С помощью справочной службы программы получить ответ на вопрос: «Что такое консолидация данных?».
3. Консолидация данных (без связи с исходными данными).
3.1. Поставьте курсор на ячейку А22 Листа1.
3.2. Выберите в меню пункт Данные / Консолидация.
3.3. Если Список диапазонов непуст, то очистите его:
* щелкните по любому диапазону в Списке диапазонов,
* щелкните по кнопке Удалить.
3.4. Создайте свой список диапазонов.
3.4.1. Задайте первый диапазон:
* переведите курсор в поле Ссылка,
* выделите ячейки А4–D7 на Листе1,
* щелкните по кнопке Добавить.
3.4.2. Аналогично задайте еще два диапазона: А9–D13 на Листе1, А15–D18 на Листе1.
3.5. В поле Функция выберите функцию Сумма.
3.6. В поле Использовать в качестве имен выберите вариант значения левого столбца.
3.7. Отключите флаг Создавать связи с исходными данными.
3.8. Щелкните по кнопке ОК.
3.9. Измените значение в ячейке В4 на Листе1 (кол-во проданных булок городского хлеба в Булочной №1). Изменилось ли значение в ячейке В22 на Листе1 (кол-во проданных булок городского хлеба по всем магазинам)?
4. Консолидация данных (создавать связи с исходными данными).
4.1. Поставьте курсор на ячейку А1 Листа2.
4.2. Выберите в меню пункт Данные / Консолидация.
4.3. Если Список диапазонов непуст, то очистите его:
* щелкните по любому диапазону в Списке диапазонов,
* щелкните по кнопке Удалить.
4.4. Создайте свой список диапазонов.
4.4.1. Задайте первый диапазон:
* переведите курсор в поле Ссылка,
* перейдите на Лист1, выделить ячейки А4–D7 на Листе1,
* щелкните по кнопке Добавить.
4.4.2. Аналогично задайте еще два диапазона: А9–D13 на Листе1, А15–D18 на Листе1.
4.5. В поле Функция выберите функцию Сумма.
4.6. В поле Использовать в качестве имен выберите вариант значения левого столбца.
4.7. Включите флаг Содавать связи с исходными данными.
4.8. Щелкните по кнопке ОК.
4.9. Измените значение в ячейке В4 на Листе1 (кол-во проданных булок городского хлеба в Булочной №1). Изменилось ли значение в ячейке С3 на Листе2 (кол-во проданных булок городского хлеба по всем магазинам)?
5. На Листе3 построить диаграмму реализации хлебобулочных изделий Булочной №2.