Создание макета сводной таблицы

После выполнения операций с помощью Мастер сводных таблиц и щелчка на кнопке Готово в последнем диалоговом окне в рабочем листе будет отображен пустой макет таблицы, панель инструментов Сводные таблицы и диалоговое окно Список полей сводной таблицы (рис. 3.41).

Рис.3.41 Диалоговое окно Список полей сводной таблицы

 

Формирование макета выполняется путем перетаскивания кнопок с названиями столбцов исходного списка из диалогового окна Список полей сводной таблицы в области макета с надписью Перетащите сюда поля столбцов (строк). В область с надписью Перетащите сюда элементы данных следует перетащить кнопки с названиями по лей, над данными которых должны быть выполнены вычислительные операции.

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

Решение

Установим курсор в область исходного списка и включим инструмент Мастер сводных таблиц.

В окне Мастер сводных таблиц (шаг 1) установим переключатель В списке или базе данных Microsoft Office Excel и переключатель Сводная таблица.

В поле Диапазон окна Мастер сводных таблиц (шаг 2) укажем адрес диапазона, в котором расположен исходный список.

В окне Мастер сводных таблиц (шаг 3) установим переключатель Новый рабочий лист.

В появившемся на рабочем листе макете выполним операции:

– перетащим кнопку Фамилия клиента в область с надписью Перетащите сюда названия строк;

– в область с надписью Перетащите сюда элементы данных перетащим кнопки с названиями Приход и Расход. После выполнения этих операций на рабочем листе будет сформирована таблица, фрагмент которой приведен на рисунке. В поле Итог этой таблицы помещены числа, соответствующие количеству операций расхода и прихода, выполненных по счетам каждого клиента;

Рис. 3.42

 

– поместим курсор в любое поле таблицы с надписью Количество по полю Приход и включим контекстное меню. В списке меню выберем Параметры поля;

– в открывшемся окне диалога Вычисление поля сводной таблицы выберем из списка Операция элемент Сумма, повторим эту операцию для поля таблицы с надписью Количество по полю Расход (рис. 3.43).

Рис. 3.43

После выполнения всех операций будет получена результирующая таблица (рис. 3.44.)

Рис. 3.44

 

Изменение сводной таблицы

Созданную сводную таблицу можно изменять. Например, чтобы переместить поле с оси строк на ось столбцов, нужно в диалоговом окне Список полей сводной таблицы в списке выбрать Область столбцов, выделить кнопку с нужным названием и щелкнуть на кнопке Поместить в. Столбцы и строки в сводной таблице поменяются местами (операция транспонирования).

Элементы, которые не нужны для анализа, можно скрывать. Эта операция выполняется путем раскрытия стрелки в нужном поле и последующим выбором только тех элементов, данные которых подлежат анализу.

 

3.2.задания для самостоятельной работы

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

	Операции банка по счетам клиентов
	№ операции	№ счета	Фамилия клиентов	Дата операции	Приход	Расход
			Петров	03.01.09
			Иванов	04.02.09
			Сидоров	05.03.09
			Смирнова	06.04.09
			Карапузов	07.05.09
			Новиков	08.06.09
			Коршиков	09.07.09
			Егорова	10.08.09
			Попов	11.09.09
			Михалева	12.10.09

 

Задание 2. Заполнить таблицу 7.1, занеся недостающие данные, рассчитав процент удержания по следующему правилу: при количестве иждивенцев более трех – 0%, при трех – 5%, при двух – 10%, при одном – 12%, если нет – 14%. Расчет оформить с помощью вложенных функций ЕСЛИ.

 

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

 

Таблица 3.2. Ведомость

Ф.И.О.	Отдел	Количество иждивенцев	Всего начислено, руб.	% удержания	Всего удержано, руб.	Сумма к выдаче, руб.
1. Петухова К.И.
2. Безенчук П.Ф.
3. Воробьянинов И.М.
4. Востриков Ф.О.
5. Коробейников В.А.
6. Грицацуева В. С.
7. Гаврилин З.С.
8. Треухов Т.И.
9. Изнуренков А.В.
10. Щукина Э.Е.

Задание 4. Построить новую сводную таблицу, разбив персонал по отделам и по категории Количество иждивенцев, и определить итоговую сумму к выдаче внутри каждого отдела. При выполнении этого задания рекомендуется использовать область Страница для размещения в нем поля Отдел.

 

Задание 5. Заполните таблицу 3.2, создав ее на листе Январь.

Рис.3.45. Диалоговое окно Консолидация

 

Таблица 3.3. Торговая фирма «Шмидт и сыновья»

Исходные данные за январь
Наименование продукции	Цена в У.Е.	Продано	Выручка
Телевизоры
Видеомагнитофоны
Музыкальные центры
Видеокамеры
Видеоплейеры
Аудиоплейеры
Радиотелефоны
Итого

 

Скопируйте таблицу на два других листа, назвав их Февраль и Март. Измените данные второго и третьего столбца (по вашему усмотрению).

На четвертом листе (Квартал. Способ 1) создайте макет таблицы сводных показателей (Продано и Выручка за квартал). Заполните эту таблицу, суммируя данные, находящиеся в соответствующих ячейках листов показателей за январь - март.

На пятом листе (Квартал. Способ 2) получите аналогичную таблицу, но с помощью консолидации.

Изучите структуру полученной консолидированной таблицы. Она содержит скрытые строки, в которых находится информация из других листов. Скрытые строки можно открывать и снова скрывать, нажимая соответственно кнопки «+» и «–». Проверьте, изменяются ли значения консолидированной таблицы при изменениях в исходных таблицах.

Сравните оба способа.

 

Задание 6. Выбрав данные из таблицы 3.4, разнести их поквартально по разным листам одного файла. Определить среднюю цену производителей по каждой позиции в каждом квартале (в тыс. руб.) и соотношение цен на отдельные виды энергоресурсов с ценой на нефть (в процентах). В другом файле с помощью консолидации сформировать таблицу Среднегодовые цены. Для переноса текстовой информации (имена строк и столбцов) используйте опцию Использовать в качестве имен.

 

Таблица 3.4. Цены производителей на отдельные виды энергоресурсов^*)

Период	Цены, тыс. руб. за тонну
Нефть	Бензин автомобильный	Топливо дизельное	Мазут топочный	Газ естественный^**)	Уголь
энергетический	для коксования
1997 г. апрель май июнь июль август сентябрь октябрь ноябрь декабрь   1998 г. январь февраль март					37,2 39,0 39,0 39,0 39,3 39,3 39,5 39,6 39,4     40,2 40,3 44,6
^*)Цены приведены на энергоресурсы, отгруженные на внутрироссийский рынок ^**)За тысячу кубических метров

Задание 7. Привести все данные в исходной таблице 3.5 к параметрам Земли, используя только абсолютную адресацию (не забудьте сменить размерность величин в заголовках новой таблицы!).

В полученной таблице, используя Данные | Сортировка или «горячие клавиши» Сортировка по возрастанию и Сортировка по убыванию:

1) отсортировать данные в порядке убывания количества спутников;

2) отсортировать данные в алфавитном порядке названий планет;

3) отсортировать данные в порядке возрастания массы.

Разместить результаты сортировки (а затем и результаты фильтрации) на различных листах рабочей книги.

 

Таблица 3.5 Планеты Солнечной системы

Планета	Период обращения, земной год	Расстояние от Солнца, млн. км	Диаметр, тыс. км	Масса, Тт	Количество спутников
Меркурий	0,241		4,9	0,32×10^9
Венера	0,615		12,1	4,86×10^9
Земля			12,8	6,0×10^9
Марс	1,881		6,8	6,1×10^8
Юпитер	11,86		142,6	19,07×10^11
Сатурн	29,46		120,2	57,09×10^10
Уран	84,01		49,0	87,24×10^9
Нептун	164,8		50,2	10,34×10^10
Плутон	247,7		2,8	0,1×10^9

 

Задание 8. С помощью Автофильтра осуществить:

1) поиск планет, имеющих диаметр менее 4-х диаметров Земли и массу менее массы Земли;

2) поиск планет, находящихся от Солнца на расстоянии не менее 0,5 расстояния от Земли, имеющих массу от одной до 100 масс Земли и не более 2-х спутников;

3) поиск трех планет, имеющих самый большой диаметр.

Вызов Автофильтра происходит при выполнении последовательности действий Данные | Фильтр | Автофильтр. При этом в ячейках, где располагаются заголовки, появляются кнопки. При нажатии на них появляется меню с условиями отбора автофильтра.

Все – задает все строки.

Первые 10 – определяет строки с максимальным или минимальным значением ячеек текущего столбца. Эта строка открывает диалоговое окно Наложение условия по списку (рис. 3.46).

Рис.3.46. Диалоговое окно Наложение условия по поиску

 

Условие – выбирает строки, удовлетворяющие одному или двум условиям. Эта строка меню открывает диалоговое окно Пользовательский автофильтр (рис. 3.47), где в левом поле под надписью Показать только те строки, значения которых, выбирается необходимый оператор сравнения (равно, меньше, больше и т.п.), а в правом поле задается значение сравнения. Нижние поля используются для второго условия отбора, соединяющегося с первым знаками логических операций И или ИЛИ.

Рис.3.47. Диалоговое окно Пользовательский автофильтр

 

Задание 9. С помощью Расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2-х.

Для этого следует сначала определить (создать) три области (диапазона):

1) исходный диапазон - область базы данных;

2) диапазон условий - область, где задаются критерии фильтрации;

3) диапазон результатов - область, в которой будут появляться результаты фильтрации.

Первый диапазон уже определен – это преобразованная исходная таблица (пусть, например, она располагается в ячейках А1:F10). Второй диапазон (пусть это будет А12:F13) необходимо сформировать из строки заголовков таблицы (просто скопировав ее; отметим, что эта строка не должна являться результатом объединения нескольких ячеек!) и строки, где в соответствующих ячейках записываются условия фильтрации (например, >10, >=2 и т.п.). Третий диапазон нужен, если результат фильтрации собираются расположить не на месте исходного диапазона, а в другом месте. Он также состоит из строки заголовков и нескольких пустых строк (например, А15:F17). В эти строки запишется результат фильтрации. Если отведенных строк не хватит, появится предупредительное сообщение, и фильтрация может быть продолжена.

Чтобы выполнить фильтрацию, необходимо воспользоваться командами меню Данные | Фильтр | Расширенный фильтр (рис. 3.48) и в открывшемся диалоговом окне указать ячейки диапазонов.

Рис.3.48. Диалоговое окно Расширенный фильтр

 

Опция Только уникальные записи позволяет исключить из таблицы все повторяющиеся строки, оставив только уникальные. Для этого необходимо ее пометить и провести фильтрацию, не задавая диапазон условий.

Чтобы восстановить таблицу после работы Автофильтра или Расширенного фильтра, следует выполнить следующие действия: Данные | Фильтр | Отобразить все.

 

 

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

1. Что представляет собой список?

2. Какие требования предъявляются к спискам?

3. Какие операции можно выполнять со списками?

4. Что называют строкой заголовка?

5. Перечислите основные правила для эффективной работы со списками.

6. Какие операции можно выполнять при использовании инструмента Форма?

7. Как создается список для пользовательского порядка сортировки, в каких случаях это бывает необходимо?

8. Какова технология создания условий для расширенного фильтра и функций баз данных?

9. Опишите параметры диалоговое окна Форма.

10. Какие операции необходимо выполнить для сортировки данных в Exсel?

11. Опишите параметры диалогового окна Сортировка диапазона.

12. Опишите технологию Пользовательского порядка сортировки.

13. Опишите технологию использования автофильтра.

14. Что такое Пользовательский автофильтр?

15. Какие необходимо соблюдать правила при использовании в Пользовательском автофильтре операций отношения И (ИЛИ)?

16. Чем Расширенный автофильтр отличается от Автофильтра и Пользовательского автофильтра?

17. Укажите правила формирования диапазона условий.

18. Опишите правила создания вычисляемых условий.

19. Опишите технологию применения функций баз данных для анализа данных.

20. В чем заключается технология консолидации данных?

21. Что такое сводная таблица?

22. Опишите технологию создания сводных таблиц.

 

 

Литература

1. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. – С-Пб.: БХВ – Санкт-Петербург, 2009. – 336 с.

2. Геращенко И.П. Экономико-математические методы и модели: учебное пособие /И.П. Геращенко, Е.В. Шульга. – Омск: Изд-во Омского экономического института, 2010. – 292 с.

3. Гусева О.Л., Миронова Н.Н. Excel для Windows. Практические работы //Информатика и образование. – 2012. – №2–6.

4. Капустин В.Ф. Практические занятия по курсу математического программирования. – Л.: Изд-во ЛГУ, 2011. – 192 с.

5. Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финансово-экономические расчеты в Excel. Изд. 3-е. – М.:Филинъ, 2010. – 328 с.

6. Рудикова Л. В. Microsoft Excel для студента / Л.В. Рудикова. – СПб.: БХВ - Петербург, 2011. – 368 с

 

 

Учебное издание