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

• 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

• 2. Рассчитайте цену.
• 3. Создайте сводную таблицу по образцу (рис. 3.12). _

Рис. 3.12

• 4. Добавьте вычисляемые поля:
  • • Себестоимость проживания = Проживание*20%;
  • • Себестоимость питания = Питание*5%;
  • • Себестоимость авиаперелета = Перелет*20%;
  • • Суммарная себестоимость = Себестоимость проживания + Себестоимость питания + Себестоимость авиаперелета + Виза;

• • Себестоимость турпакета = Стоимость - Суммарная себестоимость.
• 5. Примените Автоформат.
• 6. Постройте диаграмму по полученной сводной таблице.

Упражнение 3.2

1. Создайте БД по образцу на Листе 1 (табл. 3.2). Значения в поле Стоимость реализованных билетов вычислите по формуле.

Таблица 3.2

Окончание табл. 3.2

• 2. На основе исходной базы данных создайте две сводные таблицы на Листе 2 и Листе 3.
• 3. В первой сводной таблице (рис. 3.13) задайте расположение полей на третьем шаге Мастера сводных таблиц и диаграмм, кликнув по кнопке |Макет]:

• • Менеджер - в область столбцов;
• • Месяц - в область строк;
• • Стоимость реализованных билетов - в область данных. Выберите функцию Сумма.

Рис. 3.13

• 3. Структуру второй сводной таблицы (рис. 3.14) сформируйте вручную, используя список полей на рабочем листе:
  • • Менеджер - в область страницы;
  • • Вид экскурсии - в поле строк;
  • • Месяц - в поле столбцов;
  • • Стоимость реализованных билетов - в область данных. Укажите функцию Сумма.

Рис. 3.14

4. В исходной БД измените цену на обзорную экскурсию по Каиру в марте на 40 у. е. Обновите данные во всех сводных таблицах.

• 5. Измените формат чисел (денежный, $Английский (США)) в первой сводной таблице (см. рис. 3.13).
• 6. Во второй сводной таблице (см. рис. 3.14) измените операцию для вычисления значений по полю Стоимость реализованных билетов, поменяйте сумму на максимальное значение.
• 7. Примените один из видов Автоформата к первой сводной таблице (рис. 3.15).

Рис. 3.15

8. В первой сводной таблице создайте новое вычисляемое поле НДС, значения в котором будут вычислены по формуле:

Стоимость реализованных билстов*3%.

• 9. В первой сводной таблице (см. рис. 3.13) также создайте вычисляемое поле Налог с продаж (Стоимость реализованных билетов*20%).
• 10. В первой сводной таблице отсортируйте в обратном алфавитном порядке фамилии менеджеров.
• 11. Реорганизуйте вторую сводную таблицу, расположив имена полей следующим образом:
  • • Месяц - в область страниц;
  • • Менеджер - в область столбцов;
  • • Вид экскурсии - в область строк;
  • • Количество туристов - в область данных (рис. 3.16).
• 12. Постройте сводную диаграмму (плоская гистограмма) по реорганизованной сводной таблице (рис. 3.17).

Рис. 3.16

Рис. 3.17

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

Рис. 3.18

Упражнение 3.3

Создайте БД по образцу (табл. 3.3). Значения в поле Фактическая стоимость вычислите по формуле. Заполните столбец Оплаченная сумма, у. е. из расчета полной фактической стоимости.

Таблица 3.3

Окончание табл. 3.3

• 1. На основе исходной БД создайте сводную таблицу (рис. 3.19), задав расположение полей на третьем шаге Мастера (кнопка |Макет]) следующим образом. Переместите мышью поля:
  • • Покупатель - в область столбцов;
  • • Название тура - в область строк;
  • • Стоимость тура, у. е. - в данные.

Рис. 3.19

• 2. Создайте вторую сводную таблицу, задав расположение полей прямо на рабочем листе, используя список полей. Переместите мышью поля:
  • • Дата оплаты - в область столбцов;
  • • № пр/о - в область строк;
  • • Оплаченная сумма - в данные.
• 3. Реорганизуйте вторую сводную таблицу и приведите ее к виду (рис. 3.20).

Рис. 3.20

• 4. Измените стоимость тура (по каталогу) «Вся Испания» на 700 у. с. Обновите данные в первой сводной таблице.
• 5. Примените к первой сводной таблице Автоформат (рис. 3.21).

6. Создайте во второй сводной таблице (см. рис. 3.20) новое вычисляемое поле Комиссия, значения в котором будут вычислены по формуле

Стоимость тура, у. е.*11%.

7. Создайте во второй сводной таблице новое вычисляемое поле Себестоимость, значения в котором будут вычислены по формуле

Стоимость тура, у. е. - Комиссия (рис. 3.22).

8. Отформатируйте числа в поле Стоимость тура, у. е., Комиссия и Себестоимость, применив Денежный вид формата (€ Евро(€123)).

Рис. 3.22

• 9. На основе второй сводной таблицы (см. рис. 3.20) постройте сводную диаграмму (рис. 3.23).
• 10. Расположите сводную диаграмму на том же листе, где находится сводная таблица.
• 11. Измените расположение полей на сводной диаграмме. Проанализируйте изменения, произведенные в сводной таблице.
• 12. Измените тип плоской гистограммы на объемную круговую.

Рис. 3.23