Практическая работа №5


Анализ данных и новые возможности Excel
Динамические таблицы.
Пусть имеется таблица, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляются новые записи, удаляются, редактируются), например, рис. 1.
Рис. 1. Пример таблицы.
Для удобства работы данную таблицу можно сделать динамической. Для этого выделяем любую ячейку в таблице и на вкладке «Главная», разворачиваем список «Форматировать как таблицу» (рис. 1).

Рис.2. Форматировать как таблицу.
В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем «ОК» и получаем на выходе примерно следующее:

Рис.3. Пример динамической таблицы.
В результате после преобразования диапазона в динамическую таблицу имеем следующие преимущества:
1. Созданная таблица получает имя «Таблица1, 2, 3» и т.д., которое можно изменить на более понятное на вкладке «Конструктор». Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы.
2. Созданная один раз таблица автоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой таблице новые строки - она растянется ниже, если добавить новые столбцы - разойдется вширь.
3. В шапке «Таблицы» автоматически включается «Автофильтр».
4. При добавлении новых строк в них автоматически копируются все формулы.
5. При создании нового столбца с формулой - она будет автоматически скопирована на весь столбец - не надо тянуть формулу черным крестом автозаполнения.
6. При прокрутке «Таблицы» вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше [18].
Консолидация
В Excel данные, используемые для обработки и анализа, могут размещаться в разных ячейках и диапазонах ячеек одного листа, на нескольких листах одной книги и даже в различных книгах. При этом с помощью консолидации и связывания объектов процесс получения и отображения результатов обработки данных можно существенно упростить.
Под консолидацией данных понимают объединение значений из нескольких диапазонов данных.
Консолидация данных в Excel – это способ получения итоговой информации, при котором данные, расположенные в нескольких различных областях, объединяются в соответствии с выбранной функцией обработки.
Другим способом консолидации данных является создание отчета сводной таблицы, основанной на понятии многомерного анализа данных [11].
Большинство экономистов слышали термины «многомерные данные», «виртуальный куб», «OLAP-технологии» и т.п. Но при детальном разговоре обычно выясняется, что почти все не очень представляют, о чем идет речь. То есть люди подразумевают нечто сложное и обычно не имеющее отношение к их повседневной деятельности. На самом деле это не так.
Можно с уверенностью утверждать, что экономисты практически постоянно сталкиваются с многомерными данными, но пытаются представить их в предопределенном виде с помощью электронных таблиц. Под многомерностью здесь подразумевается возможность ввода, просмотра или анализа одной и той же информации с изменением внешнего вида, применением различных группировок и сортировок данных. Например, план продаж можно проанализировать по следующим критериям:
виды или группы товаров;
бренды или категории товаров;
периоды (месяц, квартал, год);
покупатели или группы покупателей;
регионы продаж
и т.п.
Каждый из приведенных критериев в терминах многомерного анализа данных называется «измерением». Можно сказать, что измерение характеризует информацию по определенному набору значений.
Сводные таблицы
Сводная таблица (сводный отчет) - это пользовательский интерфейс для отображения многомерных данных. С помощью данного интерфейса можно группировать, сортировать, фильтровать и менять расположение данных с целью получения различных аналитических выборок. Обновление отчета производится простыми средствами пользовательского интерфейса, данные автоматически агрегируются по заданным правилам, при этом не требуется дополнительный или повторный ввод какой-либо информации. Интерфейс сводных таблиц Excel является, пожалуй, самым популярным программным продуктом для работы с многомерными данными. Он поддерживает в качестве источника данных как внешние источники данных (OLAP-кубам и реляционным базам данных), так и внутренние диапазоны электронных таблиц. Начиная с версии 2000 (9.0), Excel поддерживает также графическую форму отображения многомерных данных – сводная диаграмма (Pivot Chart).
Реализованный в Excel интерфейс сводных таблиц позволяет расположить измерения многомерных данных в области рабочего листа. Для простоты можно представлять себе сводную таблицу, как отчет, лежащий сверху диапазона ячеек (на самом деле есть определенная привязка форматов ячеек к полям сводной таблицы). Сводная таблица Excel имеет четыре области отображения информации: фильтр, столбцы, строки и данные. Измерения данных именуются полями сводной таблицы. Эти поля имеют собственные свойства и формат отображения.
Еще раз хочется обратить внимание, что сводная таблица Excel предназначена исключительно для анализа данных без возможности редактирования информации. Ближе по смыслу было бы повсеместное употребление термина «сводный отчет» (Pivot Report), и именно так этот интерфейс и назывался до 2000го года. Но почему-то в последующих версиях разработчики от него отказались.
Для создания сводной таблицы нужно перейти на вкладку «Вставка», где в группе «Таблицы» выбрать команду «Сводная таблица» (рис. 4).

Рис.4. Создание сводной таблицы.
Далее в появившемся окне в поле «Выбрать таблицу или диапазон» автоматически выберется вся ваша таблица. Если нужно выбрать только часть таблицы для анализа, то нажмите кнопку в конце строки ввода и мышкой выделите нужную часть таблицы. Обратите особое внимание на то, что верхняя строка выделенного диапазона обязательно должна содержать названия столбцов, т.к. она не будет обрабатываться как данные, а определит будущие названия полей, по которым будет происходить формирования отчета «Сводной таблицы». После выделения нажмите на кнопку в конце строки ввода еще раз. Далее «ОК» (рис. 5).

Рис.5. Выбор диапазона данных для создания сводной таблицы.
Теперь у вас откроется новый лист, в котором и будут формироваться отчеты Сводной таблицы. В левой части листа формируется сам отчет, а в правой список полей Сводной таблицы и параметры формирования отчета. В списке полей (рис. 6) выберите те, по которым вам нужен отчет, а в нижней части мышкой поместите поля в нужные области в нужном порядке (вначале можно проиграться с полями, перетаскивая их в разные области и следя за изменением отчета в левой части экрана).

Рис. 6. Список полей сводной таблицы
На рис. 7 формируется отчет с суммой продаж по городам и по товарам в каждом городе [18].

Рис. 7. Сводный отчет с суммой продаж по городам и по товарам в каждом городе
Практическая работа №5. Консолидация, динамические и сводные таблицы
Цель: Приобрести знания и навыки по работе с динамическими таблицами в пакете MS Excel. Научиться создавать консолидацию и сводные таблицы для анализа данных.
Задание 1. Консолидация
1. Создать новую рабочую книгу. Присвоить листам книги имена: «Январь», «Февраль», «Март», «1 квартал».
2. Разместите на листе «Январь» таблицу 1:
Таблица1. Январь

3. Разместите на листе «Февраль» таблицу:
Таблица1. Февраль

4. Разместите на листе «Март» таблицу:
Таблица1. Март

5. Перейти на лист «1 квартал» и выделить ячейку А1. Выполнить команду Данные/группа Работа с данными/Консолидация.
5. В открывшемся окне в поле «Функция» установить «Сумма».
7. В поле «Ссылка» установить фокус ввода и по очереди выделять диапазоны для консолидации, находящиеся на предыдущих листах. Выделение каждого диапазона заканчивать нажатием кнопки «Добавить», при этом адрес диапазона будет помещен в окно «Список диапазонов».
8. Установить флажки «Подписи верхней строки» и «Значение левого столбца». Флажок «Создавать связи с исходными данными» пока не устанавливать. Нажать «ОК». Результат консолидации появится на листе «1 квартал».
9. Исследуйте полученные результаты. Например, измените количественные данные (объем сделок или их количество по какому-либо агенту) на листе «Март», проверьте, изменились ли при этом данные листа «1 квартал» (если все верно сделано, то изменений не будет). А теперь выполните команду Данные/Консолидация и сразу «ОК», убедитесь в обновлении таблицы на листе «1 квартал».
10. Выделите на листе «1 квартал» ячейку А1 и выполните команду Данные/Консолидация и установите флажок «Создавать связи с исходными данными» и «ОК». Внесите количественные изменения в исходные данные листа «Январь» и проследите за тем, как изменились итоги на листе «1 квартал», теперь они будут обновляться автоматически.
Задание 2:
1. Наберите в табличном процессоре Excel исходный список (таблицу) объемов продаж фирмы «Гигант».
Задайте первому рабочему листу имя «Исходная».
При создании списки сначала наберите заголовки столбцов, затем задайте форматы ячеек: для столбцов «Покупатель», «Товар», «Ед. изм.» – общий, «Количество» – числовой целый, «Цена за ед.», «Сумма», «Сумма с НДС» – денежный с двумя десятичными знаками, «НДС» – %- процентный, «Дата сделки» – Дата.
Введите первую запись и задайте формулы для вычисляемых ячеек (столбцы «Сумма» и «Сумма с НДС»).
Сделайте таблица динамической и заполните данными согласно рис. 1.
2. На основе исходной таблицы создайте сводную таблицу представленную на рис. 2.
Задайте рабочему листу имя «Покупатели». Создайте заголовок таблицы «Данные по покупателям», отформатируйте заголовок.

Рис.1. Таблица «Исходная»

Рис. 2. Таблица «Покупатель»
3. На основе исходной таблицы создайте таблицу, представленную на рис. 3.

Рис.3. Таблица «Итоги»
Поле «Покупатель» добавьте в фильтр отчета.
4. Проведите группировку данных по периодам.
Для это на вкладке параметры в группе «Группировать» выберите «Группировать по полю» и в отрывшемся окне указать необходимые условия группировки.
В результате должна получиться следующая таблица:

Рис.4. Таблица «Итоги по периодам»
Задайте рабочему листу имя «Итоги по периодам». Создайте заголовок таблицы «Данные по периодам».
5. На новом листе постройте сводную таблицу, в которой указан процент количества каждого типа проданного товара от общего числа.
Для этого в разделе «Значения» выберите свойство «Параметры полей значений» и во вкладке «Дополнительные вычисления» выберите соответствующий тип вычисления.
Задайте рабочему листу имя «Итоги в процентах». Создайте заголовок таблицы «Данные в процентном отношении от общей суммы».
Индивидуальные задания.
1. На новом листе создайте таблицу как на рис. 5, а рабочему листу задайте имя «Исходная 2».
2. Добавьте столбец «Сумма», рассчитайте его значения как произведение «Цена» на «Количество».

Рис.5. Таблица «Исходная»
3. В соответствии с вариантом создайте следующую сводную таблицу:
1) Объем сделок между всеми покупателями и продавцами в процентном отношении от общей суммы (название строк – «Покупатель», название столбцов – «Продавец», значения – «Сумма по полю Сумма»).
2) Число сделок каждого из покупателей для каждого из товаров (название строк – «Покупатель», название столбцов – «Товар», значения – «Количество по полю «Количество»).
3) Количество каждого проданного товара каждым из продавцов для каждого из покупателей в процентном отношении от общей суммы (название строк – «Товар» и «Продавец», название столбцов – «Покупатель», значения – «Сумма по полю «Количество»).
4) Процентная доля объема сделок каждого из покупателей по всем продавцам (название столбцов – «Покупатель», название строк – «Продавец», значения – сумма по каждому столбцу равно 100%).
5) Процентная доля продаж каждого из товаров относительно товара «компьютер» (название строк – «Товар», название столбцов – «Продавец» и «Покупатель», значения – процентная доля продаж относительно товара «Компьютер»).
6) Объем сделок между всеми покупателями и продавцами в процентном отношении по покупателям (название строк – «Покупатель», название столбцов – «Продавец», значения – «Сумма по полю Сумма»).
7) Объем сделок между всеми покупателями и продавцами в процентном отношении по продавцам (название строк – «Покупатель», название столбцов – «Продавец», значения – «Сумма по полю Сумма»).
8) Процентная доля продаж каждого из продавцов относительно NTT. (название строк – «Продавец», название столбцов – «Товар», значения - процентная доля продаж относительно продавца «NTT»).
9) Процентная доля объема сделок каждого из продавца по всем покупателям (название столбцов – «Покупатель», название строк – «Продавец», значения – сумма по каждой строке равно 100%).
10) Объем сделок продавцами по каждому товару в процентном отношении от общей суммы (название строк – «Продавец», название столбцов – «Товар», значения – «Сумма по полю Сумма»).

Приложенные файлы

  • docx 10798197
    Размер файла: 623 kB Загрузок: 1

Добавить комментарий