Изменение формата

  • 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. Разместите диаграмму рядом со сводной таблицей. Измените расположение полей на построенной сводной диаграмме.
  • 14. Измените данные в исходной БД в поле Количество туристов, купивших билеты на обзорную экскурсию по Каиру в январе на 100.
  • 15. Обновите данные во второй (см. рис. 3.14) сводной таблице и отследите изменения па диаграмме.
  • 16. Измените тип диаграммы на плоскую линейчатую (рис. 3.18), затем - на объемную круговую.

Рис. 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

  • 104
  • 105
  • 106

Первенцев

"Ирландия - страна кельтов" 720

107

Первенцев Итог

720

Hi::

  • 109
  • 110

Румянцев

"Париж экскурсионный"

650

111

Румянцев Итог

650

I 12

  • 113
  • 114

Савельева

Хургада

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).
  • 10. Расположите сводную диаграмму на том же листе, где находится сводная таблица.
  • 11. Измените расположение полей на сводной диаграмме. Проанализируйте изменения, произведенные в сводной таблице.
  • 12. Измените тип плоской гистограммы на объемную круговую.

Рис. 3.23

 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ ОРИГИНАЛ   След >