Задания и порядок их выполнения. Задание 1. Создайте сводную таблицу

Задание 1. Создайте сводную таблицу.

Порядок выполнения:

1. Запустите табличный процессор Excel: Пуск ñ Программы ñ Microsoft Office ñ Microsoft Office Excel 2010.

2. Введите данные и оформите таблицу по образцу, представленному на рис. 10.20.

3. Выделите блок ячеек А2:F22 и выполните команду контекстного меню Формат ячеек (). Во вкладке «Выравнивание» установите параметры: «по горизонтали» – по центру; «по вертикали» – по центру, а также флажок в строке «переносить по словам».

4. Нажмите кнопку Все границы () на вкладке Главная (группа Шрифт). Ячейки таблицы выделятся обрамлением из одной линии.

5. В случае необходимости, скорректируйте данные и отформатируйте таблицу.

6. Сохраните рабочую книгу в личной папке под именем Сводная таблица.

7. Создайте сводную таблицу. Для этого:

7.1. На вкладке Вставка в группе Таблицы щёлкните по кнопке Сводная таблица (). Появится окно меню «Создание сводной таблицы» (рис. 10.21).

7.2. В поле «Таблица или диапазон» окна «Создание сводной таблицы» укажите диапазон данных ($A$2:$F$22).

Рис. 10.20. Начальный вид таблицы

Рис. 10.21. Окно «Создание сводной таблицы»

7.3. Установите, если он не установлен, флажок для параметра «Укажите, куда следует поместить отчет сводной таблицы:» – «На новый лист» и нажмите кнопку ОК. На верхней левой части нового листа отобразится (рис. 10.22) макет для генерации сводной таблицы, а в правой части – «Список полей сводной таблицы», содержащий кнопки с названиями столбцов исходной таблицы данных.

Рис. 10.22. Лист для создания сводной таблицы

7.4. Выведите сведения о видах поступивших и рассмотренных судами делам. Для чего перетащите мышью кнопки:

«Наименование суда» – в область «Фильтр отчёта»;

«Статья» – в область «Названия строк»;

«Вид» – в область «Названия столбцов»;

«Поступило», «Рассмотрено» и «Кол-во заседаний» – в область «å Значения»;

появившееся в области «Названия столбцов» поле «å Значения» перетащите в область «Названия строк».

Сводная таблица примет вид, показанный на рис. 10.23.

Рис. 10.23. Окончательный вид сводной таблицы

7.5. Обратите внимание: в столбце «Названия строк» статьи представлены в порядке возрастания их номера; в столбце «Общий итог» по каждой статье приведено общее количество поступивших и рассмотренных дел, а также общее количество судебных заседаний; в нижних трёх строках приведены данные о поступивших, рассмотренных делах и количестве заседаний по видам, а в последнем столбце - общий итог.

8. Рассмотрим возможности навигации по сводной таблице. Для этого:

8.1. В ячейке В1 щёлкните мышью кнопку раскрытия списка «Наименование суда», выберите Коминтерновский суд и нажмите ОК. В сводной таблице выведутся данные только по этому суду.

8.2. Вернитесь к первоначальному виду сводной таблицы, щёлкнув «(Все)» в списке «Наименование суда» и нажав ОК.

8.3. В ячейке А4 щёлкните мышью кнопку раскрытия списка «Названия строк» («Статья»), уберите флажок с пункта «77» и щёлкните по кнопке ОК. В сводной таблице выведутся данные только по остальным статьям.

8.4. Вернитесь к первоначальному виду сводной таблицы, установив в списке «Названия строк» по пункту «(Выделить все)» и щёлкнув по кнопке ОК.

8.5. В ячейке В3 щёлкните мышью кнопку раскрытия списка «Названия столбцов» и уберите флажок с пункта «ТК». В сводной таблице выведутся данные только по виду «УК».

8.6. Щелчком правой кнопки мыши по ячейке А6 вызовите контекстное меню и выберите пункт «Удалить Сумма по полю Поступило». В сводной таблице выведутся данные только по полям «Рассмотрено» и «Кол-во заседаний».

8.6. Вернитесь к первоначальному виду сводной таблицы, щёлкнув по кнопке Отменить () на панели быстрого доступа.

Задание 2. Рассчитайте среднее количество судебных заседаний, затраченных на рассмотрение всех исков и по каждой статье в отдельности. Порядок выполнения:

1. Объедините ячейки Е3, Е4 (после выделения нажмите кнопку Объединить и поместить в центре () на вкладке Главная в группе Выравнивание) и введите текст «Среднее кол-во заседаний». Отформатируйте представление текста: выравнивание «по вертикали: по высоте», «переносить по словам», увеличьте ширину столбца.

2. В ячейку Е5 методом указания адресов ячеек (щелчок курсором по ячейке) введите формулу

=D8/D7,

т.е. «Среднее кол-во заседаний = Кол-во заседаний / Рассмотрено» по статье 77. Объедините ячейки E5:E8 (после выделения нажмите кнопку Объединить и поместить в центре ()).

3. Повторите действия п. 2 для остальных статей (81, 285, 290 и 291).

4. В ячейку E25 введите формулу

=D27/D26.

Объедините ячейки E25:E27.

5. Выделите блок ячеек E5:E27 и исполните команду Формат ячеек (): во вкладке «Число» установите числовой формат, число десятичных знаков равно 1, во окне «по вертикали:» вкладки «Выравнивание» - по верхнему краю и щёлкните по кнопке ОК.

6. Установите обрамление для полученных данных. Для чего выделите блок ячеек Е3:Е27 и нажмите кнопку Все границы ().

Задание 3. Спланируйте количество судебных заседаний, необходимое для рассмотрения всех оставшихся исков и по каждой статье в отдельности. Просмотрите модифицированную сводную таблицу. Порядок выполнения:

1. Объедините ячейки F3, F4 и введите текст «Планируемое кол-во заседаний». Отформатируйте представление текста: выравнивание «по вертикали: по высоте», «переносить по словам», увеличьте ширину столбца.

2. В ячейку F5 введите формулу

=(D6-D7*)*E5,

т.е. по статье 77 «Планируемое кол-во заседаний = (Поступило – Рассмотрено)*Среднее количество заседаний») и объедините ячейки F5:F8.

3. Повторите действия п. 2 для остальных статей: 81, 285, 290 и 291.

4. В ячейку F25 введите формулу

=(D25-D26)*E25.

Объедините ячейки F25:F27.

5. Выделите блок ячеек F5:F27 и исполните команду Формат ячеек (): во вкладке «Число» установите числовой формат, число десятичных знаков равно 0, во окне «по вертикали:» вкладки «Выравнивание» - по верхнему краю и щёлкните по кнопке ОК.

6. Установите обрамление для полученных данных. Для чего выделите блок ячеек F3:F27 и нажмите кнопку Все границы ().

7. В ячейке В1 щёлкните мышью кнопку раскрытия списка «Наименование суда» и выберите Советский суд. В сводной таблице выведутся данные только по этому суду.

8. Вернитесь к первоначальному виду сводной таблицы, щёлкнув «(Все)» и кнопку ОК в списке «Наименование суда».

Задание 4. Создайте сводную диаграмму на основе сводной таблицы и проанализируйте возможности манипулирования данными в сводной диаграмме. Порядок выполнения:

1. Выделите диапазон ячеек А1:D27 созданной Вами сводной таблицы.

2. На вкладке Вставка раскройте пиктограмму Гистограмма и выберите тип Гистограмма с группировкой (рис. 10.24). Диаграмма будет создана на активном листе.

Замечание: При создании сводной диаграммы на основе сводной таблицы не могут быть использованы типы диаграммы: точечная, пузырьковая и биржевая.

Рис. 10.24. Фрагмент вкладки «Вставка»

3. Обратите внимание, что на полях диаграммы отображаются кнопки сводной таблицы: в верхней части – «Наименование суда»; в правой части – «Вид»; в нижней части – «Статья».

4. Заметим, что редактировать сводную диаграмму можно так же, как и обычную. В частности, добавим название диаграммы: в группе Макеты диаграмм выберем Макет1 и в строку «Название диаграммы» введём «Данные о рассмотрении дел». Увеличим шрифт подписей по осям диаграммы до 12 пт и её размер. В результате получим примерный вид диаграммы, представленный на рис. 10.25.

5. Щёлкните курсором по кнопке списка «Наименование суда»; в раскрывшемся списке – по пункта «Ленинский» и кнопке ОК. В диаграмме останутся данные только по Ленинскому суду.

Рис. 10.25. Примерный вид сводной диаграммы

6. Щёлкните курсором по кнопке списка «Вид»; в раскрывшемся списке – по флажку пункта «ТК» и кнопке ОК. В диаграмме исчезнут данные по этому виду исков.

7. Щёлкните курсором по кнопке списка «Статья»; в раскрывшемся списке – по флажку пункта «290» и кнопке ОК. На диаграмме будут отображены данные только по оставшимся статьям. (285, 291).

8. Восстановите сводную диаграмму, полученную на шаге 4.

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

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

1. Содержание понятия «сводная таблица».

2. Порядок создания сводной таблицы.

3. Возможности организации просмотра содержимого сводной таблицы.

4. Порядок проведения расчётов по сводным таблицам.

5. Особенности построения сводных диаграмм.