Изменение формата
- 1. Выделить любую ячейку внутри области данных сводной таблицы.
- 2. На панели инструментов Сводной таблицы или через контекстное меню выбрать команду Параметры поля.
- 3. В диалоговом окне Вычисление поля сводной таблицы нажать на кнопку |Формат|, выбрать нужный формат (рис. 3.6).
Обновление данных
В случае изменения данных в исходной таблице в сводной таблице необходимо применить команду Обновить данные.
Данная команда имеется на панели инструментов Сводные таблицы (рис. 3. 7) или в контекстном меню (рис. 3.8).

Рис. 3.6

Рис. 3.7
Формат ячеек... Сводная диаграмма Мастер сводных таблиц |
|
? Обновить данные |
|
а |
Скрыть |
Выделить Группа и структура Порядок |
|
Параметры поля... Параметры таблицы... Показать панель сводной таблицы Показать список полей |
Рис. 3.8
Создание вычисляемых полей в сводных таблицах
- 1. Выделить любую ячейку внутри области данных сводной таблицы.
- 2. На панели инструментов Сводные таблицы выбрать команду Формулы - Вычисляемое поле... (рис. 3.9).

3. В диалоговом окне Вставка вычисляемого поля добавить Имя поля и ввести формулу для вычислений. В качестве переменных используются имена полей БД, представленные в окне Поля (рис. 3.10). Имена полей в формулу добавляются двойным кликом левой кнопки мыши или кнопкой [Добавить поле|.
Создание сводных диаграмм
Создание сводной диаграммы на основе БД
- 1. Выделить любую ячейку внутри области данных сводной таблицы.
- 2. Выбрать меню Данные - Сводная таблица.
- 3. В диалоговом окне Мастера выбрать исходные данные и вид отчета - Сводная диаграмма - кнопка [Далес|.

Рис. 3.10
- 4. Указать диапазон исходных данных - кнопка |Готово|.
- 5. С помощью мыши из списка полей сводной диаграммы перетащить в полученный макет соответствующие элементы.
Создание сводной диаграммы на основе сводной таблицы
- 1. Выделить любую ячейку внутри области данных сводной таблицы.
- 2. На панели инструментов сводной таблицы (рис. 3.11) или через контекстное меню выбрать команду Сводная диаграмма.

Рис. 3.11
3. С помощью мыши из списка полей сводной диаграммы перетащить в полученный макет соответствующие элементы.
Сводная диаграмма связана со сводной таблицей. При изменении данных в сводной таблице изменяется сводная диаграмма.
Упражнения
Упражнение 3.1
1. В программе Excel создайте таблицу (табл. 3.1).
Таблица 3.1
Страна |
Вылет |
Перелет |
Виза |
Проживание |
Питание |
Цена |
Количество туров |
Франция |
Понедельник |
500 |
50 |
350 |
67 |
2 |
|
Италия |
Среда |
567 |
50 |
350 |
70 |
1 |
|
Г ермания |
Суббота |
670 |
40 |
367 |
85 |
4 |
|
Испания |
Пятница |
350 |
45 |
245 |
60 |
5 |
|
Швеция |
Четверг |
420 |
35 |
400 |
67 |
8 |
|
Дания |
Среда |
500 |
50 |
350 |
67 |
2 |
- 2. Рассчитайте цену.
- 3. Создайте сводную таблицу по образцу (рис. 3.12). _
Сумма по полю |
Цена |
Вылет ?! |
||
Страна |
ж |
Понедельник Среда |
Четверг Пятница Суббота |
Общий итог |
Германия |
4648 |
4648 |
||
Дания |
1934 |
1934 |
||
Испания |
3500 |
3500 |
||
Италия |
1037 |
1037 |
||
Франция |
1934 |
1934 |
||
Швеция |
7376 |
7376 |
||
Общий итог |
1934 |
2971 7376 3500 4648 |
20429 |
Рис. 3.12
- 4. Добавьте вычисляемые поля
- • Себестоимость проживания = Проживание*20%;
- • Себестоимость питания = Питание*5%;
- • Себестоимость авиаперелета = Перелет*20%;
- • Суммарная себестоимость = Себестоимость проживания + Себестоимость питания + Себестоимость авиаперелета + Виза;
- • Себестоимость турпакета = Стоимость - Суммарная себестоимость.
- 5. Примените Автоформат.
- 6. Постройте диаграмму по полученной сводной таблице.
Упражнение 3.2
1. Создайте БД по образцу на Листе 1 (табл. 3.2). Значения в поле Стоимость реализованных билетов вычислите по формуле.
Таблица 3.2
Месяц |
Вид экскурсии |
Количество туристов, купивших билеты на экскурсию |
Цена одного билета, У- е. |
Стоимость реализованных билетов |
Менеджер |
Январь |
Джип-сафари |
15 |
10 |
Васильев |
|
Январь |
Пирамиды |
30 |
20 |
Петренко |
|
Январь |
Сафари на верблюдах |
42 |
25 |
Миронова |
|
Январь |
Путешествие на подводной лодке «Синдбад» |
25 |
15 |
Афанасьев |
|
Январь |
Обзорная по Каиру |
30 |
20 |
Васильев |
|
Январь |
Мото-сафари |
23 |
30 |
Афанасьев |
|
Январь |
Круиз по Нилу |
40 |
20 |
Миронова |
|
Январь |
Экскурсия в Луксор |
28 |
25 |
Петренко |
|
Февраль |
Джип-сафари |
32 |
10 |
Петренко |
|
Февраль |
Пирамиды |
15 |
20 |
Васильев |
|
Февраль |
Сафари на верблюдах |
45 |
25 |
Никонова |
Окончание табл. 3.2
Месяц |
Вид экскурсии |
Количество туристов, купивших билеты на экскурсию |
Цена одного билета, у. с. |
Стоимость реализованных билетов |
Менеджер |
Февраль |
Путешествие на подводной лодке «Синдбад» |
18 |
15 |
Никонова |
|
Февраль |
Обзорная по Каиру |
51 |
20 |
Афанасьев |
|
Февраль |
Мото-сафари |
18 |
30 |
Петренко |
|
Февраль |
Круиз по Нилу |
15 |
20 |
Васильев |
|
Февраль |
Экскурсия в Луксор |
10 |
25 |
Васильев |
|
Март |
Джип-сафари |
17 |
10 |
Васильев |
|
Март |
Пирамиды |
20 |
20 |
Петренко |
|
Март |
Сафари на верблюдах |
18 |
25 |
Афанасьев |
|
Март |
Путешествие на подводной лодке «Синдбад» |
10 |
15 |
Миронова |
|
Март |
Обзорная по Каиру |
30 |
20 |
Миронова |
|
Март |
Мото-сафари |
12 |
30 |
Петренко |
|
Март |
Экскурсия в Луксор |
47 |
25 |
Миронова |
|
Март |
Круиз по Нилу |
42 |
20 |
Никонова |
- 2. На основе исходной базы данных создайте две сводные таблицы на Листе 2 и Листе 3.
- 3. В первой сводной таблице (рис. 3.13) задайте расположение полей на третьем шаге Мастера сводных таблиц и диаграмм, кликнув по кнопке |Макет]:
- • Менеджер - в область столбцов;
- • Месяц - в область строк;
- • Стоимость реализованных билетов - в область данных. Выберите функцию Сумма.
А |
В |
С |
о |
Е |
F |
G |
|
1 |
|||||||
2 |
|||||||
3 |
Сумма по полю стоим эль реализованных билетое |
менеджер »| |
|||||
4 |
месяц ? |
Афанасьев |
Вас^г&ев |
Миронова |
Никсмва |
Петренкс Обшуй итог |
|
5 |
январь |
1085 |
600 |
1850 |
7001 |
4215 |
|
6 |
февраль |
1020 |
850 |
1395 |
860 |
4125 |
|
? |
март |
450 |
170 |
1925 |
840 |
760 |
4145 |
8 |
Общий итог |
2535 |
1620 |
3775 |
2235 |
2320 |
12485 |
и < |
? »|Лкт! дПлстг/Л-ктЗ / |
Н |
1 |
Рис. 3.13
- 3. Структуру второй сводной таблицы (рис. 3.14) сформируйте вручную, используя список полей на рабочем листе:
- • Менеджер - в область страницы;
- • Вид экскурсии - в поле строк;
- • Месяц - в поле столбцов;
- • Стоимость реализованных билетов - в область данных. Укажите функцию Сумма.
А |
В |
с |
D |
Е |
||
1 |
менеджер |
(Все) - |
||||
2 | ||||||
3 |
Сумма по полю стоимость реализованных билетов |
месяц ? | ||||
4 |
вид экскурсии ? |
январь |
февраль |
март |
Общий итог |
|
5 |
джип - сафари |
320 |
170 |
490 |
||
6 |
круиз по Нилу |
800 |
300 |
840 |
1940 |
|
7 |
мото - сафари |
690 |
540 |
360 |
1590 |
|
8 |
обзорная по Каиру |
600 |
1020 |
600 |
2220 |
|
9 |
пирамиды |
300 |
400 |
700 |
||
10 |
путешествие на подводной лодке"Синдбад" |
375 |
270 |
150 |
795 |
|
11 |
сафари на верблюдах |
1050 |
1125 |
450 |
2625 |
|
12 |
экскурсия в Луксор |
700 |
250 |
1175 |
2125 |
|
13 |
Общий итог |
4215 |
4125 |
4145 |
12485 |
|
14 |
||||||
15 |
kJ. |
|||||
к < |
? Н Лист! / J?ict2 ЛистЗ/ |
Рис. 3.14
4. В исходной БД измените цену на обзорную экскурсию по Каиру в марте на 40 у. е. Обновите данные во всех сводных таблицах.
- 5. Измените формат чисел (денежный, $Английский (США)) в первой сводной таблице (см. рис. 3.13).
- 6. Во второй сводной таблице (см. рис. 3.14) измените операцию для вычисления значений по полю Стоимость реализованных билетов, поменяйте сумму на максимальное значение.
- 7. Примените один из видов Автоформата к первой сводной таблице (рис. 3.15).
В |
с |
D |
Е I |
F 1 |
||
91 | ||||||
92 |
м е н е д жер______ |
Н месяц Н |
стоимость реализованных билетов НДС |
Налог с продаж| |
||
93 {Афанасьев |
?I |
|||||
94 |
январь |
$1 065 |
$32 |
$213 |
||
95 |
февраль |
$1 020 |
$31 |
$204 |
||
96 |
март |
$450 |
$14 |
$90 |
||
97 |
Афанасьев Итог |
$2 535 |
$76 |
$507 |
||
98 |
||||||
99 |
Васильев |
|||||
100 |
январь |
$750 |
$23 |
$150 |
||
101 |
февраль |
$850 |
$26 |
$170 |
||
102 |
март |
$170 |
$5 |
$34 |
||
103 |
Васильев Итог |
$1 770 |
$53 |
$354 |
||
’34 |
||||||
105 |
Миронова |
|||||
106 |
январь |
$1 850 |
$56 |
$370 |
||
107 |
март |
$1 925 |
$58 |
$385 |
||
10В |
Миронова Итог |
$3 775 $113 |
$755 |
|||
109 |
||||||
110 |
Общий итог |
$8 080 $242 |
$1 616 |
Рис. 3.15
8. В первой сводной таблице создайте новое вычисляемое поле НДС, значения в котором будут вычислены по формуле:
Стоимость реализованных билстов*3%.
- 9. В первой сводной таблице (см. рис. 3.13) также создайте вычисляемое поле Налог с продаж (Стоимость реализованных билетов*20%).
- 10. В первой сводной таблице отсортируйте в обратном алфавитном порядке фамилии менеджеров.
- 11. Реорганизуйте вторую сводную таблицу, расположив имена полей следующим образом:
- • Месяц - в область страниц;
- • Менеджер - в область столбцов;
- • Вид экскурсии - в область строк;
- • Количество туристов - в область данных (рис. 3.16).
- 12. Постройте сводную диаграмму (плоская гистограмма) по реорганизованной сводной таблице (рис. 3.17).
В |
С |
”о~ |
Е |
F~~Г |
6 |
н I |
|
70 |
|||||||
71 |
|||||||
72 |
Таблица 2 |
||||||
73 |
месяц |
(Все) | |||||
75 |
Сумма по полю кол во туристов, купивших билеты на экскурсию |
менедж'”' |
_ |
||||
76 |
вид экскурсии |
Афанасьев |
Васильев |
Миронова Никонова Петренко |
Общий итог |
||
77 |
джип • сафари |
32 |
32 |
64 |
|||
78 |
круиз по Нилу |
15 |
40 |
42 |
97 |
||
79 |
мото ? сафари |
23 |
30 |
53 |
|||
ЕО |
обзорная по Каиру |
51 |
100 |
30 |
181 |
||
81 |
пирамиды |
15 |
50 |
65 |
|||
82 |
путешествие наподводной лодке"Синдбад‘ |
25 |
10 |
18 |
53 |
||
83 |
сафари на верблюдах |
18 |
42 |
45 |
105 |
||
84 |
экскурсия в Луксор |
10 |
47 |
28 |
85 |
||
65 |
Общий итог |
117 |
172 |
169 |
105 |
140 |
703 |
Рис. 3.16

Рис. 3.17
- 13. Разместите диаграмму рядом со сводной таблицей. Измените расположение полей на построенной сводной диаграмме.

Рис. 3.18
Упражнение 3.3
Создайте БД по образцу (табл. 3.3). Значения в поле Фактическая стоимость вычислите по формуле. Заполните столбец Оплаченная сумма, у. е. из расчета полной фактической стоимости.
Таблица 3.3
Покупатель |
Название тура |
Стоимость тура, У- с. |
Скидки постоянным клиентам, % |
Фактическая стоимость, У- е. |
Оплаченная сумма. У- е. |
№ пр/о |
Дата оплаты |
Николаев |
«Вся Испания» |
600 |
123 |
10.03.07 |
|||
Васильев |
«Античная Греция» |
480 |
5 |
124 |
10.03.07 |
||
Дроздова |
«Отдых в Турции» |
430 |
205 |
15.03.07 |
|||
Дёмин |
«Париж экскурсионный» |
700 |
7 |
208 |
16.03.07 |
Окончание табл. 3.3
Покупатель |
Название тура |
Стоимость тура, У- с. |
Скидки постоянным клиентам. % |
Фактическая стоимость, У- е. |
Оплаченная сумма, У- е. |
№ пр/о |
Дата оплаты |
Первенцев |
«Ирландия -страна кельтов» |
720 |
209 |
16.03.07 |
|||
Мезенцев |
«Вся Испания» |
600 |
5 |
98 |
05.03.07 |
||
Пахомов |
«Австрийские Альпы» |
680 |
102 |
07.03.07 |
|||
Савельева |
Хургада |
400 |
5 |
210 |
18.03.07 |
||
Румянцев |
«Париж экскурсионный» |
650 |
212 |
20.03.07 |
|||
Леонов |
Хургада |
450 |
215 |
25.03.07 |
- 1. На основе исходной БД создайте сводную таблицу (рис. 3.19), задав расположение полей на третьем шаге Мастера (кнопка |Макет]) следующим образом. Переместите мышью поля:
- • Покупатель - в область столбцов;
- • Название тура - в область строк;
- • Стоимость тура, у. е. - в данные.
24 |
Сумма по полю Стоимость тура (по каталогу), у.е. |
Покупатель - |
||||
25 |
Название тура ? |
Васильев |
Демин |
Леонов |
Николаев |
Общий итог |
26 |
‘Античная Греция" |
480 |
480 |
|||
27 |
‘Вся Испания’ |
600 |
600 |
|||
28 |
‘Париж экскурсионный" |
700 |
700 |
|||
29 |
Хургада |
450 |
450 |
|||
30 |
Общий итог |
480 |
700 |
450 |
600 |
2230 |
Рис. 3.19
- 2. Создайте вторую сводную таблицу, задав расположение полей прямо на рабочем листе, используя список полей. Переместите мышью поля:
- • Дата оплаты - в область столбцов;
- • № пр/о - в область строк;
- • Оплаченная сумма - в данные.
- 3. Реорганизуйте вторую сводную таблицу и приведите ее к виду (рис. 3.20).
— — |
||||
57 Название тура |(Все) ? |
||||
58 |
Сумма по полю 59 Оплаченная сумма,у.е |
№пр/о ? | |||||||
60 Покупатель ? |
98 |
102 |
123 |
124 205 |
208 |
209 |
210 212 215 |
Общий итог |
61]Васильев |
456 |
456 |
||||||
62 Демин |
665 |
665 |
||||||
63 Дроздова |
430 |
430 |
||||||
64 Леонов |
451. |
450 |
||||||
65 Мезенцев |
522,7 |
522,7 |
||||||
66 Николаев |
600 |
600 |
||||||
67|Пахомов |
680 |
680 |
||||||
68 Первенцев |
720 |
720 |
||||||
69 Румянцев |
650 |
650 |
||||||
1 70 Савельева |
475 |
475 |
||||||
71 [Общий итог_____________ |
522,7 |
680 |
600 |
456 430 |
665 |
720 |
475 650 450 |
5648,7 |
Рис. 3.20
- 4. Измените стоимость тура (по каталогу) «Вся Испания» на 700 у. с. Обновите данные в первой сводной таблице.
- 5. Примените к первой сводной таблице Автоформат (рис. 3.21).
100 |
Покупатель____ |
Н Название тура |
н Стоимость тура (по каталогу), у.е. I |
101 |
Пахомов |
И |
|
102 |
"Австрийские Альпы" |
680 |
|
103 |
Пахомов Итог |
680 |
|
|
Первенцев |
"Ирландия - страна кельтов" 720 |
|
107 |
Первенцев Итог |
720 |
|
Hi::
|
Румянцев |
"Париж экскурсионный" |
650 |
111 |
Румянцев Итог |
650 |
|
I 12
|
Савельева |
Хургада |
400 |
115 |
Савельева Итог |
400 |
|
I IG 117 |
Общий итог |
2450 |
6. Создайте во второй сводной таблице (см. рис. 3.20) новое вычисляемое поле Комиссия, значения в котором будут вычислены по формуле
Стоимость тура, у. е.*11%.
7. Создайте во второй сводной таблице новое вычисляемое поле Себестоимость, значения в котором будут вычислены по формуле
Стоимость тура, у. е. - Комиссия (рис. 3.22).
8. Отформатируйте числа в поле Стоимость тура, у. е., Комиссия и Себестоимость, применив Денежный вид формата (€ Евро(€123)).
D I Е |
F |
G |
н |
|
Покупатель Название тура |
Стоимость тура (по каталогу), |
Комиссия |
Себестоимость |
|
93 |
D П |
у.е. |
(по каталогу) |
|
94 |
Пахомов |
|||
95 |
"Австрийские Альпы” |
€680 |
€75 |
€605 |
96 |
Пахомов Итог |
€680 |
€75 |
€605 |
97 |
||||
98 |
Первенцев |
|||
99 |
"Ирландия - страна кельто^ |
€79 |
€641 |
|
1С0 |
Первенцев Итог |
€720 |
€79 |
€641 |
101 |
||||
102 |
Румянцев |
|||
103 |
"Париж экскурсионный" |
€650 |
€72 |
€579 |
104 |
Румянцев Итог |
€650 |
€72 |
€579 |
105 |
||||
106 |
Савельева |
|||
107 |
Хургада |
€400 |
€44 |
€356 |
108 |
Савельева Итог |
€400 |
€44 |
€356 |
109 |
||||
I |
Общий итог |
______________________12 450_ |
____€270 |
________€2181 |
Рис. 3.22
- 9. На основе второй сводной таблицы (см. рис. 3.20) постройте сводную диаграмму (рис. 3.23).

Рис. 3.23