Создание сводных таблиц в ТП Excel

 

 

ФГБОУ ВПО КОСТРОМСКАЯ ГОСУДАРСТВЕННАЯ

СЕЛЬСКОХОЗЯЙСТВЕННАЯ АКАДЕМИЯ

 

Факультет заочного обучения

 

Кафедра экономической кибернетики

 

 

 

Дисциплина «Информатика»

 

КОНТРОЛЬНАЯ РАБОТА

 

Тема: Создание сводных таблиц в ТП Excel

 

 

 

Выполнил:

Студент заочного факультета

__ курса __4__ группы

Коурова Юлия Владимировна

Шифр__13973 _________

 

Руководитель:

доцент, к.э.н. Петров Пётр Петрович

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

КОСТРОМА – 2014

 

Содержание

 

 

ЗАДАНИЕ №1

Найти и обобщить материал по теоретическому вопросу «Создание сводных таблиц в ТП Excel».

ЗАДАНИЕ №2

Оформить ответ на теоретический вопрос в текстовом процессоре MS Word.

Общие сведения о сводных таблицах

Обрабатывать большие объемы информации и составлять сложные многоуровневые отчеты достаточно непросто без использования средств автоматизации. Excel  является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных (Pivot table).

Сводная таблица(Pivot Table) – инструмент Excel, используемый для создания уникального представления данных и последующего анализа. Сводная таблица в Excel используется для:

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

Сводная таблица может быть построена на основе правильно сформированной исходной таблицы данных:

• таблица данных не должна содержать объединенных ячеек;
• не должна содержать полностью пустых строк и пустых столбцов;
• в каждом столбце должны содержаться данные одного типа (либо текст, либо дата, либо числа);
• каждый столбец должен иметь уникальный, краткий и информативный заголовок;
• столбцы должны идти одной строкой и не должны содержать пустых и объединенных ячеек.

При рассмотрении ограничений сводных таблиц следует учесть, что начиная с версии Microsoft Excel 2007 увеличено количество строк и столбцов, включаемых в рабочий лист. Это позволяет говорить о поддержке больших объемов данных при управлении сводными таблицами. Увеличение размера рабочего листа приводит к расширению функциональных возможностей многих функций и команд программы, а заодно и области применения сводных таблиц.

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

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

Сводная таблица состоит из четырех областей:

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

Основные вычислительные функции области данных:

• Сумма (Sum)
• Количество (Count)
• Среднее (Average)
• Максимум (Max)
• Минимум (Min)
• Произведение (Product)

 

2. Область строк – состоит из заголовков в левой части сводной таблицы и отображает уникальные значения вставленного поля.

3. Область столбцов – состоит из заголовков столбцов сводной таблицы.

4. Область страниц и фильтров – необязательная область. Расположена в верхней части сводной таблицы и выполняет функции фильтрации по вставленным в неё полям.

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

Сам набор данных не изменяется и не подключается к сводной таблице. Сводная таблица — это всего лишь средство, используемое для создания уникального представления данных. Сводная таблица позволяет добиться интерактивного управления набором данных.

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

При использовании сводной таблицы следует учитывать, что она не обновляет свои значения автоматически при изменении значений в исходных данных. Для выполнения обновления необходимо выделить любую ячейку сводной таблицы → Правая кнопка мыши → Обновить(Refresh) или вкладка Данные (Data) → Обновить(Refresh). Это связано с тем, что сводная таблица не содержит прямой ссылки на исходные данные, а хранит их в кэше, что позволяет ей достаточно быстро обрабатывать данные.

 

Алгоритм создания сводной таблицы

Создание сводной таблицы рассмотрим на примере Excel 2010. Создавать сводные таблицы можно двумя способами. Рассмотрим каждый из них.

Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»

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

1. Для начала создаем простую таблицу с перечислением элементов, которые нам нужно использовать в отчете. Верхняя строка обязательно должна содержать заголовки столбцов.

2. Открываем вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица».

 

Если вместе со сводной таблицей нужно создать и сводную диаграмму – нажимаем на стрелку в нижнем правом углу значка «Сводная таблица» и выбираем пункт «Сводная диаграмма».

3. В открывшемся диалоговом  окне «Создание сводной таблицы» выбираем только что созданную таблицу с данными или ее диапазон. Для этого выделяем нужную область.

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

4. Далее указываем, где будет размещен отчет сводной таблицы. Удобнее всего это делать на новом листе.

5. После подтверждения  действия нажатием кнопки «ОК», будет создан и открыт макет отчета. Рассмотрим его.

 

В правой половине окна создается панель основных инструментов управления - «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отмечаем необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.

Расположением полей можно управлять – делать их названиями строк или столбцов, перетаскивая в соответствующие окна, а так же и сортировать в удобном порядке. Можно фильтровать отдельные пункты, перетащив соответствующее поле в окно «Фильтр». В окно «Значение» помещается то поле, по которому производятся расчеты и подводятся итоги.

 
         Другие опции для редактирования отчетов доступны из меню «Работа со сводными таблицами» на вкладках «Параметры» и «Конструктор». Почти каждый из инструментов этих вкладок имеет массу настроек и дополни тельных функций.

Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм»

Чтобы применить этот способ, делаем доступным инструмент, который по умолчанию на ленте не отображается. Открываем вкладку «Файл» - «Параметры» - «Панель быстрого доступа». В списке «Выбрать команды из» отмечаем пункт «Команды не на ленте». А ниже, из перечня команд, выбираем «Мастер сводных таблиц и диаграмм». Нажимаем кнопку «Добавить» (Рис.7). Иконка мастера появится вверху, на панели быстрого доступа.

 

 

Кликаем по иконке мастера в панели быстрого доступа. В диалоговом окне поставьте переключатель на нужный нам пункт списка источников данных:

• «в списке или базе данных Microsoft Excel» - источником будет база данных рабочего листа;
• «во внешнем источнике данных» - если существует подключение к внешней базе, которое нужно будет выбрать из доступных;
• «в нескольких диапазонах консолидации» - если требуется объединение данных из разных источников;
• «данные в другой сводной таблице или сводной диаграмме» - в качестве источника берется уже существующая сводная таблица или диаграмма.

После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)». (Рис.8)

 

 

 

Если в качестве источника выбираем текущий документ, где уже есть простая таблица с элементами будущего отчета, задаем диапазон охвата - выделяем нужную область. Далее выбираем место размещения таблицы - на новом или на текущем листе, и нажимаем «Готово». Сводная таблица будет создана.

 

 

 

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

При выборе «Создать поля страницы» сначала указываем диапазоны источников данных: выделяем первый диапазон, нажимаем «Добавить», потом следующий и т.д.

     

 

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

После завершения нажимаем кнопку «Далее», выбираем месторасположение будущей сводной таблицы – на текущем листе или на другом, нажимаем «Готово» и наш отчет, собранный из нескольких источников, будет создан.

При выборе внешнего источника данных используется приложение Microsoft Query, входящее в комплект Excel 2010 или, если требуется подключиться к данным Office, используются опции вкладки «Данные».

Если в документе уже присутствует отчет сводной таблицы или сводная диаграмма - в качестве источника можно использовать их. Для этого достаточно указать их расположение и выбрать нужный диапазон данных, после чего будет создана новая сводная таблица.

Заключение

Работу в Excel можно разбить на два этапа: вычисления и форматирование данных. Несмотря на немалое количество встроенных инструментов и формул, которые способствуют выполнению обеих задач, сводные таблицы зачастую предоставляют самый быстрый и эффективный способ расчета и форматирования данных.

Использование сводной таблицы для составления отчетности уменьшает количество действий наполовину и снижает возможность появления ошибок вследствие «человеческого фактора». Кроме того, сводные таблицы позволяют легко и быстро формировать данные. Сводные таблицы используются не только для расчетов и подведения итогов на основе исходного набора данных. Зачастую с помощью сводных таблиц можно выполнять задачи быстрее и эффективнее, чем при использовании стандартных функций и формул. Например, можно применять сводные таблицы для быстрого транспонирования данных из горизонтального представления в вертикальное. Можно также применять их для быстрого поиска и подсчета уникальных значений в наборе данных. Кроме того, сводные таблицы можно применять для подготовки данных для диаграмм.

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

 

ЗАДАНИЕ №3

Фирма «К» является предприятием, выпускающим промышленное оборудование. В текущем месяце фирма осуществила поставку своей продукции корпорациям «А», «В» и «С». Обычно фирма предоставляет своим поставщикам кредит (отсрочку по оплате) сроком на 1 месяц под соответствующие проценты.

Данные о совершенных продажах в кредит представлены в таблице 1.

Таблица 1

Данные о совершенных продажах в кредит

Покупатели	Сумма продаж	Процент	Выручка
Корпорация А	73000.00	3,25
Корпорация В	37000.00	3,0
Корпорация С	33000.00	3,7

Ежегодный рост инфляции составляет в среднем 12% (т.е. 1% в месяц).

Необходимо:

• определить сумму выручки фирмы от продажи продукции в кредит с учетом приведенных выше условий;
• на базе разработанной таблицы, определить критический уровень инфляции, при котором фирме не выгодно будет продавать свою продукцию на прежних условиях.

Решить задачу с помощью табличного процессора Excel.  Осуществить графическую поддержку решения.

Построим в Excel таблицу и введем данные о совершенных продажах в кредит (рис.12).

 

Рис.12 Копия 1 экрана Excel

При заполнении таблицы, показанной на рисунке 12, учитываем форматирование ячеек (формат денежный в ячейках В2, В3, В4 и процент - С2, С3, С4, С6).

В столбец D в ячейки D2, D3 и D4 вводим формулы для расчёта выручки фирмы «К» по каждой корпорации:

Выручка = сумма продаж + сумма продаж*процент (D2=B2+D2*C2)

далее, используя автозапонение запоняем ячейки D3 и D4.

Вычисляем общую сумму продаж, которую могла бы получить фирма от продаж корпорациям А, В, С без кредитования в ячейке В5 и суммарную выручку по кредиту в ячейке D5.

Строим круговую диаграмму структуры денежной выручки фирмы «К».

 

В ячейку D6 вносим формулу, которая учитывает, сколько денег из полученной выручки при продаже в кредит «пропало» из-за инфляции, а именно,

Размер месячной инфляции = суммарная выручка по кредиту*процент инфляции

Тогда в ячейке D7

Суммарная выручка с учётом инфляции= суммарная выручка по кредиту- размер месячной инфляции

Для определения критического уровня инфляции понадобится некоторый критерий оптимальности в качестве основы для принятия решения. Для упрощения будем полагать, что таким критерием является величина разности между итоговыми суммами продаж с учётом начисленных процентов и инфляции и без них. Эту разность =D7-B5 разместили в ячейке D8. Тогда, если эта разность равна нулю, то процент инфляции - критичный. А если в ячейке D8 будет отрицательное число, то условия кредитования покупателей необходимо менять.

В нашем случае, когда взят средний процент инфляции за месяц, чистая прибыль фирмы «К» от продажи своей продукции в кредит составила 3 226,47 рублей (рис. 14)

Рис.14 Копия 2 экрана Excel

Рис.15 Копия 3 экрана Excel в режиме отображения формул

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

Самый простой - это построить таблицу и график зависимости критерия оптимальности от %-та инфляции. Точка пересечения с осью абсцисс и будет соответствовать критическому уровню инфляции. Построим таблицу зависимости рис. 16

Рис.16 Копия 4 экрана Excel

Рис.17 Копия 5 экрана Excel в режиме отображения формул

По получившейся таблице строим график зависимости рис.18

Рис.18 График для нахождения критического уровня инфляции

Из графика видно, что критический уровень инфляции составляет около 3,2%.

Попробуем определить вторым способом, применив технологии подбора параметра в Excel (Меню Данные – Анализ «что если» - Подбор параметра…). В появившемся диалоговом окне заполнили поля рис. 9.

 

Рис.19 Диалоговое окно для подбора критического уровня инфляции

 

После нажатия клавиши ОК в таблице появилось значение критического уровня инфляции

Рис.20 Копия 6 экрана Excel

Из таблицы видно, что критический уровень инфляции составляет 3,18%. Таким образом, более точный результат получается при использовании технологий подбора параметра.

Значит, сумма выручки фирмы «К» от продажи продукции в кредит с учетом приведенных выше условий составляет 146 226,47 руб.

Уровень - критический инфляции, при котором фирме не выгодно будет продавать свою продукцию на прежних условиях, равняется 3,18%. 
Список использованных источников

1. Безручко, В. Т. Информатика (курс лекций) [Текст]: учебное пособие для вузов по дисциплине "Информатика" по гуманитарным и экономическим направлениям и специальностям / В. Т. Безручко – М.: Форум: ИНФРА-М, 2012 . – 432 с. – (Высшее образование) - ISBN 978-5-8199-0285-1
2. Волков, В.Б.  Понятный самоучитель Excel 2010 [Текст] / В.Б. Волков. — СПб: Питер, 2010. — 252 с. : ил. - ISBN: 978-5-49807-771-0
3. Леонтьев, В.П. Новейшая энциклопедия персонального компьютера 2011. Компьютерный справочник [Текст] / В.П. Леонтьев. — М. : ОЛМА Медиа Групп, 2010. — 960 с. : ил. ISBN: 978-5-373-03920-8
4. Свиридова, М.Ю. Электронные таблицы Excel [Текст] : учеб. пособие для начал. проф. образования / М.Ю. Свиридова. — 3-е изд., стер. — М. : Академия, 2009. — 144 с. - ISBN: 978-5-7695-6397-3
5. Викиучебник по табличному процессору Microsoft Excel [Электронный ресурс] — Режим доступа: http://ru.wikibooks.org/wiki/Microsoft_Excel, свободный. — Загл. с экрана.
6. Excel — это не сложно! [Электронный ресурс] — Режим доступа: http://www.excel-vba.ru, свободный. — Загл. с экрана.
7. Как создать сводную таблицу в Exsel 2010 [Электронный ресурс] — Режим доступа: http://advanceduser.ru/microsoft-excel/svodnuju-tablicu-excel.html, свободный. — Загл. с экрана.
8. Сводные таблицы Exsel 2010 [Электронный ресурс] — Режим доступа: http://pivot-table.ru/, свободный. — Загл. с экрана.

Вариант 73, Коурова Ю.В., № зачетной книжки 13973, группа 4