Агрегирование данных и деловая графика в MS Excel

Лабораторная работа № 1

АГРЕГИРОВАНИЕ ДАННЫХ И ДЕЛОВАЯ ГРАФИКА В MS EXCEL

Расчетное время выполнения работы – 4 а.ч.

Цель работы:

1. Ознакомиться с понятиями «агрегирование данных», «консолидация данных», «сводная таблица», «консолидированная таблица».

2. Понять назначение операций агрегирования и консолидации данных.

3. Изучить возможности и инструментарий для реализации задач агрегации и консолидации данных в MS Excel и приобрести навыки решения таких задач.

4. Ознакомиться с возможностями графического представления данных (деловой графики) в MS Excel.

5. Изучить инструментарий и возможности эффективного применения деловой графики, приобрести навыки применения MS Excel для графического представления данных.

Для чего это нужно?

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

Очень часто приходится сталкиваться со следующей ситуацией. Во-первых, данные, подлежащие анализу, расположены в различных источниках и могут иметь самые разнообразные форматы и типы - в отдельных файлах офисных документов (Excel, Word, обычных текстовых файлах), в учетных системах («1С:Предприятие», «Парус» и др.), в базах данных (Oracle, Access, dBase и др.). Во-вторых, данные могут быть избыточными или, наоборот, недостаточными. И, в-третьих, данные могут быть «грязными», то есть содержать факторы, мешающие их правильной обработке и анализу (пропуски, аномальные значения, дубликаты и противоречия).

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

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

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

Под визуализацией данных понимают, как правило, представление данных в виде, который обеспечивает наиболее эффективную работу человека по их изучению. Визуализация данных находит широкое применение, в частности, в прогнозировании, в интеллектуальном анализе данных, в бизнес-анализе и в целом ряде других областей деятельности человека. Визуализация данных часто рассматривается специалистами как часть инфографики (графического способа подачи информации, данных и знаний, целью которого является быстро и чётко преподносить сложную информацию. Помните пословицу: «Лучше один раз увидеть, чем сто раз услышать»?). Область инфографики, предназначенной для наглядного представления числовых данных, отображения соотношения различных значений или динамики изменения показателей и включающей представление данных в виде диаграммы (графики) различных видов, принято называть деловой графикой. Очевидно, что навыки использования методов и инструментов деловой графики могут быть весьма полезны не только в профессиональной, но и бытовой деятельности человека.

Консолидация данных

Консолидация данных является начальным этапом реализации любой аналитической задачи или проекта. Консолидация (лат. con - вместе, solido - скрепляю, укрепляю) – процесс объединения элементов из разных исходных областей. В MS Excel исходные области с данными могут располагаться на любом листе или книге, на других открытых листах или книгах. В зависимости от организации и структуры консолидируемых данных могут применяться различные техники консолидации.

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

Существуют следующие варианты консолидации данных:

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

Консолидация по категории

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

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

АГРЕГИРОВАНИЕ ДАННЫХ

Агрегирование или агрегация (лат. aggregatio «присоединение») – процесс объединения элементов в одну систему (пример: восемь блондинок в розовом идут в музей ночью в Таганроге есть объединение по каким-либо однородным показателям с целью получения общих итогов).

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

СПИСОК - набор строк (записей) на рабочем листе с однотипными данными, которые взаимосвязаны и имеют постоянный формат.

ПОЛЯ - категории, по которым упорядочена информации в списке (отдельный столбец - отдельная категория). Поля имеют метки – заголовки полей.

Более подробно об агрегации данных с помощью можно посмотреть здесь: http://sapr.ictis.sfedu.ru/files/16

 

 

Задания:

1. Разработать макеты таблиц, содержащих информацию об успеваемости студентов группы за предыдущие семестры по 4-5 дисциплинам, аттестация по которым предусматривает экзамен. В макет включить экзаменационные оценки и рейтинговые баллы. Занести необходимые исходные данные и расчетные формулы в ячейки электронной таблицы.

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

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

 

Дополнительные задания:

1. Используя справочную систему MS Excel (или иные доступные источники):

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

­ познакомьтесь с возможностями сортировки данных в таблице.

­ познакомьтесь с возможностями агрегирования данных на основе промежуточных итогов.

­ познакомьтесь с возможностями агрегирования данных на основе консолидации данных.

2. Измените внешний вид деловой графики (п. 3 основного задания), используя доступные инструменты (2-3 различных варианта).

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

 

Содержание отчета

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

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

3. Краткие выводы по проделанной работе.

 

Лабораторная работа № 1

АГРЕГИРОВАНИЕ ДАННЫХ И ДЕЛОВАЯ ГРАФИКА В MS EXCEL

Расчетное время выполнения работы – 4 а.ч.

Цель работы:

1. Ознакомиться с понятиями «агрегирование данных», «консолидация данных», «сводная таблица», «консолидированная таблица».

2. Понять назначение операций агрегирования и консолидации данных.

3. Изучить возможности и инструментарий для реализации задач агрегации и консолидации данных в MS Excel и приобрести навыки решения таких задач.

4. Ознакомиться с возможностями графического представления данных (деловой графики) в MS Excel.

5. Изучить инструментарий и возможности эффективного применения деловой графики, приобрести навыки применения MS Excel для графического представления данных.

Для чего это нужно?

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

Очень часто приходится сталкиваться со следующей ситуацией. Во-первых, данные, подлежащие анализу, расположены в различных источниках и могут иметь самые разнообразные форматы и типы - в отдельных файлах офисных документов (Excel, Word, обычных текстовых файлах), в учетных системах («1С:Предприятие», «Парус» и др.), в базах данных (Oracle, Access, dBase и др.). Во-вторых, данные могут быть избыточными или, наоборот, недостаточными. И, в-третьих, данные могут быть «грязными», то есть содержать факторы, мешающие их правильной обработке и анализу (пропуски, аномальные значения, дубликаты и противоречия).

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

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

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

Под визуализацией данных понимают, как правило, представление данных в виде, который обеспечивает наиболее эффективную работу человека по их изучению. Визуализация данных находит широкое применение, в частности, в прогнозировании, в интеллектуальном анализе данных, в бизнес-анализе и в целом ряде других областей деятельности человека. Визуализация данных часто рассматривается специалистами как часть инфографики (графического способа подачи информации, данных и знаний, целью которого является быстро и чётко преподносить сложную информацию. Помните пословицу: «Лучше один раз увидеть, чем сто раз услышать»?). Область инфографики, предназначенной для наглядного представления числовых данных, отображения соотношения различных значений или динамики изменения показателей и включающей представление данных в виде диаграммы (графики) различных видов, принято называть деловой графикой. Очевидно, что навыки использования методов и инструментов деловой графики могут быть весьма полезны не только в профессиональной, но и бытовой деятельности человека.

Консолидация данных

Консолидация данных является начальным этапом реализации любой аналитической задачи или проекта. Консолидация (лат. con - вместе, solido - скрепляю, укрепляю) – процесс объединения элементов из разных исходных областей. В MS Excel исходные области с данными могут располагаться на любом листе или книге, на других открытых листах или книгах. В зависимости от организации и структуры консолидируемых данных могут применяться различные техники консолидации.

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

Существуют следующие варианты консолидации данных:

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